EXPLAIN与查询优化

一条在线上跑了三个月没问题的 SQL，为什么突然开始拖垮数据库？本章建立一套完整的"发现慢→定位慢→验证优化"工作流：慢查询日志与 pt-query-digest 负责发现，EXPLAIN 与 EXPLAIN ANALYZE 负责定位和验证，成本模型与统计信息帮你理解优化器为什么"不听话"。覆盖 EXPLAIN 逐字段精讲、key_len 计算、JOIN/排序/分页/count 的专项优化，以及用 EXPLAIN 复核索引失效的实战方法。

前置阅读：索引的机理（B+ 树、最左前缀、回表代价）在 04-索引原理与实战.md，本章不重复讲"为什么索引会失效"，而是讲怎么用 EXPLAIN 把失效发现和验证出来。

1. 慢查询工作流：先定义"慢"，再分层选工具

1.1 什么叫慢

没有业务基线的"慢"是伪命题。经验做法：

• 以接口视角定标准：比如"订单列表页 P99 < 50ms"，那么单条 SQL 的预算通常只有 10~20ms（要给网络、序列化、多次查询留余量）。
• long_query_time 的阈值不等于"业务上的慢"。默认 10s 是审计口径，不是性能口径；生产上常见设 0.5s 甚至 0.1s，配合采样开关控制日志量。
• 一条 SQL 平均 5ms 但每秒执行 1 万次，也是性能事故——平均值正常不代表总耗时不重要，这要靠第 2 节的聚合工具看总耗时占比，不是看单条耗时。

1.2 工具链分层

顺序不能反：先看全局再逐条优化。凭感觉挑一条 SQL 优化，往往花了半天把最慢的一条从 3s 优化到 800ms，而真正压垮数据库的是另外 20 条各跑 200ms、每秒执行几百次的高频 SQL。

2. 慢查询日志与 pt-query-digest

2.1 开启与参数

-- 动态开启（my.cnf 中配置同名参数持久化）
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 0.5;          -- 单位秒，支持小数
SET GLOBAL log_output = 'FILE';            -- 'FILE' 或 'TABLE'（或 'FILE,TABLE'）
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

各参数要点：

• long_query_time 可精确到微秒。慢日志里每条记录还带 Query_time 与 Lock_time 两个独立字段（后者是等待锁的时间），两者口径的历史演进有过调整，排障时结合看两个数，不要死抠"等待时间算不算进阈值"这一条——以你自己的压测复现结果为准。
• log_output=TABLE 时慢日志写入 mysql.slow_log 表，方便 SQL 方式聚合（SELECT query_time, sql_text FROM mysql.slow_log ORDER BY query_time DESC），但这张表是 CSV 引擎、写入不能走索引，量大了会很慢，适合临时排查不适合长期开。
• log_queries_not_using_indexes：慎用。它会把"全表扫小表"这种完全无害的查询（配置表、字典表可能就 20 行）也记下来，日志噪音大到淹没真问题。折中方案是同时设置：

SET GLOBAL log_queries_not_using_indexes = ON;
SET GLOBAL min_examined_row_limit = 10000;  -- 扫描行数不足 1 万的不记录

• 慢日志里 Rows_examined 是判断"扫描/返回比"的关键：返回 20 行却扫描 50 万行，就是典型的低效查询。

2.2 pt-query-digest 报告怎么读

pt-query-digest /var/log/mysql/slow.log > report.txt

报告头部给全局画像（总查询数、总耗时、时间分布直方图），下面按**指纹（fingerprint，参数归一化后的 SQL 模板）**分组，每组给：出现次数、单次平均/中位耗时、总耗时占比（Rank）。读报告的顺序：

1. 只看排序第一的 3~5 项，它们的 [total time] 占比之和通常已超 80%——这就是"优化谁"的答案。
2. 对每组看 Query 1:23. 1.5k rows Examined 这类行，Rows examined / Rows sent 比值大于 1000:1 的，优先怀疑索引缺失或深分页。
3. 同一指纹出现两种形态（比如 IN 列表一个很长一个很短），说明 SQL 模板不稳定，需要先从应用层收敛。

pt-query-digest 是 Percona Toolkit 的第三方工具，不在 MySQL 发行版内，需单独安装 percona-toolkit。不想装外部工具时，performance_schema.events_statements_summary_by_digest 按 SUM_TIMER_WAIT 排序是等价替代品（口径略有差异，且受 performance_schema 上限淘汰策略影响）。

3. EXPLAIN 逐字段精讲

EXPLAIN SELECT o.id, o.status FROM orders o WHERE o.user_id = 42 ORDER BY o.created_at DESC LIMIT 20;

以下逐个字段讲透。

3.1 id

id 表示 SELECT 子句的编号，规律（经典格式下）：

• id 相同：属于同一执行层级，按从上到下（书写顺序）大致理解为执行顺序，具体连接次序以优化器和 FORMAT=TREE 为准。
• id 不同：id 越大越先执行——先算子查询，再外层用它。比如 WHERE 里的非相关子查询会被拆成独立 id。
• id 为 NULL：出现在 UNION 中，表示对前面若干 id 的结果做合并（UNION RESULT）。
• 相关子查询（内层引用了外层列）：select_type 是 DEPENDENT SUBQUERY，内层 id 通常与外层相同或需结合 UNION 一起看，规律在不同 MySQL 版本上呈现有差异，不要在文档/代码注释里把这个编号当强承诺。UNION 中第二个 SELECT 标 UNION、后续合并行标 UNION RESULT，加了 deduplicated / materialized 修饰词表示去重或物化方式。

3.2 select_type 常见值

看到 DEPENDENT SUBQUERY 就要警觉：它意味着子查询被执行 N 遍。改写为 JOIN 或让优化器做半连接（semi-join，5.6+ 会自动尝试）通常是第一板斧。

3.3 type：访问类型的等级阶梯

从好到坏排一条阶梯，生产上出现 range 及更差档位的高频 SQL 都要审：

两个易混点：index 不等于 range，前者没有过滤条件、扫的行数≈全表行数（只是扫的是更窄的索引而非聚簇记录）；range 也不是万能钥匙，WHERE id IN (...) 和 WHERE created_at > x 都是 range，但前者每段可做 index dive 估得很准，后者可能估偏（见第 5 节）。

3.4 key 与 possible_keys

• possible_keys：候选索引清单（优化器"考虑过"）。
• key：实际选中的。为 NULL 即没用索引。
• possible_keys 有值但 key 为 NULL：优化器算下来用索引更贵（回表太多/选择性太差），或统计信息失真。先 ANALYZE TABLE 更新统计再看，仍不用则参考第 5 节成本模型判断是不是"真不该用"。
• key 显示 <primaryKey> 之类或 UNIQUE_SUBQUERY 是特殊标记，含义见官方手册的 show_explain 一节。

3.5 key_len：到底用了几列、多少字节

key_len 是"本次执行实际用到的索引前缀字节数"，反推它能确认联合索引用到了第几列。计算规则：

key_len = Σ(用到的每列的宽度) + 索引中可空列每个 +1 字节（NULL 标记）
        + 用到的变长列每个再加 2 字节长度前缀（EXPLAIN 口径一律按 2 算）

字符集每字符字节数（算列宽时按字符集峰值字节数）：

三道联合索引例题（自己先算，再看答案）：

题 1：users 表 INDEX idx_name (name)，name VARCHAR(50) CHARACTER SET utf8mb4 NOT NULL，查询 WHERE name = '张三'，key_len = ？

答案：50×4 + 2（长度前缀）= 202；若 name 可空再加 1，为 203。注意：有些速查资料把 InnoDB 磁盘记录格式里"变长列最大字节 ≤255 时长度表用 1 字节"的规则套到 key_len 上、算出 201，这是张冠李戴——EXPLAIN 的 key_len 按列的最大可能字节数 + 2 口径计算，与磁盘实际存 1 还是 2 字节无关。官方手册自己的例子（author VARCHAR(20) NULL utf8 计 63 = 20×3+2+1）就是 +2；实测印证：单列表 utf8mb4 VARCHAR(20) key_len=82、VARCHAR(63)（252 字节，磁盘格式该用 1 字节前缀的临界内侧）key_len=254，一律 +2。

题 2：orders 表 INDEX idx_user_created (user_id INT NOT NULL, created_at DATETIME NOT NULL)。① WHERE user_id = 42；② WHERE user_id = 42 AND created_at > '2026-10-01'。两次 key_len 各是？

答案：① 只用到第一列，4 = 4；② 两列都用到，4 + 5 = 9。key_len 从 4 变 9，说明范围条件确实吃进了索引第二列。若 created_at 可空，则第二列另加 1（注意 NULL 标记只给可空列，非末列同样要加）。

题 3：products 表 INDEX idx_cat_name (category_id INT NOT NULL, name VARCHAR(30) CHARACTER SET utf8mb4 NULL)。WHERE category_id = 7 与 WHERE category_id = 7 AND name LIKE 'Pro%' 的 key_len？

答案：前者 4；后者 4 + (30×4 + 2 长度前缀 + 1 可空) = 4 + 123 = 127。LIKE ‘Pro%’ 是前缀范围，能继续用第二列。若写成 LIKE '%Pro%'，第二列用不上，key_len 回到 4——这是验证"最左前缀断在哪一列"最直接的手段。

3.6 rows 与 filtered

• rows：估算的"该访问方式预计要检查的行数"，不是精确值，更不是结果行数。InnoDB 靠采样统计页（见 5.2），数据倾斜时能偏差几倍。
• filtered：估算经 where 过滤后剩余行的百分比。rows × filtered% 大致是传给上一层/客户端的行数。
• 判断"优化器有没有被统计信息骗"，核心动作就是拿 EXPLAIN ANALYZE 的实际 rows 与这里的估算 rows 对比（第 4 节）。

3.7 Extra 高频项清单

4. FORMAT=TREE 与 EXPLAIN ANALYZE：估算 vs 真实

4.1 树形视图

经典 EXPLAIN 是一堆行，连接次序、物化关系要靠脑补；FORMAT=TREE（8.0.16+）把执行计划画成树，且带 cost 与行数估算：

EXPLAIN FORMAT=TREE
SELECT o.id, u.name FROM orders o JOIN users u ON u.id = o.user_id
WHERE o.status = 'PAID' ORDER BY o.created_at DESC LIMIT 20;

-> Limit: 20 row(s)
    -> Sort: o.created_at DESC, limit input to 20 row(s) per chunk
        -> Stream results
            -> Inner join on u.id = o.user_id
                -> Index lookup on o using idx_status (status='PAID')  (cost=8213.40 rows=24180)
                -> Single-row index lookup on u using PRIMARY (id=o.user_id)  (cost=7254.05 rows=24180)

逐字段读法：每个节点括注 cost=（累计估算成本，无单位、只用于比较）与 rows=（估算行数）。缩进越深越先执行（子节点先产出数据）。上面这个计划的问题是 status='PAID' 命中 2.4 万行再排序取 20——如果建 (status, created_at) 联合索引，计划会变成"沿索引反向扫、取满 20 行即停"，Sort 节点直接消失。对比两棵树的形状比看 cost 数字更有价值。

4.2 EXPLAIN ANALYZE：真实执行

EXPLAIN ANALYZE（8.0.18+）会真的把 SQL 跑完，在每个节点标注 (actual time=X..Y rows=N loops=M)：

• actual time 单位毫秒，X..Y 是首行到末行的耗时区间。
• rows 是实际产出行数——和估算 rows 对比，就是诊断统计信息失真的铁证。
• loops 是该节点被执行的次数（嵌套循环里被驱动侧 loops=驱动侧行数）。真实总耗时 ≈ Y×loops，别只看单节点数字。

注意 两个红线：其一，它会真实执行，SELECT 之外别对 UPDATE/DELETE/INSERT 用 EXPLAIN ANALYZE（语句会真改数据）；其二，大查询在高峰期跑 ANALYZE 等于给线上再压一遍同样的负载。只在预发/从库/低峰用。另外它与 ANALYZE TABLE（更新统计信息的语句）名字像但完全无关，别混。

4.3 命名撞车提醒

本章出现两个 Analyze：EXPLAIN ANALYZE＝真执行拿实测行数；ANALYZE TABLE orders;＝重新采样索引统计信息。5.2 节会用到后者。

5. 优化器为什么"不选你建的索引"

索引在那里，优化器不用——这不是 bug，是成本模型算出来的结论。要让它听话，得先理解它怎么算账。

5.1 成本模型：IO + CPU 的抽象计价

优化器把每种访问方式的代价折算成 cost 数字再比大小，主要成本项（SEcost/server_costing_parameters 可调）：

SHOW VARIABLES LIKE '%block_read_cost%';
-- memory_block_read_cost = 0.25   内存页读一次
-- io_block_read_cost     = 1.0    磁盘页读一次

粗略地说：走二级索引的代价 ≈ 命中的索引条目数（排序、扫叶子）+ 回表次数（每次回表是聚簇索引上的一次独立 B+ 树查找）；全表扫的代价 ≈ 聚簇索引的页数（顺序读）。所以当范围条件命中超过约总行数 20%~30%（经验值，数据在缓冲池比例高时阈值更低）时，回表成本超过顺序扫全表，优化器放弃索引是正确决策。典型例证：

-- is_deleted 选择性 99%（绝大多数行都是 0），单列索引基本不会被选中
EXPLAIN SELECT * FROM users WHERE is_deleted = 0;
-- key=NULL, type=ALL：不是索引白建了，是这列就不配建单列索引

真正要区分的是两种情况：优化器算得对（选择性差、回表多），还是优化器算错了（统计信息失真）。后者见下。

5.2 统计信息采样偏差

InnoDB 对每张表维护总行数估算、对每个索引维护"不同键前缀数"等采样值（持久化在内部表 mysql.innodb_table_stats / mysql.innodb_index_stats，后者每索引一行 n_diff_pfx* 记录），来源是 innodb_stats_persistent 持久化统计，默认只采样 20 个页：

SHOW VARIABLES LIKE 'innodb_stats_persistent_sample_pages';  -- 默认 20

20 页对 5GB 的 orders 表意味着极小样本，数据倾斜（比如某个 user_id 有 100 万条订单）时估算可以偏出一个数量级。修正手段按代价从小到大：

ANALYZE TABLE orders;                       -- 重新采样（8.0.28 起不再刷新表定义缓存，抖动很小；更早版本有短暂抖动，注意窗口）
SET innodb_stats_persistent_sample_pages = 100;  -- 提高采样页数：更准，但 ANALYZE 更慢
ANALYZE TABLE orders;                       -- 调完参数再采样

批量导入大改数据后、以及每天凌晨对核心大表跑一次 ANALYZE TABLE，是很多团队的例行动作（注意 innodb_stats_auto_recalc 开启时，表变更超过约 10% 行会后台自动重算，多数场景够用，不必手动刷）。

5.3 eq_range_index_dive_limit：IN 列表的两种估算

等值范围（IN/多个 OR =）的估行，优化器分两种做法：

• 范围段数 < eq_range_index_dive_limit（默认 200）：逐段做 index dive（真实下探 B+ 树数行），估得准但费 CPU；
• 段数 ≥ 200：改用统计信息估算，防止 1 万个值的 IN 把优化器 CPU 耗光——但统计信息本身有采样偏差，于是出现"IN 300 个值反而选错计划"的经典现象。

排查口诀：同一条 SQL，IN (少值) 走 range、IN (多值) 退化 ALL，先想这个阈值。确认手段是对比两种写法的 rows 估算，必要时把大 IN 拆分或临时调低阈值压测对比。

5.4 直方图：给无索引列补数据分布

8.0+ 支持列值直方图，专治"没索引、但有明显分布倾斜"的列（估算过滤率用）：

ANALYZE TABLE orders UPDATE HISTOGRAM ON status, amount WITH 32 BUCKETS;   -- 上限 1024
SELECT column_name, histogram FROM information_schema.column_statistics;   -- JSON，含每桶端点与累积频率
-- 删除：ANALYZE TABLE orders DROP HISTOGRAM ON status;

两个限制要记牢：数据变了直方图不会自动更新（需要重跑，可挂定时任务）；同一列有索引统计时直方图不参与（有索引就用索引统计）。直方图救的是 WHERE status='REFUNDED' 这类选择性极差但确实需要估准的过滤列，让优化器在"走不走某个其他索引"的决策上不跑偏。

5.5 optimizer_trace：抓一次优化决策的完整心路

想知道优化器为什么选了 A 不选 B，开 trace 看它的候选清单和逐项 cost：

SET optimizer_trace = 'enabled=on';
SELECT /* 目标 SQL */ o.id FROM orders o WHERE o.user_id = 42 ORDER BY o.created_at DESC LIMIT 20;
SELECT trace FROM information_schema.optimizer_trace\G
SET optimizer_trace = 'enabled=off';

输出 JSON 很长，只盯这几处关键行：

"rows_after_filtering": 137,
"considered_execution_plans": [
  { "plan_prefix": [], "table": "`o`",
    "best_access_path": { "considered_access_paths": [
      { "access_type": "ref",   "index": "idx_user_id",  "rows": 137, "cost": 52.1 },
      { "access_type": "scan",  "rows": 5184213, "cost": 528340.2 }
    ]},
    ...

trace 里能看到所有被评估并淘汰的方案（包括各候选索引、排序代价、是否 filesort）。线上排障一次只看一条 SQL 的 trace，别全局常开。trace 是性能_schema 之外唯一"白盒"入口，也是本章把 5.1~5.4 串起来的验证工具。

6. JOIN 优化

6.1 驱动表选择的两条原则

1. 小结果集做驱动表：不是"表小"，而是"经 where/索引过滤后产出行数少"的那一侧。驱动表每产出一行，被驱动表就要做一次查找，所以驱动侧的行数被当作"乘数"。
2. 被驱动表的 join 列必须有索引：N 行驱动 × 被驱动表一次 B+ 树 O(logN) 查找 vs N 次全表扫，天壤之别。这条是 JOIN 优化的第一检查项。

-- 好：过滤后只有 20 行的 orders 做驱动，users 主键 eq_ref 点查 20 次
EXPLAIN SELECT u.name FROM orders o JOIN users u ON u.id = o.user_id
WHERE o.status = 'PAID' AND o.created_at > '2026-10-19' LIMIT 20;
-- 期望：o 侧 ref/range，u 侧 type=eq_ref, key=PRIMARY

优化器会自己按成本选驱动表；只有统计失真把它带偏时才用 STRAIGHT_JOIN 或 /*+ JOIN_ORDER(o,u) */ 强制（第 12 节）。

6.2 嵌套循环家族三代

MySQL 的等值 JOIN 本质都是嵌套循环，差别在被驱动侧"内层循环"怎么省：

hash join 比 BNL 快一个量级，但它只是"止痛药"：内存不够时仍需分桶落盘（Grace 形态），成本依旧显著高于 INL。看到 hash join 的处方仍然是：给被驱动表 join 列建索引。

6.3 join 列字符集/排序规则不一致的代价

orders.user_id 是 utf8mb4、users.id 是 utf8，或两者 collation 不同（utf8mb4_general_ci vs utf8mb4_0900_ai_ci），后果是 join 列上索引失效（被驱动侧退化为扫描/hash join），甚至直接报错 1267（Illegal mix of collations）。建表期就把全库字符集排序规则统一（本手册基线建议 utf8mb4 + 统一 collation），详见 03-数据类型与建表.md。这是 EXPLAIN 里"明明两边都有索引却不用"的高频真因。

6.4 子查询改 JOIN：何时有效何时更差

• IN (SELECT ...) 非相关子查询：5.6+ 优化器会自动做半连接（semi-join）转成 join 形态，手写成 JOIN 常常没有收益，若原语义需要去重而改写后丢了 DISTINCT，还可能出错。
• WHERE EXISTS (相关子查询)：改 JOIN 通常有效，尤其是老版本/优化器没做物化时。
• FROM (子查询) t 派生表：8.0 支持派生表合并与条件下推，很多情况能自动展平；但子查询里带 LIMIT、复杂聚合时会被物化成临时表，此时改写成 JOIN 或拆两步才见效。
• 结论：先 EXPLAIN 看它有没有自动改写，没改写再动手改 SQL。用 FORMAT=TREE 确认形状变化，用 ANALYZE 确认实际 rows 没有变差。

7. ORDER BY 与 GROUP BY

7.1 filesort：单遍还是双遍

需要排序且索引给不了顺序时，MySQL 在 sort_buffer_size（默认 256KB，按排序操作分配、一次查询里多个排序就有多份）内存中排序，放不下走磁盘归并（Extra: Using filesort）。排序策略有两种形态：一次装入全部列（单遍），或只装排序列+主键指针、排完再回表取整行（双遍）。传统上由行宽与 max_length_for_sort_data（默认 4096）决定走哪种；8.0 后实现细节有调整（对含大字段列的处理更激进地走"排完再取"路径），该参数是否仍严格决定路径在 8.4 上表述要谨慎——实践上只要记住：SELECT * + 大字段 + 排序 = 排序成本显著上涨，能只选必要列就只选必要列。

优化优先级：索引顺序 > 减少排序行数（先过滤再排）> 减少排序列宽 > 调 sort_buffer_size（谨慎全局调，会话级临时调即可，它按排序操作分配，全局调大容易被并发查询放大成内存事故）。

7.2 用联合索引直接消灭 filesort（设计法）

排序走向"Using index for order by / 无 filesort"的模式是索引设计的一部分（机理见 04，这里给验证视角）：

-- 索引 (user_id, created_at)
WHERE user_id = 42 ORDER BY created_at DESC   -- 索引内 created_at 已按 user_id 分段有序，直接倒序读，无 filesort
-- 索引 (status, created_at)
ORDER BY status, created_at                    -- 前缀+后缀都沿索引，无 filesort
ORDER BY status ASC, created_at DESC           -- 方向混合：普通索引给不了，8.0 降序索引（DESC 列）才能消灭 filesort
WHERE user_id = 42 ORDER BY amount             -- amount 不在索引里，必然 filesort

验证时看两点：经典格式 Extra 里 Using filesort 消失；FORMAT=TREE 里 Sort: 节点消失（变成 Index lookup ... ; order by ... 形态或直接 Limit 截断）。

7.3 GROUP BY 与临时表

GROUP BY 的实现要么靠索引天然有序直接分组（Extra 出现 Using index for group-by 是理想态，松散索引扫描：无聚合函数或仅 MIN/MAX、分组列是索引前缀时才可能命中），要么建临时表（Using temporary）再排序返回。Using temporary; Using filesort 组合出现在高频 SQL 上，通常是聚合查询没有按"分组列在前、聚合排序列在后"设计索引。

注意：8.0 取消隐式排序（从 5.7 升级的最大行为坑之一） 5.7 及以前，GROUP BY city 会隐式按 city 排序输出；8.0 起不再保证。下面这个写法在 5.7 里"碰巧"有序，升 8.0 后顺序随机：

SELECT city, COUNT(*) FROM users GROUP BY city;          -- 8.0: 顺序不保证！
SELECT city, COUNT(*) FROM users GROUP BY city ORDER BY city;  -- 正确：显式 ORDER BY

升级前全量扫描代码里的 GROUP BY，把需要的顺序补成显式 ORDER BY。ORDER BY ... ASC 旧版可省略方向、以及"GROUP BY 后还能引用别名"等细节变化一并见 13-常见陷阱与最佳实践.md。

8. 分页与深分页

LIMIT 1000000, 20 的真实代价：按当前顺序扫过并丢弃 100 万行，再取 20 行返回。EXPLAIN 的 rows 会诚实地写约一百万——offset 是逐行计费的。三种标准解法：

方案一：延迟关联（覆盖索引先在索引上翻完丢弃段）

-- 内层子查询只碰二级索引（Extra: Using index），不回表扫宽行；外层按 20 个主键点查
SELECT o.*
FROM orders o
JOIN (SELECT id FROM orders WHERE user_id = 42 ORDER BY id LIMIT 1000000, 20) t
  ON o.id = t.id;

把"100 万次回表"降为"100 万次索引顺序扫 + 20 次回表"。翻页请求仍随页码线性变慢，只是常数小很多。

方案二：游标式 seek（推荐用于"下一页"）

-- 前端只带上一页最后一行的 id，不传 offset
SELECT * FROM orders
WHERE user_id = 42 AND id > 20451008   -- 上一页末行游标
ORDER BY id LIMIT 20;                   -- 无论"第几页"都是恒定开销，走索引直接定位

代价是失去"跳转到第 N 页"的能力，适合无限滚动/上下页场景。

方案三：限制最大页码 + 引导用筛选代替翻页：搜索引擎"只放前 100 页"的思路；配合业务侧强制时间范围/状态筛选把候选集先缩小。

三者共同前提：排序键稳定（ORDER BY 的列要有确定性，建议补唯一列做 tie-breaker，否则翻页会出现重复/漏行）。

9. count 家族

9.1 InnoDB 为什么不维护实时行数

MVCC 下每个事务看到的行数不同：一个事务在插入未提交时，它自己数得到这行、别的数不到——不存在一个"全局正确"的计数器可维护。所以 InnoDB 不缓存总行数，SELECT COUNT(*) FROM orders 必须扫某个索引数一遍。机理展开见 06-MVCC.md，这里只给选型结论。

9.2 三种写法的等价性

SELECT COUNT(*)  FROM orders;   -- 语义：数"行数"，NULL 也数。优化器选最小的可用索引扫
SELECT COUNT(1)  FROM orders;   -- 与 COUNT(*) 完全等价（常量非 NULL，逐行返回常数），无性能差异
SELECT COUNT(id) FROM orders;   -- id 非空时结果相同，但可能强制扫 id 所在索引（主键宽）而非更窄的二级索引，不占优
SELECT COUNT(price) FROM order_items;  -- 不等价：跳过 NULL，语义是"非 NULL 计数"

官方口径（8.0 手册）明确 COUNT(*)、COUNT(1)、COUNT(NON NULL列) 优化等价；COUNT(*) 不返回列值、不检查 NULL，是标准写法。结论：统一写 COUNT(*)。有 WHERE 时按索引选择性正常走索引，与无 WHERE 的"选最窄索引"是两条规则，别混。

9.3 大表计数的取舍

• 精确计数：只能真扫。5000 万行的 orders 全表 count 在从库跑可以接受（指 10-主从复制与高可用.md 的读写分离），在主库高频跑则是事故源。
• 估算：EXPLAIN SELECT * FROM orders 的 rows（即统计信息）或 information_schema.TABLES.TABLE_ROWS，同源、误差可到数倍，适合"页面显示约 xx 万条"。
• 业务化：计数表（写订单的同时 UPDATE shop_stat SET order_num=order_num+1，事务保证一致）或缓存层预聚合（Redis 计数，见 12-分库分表.md 的统计方案讨论）。先问产品"这个数需要精确吗"，多数答案是不需要。

10. IN / OR 与批量操作

10.1 IN 列表长度与执行行为

IN 本质是多个等值 range，命中索引时按序做 index dive 后归并。3.5/5.3 节两个结论合起来用：200 个值以内估算精度好、超过后切统计口径可能跑偏。工程约束另有两条：IN 列表受 max_allowed_packet 间接限制（超长 SQL 直接报错），且应用层拼接有注入与执行计划缓存抖动问题——批量值走临时表 JOIN 是更稳的写法。

10.2 OR 改 UNION ALL 的条件

WHERE indexed_a = ? OR indexed_b = ?：同表两个不同列各有索引时，优化器通常用 index_merge（union）自动合并；但 index_merge 的历史实现偶发选错、成本估算粗糙时也可能退化成全表扫。手动改写：

(SELECT * FROM orders WHERE user_id = 42)
UNION ALL
(SELECT * FROM orders WHERE order_no = 'N20261020001' AND user_id <> 42)  -- 补互斥条件防止重复行
ORDER BY created_at DESC;

条件清单：① 两个谓词各自有可用索引；② 必须人工保证不重复（补 AND NOT(另一条件)），否则 UNION ALL 会输出重复行；③ 各自需要单独排序时外层 ORDER BY 仍要 filesort，收益要实测。不满足就不如信任优化器（index_merge 可用时）。

10.3 批量 UPDATE 分批模板

一把 UPDATE orders SET status=3 WHERE status=1 命中 300 万行：长事务、巨量 undo、行锁范围大到阻塞业务、binlog 单事务巨物把从库打成复制延迟（锁机理指 05-事务与锁.md，binlog 影响指 07-日志体系.md）。标准分批：

-- 应用层循环执行，每轮小事务；affected_rows=0 即退出
START TRANSACTION;
UPDATE orders SET status = 3
WHERE status = 1 AND id <= 50000000
ORDER BY id LIMIT 1000;        -- UPDATE 不支持 ORDER BY+LIMIT 的组合在部分版本/语法下受限，稳妥写法：先 SELECT id 再按主键更新（见下注）
COMMIT;
DO SLEEP(0.2);                  -- 给从库追赶与业务流量让路

更稳的两步式（避免对 UPDATE+LIMIT 语义的依赖）：

SELECT id FROM orders WHERE status=1 ORDER BY id LIMIT 1000;   -- 取一批主键
UPDATE orders SET status=3 WHERE id IN (/* 上一批 1000 个 */);  -- 按主键小范围更新
-- 循环 + 每轮间 sleep；监控 Seconds_Behind_Source 动态调节节奏

每批 1000 左右、批间 100~300ms 是常见起步值，核心目标是单事务锁持有时间 < 1s 与从库延迟不爬升。

11. 索引失效的 EXPLAIN 复核

04-索引原理与实战.md 讲清了每个失效场景的机理；本节只练一个动作：如何用 EXPLAIN 的前后对照证明它确实失效了、以及改写后确实恢复了。六个高频场景（表结构：users(name VARCHAR(50), phone VARCHAR(20), city VARCHAR(30), age INT, created_at DATETIME)，索引 idx_name(name)、idx_city_age(city, age)、idx_created(created_at)）：

-- 场景 1：最左前缀断了
EXPLAIN SELECT * FROM users WHERE age = 30;
-- key=NULL type=ALL。改写 WHERE city='Hangzhou' AND age=30 → key=idx_city_age, key_len=127（30×4 + 2 长度前缀 + 1 可空 + 4）

-- 场景 2：前导通配符
EXPLAIN SELECT * FROM users WHERE name LIKE '%张%';
-- key=NULL type=ALL。改前缀 '张%' → key=idx_name type=range
-- 业务必需模糊搜索：全文索引/ngram（03 章）或外部检索引擎，不要在 LIKE 上硬撑

-- 场景 3：列上套函数
EXPLAIN SELECT * FROM users WHERE DATE(created_at) = '2026-10-20';
-- key=NULL。改区间：created_at >= '2026-10-20' AND created_at < '2026-10-21' → key=idx_created type=range

-- 场景 4：隐式类型转换（phone 是 varchar，传入数字）
EXPLAIN SELECT * FROM users WHERE phone = 13800138000;
-- key=NULL type=ALL：varchar 列 = 数字按数字比较，索引列侧被转换。改 '13800138000' → key=idx_phone
-- 反向不成立：数字列=字符串仍可用索引（转换发生在常量侧），但别留这种地雷

-- 场景 5：ORDER BY 列不在索引路径里
EXPLAIN SELECT id FROM users WHERE city='Hangzhou' ORDER BY created_at;
-- key=idx_city_age, Extra 含 Using filesort。改索引 (city, created_at) 后 filesort 消失（第 7.2 节）

-- 场景 6：NOT / != / IS NULL 选择性差
EXPLAIN SELECT * FROM users WHERE city != 'Hangzhou';
-- 多数情况 key=NULL type=ALL：!= 命中 99% 的行，走索引回表是亏的（第 5.1 节成本模型）——不是索引失效，是不值得用

两个验证工具：

-- 隐形索引：把线上可疑索引"逻辑下架"观察计划，比直接 DROP 安全得多（8.0+）
ALTER TABLE users ALTER INDEX idx_city_age INVISIBLE;
EXPLAIN SELECT ...;              -- 观察没有它之后计划是否反而更好/不变
ALTER TABLE users ALTER INDEX idx_city_age VISIBLE;   -- 随时恢复
-- 长期无人使用的候选索引，确认后 DROP（回滚只需重建索引，但大表重建成本见 09 章）

possible_keys 非空而 key 为空 → 先查选择性（第 5.1 节）与统计（第 5.2 节）；possible_keys 为空 → 才是真正的"失效"（本节的 1~4 场景）。这个分叉是复核时第一眼看的东西。

12. 其他技巧

12.1 覆盖改写验证

优化高频查询的最后一步经常是"把索引改覆盖"：SELECT 只取需要的列，联合索引把这几列补齐（尾列 INCLUDE 思路在 MySQL 里就是多放几列进索引）。验证标准动作：Extra 出现 Using index，FORMAT=TREE 节点写 Index lookup ... (covering) 形态、且不再有回表相关的 rows 放大。代价：索引变宽、写放大增加——只给高频读、低写压力的核心链路做。

12.2 优化器提示（hint）

-- 旧式索引提示：作用于单个表引用
SELECT * FROM orders FORCE INDEX (idx_status) WHERE status = 'PAID';   -- FORCE：不用也要用
SELECT * FROM orders IGNORE INDEX (idx_created) ORDER BY created_at;

-- 8.0 优化器 hint 语法（/*+ ... */ 注释式 hint，粒度更细、可命名作用域 QB_NAME）
SELECT /*+ JOIN_ORDER(o, u) NO_INDEX(o idx_status) */ o.id, u.name
FROM orders o JOIN users u ON u.id = o.user_id WHERE o.status = 'PAID';

常用 8.0 hint：JOIN_ORDER(t1,t2)（指定连接次序）、INDEX(t idx)/NO_INDEX(t idx)（按 hint 语法选/弃索引，替代旧式 FORCE/IGNORE 的更可控写法）、SEMIJOIN/NO_SEMIJOIN（子查询改写策略）、MAX_EXECUTION_TIME(1000)（只读查询超时保护，强烈建议配给报表 SQL）。原则：hint 是给优化器纠错的临时手段，根治靠统计信息与索引设计；hint 进了代码就要防它随数据分布变化变成枷锁，加注释说明"当时为什么"。

12.3 查询缓存：旧教程残留物

注意 MySQL 8.0 起查询缓存（query cache）已彻底移除，query_cache_type、show_status like 'Qcache%' 都不存在了。大量旧教程、旧面试题仍把它当调优项。如果你在 8.x 的 my.cnf 里写这些参数，服务会起不来（unknown variable）。8.0 的等价物在应用层/中间件（Redis、CDN）或上层缓存体系解决，见 12-分库分表.md 与 redis 手册。

13. 诊断速查表与优化方法论

13.1 现象 → 先看哪里

13.2 方法论收尾

1. 先测量后动手：没有 EXPLAIN（最好 ANALYZE）前后对照的"优化"等于没做——你无法证明它变快了，甚至无法证明它没变慢。留存每条优化前后的计划截图/文本，归档到工单。
2. 一次只改一个变量：同时改索引+改 SQL+调参数，出问题回滚时你不知道是哪个的功劳/锅。
3. 统计先行：怀疑计划诡异，先 ANALYZE TABLE 花两秒重采样，多数"优化器抽风"在这里就好了，再考虑 trace 深挖。
4. 为成本结构而优化：扫描行数/回表次数/排序行数这三个乘数项，砍任何一个都比调 buffer 参数收益大且副作用小。
5. 把验证做成习惯：上线新 SQL 前在预发用真实量级数据跑 EXPLAIN FORMAT=TREE；大改动（删索引、改驱动次序）在影子库灰度对比。索引怎么建、DDL 怎么在线变更，分别接着看 04 与 09 章。

实战建议：把本章工作流压成三句话贴在工位上——聚合找大头，EXPLAIN 看形状，ANALYZE 对行数。慢日志与 pt-query-digest 决定"优化谁"，FORMAT=TREE 决定"改哪里"（缺索引、回表、排序、深分页四件套），EXPLAIN ANALYZE 的 actual rows vs estimated rows 决定"统计信息还是 SQL 本身的锅"。所有优化动作做完，都要求留下改前/改后的两份执行计划，这是这一行里"我优化过了"的唯一凭证。