MySQL高级——索引失效的几种情况

上一篇介绍了索引的原理：MySQL高级——索引原理及使用_Lurenjia915的博客-CSDN博客

索引并不是建立成功就万事大吉了，在使用时稍不注意也会照成索引失效。本篇主要介绍索引在哪些情况会失效。

1、最左原则

使用联合索引时，查询的字段要从联合索引最左列开始，且中间不能断开。比如name、age、pos三个字段创建联合索引，查询使用的字段要从name开始依次使用，中间如果顺序断开则断开后的字段无法使用索引。可以理解成火车，火车头name必须有，中间如果没有age车厢dev车厢也无法使用。

有时字段排序不准循最左原则也可正常使用索引，并不是最左原则不准确，是因为MySQL内部的优化器自动将SQL进行优化，但是我们还是要保持良好编程习惯。

所有字段依次查询，索引使用成功

不是从首字段开始查询，索引使用失败

索引顺序断开，断开前的name字段索引使用成功，断开后的dev字段索引使用失败

2、不要在索引列上做任何操作

如果对索引字段做操作，比如计算、函数、类型转换等将会造成索引失效。结合最左原则，失效的索引列在中间，则后面索引都失效。

对索引字段name使用left函数计算，导致索引失效

3、范围查询会导致后面索引失效

对索引字段进行范围操作时，该字段不会失效，但该字段后面的字段都会失效。

age字段进行范围查询，name和age字段使用索引成功，pos字段索引失效

4、使用不等于（!=或者<>）

使用不等于会导致索引失效从而进行全表扫描，在业务允许的情况下不要使用不等于。

5、IS NULL和IS NOT NULL也无法使用索引

实际业务中关键字段上不要有null值，就算暂时无法赋值的也要给一个默认值。

6、LIKE时不要以通配符开头

模糊查询时如果%在左边也会造成索引失效。也很好理解，排序也是根据首字母开始排的，首字母都不确定自然没法排序。

一定要左边模糊时最好使用覆盖索引，此时索引不会失效。

7、字符串不加单引号导致索引失效

比如2000，加上单引号它是字符串不加单引号它是数字。但是此时查询也会查出正确的结果，因为MySQL进行了自动的类型转换。但前面讲过类型转换会造成索引失效，所以保持一个良好的编程习惯，使用字符串时一定要加单引号。

8、OR连接会造成所以失效

尽量不要用OR连接。

9、注意排序中的问题

一条SQL实际上可以分为三步：得到数据 -> 处理数据 -> 返回处理后的数据。根据where条件和统计信息生成执行计划，得到数据是第一步。order by和groud by等处理数据是第二步。第三步是返回处理后的数据。

当执行处理数据时，数据库会先查看第一步的执行计划，如果排序字段刚好在第一步使用的索引内且符合最左原则，那么可以利用索引顺序直接取得已经排好序的数据。所以在有where的情况下，排序操作只能根据where中使用的索引进行排序优化，就算where中没有使用索引排序也不能单独使用索引操作。如果没有where排序就可以直接使用索引进行操作，但是也要遵循最左原则

排序涉及多个字段时，各个字段排序的方向必须一样，否则索引失效。分组之前会先排序，所以group by也要遵循order by规则。having可以使用到索引，注意索引顺序。

补充：

在索引失效时，数据库有两种方法进行排序操作，分别是单路排序和双路排序：

双路排序：特点是进行两次I/O，第一次读取行指针和需要排序的那一列，在内存内进行排序。第二次根据排好序的行指针读取其他要显示的字段。缺点是I/O操作比较消耗性能，效率较慢。
单路排序：特点是只进行一次I/O，直接读取要显示的全部列，然后再内存中进行排序。缺点是比较占用内存。如果缓冲区不够大导致MySQL分多次读取，反而会增加好几次I/O操作。

排序相关参数：

sort_buffer_size：单路排序缓冲区的大小。
max_length_for_sort_data：切换排序方式的阈值，查询字段高于阈值就双路排序，反之单路排序。