MySQL 更好的语句 - LD's BLOG

本文将介绍如何写出更好的 SQL 语句。

一、避免索引失效

1. 避免联合索引失效

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

(1) 全值匹配

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

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

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

(2) 最左前缀法则

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

-- 索引失效
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 的自动类型转换将其转换为数字，从而导致缓存失效。

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

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

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

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

回表查询：先定位主键值，再根据主键值查询具体的行数据的现象

覆盖索引：查询列被索引列包含的查询

5. 避免使用 or

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

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

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

-- 索引生效
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 也不会全部使用，而是从中选择一个最优索引使用。

二、ORDER BY 优化

1. 避免 FileSort

具体请看：

MySQL order by语句一、什么是 filesort ？

尽量使用索引排序，在这种情况下，MySQL 可以将根据索引查到的记录直接返回，避免额外的 FileSort 排序，

2. FileSort 的优化

在某些情况下，FileSort 无法避免，此时也应该对 FileSort 进行优化。

FileSort 有两种算法：
- 两次扫描算法：
  - 需要两次数据读取
  - 首先获取排序字段和行指针信息，排序；排序完成后，根据行指针回表读取完整记录
- 一次扫描算法：
  - 内存开销大
  - 一次性取出所有字段，排序，排序后直接返回结果
FileSort 进行排序时，在排序区 sort buffer 中排序，当 sort buffer 不够时，会利用临时表 temporary table 进行外排序