各位观众老爷，早上好！我是今天的主讲人，咱们今天聊点儿MySQL里比较有意思的东西：Invisible Indexes（不可见索引）和 Online DDL（在线数据定义语言）的协同工作。
先来个开胃小菜，大家有没有遇到过这种情况：数据库里索引一大堆，感觉每个都像有用，但又不敢轻易删，万一删错了线上血崩怎么办？ 或者，想加个新索引，又怕锁表影响业务，只能等到半夜三更偷偷摸摸地搞？
别慌，MySQL早就为大家考虑到了这些问题， Invisible Indexes 和 Online DDL 就是解决这些痛点的两大利器。
一、 Invisible Indexes：索引界的“隐身侠”
1. 什么是 Invisible Indexes？
顾名思义，Invisible Indexes 就是“不可见”的索引。 啥意思呢？ 就是说，这个索引虽然存在于数据库中，但是优化器默认情况下不会使用它。
这个特性非常有用，主要体现在以下几个方面：
- 安全删除索引： 在删除索引之前，先将其设置为 Invisible，观察一段时间，如果系统运行正常，证明这个索引确实没啥用，就可以放心地删除了。
- 测试新索引： 在生产环境创建新索引后，先将其设置为 Invisible，然后通过
optimizer_switch参数手动开启使用，观察性能是否提升，再决定是否正式启用。 - 控制索引的使用： 在某些情况下，我们可能希望强制优化器不使用某个索引，就可以将其设置为 Invisible。
2. 如何创建和修改 Invisible Indexes？
创建 Invisible Indexes 非常简单，只需要在 CREATE INDEX 语句中加上 INVISIBLE 关键字即可：
CREATE INDEX idx_name ON table_name (column_name) INVISIBLE;如果要修改一个已有的索引为 Invisible，可以使用 ALTER TABLE 语句：
ALTER TABLE table_name ALTER INDEX idx_name INVISIBLE;同样，将 Invisible 索引设置为可见，也是用 ALTER TABLE 语句：
ALTER TABLE table_name ALTER INDEX idx_name VISIBLE;3. 如何让优化器使用 Invisible Indexes？
默认情况下，优化器不会使用 Invisible Indexes。 如果想让优化器使用它，需要修改 optimizer_switch 系统变量。
-- 全局生效
SET GLOBAL optimizer_switch='use_invisible_indexes=on';
-- 会话级别生效
SET SESSION optimizer_switch='use_invisible_indexes=on';开启之后，优化器在选择执行计划时，就会考虑 Invisible Indexes 了。
4. 举个栗子
假设我们有一张 users 表，结构如下：
CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
email VARCHAR(255) NOT NULL,
age INT,
city VARCHAR(255)
);
-- 插入一些测试数据
INSERT INTO users (name, email, age, city) VALUES
('张三', '[email protected]', 25, '北京'),
('李四', '[email protected]', 30, '上海'),
('王五', '[email protected]', 28, '广州'),
('赵六', '[email protected]', 35, '深圳');现在，我们想删除 city 列上的索引，但是又不敢直接删，怎么办？
- 创建 Invisible 索引
首先，创建 city 列上的索引，并将其设置为 Invisible：
CREATE INDEX idx_city ON users (city) INVISIBLE;- 观察一段时间
观察一段时间，比如一周或者一个月，看看系统运行是否正常。 如果没有出现任何问题，说明这个索引确实没啥用。
- 删除索引
ALTER TABLE users DROP INDEX idx_city;如果在这期间，发现系统性能下降，说明这个索引还是有用的，可以将其设置为 Visible：
ALTER TABLE users ALTER INDEX idx_city VISIBLE;5. 注意事项
- PRIMARY KEY 必须是 Visible 的，不能设置为 Invisible。
- UNIQUE 索引如果设置为 Invisible，可能会导致数据插入失败。 因为 UNIQUE 索引的主要作用是保证数据的唯一性，如果设置为 Invisible，可能会导致插入重复数据。
- 查看索引是否可见，可以通过
SHOW INDEXES命令，或者查询information_schema.statistics表。
二、 Online DDL：让 DDL 操作不再“锁表”
1. 什么是 Online DDL？
Online DDL，顾名思义，就是在执行 DDL 操作（比如添加/删除索引、修改列类型等）时，尽量减少对业务的影响，避免长时间的锁表。
在 MySQL 5.6 之前，执行 DDL 操作通常需要锁表，这会导致业务中断。 从 MySQL 5.6 开始，引入了 Online DDL，可以在执行 DDL 操作的同时，允许读写操作，大大提高了数据库的可用性。
2. Online DDL 的实现原理
Online DDL 的实现原理主要依赖于以下两种算法：
- INPLACE 算法： 在原表上直接进行修改，不需要创建临时表。 这种算法速度快，但是限制较多，比如不能修改列的类型。
- COPY 算法： 创建一个临时表，将原表的数据复制到临时表中，然后在临时表上进行修改，最后将临时表替换为原表。 这种算法速度慢，但是限制较少，可以修改列的类型。
MySQL 在执行 DDL 操作时，会根据具体的操作和参数，自动选择合适的算法。
3. Online DDL 的语法
在执行 DDL 操作时，可以通过 ALGORITHM 和 LOCK 参数来控制 Online DDL 的行为。
- ALGORITHM： 指定 DDL 操作使用的算法。 可以取以下值：
DEFAULT： 默认算法，由 MySQL 自动选择。INPLACE： 使用 INPLACE 算法。COPY： 使用 COPY 算法。
- LOCK： 指定 DDL 操作使用的锁级别。 可以取以下值：
DEFAULT： 默认锁级别，由 MySQL 自动选择。NONE： 允许并发的读写操作。SHARED： 允许并发的读操作，禁止写操作。EXCLUSIVE： 排他锁，禁止并发的读写操作。
例如，要使用 INPLACE 算法创建一个索引，可以使用以下语句：
ALTER TABLE table_name ADD INDEX idx_name (column_name), ALGORITHM=INPLACE, LOCK=NONE;4. Online DDL 的限制
虽然 Online DDL 可以减少对业务的影响，但是它也有一些限制：
- 并非所有的 DDL 操作都支持 Online DDL。
- Online DDL 操作仍然会占用一定的资源，可能会影响数据库的性能。
- 在执行 Online DDL 操作期间，可能会出现短暂的锁表。
5. 举个栗子
假设我们需要在 users 表的 email 列上添加一个索引，但是又不想影响业务，怎么办？
ALTER TABLE users ADD INDEX idx_email (email), ALGORITHM=INPLACE, LOCK=NONE;这条语句会使用 INPLACE 算法创建一个索引，并且允许并发的读写操作。 这样就可以在不影响业务的情况下，完成索引的创建。
6. 注意事项
- 在执行 Online DDL 操作之前，最好先进行测试，评估其对数据库性能的影响。
- 在执行 Online DDL 操作期间，要密切关注数据库的运行状态，及时发现和解决问题。
- 不同的 MySQL 版本对 Online DDL 的支持程度可能不同，要注意查看官方文档。
三、 Invisible Indexes 与 Online DDL 的协同工作
Invisible Indexes 和 Online DDL 可以协同工作，共同解决索引管理的问题。
例如，我们可以先使用 Online DDL 创建一个 Invisible 索引，然后在生产环境观察一段时间，如果性能提升明显，就可以将其设置为 Visible。
-- 使用 Online DDL 创建一个 Invisible 索引
ALTER TABLE users ADD INDEX idx_email (email) INVISIBLE, ALGORITHM=INPLACE, LOCK=NONE;
-- 观察一段时间，如果性能提升明显，就将其设置为 Visible
ALTER TABLE users ALTER INDEX idx_email VISIBLE;这样可以最大限度地降低风险，保证数据库的稳定性和可用性。
四、 总结
今天我们学习了 Invisible Indexes 和 Online DDL 的基本概念、使用方法和注意事项。 希望大家能够灵活运用这两个特性，更好地管理 MySQL 数据库的索引，提高数据库的性能和可用性。
| 特性 | 作用 | 优点 | 缺点 | 使用场景 |
|---|---|---|---|---|
| Invisible Indexes | 允许创建对优化器不可见的索引，主要用于安全删除索引、测试新索引和控制索引的使用。 | 1. 安全删除索引：在删除索引之前，先将其设置为 Invisible，观察一段时间，如果系统运行正常，证明这个索引确实没啥用，就可以放心地删除了。 2. 测试新索引：在生产环境创建新索引后，先将其设置为 Invisible，然后通过 optimizer_switch 参数手动开启使用，观察性能是否提升，再决定是否正式启用。 3. 控制索引的使用：在某些情况下，我们可能希望强制优化器不使用某个索引，就可以将其设置为 Invisible。 |
1. PRIMARY KEY 必须是 Visible 的，不能设置为 Invisible。 2. UNIQUE 索引如果设置为 Invisible，可能会导致数据插入失败。 因为 UNIQUE 索引的主要作用是保证数据的唯一性，如果设置为 Invisible，可能会导致插入重复数据。 3. 查看索引是否可见，可以通过 SHOW INDEXES 命令，或者查询 information_schema.statistics 表。 |
1. 评估现有索引的有效性，安全删除无用索引。 2. 在生产环境中测试新索引，评估其性能影响。 3. 临时禁用某些索引，用于问题排查或性能调优。 |
| Online DDL | 允许在执行 DDL 操作（比如添加/删除索引、修改列类型等）时，尽量减少对业务的影响，避免长时间的锁表。 | 1. 减少锁表时间，提高数据库的可用性。 2. 可以在业务高峰期执行 DDL 操作，无需等到业务低谷期。 | 1. 并非所有的 DDL 操作都支持 Online DDL。 2. Online DDL 操作仍然会占用一定的资源，可能会影响数据库的性能。 3. 在执行 Online DDL 操作期间，可能会出现短暂的锁表。 | 1. 在生产环境中创建、修改或删除索引，尽量避免对业务造成影响。 2. 修改表结构，例如添加、删除或修改列，尽量减少锁表时间。 3. 执行 ALTER TABLE 操作时，尽量选择 INPLACE 算法，减少资源消耗。 |
好了，今天的讲座就到这里，感谢大家的观看！ 如果大家有什么问题，欢迎随时提问。