在实时 myISAM 表上添加索引的最简单方法是什么？
我有一个在 mySQL 上运行的 myISAM 表，通过进行一些测试，我们发现通过添加某个复合索引可以极大地加快查询速度。到目前为止，一切都很好。但是，我并不是真正想要在生产环境中添加此索引而无需长时间锁定表的最佳方法（它有 27GB 的数据，所以不是那么多，但确实需要一段时间）。
你有什么建议吗？如果这是一个更复杂的设置，当然我们会在另一台机器上拥有所有数据的实时副本，并且我们可以安全地进行切换。不幸的是，我们还没有做到这一点，我想尽快加快这个查询的速度（这让客户很头疼）。有没有一些简单的方法来复制数据然后进行交换技巧？我还缺少其他一些技巧吗？
更新：阅读 SQL Server 中的“在线索引操作”让我非常嫉妒 http: //msdn.microsoft.com/en-us/library/ms191261.aspx :)
谢谢！
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?
UPDATE: Reading about "Online Index Operations" in SQL Server makes me very jealous http://msdn.microsoft.com/en-us/library/ms191261.aspx :)
Thanks!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
您可以使用复制来获得大约几分钟的停机时间，而不是在该表上创建索引可能需要的几个小时。
要设置从站，请参阅 http://dev .mysql.com/doc/refman/5.0/en/replication-howto-existingdata.html
我可以提出的有助于加快该过程的建议是在步骤 2 中遵循“使用原始数据文件创建数据快照” “ 方法。但不是通过线路复制到从站，而是复制到主站上的不同位置。并在复制完成并且对配置文件进行了必要的更改（设置服务器 ID 并启用二进制日志记录）后立即恢复主服务器。这会将您的停机时间减少到一两分钟。一旦服务器备份，您可以将复制的文件复制到从机盒中。
一旦您启动并运行了从属设备，并且您已验证一切都正常复制，您可以暂停奴隶。在从站上创建索引。当索引创建完成后，恢复从站。这将使奴隶赶上主人。在主服务器上，使用 FLUSH TABLE AND READ LOCK。检查从站状态以确保主站和从站上的日志位置匹配。如果这样做，请关闭从属服务器并将该表的文件复制回主服务器。
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.
to set up the slave, see http://dev.mysql.com/doc/refman/5.0/en/replication-howto-existingdata.html
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.
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.
我和兰迪在一起。我们也遇到过类似的情况，MySQL 有两种方法可以完成这样的事情：
在服务器运行时关闭服务器。这就是你可能会做的。这很简单，很容易，而且有效。有时间做吗？可能需要半小时/45 分钟，具体取决于磁盘带宽。请参阅下文。
使用新索引创建一个新表，复制所有数据，暂停服务器删除第一个表，将新表更改为旧名称，启动服务器。停机时间？也许需要 10 分钟，但确实很复杂。
选项二有效，可以节省创建索引的停机时间（如果需要很长时间）。但它需要更多的空间，更复杂（因为你必须处理从主表插入的新记录，并且在复制数据时它可能会锁定MyISAM。删除表将需要一些时间，将表更改为新名称确实需要一些时间。如果您有一个 2TB 的表，这可能很有用，但对于 27G 的表来说，这可能有点过分了。
您是否有第二台与您的生产服务器规格接近的服务器？最近的备份并在那里做索引，所以你知道需要多长时间才能添加，然后计划停机时间
在很多方面都更好，但新索引仍然锁定表。 ）必须在不锁定的情况下做这些事情会很棒。
I'm with Randy. We've been in a similar situation, and there are two ways in MySQL to accomplish something like this:
Take down the server while it runs. This is what you'll probably do. It's simple, it's easy, it works. Time to do? Maybe a half hour/45 minutes, dependent on disk bandwidth. See below.
Make a new table with the new index, copy all the data over, pause the server delete the first table, alter the new one to the old name, start the server. Downtime? 10 minutes, maybe, but really complicated.
Option two works, and saves you the downtime of creating the index (if it takes a long time). But it takes more space, it's more complicated (since you have to deal with the new records inserted off the main table, and it will probably lock on MyISAM while copying the data out. Deleting a table will take some time, altering the table to the new name will take some time. It's just really complicated. If you had a 2TB table this might be useful, but for 27G it's probably overkill.
Do you have a second server that is close in specifications to your production server? Load up your most recent backup and do the index there, so you know about how long it will take to add. Then plan for downtime.
InnoDB is better about many things, but new indexes still lock the table. Those abilities that MSSQL (and I think PostgreSQL) have to do those kind of things without locking would be great.
找到您的低使用率窗口，并在索引构建期间使您的应用程序脱机。由于您没有复制或多主机或其他任何东西，因此您只能硬着头皮做这件事。凌晨 1 点见。 :-)
Find your low usage window and take your application offline during the index build. Since you don't have replication or a multimaster or whatever, you're just going to have to bite the bullet on this one. See you at 1am. :-)
在这里，一台服务器无能为力。
如果您复制表并进行试运行，至少您会发现在不锁定活动表的情况下需要多长时间，因此您可以在必要时安排一些维护时间，或者决定是否可以只推送按钮并让用户等待几分钟:)
或者安排一个安静的时间......
Not much you can do with one server here.
If you copy the table and do a dry run, at least you'll find out how long it's going to take without locking the live table, so you can schedule some maintenance time if necessary, or make a decision whether you can just push the button and leave users hanging for a couple of minutes :)
Or schedule it for a quiet time...