索引的简介

类似于一本书的目录，起到优化查询的内容

索引的分类

• 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' ，百分号在前面时
• 联合索引 ???

联合索引，意外情况

将表中所有列建立联合索引，每个列作为查询条件都会走索引