MySQL的最左前缀原则和联合索引失效的情况

来自
复合(联合)索引失效解析

从索引的底层结构看待最左前缀原则

对于复合索引（多列b+tree，使用多列值组合而成的b+tree索引）。遵循最左侧原则，从左到右的使用索引中的字段，一个查询可以只使用索引中的一部份，但只能是最左侧部分。例如索引是key index (a,b,c). 可以支持a a,b a,b,c 3种组合进行查找，但不支持 b,c进行查找。当使用最左侧字段时，索引就十分有效。

 create table myTest(
         a int,
         b int,
         c int,
         KEY a(a,b,c)
);

比如(a,b,c)的时候，b+数是按照从左到右的顺序来建立搜索树的，

比如当(a=? and b=? and c=?)这样的数据来检索的时候，b+树会优先比较a列来确定下一步的所搜方向，如果a列相同再依次比较b列和c列，最后得到检索的数据；

但当(b=? and c=?)这样的没有a列的数据来的时候，b+树就不知道下一步该查哪个节点，因为建立搜索树的时候a列就是第一个比较因子，必须要先根据a列来搜索才能知道下一步去哪里查询。

比如当(a=? and c=?)这样的数据来检索时，b+树可以用a列来指定搜索方向，但下一个字段b列的缺失，所以只能把a列的数据找到，然后再匹配c列的数据了， 这个是非常重要的性质，即索引的最左匹配特性。

利用索引中的附加列，您可以缩小搜索的范围，但使用一个具有两列的索引不同于使用两个单独的索引。复合索引的结构与电话簿类似，人名由姓和名构成，电话簿首先按姓氏对进行排序，然后按名字对有相同姓氏的人进行排序。如果您知道姓，电话簿将非常有用；如果您知道姓和名，电话簿则更为有用，但如果您只知道名不姓，电话簿将没有用处。

所以说创建复合索引时，应该仔细考虑列的顺序。对索引中的所有列执行搜索或仅对前几列执行搜索时，复合索引非常有用；仅对后面的任意列执行搜索时，复合索引则没有用处。

一、多列索引在and查询中应用

1、select a,b,c from test where a=? and b=? and c=?；---- abc顺序
查询效率最高，索引全覆盖。

select a,b,c from test where b=? and a=? and c=?；
where里面的条件顺序在查询之前会被mysql自动优化，效果跟上一句一样

2、select a,b,c from test where a=? and b=?；
索引覆盖a和b。
select a,b,c from test where a=? and c=?；
a用到索引，b,c都没有用到

3、select a,b,c from test where b=? and a=?；
经过mysql的查询分析器的优化，索引覆盖a和b。

4、select a,b,c from test where a=?；
索引覆盖a。

5、select a,b,c from test where b=? and c=?；
没有a列，不走索引，索引失效。

6、select a,b,c from test where c=?；
没有a列，不走索引，索引失效。

二、多列索引在范围查询中应用

select * from test where a=? and b between ? and ? and c=?；
索引覆盖a和b，因b列是范围查询，断点，阻塞了c的索引，因此c列不能走索引。

select * from test where a between ? and ? and b=?；
a列走索引，因a列是范围查询，因此b列是无法使用索引。

select * from test where a between ? and ? and b between ? and ? and c=?；
a列走索引，因a列是范围查询，b列是范围查询也不能使用索引。

三、多列索引在排序中应用

select * from test where a=? and b=? order by c；
a、b、c三列全覆盖索引，查询效率最高。

select * from test where a=? and b between ? and ? order by c；
a、b列使用索引查找，因b列是范围查询，断点，阻塞了c的索引，因此c列不能使用索引，会出现file sort。

四，总结

联合索引的使用在写where条件的顺序无关，mysql查询分析会进行优化而使用索引。但是减轻查询分析器的压力，最好和索引的从左到右的顺序一致。

使用等值查询，多列同时查询，索引会一直传递并生效。因此等值查询效率最好。

索引查找遵循最左侧原则。但是遇到范围查询列之后的列索引失效。

排序也能使用索引，合理使用索引排序，避免出现file sort。

索引失效的情况

• 不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描

• 存储引擎不能使用索引范围条件右边的列

• 尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select *

• mysql在使用不等于（！=或者<>）的时候无法使用索引会导致全表扫描

• is null,is not null也无法使用索引 ---- 此处存在疑问，经测试确实可以使用，ref和const等级，并不是all

• like以通配符开头（’%abc…’）mysql索引失效会变成全表扫描的操作。问题：解决like‘%字符串%’时索引不被使用的方法？

• 字符串不加单引号索引失效

一般性建议

• 对于单键索引，尽量选择针对当前query过滤性更好的索引
• 在选择组合索引的时候，当前Query中过滤性最好的字段在索引字段顺序中，位置越靠前越好。
• 在选择组合索引的时候，尽量选择可以能够包含当前query中的where子句中更多字段的索引
• 尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的

建立索引的原则

1，对于经常存取的列避免建立索引。

2，尝试建立索引来帮助特定的查询。检查自己的sql语句，为那些频繁在where子句中出现的字段建立索引。

3，尝试建立复合索引来进一步提高系统性能。修改复合索引将消耗更长时间，同时，复合索引也占磁盘空间。

4，对于小型的表，建立索引可能会影响性能

5，选择对区分度高的字段建立索引

6，避免选择大型数据类型的列作为索引

一条SQL语句句执⾏行行得很慢的原因有哪些？

分以下两种情况讨论。

1. ⼤大多数情况是正常的，只是偶尔会出现很慢的情况。

2. 在数据量量不不变的情况下，这条SQL语句句⼀一直以来都执⾏行行的很慢

3. 偶尔很慢的情况
   1.1 数据库在刷新脏⻚

当我们要往数据库插⼊入⼀一条数据、或者要更更新⼀一条数据的时候，我们知道数据库会在内存中把对应字段
的数据更更新了了，但是更更新之后，这些更更新的字段并不不会⻢马上同步持久化到磁盘中去，⽽而是把这些更更新的
记录写⼊入到 redo log ⽇日记中去，等到空闲的时候，在通过 redo log ⾥里里的⽇日记把最新的数据同步到磁盘
中去。
不不过， redo log ⾥里里的容量量是有限的，如果数据库⼀一直很忙，更更新⼜又很频繁，这个时候 redo log 很快就
会被写满了了，这个时候就没办法等到空闲的时候再把数据同步到磁盘的，只能暂停其他操作，全身⼼心来
把数据同步到磁盘中去的，⽽而这个时候， 就会导致我们平时正常的SQL语句句突然执⾏行行的很慢，所以说，
数据库在在同步数据到磁盘的时候，就有可能导致我们的SQL语句句执⾏行行的很慢了了。

1.2 其他人对数据库加锁了

执⾏这行的这条语句句，刚好这条语句句涉及到的表，别⼈人在⽤用，并且加锁了了，
我们拿不不到锁，只能慢慢等待别⼈人释放锁了了。或者，表没有加锁，但要使⽤用到的某个⼀一⾏行行被加锁了了，这
个时候，我也没办法啊。
如果要判断是否真的在等待锁，我们可以⽤用 show processlist这个命令来查看当前的状态

2. 一直很慢
   2.1 没⽤用到索引

例如该字段没有索引；
或者由于对字段进⾏行行运算、函数操作导致⽆无法⽤用索引。

2.1 数据库选错了了索引