在现场myISAM表上添加索引的最简单方法是什么？ [英] What's the easiest way to add an index on a live myISAM table?

问题描述

我在mySQL上运行myISAM表，通过一些测试，我们发现通过添加某个复合索引可以极大地加快查询速度。到现在为止还挺好。但是，我并不是真正想要在生产环境中添加此索引而不长时间锁定表的最佳方法（它有27GB的数据，所以不是很多，但确实需要一段时间）。

I have a myISAM table running in production on mySQL, and by doing a few tests, we've found we can tremendously speed up a query by adding a certain compound index. So far so good. However, I am not really about the best way to add this index in a production environment without locking the table for a long time (it's got 27GBs of data, so not so much, but it does take a while).

您有任何提示吗？如果这是一个更复杂的设置，我们当然会有另一台机器上所有数据的实时复制品，我们可以安全地切换。不幸的是，我们还没有，我想尽快加快这个问题（这会引起很大的客户头痛）。是否有一些简单的方法来复制数据然后做出换出技巧？我缺少一些其他技巧？

Do you have any tips? If this was a more sophisticated setup of course we'd have a live replica of all of the data on another machine, and we could safely switch. Unfortunately, we're not there yet, and I would like to speed up this query as soon as possible (it's causing big customer headaches). Is there some simple way to replicate the data and then do a swap-out trick? Some other tricks that I am missing?

更新：在SQL Server中阅读在线索引操作让我非常嫉妒 http://msdn.microsoft.com/en-us/library/ms191261.aspx ：）

UPDATE: Reading about "Online Index Operations" in SQL Server makes me very jealous http://msdn.microsoft.com/en-us/library/ms191261.aspx :)

谢谢！

推荐答案

您可以使用复制来获得停机时间几分钟，而不是在该表上创建索引所需的时间。

you can use replication to get downtime on the order of a couple minutes, instead of the hours it might take to create an index on that table.

设置奴隶，参见 http://dev.mysql.com/doc/refman/5.0/en/replication-howto- existingdata.html

我可以根据使用原始数据文件创建数据快照方法在第2步中提出的建议来帮助加快流程。但不是将电线复制到从属设备，而是复制到主设备上的其他位置。在副本完成后立即重新启动主服务器并对配置文件进行必要的更改（设置server-id并启用二进制日志记录）。这样可以将停机时间缩短到一两分钟。一旦服务器备份，您可以将复制的文件复制到从属框。

a recommendation i can make to help speed up the process is in step 2 follow the "Creating a Data Snapshot Using Raw Data Files" method. but instead of copying over the wire to the slave, copy to a different location on the master. and bring the master back up as soon at the copy is done and you've made the necessary changes to the config file (set server-id and enabled binary logging). this will minimize your downtime to just a minute or two. once the server is back up, you can copy the copied files to the slave box.

一旦你启动并运行了一个奴隶，你有验证一切正常复制，你可以暂停奴隶。在药膏上创建索引。索引创建完成后，恢复从站。这将抓住奴隶到主人。在主服务器上，使用FLUSH TABLE WITH READ LOCK。检查从站状态以确保主站和从站上的日志位置匹配。如果他们这样做，关闭奴隶并将该表的文件复制回主人。

once you have the slave up and running and you have verified everything is replicating properly, you can pause the slave. create the index on the salve. when the index creation is complete, resume the slave. this will catch the slave up to the master. on the master, use FLUSH TABLE WITH READ LOCK. check the slave status to make sure the log position on the master and the slave match. if they do, shut down the slave and copy the files for that table back to the master.

这篇关于在现场myISAM表上添加索引的最简单方法是什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！