MySQL中如何优雅地转换表的存储引擎
2018-01-02| 程成| 1061| 0| MySQL

Mysql中如何做到转换表的存储引擎,同时做到数据不丢失和快速高效。


ALTER TABLE

将表从一个引擎修改为另一个引擎最简单地方法是使用 ALTER TABLE 语句。下面是将 table 的引擎修改为 InnoDB:


            ALTER TABLE table ENGINE = InnoDB;


上述语法可以适用任何存储引擎。但有一个问题:需要执行很长的时间。Mysql会按行将数据从原表复制到一张新的表中,在复制期间可能会消耗系统所有的 I/O 能力,同时原表上会加上读锁。所以,在繁忙的表上执行操作要特别小心。一个替代方案是采用接下来将讨论的导出与导入的方法,手工进行的复制。


如果转换表的存储引擎,将会失去和原引擎相关的所有特性。例如,如果将一张 InnoDB 表转换为 MyISAM,然后再转换回 InnoDB,原 InnoDB 表上所有的外键将丢失。



导出与导入

为了更好地控制转换的过程,可以使用 mysqldump 工具将数据导出到文件,然后修改文件中 CREATE TABLE 语句的存储引擎选项,注意同时修改表名,因为同一个数据库中不能存在相同的表名,即使他们使用的是不同的存储引擎。同时要注意  mysqldump 默认会自动在 CREATE TABLE 语句前加上 DROP TABLE 语句,不注意这一点可能会导致数据丢失。



创建与查询(CREATE 和 SELECT)

第三种转换的技术综合了第一种方法的高效和第二种方法的安全。不需要导出整个表的数据,而是先创建一个新的存储引擎的表,然后利用 INSERT...SELECT 语法来导出数据:


CREATE TABLE innodb_table LIKE myisam_table;
ALTER TABLE innodb_table ENGINE=InnoDB;
INSERT INTO innodb_table SELECT * FROM myisam_table;


数据量不大的话,这样做工作的很好。如果数据量很大,则可以考虑做分批处理,针对每一段数据执行事务提交操作,以避免大事务产生过多的undo。假设有主键字段id,重复运动以下语句(最小值x和最大值y进行相应的替换)将数据导入到新表:


START TRANSACTION;
INSERT INTO innodb_table SELECT * FROM myisam_table WHERE id BETWEEN x AND y;
COMMIT;


这样操作完成以后,新表是原表的一个全量复制,原表还在,如果需要可以删除原表。如果有必要,可以在执行的过程中对原表加锁,以确保新表和原表的数据一致。


Percona Tookit 提供了一个 pt-online-schema-change 的工具(基于 Facebook 的在线 schema 变更技术),可以比较简单、方便的执行上述过程,避免手工操作可能导致的失误和繁琐。





×
作者:程成
QQ:492245711