史上最LOW的在线DDL解决方案

赞助

如果你觉得我写得还行,欢迎付费支持。

说起在线 DDL,最常见的操作莫过于在线加一个字段或者索引,不过如果数据量比较大的话,伴随而来的往往是长时间的等待,更要命的是系统在操作期间很可能会出现不可用的情况,所以一般只能等到凌晨操作,简直就是梦魇一般的存在。

在 PostgreSQL 中,如果注意使用方法,那么在线 DDL 并不是一个太难的事情。这里面说注意使用方法,指的是 PostgreSQL 跟其它一些数据库一样,在加字段或者索引的时候会锁住表,不过有一些技巧可以绕开此限制:

  • 加字段:使用 ALTER TABLE ADD COLUMN,注意允许 NULL,并且没有缺省值。
  • 加索引:使用 CREATE INDEX CONCURRENTLY,注意 CONCURRENTLY。

说完了 PostgreSQL 再看看 MySQL,从 5.5 开始,MySQL 就具备了 Fast Index Creation 的能力,并且从 5.6 开始,InnoDB 具备了相对比较完善的在线 DDL 能力:

InnoDB DDL

InnoDB DDL

如上表所示,新版 MySQL 的在线 DDL 能力确实有提升,比如加索引等操作不用锁表,但是加字段等操作还是需要拷表,如果数据量比较大的话,巨大的 IO 压力无疑还是会影响系统可用性,更要命的是一旦有主从,当加字段的操作在从服务器上重放的时候,因为基本上是单线程重放,所以会导致非常严重的延迟。

开源社区也有一些处理在线 DDL 的工具,比如 pt-online-schema-change 和 gh-ost。其中 pt-online-schema-change 是以触发器为基础来构建的:数据通过可控的增量方式拷贝到临时表中,操作过程中原始表里新的数据修改通过触发器同步到临时表中,最终用临时表替换原始表。至于 gh-ost,则在前人的基础上做出了改良,去掉了触发器,使用异步分析日志的无触发器设计。不过不管你使用哪个方案,都挺复杂的!

唧唧歪歪扯了着么多,终于要开始说史上最 LOW 的在线 DDL 解决方案了。其实它的原理非常简单,说白了就是利用 MySQL 的 JSON虚拟列来实现:通过把数据都存到一个特定的 JSON 字段里去,从而让 MySQL 变身为 MongoDB 那样的 schemaless 数据库,加减字段之类的操作都不在是问题,不过毕竟我们说的是 MySQL,不是 MongoDB,所以我们还需要借助虚拟列把 JSON 中的数据展现出来,此时虚拟列就好像是 JSON 中数据的快捷方式一样。如此一来,既保证了 schemaless,又保证了查询数据的方式还和以前一摸一样,从而相当于在无痛状况下绕开了在线 DDL 问题。

假设我有一个 users 表,它开始只有两个字段,一个是主键,另一个用来保存 JSON 数据;然后保存了很多数据;接着通过虚拟列我们把 JSON 中的数据(name,address)以独立字段的方式展示出来;下面需要加字段(level);把新加入 JSON 的字段同样通过虚拟列展示出来;最后更新旧数据,填充新字段的内容:

mysql> CREATE TABLE users (
           id INT UNSIGNED NOT NULL AUTO_INCREMENT,
           data JSON NOT NULL,
           PRIMARY KEY(id)
       );

mysql> INSERT INTO users (data)
       VALUES ('{"name":"老王","address":"东北"}');

mysql> ALTER TABLE users add name VARCHAR(100) AS 
       (JSON_UNQUOTE(JSON_EXTRACT(data, '$.name'))) AFTER id;

mysql> ALTER TABLE users add address VARCHAR(100) AS 
       (JSON_UNQUOTE(JSON_EXTRACT(data, '$.address'))) AFTER name;

mysql> INSERT INTO users (data)
       VALUES ('{"name":"小黄","address":"四川","level":10}');

mysql> ALTER TABLE users add level INT UNSIGNED AS 
       (JSON_EXTRACT(data, '$.level')) AFTER name;

mysql> UPDATE users SET data = JSON_SET(data, '$.level', 0)
       WHERE id = 1;

最终 MySQL 数据库里数据的情况如下图所示:

USERS

USERS

因为虚拟列本身是虚拟的,所以并没有物化,进而保证了添加删除虚拟列的时候无需重建表,只有在虚拟列上构建索引的时候才会物化虚拟列的数据,不过你不需要手动维护虚拟列索引上的值,并且在虚拟列上创建索引的过程中,并不会锁住表。

最终在使用时,读操作基本都是在虚拟列上完成的,和以前的使用习惯别无二致;写操作则需要在 JSON 字段上完成,但是借助框架的帮助,我们也可以让写操作对 JSON 实现透明,比如 Laravel 的 ORM 中,针对每个字段的读写都有专门的 getter/setter 钩子可供使用,再覆盖一下 ORM 本身的 save 实现,就能做到写操作的完全透明。不过这个实现就是另外一个话题了,本文不再展开讨论,有兴趣的读者自己琢磨吧。

更新:我在 Laravel 中实现了相应的功能,有兴趣的可以参阅。

写完了回头看看,此方法确实很 LOW,既没有用到触发器,也没有用到分析日志,只是简单组合使用 JSON 和虚拟列而已,不过能解决问题就是好方法。

史上最LOW的在线DDL解决方案》上有4条评论

  1. Pingback引用通告: Laravel专供:实现Schemaless | 火丁笔记

  2. Pingback引用通告: Laravel专供:实现Schemaless - 莹莹之色

发表评论

电子邮件地址不会被公开。 必填项已用*标注