MySQL 修复与 Keycache 性能
我有一个包含 125M 记录的 MyISAM 表。我通过以下方式向其中添加了 25M 条记录： 更改表 x 禁用键； 插入 x 从 y 中选择 *； 更改表 x 启用键；
当前 ALTER TABLE x ENABLE KEYS 处于“Repair with Keycache”状态。此修复操作有多快？如果我没有禁用索引并让行添加动态更新的索引，那么它是否至少与情况一样快，还是更慢？
如果我现在终止查询，删除所有索引，然后再次重新创建它们以强制按排序修复（我的缓冲区大小足够大），我是否会冒丢失任何数据的风险？
I have a MyISAM table with 125M records. I added 25M more records to it via:
ALTER TABLE x DISABLE KEYS;
INSERT INTO x SELECT * FROM y;
ALTER TABLE x ENABLE KEYS;
Currently ALTER TABLE x ENABLE KEYS is in the "Repair with Keycache" state. How fast is this repair operation? Is it at least as fast as the case if I didn't disable the index and let rows be added with indexes updated on the fly or is it slower?
If I kill the query now, DROP all the indexes and then re-create them again to force repair by sort (my buffer sizes are large enough) would I risk losing any data?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
如果在 ALTER TABLE 操作正在进行时终止查询，则可能会丢失在 ALTER TABLE 操作开始后添加到表中的所有数据。此外，删除并重新创建表上的索引还会导致您丢失删除索引后添加到表中的所有数据。
一般来说，最好避免在 ALTER TABLE 操作进行时对表进行任何更改。如果您担心操作的速度，最好让它完成而不是试图中断它。
就操作速度而言，在向表中插入新行之前是否禁用键不会影响 ALTER TABLE 操作的速度。无论键是否被禁用，ALTER TABLE 操作将以大致相同的速度运行。操作的速度取决于几个因素，包括表的大小和底层硬件的速度。
如果要强制 ALTER TABLE 操作使用“按排序修复”方法，可以通过在运行 ALTER TABLE 操作之前将 myisam_repair_threads 系统变量设置为大于 1 的值来实现。这将导致操作使用“按排序修复”方法，在某些情况下可能会更快。但是，请记住，这也会使用更多的系统资源，因此它可能并不适合所有情况。
在对表进行任何更改之前备份数据始终是一个好主意，以防出现问题。这样，您可以根据需要从备份中恢复数据。
If you kill the query while the ALTER TABLE operation is in progress, you risk losing any data that was added to the table after the ALTER TABLE operation began. Additionally, dropping and re-creating the indexes on the table would also cause you to lose any data that was added to the table after the indexes were dropped.
In general, it is a good idea to avoid making any changes to the table while the ALTER TABLE operation is in progress. If you are concerned about the speed of the operation, it may be better to let it complete rather than trying to interrupt it.
In terms of the speed of the operation, whether or not you disable the keys before inserting new rows into the table will not affect the speed of the ALTER TABLE operation. The ALTER TABLE operation will run at roughly the same speed regardless of whether the keys were disabled or not. The speed of the operation will depend on several factors, including the size of the table and the speed of the underlying hardware.
If you want to force the ALTER TABLE operation to use the "Repair by sort" method, you can do so by setting the myisam_repair_threads system variable to a value greater than 1 before running the ALTER TABLE operation. This will cause the operation to use the "Repair by sort" method, which may be faster in some cases. However, keep in mind that this will also use more system resources, so it may not be appropriate for all situations.
It is always a good idea to back up your data before making any changes to a table, in case something goes wrong. This way, you can restore the data from the backup if needed.