KEEL · 龙骨 · A CURRICULUM FOR THE AI ERA

04 · 索引的代价与线上变更 — keel 龙骨

这一章回答：索引不是免费的，以及在大表上"加一个索引"为什么是一次需要预案的变更。

这一章回答：索引不是免费的，以及在大表上"加一个索引"为什么是一次需要预案的变更。

前两章都在讲怎么让索引用起来。这一章讲反面：索引本身的成本，以及在线上变更索引时如何不出事故。这是从"会设计"到"敢上线"的分界。

一、索引的四项成本

成本	具体表现	量化方式
写放大	每次 INSERT/UPDATE/DELETE 都要维护所有二级索引	对比加索引前后的写入 TPS
空间	索引与数据同量级甚至更大	`information_schema.tables` 的 `index_length`
内存挤占	索引占用 buffer pool，挤走数据页	`innodb_buffer_pool_stats`
优化器负担	索引越多，优化器选择成本越高，也更容易误判	索引多时 `EXPLAIN` 明显变慢就是信号

SELECT table_name,
       ROUND(data_length/1024/1024)  AS data_mb,
       ROUND(index_length/1024/1024) AS index_mb
FROM information_schema.tables
WHERE table_schema = 'app' ORDER BY index_mb DESC LIMIT 10;

一条实用经验：如果 index_mb 接近或超过 data_mb，这张表的索引已经过度了——通常意味着有冗余索引或历史遗留。

二、识别冗余索引与未使用索引

冗余索引（左前缀重复）

-- sys 库提供现成视图
SELECT * FROM sys.schema_redundant_indexes WHERE table_schema='app'\G

典型情形：已有 (a, b, c)，又单独建了 (a) ——后者完全被前者覆盖，可以直接删。

⚠️ 例外：如果 (a) 是唯一索引或有特殊约束，不能删；另外前缀索引 (a(10)) 与 (a) 不构成冗余。

未使用索引

SELECT * FROM sys.schema_unused_indexes WHERE table_schema='app';

-- 更精确：看 performance_schema 的统计（8.0）
SELECT object_schema, object_name, index_name, count_star
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema='app' AND index_name IS NOT NULL AND count_star = 0
ORDER BY object_name;

判据要谨慎：

performance_schema 的统计在实例重启后清零，跑了一天的实例得出的"未使用"不可信；
有些索引服务于低频但关键的查询（月度报表、对账、运维排查），删了会在关键时刻出事；
唯一索引即使没被查询使用，也在承担约束职责，不能删。

删除流程：先标记观察一个完整业务周期（至少覆盖月末/大促等低频场景）→ 备份索引定义 → 删除 → 观察慢日志与错误率 → 出问题立刻按定义重建。

SHOW CREATE TABLE orders\G   -- 删除前务必保存完整建表语句

三、统计信息与索引维护

ANALYZE TABLE orders;                     -- 重新采样统计信息（轻量，可在线执行）
SHOW INDEX FROM orders;                   -- 看 Cardinality 是否合理
OPTIMIZE TABLE orders;                    -- 重建表（会锁表！大表慎用，等同 DDL）

操作	代价	何时用
`ANALYZE TABLE`	低（采样读）	大批量写入/删除后，或优化器明显误判时
`OPTIMIZE TABLE`	高（重建 + 锁表）	大量删除后回收空间；大表应改用 gh-ost 类工具

⚠️ OPTIMIZE TABLE 在大表上等同于一次 DDL，必须走下面的变更流程。MySQL 8.0 的 ALTER TABLE ... FORCE 同理。

四、线上加索引：一次需要预案的变更

风险点

锁表时间：MySQL 5.5 及以前的加索引是全程锁表；5.6+ 支持 Online DDL，但开始与结束阶段仍需要短暂的排他锁（等待其它事务释放 MDL）；
MDL 阻塞：如果有一个长事务没提交，ALTER TABLE 会等待 MDL 锁，而后续所有针对该表的查询都会被阻塞——这会引发雪崩；
主从延迟：大表加索引在从库回放会造成明显延迟；
磁盘与 IO 峰值：建索引要排序，可能打满 IO 和临时空间。

标准流程

① 前置检查
   - 确认无长事务：SELECT * FROM information_schema.innodb_trx WHERE TIMEDIFF(NOW(), trx_started) > '00:00:10';
   - 确认磁盘剩余空间 > 表大小 × 1.5
   - 确认当前主从延迟正常

② 选择方案
   - 小表（< 100 万行）且低峰：直接 Online DDL
   - 大表或核心表：gh-ost / pt-online-schema-change（影子表 + 增量回放 + 切换）

③ 灰度
   - 先在从库执行 → 观察 → 再切主库执行（gh-ost 支持先在从库测）

④ 执行与观察
   - 盯：Threads_running、主从延迟、慢日志、错误率
   - 出现 MDL 等待激增 → 立刻取消（gh-ost 支持随时中断，这是它比直连 DDL 安全的核心原因）

⑤ 回滚预案
   - gh-ost：中断即可，原表未动
   - Online DDL：删除索引（同样需要 DDL 时间）
   - 备份：执行前保存 SHOW CREATE TABLE 结果

# gh-ost 示例（大表首选）
gh-ost \
  --alter="ADD INDEX idx_tenant_created (tenant_id, created_at)" \
  --database=app --table=orders \
  --host=127.0.0.1 --user=gh_user --password=... \
  --allow-on-master --chunk-size=1000 --max-load=Threads_running=50 \
  --critical-load=Threads_running=100 \
  --exact-rowcount --execute

关键参数：--max-load 达到阈值时 gh-ost 会自动暂停，--critical-load 会中止。这两个参数是它的安全阀。

五、权限受限环境下的索引变更（真实场景）

很多生产环境里，应用账号只有 DML 权限（SELECT/INSERT/UPDATE/DELETE），没有 INDEX/ALTER/DROP。这时：

应用代码里如果依赖"启动时自动建索引/建表"（如 Base.metadata.create_all()），会直接报错：ERROR 1142 (42000): INDEX command denied to user；
更隐蔽的情况：ORM 把索引编译成独立的 CREATE INDEX 语句，即使表已存在也会执行，启动即失败。

三种合规解法：

方案	做法	适用
索引写在建表 DDL 里	用 `KEY idx_x (col)` 内联在 `CREATE TABLE` 中，而不是独立 `CREATE INDEX`	初始化脚本、迁移工具，权限只需 CREATE
DDL 走独立运维通道	应用只用 DML 账号；DDL 由迁移脚本/DBA 用高权限账号执行	生产标准做法
迁移前校验权限	在部署流程里加一步权限检查，提前暴露而不是启动崩溃	所有环境

-- ✅ 索引内联在表定义里（一次 CREATE 完成）
CREATE TABLE orders (
  id BIGINT NOT NULL AUTO_INCREMENT,
  tenant_id BIGINT NOT NULL,
  created_at DATETIME NOT NULL,
  PRIMARY KEY (id),
  KEY idx_tenant_created (tenant_id, created_at)   -- 不需要 INDEX 权限
) ENGINE=InnoDB;

-- ❌ 独立语句（需要 INDEX 权限，受限账号会 1142 报错）
CREATE INDEX idx_tenant_created ON orders (tenant_id, created_at);

这是"本地跑得好好的，上线启动就崩"的经典原因之一，根源是本地用的是高权限账号而生产不是。

动手：可观察结果

产出	判断标准
一份索引成本清单	每张核心表的 `data_length` vs `index_length`，标出索引大于数据的表
冗余/未使用索引候选	列出候选，并说明每条的删除风险（是否唯一索引、是否服务低频查询）
一次索引删除演练	保存 `SHOW CREATE TABLE` → 删除 → 观察 → 需要时按备份重建（在测试库）
一次大表加索引演练	用 gh-ost 完成，记录 `max-load` 触发时的暂停行为与耗时
权限受限验证	用只有 DML 权限的账号执行 `CREATE INDEX`，复现 1142 并给出合规写法

完成标志：你能独立完成一次大表索引变更：有前置检查、有中断手段、有回滚路径，且能说清楚每一步失败时的表现。

故障注入

注入方式	观察
在加索引期间开一个长事务不提交	是否出现 MDL 等待、后续查询是否被阻塞
给一张 1000 万行的核心表直接 `ALTER TABLE ADD INDEX`	耗时、Threads_running、主从延迟
磁盘剩余空间不足时加索引	是否失败、失败后表是否可用
用无 INDEX 权限的账号执行 `CREATE INDEX`	复现 1142，验证内联 KEY 写法可行
删除一个实际被月度报表使用的索引	观察期不足会导致什么后果（演练中模拟）
实例刚重启就查 `schema_unused_indexes`	统计是否为空、结论是否可信

自测题

索引的四项成本分别是什么？你用什么指标量化写放大？
发现一个"未被使用"的索引，直接删除安全吗？列出至少三条判断依据。
大表加索引为什么会引发雪崩？关键机制是什么（提示：MDL）？
gh-ost 比直接 Online DDL 安全的核心原因是什么？它的两个安全阀参数是什么？
应用账号没有 INDEX 权限时，如何保证索引仍然能被创建？给出两种写法并说明区别。

进入 keel 阅读