MySql复合索引失效的几种情况

<>建表

<>插入数据

<>建立复合索引

或者

<>查询插入的索引

<>执行测试Sql

<>测试数据1


这里常规查询，分别查询了当使用复合索引中的部分字段当查询条件时的索引使用情况，通过explain执行计划结果显示，以上四种情况全部都使用到了索引，ref属性代表有几个常量，还可以看出key_len在增加，代表索引检索的长度在增加，key_len越小效率越高，所以在相同查询结果的前提下避免使用多个条件拼接。这里沾出来key_len也是方便观察后续的测试数据，用来判断哪些字段使用了索引。

<>测试数据2


打乱查询条件的顺序，不按照复合索引的创建顺序拼接，执行计划结果与顺序拼接的结果一致，不会失效。实际上mysql底层会进行自我优化，优化成索引中的顺序。所以在日常使用时虽然不按照索引顺序拼写条件的结果是一样的，但是还是建议应该和创建索引的顺序保持一致，这样可以省去mysql内部自我优化的时间。

<>测试数据3


当c3字段拼接上范围条件的时候，执行计划结果显示key_len=414,通过对比测试1的数据，可以得出这条sql只有3个字段使用了索引，c4索引失效，ref=null
,这是因为c3使用了范围查询，c3字段只能使用索引排序不能使用索引查询，导致索引中断,c4索引失效。

<>测试数据4


查询结果显示使用呢了索引，并且key_len=552，4个字段使用了索引。根据mysql最佳左前缀法则，索引的使用会根据创建复合索引的字段顺序依次索引。以上sql虽然c4条件拼接更靠左，但是mysql会进行自我优化重新排序，索引的使用顺序依然是c1,c2,c3,c4,但是由于c4最后使用了范围查询，所以ref=null

<>测试数据5


结果显示使用了索引，但是key_len=276，只有两个字段使用了两个索引分别是c1,c2。c3,c4索引失效。这个是因为c3在这里只使用了索引排序并没有使用索引查询,造成索引中断，所以c4也使用不了索引。这里与上面的测试结果不同的是虽然这里c3只使用了索引排序但是并没有被explain统计到所以显示的只有2个字段使用了索引。那么怎么知道的c3也使用了索引？，那是因为Extra显示仍然只有using
where,并没有显示using filesort,接下来跟测试6的结果一对比就知道了。

<>测试数据6

仍然还是只有c1,c2使用了索引，这次排序的条件换成了c4,由于c3条件的缺失，造成索引中断，c4索引失效，Extra显示using where
using filesort,并没有使用索引排序。

<>测试数据7

结果显示使用了索引但只有c1使用了，而Extra没有出现using filesort
说明c2,c3只使用了索引排序。(这里的c5只是做一个干扰实则不影响索引的使用)

<>测试数据8

将order by 后的顺序交换，结果显示与测试7结果基本一样，但是出现了Extra using
filesort,说明并没有使用索引排序，效率降低。这还是因为mysql会根据你建立的复合索引的顺序来依次执行，那么这里直接c3在前，造成了索引中断。所以日常使用时一定要注意使用复合索引时的字段顺序，虽然查询结果一样，但是效率确不一样了。

<>测试数据9

这里又加了一个c2条件之后，using filesort
消失了，这是因为条件里面优先使用了c2这个字段，那么接下来的顺序刚好就是c3了，索引没有造成中断，而这时c2是一个常量并不会影响排序效率。

<>测试数据10

这种情况下结果显示索引只使用到了一个c1,c4索引失效，是因为c2,c3只使用了索引排序，造成索引中断，无法传递。

<>结束。

<>欢迎大家补充纠正，共同学习，努力进步！！！

• « 上一篇：nginx配置阿里云/腾讯云申请的免费SSL证书（nginx配置https）
• » 下一篇：计算机网络 - linux - 配置静态ip地址（NAT模式）