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,不为想象中的查询建索引。
- 前缀匹配:联合索引按等值/范围/排序角色排列列顺序。
- 闭环验证:设计后必须用计划与压测验证,而非凭经验上线。
原理流程
- 列出核心查询,按 QPS 与延迟要求排序。
- 逐条设计匹配前缀的联合索引,评估覆盖收益。
- 检查函数/隐式转换、索引数量与宽度,计划验证后压测上线。
- 定期清理无用/冗余索引并更新统计信息。
代码 / 命令
检查清单:
核心查询是否按 QPS 排序?索引前缀是否匹配条件形态?
等值在前、范围在后、排序列接续?索引宽度是否可控?
索引列是否避免函数/隐式转换?计划 type/key/rows/Extra 是否符合预期?
是否在真实数据量压测?无用/冗余索引是否定期清理?
常见误区
- 误区:索引越多查询越快。正解:索引越多写入越慢、空间越大、优化器越易选错。
- 误区:小表验证过索引就可直接上线大表。正解:小表数据分布与基数不代表线上,必须在真实量级压测。
追问
- 问:怎么验证索引有效?答:计划看访问路径与命中行数,
EXPLAIN ANALYZE类工具核对实际行数与耗时。 - 问:索引设计文档要记录什么?答:服务的查询、列顺序、覆盖列、创建理由与变更时间,便于后续评审。
05.1.1.5-002 如何梳理核心查询?
频率 ★★★★☆ | 难度 进阶
一句话结论
从慢查询日志、接口监控与代码 SQL 入手,按 QPS、延迟占比与业务重要性排序,提取每条查询的过滤列、排序列与返回列,作为索引设计输入。
概念解释
- 来源:慢日志、性能监控、ORM 统计与代码审计。
- 提取要素:
WHERE列、ORDER BY/GROUP BY列、SELECT返回列。 - 排序口径:高频与高延迟优先,核心业务链路优先。
原理流程
- 收集慢查询与高频 SQL 模板,合并相同形态。
- 标注每条查询的过滤、排序、返回列与 QPS/延迟。
- 按优先级分组,先服务头部查询,再处理长尾。
代码 / 命令
-- 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时都要维护。 - 优化器负担:候选索引过多增加计划选择范围,也可能误选。
- 业务差异:读多写少表可适当放宽,高频写表严格控制。
原理流程
- 统计现有索引服务的查询与使用频次。
- 剔除无查询命中的索引,合并可复用的联合前缀。
- 新增索引前评估已有索引能否覆盖该查询。
代码 / 命令
-- 列出单表所有索引
SHOW INDEX FROM orders;
常见误区
- 误区:按字段个数建索引,一个列一个索引。正解:应按查询模式组合成联合索引,减少索引数量。
- 误区:索引数量上限是硬性规定。正解:3~5 是经验值,实际按写入吞吐与查询收益权衡。
追问
- 问:索引过多如何拖慢写入?答:单行写入要维护所有相关索引项,写入放大随索引数线性增加。
- 问:什么情况下可以放宽数量?答:报表/读多写少的归档表,查询复杂且写入低频时可多建覆盖索引。
05.1.1.5-004 什么是冗余索引和重复索引?
频率 ★★★☆☆ | 难度 进阶
一句话结论
重复索引是列与顺序完全相同的索引;冗余索引是已有索引可通过最左前缀完全覆盖另一索引(如 (a,b) 已存在再建 (a));唯一索引与普通索引语义不同,不能简单判为冗余删除。
概念解释
- 重复索引:同列同顺序重复创建,维护两份相同结构。
- 冗余索引:前缀被另一索引包含,可被复用。
- 例外:唯一索引保证约束,普通索引不保证,二者并存有语义价值。
原理流程
- 对比各索引的列集合与顺序。
- 判断前缀包含关系与唯一性语义。
- 确认无依赖后,按流程清理重复与冗余索引。
代码 / 命令
SHOW INDEX FROM t;
-- MySQL 8.0:冗余索引视图(口径以版本为准)
SELECT * FROM sys.schema_redundant_indexes;
常见误区
- 误区:
(a,b)存在时(b,a)也是冗余。正解:顺序不同前缀不同,不是冗余索引。 - 误区:冗余索引可以直接线上删除。正解:先确认无查询依赖、保留回滚脚本,走变更流程。
追问
- 问:冗余索引有什么危害?答:写入放大、空间浪费,还可能干扰优化器选择。
- 问:唯一索引被普通索引前缀包含时怎么处理?答:保留唯一索引满足约束,普通冗余部分评估移除。
05.1.1.5-005 如何识别无用索引?
频率 ★★★★☆ | 难度 进阶
一句话结论
通过性能统计中的索引读取计数与近期使用周期判断:长期读计数为 0、无查询命中的索引为候选无用索引;确认后结合写入频率与历史流量季节性,走流程下线。
概念解释
- 使用统计:索引级别的读/写次数与等待统计。
- 观察周期:覆盖一个完整业务周期,避免误删季节性查询依赖的索引。
- 写入成本:长期无用的索引持续消耗写入与空间,应优先清理。
原理流程
- 采集索引使用计数,列出读计数为 0 的索引。
- 核对 SQL 与慢日志,确认近期与历史峰值流量都未使用。
- 评估下线影响,备份定义后按流程删除并观察。
代码 / 命令
-- 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。
- 评审维度:前缀顺序、选择性、覆盖宽度、与现有索引冗余。
- 风险记录:写入放大、变更方式、回滚步骤。
原理流程
- 提交索引方案:SQL 样本、过滤/排序/返回列、预期收益。
- 评审核对最左前缀、列顺序、宽度与冗余关系。
- 确认变更方式与回滚脚本,上线后监控计划与慢查询。
代码 / 命令
评审材料:
服务的 SQL 模板与 QPS;索引列角色(等值/范围/排序/覆盖)
与现有索引的关系(新增/复用/替代);写入影响评估;回滚脚本
常见误区
- 误区:评审只讨论加不加索引。正解:还要评估能否用已有索引覆盖、是否存在更窄的等价方案。
- 误区:评审通过即可长期有效。正解:查询模式变化后方案需重新评审,索引不是一次定终身。
追问
- 问:评审时怎么判断索引是否冗余?答:对照现有索引前缀与唯一性语义,确认可被复用再决定合并或删除。
- 问:评审记录保存在哪里?答:随表结构文档记录索引理由与变更历史,便于后续追溯。
05.1.1.5-007 上线前如何用执行计划验证索引?
频率 ★★★☆☆ | 难度 进阶
一句话结论
用 EXPLAIN 核对访问类型、命中索引、估算行数与 Extra 标记,确认走了预期前缀、无意外回表或 filesort;关键查询再用实际执行计划核对估算与实测偏差。
概念解释
- 访问类型:
const/ref/range较好,ALL为全表扫描。 - 命中索引:
key列显示实际使用的索引。 - 标记核对:
Using index覆盖、Using index condition下推、Using filesort/temporary需关注。
原理流程
- 在与线上相近数据量环境执行
EXPLAIN。 - 核对
type/key/rows/filtered/Extra是否符合预期。 - 关键语句用实际执行计划对比估算与实测行数。
代码 / 命令
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 问题。
原理流程
- 构造或导出演线上量级的测试数据。
- 跑目标查询对比有无索引的耗时、扫描行数与缓冲命中。
- 评估索引在真实量级下的收益与写入成本。
代码 / 命令
压测记录:
数据量 / 基数分布;有无索引下的耗时、扫描行数、回表次数
写入场景下单行维护索引的额外成本
常见误区
- 误区:功能跑通就说明索引有效。正解:功能正确与性能达标是两件事,量级差异下计划可能完全不同。
- 误区:压测只测查询不测写入。正解:索引收益在查询侧,成本在写入侧,两侧都要评估。
追问
- 问:没有生产量级数据怎么办?答:按线上基数分布造数,或在隔离环境用脱敏数据回放,避免只靠小样本判断。
- 问:压测结果如何对照生产?答:重点看扫描行数与访问路径是否一致,单耗时受硬件影响需结合比例判断。
05.1.1.5-009 索引写入放大怎么评估?
频率 ★★★☆☆ | 难度 进阶
一句话结论
每次写行都要维护相关索引:插入新增索引项,更新索引列先删旧项再插新项,删除移除索引项;评估新增索引带来的额外索引项数量、页维护与 undo/redo 开销,对照表写入频率判断。
概念解释
- 插入维护:每个相关索引插入一项。
- 更新维护:索引列变化触发旧项删除与新项插入。
- 放大来源:随机页修改、页分裂与日志记录。
原理流程
- 统计表写入 QPS 与涉及索引列的更新比例。
- 估算新增索引单行维护成本(项宽度、页访问)。
- 结合写入峰值判断是否可接受,必要时缩减索引列。
代码 / 命令
写入维护:
INSERT:聚簇 + 每个二级索引各插一项
UPDATE 索引列:旧项删除 + 新项插入(各相关索引)
DELETE:各索引移除项
常见误区
- 误区:索引只影响查询时的读取。正解:写入路径上每个索引都有同步维护成本。
- 误区:更新非索引列也维护该索引。正解:只有索引列值变化才触发对应索引项更新,不涉及列的索引不受影响。
追问
- 问:怎么量化写入放大?答:对比加索引前后的写入耗时、redo/undo 量与索引页修改次数。
- 问:高频写表怎么控制?答:收紧索引数量,覆盖列只保留高频必需列。
05.1.1.5-010 索引空间与缓冲池占用怎么评估?
频率 ★★☆☆☆ | 难度 基础
一句话结论
每个二级索引是独立 B+ 树,空间为索引列宽度乘行数并含页开销;热点索引页进入缓冲池与数据页争用内存,规划容量时需把索引空间与缓存占用一并计入。
概念解释
- 表空间:索引数据存于独立索引段,占用磁盘空间。
- 缓冲池竞争:热点索引页与数据页共享内存容量。
- 冗余放大:宽主键、宽覆盖列放大每个索引项。
原理流程
- 按索引列字节数乘行数估算体积。
- 结合页填充率与碎片修正空间需求。
- 评估热点索引页占缓冲池比例,避免挤占数据缓存。
代码 / 命令
-- 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 负载、锁等待、连接数。
原理流程
- 评估表数据量、磁盘空间与变更窗口。
- 选择 Online DDL 或影子表工具,明确算法与锁级别。
- 低峰执行,监控延迟与负载,完成后
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原地构建 vsCOPY拷贝重建;锁级别LOCK=NONE/SHARED/EXCLUSIVE。 - MDL 排队:变更前后需元数据锁,长事务持有会阻塞后续读写。
- 版本边界:不同变更类型的在线支持程度不同,需核对文档。
原理流程
- 确认变更类型是否支持
INPLACE + LOCK=NONE。 - 变更开始申请元数据锁,等待持有锁的事务结束。
- 构建期间后台扫描与写入并行,结束阶段再次排队元数据锁提交。
代码 / 命令
-- 查看 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 互换。
原理流程
- 建新影子表并批量回填存量数据。
- 持续同步原表增量写入。
- 追平后短暂加锁切换表名,旧表保留回滚。
代码 / 命令
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,影响在线请求。
原理流程
- 估算索引体积与临时空间需求,确认磁盘余量。
- 评估变更窗口内 IO/CPU 余量与复制延迟水位。
- 准备回滚空间与预案,低峰执行并设置监控阈值。
代码 / 命令
评估清单:
磁盘余量 ≥ 索引体积 + 临时构建空间
当前 IO/CPU/主从延迟水位;变更窗口长度
锁等待与长事务情况;回滚脚本与旧表保留方案
常见误区
- 误区:只评估索引最终体积。正解:构建期间的临时日志、影子表与碎片空间同样需要预留。
- 误区:白天业务低峰就可以随便加索引。正解:需看复制延迟与 IO 余量,避免变更影响从库与在线请求。
追问
- 问:空间不足会怎样?答:构建中途失败,回滚可能耗时,且残留临时对象需清理。
- 问:怎么预估索引体积?答:按索引列宽度乘行数,参考现有同类索引大小推算。
05.1.1.5-015 索引变更为什么会造成主从延迟?
频率 ★★★☆☆ | 难度 进阶
一句话结论
DDL 与大批量索引构建在主库占用资源,从库回放相同变更时单线程/并行回放能力有限,构建期间主库已承接新写入,从库回放落后形成延迟;复制原理与延迟监控详见 05.1.4.1。
概念解释
- 回放压力:从库按顺序回放主库日志,DDL/大事务回放耗时。
- 并行限制:历史上从库回放并行度有限,大事件容易堆积。
- 延迟传导:延迟期间从库读到旧数据,影响读写分离一致性。
原理流程
- 主库执行加索引 DDL 并记录日志。
- 从库拉取日志回放,构建索引过程持续消耗。
- 主库新事务持续写入,从库回放追不上则延迟累积。
代码 / 命令
-- MySQL:查看复制延迟(视图名以版本为准)
SHOW REPLICA STATUS\G
-- 关注 Seconds_Behind_Source 与回放位点
常见误区
- 误区:加索引只在主库执行,从库不受影响。正解:DDL 与数据变更都要在从库回放,延迟风险同样存在。
- 误区:延迟只是展示问题。正解:读写分离下从库延迟会读到旧数据,需一致性策略兜底。
追问
- 问:怎么降低变更期间延迟?答:低峰执行、控制变更批量、监控延迟阈值,必要时分批/工具化变更。
- 问:大事务回放为什么慢?答:事件体积大、回放顺序受限,详见 05.1.4.1 与 05.1.2.4。
05.1.1.5-016 索引监控与定期清理怎么做?
频率 ★★★☆☆ | 难度 进阶
一句话结论
建立索引使用监控(读计数、慢查询命中、索引大小),按周期评审无用与冗余索引,结合业务季节性确认后下线;删除后持续观察慢查询与计划变化,保证可回滚。
概念解释
- 监控指标:索引扫描次数、用户查询命中、索引体积增长。
- 评审周期:按业务节奏定期复核,而非一次性清理。
- 下线观察:删除后监控慢查询与计划,异常则按脚本回滚。
原理流程
- 持续采集索引使用与体积数据。
- 周期列出读计数为 0、冗余与超宽索引候选。
- 确认无依赖后下线,保留定义备份,观察线上指标。
代码 / 命令
-- 定期导出索引定义作为备份
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语句,可快速执行。 - 观察期:旧索引不立即删除,保留回退路径。
原理流程
- 变更前记录现有索引结构与新索引 DDL。
- 上线后监控查询计划、慢查询与写入指标。
- 异常则执行回滚脚本;稳定后再评估旧索引下线。
代码 / 命令
-- 回滚:移除新增索引
DROP INDEX idx_status_time ON orders;
-- 被替代的旧索引:观察期内保留,确认稳定后再删除
常见误区
- 误区:新索引上线后立即删掉旧索引。正解:先保留观察期,出现计划回退时可快速切回。
- 误区:回滚就是再建一次旧索引。正解:旧索引未删除时无需重建;若已删除,回滚需重新走在线变更。
追问
- 问:回滚删索引会锁表吗?答:删除二级索引通常可在线执行,但仍占用资源,需低峰并监控。
- 问:怎么判断新索引上线后变差?答:对比变更前后慢查询、计划路径与写入延迟,指标恶化再回滚。
05.1.1.5-018 分区表与索引的关系是什么?
频率 ★★☆☆☆ | 难度 进阶
一句话结论
分区把表按规则切成多个分区,索引可建在分区内(本地索引)或全局维护;分区裁剪依赖分区键条件,索引设计需与分区规则配合,避免跨分区扫描。分区细节见 05.2 / 05.3 对应章节。
概念解释
- 分区裁剪:查询条件命中分区键时只扫描相关分区。
- 本地/全局索引:本地索引依附分区,全局索引跨分区维护(引擎支持不同)。
- 索引配合:分区键与索引前缀协调,减少跨分区访问。
原理流程
- 按访问模式选择分区键与分区策略。
- 索引设计保证分区内查询走索引。
- 查询带分区键条件以触发裁剪,避免全分区扫描。
代码 / 命令
-- 概念示例:按时间分区(语法随引擎不同,具体以 MySQL/PG 文档为准)
-- CREATE TABLE orders (...) PARTITION BY RANGE (create_time) (...)
常见误区
- 误区:分区能替代索引。正解:分区减少扫描范围,分区内过滤仍需索引支撑。
- 误区:跨分区查询和单分区性能一样。正解:跨多个分区扫描与合并成本上升,分区设计应匹配主要查询条件。
追问
- 问:分区键与索引前缀怎么配合?答:高频条件尽量同时覆盖分区键与索引前缀,先裁剪分区再走索引。
- 问:全局索引与本地索引怎么选?答:随引擎支持与跨分区查询需求而定,具体实现见 05.2.2 / 05.3.2。
参考来源
- MySQL 8.0 Reference Manual: Adding Indexes to InnoDB Tables:https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html
- MySQL 8.0 Reference Manual: Verifying Index Usage:https://dev.mysql.com/doc/refman/8.0/en/checking-index-membership.html
- PostgreSQL Documentation: Partitioned Tables:https://www.postgresql.org/docs/current/ddl-partitioning.html
- JavaGuide:MySQL 索引:https://javaguide.cn/database/mysql/mysql-index.html