跳至主要內容

SQL优化

本文从 SQL 编写和执行角度提供实用优化指南，与 索引（侧重索引原理与 B+Tree 结构）互为补充。

适用场景：慢查询优化、新 SQL 上线前的规范审查、数据库性能调优。使用 EXPLAIN 分析执行计划是 SQL 优化的第一步。

存储引擎选择

  • 没有特殊要求所有表必须使用 Innodb 存储引擎
  • 禁止使用存储过程、视图、触发器、Event
  • 优先选择符合存储需要的最小的数据类型
  • 建议把 BLOB 或是 TEXT 列分离到单独的扩展表中
  • 尽可能把所有列定义为 NOT NULL

索引优化

索引列建议

与 索引 的关系

索引.md 侧重 B+Tree 原理和索引分类等底层知识，本节侧重 SQL 编写时如何选择和使用索引。

  • 出现在 SELECT、UPDATE、DELETE 语句的 WHERE 从句中的列
  • 包含在 ORDER BY、GROUP BY、DISTINCT 中的字段
  • 多表 join 的关联列
  • 不要将符合上述条件的列都单独建索引，通常建立联合索引效果更好

索引列顺序

  • 区分度最高的放在联合索引的最左侧（区分度 = 列中不同值的数量 / 列的总行数）
  • 字段长度小的列放在联合索引的左侧（字段长度越小，一页能存储的数据量越大，IO 性能越好）
  • 使用最频繁的列放到联合索引的左侧

覆盖索引

覆盖索引是指包含了所有查询字段（where、select、order by、group by 包含的字段）的索引。

优势：避免二次查询和减少 IO 操作。由于覆盖索引的键值中可以直接获取所有数据，无需回表查询主键；同时索引按顺序存储，可将随机 IO 转换为顺序 IO。

索引失效场景

  1. 索引列上做计算、函数、类型转换
  2. 使用不等于（!= 或 <>）、is null、is not null（新版本 MySQL 已可使用索引）
  3. 使用 like 以通配符开头会导致全表扫描
  4. varchar 字段值为数字但没带引号，不会命中索引
  5. 使用 or 或 in，MySQL 内部优化器可能不走索引
  6. 隐式转换会导致索引失效：select name,phone from customer where id = '111'

充分利用已有索引

  • 避免使用双%号的查询条件（a like '%123%'），只有后置%可以用到索引
  • 一个 SQL 只能利用复合索引中的一列进行范围查询
  • 范围查询的列放到联合索引的右侧
  • 使用 left join 或 not exists 优化 not in 操作

查询优化

order by 优化

  • 如果排序字段创建了联合索引，遵循最左前缀法则
  • 如果文件排序无法避免，想办法使用覆盖索引

group by 优化

group by 的原理是先排序后分组，优化参考 order by。

分页优化

-- 通过覆盖索引 + join 优化深分页
SELECT * FROM dept a 
INNER JOIN (SELECT id FROM dept ORDER BY name LIMIT 10000, 10) b 
ON a.id = b.id;

join 优化

MySQL 内部优化器会根据关联字段是否创建索引来使用不同算法：

  • NLJ（嵌套循环算法）：关联字段有索引，对小表做全表扫描，用小表数据去大表做索引关联查询
  • BNLJ（块嵌套循环算法）：关联字段无索引，提供 join buffer 缓冲区，先把小表放缓冲区，然后全表扫描大表进行内存匹配

使用 join 查询时，一定要建立关联字段的索引，且两张表的关联字段类型、长度要一致，否则索引失效。

SQL 语句规约

强制规则

  1. 不要使用 count(列名) 或 count(常量) 替代 count(*)
  2. count(distinct column) 计算该列除 NULL 外的不重复数量
  3. 当某一列的值全是 NULL 时，count(col) 返回 0，但 sum(col) 返回 NULL
  4. 使用 ISNULL() 判断是否为 NULL 值
  5. 分页查询时，count 为 0 需返回空数组
  6. 不得使用外键与级联，所有外键概念必须在应用层解决
  7. 禁止使用存储过程
  8. 禁止使用 select *，使用具体字段查询
  9. INSERT 语句使用具体字段
  10. in 操作能避免则避免，数据量过大的 in 需结合业务优化

推荐规则

  1. 避免直接拼接 SQL
  2. BETWEEN 包含边界值
  3. 使用 exist 替代 in
  4. 避免使用 having，将条件移至 where 子句
  5. 使用 limit N 代替 limit N, M

Profile 分析

使用 Profile 分析 SQL 执行过程：

-- 查看 profile 是否开启
SHOW VARIABLES LIKE '%profiling%';

-- 开启 profile
SET profiling = 1;

-- 执行查询
SELECT * FROM table_name;

-- 查看分析列表
SHOW PROFILES;

-- 查看单条语句执行详情
SHOW PROFILE FOR QUERY 2;

执行状态

状态说明
starting开始执行
checking permissions检查权限
Opening tables打开表
optimizing优化查询
statistics统计信息
creating tmp table创建临时表
executing执行中
Sending data发送数据
Sorting result排序结果

重点关注 Sending data 和 Sorting result 阶段，这两个阶段耗时最长。

慢查询优化流程

  1. 开启慢查询日志
  2. 使用 EXPLAIN 分析执行计划
  3. 检查 type 列，避免全表扫描
  4. 关注 Using filesort 和 Using temporary
  5. 使用 Profile 定位具体瓶颈