咱们上篇文章简略剖析了下 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=utf8insert 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 和架构