sql - 加载数据文件 + 禁用/启用按键性能

Question

我有一个包含大约 700 万行的表。每天一次，我需要向该表中批量导入大约 200,000 行新行。为此，我首先使用 LOAD DATA INFILE 禁用表上的键，然后重新启用表上的键。

我遇到的问题是 ALTER TABLE my_table ENABLE KEYS 语句。
大约需要 15 分钟才能完成。我试图通过增加 myisam_sort_buffer_size 来提高性能，但它似乎没有帮助。还有其他想法吗？

Answer 1

您可以尝试外部 MySQL 工具，例如 mysqladmin 和 myisamchk。对于常规安装，它们位于/usr/local/mysql/bin 路径中。

来自 MySQL 网站的解决方案路径:

• Execute a FLUSH TABLES statement or a mysqladmin flush-tables command.

• Use myisamchk --keys-used=0 -rq /path/to/db/tbl_name. This removes all use of indexes for the table.

• Insert data into the table with LOAD DATA INFILE. This does not update any indexes and therefore is very fast.

• If you intend only to read from the table in the future, use myisampack to compress it. See Section 13.4.3.3, “Compressed Table Characteristics”.

• Re-create the indexes with myisamchk -rq /path/to/db/tbl_name. This creates the index tree in memory before writing it to disk, which is much faster that updating the index during LOAD DATA INFILE because it avoids lots of disk seeks. The resulting index tree is also perfectly balanced.

• Execute a FLUSH TABLES statement or a mysqladmin flush-tables command.

mysql official documents