发布于 由admin

MySQL高阶讲座之：`MySQL`的`Online DDL`：`ALGORITHM=INPLACE`的实现原理。

各位朋友，大家好！我是老张，今天咱们来聊聊MySQL的Online DDL，尤其是ALGORITHM=INPLACE这块儿。这可是个宝贝，能让你在不锁表的情况下，优雅地修改表结构，简直是运维的福音！

一、 为啥需要Online DDL？

话说当年，没有Online DDL的时候，修改个表结构那真是要命。你想想，一个线上跑着的表，数据量几百万甚至几亿，你要加个字段、改个类型，直接ALTER TABLE？那可是要锁表的！整个数据库都得卡住，用户直接投诉到你怀疑人生。

所以，Online DDL就应运而生了。它的目标很简单：在修改表结构的同时，尽量减少对线上业务的影响。最好就是能做到“零感知”，用户该用就用，一点感觉都没有，那才叫牛！

二、 Online DDL的三种算法：COPY、INPLACE、INSTANT

MySQL 5.6之后，引入了Online DDL，提供了三种算法：COPY、INPLACE和INSTANT。

  • COPY: 这是最原始、最粗暴的方式。它会创建一个新的表，把老表的数据复制过去，然后在新的表上执行修改，最后再把新表重命名成老表的名字。这期间，老表会被锁定，不能进行任何写入操作。虽然可以读取，但如果数据量巨大，复制过程耗时很长，影响还是很大。

    你可以想象成：你要把家里的东西搬到新房子里，得先把旧房子锁起来，然后一点一点地搬，搬完才能让别人进去。

  • INPLACE: 这种算法相对聪明一些。它允许在原表上直接进行修改，而不需要创建新表。但是，这并不意味着它完全不锁表。在修改的过程中，可能会短暂地锁定表，但锁定的时间通常很短，影响相对较小。关键是，它不需要复制数据，省去了大量的IO操作。

    你可以想象成：你要给家里的墙刷漆，不用搬家，直接在墙上刷就行了。但是，刷漆的时候，你可能需要让别人稍微避让一下，不能直接靠在墙上。

  • INSTANT: 这是最理想的算法。它几乎不需要锁定表，修改操作可以在瞬间完成。但是，它也有很大的限制，只能用于某些特定的操作，比如增加或删除表尾部的列，或者修改列的默认值。

    你可以想象成：你要在墙上贴一张画，直接贴上去就行了，完全不会影响别人。

咱们今天要重点讲的就是INPLACE算法。

三、 ALGORITHM=INPLACE的实现原理

ALGORITHM=INPLACE的核心思想是：在原表上进行修改，并尽量减少对线上业务的影响。为了实现这个目标，它采用了以下几种策略：

  1. 元数据锁（Metadata Lock，MDL）: 在修改表结构之前，MySQL会先获取一个元数据锁。这个锁可以防止其他会话同时修改同一个表结构，从而保证数据的一致性。

    你可以理解为：在刷漆之前，你要先插个牌子，告诉别人“正在施工，请勿靠近”。

  2. 表重建（Rebuild Table）: 并不是所有的INPLACE操作都不需要重建表。有些操作，比如修改列的数据类型，仍然需要重建表。但是，INPLACE算法会尽量优化重建过程，比如采用增量重建的方式，只重建需要修改的部分，而不是整个表。

    你可以理解为：如果墙上的洞比较小，你只需要把洞补上，重新刷一下就行了，不用把整面墙都拆掉重做。

  3. 排序（Sort）: 有些操作，比如添加索引，需要在已有的数据上进行排序。INPLACE算法会尽量在后台进行排序，而不是阻塞当前的查询。

    你可以理解为：你要把房间里的东西重新整理一下，可以在晚上或者周末整理，不会影响白天的工作。

  4. 临时表（Temporary Table）: 在某些情况下，INPLACE算法也会用到临时表。但是，它会尽量减少临时表的使用，并优化临时表的创建和删除过程。

    你可以理解为：刷漆的时候，你可能需要用一个桶来装油漆，但是你不会把所有的东西都放到桶里。

四、 ALGORITHM=INPLACE的适用场景

ALGORITHM=INPLACE并不是万能的，它也有一些限制。一般来说，以下几种操作可以使用ALGORITHM=INPLACE：

  • 添加索引（ADD INDEX）：创建索引时，MySQL会扫描表中的数据，并构建索引结构。这个过程可能会比较耗时，但是INPLACE算法可以尽量在后台进行，减少对线上业务的影响。

    ALTER TABLE `user` ADD INDEX `idx_name` (`name`) ALGORITHM=INPLACE, LOCK=NONE;

  • 删除索引（DROP INDEX）：删除索引的操作相对简单，通常可以在很短的时间内完成。

    ALTER TABLE `user` DROP INDEX `idx_name` ALGORITHM=INPLACE, LOCK=NONE;

  • 修改列的默认值（ALTER COLUMN DEFAULT）：修改列的默认值只需要修改表的元数据，不需要修改实际的数据。

    ALTER TABLE `user` ALTER COLUMN `age` SET DEFAULT 18 ALGORITHM=INPLACE, LOCK=NONE;

  • 修改列的注释（ALTER COLUMN COMMENT）：修改列的注释也只需要修改表的元数据，不需要修改实际的数据。

    ALTER TABLE `user` ALTER COLUMN `name` COMMENT '用户姓名' ALGORITHM=INPLACE, LOCK=NONE;

五、 ALGORITHM=INPLACE的限制

以下是一些ALGORITHM=INPLACE不能使用的场景:

  • 修改列的数据类型（ALTER COLUMN DATA TYPE）：如果修改列的数据类型会导致数据长度发生变化，那么就需要重建表。例如，将INT改为VARCHAR，或者将VARCHAR(10)改为VARCHAR(20)。

  • 修改列的排序规则（ALTER COLUMN COLLATE）：修改列的排序规则也需要重建表。

  • 添加/删除主键（ADD/DROP PRIMARY KEY）：添加或删除主键通常需要重建表。

  • 修改表的存储引擎（ALTER TABLE ENGINE）：修改表的存储引擎需要重建表。

  • 修改表的字符集（ALTER TABLE CHARACTER SET）：修改表的字符集需要重建表。

六、 LOCK=NONE和LOCK=SHARED

在执行ALTER TABLE语句时，还可以指定LOCK选项，用于控制锁定级别。

  • LOCK=NONE: 表示允许并发的读写操作。但是，并不是所有的INPLACE操作都支持LOCK=NONE。有些操作可能需要短暂地锁定表，才能保证数据的一致性。

  • LOCK=SHARED: 表示允许并发的读取操作，但是不允许写入操作。

  • LOCK=EXCLUSIVE: 表示完全锁定表，不允许并发的读写操作。

  • LOCK=DEFAULT: 由MySQL自动选择锁定级别。通常情况下，MySQL会尽量选择LOCK=NONE，以减少对线上业务的影响。

一般情况下，我们建议使用LOCK=NONE，除非你知道自己在做什么。

七、 如何判断是否可以使用ALGORITHM=INPLACE

在执行ALTER TABLE语句之前，可以使用SHOW GLOBAL STATUS LIKE 'Innodb_online_alter_log_max_size';命令查看Innodb_online_alter_log_max_size变量的值。这个变量表示在线修改操作可以使用的最大日志空间。如果修改操作需要的日志空间超过了这个值，那么INPLACE算法就会失败，MySQL会自动切换到COPY算法。

此外，还可以使用pt-online-schema-change工具来模拟执行ALTER TABLE语句，并查看执行计划。这个工具可以告诉你是否可以使用INPLACE算法，以及需要锁定的时间。

八、 几个常用的例子

  1. 添加一个普通索引，使用INPLACE算法，允许并发读写：

    ALTER TABLE `user` ADD INDEX `idx_email` (`email`) ALGORITHM=INPLACE, LOCK=NONE;

  2. 修改列的默认值，使用INPLACE算法，允许并发读写：

    ALTER TABLE `product` ALTER COLUMN `price` SET DEFAULT 0.00 ALGORITHM=INPLACE, LOCK=NONE;

  3. 修改列的注释，使用INPLACE算法，允许并发读写：

    ALTER TABLE `order` ALTER COLUMN `order_id` COMMENT '订单ID' ALGORITHM=INPLACE, LOCK=NONE;

  4. 添加一个全文索引（FULLTEXT INDEX），使用INPLACE算法，允许并发读写（MySQL 5.7.6及以上版本支持）：

    ALTER TABLE `article` ADD FULLTEXT INDEX `idx_content` (`content`) ALGORITHM=INPLACE, LOCK=NONE;

  5. 尝试添加一个索引，如果INPLACE算法不可用，则报错：

    ALTER TABLE `user` ADD INDEX `idx_age` (`age`) ALGORITHM=INPLACE, LOCK=NONE;

    如果INPLACE算法不可用，这条语句会报错。

九、 总结与注意事项

  • ALGORITHM=INPLACE是Online DDL的一种重要算法，可以在原表上进行修改，尽量减少对线上业务的影响。
  • 并不是所有的ALTER TABLE操作都支持ALGORITHM=INPLACE。
  • 在执行ALTER TABLE语句之前，一定要仔细评估，确认是否可以使用ALGORITHM=INPLACE。
  • 可以使用pt-online-schema-change工具来模拟执行ALTER TABLE语句，并查看执行计划。
  • 监控Innodb_online_alter_log_max_size变量，确保在线修改操作有足够的日志空间。
  • 在生产环境中执行ALTER TABLE语句时，一定要谨慎，做好备份，并进行充分的测试。

十、一些额外的思考

  • 存储引擎的选择: INPLACE算法在不同的存储引擎上的表现可能有所不同。例如，InnoDB存储引擎对INPLACE算法的支持更好。

  • 版本的影响: 不同版本的MySQL对INPLACE算法的支持程度也不同。建议使用较新的版本，以获得更好的性能和稳定性。

  • 硬件资源: INPLACE算法虽然可以减少锁定的时间，但仍然需要消耗一定的CPU和IO资源。因此，在执行ALTER TABLE语句时，要确保服务器有足够的硬件资源。

十一、进阶：pt-online-schema-change工具

pt-online-schema-change是Percona Toolkit中的一个工具，专门用于在线修改表结构。它比MySQL自带的ALTER TABLE语句更加强大，可以处理更复杂的场景，并提供更多的控制选项。

pt-online-schema-change的原理是：

  1. 创建一个新的表，结构和老表一样。
  2. 在新表上执行修改操作。
  3. 创建一个触发器，将老表上的数据同步到新表。
  4. 逐渐将老表上的数据复制到新表。
  5. 切换新表和老表的名字。
  6. 删除老表。

这个工具可以最大程度地减少对线上业务的影响，但是也需要消耗一定的资源。

十二、最后的叮嘱

各位，今天咱们就聊到这里。关于ALGORITHM=INPLACE，希望大家能记住：谨慎使用，充分测试，做好监控。别一上来就直接在生产环境上搞，出了问题可就麻烦了！

希望今天的讲座对大家有所帮助。谢谢大家！

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注