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