MySQL / 系统课时

在线创建与删除二级索引

在线创建与删除二级索引

本课以 MySQL 8.4 与 InnoDB 为基线,完成普通二级索引的在线新增、可见性切换和安全删除。重点是把构建、观察、计划验证和灰度移除串成可回退的变更流程。

学习目标

  • 使用 ALGORITHM=INPLACE 与 LOCK=NONE 在线新增、删除普通二级索引
  • 通过 Performance Schema 观察索引构建,并理解并发 DML 的影响
  • 用不可见索引验证查询计划变化,再决定保留或删除索引

先确认在线操作的边界

对 InnoDB 的普通 B+Tree 二级索引,新增索引支持 INPLACE,在支持条件下可以允许并发读写;删除二级索引也可以在线完成。这里讨论普通二级索引,不把 FULLTEXT、SPATIAL 或主键变更混在一起,因为它们的锁与重建规则不同。

“在线”不等于“没有影响”。创建索引仍要扫描聚簇索引并构建新的索引结构,会消耗 CPU、I/O、磁盘和在线 DDL 资源;DDL 的准备与提交阶段还可能等待元数据锁。因此上线前要先检查磁盘余量、长事务和业务高峰。

先以不可见索引完成新增

下面建立一个可独立运行的实验表,并新增一个不可见的唯一二级索引。不可见索引仍会被维护,也仍执行唯一性约束,只是默认不参与优化器选路。

sql
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;

预期结果

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

实际耗时取决于表大小与负载。用 INFORMATION_SCHEMA.STATISTICS 验证索引已经建立且暂时不可见。

sql
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';

预期结果

text
+----------------------+------------+------------+
| INDEX_NAME           | NON_UNIQUE | IS_VISIBLE |
+----------------------+------------+------------+
| uk_tenant_external   |          0 | NO         |
+----------------------+------------+------------+

在线创建二级索引时 DDL 与并发 DML 的关系

观察构建进度与查询计划

大表建索引时,可以在另一个会话查看 Performance Schema 的阶段进度。WORK_COMPLETED 与 WORK_ESTIMATED 是抽象工作量,阶段和估算值会变化;如果操作已经完成,当前阶段表也可能为空。

sql
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%';

预期结果

text
+------------------------------------------+----------------+----------------+--------------+
| EVENT_NAME                               | WORK_COMPLETED | WORK_ESTIMATED | progress_pct |
+------------------------------------------+----------------+----------------+--------------+
| stage/innodb/alter table (<VALUE>)       | <VALUE>        | <VALUE>        | <VALUE>      |
+------------------------------------------+----------------+----------------+--------------+

完成构建后,再把索引设为可见并检查查询计划。可见性切换不需要重新构建索引。

sql
ALTER TABLE orders
    ALTER INDEX uk_tenant_external VISIBLE;

EXPLAIN
SELECT amount
FROM orders
WHERE tenant_id = 10
  AND external_no = 'A002';

预期结果

text
索引可见性切换成功;执行计划中的 key 可能包含 uk_tenant_external。rows、cost 等估算值会随统计信息与数据量变化。

如果计划没有按预期使用索引,不要只凭 key 一个字段下结论,还要检查选择性、统计信息、谓词写法以及是否存在更低成本的访问路径。

删除前先做不可见演练

直接 DROP INDEX 的问题在于回退时需要重新构建索引。更稳妥的顺序是先把候选索引设为不可见,让真实工作负载运行一个观察窗口;如果延迟、扫描量或慢查询恶化,可以先快速恢复可见性。

sql
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';

预期结果

text
+----------------------+------------+
| INDEX_NAME           | IS_VISIBLE |
+----------------------+------------+
| uk_tenant_external   | NO         |
+----------------------+------------+

确认业务查询不再依赖该索引后,再执行真正删除。

sql
ALTER TABLE orders
    DROP INDEX uk_tenant_external,
    ALGORITHM=INPLACE,
    LOCK=NONE;

预期结果

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

删除之后还要继续观察查询延迟、rows_examined、慢日志和写入吞吐。对疑似无用的索引,先不可见、再观察、最后删除通常比直接删除更容易回退;对新增索引,则应先控制构建资源,再验证执行计划和真实业务收益。

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

目录与阅读设置

记录一个想法

在线创建与删除二级索引

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

登录哪都通