执行计划与 SQL 调优

调优的顺序永远是：定位慢 SQL → 看执行计划 → 理解优化器为什么这么选 → 改 SQL 或改索引 → 验证。 不要凭"经验"直接改。

一、EXPLAIN 逐列详解

EXPLAIN SELECT e.name, d.name FROM employee e
JOIN department d ON e.department_id = d.id WHERE e.salary > 10000;
列含义与关注点
id查询的序号。id 越大越先执行；id 相同则从上往下执行
select_typeSIMPLE / PRIMARY / SUBQUERY / DERIVED（派生表）/ MATERIALIZED（物化子查询）/ UNION
table涉及的表；<derivedN> 表示 id=N 的派生表
partitions命中的分区
type访问类型，最重要的指标之一，见下表
possible_keys可能用到的索引
key实际使用的索引，NULL 表示没用索引
key_len使用的索引长度（字节），可推断联合索引用到了第几列
ref与索引比较的对象：常量 const 或某个表的列
rows预估要扫描的行数（估算值，不是精确值）
filtered经 WHERE 过滤后剩余行的百分比。rows × filtered ≈ 送给下一张表的行数
Extra附加信息，见下表

type：从好到坏

system > const > eq_ref > ref > fulltext > ref_or_null
       > index_merge > range > index > ALL
type说明
const通过主键或唯一索引等值匹配，最多一行，直接当常量处理
eq_ref连接时，对左表每一行在右表通过主键/唯一索引精确匹配一行
ref通过非唯一索引等值匹配，可能返回多行
range索引范围扫描：>、BETWEEN、IN
index全索引扫描——扫的是整棵索引树，不是全表，但行数一样多
ALL全表扫描

底线要求：线上查询至少达到 range，核心接口应达到 ref 或 eq_ref。

Extra：关键取值

取值含义好坏
Using index覆盖索引，无需回表✅ 很好
Using index condition索引条件下推 ICP，减少了回表次数✅ 好
Using whereServer 层还要再过滤一次⚠️ 说明索引没能完全过滤
Using filesort需要额外排序（不一定用磁盘，内存排序也叫这个名字）❌ 尽量消除
Using temporary使用了内部临时表（常见于 GROUP BY、DISTINCT、UNION）❌ 重点优化
Using join buffer (hash join)走了哈希连接，通常意味着被驱动表没有可用索引⚠️ 关注
Using index for group-by松散索引扫描，GROUP BY 直接用索引完成✅ 很好
Impossible WHERE条件恒假，不用执行—

更强的工具

-- 8.0.18+：真正执行 SQL 并给出实际行数与耗时，用于验证 rows 估算是否准确
EXPLAIN ANALYZE SELECT ...;

-- JSON 格式：能看到 cost 估算值和更详细的过滤信息
EXPLAIN FORMAT=JSON SELECT ...;

-- 优化器决策全过程：为什么没选那个索引，答案在这里
SET optimizer_trace = 'enabled=on';
SELECT ...;
SELECT * FROM information_schema.optimizer_trace\G
SET optimizer_trace = 'enabled=off';

二、优化器如何选择

MySQL 是基于代价（cost-based）的优化器。代价 ≈ I/O 代价 + CPU 代价，数据来源是统计信息（索引基数 cardinality、表行数），而统计信息是采样估算的，因此可能出错。

索引选错时的处理顺序：

  1. ANALYZE TABLE t; —— 重新采样统计信息，大多数选错索引的问题到这一步就解决了；
  2. 检查是否有 8.0 的直方图能帮上忙：ANALYZE TABLE t UPDATE HISTOGRAM ON col WITH 32 BUCKETS;（适用于数据分布严重倾斜、且列上没有索引的情况）；
  3. 改写 SQL，让条件更容易命中索引；
  4. 最后才考虑索引提示（属于硬编码，表结构变化后可能变成负优化）：
    SELECT * FROM t FORCE INDEX (idx_a) WHERE ...;
SELECT /*+ JOIN_ORDER(a, b) */ ... ;   -- 8.0 优化器提示，比 FORCE INDEX 更细粒度

三、连接算法

算法版本说明
Nested-Loop Join (NLJ)一直有驱动表每行去被驱动表查一次。被驱动表的连接列有索引时用它，复杂度 O(n × log m)
Block Nested-Loop (BNL)一直有被驱动表无索引时，把驱动表批量放进 join buffer 再扫描被驱动表，减少扫描次数，但仍是 O(n × m) 次比较
Hash Join8.0.18+对较小的一侧建哈希表，再扫描另一侧探测。等值连接且无可用索引时，大幅优于 BNL，8.0.20 起已基本取代 BNL

小表驱动大表

NLJ 的代价 ≈ 驱动表行数 × 被驱动表单次查找代价。所以要让过滤后行数少的表做驱动表（不是原始表小，而是 WHERE 之后小）。

MySQL 优化器会自动选择驱动表（STRAIGHT_JOIN 可强制左表驱动），但前提是它对行数的估算准确。因此：

  1. 保证被驱动表的连接列有索引——这是让连接从 O(n×m) 降到 O(n×log m) 的唯一途径；
  2. 连接列的类型、字符集、排序规则必须一致，否则隐式转换会让索引失效；
  3. 尽早用 WHERE 缩小驱动表。

四、排序与分组优化

Using filesort 的两种执行方式：

  • 全字段排序：把 SELECT 需要的所有字段放进 sort_buffer 排序，排完直接返回；
  • rowid 排序：只把排序列 + 主键放进 buffer，排完再回表取其他字段。单行太长时采用，多了一次回表。（控制该阈值的 max_length_for_sort_data 在 8.0.20 起废弃、8.0.29 起移除，现由优化器自行判断。）

sort_buffer_size 放不下时会使用磁盘临时文件做归并排序，这是排序变慢的主因。

消除排序的正解是让索引天然有序：

-- 索引 (department_id, salary)
SELECT * FROM employee WHERE department_id = 3 ORDER BY salary;   -- ✅ 无 filesort
SELECT * FROM employee WHERE department_id = 3 ORDER BY hired_at; -- ❌ filesort

规则：ORDER BY 的列要能接在 WHERE 中等值条件列的后面，构成索引的连续前缀，且排序方向一致（或全部相反）。

GROUP BY 同理——8.0 已经取消了 GROUP BY 的隐式排序，若还出现 Using temporary; Using filesort，说明分组列没能用上索引。

五、深分页优化 ⭐⭐

-- ❌ 需要扫描并丢弃前 100 万行（每行还可能回表）
SELECT * FROM orders ORDER BY id LIMIT 1000000, 20;

方案一：延迟关联（覆盖索引 + 自连接）

SELECT o.* FROM orders o
JOIN (SELECT id FROM orders ORDER BY id LIMIT 1000000, 20) t ON o.id = t.id;

内层只扫索引（覆盖索引，不回表），拿到 20 个 id 后才回表取完整行，把 100 万次回表降到 20 次。

方案二：书签 / keyset 分页（最优）

-- 记住上一页最后一行的 id，下一页从它之后开始
SELECT * FROM orders WHERE id > 1000000 ORDER BY id LIMIT 20;

代价与页码无关，恒定 O(20)。缺点是只能顺序翻页，无法跳页，适合 App 的下拉加载。排序列非唯一时，用 (sort_col, id) 组合做游标：

SELECT * FROM orders
WHERE (created_at, id) < ('2024-01-01 10:00:00', 12345)
ORDER BY created_at DESC, id DESC LIMIT 20;

方案三：业务上限制最大页数（搜索引擎都这么干），或对深页改用 ES。

六、COUNT 优化

SELECT COUNT(*) FROM big_table;    -- InnoDB 必须实时统计，无法像 MyISAM 那样读缓存值

InnoDB 因为 MVCC 的存在，不同事务看到的行数可能不同，所以没有维护总行数。优化手段：

  1. 可接受估算值：用 EXPLAIN SELECT * FROM t 或 SHOW TABLE STATUS 的 rows（误差可达 40%+）；
  2. 精确值：单独维护计数表 / Redis 计数器，在事务内同步更新；
  3. 保证有一个小的二级索引可供扫描（InnoDB 会自动挑最小的那个）。

七、慢查询定位

-- 开启慢查询日志
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 0.1;                 -- 单位秒，可用小数
SET GLOBAL log_queries_not_using_indexes = ON;    -- 记录未走索引的查询（生产慎用，日志量大）

分析工具：mysqldumpslow（自带）、pt-query-digest（Percona Toolkit，更强）。

无需改配置的实时排查：

-- 当前正在执行的语句
SELECT * FROM information_schema.processlist WHERE command <> 'Sleep' ORDER BY time DESC;

-- performance_schema：按总耗时排名的 SQL 模板
SELECT * FROM sys.statement_analysis ORDER BY total_latency DESC LIMIT 10;
-- 全表扫描最多的语句
SELECT * FROM sys.statements_with_full_table_scans LIMIT 10;

八、SQL 编写规范清单

  1. 禁止 SELECT *：破坏覆盖索引、传输多余数据、字段变更时容易出错；
  2. WHERE 中不对索引列做运算和函数调用；
  3. 注意隐式类型转换，尤其是"数字型字符串"列；
  4. JOIN 的表数量控制在 3 张以内，被驱动表连接列必须有索引；
  5. 用 EXISTS 表达存在性判断，不要 JOIN + DISTINCT；
  6. UNION ALL 优先于 UNION，确认无重复时不要做无谓的去重；
  7. 批量操作要分批：一次 DELETE 百万行会造成长事务、大量 undo、主从延迟。改成 LIMIT 1000 循环删；
  8. 不要在事务中做 RPC / 文件 IO，事务越短越好；
  9. 分页深了用游标，不要 LIMIT 大偏移量；
  10. 改动前后都跑一次 EXPLAIN，用数据而不是感觉判断优化是否有效。

九、系统层面的优化方向

当 SQL 和索引都优化到位仍然扛不住时，按代价从低到高依次考虑：

手段说明
参数调优innodb_buffer_pool_size 设为物理内存的 50%~70%，这是收益最大的单个参数
加缓存Redis 挡住热点读，注意缓存一致性与击穿
读写分离一主多从，注意主从延迟导致的读旧数据
归档冷数据历史表按时间归档，让热表保持在可控体量
分区表单表逻辑拆分，分区键必须包含所有唯一索引/主键的列，跨分区查询反而更慢
分库分表最后手段。带来分布式事务、跨库 JOIN、全局唯一 ID、扩容迁移等一系列复杂度

详见表结构设计。

下一步 👉 表结构设计

上次更新:
贡献者: Joe