MySQL / 系统课时
在线创建与删除二级索引
在线创建与删除二级索引
本课以 MySQL 8.4 与 InnoDB 为基线,完成普通二级索引的在线新增、可见性切换和安全删除。重点是把构建、观察、计划验证和灰度移除串成可回退的变更流程。
学习目标
- 使用
ALGORITHM=INPLACE与LOCK=NONE在线新增、删除普通二级索引 - 通过 Performance Schema 观察索引构建,并理解并发 DML 的影响
- 用不可见索引验证查询计划变化,再决定保留或删除索引
先确认在线操作的边界
对 InnoDB 的普通 B+Tree 二级索引,新增索引支持 INPLACE,在支持条件下可以允许并发读写;删除二级索引也可以在线完成。这里讨论普通二级索引,不把 FULLTEXT、SPATIAL 或主键变更混在一起,因为它们的锁与重建规则不同。
“在线”不等于“没有影响”。创建索引仍要扫描聚簇索引并构建新的索引结构,会消耗 CPU、I/O、磁盘和在线 DDL 资源;DDL 的准备与提交阶段还可能等待元数据锁。因此上线前要先检查磁盘余量、长事务和业务高峰。
先以不可见索引完成新增
下面建立一个可独立运行的实验表,并新增一个不可见的唯一二级索引。不可见索引仍会被维护,也仍执行唯一性约束,只是默认不参与优化器选路。
CREATE DATABASE IF NOT EXISTS ddl_demo;
USE ddl_demo;
DROP TABLE IF EXISTS orders;
CREATE TABLE orders (
id BIGINT PRIMARY KEY,
tenant_id BIGINT NOT NULL,
external_no VARCHAR(32) NOT NULL,
amount DECIMAL(10,2) NOT NULL
) ENGINE=InnoDB;
INSERT INTO orders VALUES
(1, 10, 'A001', 99.00),
(2, 10, 'A002', 120.00),
(3, 20, 'B001', 88.00);
ALTER TABLE orders
ADD UNIQUE INDEX uk_tenant_external (tenant_id, external_no) INVISIBLE,
ALGORITHM=INPLACE,
LOCK=NONE;预期结果
Query OK, 0 rows affected (<TIME> sec)实际耗时取决于表大小与负载。用 INFORMATION_SCHEMA.STATISTICS 验证索引已经建立且暂时不可见。
SELECT INDEX_NAME, NON_UNIQUE, IS_VISIBLE
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'ddl_demo'
AND TABLE_NAME = 'orders'
AND INDEX_NAME = 'uk_tenant_external';预期结果
+----------------------+------------+------------+
| INDEX_NAME | NON_UNIQUE | IS_VISIBLE |
+----------------------+------------+------------+
| uk_tenant_external | 0 | NO |
+----------------------+------------+------------+
观察构建进度与查询计划
大表建索引时,可以在另一个会话查看 Performance Schema 的阶段进度。WORK_COMPLETED 与 WORK_ESTIMATED 是抽象工作量,阶段和估算值会变化;如果操作已经完成,当前阶段表也可能为空。
UPDATE performance_schema.setup_consumers
SET ENABLED = 'YES'
WHERE NAME = 'events_stages_current';
SELECT EVENT_NAME,
WORK_COMPLETED,
WORK_ESTIMATED,
ROUND(WORK_COMPLETED / NULLIF(WORK_ESTIMATED, 0) * 100, 1) AS progress_pct
FROM performance_schema.events_stages_current
WHERE EVENT_NAME LIKE 'stage/innodb/alter table%';预期结果
+------------------------------------------+----------------+----------------+--------------+
| EVENT_NAME | WORK_COMPLETED | WORK_ESTIMATED | progress_pct |
+------------------------------------------+----------------+----------------+--------------+
| stage/innodb/alter table (<VALUE>) | <VALUE> | <VALUE> | <VALUE> |
+------------------------------------------+----------------+----------------+--------------+完成构建后,再把索引设为可见并检查查询计划。可见性切换不需要重新构建索引。
ALTER TABLE orders
ALTER INDEX uk_tenant_external VISIBLE;
EXPLAIN
SELECT amount
FROM orders
WHERE tenant_id = 10
AND external_no = 'A002';预期结果
索引可见性切换成功;执行计划中的 key 可能包含 uk_tenant_external。rows、cost 等估算值会随统计信息与数据量变化。如果计划没有按预期使用索引,不要只凭 key 一个字段下结论,还要检查选择性、统计信息、谓词写法以及是否存在更低成本的访问路径。
删除前先做不可见演练
直接 DROP INDEX 的问题在于回退时需要重新构建索引。更稳妥的顺序是先把候选索引设为不可见,让真实工作负载运行一个观察窗口;如果延迟、扫描量或慢查询恶化,可以先快速恢复可见性。
ALTER TABLE orders
ALTER INDEX uk_tenant_external INVISIBLE;
SELECT INDEX_NAME, IS_VISIBLE
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'ddl_demo'
AND TABLE_NAME = 'orders'
AND INDEX_NAME = 'uk_tenant_external';预期结果
+----------------------+------------+
| INDEX_NAME | IS_VISIBLE |
+----------------------+------------+
| uk_tenant_external | NO |
+----------------------+------------+确认业务查询不再依赖该索引后,再执行真正删除。
ALTER TABLE orders
DROP INDEX uk_tenant_external,
ALGORITHM=INPLACE,
LOCK=NONE;预期结果
Query OK, 0 rows affected (<TIME> sec)删除之后还要继续观察查询延迟、rows_examined、慢日志和写入吞吐。对疑似无用的索引,先不可见、再观察、最后删除通常比直接删除更容易回退;对新增索引,则应先控制构建资源,再验证执行计划和真实业务收益。