深入浅出MySQL索引学习

MySQL 5.6版本之前和之后查询数据区别

在 MySQL 5.6 之前，只能从 ID3 开始一个个回表。到主键索引上找出数据行，再对比字段值。
而 MySQL 5.6 引入的索引下推优化（index condition pushdown)， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

删除数据后重建索引操作

删除数据后需要重建索引，语句：alter table T engine=InnoDB
重建索引 k 的做法是合理的，可以达到省空间的目的。但是，重建主键的过程不合理。
不论是删除主键还是创建主键，都会将整个表重建。

alter table T engine=InnoDB解释一下原理

重建表的流程

1. 创建一个临时表,扫描主键索引得到行数据,根据数据页生成B+树,存储到临时文件中(此时页中数据变的紧凑,不会存在空洞)
2. 新数据写入原表的同时记录到row_log中(这样保证拷贝过程数据不丢失)
3. 临时文件生成完之后,在将row_log操作到临时文件中,这样两者的数据则一致了
4. 最后临时文件替换原表的数据文件
   整个过程是online,获取MDL写锁->降级为MDL读锁->DDL->升级为MDL写锁->释放MDL锁

between和in性能区别

1. select * from T where k in(1,2,3,4,5),需要树搜索5次
   select * from T where k between 1 and 5,只需要树搜索1次

总结

1. 覆盖索引：如果查询条件使用的是普通索引（或是联合索引的最左原则字段），查询结果是联合索引的字段或是主键，不用回表操作，直接返回结果，减少IO磁盘读写读取正行数据
2. 最左前缀：联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符
3. 联合索引：根据创建联合索引的顺序，以最左原则进行where检索，
   比如（age，name）以age=1 或 age= 1 and name=‘张三’可以使用索引，
   单以name=‘张三’ 不会使用索引，考虑到存储空间的问题，还请根据业务需求，将查找频繁的数据进行靠左创建索引。
4. 索引下推：like 'hello%’ and age>10 检索，MySQL5.6版本之前，会对匹配的数据进行回表查询。
5. 5.6版本后，会先过滤掉age<10的数据，再进行回表查询，减少回表率，提升检索速度
6. 组合索引只要查询的信息是组合索引的信息，那么不用最左原则也能匹配到