关于mysql: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 和架构