MySQL 索引失效常见的几种情况 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和架构