关于MySQL索引的一些理解

最近在看mysql索引优化方面的知识，查阅了一些资料，加上自己的实践，得到了一些结论分享给大家

这里可以通过explain语句来查看mysql的执行计划来判断，查询语句是否命中索引，来进行sql的优化；

mysql中文官方文档介绍如下：

https://www.mysqlzh.com/doc/66/292.html

最左匹配原则

简单来讲：最左匹配原则就是，从联合索引的最左侧开始匹配，当满足了最左侧的查询结果，再进行下一个字段的查询匹配，直到所有的字段匹配结束。

从mysql底层数据结构的角度来讲，mysql默认引擎innodb的情况下，可以知道innodb用的都是B+树作为索引的数据结构，索引还可分为聚簇索引和非聚簇索引；

聚簇索引

特点：

首行节点存储索引列值，叶子节点则存放所有数据；

innodb默认将主键作为聚簇索引列，若无主键，则默认添加一个6字节的rowid作为隐藏主键；

结构图如下

首行节点有序排列，其中叶子节点之间相连的为顺序指针，用来提高范围查询的查询速度，这里推荐大家看一下《高性能MySQL》，有更细的讲解；

非聚簇索引

特点：

非聚簇索引，非叶子节点存放索引列的值，叶子节点则存放的是主键和指向数据的指针；

结构图下如：

非聚簇索引，其实就是其他的非主键索引，非主键索引树，按非主键顺序有序递归建立索引树，按索引的顺序从左到有，这里保证的数据整体的排列，便于减少二分查找的时间，这也是为什么索引的使用要符合最左匹配原则的原因；

最左匹配原则的测试：

准备工作

创建表test                                                                    

索引如下

id为主键索引，联合索引(a,b,c)

索引与查询

select *  from test where  a=5 and b=6 and c=5；

select * From test where a=5 and b=6;

 select * From test where a=5;

从上面的执行计划，来看，这里只要过滤条件按照 联合索引的顺序去使用，总是会用到索引的，但是下面这条语句没有按照顺序，也用到了索引，这是因为mysql内置了查询优化器，对执行进行了修改； 借鉴MySQL官方文档原话就是

“查询优化器的任务是发现执行SQL查询的最佳方案。因为“好”方案和“坏”方案之间的性能差别会巨大(也就是说，秒相对于小时或甚至天)，大多数查询优化器，包括MySQL的查询优化器，总或多或少地在所有可能的查询评估方案中搜索最佳方案。”

select * From test where b=6 and a=5 

范围查询

 select * From test where a>5 and b=6 and c=5 

select * From test where a in(5,9) and b=6 and c=5 

 select * From test where a in(5,9) and b in(6,5) and c in (5,12)

从结论上看，都用到了索引，但是范围查询，只会用到第一个范围查询的索引，后面的就用不到了，但是看过《高性能MySQL》的小伙伴就知道，可以用in代替范围查询，让后面的索引全部命中，缺点：in列表不可太长；

关联查询与索引命中

 

驱动表的选择

这里，id相同的情况下，排第一行的为驱动表；

通常情况下，left join 一般左边为驱动表，right join一般右边为驱动表

而inner join则由结果集进行判定，mysql会对每个可作为驱动表的表做个结果记录预估，预估每个表的返回记录，结果集小的则作为驱动表；

 关联查询的过滤，on和where列都可使用到索引，对关联查询的最终结果集进行排序和分组时，只有驱动表的索引才会生效，且列须为同一表的索引列；

索引与排序

排序与查询同样会用到最左匹配原则

select * From test    ORDER BY a,b,c

select * From test    ORDER BY a,c,b

这里排序列不符合最左匹配原则，extra列显示usring filesort，文件排序的含义是排序的过程将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序。这种情况下一 般也是要考虑使用索引来优化的。

select * From test where a=5 ORDER BY b,c;

这里同样可以用到索引，只要where条件列与排序列满足最左匹配原则即可，其实从mysql语句的执行顺序就可以推理出来，where在order by之前执行，而条件列有序的情况下，后面的排序列同样是有序的，因此可以用到索引；