本文基于MySQL 8.0测试常用的DDL工具,通过多个维度进行对比分析。基本结论是MySQL原生Online DDL在性能、存储开销和易用性等方面有较大优势,目前在加列等场景可取代pt-osc/gh-ost,在创建索引等场景还需要做进一步优化。
DDL工具实现分析
在MySQL使用过程中,根据业务的需求对表结构进行变更是个普遍的运维操作,这些称为DDL操作。常见的DDL操作有在表上增加新列,或给某个列添加索引。
目前可用的DDL操作工具包括pt-osc,github的gh-ost,以及MySQL提供的在线修改表结构命令Online DDL。pt-osc和gh-ost均采用拷表方式实现,即创建个空的新表,通过select+insert将旧表中的记录逐次读取并插入到新表中,不同之处在于处理DDL期间业务对表的DML操作(增删改)。
pt-osc
pt-osc是Percona运维工具包中提供的表结构变更工具,全称pt-online-schema-change。执行流程图如下:
从中可知其采用触发器来应用DDL执行期间对表所做的DML操作,每种DML操作均对应一个触发器,如下所示:
CREATE TRIGGER `pt_osc_sbtest_sbtest2_del` AFTER DELETE ON `sbtest`.`sbtest2` FOR EACH ROW DELETE IGNORE FROM `sbtest`.`_sbtest2_new` WHERE `sbtest`.`_sbtest2_new`.`id` <=> OLD.`id`
CREATE TRIGGER `pt_osc_sbtest_sbtest2_upd` AFTER UPDATE ON `sbtest`.`sbtest2` FOR EACH ROW REPLACE INTO `sbtest`.`_sbtest2_new` (`id`, `k`, `c`, `pad`, `ptosc`) VALUES (NEW.`id`, NEW.`k`, NEW.`c`, NEW.`pad`, NEW.`ptosc`)
CREATE TRIGGER `pt_osc_sbtest_sbtest2_ins` AFTER INSERT ON `sbtest`.`sbtest2` FOR EACH ROW REPLACE INTO `sbtest`.`_sbtest2_new` (`id`, `k`, `c`, `pad`, `ptosc`) VALUES (NEW.`id`, NEW.`k`, NEW.`c`, NEW.`pad`, NEW.`ptosc`)
根据定义可知,delete变为delete ignore,update和insert均转换为replace into。
pt-osc同时处理全量和增量数据,即一边拷表一边回放增量DML,其所用拷表语句如下:
INSERT LOW_PRIORITY IGNORE INTO `sbtest`.`_sbtest2_new` (`id`, `k`, `c`, `pad`, `ptosc`) SELECT `id`, `k`, `c`, `pad`, `ptosc` FROM `sbtest`.`sbtest2` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?)) AND ((`id` <= ?)) LOCK IN SHARE MODE /*pt-online-schema-change 115039 copy nibble*/
SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `sbtest`.`sbtest2` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?)) ORDER BY `id` LIMIT ?, 2 /*next chunk boundary*/
由于采用触发器方式&