索引失效这个问题的前提应该是建立了索引,却没有使用到,或者没有完全使用到,下面列举了一些常见原因,面试中可能也会闻到。
- 原因一:复合索引没有遵守最左前缀
例如,建立复合索引(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 * 无法使用索引,当把所有列都选中建立联合索引,也是可以使用索引的,但这并没有意义。
-
原因五:使用了不等于(!= 或者 <>) 作为条件