罗罗编程学习记录
本页内容 展开查看

05.1.1.5 索引设计评审与线上治理

所属:全栈开发面试题库 / 05 数据库与存储 / 05.1 关系型数据库通用能力 / 05.1.1 索引原理与设计

覆盖范围

  • 核心查询梳理与索引设计清单
  • 索引数量、写入放大、空间与缓存占用评估
  • 重复/冗余/无用索引的识别与清理
  • 上线前计划验证与真实数据压测
  • 大表加索引流程、Online DDL 边界与变更评估
  • 索引监控、回滚方案与分区表索引边界

高频题速览

编号 题目 难度 频率
05.1.1.5-001 索引设计检查清单 进阶 ★★★★☆
05.1.1.5-005 如何识别无用索引 进阶 ★★★★☆
05.1.1.5-011 线上大表加索引 高级 ★★★★☆
05.1.1.5-012 Online DDL 边界与风险 高级 ★★★★☆

治理主线

梳理高频查询 → 设计匹配前缀的索引 → 评审数量/宽度/写入成本
  → 计划验证 + 真实数据压测 → 低峰期在线变更 → 监控使用情况 → 定期清理冗余

05.1.1.5-001 索引设计的检查清单有哪些?

频率 ★★★★☆ | 难度 进阶

一句话结论

设计索引先按 QPS 与延迟要求梳理核心查询,为每个查询设计最左前缀可用的联合索引,优先覆盖索引;控制索引数量,避免索引列函数化,用计划验证并在真实数据量压测,定期清理无用索引。

概念解释

  • 查询优先:索引服务于真实 SQL,不为想象中的查询建索引。
  • 前缀匹配:联合索引按等值/范围/排序角色排列列顺序。
  • 闭环验证:设计后必须用计划与压测验证,而非凭经验上线。

原理流程

  1. 列出核心查询,按 QPS 与延迟要求排序。
  2. 逐条设计匹配前缀的联合索引,评估覆盖收益。
  3. 检查函数/隐式转换、索引数量与宽度,计划验证后压测上线。
  4. 定期清理无用/冗余索引并更新统计信息。

代码 / 命令

检查清单:
  核心查询是否按 QPS 排序?索引前缀是否匹配条件形态?
  等值在前、范围在后、排序列接续?索引宽度是否可控?
  索引列是否避免函数/隐式转换?计划 type/key/rows/Extra 是否符合预期?
  是否在真实数据量压测?无用/冗余索引是否定期清理?

常见误区

  • 误区:索引越多查询越快。正解:索引越多写入越慢、空间越大、优化器越易选错。
  • 误区:小表验证过索引就可直接上线大表。正解:小表数据分布与基数不代表线上,必须在真实量级压测。

追问

  • 问:怎么验证索引有效?答:计划看访问路径与命中行数,EXPLAIN ANALYZE 类工具核对实际行数与耗时。
  • 问:索引设计文档要记录什么?答:服务的查询、列顺序、覆盖列、创建理由与变更时间,便于后续评审。

05.1.1.5-002 如何梳理核心查询?

频率 ★★★★☆ | 难度 进阶

一句话结论

从慢查询日志、接口监控与代码 SQL 入手,按 QPS、延迟占比与业务重要性排序,提取每条查询的过滤列、排序列与返回列,作为索引设计输入。

概念解释

  • 来源:慢日志、性能监控、ORM 统计与代码审计。
  • 提取要素:WHERE 列、ORDER BY/GROUP BY 列、SELECT 返回列。
  • 排序口径:高频与高延迟优先,核心业务链路优先。

原理流程

  1. 收集慢查询与高频 SQL 模板,合并相同形态。
  2. 标注每条查询的过滤、排序、返回列与 QPS/延迟。
  3. 按优先级分组,先服务头部查询,再处理长尾。

代码 / 命令

-- MySQL:慢日志定位高频慢 SQL(参数与视图口径以版本为准)
SHOW VARIABLES LIKE 'slow_query_log%';
-- 结合 performance_schema / 慢日志按 SQL 模板聚合 QPS 与耗时

常见误区

  • 误区:只看最慢的几条 SQL。正解:高频低单条耗时的 SQL 累计成本可能更高,需按 QPS × 延迟综合排序。
  • 误区:把 ORM 生成的 SQL 当作不可变。正解:先确认查询形态能否在代码侧优化,再决定索引方案。

追问

  • 问:相同 SQL 模板不同参数怎么归类?答:按 SQL 文本形态聚合,参数差异在计划分析时单独看分布。
  • 问:跨表查询怎么梳理?答:分别记录各表过滤列与连接列,连接索引在被驱动表上评估。

05.1.1.5-003 单表索引数量控制在多少?

频率 ★★★☆☆ | 难度 进阶

一句话结论

没有绝对上限,经验上单表 3~5 个二级索引较常见,写入越频繁数量越应收紧;每条索引都要对应真实高频查询,长期无收益的索引应清理。

概念解释

  • 写入成本:每个索引在 INSERT/UPDATE/DELETE 时都要维护。
  • 优化器负担:候选索引过多增加计划选择范围,也可能误选。
  • 业务差异:读多写少表可适当放宽,高频写表严格控制。

原理流程

  1. 统计现有索引服务的查询与使用频次。
  2. 剔除无查询命中的索引,合并可复用的联合前缀。
  3. 新增索引前评估已有索引能否覆盖该查询。

代码 / 命令

-- 列出单表所有索引
SHOW INDEX FROM orders;

常见误区

  • 误区:按字段个数建索引,一个列一个索引。正解:应按查询模式组合成联合索引,减少索引数量。
  • 误区:索引数量上限是硬性规定。正解:3~5 是经验值,实际按写入吞吐与查询收益权衡。

追问

  • 问:索引过多如何拖慢写入?答:单行写入要维护所有相关索引项,写入放大随索引数线性增加。
  • 问:什么情况下可以放宽数量?答:报表/读多写少的归档表,查询复杂且写入低频时可多建覆盖索引。

05.1.1.5-004 什么是冗余索引和重复索引?

频率 ★★★☆☆ | 难度 进阶

一句话结论

重复索引是列与顺序完全相同的索引;冗余索引是已有索引可通过最左前缀完全覆盖另一索引(如 (a,b) 已存在再建 (a));唯一索引与普通索引语义不同,不能简单判为冗余删除。

概念解释

  • 重复索引:同列同顺序重复创建,维护两份相同结构。
  • 冗余索引:前缀被另一索引包含,可被复用。
  • 例外:唯一索引保证约束,普通索引不保证,二者并存有语义价值。

原理流程

  1. 对比各索引的列集合与顺序。
  2. 判断前缀包含关系与唯一性语义。
  3. 确认无依赖后,按流程清理重复与冗余索引。

代码 / 命令

SHOW INDEX FROM t;

-- MySQL 8.0:冗余索引视图(口径以版本为准)
SELECT * FROM sys.schema_redundant_indexes;

常见误区

  • 误区:(a,b) 存在时 (b,a) 也是冗余。正解:顺序不同前缀不同,不是冗余索引。
  • 误区:冗余索引可以直接线上删除。正解:先确认无查询依赖、保留回滚脚本,走变更流程。

追问

  • 问:冗余索引有什么危害?答:写入放大、空间浪费,还可能干扰优化器选择。
  • 问:唯一索引被普通索引前缀包含时怎么处理?答:保留唯一索引满足约束,普通冗余部分评估移除。

05.1.1.5-005 如何识别无用索引?

频率 ★★★★☆ | 难度 进阶

一句话结论

通过性能统计中的索引读取计数与近期使用周期判断:长期读计数为 0、无查询命中的索引为候选无用索引;确认后结合写入频率与历史流量季节性,走流程下线。

概念解释

  • 使用统计:索引级别的读/写次数与等待统计。
  • 观察周期:覆盖一个完整业务周期,避免误删季节性查询依赖的索引。
  • 写入成本:长期无用的索引持续消耗写入与空间,应优先清理。

原理流程

  1. 采集索引使用计数,列出读计数为 0 的索引。
  2. 核对 SQL 与慢日志,确认近期与历史峰值流量都未使用。
  3. 评估下线影响,备份定义后按流程删除并观察。

代码 / 命令

-- MySQL:索引使用统计(视图与字段口径以版本为准)
SELECT * FROM performance_schema.table_io_waits_summary_by_index_usage;

-- 对比索引定义
SHOW INDEX FROM orders;

常见误区

  • 误区:几天没用到就是无用索引。正解:需覆盖完整业务周期,批量任务、月度报表可能周期触发。
  • 误区:读计数为 0 就直接删除。正解:先备份索引定义、确认无业务依赖,灰度观察后再正式下线。

追问

  • 问:PG 怎么看索引使用?答:pg_stat_user_indexes 的 idx_scan 等字段,详见 05.3.2。
  • 问:无用索引删除后查询会变慢吗?答:若统计准确则不会;删除后需监控慢查询与计划变化。

05.1.1.5-006 索引评审流程怎么走?

频率 ★★★☆☆ | 难度 进阶

一句话结论

评审按"查询依据 → 索引方案 → 代价评估 → 风险与回滚"推进:提交者给出服务的 SQL 与列角色,评审者核对前缀匹配、宽度、冗余与写入影响,确认计划与回滚方案后上线。

概念解释

  • 方案依据:每条索引对应明确的查询与 QPS。
  • 评审维度:前缀顺序、选择性、覆盖宽度、与现有索引冗余。
  • 风险记录:写入放大、变更方式、回滚步骤。

原理流程

  1. 提交索引方案:SQL 样本、过滤/排序/返回列、预期收益。
  2. 评审核对最左前缀、列顺序、宽度与冗余关系。
  3. 确认变更方式与回滚脚本,上线后监控计划与慢查询。

代码 / 命令

评审材料:
  服务的 SQL 模板与 QPS;索引列角色(等值/范围/排序/覆盖)
  与现有索引的关系(新增/复用/替代);写入影响评估;回滚脚本

常见误区

  • 误区:评审只讨论加不加索引。正解:还要评估能否用已有索引覆盖、是否存在更窄的等价方案。
  • 误区:评审通过即可长期有效。正解:查询模式变化后方案需重新评审,索引不是一次定终身。

追问

  • 问:评审时怎么判断索引是否冗余?答:对照现有索引前缀与唯一性语义,确认可被复用再决定合并或删除。
  • 问:评审记录保存在哪里?答:随表结构文档记录索引理由与变更历史,便于后续追溯。

05.1.1.5-007 上线前如何用执行计划验证索引?

频率 ★★★☆☆ | 难度 进阶

一句话结论

用 EXPLAIN 核对访问类型、命中索引、估算行数与 Extra 标记,确认走了预期前缀、无意外回表或 filesort;关键查询再用实际执行计划核对估算与实测偏差。

概念解释

  • 访问类型:const/ref/range 较好,ALL 为全表扫描。
  • 命中索引:key 列显示实际使用的索引。
  • 标记核对:Using index 覆盖、Using index condition 下推、Using filesort/temporary 需关注。

原理流程

  1. 在与线上相近数据量环境执行 EXPLAIN。
  2. 核对 type/key/rows/filtered/Extra 是否符合预期。
  3. 关键语句用实际执行计划对比估算与实测行数。

代码 / 命令

EXPLAIN SELECT ...;
-- 关注:type、key、rows、filtered、Extra

-- PostgreSQL 实际执行计划
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

常见误区

  • 误区:计划里出现索引名就验证通过。正解:还要看扫描行数、回表与排序标记,确认路径真正合理。
  • 误区:开发库计划可代表线上。正解:数据量与统计不同,计划可能不同,需在相近量级环境复核。

追问

  • 问:估算行数与实际差很多怎么办?答:先更新统计信息,仍偏差则排查分布倾斜与条件复杂度,见 05.1.3.4。
  • 问:Extra 出现 Using filesort 一定要改吗?答:小结果集内存排序成本可控;大结果集则需调整索引列顺序。

05.1.1.5-008 为什么要在真实数据量下压测?

频率 ★★★☆☆ | 难度 基础

一句话结论

小表上所有路径都快,区分不出索引收益;真实数据量下的基数、命中比例、回表次数与缓冲池命中率才反映线上表现,索引是否有效必须按量级验证。

概念解释

  • 基数差异:小表不同值少,统计与选择率不代表线上。
  • 回表放大:大命中行下回表随机读成本显著。
  • 缓存效应:小表常驻内存,掩盖磁盘 I/O 问题。

原理流程

  1. 构造或导出演线上量级的测试数据。
  2. 跑目标查询对比有无索引的耗时、扫描行数与缓冲命中。
  3. 评估索引在真实量级下的收益与写入成本。

代码 / 命令

压测记录:
  数据量 / 基数分布;有无索引下的耗时、扫描行数、回表次数
  写入场景下单行维护索引的额外成本

常见误区

  • 误区:功能跑通就说明索引有效。正解:功能正确与性能达标是两件事,量级差异下计划可能完全不同。
  • 误区:压测只测查询不测写入。正解:索引收益在查询侧,成本在写入侧,两侧都要评估。

追问

  • 问:没有生产量级数据怎么办?答:按线上基数分布造数,或在隔离环境用脱敏数据回放,避免只靠小样本判断。
  • 问:压测结果如何对照生产?答:重点看扫描行数与访问路径是否一致,单耗时受硬件影响需结合比例判断。

05.1.1.5-009 索引写入放大怎么评估?

频率 ★★★☆☆ | 难度 进阶

一句话结论

每次写行都要维护相关索引:插入新增索引项,更新索引列先删旧项再插新项,删除移除索引项;评估新增索引带来的额外索引项数量、页维护与 undo/redo 开销,对照表写入频率判断。

概念解释

  • 插入维护:每个相关索引插入一项。
  • 更新维护:索引列变化触发旧项删除与新项插入。
  • 放大来源:随机页修改、页分裂与日志记录。

原理流程

  1. 统计表写入 QPS 与涉及索引列的更新比例。
  2. 估算新增索引单行维护成本(项宽度、页访问)。
  3. 结合写入峰值判断是否可接受,必要时缩减索引列。

代码 / 命令

写入维护:
  INSERT:聚簇 + 每个二级索引各插一项
  UPDATE 索引列:旧项删除 + 新项插入(各相关索引)
  DELETE:各索引移除项

常见误区

  • 误区:索引只影响查询时的读取。正解:写入路径上每个索引都有同步维护成本。
  • 误区:更新非索引列也维护该索引。正解:只有索引列值变化才触发对应索引项更新,不涉及列的索引不受影响。

追问

  • 问:怎么量化写入放大?答:对比加索引前后的写入耗时、redo/undo 量与索引页修改次数。
  • 问:高频写表怎么控制?答:收紧索引数量,覆盖列只保留高频必需列。

05.1.1.5-010 索引空间与缓冲池占用怎么评估?

频率 ★★☆☆☆ | 难度 基础

一句话结论

每个二级索引是独立 B+ 树,空间为索引列宽度乘行数并含页开销;热点索引页进入缓冲池与数据页争用内存,规划容量时需把索引空间与缓存占用一并计入。

概念解释

  • 表空间:索引数据存于独立索引段,占用磁盘空间。
  • 缓冲池竞争:热点索引页与数据页共享内存容量。
  • 冗余放大:宽主键、宽覆盖列放大每个索引项。

原理流程

  1. 按索引列字节数乘行数估算体积。
  2. 结合页填充率与碎片修正空间需求。
  3. 评估热点索引页占缓冲池比例,避免挤占数据缓存。

代码 / 命令

-- MySQL:查看表与索引大小(统计口径以版本为准)
SELECT table_name, data_length, index_length
FROM information_schema.tables WHERE table_name = 'orders';

常见误区

  • 误区:索引只占磁盘不占内存。正解:被访问的索引页同样驻留缓冲池。
  • 误区:索引空间建好后固定不变。正解:更新与分裂会产生碎片,长期需评估重建整理。

追问

  • 问:二级索引为什么比想象中大?答:叶子冗余主键,宽主键与多覆盖列都会放大体积。
  • 问:缓冲池不足时索引会被淘汰吗?答:热点索引页与数据页按访问策略竞争淘汰,冷页被换出后下次访问再读盘。

05.1.1.5-011 线上大表加索引怎么做?

频率 ★★★★☆ | 难度 高级

一句话结论

大表加索引先评估空间、延迟与锁影响;MySQL 5.6+ 二级索引通常可 INPLACE + LOCK=NONE 在线创建,千万级以上大表优先用影子表工具(gh-ost / pt-osc)可控变更,低峰执行并监控延迟、IO 与锁等待,完成后更新统计信息。

概念解释

  • 在线变更:二级索引创建通常不阻塞读写,但扫描全表占用 IO。
  • 影子表工具:建新表、增量同步、原子切换,可暂停与回滚。
  • 监控项:主从延迟、IO 负载、锁等待、连接数。

原理流程

  1. 评估表数据量、磁盘空间与变更窗口。
  2. 选择 Online DDL 或影子表工具,明确算法与锁级别。
  3. 低峰执行,监控延迟与负载,完成后 ANALYZE TABLE 更新统计。

代码 / 命令

-- MySQL:明确指定在线算法,避免退化为 COPY
ALTER TABLE orders
  ADD INDEX idx_status_time (status, create_time),
  ALGORITHM=INPLACE, LOCK=NONE;

-- 大表工具:gh-ost / pt-online-schema-change(外部工具,按其文档执行)
-- 变更后更新统计
ANALYZE TABLE orders;

常见误区

  • 误区:Online DDL 全程不锁表。正解:开始与结束阶段需要短暂元数据锁,长事务会阻塞该阶段。
  • 误区:加索引不影响从库。正解:DDL 在从库回放耗时,可能造成主从延迟,详见 05.1.4.1。

追问

  • 问:什么情况需要用影子表工具?答:超大表、需要暂停/回滚控制、担心 Online DDL 锁或复制延迟时。
  • 问:变更前要预留什么空间?答:索引体积与在线变更临时日志空间,磁盘余量不足会导致变更失败。

05.1.1.5-012 Online DDL 的边界与风险是什么?

频率 ★★★★☆ | 难度 高级

一句话结论

Online DDL 可在创建二级索引时保持读写可用,但并非完全无锁:元数据锁在开始/结束阶段短暂持有,长事务会阻塞;部分变更需重建表或退化为拷贝,执行中占用 IO 与空间,具体支持范围随引擎版本变化(待确认以版本文档为准)。

概念解释

  • 算法模式:INPLACE 原地构建 vs COPY 拷贝重建;锁级别 LOCK=NONE/SHARED/EXCLUSIVE。
  • MDL 排队:变更前后需元数据锁,长事务持有会阻塞后续读写。
  • 版本边界:不同变更类型的在线支持程度不同,需核对文档。

原理流程

  1. 确认变更类型是否支持 INPLACE + LOCK=NONE。
  2. 变更开始申请元数据锁,等待持有锁的事务结束。
  3. 构建期间后台扫描与写入并行,结束阶段再次排队元数据锁提交。

代码 / 命令

-- 查看 DDL 在线能力(MySQL 文档按变更类型给出 ALGORITHM/LOCK 支持矩阵)
ALTER TABLE orders ADD INDEX idx_x (x), ALGORITHM=INPLACE, LOCK=NONE;

-- 检查当前长事务,避免阻塞 DDL 提交阶段
SELECT trx_id, trx_started, trx_query FROM information_schema.innodb_trx;

常见误区

  • 误区:写了 LOCK=NONE 就任何时候都不阻塞。正解:MDL 排队阶段仍可能被长事务阻塞,进而阻塞后续请求。
  • 误区:所有 DDL 都能 Online。正解:改列类型、部分索引类型调整可能重建表,在线能力需按变更类型核对文档。

追问

  • 问:DDL 被长事务卡住有什么表现?答:变更等待 MDL,后续新读写在队列中阻塞,需先处理长事务。
  • 问:PG 的在线变更机制有何不同?答:PG 多数 DDL 轻量加锁,重写表操作行为不同,详见 05.3.x 相关章节。

05.1.1.5-013 gh-ost / pt-osc 方案概览是什么?

频率 ★★★☆☆ | 难度 高级

一句话结论

影子表工具通过创建结构相同的新表、回填数据、同步增量变更、原子 rename 切换完成结构变更,把大表重构对在线业务的影响打散,支持暂停与回滚;工具原理与命令细节按各自文档执行。

概念解释

  • 影子表:按目标结构新建空表。
  • 增量同步:通过日志或触发器把原表增量变更同步到影子表。
  • 原子切换:数据追平后短时间锁表 rename 互换。

原理流程

  1. 建新影子表并批量回填存量数据。
  2. 持续同步原表增量写入。
  3. 追平后短暂加锁切换表名,旧表保留回滚。

代码 / 命令

gh-ost / pt-osc 通用流程:
  1. 复制表结构到影子表
  2. 存量数据分批回填
  3. 增量事件同步(binlog 解析 / 触发器)
  4. 追平校验 → 原子 rename 切换 → 观察旧表后清理

常见误区

  • 误区:影子表工具完全无锁无风险。正解:切换阶段仍有短暂锁,增量同步与回放资源也需监控。
  • 误区:工具可以随意中断不留痕迹。正解:中断后影子表与同步链路需按文档清理,避免残留。

追问

  • 问:两种工具的增量同步方式有何不同?答:gh-ost 解析 binlog,pt-osc 用触发器;具体取舍按部署环境与版本兼容性评估。
  • 问:切换失败怎么回滚?答:保留旧表与 rename 前结构,按工具回滚流程切回,数据以旧表为准。

05.1.1.5-014 加索引前要评估哪些资源?

频率 ★★★☆☆ | 难度 进阶

一句话结论

变更前评估磁盘空间(索引体积加临时日志)、主从延迟风险、IO/CPU 负载、锁等待与连接占用,以及回滚所需空间;资源不足或高峰窗口不应执行大表变更。

概念解释

  • 空间:新索引体积与 Online DDL 临时日志/影子表空间。
  • 延迟:变更扫描与回放可能拉长主从延迟。
  • 负载:构建占用 IO 与 CPU,影响在线请求。

原理流程

  1. 估算索引体积与临时空间需求,确认磁盘余量。
  2. 评估变更窗口内 IO/CPU 余量与复制延迟水位。
  3. 准备回滚空间与预案,低峰执行并设置监控阈值。

代码 / 命令

评估清单:
  磁盘余量 ≥ 索引体积 + 临时构建空间
  当前 IO/CPU/主从延迟水位;变更窗口长度
  锁等待与长事务情况;回滚脚本与旧表保留方案

常见误区

  • 误区:只评估索引最终体积。正解:构建期间的临时日志、影子表与碎片空间同样需要预留。
  • 误区:白天业务低峰就可以随便加索引。正解:需看复制延迟与 IO 余量,避免变更影响从库与在线请求。

追问

  • 问:空间不足会怎样?答:构建中途失败,回滚可能耗时,且残留临时对象需清理。
  • 问:怎么预估索引体积?答:按索引列宽度乘行数,参考现有同类索引大小推算。

05.1.1.5-015 索引变更为什么会造成主从延迟?

频率 ★★★☆☆ | 难度 进阶

一句话结论

DDL 与大批量索引构建在主库占用资源,从库回放相同变更时单线程/并行回放能力有限,构建期间主库已承接新写入,从库回放落后形成延迟;复制原理与延迟监控详见 05.1.4.1。

概念解释

  • 回放压力:从库按顺序回放主库日志,DDL/大事务回放耗时。
  • 并行限制:历史上从库回放并行度有限,大事件容易堆积。
  • 延迟传导:延迟期间从库读到旧数据,影响读写分离一致性。

原理流程

  1. 主库执行加索引 DDL 并记录日志。
  2. 从库拉取日志回放,构建索引过程持续消耗。
  3. 主库新事务持续写入,从库回放追不上则延迟累积。

代码 / 命令

-- MySQL:查看复制延迟(视图名以版本为准)
SHOW REPLICA STATUS\G
-- 关注 Seconds_Behind_Source 与回放位点

常见误区

  • 误区:加索引只在主库执行,从库不受影响。正解:DDL 与数据变更都要在从库回放,延迟风险同样存在。
  • 误区:延迟只是展示问题。正解:读写分离下从库延迟会读到旧数据,需一致性策略兜底。

追问

  • 问:怎么降低变更期间延迟?答:低峰执行、控制变更批量、监控延迟阈值,必要时分批/工具化变更。
  • 问:大事务回放为什么慢?答:事件体积大、回放顺序受限,详见 05.1.4.1 与 05.1.2.4。

05.1.1.5-016 索引监控与定期清理怎么做?

频率 ★★★☆☆ | 难度 进阶

一句话结论

建立索引使用监控(读计数、慢查询命中、索引大小),按周期评审无用与冗余索引,结合业务季节性确认后下线;删除后持续观察慢查询与计划变化,保证可回滚。

概念解释

  • 监控指标:索引扫描次数、用户查询命中、索引体积增长。
  • 评审周期:按业务节奏定期复核,而非一次性清理。
  • 下线观察:删除后监控慢查询与计划,异常则按脚本回滚。

原理流程

  1. 持续采集索引使用与体积数据。
  2. 周期列出读计数为 0、冗余与超宽索引候选。
  3. 确认无依赖后下线,保留定义备份,观察线上指标。

代码 / 命令

-- 定期导出索引定义作为备份
SHOW INDEX FROM orders;

-- 结合使用统计复核候选索引(视图口径以版本为准)
SELECT * FROM performance_schema.table_io_waits_summary_by_index_usage;

常见误区

  • 误区:清理一次就不用再管。正解:查询模式与数据分布持续变化,索引需要长期治理。
  • 误区:直接删除不保留备份。正解:先备份索引定义与回滚脚本,确认无依赖再删。

追问

  • 问:索引体积持续增长说明什么?答:可能碎片积累或写入模式变化,需评估重建表/索引整理。
  • 问:清理后如何快速恢复?答:按备份的 CREATE INDEX 语句重建,大表重建仍需走在线变更流程。

05.1.1.5-017 索引回滚方案怎么准备?

频率 ★★★☆☆ | 难度 高级

一句话结论

新增索引上线前保存 CREATE INDEX 定义与下线脚本;若上线后计划变差或写入压力超标,可按脚本删除新索引回到原状态;被替代的旧索引先保留观察期,确认无回退需求再清理。

概念解释

  • 定义备份:变更前导出原索引与新索引 DDL。
  • 回滚脚本:删除新索引的 DROP INDEX 语句,可快速执行。
  • 观察期:旧索引不立即删除,保留回退路径。

原理流程

  1. 变更前记录现有索引结构与新索引 DDL。
  2. 上线后监控查询计划、慢查询与写入指标。
  3. 异常则执行回滚脚本;稳定后再评估旧索引下线。

代码 / 命令

-- 回滚:移除新增索引
DROP INDEX idx_status_time ON orders;

-- 被替代的旧索引:观察期内保留,确认稳定后再删除

常见误区

  • 误区:新索引上线后立即删掉旧索引。正解:先保留观察期,出现计划回退时可快速切回。
  • 误区:回滚就是再建一次旧索引。正解:旧索引未删除时无需重建;若已删除,回滚需重新走在线变更。

追问

  • 问:回滚删索引会锁表吗?答:删除二级索引通常可在线执行,但仍占用资源,需低峰并监控。
  • 问:怎么判断新索引上线后变差?答:对比变更前后慢查询、计划路径与写入延迟,指标恶化再回滚。

05.1.1.5-018 分区表与索引的关系是什么?

频率 ★★☆☆☆ | 难度 进阶

一句话结论

分区把表按规则切成多个分区,索引可建在分区内(本地索引)或全局维护;分区裁剪依赖分区键条件,索引设计需与分区规则配合,避免跨分区扫描。分区细节见 05.2 / 05.3 对应章节。

概念解释

  • 分区裁剪:查询条件命中分区键时只扫描相关分区。
  • 本地/全局索引:本地索引依附分区,全局索引跨分区维护(引擎支持不同)。
  • 索引配合:分区键与索引前缀协调,减少跨分区访问。

原理流程

  1. 按访问模式选择分区键与分区策略。
  2. 索引设计保证分区内查询走索引。
  3. 查询带分区键条件以触发裁剪,避免全分区扫描。

代码 / 命令

-- 概念示例:按时间分区(语法随引擎不同,具体以 MySQL/PG 文档为准)
-- CREATE TABLE orders (...) PARTITION BY RANGE (create_time) (...)

常见误区

  • 误区:分区能替代索引。正解:分区减少扫描范围,分区内过滤仍需索引支撑。
  • 误区:跨分区查询和单分区性能一样。正解:跨多个分区扫描与合并成本上升,分区设计应匹配主要查询条件。

追问

  • 问:分区键与索引前缀怎么配合?答:高频条件尽量同时覆盖分区键与索引前缀,先裁剪分区再走索引。
  • 问:全局索引与本地索引怎么选?答:随引擎支持与跨分区查询需求而定,具体实现见 05.2.2 / 05.3.2。

参考来源