Skip to content

4.1-6 索引设计与优化:联合索引、覆盖索引、索引下推 ​

所属:04 数据存储 / 4.1 MySQL 分级:A 状态:◐ 初稿 关联:4.1-5、4.1-7、4.1-8、4.1-9、4.1-10、4.1-12、4.1-16

  • 题型:设计 / 调优 / 场景

  • 一句话结论:索引设计不是“给 WHERE 列都加索引”,而是从查询模式、数据分布和写入成本出发,设计少量高复用的联合索引:等值列在前、范围列在后、排序/分组列尽量接上;高频查询尽量做成覆盖索引,减少回表;索引下推(ICP)把索引列条件下推到引擎层,减少回表但不减少索引扫描;最后用 EXPLAIN、optimizer_trace、慢日志和压测验证,不能凭直觉。

  • 分层要点:

    1. 先看查询模式,不是先看列:索引设计的输入是 Top SQL 及其 QPS/延迟目标、谓词形态(等值/范围/排序/分页/join/聚合)、数据分布(基数、选择性、倾斜、NULL)、写入成本(索引数量、写放大、DDL)和存储/BP 成本。先抓 Top 20% 的 SQL,不为低频报表牺牲核心写路径;没有慢日志和 performance_schema 数据就设计索引,基本是拍脑袋。
    2. 联合索引的列序:优先级是“等值列 → 范围列 → 排序/分组列”;最左前缀决定哪些查询能用。多个等值列的顺序对当前查询影响不大,但会影响其他查询的前缀复用、排序和覆盖,所以要按“最常用的前缀”排,而不是机械地把选择性最高的列放最左。范围列之后的列不能继续做等值/范围定位,但可以被 ICP 过滤、被覆盖索引读取;能否用于排序要看 ORDER BY 是否以范围列为起点。
    3. 覆盖索引:查询所需列都在索引里(二级索引隐含 PK),不用回表;EXPLAIN 出现 Using index。收益是少一次随机 IO、少读整行;代价是索引变宽、写放大、BP 占用变多。SELECT *、长 VARCHAR、大字段会破坏覆盖;覆盖索引也可能因为二级索引 MVCC 判断而回表(4.1-3)。
    4. 索引下推 ICP:5.6 引入,把索引列上的 WHERE 条件下推到 InnoDB,在索引记录上先过滤再回表;EXPLAIN 出现 Using index condition。它减少回表次数,但不减少索引扫描范围;对联合索引中“范围列之后的列”特别有用。条件下推需要条件只引用索引列;子查询等不能下推。
    5. MRR、index merge、skip scan:MRR 把待回表的主键排序后批量读,降低随机 IO;index merge 合并多个单列索引的结果,常见于 OR/多条件,往往是缺联合索引的信号;skip scan(8.0.13)在前导列基数很低且未被条件使用时跳过前导列。三者都是优化器的兜底手段,不是设计目标,可能比正确联合索引更慢。
    6. 选择性与前缀索引:单列选择性 = COUNT(DISTINCT col)/COUNT(*);联合索引看组合区分度。低选择性列单独建索引收益有限,但可以作为覆盖索引的一部分或用于排序。长字符串用前缀索引,长度用 COUNT(DISTINCT LEFT(col,n))/COUNT(*) 逼近全列区分度;前缀索引不能覆盖完整列值,也不能支持按完整列排序。
    7. 失效与边界:列上函数/表达式、隐式类型转换、前导 %、OR 连接非索引列、NOT IN/!= 低选择性、字符集/排序规则不一致、统计信息过期、优化器成本判断都会让索引用不上或不被选;8.0 的函数索引、直方图、降序索引能缓解部分问题,但不能滥用(4.1-7)。
    8. 验证与治理:慢日志找 Top SQL,EXPLAIN 看 key/key_len/rows/filtered/Extra,optimizer_trace 看候选成本,EXPLAIN ANALYZE 看实际行数;上线前压测读 RT、写 TPS、BP 命中和索引体积;定期用 sys.schema_unused_indexes、sys.schema_redundant_indexes 治理,但删除前要覆盖完整业务周期和从库/报表场景。
  • 图/流程:

text
索引设计流程:
  1. 采样 Top SQL(慢日志 / performance_schema / 业务埋点)
  2. 归类查询模式:等值、范围、排序、分页、join、聚合
  3. 设计候选联合索引:等值 → 范围 → 排序/覆盖
  4. EXPLAIN / optimizer_trace 验证:key、key_len、rows、filtered、Extra
  5. 压测:读 RT、写 TPS、BP 命中、索引体积
  6. 灰度上线 + 监控;定期治理冗余/未使用索引
text
列序决策:
WHERE a=? AND b=? AND c>? ORDER BY d
  等值:a, b
  范围:c
  排序:d(c 是范围,d 不能接着排序)
  → 候选 (a,b,c):c 走范围,d 需要 filesort
  → 备选 (a,b,d,c):d 有序,c 只能 ICP/过滤
  用 EXPLAIN + 压测二选一,不凭直觉
text
二级索引扫描的三种路径:
  ├─ 覆盖索引:索引里有全部查询列 → 直接返回(Using index)
  ├─ ICP:索引列条件下推 → 先过滤,减少回表次数(Using index condition)
  └─ 普通回表:每个匹配行按 PK 查聚簇索引
        └─ MRR:把 PK 排序后批量回表,降低随机 IO(Using MRR)
text
索引失效排查:
EXPLAIN: type=ALL / key=NULL
  → 列上有函数/隐式转换? → 改写 / 函数索引
  → 前导 % / OR 非索引列? → 全文/ES / UNION
  → 违反最左前缀? → 调整索引
  → 范围列后还想定位? → 改列序 / 接受 ICP
  → 统计过期/倾斜? → ANALYZE / 直方图
  → 回表成本太高? → 覆盖索引 / 接受全表
  • 版本/源码锚点:

    • MySQL 5.6:ICP、MRR、BKA。
    • MySQL 5.7:optimizer cost tables(server_cost/engine_cost);innodb_stats_persistent 默认 ON。
    • MySQL 8.0:直方图;不可见索引、降序索引;8.0.13 skip scan、函数索引;8.0.18 EXPLAIN ANALYZE、hash join。
    • 关键默认值:eq_range_index_dive_limit=200(8.0,5.7 为 10)、range_optimizer_max_mem_size=8MB、read_rnd_buffer_size=256KB、innodb_stats_persistent_sample_pages=20、innodb_stats_auto_recalc=ON、innodb_online_alter_log_max_size=128MB。
    • optimizer_switch 相关开关:index_condition_pushdown、mrr、mrr_cost_based、index_merge、index_merge_union、index_merge_sort_union、index_merge_intersection、use_index_extensions、skip_scan。
    • 源码:sql/opt_range.cc(range 访问路径与索引选择)、sql/opt_costmodel.cc(成本模型)、sql/opt_explain.cc(EXPLAIN)、sql/sql_select.cc / sql/join_optimizer(连接与计划)、storage/innobase/row/row0sel.cc(row_search_mvcc、ICP 执行)、handler/ha_innodb.cc(index_read_map)、dict/dict0stats.cc(统计信息)。
    • 成本表:mysql.server_cost、mysql.engine_cost;8.0 默认 io_block_read_cost=1.0、memory_block_read_cost=0.25、row_evaluate_cost=0.1、key_compare_cost=0.05。
  • 查询模式 → 索引设计:

查询模式推荐索引关键点
单列等值(col)高选择性;可加列做覆盖
多列等值(a,b,...)顺序看前缀复用;当前查询等值列顺序影响小
等值 + 范围(eq..., range)范围列放最后定位
等值 + 排序(eq..., sort...)避免 filesort;范围列会截断
范围 + 排序视取舍ORDER BY 以范围列为起点可沿用索引顺序
分页(filter..., order..., PK)覆盖/延迟关联/游标
Join被驱动表 (join_col, filter...)小表驱动大表
聚合(group_col, agg_col)覆盖,避免临时表
前缀匹配(col) 或前缀索引LIKE 'x%'
多条件 OR联合索引 / UNIONindex merge 只是兜底
  • 联合索引列序规则:
规则正确做法常见反例
等值在前(a,b,c) 对应 a=? AND b=? AND c>?(c,a,b) 把范围放最左
范围在后范围列后不用于定位以为 (a,c,b) 能用 b 定位
排序接上(a,b) 对应 a=? ORDER BY b只建 (a) 导致 filesort
多等值顺序看前缀复用/排序/覆盖盲目“选择性最高放最左”
范围 vs 排序按选择性/limit 压测取舍一律范围优先或一律排序优先
覆盖列把 SELECT 列放进索引SELECT * 破坏覆盖
  • 覆盖索引 / ICP / MRR / index merge / skip scan:
手段解决什么不解决什么EXPLAIN 信号代价
覆盖索引不回表不减少索引扫描;不保证 MVCC 不回表Using index索引变宽、写放大
ICP减少回表次数不减少索引扫描范围Using index condition条件下推限制
MRR降低回表随机 IO不减少回表次数Using MRR排序/缓冲开销
index merge合并多个单列索引常是缺联合索引;多次扫描+合并Using union/intersectCPU/IO
skip scan跳过前导列只适合低基数/特定成本Using index for skip scan可能更慢
  • 索引失效速查:
场景结果处理
列上函数/表达式通常失效改写范围/函数索引(8.0.13)
隐式类型转换失效/低效类型一致/显式转换
LIKE '%x'失效全文/ES/反向索引
违反最左前缀失效调整索引/补前缀
范围列后定位不能定位ICP/调整列序
OR 非索引列易全表UNION/补索引
!=/NOT IN常低效改写/接受
字符集/排序规则不一致失效统一 collation
统计过期/倾斜选错ANALYZE/直方图
小表/低选择性全表更便宜接受
  • 索引治理视图/工具:
视图/工具用途注意
sys.schema_unused_indexes未使用索引统计窗口/从库/报表场景
sys.schema_redundant_indexes冗余索引前缀/唯一性判断
performance_schema.table_io_waits_summary_by_index_usage索引使用计数重启/重置后重新观察
pt-duplicate-key-checker重复索引Percona Toolkit
SHOW INDEX列序/基数Cardinality 是估算
mysql.innodb_index_stats统计细节n_diff_pfx
information_schema.COLUMN_STATISTICS直方图8.0
EXPLAIN / optimizer_trace计划/成本结合 EXPLAIN ANALYZE
  • 关键参数:
参数默认作用提示
optimizer_switch多开关ICP/MRR/index_merge/skip_scan按版本验证
eq_range_index_dive_limit200(8.0)大 IN 列表改用统计过大估算粗
range_optimizer_max_mem_size8MBrange 优化内存超限退化
read_rnd_buffer_size256KBMRR 排序缓冲太小多次回表
innodb_stats_persistent_sample_pages20统计采样页越大越准、ANALYZE 越慢
innodb_stats_auto_recalcON自动重算统计约 10% 行变化触发
innodb_online_alter_log_max_size128MBOnline DDL 期间 DML 日志超限 DDL 失败
innodb_fill_factor100索引重建填充率80–90 预留增长空间
  • 深挖点(面试官常追的 6 个):

    1. “选择性最高的列放最左”不是万能法则:如果查询对所有列都是等值,(a,b) 和 (b,a) 都能用上两列,扫描行数差不多;顺序真正影响的是“其他查询能否复用前缀”、能否避免排序、能否覆盖,以及优化器的基数估算。只有当查询只用到其中一个列作为前缀时,才需要优先把那个列放最左。
    2. 范围列之后的列:定位、排序、ICP 要分开看:范围列后不能继续做等值/范围定位;但 ORDER BY 以范围列为起点时,索引顺序仍可用((a,b,c) 对 a=? AND b>? ORDER BY b,c 可避免 filesort);而 ORDER BY c 不行。ICP 则可以在索引层过滤 c,减少回表。三个问题不要混为一谈。
    3. 覆盖索引的收益与成本:收益是少一次回表随机 IO、少读整行;成本是索引变宽、每个二级索引写入变多、BP 能缓存的索引页变少。长 VARCHAR/大字段、SELECT * 会让覆盖失效;覆盖索引也不等于绝对不回表,二级索引的 MVCC 可见性判断在部分场景仍要访问聚簇索引(4.1-3)。
    4. ICP 只减少回表,不减少索引扫描:它把索引列条件下推到引擎,在索引记录上先过滤;扫描的索引范围没变,减少的是“不满足条件的行去回表”的次数。条件必须只引用索引列;覆盖索引场景下 ICP 的收益会下降。
    5. index merge / skip scan 是兜底,不是设计目标:出现 Using union/intersect 往往说明缺一个合适的联合索引;skip scan 只在前导列低基数、优化器认为划算时生效。把这两者当长期方案,通常会在数据量/分布变化后翻车。
    6. 统计信息、直方图与成本模型决定“选不选”:统计是采样估算,数据倾斜时平均值会骗人;8.0 直方图能改善倾斜列的估算;回表成本、ORDER BY ... LIMIT、函数/隐式转换都会改变成本。优化器选错时先看 optimizer_trace 的候选成本,再决定改 SQL、补索引还是更新统计。
  • 追问链:

    1. 索引设计要看哪些输入? → Top SQL 与 QPS/延迟目标、谓词形态、数据分布(基数/倾斜/NULL)、写入成本、存储/BP 成本;先抓核心路径,不为低频 SQL 过度建索引。
    2. 联合索引的列序怎么定? → 等值列 → 范围列 → 排序/分组列;同时考虑最左前缀复用、排序、覆盖和选择性;没有一套顺序能适配所有 SQL。
    3. 多个等值列,谁放最左? → 当前查询所有等值列都会被用上,顺序影响不大;按“最常用的前缀”和排序/覆盖需求排,而不是只看单列选择性。
    4. 范围列为什么放后面? → 范围列会截断后续列的定位能力;把它放最后,可以让前面的等值列充分利用索引。
    5. 范围列后面的列还能用吗? → 不能继续做等值/范围定位;可以被 ICP 过滤、被覆盖索引读取;能否排序取决于 ORDER BY 是否以范围列为起点。
    6. 等值 + 排序怎么建索引? → (eq_cols..., sort_cols...),让排序走索引顺序;如果中间插入范围列,排序通常会被截断。
    7. 范围 + 排序冲突怎么取舍? → 看范围选择性和 LIMIT:范围很窄、结果少,优先范围;排序 + 小 LIMIT 是瓶颈,评估把排序列提前,让范围列退化为 ICP/过滤;用 EXPLAIN + 压测决定。
    8. 最左前缀是什么? → 联合索引 (a,b,c) 能用于 a、a+b、a+b+c 的定位;跳过 a 一般不能用,8.0 skip scan 在特定低基数场景例外。
    9. 覆盖索引是什么? → 查询所需列都在索引里(二级索引隐含 PK),不用回表;EXPLAIN 出现 Using index。
    10. 覆盖索引一定不回表吗? → 优化器层面通常不回表;但二级索引的 MVCC 可见性判断在部分场景仍要访问聚簇索引,前缀索引也不能覆盖完整列值。
    11. 覆盖索引的代价? → 索引变宽、写放大、BP 占用增加、DDL 更慢;不是所有查询都值得做覆盖。
    12. SELECT * 为什么不好? → 破坏覆盖索引、传输无用列、增加网络/内存/回表成本;只取需要的列。
    13. ICP 是什么? → Index Condition Pushdown,5.6 引入;把索引列上的 WHERE 条件下推到 InnoDB,在索引记录上先过滤再回表。
    14. ICP 解决什么、不解决什么? → 减少回表次数;不减少索引扫描范围,也不改变索引定位;覆盖索引时收益下降。
    15. ICP 什么时候生效? → 条件只引用索引列、可下推;EXPLAIN 出现 Using index condition;子查询等不能下推。
    16. MRR 是什么? → Multi-Range Read,把待回表的主键排序后批量读聚簇索引,降低随机 IO;EXPLAIN 出现 Using MRR。
    17. MRR 一定更快吗? → 不一定。小范围、排序/缓冲开销大时可能更慢;mrr_cost_based=on 时由优化器按成本决定。
    18. index merge 说明什么? → 优化器合并多个单列索引(union/intersect);常见于 OR 或多条件,往往说明缺联合索引。
    19. skip scan 是什么? → 8.0.13 引入;前导列基数很低且未被条件使用时,优化器可以跳过前导列扫描后续列;Using index for skip scan。
    20. 前缀索引长度怎么选? → 用 COUNT(DISTINCT LEFT(col,n))/COUNT(*) 逼近全列区分度,选最小可接受长度;不能覆盖完整值、不能按完整列排序。
    21. 选择性和基数怎么算? → 单列 COUNT(DISTINCT col)/COUNT(*);联合索引看组合区分度;SHOW INDEX 的 Cardinality 是估算值。
    22. 低选择性列要不要建索引? → 单独建通常收益有限;但可以作为覆盖索引的一部分、用于排序/分组,或与其他列组成联合索引。
    23. 数据倾斜怎么办? → 平均基数会掩盖热点值;用 8.0 直方图改善估算,或调整联合索引/查询写法;极端热点考虑缓存/分桶。
    24. 统计信息怎么更新? → ANALYZE TABLE;innodb_stats_persistent 持久化、innodb_stats_auto_recalc 自动重算、采样页数控制精度;8.0 可对列建直方图。
    25. 为什么优化器会选错索引? → 统计过期/采样误差、数据倾斜、回表成本估算、ORDER BY ... LIMIT、函数/隐式转换、成本常数;看 optimizer_trace。
    26. FORCE INDEX 能长期用吗? → 不建议;数据分布变化后会反噬,优先改 SQL、补联合索引、更新统计;只做短期兜底。
    27. 联合索引和多个单列索引怎么选? → 多条件/排序/覆盖优先联合索引;单列索引适合独立查询,但过多会增加写放大;避免 (a) 与 (a,b) 这类冗余。
    28. 索引越多越好吗? → 不是;每个二级索引都增加写放大、DDL 时间、BP 占用和优化器选择成本;按查询模式建少量高复用索引。
    29. 冗余索引怎么识别? → sys.schema_redundant_indexes、pt-duplicate-key-checker;(a) 是 (a,b) 的前缀,通常冗余;唯一索引可替代同列普通索引。
    30. 未使用索引能直接删吗? → 不能。统计窗口可能没覆盖月报/从库/灾备;先观察完整业务周期,灰度下线,保留回滚方案。
    31. 大表加索引怎么做? → Online DDL ALGORITHM=INPLACE, LOCK=NONE;高峰期用 gh-ost/pt-osc;控制 innodb_online_alter_log_max_size(4.1-12)。
    32. 索引和锁是什么关系? → 锁定读沿索引加 next-key lock;没有合适索引会扫全表、锁大量记录和间隙;索引设计也是锁优化(4.1-10)。
    33. 深分页怎么优化? → 延迟关联(先取 PK 再回表)、游标 WHERE id > ?、覆盖索引;避免大 offset 扫过再丢弃。
    34. 分库分表下索引怎么设计? → 分片键要进索引;本地索引 vs 全局索引;跨分片查询/排序/聚合代价高,必要时异构到 ES(4.1-16)。
    35. 怎么验证索引优化有效? → 对比 EXPLAIN 的 key/rows/filtered、EXPLAIN ANALYZE 实际行数、慢日志 RT、压测写 TPS/BP 命中/索引体积;不能只看“有没有走索引”。
    36. 索引设计有哪些反模式? → 每列都建索引、低选择性列单独索引、超长字符串全列索引、SELECT *、函数/隐式转换、冗余索引、只看读不看写、盲信 FORCE INDEX。
    37. key_len 怎么看? → 表示实际使用的索引前缀字节数,可反推联合索引用到第几列;varchar(20) utf8mb4 NULL 约为 20*4 + 2 + 1 = 83,非空为 82,bigint 8、int 4、datetime 5;要结合字符集、可空和变长字段判断。
  • 生产案例/取舍(示例,替换成你的项目):

    • 订单列表按 user_id + status + create_time 查询和排序;建 (user_id, status, create_time) 后,等值 + 范围 + 排序都走索引,深分页再改延迟关联。
    • 报表 WHERE status=? AND create_time BETWEEN ... ORDER BY id;(status, create_time) 能过滤但排序要 filesort,评估 (status, id) 或接受排序成本。
    • 手机号查询用前缀索引 + 精确校验,避免 20 位字符串全列索引;或增加手机号哈希列做等值索引。
    • 联合索引把范围列放中间,后续列无法定位、排序也失效;调整列序后 RT 下降。
    • 高频列表查询补覆盖索引,回表次数下降、RT 降低;但索引变宽后写 TPS 下降,最终只保留核心查询的覆盖索引。
    • 写入型表建了 10 个二级索引,TPS 上不去;删冗余索引、合并联合索引后写放大下降。
    • 大 IN 列表查询变慢,eq_range_index_dive_limit 导致估算变粗;改批量查询/临时表后稳定。
    • 字段类型不一致导致隐式转换、索引失效;统一类型/字符集后恢复。
    • 数据倾斜导致优化器低估行数、选错索引;建直方图/更新统计后计划稳定。
    • 分库分表后跨分片排序/分页困难;按分片键设计本地索引,复杂检索异构到 ES。
    • 取舍:联合索引换多条件复用,代价是列序要兼顾;覆盖索引换少回表,代价是写放大;ICP 换少回表,代价是下推限制;索引治理换写性能,代价是删索引要谨慎。
  • 易错点:

    • 认为“选择性最高的列一定放最左”。
    • 认为联合索引的列顺序对任何查询都无所谓。
    • 认为范围列后面的列完全不能用;忽略 ICP 和“ORDER BY 以范围列为起点”的情况。
    • 认为覆盖索引一定要 SELECT 所有列;实际只要查询列都在索引里。
    • 认为覆盖索引在任何情况下都绝对不回表,忽略二级索引 MVCC 判断。
    • 认为 ICP 能减少索引扫描量;它只减少回表。
    • 认为 MRR 总是更快;小范围/排序开销大时可能更慢。
    • 认为 index merge 是好方案;它常是缺联合索引的信号。
    • 认为索引越多查询越快,忽略写放大和优化器成本。
    • 认为低选择性列一律不能建索引,忽略覆盖/排序/联合索引场景。
    • 认为前缀索引可以覆盖完整列值。
    • 认为 LIKE 'x%' 一定走索引,忽略字符集/排序规则和函数影响。
    • 认为隐式转换只影响结果不影响索引。
    • 认为 NULL 不能建索引;InnoDB 会索引 NULL。
    • 认为 NOT IN/!= 一定不走索引;可能走范围但选择性差。
    • 认为 OR 一定全表;可能 index merge/union。
    • 认为函数索引能替代所有函数改写;它只匹配相同表达式且需 8.0.13+。
    • 认为 FORCE INDEX 是长期方案。
    • 认为统计信息实时准确,忽略采样和倾斜。
    • 认为直方图可以替代索引;它只改善估算。
    • 认为索引设计一次到位,忽略业务和数据分布变化。
    • 认为分库分表后索引设计与单机完全一样。
  • 30 秒口述稿:

索引设计先看 Top SQL 和查询模式,不是给每个 WHERE 列都建索引。联合索引按“等值 → 范围 → 排序/分组”排,利用最左前缀;高频查询尽量做覆盖索引,减少回表;ICP 把索引列条件下推到引擎层,减少回表但不减少索引扫描;MRR 把回表主键排序批量读。选择性、前缀索引、统计信息和直方图影响优化器选择。最后用 EXPLAIN、optimizer_trace、慢日志和压测验证,并定期治理冗余/未使用索引。

  • 3 分钟展开顺序:结论(查询模式 + 联合索引 + 覆盖/ICP + 验证)→ 设计输入 → 联合索引列序与最左前缀 → 覆盖索引收益/成本 → ICP 边界 → MRR/index merge/skip scan → 选择性/前缀索引/统计信息 → 失效场景 → 治理与在线 DDL → 生产案例与取舍。