# 第 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` 之间做出有理由的选择。 ### 前置知识检查 | 你需要知道 | 在哪一节补齐 | |---|---| | 聚簇索引与二级索引的物理差异、回表、覆盖索引、最左前缀 | 见第 23 章 23.2、23.3 节；本章的 `key_len` 与 `Using index` 全部建立在这两点上 | | `filesort` 与 `index` 排序的区别、索引失效的情形清单 | 见第 23 章 23.3、23.6 节；本章只讲"怎么在 `Extra` 里看出来" | | 优化器的代价模型、统计信息从哪来 | 见第 20 章 20.1 节；本章讲"统计信息不准时会发生什么" | | `MDL` 与在线 DDL 的阻塞关系 | 见第 24 章 24.1 节；本章只讲工具与算法选择 | | 缓冲池、页、change buffer | 见第 20 章 20.2 节；本章用它解释大表变更的代价 | ## 25.1 EXPLAIN 全字段解读 `EXPLAIN` 的输出是**优化器执行计划**的文本投影，而不是执行结果。要建立的第一个直觉是：**这张表的每一列都在回答同一个问题——MySQL 打算怎么把行取出来，以及它为此要付出多少代价**。代价由 I/O 页数与 CPU 比较次数构成，优化器的目标是最小化二者之和。 ### 25.1.1 id 与 select_type `id` 是**查询块的编号**，不是执行顺序。规则只有两条：`id` **相同**时按**从上到下**的顺序执行（出现在上面的表先被访问，通常是驱动表）；`id` **不同**时**值越大越先执行**（子查询先于外层查询）。`select_type` 回答"这个查询块是什么角色"。 | select_type | 出现条件 | 关键观察点 | |---|---|---| | `SIMPLE` | 不含子查询与 `UNION` 的普通查询 | 派生表被合并进外层时也会显示 `SIMPLE` | | `PRIMARY` | 最外层查询块 | 有子查询时，外层标为 `PRIMARY` | | `SUBQUERY` | `SELECT` 列表或 `WHERE` 中的**非相关**子查询 | 通常只执行一次，结果可物化或转半连接 | | `DEPENDENT SUBQUERY` | 引用了外层列的**相关**子查询 | 外层每扫一行就重算一次，是性能高危信号 | | `DERIVED` | `FROM` 子句中的派生表被物化 | 5.7 物化后**无索引**，是"改写动机"的来源 | | `UNION` | `UNION` 中第二个及之后的查询块 | 与 `UNION RESULT` 成对出现 | | `UNION RESULT` | 合并 `UNION` 结果集的临时表 | 出现在 `Extra` 为 `Using temporary` 的那一行 | | `MATERIALIZED` | 子查询被物化为临时表 | 8.0 半连接策略之一 | > [!WARNING] > 把 `id` 大当成"后执行"是最常见的错误。`EXPLAIN` 的行序与 `id` 序都不等于运行时顺序，真正的顺序只能由 `EXPLAIN ANALYZE` 的树形缩进（`->`）读出，或用 `FORMAT=TREE` 查看。 ### 25.1.2 table、partitions 与 type 的十级排序 `table` 是本次访问的表名或别名；派生表显示为 ``，`UNION` 结果中间表显示为 ``。`partitions` 在分区表下显示**实际会被访问的分区**，它是分区裁剪（partition pruning）是否生效的唯一直接证据：写 `p0,p1` 说明裁剪失败要扫两个分区，写 `NULL` 说明该表不是分区表。 `type` 是访问类型，直接决定单次访问的代价量级，从优到劣的十级如下。 | type | 含义 | 触发条件 | 量级 | |---|---|---|---| | `system` | 表只有一行 | 常量表特例（如仅一行的系统表） | 最优 | | `const` | 主键或唯一索引的等值命中，至多一行 | 唯一索引（或 `NOT NULL` 的唯一索引前缀）等值查询 | `O(1)` | | `eq_ref` | 被驱动表通过**主键/唯一索引**逐行精确匹配 | 多表 JOIN 中使用 `=` 且被驱动侧是唯一索引 | 每驱动行 1 次唯一查找 | | `ref` | 非唯一索引等值匹配，可能多行 | 普通二级索引 `=` 查询 | 索引区间扫描 | | `fulltext` | 全文索引检索 | 使用 `MATCH ... AGAINST` | 与排序无关，独立一档 | | `ref_or_null` | `ref` 且额外需要匹配 `IS NULL` | 形如 `col = ? OR col IS NULL` | 索引两次区间扫描 | | `index_merge` | 合并多个索引的结果 | 多个单列索引上用 `OR` 或 `AND` 组合 | 需去重/排序合并 | | `range` | 索引区间扫描 | `BETWEEN`、`<`、`>`、`IN`、`LIKE 'x%'` | 部分索引扫描 | | `index` | 扫整棵二级索引（全索引扫描） | 无可用条件但索引比表窄，或需索引序输出 | 全索引 | | `ALL` | 全表扫描 | 无可用索引，或优化器判定全扫更便宜 | 全表 | 需要特别记忆两点。**第一，`fulltext` 与 `index_merge` 并非可以简单插入这条链**：`fulltext` 是全文索引专属，与 B+ 树索引的代价不可比；`index_merge` 虽然排在 `range` 之前，但它的实际代价常常**高于**单索引的 `range`，因为它要维护多路结果并做并集去重（`Using union(...)`）或排序合并（`Using sort_union(...)`），因此看到 `index_merge` 不要急着当"好计划"。**第二，`index` 看着像"用了索引"，实际是整棵索引扫一遍**，只有两种情况可以接受：需要索引提供有序输出以避免 `filesort`，或索引比聚簇索引窄得多因此扫描页数更少。 > [!TIP] > 面试速答口径：把十级排序背成"system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > range > index > ALL"，并补一句"`ALL` 与 `index` 的区别只在扫的是表还是索引，两者都是全扫；`range` 与 `ref` 的区别只在是否退化成区间"。 ### 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` 中条件的书写顺序无关，只取决于索引定义中从左到右被真正用上的列数。单列字节数按下面这张表累加。 | 列类型 | 索引自身字节 | 是否允许 `NULL` 的额外开销 | 说明 | |---|---|---|---| | `TINYINT` | 1 | +1 | | | `SMALLINT` | 2 | +1 | | | `MEDIUMINT` / `INT` | 3 / 4 | +1 | | | `BIGINT` | 8 | +1 | | | `FLOAT` / `DOUBLE` | 4 / 8 | +1 | | | `CHAR(n)` | `n × 字符集最大字节数` | +1 | 定长，无长度前缀 | | `VARCHAR(n)` | `n × 字符集最大字节数 + 1 或 2` | +1 | 最大字节长度 `≤ 255` 时前缀 1 字节，否则 2 字节 | | `DATETIME` / `TIMESTAMP` | 5 / 4 | +1 | 8.0 起 `DATETIME` 无微秒为 5 字节 | 字符集最大字节数：`latin1` 为 1，`gbk` 为 2，`utf8mb3` 为 3，`utf8mb4` 为 4。下面用一个具体表把所有情形推一遍。 ```sql -- 用 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; ``` ```text 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`"不只是约束问题：它能让索引项更短，间接降低树高与页数。 ```text 情形一 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` 是信息量最大的一列，其关键取值见下表。 | Extra 取值 | 含义 | 是否危险信号 | 应对 | |---|---|---|---| | `Using index` | 覆盖索引，无需回表 | 否（这是目标态） | 保持 | | `Using where` | Server 层对引擎返回的行做过滤 | 视情况 | 若 `filtered` 很低，说明条件该进索引 | | `Using index condition` | 索引条件下推（ICP），引擎层用索引列过滤 | 否（通常是好事） | 需确认 `Handler_read_next` 下降 | | `Using temporary` | 需要临时表，常见于 `GROUP BY`/`DISTINCT`/`UNION` | 是 | 用索引消除分组或改成 `UNION ALL` | | `Using filesort` | 无法用索引完成排序 | 是 | 让 `ORDER BY` 顺序与索引一致 | | `Using join buffer (Block Nested Loop)` | 被驱动表无索引，用连接缓冲 | 是 | 给被驱动表关联列加索引 | | `Using join buffer (hash join)` | 8.0.18+ 等值连接且无索引时的默认算法 | 视情况 | 有索引时 `NLJ` 仍常更优 | | `Select tables optimized away` | 由索引直接得出结果，无需访问行 | 否 | `MIN`/`MAX` 走索引首尾即可 | | `Impossible WHERE` | `WHERE` 恒为假，不执行 | 否 | 检查条件是否写错 | | `Using MRR` / `Using union(...)` | 多范围读 / 索引合并 | 否 | `index_merge` 需评估去重代价 | `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 实际"偏差的工具。 ```text -> 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`）。它**不执行语句**，因此拿不到真实行数，但它能回答"优化器为什么选了这个索引"。 | 维度 | `EXPLAIN` | `EXPLAIN FORMAT=JSON` | `EXPLAIN ANALYZE` | |---|---|---|---| | 是否执行语句 | 否 | 否 | 是（有副作用，`INSERT` 等不可用） | | 行数 | 估算 `rows` | 估算 `rows` | 估算 + 真实 `actual rows` 与 `loops` | | 耗时 | 无 | 无 | 真实 `actual time` | | 独有信息 | `filtered`、`Extra` 文本 | 代价明细、候选计划、覆盖列集合 | 各节点真实执行顺序与缩进树 | | 典型用途 | 快速判断访问类型 | 追问"为什么选错索引" | 追问"估算偏差多大、慢在哪一层" | > [!NOTE] > MySQL 版本差异：`EXPLAIN ANALYZE`、`Hash Join` 均为 8.0.18 起提供；派生表条件下推（derived condition pushdown）8.0.22 起提供；`FORMAT=TREE` 8.0.16 起提供。5.7 上定位估算偏差只能靠 `SHOW INDEX` 看基数、`ANALYZE TABLE` 后重跑对比。 ## 25.3 慢查询日志：从几千条收敛到一条 慢查询日志（slow query log）的价值不在于"记录了什么"，而在于**它能被聚合**。配置项必须成组理解： | 参数 | 默认值 | 作用与坑 | |---|---|---| | `slow_query_log` | `OFF` | 必须显式打开；动态可改，无需重启 | | `long_query_time` | 10（秒） | 支持小数（如 `0.5`）；**已有连接不受动态修改影响**，需重连 | | `log_queries_not_using_indexes` | `OFF` | 打开后会把所有未走索引的语句写入，**小表全扫也会被记录**，日志量爆炸 | | `log_throttle_queries_not_using_indexes` | 0 | 配合上一项使用，限制每分钟最多记录的条数 | | `log_slow_admin_statements` | `OFF` | 是否记录 `ALTER TABLE`/`ANALYZE TABLE` 这类管理语句 | | `min_examined_row_limit` | 0 | 扫描行数低于此值的语句不记录，用来压掉"慢但无害"的小表语句 | | `log_slow_extra` | `OFF` | 8.0.14+ 记录更多会话级指标（如 `Rows_affected`、`Sort_scan`） | ```bash # 打开并设置阈值：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`）**。 > [!IMPORTANT] > 面试标准答法分三步：先用 `pt-query-digest` 按 `Query_time:sum` 排序定位贡献最大的模板；再看该模板的 `count` 与 `avg` 决定是"优化单条"还是"减少调用"；最后用 `EXPLAIN ANALYZE` 验证改写后的计划与真实行数是否下降。只答"看慢日志找出慢 SQL"没有区分度。 ## 25.4 深分页的三种优化方案 `LIMIT 1000000, 20` 慢的根因是**`OFFSET` 语义要求服务端生成前 1000020 行再丢弃前 1000000 行**，即"读了但不用"。如果查询走二级索引还需要回表，成本是 `100 万次随机 I/O` 量级；即使走覆盖索引，也是 `100 万次索引项扫描`。 | 方案 | 核心思路 | 适用边界 | 不能用的场景 | |---|---|---|---| | 延迟关联 | 先在覆盖索引上取主键，再 JOIN 回原表 | 必须排序字段有索引，且能组成覆盖索引 | 排序列无索引时子查询本身就要 filesort | | 键集分页 | `WHERE id > last_id ORDER BY id LIMIT n` | 有序且唯一（或唯一组合）的排序键 | 需要跳页、需要按其他列排序且该列不唯一 | | 限制页码 | 业务上不接受深页 | 任何场景，成本最低 | 需要"查看全部历史"的场景 | ```sql -- 方案一：延迟关联。先在索引上把"要哪些主键"算出来，避免 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 下的执行 | 代价 | |---|---|---|---| | `COUNT(*)` | 行数 | 优化器选**最小的可用二级索引**做全索引扫描 | `O(N)`，但单位是索引页 | | `COUNT(1)` | 行数 | 与 `COUNT(*)` 同一条路径 | 与 `COUNT(*)` 相同 | | `COUNT(col)` | 非 `NULL` 行数 | 需读 `col` 的 `NULL` 标志；若 `col` 有索引则走该索引 | 略高于前两者，差别通常可忽略 | | `COUNT(DISTINCT col)` | 去重非 `NULL` 行数 | 需临时表或索引跳跃去重 | 显著更高 | **InnoDB 为什么不缓存行数**：MVCC 要求同一张表在不同事务、不同时刻看到的行数可能不同（见第 22 章 22.3 节），一个共享的行数计数器无法对每个 ReadView 都成立，因此只能实时扫描。**MyISAM 为什么快**：它没有 MVCC 的行级可见性，表头直接维护精确行数，`COUNT(*)` 无 `WHERE` 时 `O(1)` 返回。但**一旦带 `WHERE`，MyISAM 也必须逐行判断**，此时它的优势归零——这是"MyISAM 的 `COUNT(*)` 快"这句话最常被追问的下一层。 > [!WARNING] > 错误回答长这样："`COUNT(1)` 比 `COUNT(*)` 快，因为 `COUNT(*)` 要展开成所有列。"优化器在 `COUNT(*)` 上根本不做列展开，它只需判断"这一行存在"，两者走的是同一条执行路径；把这句话说出口，面试官会立刻追问"InnoDB 不存行数，那 `COUNT(*)` 到底扫的什么索引"，答不上就崩了。 大表实时计数的替代方案各有代价：**近似值**（`EXPLAIN` 的 `rows`、`SHOW TABLE STATUS` 的 `Rows`）零成本但误差可达 40% 以上，只适合展示；**计数表**（同事务内 `UPDATE counter SET c = c + 1`）能保证与业务数据一致，代价是把并发写集中到一行上，需要分片计数（如按 `hash % 100` 拆 100 行，求和得到总数）；**Redis 计数器**性能最好，但放弃了与数据库的强一致，需要接受"计数可能回退"并设计对账。三者没有优劣，只有一致性、延迟、复杂度之间的取舍。 ## 25.6 JOIN 的三种算法与驱动表选择 JOIN 的性能由两件事决定：**用哪种算法**，以及**谁驱动谁**。 ```text ① 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` 条件改写了。 > [!TIP] > 面试追问"怎么判断该给哪个表加索引"的标准答法：先看 `EXPLAIN` 里哪一行的 `type` 是 `ALL` 或出现了 `Using join buffer`，那个表就是被驱动表，给它的**关联列**加索引即可把 `BNL`/`hash join` 变成 `NLJ`。 ## 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`。 ```sql -- 危险：若 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` 有三种算法，决定了它会不会长时间锁表、以及代价有多大。 | 算法 | 是否重建表 | 是否允许并发 DML | 典型适用操作 | 风险 | |---|---|---|---|---| | `COPY` | 是 | 否（全程阻塞写，通常也阻塞读） | 任何不支持 INPLACE 的变更 | 表越大锁越久 | | `INPLACE` | 视操作而定 | 视是否声明 `ONLINE` 而定 | 加/删二级索引、修改 `VARCHAR` 长度、重命名列 | 需重建二级索引时消耗大量 I/O | | `INSTANT`（8.0.12+） | 否，仅改元数据 | 是 | 加列（限定位）、改默认值、重命名表 | 版本与列位置有约束 | **能否在线取决于操作类型，而不是取决于参数**。`ALGORITHM=INPLACE` 只保证"不需要重建整表（聚簇索引）"，它并不等于"不阻塞并发 DML"；真正允许并发写的是 `ALGORITHM=INPLACE, LOCK=NONE`，且仅当该操作本身支持时才生效。几个必须记住的边界：**加二级索引**是 `INPLACE`，且只重建该索引，代价远小于重建整表；**改列类型或转换字符集**即使是 `INPLACE`，MySQL 可能仍选择重建整棵聚簇索引，代价与 `COPY` 接近但允许并发 DML；**`INSTANT` 加列**在 8.0.29 之前要求新列加在最后，否则退化。判断方法是先用 `ALGORITHM=INPLACE, LOCK=NONE` 试跑，失败时 MySQL 会直接报错而**不会退化成锁表执行**——这正是这个组合参数的真正用途。 **两个第三方工具的原理差异**： ```text 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 本身慢，而是它把整张表的流量卡住了。 ## 本章小结 | 关键结论 | 一句话表述 | 常见错误 | |---|---|---| | `type` 十级排序 | `system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > range > index > ALL`，`index` 与 `ALL` 都是全扫 | 看到 `index` 就认为"走了索引所以快" | | `key_len` 的计算 | 索引最左连续被使用的列，按其定义字节数 + `NULL` 标志 + `VARCHAR` 长度前缀累加 | 把 `WHERE` 条件顺序当作决定因素 | | `rows` 是估算 | 由索引基数采样推得，`innodb_stats_persistent_sample_pages` 默认 20 页 | 把 `rows` 当成精确行数去做容量估算 | | 危险信号 | `Using temporary` + `Using filesort` 同时出现即高危 | 只盯 `type`，忽略 `Extra` | | 深分页 | 延迟关联省回表、键集分页省扫描，两者都不能跳页 | 答"加索引就好"，但 `OFFSET` 的扫描量不由索引决定 | | COUNT | `COUNT(*)` 与 `COUNT(1)` 在 InnoDB 等价，都靠扫最小的二级索引 | 答"`COUNT(1)` 更快" | | `NOT IN` 与 `NULL` | 子查询含 `NULL` 时结果恒为空 | 认为"`NULL` 不参与比较所以会被忽略" | ## 自测题 **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`，并说明为什么第三个值这么大却不一定是好计划。
答案与解析 依次为 `9`、`4`、`1247`。 `a` 是 `NOT NULL INT`，自身 4 字节且无 `NULL` 标志；`b` 是 `NULL INT`，4 + 1 = 5 字节，故 `a=1 AND b=2` 得 4 + 5 = 9。`b>2` 是范围条件，定位阶段只能用到 `a`，`b` 只能在索引内逐条过滤，故为 4。`c VARCHAR(300) NOT NULL` 在 `utf8mb4` 下最大字节长 1200 > 255，长度前缀占 2 字节，共 1202；`d CHAR(11) NULL` 为 11 × 4 = 44 加 1 字节 `NULL` 标志，共 45；合计 1247。 第三个值大只是说明"用上了两个索引列"，不说明高效：`key_len` 不包含区分度信息，若 `c` 的选择性极差（例如只有少量取值），优化器仍可能放弃 `idx_cd` 转而全扫；而且超宽索引本身会推高树高与页数，写入放大也随之增加。面试官在此处的追问通常是"`key_len` 能推出区分度吗"，正确答案是不能，要另看 `SHOW INDEX` 的 `Cardinality` 与 `rows`/`filtered`。 错在哪：最常见错误是把 `NULL` 标志位漏掉（把 9 答成 8），或把 `VARCHAR(300)` 的长度前缀按 1 字节算（把 1247 答成 1246）。
**Q2**（推导）`EXPLAIN` 显示某表 `type=ALL`、`possible_keys=idx_a`、`key=NULL`、`rows=200000`、`filtered=0.05`。请判断这一定是优化器选错了吗，并给出两条排查动作。
答案与解析 不一定。`possible_keys` 有值而 `key` 为 `NULL`，说明优化器**比较代价后主动放弃**了索引，常见于三类原因：一是该索引区分度极差（例如 `status` 只有两个取值），走索引会产生大量回表，随机 I/O 成本高于顺序全扫；二是表实际很小或大部分页已在 buffer pool 中，全扫的代价被高估了；三是统计信息失真，基数与实际分布严重不符，导致代价估算结论错误。 排查动作：先 `ANALYZE TABLE` 刷新统计信息后重新 `EXPLAIN` 对比，若计划改变则属第三类；再 `SHOW INDEX FROM t` 看 `Cardinality`，用它除以总行数得到区分度，若远小于 1%（经验阈值）则属第一类，此时正确的动作不是强制走索引，而是改索引设计（如建联合索引或前缀索引）或改写查询。 `filtered=0.05` 说明引擎返回的行只有 5% 被最终使用，这正是"引擎白扫"的信号——但注意它同时说明**条件没有下推到引擎**。若能把该条件并入索引成为联合索引的一部分，`filtered` 会上升、`rows` 会下降，这可能让优化器主动改选索引。 错在哪：直接答"优化器傻了，加 `FORCE INDEX`"。`FORCE INDEX` 只是把选择权从优化器手里夺走，它不改变索引本身的低区分度，在并发下经常把计划变得更差。
**Q3**（设计）`orders` 表 2000 万行，主键自增 `id`，`created_at` 上有索引，业务接口按 `created_at` 倒序分页，当前实现是 `SELECT * FROM orders ORDER BY created_at DESC LIMIT 1000000, 20`，接口 P99 超过 2 秒。请给出至少两种优化方案，并说清各自的限制。
答案与解析 方案一（延迟关联）：把外层需要的列与内层取主键分开，`SELECT o.* FROM orders o JOIN (SELECT id FROM orders ORDER BY created_at DESC LIMIT 1000000, 20) t ON o.id = t.id`。内层只扫 `idx_created` 且不回表，把 100 万次随机 I/O 降为索引页顺序扫描。限制：子查询仍要扫 100 万索引项，深分页没有从根上解决；且若 `SELECT` 列表需要 `created_at` 之外的列且无法覆盖，外层仍需 20 次回表（可接受）。 方案二（键集分页）：改为 `WHERE (created_at, id) < (?, ?) ORDER BY created_at DESC, id DESC LIMIT 20`，游标由客户端从上一页结果带回。扫描量恒为 20 行，与页深无关。限制：不能跳页，前端分页控件必须改成上一页/下一页；`created_at` 不唯一，必须补 `id` 组成复合游标，否则同秒数据会被跳过或重复。 方案三（限制页码）：在接口契约上拒绝深页，例如只允许前 5000 条。零改造成本，但牺牲了功能。 面试官会追问的下一层是"你们的索引是否覆盖"——若 `SELECT` 列表只有 `id` 和 `created_at`，方案一可以直接退化成覆盖索引查询，连 JOIN 都不需要。 错在哪：答"给 `created_at` 加索引"或"用 `LIMIT` 优化"，说不出 `OFFSET` 的语义就是"生成并丢弃前 N 行"。索引是否存在与深分页是否慢是两件事。
**Q4**（辨析）有人说"`COUNT(1)` 比 `COUNT(*)` 快，`COUNT(*)` 会展开成所有列；MyISAM 的 `COUNT(*)` 是 `O(1)`，所以计数应该用 MyISAM"。请逐句判断对错。
答案与解析 第一句错。`COUNT(*)` 与 `COUNT(1)` 的语义都是"统计行数"，优化器在 `COUNT(*)` 上不做列展开，二者执行路径一致。InnoDB 下优化器会挑选**最小的可用二级索引**做全索引扫描，因为二级索引叶子只存索引列 + 主键，页数少于聚簇索引，扫描的 I/O 更少。 第二句部分对但有重大前提缺失。MyISAM 表头维护精确行数，因此**不带 `WHERE` 的** `COUNT(*)` 确实是 `O(1)`；但一旦带 `WHERE`，MyISAM 也必须逐行判断条件，`O(1)` 优势完全失效——而真实业务里的计数几乎总带条件（如"某用户的未读消息数"）。 第三句错。MyISAM 不支持事务与行级锁，在需要 MVCC 与并发写的业务上不可用；为了一个计数指标换存储引擎是把架构问题误当成语法问题。正确做法是接受 InnoDB 的 `O(N)`，再按一致性要求选择替代方案：近似值（`EXPLAIN` 的 `rows`）零成本但误差可达 40% 以上；计数表（同事务 `UPDATE`）强一致但需分片计数以避免单行热点；Redis 计数器性能最好但需放弃强一致并对账。 面试官追问的下一层通常是"为什么 InnoDB 不缓存行数"，答案是 MVCC 下不同事务看到的行数可能不同，单一计数器无法对每个 ReadView 都成立（见第 22 章 22.3 节）。
**Q5**（判断）线上要把一张 8000 万行表的某个 `VARCHAR(50)` 列改成 `VARCHAR(200)`，团队打算直接执行 `ALTER TABLE ... MODIFY col VARCHAR(200), ALGORITHM=INPLACE, LOCK=NONE`。请判断这个方案是否可行，并说明如果不行该怎么改。
答案与解析 先要分清 `INPLACE` 与"在线"是两回事。`ALGORITHM=INPLACE` 只承诺"不重建整棵聚簇索引"，是否允许并发 DML 由 `LOCK=NONE` 声明，而 `LOCK=NONE` 能否生效取决于操作类型本身。扩大 `VARCHAR` 长度在 InnoDB 行格式为 `DYNAMIC`/`COMPACT` 时，若新长度仍在"长度前缀字节数不变"的范围内（`VARCHAR` 的最大字节长度不超过 255 则前缀 1 字节，超过则为 2 字节），通常可以走 `INPLACE` 且不重建表；但一旦跨越 255 字节这个边界，长度前缀字节数改变，就可能触发重建，此时执行会返回错误或退化为代价极高的重建。 可操作的做法：先用 `ALGORITHM=INPLACE, LOCK=NONE` **试跑**——不支持时 MySQL 会直接报错而**不会**退化成锁表执行，这正是这对参数的用途；若报错，则选择 `pt-online-schema-change`（触发器 + 影子表 + 分批拷贝 + 原子换名）或 `gh-ost`（读 binlog 而非触发器，主库无触发器开销，可限流可暂停，但要求 `ROW` 格式 binlog 且表有主键/唯一键）。 还有一条容易被忽略的风险：即使操作本身在线，DDL 在开始与结束阶段要获取 `MDL` 排他锁，若存在长事务持有 `MDL` 读锁，DDL 会长时间等待并**阻塞其后的所有查询**，表现为"DDL 卡住，全表流量雪崩"。因此上线前必须确认没有长事务（见第 24 章 24.1 节）。 错在哪：答"加了 `ALGORITHM=INPLACE` 就不锁表"。`INPLACE` 与"不阻塞并发 DML"是两个独立维度，写成一句话会立刻被追问"那 `LOCK=NONE` 是干什么的"。
## 面试高频问题与陷阱 | 面试问法 | 考查角度 | 陷阱 / 加分点 | 对应本章小节 | |---|---|---|---| | `EXPLAIN` 结果里 `type` 有哪些取值，你怎么判断一个计划好不好？ | 访问类型的完整谱系与危险阈值 | 错误回答："`range` 就不错了，`ALL` 才要优化。"漏掉 `index` 与 `ALL` 都是全扫、`index_merge` 常比单索引 `range` 更贵这两点 | 25.1.2 | | `key_len` 怎么算？为什么要关心它？ | 索引实际生效列数的量化 | 错误回答："`key_len` 就是索引定义的总长度。"答不出范围条件之后的列不计入、`NULL` 标志位与 `VARCHAR` 长度前缀要单独加 | 25.1.3 | | `EXPLAIN` 的 `rows` 是精确值吗？为什么会有偏差？ | 统计信息的采样本质 | 错误回答："是精确值"，或"不准就 `ANALYZE TABLE`"却说不出基数由索引页随机采样估算、默认只采 20 页 | 25.1.4、25.2 | | 慢查询日志里几千条慢 SQL，你怎么决定先优化哪一条？ | 从日志到行动的收敛口径 | 错误回答："按单条最慢的排。"单条偶发超长往往是锁等待或刷盘抖动，应看 `Query_time:sum` 的时间占比，再区分高频短耗与低频长耗 | 25.3 | | `LIMIT 1000000, 20` 为什么慢？你怎么优化？ | 深分页的语义根因与三种解法 | 错误回答："给排序字段加索引就好了。"索引不改变 `OFFSET` 必须生成并丢弃前 N 行的语义；也答不出键集分页不能跳页 | 25.4 | | `COUNT(*)` 和 `COUNT(1)` 哪个快？为什么 InnoDB 不缓存行数？ | COUNT 语义与 MVCC 的冲突 | 错误回答："`COUNT(1)` 快，`COUNT(*)` 要展开所有列。"正确要点是 `COUNT(*)` 扫最小的二级索引、MVCC 使行数对不同 ReadView 不唯一 | 25.5 | | 为什么 `NOT IN` 有时候查不出数据，明明子查询里有值？ | 三值逻辑与 `NULL` | 错误回答："`NULL` 不参与比较所以被忽略。"实际是 `x <> NULL` 得 `UNKNOWN`，整个条件对任何 `x` 都不成立，结果恒为空 | 25.7 | | `JOIN` 的驱动表是怎么选的？`LEFT JOIN` 和 `INNER JOIN` 有区别吗？ | 代价模型与连接语义的相互作用 | 错误回答："小表驱动大表，就是行数少的表。"准确口径是 `rows × filtered` 估算输出小的一侧；且 `WHERE` 里对右表列的非 `NULL` 过滤会把 `LEFT JOIN` 改写成 `INNER JOIN` | 25.6 | | `ALTER TABLE` 加索引会锁表吗？`ALGORITHM=INPLACE` 是不是就一定不锁？ | 在线 DDL 的能力边界 | 错误回答："加了 `INPLACE` 就万无一失。"`INPLACE` 与"允许并发 DML"是两个维度，还需 `LOCK=NONE`；且首尾仍需 `MDL`，长事务会让它阻塞全表流量 | 25.9 | ## 延伸阅读 - 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` 章节。 --- > 作者: [破戒僧](https://onlyblacktea.cn) > URL: https://onlyblacktea.cn/book/backend/ch25/