先给结论：优化器翻转的判据不是「命中行占比」，而是「回表随机 I/O 总代价 vs 全表顺序扫描代价」的比值——具体公式是 R × c_random > (P_table − P_index) × c_sequential，其中 R 是回表行数，c_random/c_sequential 取决于页面在不在 buffer pool。你 41 万行回表 × 1.0（磁盘随机读）= 410K，而全表 2.5M 页 × 0.1（BP 顺序读）= 250K，410K > 250K，优化器「理性地」选全表扫。但实际跑出来全表扫 6.8s > 索引 340ms，说明 cost model 对「哪些页在 BP」的假设和真实热数据分布不一致。
一、cost model 公式拆解（MySQL 8.0.34 InnoDB）
InnoDB 优化器对两种路径的代价估算：
# 索引路径（你的场景）
cost_index = P_index × c_page + R × c_lookup
P_index = 索引叶子页数（扫描范围）
c_page = 顺序读页代价（BP 命中 ≈ 0.1，磁盘 ≈ 1.0）
R = 回表行数（= 命中行数，因为 deleted_at 不在索引里）
c_lookup = 每次回表的随机读代价（BP 命中 ≈ 0.1，磁盘 ≈ 1.0）
# 全表扫描
cost_full = P_table × c_page_table
P_table = 聚簇索引总页数
c_page_table = 顺序读页代价
翻转临界条件：
R × c_lookup > (P_table − P_index) × c_page_table
代入你的数据（5200 万行 / 38GB / 16KB 页）：
- P_table ≈ 38GB / 16KB ≈ 2,496,000 页
- 你的大商户 R = 41 万行
- 若回表页 不在 BP（c_lookup = 1.0），全表页 在 BP（c_page = 0.1）：
- 410,000 × 1.0 = 410K > (2,496,000 − 8,000) × 0.1 ≈ 249K → 选全表扫 ✓
- 若回表页 在 BP（c_lookup = 0.1）：
- 410,000 × 0.1 = 41K < 249K → 选索引 ✓
所以翻转的临界回表行数：
R_critical = (P_table − P_index) × c_page_table / c_lookup
| 场景 | c_page | c_lookup | R_critical | |------|--------|----------|------------| | 全在 BP | 0.1 | 0.1 | ≈ 2,488,000 行（几乎不翻转）| | 回表冷、表热 | 0.1 | 1.0 | ≈ 248,800 行（你的 41 万已超）| | 回表热、表冷 | 1.0 | 0.1 | ≈ 24,880,000 行（不可能翻转）| | 全冷 | 1.0 | 1.0 | ≈ 2,488,000 行 |
你的大商户 41 万行刚好落在「回表冷」场景的翻转区——因为 96 万行的数据散布在聚簇索引的 2 万+ 页上，这些页在 40GB BP 里和其他 5200 万行的页竞争，热商户的索引页热但回表页冷。
二、可复现的验证脚本
给你一个能画出「命中行占比 vs 优化器选择」拐点曲线的完整步骤（MySQL 8.0.34）：
-- 1. 建测试表（模拟你的结构）
CREATE TABLE t_bench (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
merchant_id BIGINT NOT NULL,
status VARCHAR(16) NOT NULL,
created_at DATETIME NOT NULL,
amount DECIMAL(12,2),
settle_no VARCHAR(64),
deleted_at DATETIME DEFAULT NULL,
KEY idx_merchant_status_time (merchant_id, status, created_at)
) ENGINE=InnoDB;
-- 2. 灌 1000 万行（1 个商户 50 万行，软删比例从 10% 到 60% 分 6 批）
-- 用 stored procedure 或批量 INSERT，每批控制 deleted_at IS NULL 比例
-- 关键：让 deleted_at 值随机散布，模拟真实数据分布
-- 3. 对每个软删比例，执行：
SET optimizer_trace="enabled=on";
EXPLAIN SELECT id, settle_no, amount, status, created_at
FROM t_bench
WHERE merchant_id = 1 AND status = 'SUCCESS' AND deleted_at IS NULL
ORDER BY created_at DESC LIMIT 20;
-- 4. 读 trace 里的 cost 明细
SELECT JSON_EXTRACT(trace, '$.steps[*].considered_execution_plans[*].cost_info')
FROM information_schema.OPTIMIZER_TRACE;
-- 5. 对比实际耗时（跑 100 次取 P50/P99）
ANALYZE FORMAT=JSON SELECT ... ;
-- 注意：ANALYZE FORMAT=JSON 会真执行，看 actual_time 和 actual_rows
关键观测点：
OPTIMIZER_TRACE里best_access_path的cost字段会拆出rows、filtered、cost，但不直接拆出「索引扫描 cost」和「回表 cost」各多少——这是 MySQL 8.0 的已知局限。- 要拆出来，用
SHOW PROFILE FOR QUERY或ANALYZE FORMAT=JSON的actual_time对比：索引扫描时间（无回表时）vs 总时间（含回表），差值就是回表 cost 的实测值。 - 画拐点：x 轴 = 软删比例（10%→60%），y 轴 = 优化器选择（索引/全表），标记翻转点。
预期结果：在「回表页冷」条件下，翻转点大约在回表行数 / 表总行数 ≈ 5%~10% 处出现（对应你的 41万/5200万 ≈ 0.8%，但因为回表页冷，实际翻转阈值更低）。
三、三方案取舍账（软删比例持续增长前提下）
| 方案 | 失效时间点 | 失效原因 |
|------|-----------|---------|
| A. 把 deleted_at 挪进索引 (merchant_id, status, deleted_at, created_at) | 软删比例 > 50% 时失效 | deleted_at IS NULL 占 50% 以上时，索引内仍有一半行要扫；且 ORDER BY created_at 退化为 filesort（因为 deleted_at 在 created_at 前面，无法利用索引排序），大商户 41 万行 filesort 内存代价 ≈ 41万 × 80B ≈ 33MB/查询，QPS 上去后 OOM 风险 |
| B. 定期重建索引（类似 VACUUM） | 写入 QPS > 5000 时失效 | 5200 万行 Online DDL 约 20~30 分钟（pt-osc 更久），期间写放大 2x；1200 写 QPS 下可以接受，但如果业务涨到 5000+，DDL 窗口和业务争 IO 会导致 P99 抖动。另外重建频率受软删增速限制：如果每天新增 5 万软删行，每月软删比例涨 0.3%，每季度就得重建一次 |
| C. 改写 SQL 去掉 ORDER BY，应用层排序 | 命中行数 > 10 万时失效 | 应用层排序 41 万行 × 5 字段 ≈ 33MB 内存 + CPU 排序耗时 ≈ 50~100ms（Java Arrays.sort），叠加网络传输 41 万行 JSON ≈ 200MB，不可行。只适合命中行数 < 1 万的场景 |
我的建议（基于你的数据）：
你的核心矛盾是「deleted_at 是等值过滤（IS NULL）但不在索引里，导致回表」。最干净的解法其实是 方案 A 的变体：
-- 不要 (merchant_id, status, deleted_at, created_at)
-- 而是建一个「只存活跃行」的索引：
-- 用生成列 + 条件索引思路（MySQL 不支持条件索引，用生成列模拟）
ALTER TABLE t_settle_detail
ADD COLUMN active_flag TINYINT GENERATED ALWAYS AS
(IF(deleted_at IS NULL, 1, 0)) STORED,
ADD INDEX idx_active (merchant_id, status, active_flag, created_at);
-- 查询改为：
WHERE merchant_id = ? AND status = 'SUCCESS' AND active_flag = 1
为什么这样更好：
active_flag是 0/1 两值，选择性比deleted_at IS NULL（占 72%）好得多——优化器能精确定位active_flag=1的连续区间created_at仍在索引最后，ORDER BY 不退化- 已删除行（active_flag=0）在 B+ 树里和活跃行（active_flag=1）物理分离，索引扫描只碰活跃行
- 代价：多一个 STORED 列（1 字节/行 × 5200万 ≈ 52MB 额外空间），可忽略
配合每周归档（把 180 天前的软删行物理 DELETE），active_flag=0 的行会持续减少，索引选择性只会越来越好。
四、一句话总结
优化器不是「只看叶子页数」，它是把回表算成随机读的——你的 41 万行回表 × 随机读代价（1.0）超过了全表顺序扫（2.5M × 0.1），所以它选了全表扫。问题不是 cost model 有 bug，而是它假设回表页在磁盘上（c=1.0），但实际你的热商户回表页部分在 BP（c≈0.3~0.5），导致 cost model 高估了索引路径的代价。解法不是 FORCE INDEX（治标），而是让索引本身不需要回表（active_flag 生成列 + 索引内等值过滤），彻底消除随机 I/O 项。
引用依据：539 号文章（《软删 31% 后点查 P99 从 9ms 涨到 520ms》）验证了「回表随机 IO 是 P99 劣化主因」和「buffer pool 命中率是先行指标」，我的回答在此基础上补充了 cost model 的量化公式和翻转临界条件。