MySQL / 系统课时

建立索引变更上线检查

建立索引变更上线检查

索引优化不能以“目标 SQL 变快”作为上线终点。一次可靠的索引变更还要确认 DDL 风险、其他查询计划、写入成本和线上指标都可接受,并提前定义明确的回退路径,避免局部优化演变成新的性能故障。

学习目标

  • 建立索引变更前的查询与系统性能基线
  • 使用不可见索引和执行计划降低新索引验证风险
  • 制定回归范围、上线观测指标与可执行的回退条件

把索引变更定义成可回退实验

上线前不要只保存一条慢 SQL。应记录目标查询的 SQL 指纹、典型参数、执行计划和延迟,同时保存与索引维护成本有关的写入 QPS、CPU、磁盘空间、复制延迟等指标。上线后才能判断变化究竟来自索引,还是流量、缓存或数据分布发生了变化。

变更单至少应包含以下检查项:

检查面 上线前记录 上线后比较
目标查询 执行计划、实际耗时、扫描行数 是否使用预期索引,扫描量与耗时是否下降
关联查询 使用同表的高频查询样本 是否出现计划回退或延迟升高
写入 INSERT、UPDATE、DELETE 基线 延迟、吞吐是否明显恶化
数据库资源 CPU、I/O、Buffer Pool、磁盘空间 是否出现持续异常增幅
高可用链路 复制延迟与节点状态 是否因 DDL 或写放大产生积压

索引变更从基线验证到上线回退的闭环

基线的关键不是追求固定阈值,而是让上线前后使用同一套采样口径。业务没有提供明确 SLO 时,不应临时编造“延迟超过多少毫秒就回退”;应在变更单中根据当前基线和业务容量提前填写可接受范围。

先验证 DDL,再验证执行计划

新增二级索引既会消耗构建时间和磁盘,也会增加后续写入维护成本。正式执行前应在接近生产数据规模的环境确认 DDL 能否采用预期算法和锁级别。对于 MySQL InnoDB,可以在创建时直接指定 INVISIBLE,使索引完成构建但暂不参与普通会话的优化器选择,从一开始就隔离普通会话的计划变化。

下面以订单查询需要的新联合索引为例。表名和字段应替换为实际对象;在生产执行前仍需确认当前 MySQL 版本和表结构支持指定的 DDL 方式。

sql
ALTER TABLE orders
  ADD INDEX idx_orders_tenant_status_created
    (tenant_id, status, created_at DESC) INVISIBLE,
  ALGORITHM=INPLACE,
  LOCK=NONE;

SHOW INDEX FROM orders
WHERE Key_name = 'idx_orders_tenant_status_created';

预期结果:DDL 成功后,新索引已存在且处于不可见状态;创建期间的具体耗时属于动态值。

text
Query OK, 0 rows affected (<TIME>)
...
Key_name: idx_orders_tenant_status_created
Visible: NO

不可见索引仍然会被写操作维护,因此它只能隔离查询计划变化,不能消除索引带来的写放大和空间成本。

随后在单独的验证会话中允许优化器考虑不可见索引,再执行真实且有代表性的查询。这样可以在不影响其他普通会话的情况下判断新索引是否值得启用。

sql
SET SESSION optimizer_switch = 'use_invisible_indexes=on';

EXPLAIN ANALYZE
SELECT id, created_at, total_amount
FROM orders
WHERE tenant_id = 42
  AND status = 'PAID'
ORDER BY created_at DESC
LIMIT 50;

预期结果:执行计划应出现对 idx_orders_tenant_status_created 的索引访问,并能看到实际行数与耗时;具体算子文本、行数和时间取决于数据分布。

text
<执行计划中出现 idx_orders_tenant_status_created,并包含实际 rows 与 timing 信息>

如果新索引没有被选择,不要立即使用强制索引掩盖问题。应先检查列顺序是否匹配过滤和排序、统计信息是否合理,以及新旧访问路径的实际成本差异。

回归范围要覆盖读写两侧

索引上线最容易遗漏的是“受益 SQL 之外的对象”。同一张表上的其他查询可能因为新索引改变成本估算而选择不同计划;INSERT、UPDATE 和 DELETE 则需要维护新增的 B+Tree,并可能增加页分裂、日志量和磁盘占用。

回归样本应至少覆盖三类:目标慢查询的典型参数与边界参数、访问同一张表的高频或关键查询、该表主要写入路径。验证时比较执行计划和实际指标,而不是只检查 SQL 能否正常返回。

当回归通过后,再把索引切换为可见,使普通会话可以选择它。

sql
ALTER TABLE orders
  ALTER INDEX idx_orders_tenant_status_created VISIBLE;

预期结果:索引切换为可见状态,之后新解析和优化的查询可以由优化器正常选择该索引。

text
Query OK, 0 rows affected (<TIME>)

上线观察必须绑定回退条件

索引可见后需要同时观察目标查询和数据库整体。至少检查目标查询的延迟与扫描量、同表关键查询的计划和延迟、写入吞吐与延迟、CPU 与 I/O、磁盘增长以及复制延迟。只有目标 SQL 变快而写入或其他核心查询明显退化,仍应判定本次变更失败。

回退条件必须在上线前写清楚,例如“目标查询没有达到预期改善”“关键查询出现稳定回退”“写入延迟突破既定容量线”或“复制延迟持续超过业务允许范围”。不要等故障发生后再讨论什么程度才算异常。

如果问题来自优化器开始选择新索引,可以先快速将索引重新设为不可见;如果根因是索引自身的写入维护或空间成本,则仅隐藏索引没有作用,验证业务已经恢复后还需要删除它。

sql
ALTER TABLE orders
  ALTER INDEX idx_orders_tenant_status_created INVISIBLE;

-- 确认不再需要该索引后,再执行物理删除。
DROP INDEX idx_orders_tenant_status_created ON orders;

预期结果:第一条语句先阻止普通优化器继续选择该索引;确认不再需要后执行删除,索引及其持续写入维护成本被移除。

text
Query OK, 0 rows affected (<TIME>)
Query OK, 0 rows affected (<TIME>)

删除索引本身也是一次 DDL,仍需按当前表规模和 MySQL 版本重新评估执行风险。成熟的索引上线检查不是“创建索引并观察一下”,而是一条可重复执行的闭环:保存基线、验证 DDL、隔离验证计划、回归读写路径、逐步启用、按预定义指标观察,并确保每个异常分支都有真正可执行的回退动作。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

建立索引变更上线检查

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通