第 25 章 SQL 优化与执行计划

前几章讲引擎"为什么这么设计"，本章讲工程师唯一能直接操纵的杠杆：把一条 SQL 变成另一条 SQL，然后读懂优化器给出的执行计划。

学习目标

读完本章，你应当能够：

1. 逐列解读 EXPLAIN 输出，判定 type 处于十级排序中的哪一级并说明触发条件，独立算出联合索引的 key_len；
2. 从 Extra 中识别 Using temporary、Using filesort、Using join buffer 等危险信号，并判断它属于 Server 层还是引擎层的问题；
3. 说明 EXPLAIN 的 rows 为什么是估算，用 EXPLAIN ANALYZE 与 FORMAT=JSON 的差异定位"估算与实际差几个数量级"导致的选错索引；
4. 配置慢查询日志并写出从几千条慢 SQL 收敛到"最该优化的那一条"的筛选口径；
5. 为深分页给出延迟关联、键集分页、限制页码三种方案并说清各自的边界，辨析 COUNT(*)/COUNT(1)/COUNT(列) 的语义与代价；
6. 判断一次 JOIN 会走 NLJ/BNL/Hash Join 中的哪一种，并解释驱动表由什么决定；
7. 判断一条 DDL 能否 ALGORITHM=INPLACE，并在 pt-online-schema-change 与 gh-ost 之间做出有理由的选择。

前置知识检查

25.1 EXPLAIN 全字段解读

EXPLAIN 的输出是优化器执行计划的文本投影，而不是执行结果。要建立的第一个直觉是：这张表的每一列都在回答同一个问题——MySQL 打算怎么把行取出来，以及它为此要付出多少代价。代价由 I/O 页数与 CPU 比较次数构成，优化器的目标是最小化二者之和。

25.1.1 id 与 select_type

id 是查询块的编号，不是执行顺序。规则只有两条：id 相同时按从上到下的顺序执行（出现在上面的表先被访问，通常是驱动表）；id 不同时值越大越先执行（子查询先于外层查询）。select_type 回答"这个查询块是什么角色"。

25.1.2 table、partitions 与 type 的十级排序

table 是本次访问的表名或别名；派生表显示为 <derivedN>，UNION 结果中间表显示为 <union1,2>。partitions 在分区表下显示实际会被访问的分区，它是分区裁剪（partition pruning）是否生效的唯一直接证据：写 p0,p1 说明裁剪失败要扫两个分区，写 NULL 说明该表不是分区表。

type 是访问类型，直接决定单次访问的代价量级，从优到劣的十级如下。

需要特别记忆两点。第一，fulltext 与 index_merge 并非可以简单插入这条链：fulltext 是全文索引专属，与 B+ 树索引的代价不可比；index_merge 虽然排在 range 之前，但它的实际代价常常高于单索引的 range，因为它要维护多路结果并做并集去重（Using union(...)）或排序合并（Using sort_union(...)），因此看到 index_merge 不要急着当"好计划"。第二，index 看着像"用了索引"，实际是整棵索引扫一遍，只有两种情况可以接受：需要索引提供有序输出以避免 filesort，或索引比聚簇索引窄得多因此扫描页数更少。

25.1.3 possible_keys、key 与 key_len

possible_keys 是候选集合，key 是最终选定。二者不一致有两种含义：possible_keys 有值而 key 为 NULL，说明优化器算完代价后认为扫描的成本更低（常见于索引区分度差、表很小、或统计信息失真）；key 不在 possible_keys 里，说明优化器选了一个候选集之外的索引，比如把 Using index 的覆盖索引当成了更便宜的选项。key 为 NULL 且 type = ALL 才是真正的"没走索引"。

key_len 是本次实际用于查找的索引列的总字节数，它与 WHERE 中条件的书写顺序无关，只取决于索引定义中从左到右被真正用上的列数。单列字节数按下面这张表累加。

字符集最大字节数：latin1 为 1，gbk 为 2，utf8mb3 为 3，utf8mb4 为 4。下面用一个具体表把所有情形推一遍。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

-- 用 utf8mb4 而不是 latin1，是因为线上库基本都是它，key_len 会差 4 倍
CREATE TABLE t (
    id        BIGINT       NOT NULL AUTO_INCREMENT,
    a         INT          NOT NULL,          -- 索引自身 4 字节，NOT NULL 不加开销
    b         INT          NULL,              -- 4 + 1（NULL 标志位）
    c         VARCHAR(300) NOT NULL,          -- 300×4=1200 > 255，故长度前缀占 2 字节
    d         CHAR(11)     NULL,              -- 11×4=44，加 1 字节 NULL 标志
    PRIMARY KEY (id),
    KEY idx_ab (a, b),
    KEY idx_cd (c, d)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

EXPLAIN SELECT * FROM t WHERE a = 1;

1
2

id  select_type  table  partitions  type  possible_keys  key     key_len  ref   rows  filtered  Extra
 1  SIMPLE       t      NULL        ref   idx_ab         idx_ab  4        const 100   100.00    NULL

key_len = 4：只用到 a，a 是 NOT NULL INT。如果查询写成 WHERE b = 2，key_len 是 NULL（走 ALL），因为 b 不是最左列。若写成 WHERE a = 1 AND b = 2，key_len = 4 + 4 + 1 = 9，多出的 1 字节就是 b 的 NULL 标志位——这也是为什么"能 NOT NULL 就 NOT NULL“不只是约束问题：它能让索引项更短，间接降低树高与页数。

1
2
3
4
5

情形一  WHERE a = 1 AND b = 2           → key_len = 9    （4 + (4+1)）
情形二  WHERE a = 1 AND b = 2 AND c = 'x' → key_len = 9   （c 属于 idx_cd，idx_ab 到 b 为止）
情形三  WHERE a = 1 AND b > 2           → key_len = 4    （b 是范围条件，后续列无法用于定位）
情形四  WHERE c = 'x'                   → key_len = 1202 （1200 + 2，无 NULL 标志）
情形五  WHERE c = 'x' AND d = 'y'       → key_len = 1247 （1200+2 + 44+1）

情形三是最容易被追问的一条：范围条件之后的索引列不再计入 key_len，因为定位阶段只能用到范围列为止，后续列只能在索引内逐条过滤（这正是 25.1.4 中 filtered 会变小的原因）。情形五给出一个反直觉结论：key_len 大不等于计划好，1247 只说明用上了两列，是否高效仍要看区分度与 rows。

25.1.4 ref、rows、filtered 与 Extra

ref 显示与索引列做等值比较的对象：const 表示常量，库名.表名.列名 表示来自前面某张表的列（这是 JOIN 的关联列，看到它说明被驱动表的这一列被用于查找），func 表示用了函数或隐式转换——func 基本等价于"索引只能部分生效”，例如字符集不一致的 JOIN 会把驱动列的字符集转换后再匹配。

rows 是基于统计信息估算的、需要扫描的行数，不是精确值。它由索引的基数（cardinality）与数据分布直方图推得，而基数本身是通过随机采样索引页估算出来的，innodb_stats_persistent_sample_pages 默认只有 20 页。因此数据分布倾斜、索引页很多或长期不更新统计信息时，rows 可能偏离实际几个数量级；优化器又正是用 rows × 单行代价 比较各计划的，rows 估错就直接导致选错索引或选错驱动表。另外，rows 是"该表被驱动多少次 × 每次扫多少行"的估算值，多表 JOIN 时不能简单相乘。

filtered 是引擎返回的行中经过 Server 层条件过滤后剩余的行百分比。它的价值在于区分"引擎扫得多"和"Server 层丢得多"：rows = 10000 且 filtered = 1.00 说明引擎扫的都要用；rows = 10000 且 filtered = 0.10 说明引擎白扫了 9000 行，典型场景是索引只能定位到 a，而 b 的条件没进索引、只能在 Server 层剔除。rows × filtered 才是估算的最终输出行数，多表 JOIN 时用它判断优化器认为谁是驱动表。

Extra 是信息量最大的一列，其关键取值见下表。

Using temporary 与 Using filesort 同时出现在大表查询上是唯一一个可以"不问数据量就判定为高危"的组合：临时表在超过 tmp_table_size 与 max_heap_table_size 较小值后落盘，再叠一次外部排序，代价是数千次随机 I/O 量级。看到这个组合，先问数据量与并发。

25.2 EXPLAIN ANALYZE 与 FORMAT=JSON

EXPLAIN 不执行语句，因此只给估算。EXPLAIN ANALYZE（8.0.18+）真正执行语句并返回树形结构，每一节点同时打印估算值（cost、rows）与真实值（actual time、actual rows、loops），这是唯一能直接看见"估算 vs 实际"偏差的工具。

1
2
3
4

-> Nested loop inner join  (cost=1105.6 rows=1000) (actual time=0.09..38.4 rows=1200000 loops=1)
    -> Index scan on o using idx_user (cost=105.4 rows=1000) (actual time=0.05..4.2 rows=1000 loops=1)
    -> Filter: (i.user_id = o.id)  (cost=0.8 rows=1) (actual time=0.02..0.03 rows=1200 loops=1000)
        -> Index lookup on i using idx_uid (user_id=o.id)  (cost=0.8 rows=1) (actual time=0.02..0.03 rows=1200 loops=1000)

读法是三步。第一看 rows 与 actual rows 的倍数：上面第二个节点估算 rows=1 而实际每轮 1200 行，偏了三个数量级，外层 JOIN 的行数估算随之从 1000 崩到 120 万——这就是"统计信息失真导致计划错误"的典型形态。第二看 loops：它表示该节点被重复执行的次数，actual rows 是每轮平均，总行数等于 actual rows × loops；忽略这一点会把 120 万行读成 1200 行。第三看 actual time 的 a..b 区间：前者是返回首行耗时，后者是返回全部行耗时，b 大而 a 小说明是扫描量问题，a 本身就大说明是启动代价问题（如临时表、排序）。

EXPLAIN FORMAT=JSON（5.6.5+）给的是同一棵计划树的 JSON 形式，重点是它比表格多出四类信息：代价明细（cost_info 里的 read_cost/eval_cost/prefix_cost）、索引选择的候选比较（8.0 的 considered_execution_plans）、覆盖索引判定所需的列集合（using_index 与 covered）、是否启用了 ICP 与 MRR（index_condition、using_MRR）。它不执行语句，因此拿不到真实行数，但它能回答"优化器为什么选了这个索引"。

25.3 慢查询日志：从几千条收敛到一条

慢查询日志（slow query log）的价值不在于"记录了什么"，而在于它能被聚合。配置项必须成组理解：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

# 打开并设置阈值：0.1 s 是为了先"抓全"，日志量问题随后靠 min_examined_row_limit 收敛
mysql -e "SET GLOBAL slow_query_log=ON;
          SET GLOBAL long_query_time=0.1;
          SET GLOBAL min_examined_row_limit=1000;
          SET GLOBAL log_queries_not_using_indexes=ON;
          SET GLOBAL log_throttle_queries_not_using_indexes=10;"

# mysqldumpslow 按"语句模板"聚合，回答"哪类 SQL 出现最多"
mysqldumpslow -s c -t 20 /var/log/mysql/slow.log      # -s c 按出现次数，-t 取前 20
mysqldumpslow -s t -t 20 /var/log/mysql/slow.log      # -s t 按总耗时

# pt-query-digest 额外给出响应时间占比、单次最慢、执行计划样例与方差
pt-query-digest --limit 20% --order-by Query_time:sum /var/log/mysql/slow.log > digest.txt

两者的差异是面试区分点：mysqldumpslow 只做模板聚合 + 计数/总时长排序，它不区分语句来自哪个库、不给时间分布；pt-query-digest 会做响应时间占比（profile），即把"总响应时间"当作 100%，列出每条模板占了多少，同时给出 pct（占总时间比）、count、sum、min/max/avg 与方差。收敛口径因此是：先按 Query_time:sum 排序取前若干条，再在其中排除"次数极高但单次极短"的模板（那是吞吐问题不是单条问题）与"次数极低但偶发超长"的模板（那是锁等待或刷盘抖动，要看 Innodb_row_lock_time 与 SHOW ENGINE INNODB STATUS）。

25.4 深分页的三种优化方案

LIMIT 1000000, 20 慢的根因是**OFFSET 语义要求服务端生成前 1000020 行再丢弃前 1000000 行**，即"读了但不用"。如果查询走二级索引还需要回表，成本是 100 万次随机 I/O 量级；即使走覆盖索引，也是 100 万次索引项扫描。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

-- 方案一：延迟关联。先在索引上把"要哪些主键"算出来，避免 100 万次回表
SELECT o.*
FROM orders o
JOIN (
    -- 子查询只扫 idx_created，且被 LIMIT 截断在索引内完成，不回表
    SELECT id FROM orders ORDER BY created_at LIMIT 1000000, 20
) AS t ON o.id = t.id;

-- 方案二：键集分页。用"上一页最后一个键"替代 OFFSET，扫描量恒为 n 行
SELECT * FROM orders
WHERE id > 1000000          -- 上一页返回的最大 id，由客户端带回
ORDER BY id LIMIT 20;

-- 方案三：业务限制。把页码上限做成接口契约，超限直接拒绝
SELECT * FROM orders ORDER BY id LIMIT 20;   -- 只允许 page <= 100

三点必须补全的限制。第一，延迟关联省的是回表而不是索引扫描：内层子查询仍然要沿索引走 100 万项，只是这些项都在索引页里、不需要回表，因此它把成本从"100 万次随机 I/O"降成"100 万次顺序索引扫描"，但深分页依然要扫索引，这是上一节表格中"不能跳页"之外最容易被忽略的边界。第二，键集分页要求排序键唯一：若按 created_at 做键集分页，同一秒的订单会被跳过或重复，必须先补一个唯一列组成 (created_at, id) 的复合游标。第三，键集分页不能跳页（因为"下一页"依赖"上一页的最后一行"），前端的分页控件必须改成"下一页/上一页"。

25.5 COUNT 的四种写法与大表计数

COUNT(expr) 的语义是统计 expr 不为 NULL 的行数：COUNT(*) 不读任何列值只统计行数，COUNT(1) 的常量恒不为 NULL 因此与它完全一致，COUNT(col) 必须读该列的 NULL 标志位因而不统计 NULL，COUNT(DISTINCT col) 还需额外去重。

InnoDB 为什么不缓存行数：MVCC 要求同一张表在不同事务、不同时刻看到的行数可能不同（见第 22 章 22.3 节），一个共享的行数计数器无法对每个 ReadView 都成立，因此只能实时扫描。MyISAM 为什么快：它没有 MVCC 的行级可见性，表头直接维护精确行数，COUNT(*) 无 WHERE 时 O(1) 返回。但一旦带 WHERE，MyISAM 也必须逐行判断，此时它的优势归零——这是"MyISAM 的 COUNT(*) 快"这句话最常被追问的下一层。

大表实时计数的替代方案各有代价：近似值（EXPLAIN 的 rows、SHOW TABLE STATUS 的 Rows）零成本但误差可达 40% 以上，只适合展示；计数表（同事务内 UPDATE counter SET c = c + 1）能保证与业务数据一致，代价是把并发写集中到一行上，需要分片计数（如按 hash % 100 拆 100 行，求和得到总数）；Redis 计数器性能最好，但放弃了与数据库的强一致，需要接受"计数可能回退"并设计对账。三者没有优劣，只有一致性、延迟、复杂度之间的取舍。

25.6 JOIN 的三种算法与驱动表选择

JOIN 的性能由两件事决定：用哪种算法，以及谁驱动谁。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

① NLJ（Nested-Loop Join，被驱动表关联列有索引）
   驱动表每命中一行 ──▶ 用该行的关联值去被驱动表的索引上做一次查找
   Extra: 无 "join buffer" 字样；被驱动表 type 通常为 eq_ref / ref

② BNL（Block Nested-Loop Join，被驱动表无可用索引）
   把驱动表的一批行读入 join_buffer ──▶ 扫描被驱动表全表，逐行与 buffer 比对
   Extra: Using join buffer (Block Nested Loop)
   代价：驱动表批数 × 被驱动表全表扫描次数

③ Hash Join（8.0.18+，等值连接且无可用索引，默认取代 BNL）
   用驱动表（较小侧）建哈希表 ──▶ 扫描被驱动表探测哈希表
   Extra: Using join buffer (hash join)
   相对 BNL：比对从 O(n×m) 比较降为 O(n+m) 探测，且不再需要把驱动表分批

驱动表选择逻辑：优化器比较两种顺序的代价，判断依据是 rows × filtered 估算的输出行数——估算越小的一侧做驱动表，这样"外层循环次数"最少，内层索引查找次数随之最少。这就是"小表驱动大表"的准确含义：不是物理行数小，而是参与 JOIN 后估算输出行数小；一张大表若被 WHERE 过滤到只剩 10 行，它才是驱动表。STRAIGHT_JOIN 的作用是强制按书写顺序决定驱动表，即左表永远是驱动表，它只在优化器选错时作为兜底手段，滥用会锁死一个更差的计划。

LEFT JOIN 与 INNER JOIN 的差异在于谁能被重排：INNER JOIN 的两个表可以自由交换顺序；LEFT JOIN 在语义上必须保留左表全部行，因此默认由左表驱动。但有一个关键例外：如果 WHERE 中对右表列有非 NULL 的过滤条件，这个 LEFT JOIN 会被优化器改写成 INNER JOIN（因为右表不匹配的行最终也会被 WHERE 滤掉），此时表序又可以重排。这也是"LEFT JOIN 变慢"最典型的根因：本该被外连接固定的顺序被 WHERE 条件改写了。

25.7 子查询、IN 与 EXISTS 的取舍

IN 在 5.6+ 会尝试半连接（semi-join）优化：因为 IN 只关心"存在匹配"，不需要真正的笛卡尔积，优化器可以把子查询展平成 JOIN 并去重（策略包括 Materialization 物化后建唯一索引、FirstMatch 逐行首次匹配即停、LooseScan 松散索引扫描、DuplicateWeedout 去重表）。EXISTS 则通常不做半连接改写，它保持相关子查询的形态逐行探测。这带来两条可操作的结论：

• 半连接生效时，IN 与等价的 EXISTS 计划往往趋同，此时性能差异来自子查询结果集是否被物化；
• 半连接不生效时（子查询含聚合、含 UNION、或条件中含 OR），IN 会退化为物化 + 逐行比对，代价是"子查询执行一次 + 结果集扫描"，而 EXISTS 的代价是"外层行数 × 内层索引查找"。

因此"外表小用 IN、内表小用 EXISTS“的准确含义是两条量化口径的对比：IN 的代价约为 子查询结果集大小（物化后一次性比对），EXISTS 的代价约为 外表行数 × 单次探测代价。判断口诀是：IN 面向"集合”，EXISTS 面向"循环"——子查询产出的集合小、且外层大，用 IN；外层小、内层有索引，用 EXISTS。例外也同样明确：若子查询无法被半连接优化（聚合、UNION、OR），IN 的物化开销可能远超 EXISTS 的循环探测；反之若内层关联列没有索引，EXISTS 会退化成对外层的每一行做一次全表扫描，此时必须改回 IN。

NOT IN 的 NULL 陷阱必须写死：NOT IN 等价于对集合中每个值做 <> 再取合取，一旦子查询结果含 NULL，x <> NULL 求值为 UNKNOWN，整个 WHERE 对任何 x 都不为真，结果集恒为空。MySQL 手册与 SQL 标准在这里是一致的。安全写法有二：NOT EXISTS，或在子查询中显式 WHERE col IS NOT NULL。

1
2
3
4
5
6
7
8

-- 危险：若 sub 的 col 含 NULL，下面恒返回空集，且不报错——最难查的一类"业务无数据"故障
SELECT * FROM t1 WHERE t1.a NOT IN (SELECT col FROM t2);

-- 安全写法一：NOT EXISTS 天然不受 NULL 影响
SELECT * FROM t1 WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE t2.col = t1.a);

-- 安全写法二：保留 NOT IN 但显式排除 NULL，注意这会改变语义（原意是否要"col IS NULL 也算不匹配"要先确认）
SELECT * FROM t1 WHERE t1.a NOT IN (SELECT col FROM t2 WHERE col IS NOT NULL);

FROM 子查询（派生表）的处理在版本间差异很大：5.7 会先物化派生表，且物化结果上没有索引、条件下推不进去，所以 SELECT ... FROM (SELECT ...) d WHERE d.x = 1 里的 d.x = 1 无法下推，物化出的是全量结果；8.0 引入派生表合并（把可合并的派生表直接展平进外层，select_type 因此变成 SIMPLE），8.0.22 起又支持派生表条件下推，才真正缓解了这个问题。改写策略很简单：能用 JOIN 表达的就不要包成派生表，尤其不要在派生表里 ORDER BY + LIMIT 后再在外层过滤。

25.8 其他高频优化点

UNION 与 UNION ALL：UNION 要做去重，代价是一次临时表 + 可能的排序（Extra 出现 Using temporary）；UNION ALL 只是拼接。当能确定两侧不会产生重复行时（例如按互斥条件拆分同一张表），应无条件改用 UNION ALL。

ORDER BY/GROUP BY 的索引化：Extra 的 Using filesort 与 Using temporary 都表示"用了索引之外的手段"，消除办法是让排序/分组顺序与某个索引的前缀顺序一致。两个细节：若 WHERE 对索引前导列做了等值条件，该列在索引中恒定，ORDER BY 只需匹配后续列的顺序；若查询是 GROUP BY a ORDER BY b，MySQL 可能先分组再排序，让 a、b 落在同一索引上即可省掉一次排序。8.0 移除了 GROUP BY 的隐式排序，这一点在版本迁移时会造成"计划没变但结果顺序变了"。

OR 改 UNION ALL：同一列上的多个 OR 一般能被优化成 range 或 index_merge，但跨列的 OR（如 WHERE a = 1 OR b = 2）在无法用索引合并时会导致全表扫描。改写为两个走各自索引的查询再 UNION ALL（或 UNION，视是否需要去重）通常更快。

批量操作：逐条 INSERT 的代价是每次往返 + 每次事务提交的日志刷盘；批量写法有两种，INSERT INTO t VALUES (...),(...),(...) 的多值语法和显式事务包裹多条语句。前者减少网络往返与解析次数，后者减少日志刷盘次数，二者收益不重叠，可以叠加。LIMIT 与 ORDER BY 组合的总排序量取决于排序键与过滤条件：能在索引上完成排序时，LIMIT n 只需读 n 行即可停止；若走 filesort，则必须先取出全部候选行排序，此时 LIMIT 10 并不比不写 LIMIT 便宜——这也是深分页与宽表排序会同时爆炸的原因。

25.9 大表结构变更的在线方案

ALTER TABLE 有三种算法，决定了它会不会长时间锁表、以及代价有多大。

能否在线取决于操作类型，而不是取决于参数。ALGORITHM=INPLACE 只保证"不需要重建整表（聚簇索引）"，它并不等于"不阻塞并发 DML"；真正允许并发写的是 ALGORITHM=INPLACE, LOCK=NONE，且仅当该操作本身支持时才生效。几个必须记住的边界：加二级索引是 INPLACE，且只重建该索引，代价远小于重建整表；改列类型或转换字符集即使是 INPLACE，MySQL 可能仍选择重建整棵聚簇索引，代价与 COPY 接近但允许并发 DML；INSTANT 加列在 8.0.29 之前要求新列加在最后，否则退化。判断方法是先用 ALGORITHM=INPLACE, LOCK=NONE 试跑，失败时 MySQL 会直接报错而不会退化成锁表执行——这正是这个组合参数的真正用途。

两个第三方工具的原理差异：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

pt-online-schema-change（基于触发器）
  ① 建影子表 _t_new，按目标结构 DDL
  ② 在原表上建 AFTER INSERT/UPDATE/DELETE 三个触发器，把增量写入影子表
  ③ 分批（chunk）复制原表数据到影子表
  ④ RENAME TABLE 原表→_t_old，_t_new→原表（原子换名，瞬时）
  ⑤ 删除 _t_old 与触发器
  风险：触发器与业务既有触发器冲突；触发器在每次 DML 上增加开销；
        换名瞬间需要短暂元数据锁；外键引用可能阻止换名

gh-ost（基于 binlog，无触发器）
  ① 建影子表 _t_gho
  ② 作为 MySQL 从库连接，读取 binlog（ROW 格式）解析出对原表的变更
  ③ 分批复制存量数据，同时把 binlog 事件应用到影子表
  ④ 通过原子 RENAME 完成切换（可用 cut-over 精确控制时机）
  优势：主库无触发器开销；可随时暂停、可动态限流（throttle）
  约束：必须 ROW 格式 binlog；表需有主键或唯一键；外键支持受限；
        需额外的从库连接与 binlog 解析开销

选型口径：业务侧已有触发器、或环境无法开 ROW 格式 binlog 时选 pt-online-schema-change；追求对主库影响最小、希望精细限流与暂停时选 gh-ost。直接 ALTER 会长时间锁表的三种情况：目标操作只支持 COPY；显式写了 LOCK=EXCLUSIVE（或旧版本默认降级）；操作本身支持在线，但开始与结束时要获取 MDL 排他锁，若存在长事务或长查询持有 MDL 读锁，DDL 会一直等锁并阻塞其后的所有查询——这不是 DDL 本身慢，而是它把整张表的流量卡住了。

本章小结

自测题

Q1（计算）表 t(id BIGINT PRIMARY KEY, a INT NOT NULL, b INT NULL, c VARCHAR(300) NOT NULL, d CHAR(11) NULL) 字符集 utf8mb4，索引 idx_ab(a,b)、idx_cd(c,d)。分别写出 WHERE a=1 AND b=2、WHERE a=1 AND b>2、WHERE c='x' AND d='y' 三种查询的 key_len，并说明为什么第三个值这么大却不一定是好计划。

Q2（推导）EXPLAIN 显示某表 type=ALL、possible_keys=idx_a、key=NULL、rows=200000、filtered=0.05。请判断这一定是优化器选错了吗，并给出两条排查动作。

Q3（设计）orders 表 2000 万行，主键自增 id，created_at 上有索引，业务接口按 created_at 倒序分页，当前实现是 SELECT * FROM orders ORDER BY created_at DESC LIMIT 1000000, 20，接口 P99 超过 2 秒。请给出至少两种优化方案，并说清各自的限制。

Q4（辨析）有人说"COUNT(1) 比 COUNT(*) 快，COUNT(*) 会展开成所有列；MyISAM 的 COUNT(*) 是 O(1)，所以计数应该用 MyISAM"。请逐句判断对错。

Q5（判断）线上要把一张 8000 万行表的某个 VARCHAR(50) 列改成 VARCHAR(200)，团队打算直接执行 ALTER TABLE ... MODIFY col VARCHAR(200), ALGORITHM=INPLACE, LOCK=NONE。请判断这个方案是否可行，并说明如果不行该怎么改。

面试高频问题与陷阱

延伸阅读

• MySQL 8.0 Reference Manual, Optimization 篇：8.8 Understanding the Query Execution Plan（含 EXPLAIN 输出格式全字段与 EXPLAIN ANALYZE）、8.9 Controlling the Query Optimizer、8.2 Optimizing SQL Statements。
• MySQL 8.0 Reference Manual, The Slow Query Log 与 Online DDL Operations（ALTER TABLE 各操作的 In Place/Rebuilds Table/Permits Concurrent DML 三列对照表，是判断算法边界的权威依据）。
• MySQL 8.0 Reference Manual, Optimizing Subqueries, Derived Tables, and View References（semi-join 各策略与派生表合并、条件下推的版本节点）。
• SQL 标准（ISO/IEC 9075）中关于 NULL 与三值逻辑的部分，以及 COUNT 聚合对 NULL 的处理定义。
• 《高性能 MySQL》（第 4 版）第 6 章"查询性能优化”、第 8 章"优化特定类型的查询”：与本章 25.4~25.8 节互为补充，其对 filesort 与临时表的代价分析更完整。
• Percona Toolkit 文档中 pt-query-digest 的 --order-by 与 profile 输出说明；gh-ost 官方文档的 throttle 与 cut-over 章节。