MySQL 8.0 index_merge 走了 sort_union 反而比全表扫描慢 8 倍：什么条件下该让优化器选 union / intersection，有没有可复用的判断框架？

陈清和已解决

MySQLindex_mergesort_unionoptimizer_switchEXPLAIN慢查询治理OR条件UNION ALL

背景

MySQL 8.0.32 / InnoDB，一张 900 万行的工单表 t_ticket，有两个单列索引：

idx_priority（INT，分布极不均匀：P1 占 1.2%、P2 占 8%、P3 占 90.8%）
idx_created_at（DATETIME，近 7 天占 3%）

业务方的高频查询长这样：

SELECT id, title, priority, created_at, assignee_id
FROM t_ticket
WHERE (priority IN (1, 2) AND status = 'OPEN')
   OR created_at >= '2025-01-10 00:00:00'
ORDER BY created_at DESC
LIMIT 50;

注意：status 列没有索引（单列选择性只有 12%，单独建索引性价比不高），所以 WHERE 的两个 OR 分支各自只能命中一个索引。

现象

EXPLAIN 显示 type=ALL 退化为 index_merge，Extra 列出现 Using sort_union(idx_priority, idx_created_at); Using where; Using filesort。
sort_union 路径：P99 = 2,840ms，平均 1,120ms。
我手动 SET optimizer_switch='index_merge_union=off,index_merge_sort_union=off,index_merge_intersection=off' 强制走全表扫描 + filesort：P99 = 3,100ms，平均 1,350ms——只比 sort_union 慢 8%。
但如果把两个 OR 分支拆成 UNION ALL（各自走自己的索引再合并去重），P99 降到 420ms，快了近 7 倍。

也就是说：优化器选了 index_merge sort_union，但它的代价几乎等于全表扫描，而 UNION ALL 改写才是正解。 这个 gap 我没法接受"优化器判断失误"就完了，想搞清楚背后的判断逻辑。

已尝试的排查

ANALYZE TABLE 后重跑，统计信息刷新了但优化器仍然选 sort_union（EXPLAIN FORMAT=JSON 里 cost_info.sort_union.cost 算出来比 scan.cost 低，但实际执行差距不大——说明代价估算模型在这类"OR 两分支选择性都不高 + 最终要 filesort"的场景里偏乐观）。
optimizer_trace（SET optimizer_trace='enabled=on'）：看到优化器在 consider_access_paths_for_reference 阶段确实考虑了 index_merge，但最终在 reorder_index_sort_union 里选了 sort_union 而非 union（因为两个索引的命中行集合有重叠，union 需要额外的去重 sort）。
尝试建联合索引 idx_priority_status：覆盖了第一个 OR 分支，但第二个分支还是走 idx_created_at，EXPLAIN 仍然走 index_merge sort_union，P99 只降到 2,200ms，改善有限。
改成 UNION ALL 改写后（上面提到），效果最好，但要求业务方改 SQL——落地成本高，不是所有场景都能推动。

期望得到的答案形态

我不想要"改成 UNION ALL"这种单点建议（我已经知道），而是想搞清楚一个可复用的判断框架：

什么条件下优化器会选 sort_union 而非 union？ 我目前只看到"命中行集合有重叠"这一个因素，还有没有别的（比如两个索引的命中行数差距、是否涉及 ORDER BY 列、buffer pool 热度）？optimizer_trace 里哪个字段是决定性证据？
sort_union 的代价估算模型具体怎么算的？ 它把"两路索引扫描 + 按 PK 排序合并 + 逐行回表 + 再 filesort"折算成什么 cost？在什么数据分布下会系统性低估（比如我这种"两分支各命中 10% 行、重叠 3%"的场景）？
有没有比"逐条 SQL 改写 UNION ALL"更低落地成本的干预手段？ 比如：能不能在表级别 / 会话级别用 optimizer_switch 精确关闭 sort_union 但保留 union？或者用 stored procedure + PREPARE 在特定数据量阈值下自动切换执行路径？
如果一定要建索引来让优化器放弃 index_merge，应该建什么样的联合索引？ 是 (priority, status, created_at) 三列联合？还是分别给两个 OR 分支各建一个覆盖索引？有没有"让优化器算出 scan cost < sort_union cost"的索引设计原则？

1. 优化器选 sort_union 而非 union 的决策逻辑

MySQL 8.0 的 index_merge 有三种策略，选择顺序在 SQL_SELECT::reorder_index_sort_union() 里：

| 策略 | 触发条件 | 代价模型 | |------|----------|----------| | union | 两路索引扫描结果无重叠（同一 PK 不可能同时命中两个索引） | scan1 + scan2 + 回表 | | sort_union | 两路索引扫描结果有重叠，需按 PK 排序去重 | scan1 + scan2 + sort(total_rows) + 回表 | | intersection | AND 条件（你的场景不适用） | 逐行匹配 |

你的场景为什么走了 sort_union：priority IN (1,2) AND status='OPEN' 和 created_at >= X 这两个分支逻辑上有交集——一条 P1 工单如果同时满足"近 7 天"，就会同时命中两个索引。优化器通过 test_quick_select() 估算两路命中行集合的交集概率，只要 > 0 就选 sort_union 而非 union。

optimizer_trace 里的决定性字段：

SET optimizer_trace='enabled=on';
-- 执行你的查询
SELECT * FROM information_schema.OPTIMIZER_TRACE\G

看 considered_execution_plans → best_access_path → range_analysis 里的 analyzed_rowgroups，重点看两个索引各自的 rows 估算值，以及 reorder_index_sort_union 节点里的 sort_cost。决定性证据是 sort_union 节点的 cost 字段：它 = scan_cost_1 + scan_cost_2 + sort_cost + lookup_cost，其中 sort_cost = total_rows * log2(total_rows) * cost_per_cmp。

除了重叠，还有两个隐藏因素：

ORDER BY 列不在任何一个索引的 leading position：你的 ORDER BY 是 created_at DESC，idx_created_at 的 leading 是 created_at，但 idx_priority 的 leading 是 priority。优化器认为两个索引的产出序不一致 → 必须额外 filesort → 这让"单索引扫描 + filter + filesort"的 cost 被抬高，而 sort_union 的 sort 步骤反而被模型认为"更有序"。
buffer pool 热度假设：优化器的 io_block_size 和 random_read_cost 参数是全局的，不感知你这张 900 万行的表是否热数据。如果热，随机 I/O ≈ 顺序 I/O，sort_union 的随机回表代价被低估；如果冷，差距会放大。

2. sort_union 的代价估算模型 & 系统性低估的根因

MySQL 8.0 的 sort_union cost 公式（简化）：

cost_sort_union = cost_scan(idx1) + cost_scan(idx2)
                + (rows1 + rows2) * log2(rows1 + rows2) * 0.2  -- 排序
                + (rows1 + rows2 - overlap) * random_page_cost  -- 回表

你的数据：

Branch 1: priority IN (1,2) AND status='OPEN' ≈ 10.8% × 12% ≈ 1.3% × 9M ≈ 117K 行
Branch 2: created_at >= 7天前 ≈ 3% × 9M ≈ 270K 行
合计 ≈ 387K 行进入 sort + 回表

系统性低估的三个来源：

LIMIT 50 完全没进 cost 模型。优化器算的是"处理全部 387K 行的代价"，但实际执行到 50 行就该停。对于 sort_union，这个早停不生效（必须先全量排序去重），但对于"单索引有序扫描 + filter"路径，早停意味着只需扫 50~100 行。优化器没有为后者建模这个收益。
回表代价假设。random_page_cost 默认 1.0（InnoDB 默认），意味着每次回表 = 一次随机页读取。387K 次回表 × 1.0 = 387K 个 I/O unit。但实际上：
- 如果 buffer pool 命中率高（你的热表大概率 >90%），实际 I/O ≈ 0
- 如果命中率低，387K 次随机页读在 SSD 上 ≈ 387K × 0.1ms = 38.7s（这就是你看到的 2~3s 量级）
- 优化器用 1.0 的固定系数，不区分热/冷
sort 的常数因子。MySQL 用 0.2 * rows * log2(rows) 作为 sort cost 的估算，这个 0.2 是经验常数，对于 387K 行 ≈ 0.2 × 387K × 18.5 ≈ 1.43M cost units。看起来很大，但对比回表的 387K × 1.0 = 387K，排序反而被高估了——真正贵的是回表，不是排序，但优化器的 cost 公式让排序看起来是主要开销，导致它高估了"换一条路径能省多少"。

一句话：在你的场景里，sort_union 的真实瓶颈是 387K 次随机回表，而优化器的 cost 模型把这个代价摊薄了（假设热数据），同时又没有给"ORDER BY + LIMIT 早停"路径任何加分。

3. 最低落地成本的干预手段（按成本从低到高排序）

方案 A：会话级 optimizer_switch 精确关闭 sort_union（零代码改动，5 分钟落地）

-- 在连接池初始化时执行（MyBatis / Spring DataSource 的 init SQL）
SET SESSION optimizer_switch = 'index_merge_sort_union=off';

效果：优化器不再考虑 sort_union，回退到以下路径之一：

单索引 idx_created_at 范围扫描 + filter（branch 1 的行在扫描中被过滤掉）
全表扫描 + filesort（你已验证 P99=3100ms）
如果建了联合索引（见方案 D），走联合索引

注意：index_merge_sort_union=off 只关 sort_union，不影响 index_merge_union（无重叠时仍可用）和 index_merge_intersection（AND 场景仍可用）。比全关更精确。

验证：

SET SESSION optimizer_switch='index_merge_sort_union=off';
EXPLAIN SELECT ...;
-- 确认 type 不再是 index_merge，而是 range 或 ALL

方案 B：应用层 UNION ALL 改写 + 早停（需改 SQL，但收益最大）

你已经知道 UNION ALL 是最优解，但落地成本高。一个折中：把改写封装在 DAO 层，对业务方透明：

// MyBatis mapper 里拆成两条 SQL
// 1) SELECT id, title, priority, created_at, assignee_id 
//    FROM t_ticket 
//    WHERE priority IN (1,2) AND status = 'OPEN' AND created_at >= #{cutoff}
//    ORDER BY created_at DESC LIMIT 50

// 2) SELECT id, title, priority, created_at, assignee_id 
//    FROM t_ticket 
//    WHERE created_at >= #{cutoff} AND NOT (priority IN (1,2) AND status = 'OPEN')
//    ORDER BY created_at DESC LIMIT 50
// 然后应用层 merge + sort + limit 50

但更简单的做法：因为你的 ORDER BY 是 created_at DESC，直接让两个分支都走 idx_created_at：

SELECT id, title, priority, created_at, assignee_id
FROM t_ticket
WHERE created_at >= '2025-01-10 00:00:00'
  AND (priority IN (1, 2) AND status = 'OPEN' OR created_at >= '2025-01-10 00:00:00')
ORDER BY created_at DESC
LIMIT 50;

化简后：WHERE created_at >= X AND (branch1 OR TRUE) = WHERE created_at >= X。

等等——这里有个关键洞察：你的 WHERE 条件在逻辑上等价于 created_at >= X OR (priority IN (1,2) AND status='OPEN')，但如果 created_at >= X 已经覆盖了大部分命中行（3% vs 1.3%），那这个 OR 的第二分支只贡献了 1.3% 的增量行。

真正最优的执行路径：走 idx_created_at 从大到小扫描，每行检查是否满足 branch 1（priority IN (1,2) AND status='OPEN'），满足则输出，不满足则跳过（因为这些行已经被 branch 2 的 created_at >= X 覆盖了）。LIMIT 50 意味着最多扫 50~200 行就停了。

让优化器走这条路的关键：建一个 (created_at, priority, status) 的复合索引。

方案 C：存储过程 + PREPARE 动态切换（中等成本）

DELIMITER //
CREATE PROCEDURE get_tickets(
    IN p_cutoff DATETIME,
    IN p_limit INT
)
BEGIN
    DECLARE v_recent_rows BIGINT;
    
    -- 估算近 7 天行数
    SELECT COUNT(*) INTO v_recent_rows 
    FROM t_ticket 
    WHERE created_at >= p_cutoff 
    LIMIT 1;  -- 走索引，很快
    
    IF v_recent_rows > 100000 THEN
        -- 热路径：直接走 created_at 索引有序扫描
        SELECT id, title, priority, created_at, assignee_id
        FROM t_ticket
        WHERE created_at >= p_cutoff
        ORDER BY created_at DESC
        LIMIT p_limit;
    ELSE
        -- 冷路径：UNION ALL
        SELECT id, title, priority, created_at, assignee_id
        FROM t_ticket
        WHERE priority IN (1,2) AND status = 'OPEN'
        ORDER BY created_at DESC
        LIMIT p_limit
        UNION ALL
        SELECT id, title, priority, created_at, assignee_id
        FROM t_ticket
        WHERE created_at >= p_cutoff
          AND NOT (priority IN (1,2) AND status = 'OPEN')
        ORDER BY created_at DESC
        LIMIT p_limit;
    END IF;
END //
DELIMITER ;

注意：存储过程方案在连接池复用场景下有个坑——PREPARE 后的计划缓存（prepared_statement_cache）可能缓存了旧计划，数据分布变化后不会自动重编译。建议加 FLUSH PRIVILEGES 或 SET SESSION optimizer_trace='enabled=off' 强制刷新，或者干脆不用存储过程，在应用层用 if-else 判断。

方案 D：复合索引（根治，但 DDL 有代价）

-- 900 万行表，在线 DDL 约 40~90s（取决于 SSD 和 buffer pool 大小）
-- 用 pt-online-schema-change 或 gh-ost 避免锁表
ALTER TABLE t_ticket 
  ADD INDEX idx_created_priority_status (created_at, priority, status);

为什么这个索引能根治：

created_at 做 leading → ORDER BY 免 filesort
priority, status 做 covering → 回表时只需在索引内验证 branch 1 条件，不需要回表取 priority 和 status
LIMIT 50 + 有序扫描 → 最多扫 50~200 行就停
关键：这个索引让优化器看到的 cost 是 50~200 × index_page_cost，远低于 sort_union 的 387K × random_page_cost，自然就会选这条路径，不需要关 optimizer_switch

DDL 代价评估（900 万行 × 16 列 ≈ 2.3GB）：

pt-online-schema-change：约 40~90s（SSD），期间额外 I/O 约 4.6GB（建新表 + 数据拷贝）
gh-ost：类似，但支持在线暂停（--cut-over=atomic 前可暂停）
风险：DDL 期间 Innodb_buffer_pool_wait_free 会短暂上升，如果有大事务（>100K 行 UPDATE）并发，DDL 会被阻塞

验证：

EXPLAIN SELECT id, title, priority, created_at, assignee_id
FROM t_ticket
WHERE created_at >= '2025-01-10'
  OR (priority IN (1,2) AND status = 'OPEN')
ORDER BY created_at DESC
LIMIT 50;
-- 期望：type=range, key=idx_created_priority_status, Extra=Using index condition

4. 回归防护：怎么防止优化器在未来重新选回 sort_union

这是大多数团队忽略的环节——你修好了，三个月后数据分布变了（比如 P1 工单占比从 10.8% 涨到 25%，或者 created_at 的基数因为归档策略变了），优化器可能悄悄切回 sort_union，而你的 P99 告警阈值还是旧的，没人发现。

4.1 计划指纹监控（推荐）

-- 每小时跑一次（cron 或应用内定时任务）
-- 抓取这条 SQL 的执行计划指纹
SET optimizer_trace='enabled=on';
SELECT id, title, priority, created_at, assignee_id
FROM t_ticket
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)
  OR (priority IN (1,2) AND status = 'OPEN')
ORDER BY created_at DESC
LIMIT 50;
SELECT * FROM information_schema.OPTIMIZER_TRACE\G
SET optimizer_trace='enabled=off';

抓取的指纹字段：

best_access_path.access_type（range / ALL / index_merge）
best_access_path.key（用了哪个索引）
range_analysis.possible_keys（候选索引列表）
considered_execution_plans.best_access_path.cost（cost 值）

告警规则：

# 如果 access_type 变成 index_merge 或 ALL → 优化器切回了坏路径
alert: mysql_plan_regression
  when plan_fingerprint.access_type in ['index_merge', 'ALL']
    AND plan_fingerprint.key != 'idx_created_priority_status'
  severity: warning  # 先告警，不自动切换

4.2 数据分布漂移监控

-- 每天跑一次，监控两个关键比例
SELECT 
  COUNT(*) AS total,
  SUM(CASE WHEN priority IN (1,2) AND status='OPEN' THEN 1 ELSE 0 END) AS branch1_rows,
  SUM(CASE WHEN created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY) THEN 1 ELSE 0 END) AS branch2_rows,
  ROUND(SUM(CASE WHEN priority IN (1,2) AND status='OPEN' THEN 1 ELSE 0 END) / COUNT(*) * 100, 2) AS branch1_pct,
  ROUND(SUM(CASE WHEN created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY) THEN 1 ELSE 0 END) / COUNT(*) * 100, 2) AS branch2_pct
FROM t_ticket;