导入前后的InnoDB索引

Question

我正在尝试为InnoDB表导入mysqldump生成的大型SQL文件，但是即使在调整my.cnf中的某些参数并禁用了AUTOCOMMIT（以及FOREIGN_KEY_CHECKS和UNIQUE_CHECKS之后，也要花很长时间），但是该表确实没有任何外来或唯一键）。但是我想知道是否由于表中的多个索引花费了这么长时间。

查看SQL文件，似乎在插入所有数据之前在CREATE
TABLE语句中创建了索引。根据我的（有限的）研究和个人经验，我发现插入所有数据后添加索引的速度更快。是否不必检查每个INSERT的索引？我知道mysqldump确实有一个--disable- keys选项，它确实可以在插入之前禁用密钥，但是显然这仅适用于MyISAM表而不适用于InnoDB。

但是，为什么mysqldump不能在InnoDB表的CREATE TABLE语句中不包含键，然后在插入所有数据之后执行ALTER
TABLE？还是InnoDB的工作方式不同，并且没有速度差异？

谢谢！

Answer 1

我在过去的工作中对这个概念进行了一些试验，我们需要一种在MySQL服务器之间复制架构的快速方法。

当您插入具有二级索引的表时，确实存在性能开销。插入需要更新聚簇索引（又名表），也需要更新二级索引。一个表具有的索引越多，它导致插入的开销就越大。

InnoDB具有称为更改缓冲区的功能，该功能可以通过延迟索引更新来有所帮助，但最终必须将它们合并。

插入没有辅助索引的表的速度更快，因此很容易尝试将索引的创建推迟到数据加载后再进行，正如您所描述的那样。

Percona Server是MySQL的一个分支，尝试了一个mysqldump --optimize- keys选项。当您使用此选项时，它会将mysqldump的输出更改为不带索引的CREATE TABLE，然后插入所有数据，然后在数据加载后添加ALTER
TABLE以添加索引。参见https://www.percona.com/doc/percona-
server/LATEST/management/innodb_expanded_fast_index_creation.html

但是以我的经验来看，性能的净改善很小。即使没有索引的表也要花很多时间才能插入很多行。然后，还原需要运行ALTER
TABLE来建立索引。一张大桌子要花一些时间。当您计算INSERT的时间加上建立索引的额外时间时，它比将传统方法插入具有索引的表中仅快百分之几（低个位数）。

创建这种后处理索引的另一个好处是索引的存储更加紧凑，因此，如果需要节省磁盘空间，这是使用此技术的更好理由。

我发现通过 并行加载多个表 对性能进行恢复更有利。

• 新的MySQL 8.0工具mysqlpump支持多线程转储。
• 开源工具mydumper支持多线程转储，并且还具有一个名为的多线程还原工具myloader。mydumper / myloader的最糟糕的缺点是该文档实际上不存在，因此您必须是一个勇敢的超级用户才能弄清楚如何运行它。

另一种策略是使用mysqldump --tab转储CSV文件而不是SQL脚本。批量加载CSV文件比执行SQL脚本还原数据要快得多。好吧，它转储用于表定义的SQL文件和用于导入数据的CSV文件。它为每个表创建单独的文件。您必须通过加载所有SQL文件（这是快速的）来手动重新创建表，然后使用mysqlimport加载CSV数据文件。mysqlimport工具甚至还有一个--use- threads用于并行执行的选项。

使用不同数量的并行线程仔细测试。我的经验是4个线程是最好的。随着更大的并行度，InnoDB成为瓶颈。但是，您的体验可能会有所不同，具体取决于MySQL的版本和服务器硬件的性能。

最快的还原方法是使用物理备份工具时，最流行的还原方法是Percona
XtraBackup。这样可以实现快速备份甚至更快的还原。实际上，已备份的文件已准备就绪，可以复制到位并用作活动表空间文件。缺点是您必须关闭MySQL服务器才能执行还原。