MySQL 通过排序修复？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4999298/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
repair by sorting?
提问by user_78361084
I have a script that updates localhost, then dumps the updated results to remotehost:
我有一个更新本地主机的脚本，然后将更新的结果转储到远程主机：
mysqldump --skip-tz-utc --no-create-info --insert-ignore --host=localhost -u u -ppass db table --where=\"timeStamp > FROM_UNIXTIME( $time )\" | mysql -h remote -u u -ppass db
With 20 records, the update to localhost is very quick (a few seconds) but the dump to remotehost is taking over 4 minutes...when I look at mysql workbench, it says the state of remote host is "Repair by Sorting" and the Info column is "/*!40000 ALTER TABLE 'table' ENABLE KEYS */".
有 20 条记录，对 localhost 的更新非常快（几秒钟），但转储到远程主机需要 4 多分钟......当我查看 mysql 工作台时，它说远程主机的状态是“按排序修复”和信息列是“/*!40000 ALTER TABLE 'table' ENABLE KEYS */”。
What does this message mean (and why is it taking so long to dump to remotehost with so few records)?
这条消息是什么意思（以及为什么转储到具有如此少记录的远程主机需要这么长时间）？
thx
谢谢
回答by Jeff Ferland
mysqldumpis disabling indexes, inserting the records, and re-enabling the indexes. This means it affects the entire table, including the many more records I expect are there based on the time.
mysqldump正在禁用索引、插入记录并重新启用索引。这意味着它会影响整个表，包括我期望根据时间存在的更多记录。
Add --skip-disable-keysto the arguments for mysqldumpand that should stop happening.
添加--skip-disable-keys到 和 的论点mysqldump应该停止发生。
回答by RolandoMySQLDBA
mysqldump will do three things to a table during the reload
mysqldump 将在重新加载期间对表做三件事
DISABLE KEYS;
禁用键；
Multiple Inserts
多次插入
ENABLE KEYS;
启用密钥；
When you disable keys, it actually disables non-unique indexes.
当您禁用键时，它实际上禁用了非唯一索引。
Primary Keys and Unique Keys are Loaded Immediately.
立即加载主键和唯一键。
Once ENABLE KEYS is started, all non-unique indexes are then build using 'Repair By Sorting';
启动 ENABLE KEYS 后，所有非唯一索引都将使用“按排序修复”构建；
If you bypass DISABLE KEYS and ENABLE KEYS, you will make things much worse, because the Primary Keys, Unique Keys, and Non-Unique Keys are are built row by row. That's internally a messier operation for MySQL to have to perform FOR A FULL LOADING OF A TABLE !!!
如果你绕过DISABLE KEYS 和ENABLE KEYS，你会让事情变得更糟，因为主键、唯一键和非唯一键是逐行构建的。对于 MySQL 而言，这在内部是一个更混乱的操作，必须执行 FOR A FULL LOADING OF A TABLE ！！！
Since so little data is being added, doing --skip-disable-keys as 'Autocracy' suggested is both wise and concise.
由于添加的数据很少，因此按照“专制”的建议执行 --skip-disable-keys 既明智又简洁。
BTW this only applies to MyISAM tables. InnoDB ignores DISABLE KEYS and ENABLE KEYS.
顺便说一句，这仅适用于 MyISAM 表。InnoDB 忽略 DISABLE KEYS 和 ENABLE KEYS。
