本篇主要介绍MySQL中索引使用的相关内容。

目录

一、最左前缀法则

二、索引失效的场景

索引列运算

字符串无引号

模糊查询

or连接条件

数据分布

---

一、最左前缀法则

当我们在使用多个字段构成的索引时（联合索引），需要考虑最左前缀法则，最左前缀法则指的是，在使用联合索引来查询时，需要在查询条件里包含联合索引的所有字段，如果跳过了某个字段，那么该字段后面的其它字段（联合索引中的字段）的索引将全部失效。

例如，这里有一张表，表结构如下：

然后我们根据其中的sn，name，price三个字段建立联合索引 

此时，我们再通过explain来查看一下查询条件三个字段都包含的SQL语句的执行情况：

可以发现此时索引长度为1208.

接下来我们去掉最左边的sn字段

可以发现此时一个索引都不走了，因为最左前缀法则，使得sn以及后面的name，prcie字段的索引都失效了，从而整个联合索引都失效，致使该查询SQL不再走索引。我们再来跳过name字段看一下 可以发现索引长度只有402，那是因为跳过了name字段，使得name字段和price的索引都失效。

需要注意的是，如果我们其中一个字段，进行了范围查询，例如”<"，也会导致该字段被跳过，此时我们需要使用 " <= "才不会使字段被跳过。因此，在使用联合索引时，如果要范围查询，尽量使用“<=" 或者 ">="。

综上所述，我们在使用联合索引时，应当遵从最左前缀法则，以免索引失效影响我们查询的性能。

二、索引失效的场景

在MySQL中，即使我们创建了索引，但在查询时不走索引，而是继续全表扫描，像这种情况，就称为索引失效，前面，联合索引跳过字段就是一种索引失效的场景。在MySQL中，很多场景下都有可能会出现索引失效的情况，下面我们来具体了解一下。

索引列运算

当我们在使用单列索引时（对一个字段创建的索引），如果我们对该列进行函数运，那么索引将会失效。例如，我们对weight字段创建一个单列索引：

如果我们对weight字段进行等值查询，通过explain可以发现成功走了索引 ：

但如果我们在查询时使用了函数运算，通过explain可以发现并没有走索引，索引失效了。

字符串无引号

当我们在对一个字符串类型的字段通过索引进行查询时，如果字段的值未数值且没有加引号，索引将失效。

例如这里有一个字符串类型的字段name，给该字段添加索引。

 

然后我们通过索引来进行一下查询，查询时字段值加引号 可以发现此次查询走了索引。

我们再来看一下不加引号的情况(值为数值） 可以发现并未走索引，索引失效了 。

模糊查询

在对字符串类型的字段通过索引来查询时，如果使用了模糊匹配，且在模糊匹配的字符串前加了‘ % ’号则索引失效。例如我们还是使用前面的name字段，然后使用模糊匹配进行查询，分别在中间和末尾加上”%"号

可以发现这两次都走了索引。接下来我们再来看一下"%"号在前面的情况：

 结果可以发现索引失效了，但通常情况下，我们需要在最前面加"%"号，但我们又想走索引，那怎么办呢？我们可以使用覆盖索引来解决，使用覆盖索引在前面加 ” %"号索引就不会失效了。（覆盖索引在后面会详细介绍）。

or连接条件

在查询中使用了or关键字时，如果or前面的字段有索引，而or后面的字段没有使用索引，那么or前面的和or后面的字段的索引将全部失效。

例如，我们通过explain来查看一下下面这条SQL, 可以发现由于or后面的age字段没有添加索引，导致前面的name字段的索引失效了，接下来，我们给age加上索引

可以发现此时索引生效了，因此可以得出结论只有or前面的字段和后面的字段都有索引时，索引才会生效。

数据分布

在查询过程中，如果查询的结果的行数和整张表的行数接近，那么MySQL就会认为走索引不如全表扫描效率高（因为此时索引查的次数和全表查询接近了，且索引还需要进行回表等操作，因此此时索引的效率就不如全表扫描了），因此就不走索引，而是进行全部扫描，从而导致索引失效。

例如我们来用explian看一下下面这条SQL 

可以发现并没有走索引，这是因为表中大部分记录的name字段都不为空，系统认为全表扫描可能效率更高，从而使索引失效了。

然后我们再来看一下下面这条SQL：

可以发现这次走了索引，这是因为表中的记录基本都不为空，因此索引只需要查几次就能完成查询而全表扫描需要遍历整张表，因此系统选择了更快的走索引的方式。