MySQL 索引失效常见的几种情况

我们上篇文章简单分析了下InnoDB行锁，文中有提及索引失效时，行锁会升级为表锁，今天我们这篇文章来聊一聊常见的索引失效的几种情况：

还是和往常一样，我们先建一张表：

CREATE TABLE `user_info` (
  `id` int(11) DEFAULT NULL auto_increment,
  `name` varchar(255) DEFAULT NULL,
  `nick` varchar(255) DEFAULT NULL,
  `sex` char(2),
  `score` int(3),
  KEY `idx_id` (`id`),
  KEY `idx_sex` (`sex`),
  KEY `idx_name_score_nick` (`name`, `score`, `nick`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

insert into user_info values (1, '杨过', 'yangguo', '1', 90);
insert into user_info values (2, '小龙女', 'xiaolongnv', '0', 88);
insert into user_info values (3, '黄药师', 'huangyaoshi', '1', 75);
insert into user_info values (4, '郭襄', 'guoxiang', '0', 66);
insert into user_info values (5, '何足道', 'hezudao', '1', 50);
insert into user_info values (6, '独孤求败', 'duguqiubai', '1', 55);
insert into user_info values (7, '方东白', 'fangdongbai', '1', 88);
insert into user_info values (8, '赵敏', 'zhaomin', '0', 75);
insert into user_info values (9, '程灵素', 'chenglingsu','0', 66);

MySQL [wakka]> select * from user_info;
+----+--------------+-------------+------+-------+
| id | name         | nick        | sex  | score |
+----+--------------+-------------+------+-------+
|  1 | 杨过         | yangguo     | 1    |    90 |
|  2 | 小龙女       | xiaolongnv  | 0    |    88 |
|  3 | 黄药师       | huangyaoshi | 1    |    75 |
|  4 | 郭襄         | guoxiang    | 0    |    66 |
|  5 | 何足道       | hezudao     | 1    |    50 |
|  6 | 独孤求败     | duguqiubai  | 1    |    55 |
|  7 | 方东白       | fangdongbai | 1    |    88 |
|  8 | 赵敏         | zhaomin     | 0    |    75 |
|  9 | 程灵素       | chenglingsu | 0    |    66 |
+----+--------------+-------------+------+-------+
9 rows in set (0.00 sec)

索引失效的第一种常见情况：对字段进行运算，比如对name字段截取操作

MySQL [wakka]> explain select * from user_info where name = '程灵素'; #name字段原本有索引
+----+-------------+-----------+------------+------+---------------------+---------------------+---------+-------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys       | key                 | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------------+---------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | user_info | NULL       | ref  | idx_name_score_nick | idx_name_score_nick | 768     | const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+---------------------+---------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

MySQL [wakka]> explain select * from user_info where substr(name, 2, 2) = '灵素'; #对name字段进行运算
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user_info | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    9 |   100.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

索引失效的第二种常见情况：范围查询右边的列不能够使用索引

MySQL [wakka]> explain select * from user_info where name = '郭襄' and score = 66 and nick = 'guoxiang'; #索引用到了name,score,nick三个字段
+----+-------------+-----------+------------+------+---------------------+---------------------+---------+-------------------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys       | key                 | key_len | ref               | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------------+---------------------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | user_info | NULL       | ref  | idx_name_score_nick | idx_name_score_nick | 1541    | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+---------------------+---------------------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

MySQL [wakka]> explain select * from user_info where name = '郭襄' and score < 80 and nick = 'guoxiang'; #索引用到了name,score两个字段，nick没用到索引，范围查询之后的字段用不到索引
+----+-------------+-----------+------------+-------+---------------------+---------------------+---------+------+------+----------+-----------------------+
| id | select_type | table     | partitions | type  | possible_keys       | key                 | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-----------+------------+-------+---------------------+---------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | user_info | NULL       | range | idx_name_score_nick | idx_name_score_nick | 773     | NULL |    1 |    11.11 | Using index condition |
+----+-------------+-----------+------------+-------+---------------------+---------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

MySQL [wakka]> explain select * from user_info where name = '郭襄' and score = 66;
+----+-------------+-----------+------------+------+---------------------+---------------------+---------+-------------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys       | key                 | key_len | ref         | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------------+---------------------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | user_info | NULL       | ref  | idx_name_score_nick | idx_name_score_nick | 773     | const,const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+---------------------+---------------------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

#我们可以看到第二个SQL的key_len和第三个SQL的key len一样长度

索引失效的第三种常见情况：数值型字符字段没有用引号

MySQL [wakka]> explain select * from user_info where sex = '0';
+----+-------------+-----------+------------+------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | user_info | NULL       | ref  | idx_sex       | idx_sex | 7       | const |    4 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

MySQL [wakka]> explain select * from user_info where sex = 0; #这样做，相当于字段做了一层隐式类型转换，前面我们第一点说过，如果字段进行运算，则索引还小
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user_info | NULL       | ALL  | idx_sex       | NULL | NULL    | NULL |    9 |    11.11 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 3 warnings (0.00 sec)

索引失效的第四种常见情况：or分隔的条件，如果or前面的列有索引，or后面的列没索引，则涉及的索引都失效

MySQL [wakka]> select * from user_info where name = '方东白' or nick = 'xiaolongnv';
+----+-----------+-------------+------+-------+
| id | name      | nick        | sex  | score |
+----+-----------+-------------+------+-------+
|  2 | 小龙女    | xiaolongnv  | 0    |    88 |
|  7 | 方东白    | fangdongbai | 1    |    88 |
+----+-----------+-------------+------+-------+
2 rows in set (0.00 sec)

MySQL [wakka]> explain select * from user_info where name = '方东白' or nick = 'xiaolongnv';
+----+-------------+-----------+------------+------+---------------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys       | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user_info | NULL       | ALL  | idx_name_score_nick | NULL | NULL    | NULL |    9 |    20.99 | Using where |
+----+-------------+-----------+------------+------+---------------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

索引失效的第五种常见情况：like %开头

MySQL [wakka]> explain select * from user_info where name like '%白';
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user_info | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    9 |    11.11 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

相反，如果like 后缀%的话，是可以用到索引的

MySQL [wakka]> explain select * from user_info where name like '杨%';
+----+-------------+-----------+------------+-------+---------------------+---------------------+---------+------+------+----------+-----------------------+
| id | select_type | table     | partitions | type  | possible_keys       | key                 | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-----------+------------+-------+---------------------+---------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | user_info | NULL       | range | idx_name_score_nick | idx_name_score_nick | 768     | NULL |    1 |   100.00 | Using index condition |
+----+-------------+-----------+------------+-------+---------------------+---------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

---

如果喜欢我的文章，欢迎关注我的公众号：“码农漫谈”，专注Web开发，后台开发，DB和架构