MySQL 索引失效可能的原因

索引失效这个问题的前提应该是建立了索引，却没有使用到，或者没有完全使用到，下面列举了一些常见原因，面试中可能也会闻到。

 

• 原因一：复合索引没有遵守最左前缀

例如，建立复合索引（login_name, name, mobile），那么如果单独匹配 name 或 mobile （即 WHERE name = 'name' AND mobile = 'mobile'）索引是不会生效的。

 

复合索引必须遵守最左前缀，也就是按照复合索引创建的顺序，左边的列必须按顺序出现。

 

如果缺省了中间字段，比如：WHERE login_name = 'name' AND mobile = 'mobile'，那么复合索引将不会被充分使用，只会使用 login_name 部分的索引，并不是完全失效。

 

ps: 不过我工作中几乎没有遇到建立复合索引的场景，可能是复合索引的使用条件比较严格，建立的用处不大，业务场景没有这种需求。

 

 

• 原因二：在索引列上做了任何操作（计算、函数、类型转换）

例如，SELECT * FROM sys_user WHERE left(login_name, 4) = 'haha'，该SQL 使用了内置函数 left()，取出左 4 字符，从 explain 可以知道这个 SQL 语句将会以 ALL 的方式进行全表扫描

 

上面的 SQL 可以等价替换为 SELECT * FROM sys_user WHERE login_name LIKE 'haha%'，这样就可以利用到索引了。

 

禁止索引列任何计算；不过注意，只是禁止索引列进行计算，如果是对某一个值进行了计算是没有问题的，比如下面的例子：

mysql> explain select * from user where name like CONCAT(left('name', 1), '%');
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | user  | NULL       | range | idx_name      | idx_name | 767     | NULL |    6 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

 

 

• 原因三：出现范围条件，往后全部失效

 

 

 

• 原因四：没有充分利用覆盖索引

以下第一个 SQL 中 SELECT 子句只查询需要的字段，而且这个字段就是索引列，使用了覆盖索引；而第二个 SQL 中 SELECT 子句查询使用了 *，表示查询所有字段，效率低下。

 SELECT 子句禁止使用 * 查询所有字段。

经过测试验证：并不是使用 SELECT * 无法使用索引，当把所有列都选中建立联合索引，也是可以使用索引的，但这并没有意义。

 

 

• 原因五：使用了不等于（!= 或者 <>） 作为条件