复合索引选择性阈值：tenant 行数占比多高时优化器会从 index scan 切到全表扫？有没有可量化的判据或稳定压住计划的手段？

陈知远-612 已解决

MySQL复合索引选择性租户慢查询治理优化器统计信息

背景

我负责一套 SaaS 多租户订单库，MySQL 8.0.36（InnoDB，16C64G，buffer_pool 48G），核心表 t_order 约 1.2 亿行，联合索引 idx_tenant_time (tenant_id, created_at)。业务上 95% 的查询都带 tenant_id，按「大多数查询带它」的假设建了这个索引。

现象

最近 3 个月租户从 200 涨到 800，其中 3 个头部租户合计占全表约 47% 的行。同一 SQL：

SELECT id, amount, status FROM t_order
WHERE tenant_id = ? AND created_at >= ? ORDER BY created_at DESC LIMIT 20;

小租户（<1% 占比）：走 idx_tenant_time，P99 约 3ms；
头部租户（单 tenant 占 15%+）：EXPLAIN 直接切到 type=ALL 全表扫，P99 飙到 2.8s；
同一个租户，白天低峰走索引、晚高峰又切回全表扫（怀疑统计信息刷新时机影响，但还没验证）。

已尝试

把 idx_tenant_time 改成覆盖索引 idx_tenant_time_cover (tenant_id, created_at, id, amount, status)，头部租户仍然全表扫——说明不是回表成本的问题；
ANALYZE TABLE 后立即查，头部租户依然 ALL；小租户正常；
用 FORCE INDEX (idx_tenant_time) 压住计划，头部租户 P99 从 2.8s 降到 180ms——说明索引本身是可用的，纯粹是优化器 cost 判断偏了；
建了 information_schema 上的 histogram（ANALYZE TABLE ... UPDATE HISTOGRAM），计划暂时稳定，但 24h 后批跑写入大量数据后又漂移回 ALL。

期望的答案形态

可量化的判据：tenant 行数占比（或 cardinality(tenant_id) 与总行数的比值）到什么阈值附近，优化器会倾向切全表扫？有没有从 information_schema.INDEX_STATISTICS / mysql.innodb_index_stats 直接算出这个临界值的方法？
稳定压住计划的手段：FORCE INDEX 硬编码 vs 应用层按 tenant 路由不同 SQL vs 分区表按 tenant_id 分，哪种在 800 个租户、3 个头部占 47% 的场景下运维成本最低？
统计信息漂移的治理：histogram 24h 后失效，有没有比「定时 ANALYZE」更轻量的方案（比如按写入量触发、或 per-tenant 统计）？

参考

站内已有几篇讨论租户分布与索引取舍的文章，但都偏向「列序怎么选」和「覆盖索引值不值」，没覆盖「同一索引对不同租户走不同计划」这个动态漂移问题：

《加了索引还是慢：索引选择性、覆盖索引与回表成本的一次量化拆解》
《复合索引首列选择性退化：8800 万行工单表 status 只有 3 个值，大租户点查优化器偏全表扫》

引用 #732 加了索引还是慢：索引选择性、覆盖索引与回表成本的一次量化拆解 #1601 复合索引首列选择性退化：8800 万行工单表 status 只有 3 个值，大租户点查优化器偏全表扫，拆索引还是别的方案？#1492 InnoDB 二级索引列序怎么定：长尾租户（5 个头部占 62% 数据）下 (tenant_id, created_at) vs (created_at, tenant_id) 的量化取舍

回答 1 条

沈若衡-956 已采纳

引用 #732 加了索引还是慢：索引选择性、覆盖索引与回表成本的一次量化拆解 #1601 复合索引首列选择性退化：8800 万行工单表 status 只有 3 个值，大租户点查优化器偏全表扫，拆索引还是别的方案？

结论先行：你这不是"优化器偏了"，是 InnoDB cost model 在 leaf_pages / data_pages 的比值越过一个临界点后，全表扫的"顺序 IO 优势"在账面上赢了索引的"随机 IO 劣势"——这个临界点跟"租户占比 5%"没有直接关系，跟索引叶子页数 / 数据页数才有直接关系。你那个"头部 15% 走 ALL、小租户走 index"的差异，本质是同一 SQL 在两条路径上 cost 估算的比值刚好卡在了阈值两侧。下面给判据、给稳定手段、给漂移治理，都是可直接落地的。

一、可量化判据：不是"占比 5%"，是"叶子页 / 数据页"的比值

MySQL 8.0 的 cost model 对两条路径的估算（简化到关键因子）：

index_scan_cost ≈ index_rows × (index_depth + 1) × innodb_random_read_cost
                 （+1 是回表那一次随机 IO；覆盖索引时这一项消失）

table_scan_cost ≈ data_pages × 1.0
                 （顺序读，每 page 一次 IO；InnoDB 默认 innodb_random_read_cost=1.0）

优化器切全表扫的临界条件（覆盖索引）：

index_rows × index_depth ≈ data_pages
→ index_rows ≈ data_pages / index_depth

这就是你要的判据——它可以直接从 mysql.innodb_index_stats 算出来：

-- 看 idx_tenant_time 的叶子页数
SELECT index_name, stat_name, stat_value
FROM mysql.innodb_index_stats
WHERE database_name='your_db' AND table_name='t_order'
  AND stat_name IN ('size', 'leaf_data', 'n_diff_pfx01');

-- size 单位是 16KB page；n_diff_pfx01 是 tenant_id 这一列的 distinct 数

判据落地公式（覆盖索引场景，depth=3）：

切全表扫临界 rows ≈ size_pages / 3

代入你的场景：t_order 1.2 亿行，假设平均 row_size ≈ 80B → data_pages ≈ 1.2e8 × 80 / 16384 ≈ 586K pages。头部 15% 租户的 index_rows ≈ 1.8M，1.8M × 3 = 5.4M > 586K，账面上索引输了 9 倍，切 ALL 是优化器"算对了"。

验证方法：用 EXPLAIN FORMAT=JSON 抓 cost_info 字段，对比 query_cost 和 plan_info.cost_info.query_cost——你那个 2.8s 的 ALL 计划，query_cost 一定是个 4-5 位的数，而 FORCE INDEX 那条 180ms 的计划 cost 应该只有 3 位。cost 差 5 倍以上，说明优化器不是"偏"，是账面上确实觉得全表扫便宜。

关键认知：innodb_random_read_cost 这个参数默认值 1.0，在 buffer pool 命中率高的场景是偏保守的——你 48G buffer pool 装得下 586K pages 的热点集，实际"随机 IO"很多是命中内存的，cost model 没感知到。这就是为什么 FORCE INDEX 后 P99 只有 180ms 而 cost model 觉得要 2.8s。优化器的 cost model 不知道你的 buffer pool 命中率。

二、稳定压住计划的手段：推荐"分区 + hint"双层，而不是二选一

800 租户 + 3 头部占 47%，三个方案的运维成本排序（低→高）：

方案 A（推荐）：LIST 分区 + 应用层 hint

-- 1. 给 3 个头部租户单独建分区（LIST on tenant_id）
ALTER TABLE t_order PARTITION BY LIST COLUMNS(tenant_id) (
  PARTITION p_tenant_a VALUES IN (1001),
  PARTITION p_tenant_b VALUES IN (2042),
  PARTITION p_tenant_c VALUES IN (3307),
  PARTITION p_other VALUES IN ( /* 其余 797 个租户 */ )
);

-- 2. 头部租户的查询加 hint（MySQL 8.0.14+ 优化器 hint）
/*+ INDEX(t idx_tenant_time_cover) */ SELECT ... WHERE tenant_id=1001 ...

-- 3. 长尾租户的查询不加 hint，让优化器自己选

为什么这个组合最划算：

分区后头部租户的 index_rows 从 1.8M 降到单分区内的量（假设 tenant_a 占 20% → 2.4M 行但叶子页只扫到该租户的），data_pages 也跟着降到该分区的 page 数，临界比值回到索引占优的一侧，优化器自然走索引；
长尾租户（单租户 < 1%）本来就走索引，分区不影响；
hint 是兜底，防止分区后 cost model 仍然漂移；
运维成本：一次性 DDL（注意 1.2 亿行的 ALTER 要走 pt-online-schema-change 或 gh-ost，别裸 ALTER），之后零维护。

方案 B（次选）：应用层路由不同 SQL

HEAVY_TENANTS = {1001, 2042, 3307}  # 维护一份"大租户"清单
def query_orders(tenant_id, since, limit=20):
    if tenant_id in HEAVY_TENANTS:
        return db.execute(
            "SELECT /*+ INDEX(t idx_tenant_time_cover) */ ...", ...)
    else:
        return db.execute("SELECT ... WHERE tenant_id=? ...", ...)

不推荐的原因：HEAVY_TENANTS 清单要跟着租户增长维护，3 个月后可能变成 5 个、10 个，每次都要改代码重新发版；而且"大租户"边界模糊（15% 算大、10% 算不算？）。

方案 C（不推荐）：FORCE INDEX 全量硬编码

你已经在用这个验证了，但长期维护成本最高——任何 schema 变更（加列、加索引）都可能让 hint 失效，且无法区分大小租户。

三、统计信息漂移治理：按写入量触发，不要定时

ANALYZE TABLE 24h 后漂移的根因：批跑写入大量数据后，n_diff_pfx01（tenant_id 的 distinct 数）和 cardinality 都变了，但 histogram 是采样快照，不会自动刷新。

轻量方案：用触发器 + 计数器，按写入量阈值触发：

-- 1. 建一张计数器表
CREATE TABLE t_analyze_counter (
  table_name VARCHAR(64) PRIMARY KEY,
  writes_since_analyze BIGINT DEFAULT 0,
  last_analyze_at DATETIME
);

-- 2. 触发器（AFTER INSERT / UPDATE / DELETE）
DELIMITER //
CREATE TRIGGER trg_order_writes AFTER INSERT ON t_order
FOR EACH ROW
BEGIN
  UPDATE t_analyze_counter
  SET writes_since_analyze = writes_since_analyze + 1
  WHERE table_name = 't_order';
END //

-- 3. 定时任务（每 10min 跑一次，看阈值）
-- 阈值建议：writes_since_analyze > 500K 时触发 ANALYZE
-- （500K 大约是你批跑一次写入的量级，按你的实际批跑大小调）

为什么不直接"定时 ANALYZE"：ANALYZE 本身有开销（1.2 亿行的表采样 20000 行，耗时 5-30s），且每次 ANALYZE 都会刷新统计信息，可能让原本稳定的计划突然漂移——你那个"白天走索引、晚高峰切回 ALL"的现象，很可能就是某个定时任务在晚高峰跑了 ANALYZE。按写入量触发比定时触发更精准：没有批跑写入时不刷新，有批跑时立刻刷新。

进阶：用 information_schema.INDEX_STATISTICS（8.0.30+）监控 rows_selected 的变化，当某租户的 rows_selected 突变 > 50% 时告警——这是"统计信息漂移"的早期信号，比 ANALYZE 后看 EXPLAIN 更早。

四、一个开放问题留给你

你的场景里，3 个头部租户占 47%，这个分布其实非常"偏"——正常 SaaS 的租户分布是幂律的（头部 10% 占 80%+），47% 说明你的 3 个头部租户可能是大客户 / 内部系统，而不是"普通租户里的头部"。如果是这种情况，分区方案之外还可以考虑把这 3 个租户的数据拆到独立表（t_order_tenant_a / t_order_tenant_b / t_order_tenant_c），应用层路由——这比 LIST 分区更干净，因为分区的 DDL 成本（1.2 亿行的 ALTER）比建 3 张独立表高。

你们这 3 个头部租户是"大客户"还是"普通租户里的头部"？这个信息会改变推荐方案。

举报该内容