索引的简介
类似于一本书的目录，起到优化查询的内容
索引的分类
- BTREE
- RTREE
- Hash innodb中自适应hash算法，自动维护
- fullText：全文索引（实现和es差不多的功能，把一句话拆分成一个个词，但效果不怎么理想，因为MySQL是结构化存储，es是json格式存储）
- Gis：地理位置索引（学的mongodb，一般存储地图）
BTREE 索引的演变
BTREE 索引原理图.png
上层节点存放下层节点的最小值，即根节点存放枝节点的最小值，枝节点存放叶子节点的最小值
BTREE+树在叶子节点增加了双向指针，BTREE*是在叶子节点和枝节点增加双向指针
辅助索引（二级索引）
1 .管理员选择一个列建立辅助索引
2 .MySQL自动将此列取出来
3 .将此列值排好序（从小到大）
4 .将排好的数据均匀的分配到叶子节点上
5 .生成枝节点和跟节点
6 .在叶子节点中的值，都会存储主键ID
聚集索引
1 .如果用的是辅助索引，拿到辅助索引叶子节点的聚集索引的ID值，去遍历，然后去查找数据
2 .MySQL会自动选择主键，作为聚集索引，没有主键会选择唯一主键，如果都没有会生成隐藏的
3 .MySQL进行存储数据时，会按照聚集索引列的值的顺序，有序的存储数据行
4 .聚集索引直接将原表数据页，作为叶子节点，然后提取聚集索引列向上生成枝节点和根节点
辅助索引细分
- 单列辅助索引
- 联合索引（覆盖索引）【重要】
- 唯一索引
索引树高度问题
索引树高度一般越低越好，一般维持在3-4层最佳
数据行多的时候
采用分布式架构，进行分库分表
字段长度
尽量选择，字符串长度短的列作为索引列，如果业务不允许，则采用前缀索引
数据类型
char 和varchar的选择，enum的选择
关于索引操作的问题
查询索引
desc 表名；
PRI ===> 主键索引（聚集索引）
MUL===> 普通索引（辅助）
UNI====> 唯一索引
show index from 表名; //查的比较全
创建索引
# 创建普通索引
alter table city add index idx_name(name)
这也是属于DDL语句，也会锁表。在业务不繁忙时间做，或者使用pt-osc工具
# 创建联合索引
alter table city add index idx_c_p(countrycode,population);
# 建立唯一索引
alter table city add index uniqe uniqx_dis(district);
注： 唯一索引列的值必须是唯一的，不能有重复值
# 建立前缀索引
alter table city add index idx_dis(district(5))
执行计划分析
作用
将优化器选择后的执行计划，给截取出来，给管理员判断执行效率。（在语句执行之前，把执行计划拿到）
获取执行计划
desc sql语句;
explain sql语句；
分析执行计划
table
表名，如果是多表连接，可能一个表出现问题，此时这个table就有作用了
type 重要
查询类型，MySQL查询中有两大类型 全表扫描和索引扫描
索引扫描的级别为: index,range,ref,eq_ref,const(system),null 从左到右的性能依次变好
-
index: 全索引扫描（整个索引列都进行遍历）
desc select id from ctiy -
range: 索引范围扫描（>,<,>=,<=,between and, in,and ,or,like）
desc select id from city where id <100; desc select * from city countrycode='CHN' and countrycode='USA'like,>,<,>=,<=的性能要比and,or,between and,in 好，因为MySQL采用BTREE* 树索引，枝节点和叶子节点都有双向指针。and,or,in,between and 都用不了双向指针，一般要把and和or改写为union all对于辅助索引来说，!=和not in 不走索引,但对于主键来讲 !=和not in 等语句是走range -
ref: 辅助索引等值查询
desc select * from city where countrycode='CHN' -
eq_ref: 在多表连接时，子表使用主键列或唯一键作为连接条件
A join B on A.x = B.y,B作为子表，B.y为主键列时，为eq_ref ，A为驱动表，是不走索引的desc select b.name,a.name,a.population from city as a join country as b on a.countrycode=b.code where a.population<100 -
const（system): 两个一样，当主键或者唯一键的等值查询时
desc select * from city where id=2 - NULL: 所查询的数据，在数据中没有时。
possible_key
可能会用到的key
key
最后用到的key
key_len
索引覆盖长度，当前列可以为空时，其中有一个字节标识是否为空
单列索引长度
注： 所有列的可以为空，utf8mb4的字符集
varchar(20) 说明
- 能存任意20个字符
- 不管存储的字符，数字，中文，都是1个字符最大预留长度是4字节
- 对于中文，1个字符占4个字节
- 对于数字和字母，1个字符实际占用大小是1个字节
-
select length(列名) from 表名查看这一列，每行的长度
int(5) ===> 4+1 ==》 1 为标识该列是否为空
char(2) ===> 4+4+1 ==》 1 为标识该列是否为空
varchar(2) ===> 4+4+1+1+1 ===> 1 为标识该列是否为空 其余两个1位开始位置和结束位置
联合索引
add index idx(a,b,c,d)
- 唯一值多的列放在左侧
- 只要我们将来的查询，所有的列都是等值查询，无关顺序排序
(a,b,c,d),(b,d,c,a),(a,b,d,c)……，因为优化器自动将我们查询条件进行排序了 - 不连续的部分条件
cda ==> a,c,d 只走a索引，因b没有了。建立联合索引的时候是按照a,b,c,d的顺序建立的===>优化建议，可以删除原来索引，在新建一个cda的索引。（在业务支持的情况下）
所有列相同的不同的索引会相互影响。
- 在where查询中如果出现不等值(>,<,>=,<=,like)
add index idx(a,b,c,d)
…… where a=1 and b>3 and c=2 and d=5 如果出现不等值查询，索引只会卡在b那，即走a和b的索引
优化建议:
1 .把不等值查询放在最后面（不过优化器会自动排序）
2 .更改联合索引的顺序，删了重新建（重点）
- 对于多子句的情况，应用联合索引
需要按照语句的执行顺序，建立联合索引
为什么要用联合索引，而不是单列索引。因为MySQL默认只会使用一种索引
Extra
额外执行的命令
重点： using filesort额外的排序 ，出现此语句，说明select排序的条件列中，没有合理的用到索引,涉及到排序的条件order by,group by,distinct,union
索引的应用规范
建立索引的原则（DBA的运维规范）
- 建表必须要有主键，一般是无关列，自增长
- 经常为where条件列 order by,group by,join on,distinct的条件
- 最好使用唯一值多的列，做为联合索引的最左列，其余的按照优化细节来做
- 列值较长的索引列，我们建议使用前缀索引
- 降低索引条目，一方面不要创建没用的索引，不常使用的索引进行清除，percona toolkit(xxx),
pt-duplicate-key-checker ---检查数据库重复索引 - 索引维护，要避开业务繁忙期
- 小表不建索引
不走索引的情况（开发规范）
- 没用查询条件，或者条件没用建立索引
select * from city,select * from city where 1=1 - 查询结果集是原表的绝大部分数据，应该是25%以上
- 索引本身失效，统计数据不真实
- 查询条件调用函数在索引列上，或者对索引列进行运算,运算包括（+,-,*,/,!）等
例desc select * from city where id-99=1 - 隐式转换导致索引失效
CREATE TABLE ttt(id INT,num CHAR(2))
INSERT INTO ttt VALUES(1,2),(2,'3');
ALTER TABLE ttt ADD INDEX idx(num)
DESC SELECT * FROM ttt WHERE num=2 //不走索引
DESC SELECT * FROM ttt WHERE num='2' //走索引
num=2 ,MySQL会通过函数将2 转换为字符串类型，根据上一条，索引列有函数，将不会走索引
- <>(相当于!),not ni 不走索引（辅助索引）
- like '%aa' ，百分号在前面时
- 联合索引 ???
联合索引，意外情况
将表中所有列建立联合索引，每个列作为查询条件都会走索引
网友评论