MySQL 避免索引失效

本文将介绍如何使索引生效，确保它能够真正提升 MySQL 的性能。

一、查看索引是否生效

最简单的方式是使用 EXPLAIN 查看执行计划，通过 type 属性查看它是否使用了索引。

二、避免索引失效

1. 避免联合索引失效

假设创建了联合索引 name, age, address

(1) 全值匹配

如果索引中每个列都有对应的 “等值” 查询条件，无论顺序如何，索引都会生效且执行效率最高

1
2
3
4
5
-- 索引完整生效
select * from sys_user where name='小明' and age='11' and address='北京';

-- 索引完整生效
select * from sys_user where age='11' and address='北京' and name='小明';

(2) 最左前缀法则

如果查询条件未具体对应到索引中每个列，则应该遵循最左前缀原则，即查询条件应该从索引的最左列开始，并且不能跳过索引中的列。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 索引失效
select * from sys_user where age='11' and address='北京';

-- 索引失效
select * from sys_user where address='北京' and age='11';

-- 索引第一列生效
select * from sys_user where name='小明';

-- 索引第一列生效
select * from sys_user where name='小明' and address='北京';

-- 索引第一列、第二列生效
select * from sys_user where name='小明' and age='11';

2. 避免在索引列上进行运算操作

如果在索引列上进行运算操作，则索引将会失效。

1
2
-- 索引失效
select * from tb_seller where substring(name, 3, 2)='小米科技' and status='1' and address='北京市';

3. 字符串应该使用单引号包裹

在实际开发中，我们可能会使用 uuid 作为表的主键，如果使用纯数字的 uuid 并且将数据库中主键类型设为 varchar，此时便会出现数据类型为字符串的纯数字。

此时，在查询时应该注意为 uuid 值手动添加单引号包裹，以避免 MySQL 的自动类型转换将其转换为数字，从而导致缓存失效。

1
2
3
4
5
-- 索引失效
select * from sys_user where id=21312152134124;

-- 索引生效
select * from sys_user where id='21312152134124';

4. 避免查询索引以外的字段

尽量避免使用 select *，尽量避免查询除索引以外的其它字段，因为此时即使索引生效也需要回表查询其它字段。

  • 回表查询：先定位主键值，再根据主键值查询具体的行数据的现象
  • 覆盖索引：查询列被索引列包含的查询

5. 避免使用 or

大多数情况下，使用 or 会导致索引失效。

6. “前模糊查询” 会导致索引失效

如果进行 “前模糊查询” 或 “前后模糊查询” 将会导致缓存失效。

1
2
3
4
5
6
7
8
9
10
11
-- 索引生效
select * from sys_user where name like '小明%' and age='11' and address='北京';

-- 索引失效
select * from sys_user where name like '%小明' and age='11' and address='北京';

-- 索引失效
select * from sys_user where name like '%小明%' and age='11' and address='北京';

-- 索引第一列、第二列生效
select * from sys_user where name='小明' and age='11' and address like '%北京';

7. 避免使用 not in

索引列 not in (···) 将会导致索引失效；

相反，索引列 in (···) 索引仍会生效。

8. 尽量使用复合索引

联合索引 name, age, address 相当于三个索引：

  • name
  • name, age
  • name, age, address

并且，使用单列索引时，即使三个索引都能够使用，MySQL 也不会全部使用，而是从中选择一个最优索引使用。

三、说明

1. 索引不一定更快

在执行 SQL 语句之前，MySQL 会首先分析并指定执行计划。某些情况下，使用索引可能导致查询更慢，因此，某些时候 MySQL 会放弃使用索引。

参考