将大型的MyISAM表转换为InnoDB

Question

我有一个MyISAM表（10M行，3.5G，计划达到~80M），我总是无法将其转换为InnoDB。

我尝试过：

• ALTER TABLE - 2分钟后就失去连接了。也许我做错了什么。

• mysqldump - 尝试创建转储，然后将ENGINE=MyISAM更改为ENGINE=InnoDB。

它开始得很好，但随着新表中的行数增加（~3M），它变得越来越慢，最终在几个小时后超时（--reconnect已开启）。

如果我将缓冲池大小增加到2G，它会在更多的行（~6M）后变慢，但机器会耗尽内存。

在恢复转储期间，在SHOW PROCESSLIST中，我看到许多查询在“查询结束”状态下卡住了2-3分钟。从谷歌搜索中无法理解这是什么意思。

• INSERT INTO ... SELECT * FROM - 创建相同结构的表并尝试此操作。在一些百万行之后也会变慢，然后超时。（感谢@Ernestas Stankevičius提醒我这一点。）

服务器：

Aws EC2 4GB Ubuntu14.04

my.cnf：

wait_timeout=28800
connect_timeout=28800
innodb_lock_wait_timeout=28800
net_read_timeout=7200
net_write_timeout=7200
innodb_buffer_pool_size=1G
innodb_io_capacity=100 /*200 is heavy on the machine*/
innodb_flush_log_at_trx_commit=0
reconnect=1

- Mor Cohen

---

我建议您创建一个具有相同结构但使用InnoDB的新表，然后从MyISAM表中插入数据，之后删除旧表并重命名新表。您在插入大量记录时可能会遇到问题，因此请尝试每次插入1000条记录。同时不要忘记索引，因为稍后添加它们也很麻烦。 - Ernestas Stankevičius

@ErnestasStankevičius 哦，谢谢你提醒我！这是我尝试的第一件事，但在处理数百万行后速度也变慢了。 - Mor Cohen

@MorCohen "在几百万行之后变慢" - 这听起来像是你有很多索引。如果是这样，它们会减缓大量插入的速度。尝试创建一个没有索引的新InnoDB表，并在数据加载后再创建它们。 - i486

你可以尝试使用Facebook的MySQL在线模式更改脚本：https://www.facebook.com/notes/mysql-at-facebook/online-schema-change-for-mysql/430801045932/ - ajtrichards

@i486 谢谢。我一开始有些担心尝试这个操作，因为重新添加需要很长时间，但是看起来效果不错！ - Mor Cohen

Answer 1

innodb_buffer_pool_size=2G 对于4GB的机器来说可能过高了，尝试使用1500M。交换或内存不足比拥有小缓存更糟糕。

请在mysql命令行工具中运行ALTER，而不是某些UI。（该UI可能有您正在遇到的时间限制）。

您正在运行哪个版本的MySQL？ 您有多少索引？ 请向我们展示SHOW CREATE TABLE。 删除所有次要键，仅保留PRIMARY KEY。 在转换后添加其他索引； 5.6或更高版本可以“原地”执行此操作。

如果没有PRIMARY KEY，请创建一个； InnoDB确实需要一个。

这可能涵盖了大多数情况：

CREATE TABLE new LIKE real;
ALTER TABLE new ENGINE=InnoDB,
    DROP ..., -- all the secondary keys
    ADD PRIMARY (...), -- if needed
    ENGINE=InnoDB;
INSERT INTO new (...)
    SELECT ... FROM real ORDER BY ... -- insert in PK order
ALTER TABLE new
    ADD index ...;  -- rebuild secondary key(s) (see note)
RENAME TABLE real TO old, new TO real;
DROP TABLE old;

注意：如果您正在运行5.5或更早版本，请在单个ALTER中添加所有辅助键。如果您正在运行5.6或更新版本，请逐个添加。

- Rick James

Answer 2

我的解决方案是从新的（InnoDB）表结构中删除一些索引，然后再添加数据。
我使用了INSERT new_table SELECT * FROM old_table来复制数据。

删除的索引越多，数据进入的速度就越快。

之后，我重新创建了索引。
感谢@i486。

- Mor Cohen