MySQL 什么时候应该重建数据库索引？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/321461/
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
When should database indexes be rebuilt?
提问by Karl
I was reading about refactoring a large slow SQL Query over here, and the current highest response is from Mitch Wheat, who wants to make sure the query uses indexes for the major selects, and mentions:
我在读有关通过重构大慢SQL查询在这里，而目前的最高响应是米奇小麦，谁希望确保为主要选择的查询使用索引，并提到：
First thing I would do is check to make sure there is an active index maintenance job being run periodically. If not, get all existing indexs rebuilt or if not possible at least get statistics updated.
我要做的第一件事是检查以确保定期运行活动索引维护作业。如果没有，请重建所有现有索引，或者如果不可能，至少更新统计信息。
I'm only am amateur DBA, and I've made a few programs freelance that are basically Java desktop clients and occasionally a MySQL backend. When I set up the system, I know to create an index on the columns that will be queried by, there's a varchar CaseID and a varchar CustName.
我只是一个业余 DBA，我做了一些自由职业者的程序，这些程序基本上是 Java 桌面客户端，偶尔是 MySQL 后端。当我设置系统时，我知道要在将被查询的列上创建一个索引，有一个 varchar CaseID 和一个 varchar CustName。
However, I set this system up months ago and left the client operating it, and I believe the indexes should grow as data is entered and I believe everything is still working nicely. I'm worried though that the indexes should be rebuilt periodically, because today i have read that there should be an 'active maintenance job'. The only maintenance job I set on the thing was a nightly backup.
然而，我在几个月前设置了这个系统并让客户运行它，我相信索引应该随着数据的输入而增长，我相信一切仍然运行良好。我担心应该定期重建索引，因为今天我读到应该有一个“积极的维护工作”。我在这件事上设置的唯一维护工作是每晚备份。
I wanted to ask the community about regular maintenance that a database might require. Is it neccessary to rebuild indexes? Can I trust the MySQL backend to keep going so long as no one messes with it and the data stays under a few gigabytes?
我想向社区询问数据库可能需要的定期维护。是否有必要重建索引？只要没有人弄乱它并且数据保持在几 GB 以下，我是否可以相信 MySQL 后端会继续运行？
采纳答案by nathan
There is no need to 'rebuild' an index. They are always kept up-to-date. Maybe he was referring to rebuilding the table. Depending on your usage patterns and schema, you can get fragmented pages in InnoDB, and I think in MyISAM also. Rebuilding the table can improve performance by getting rid of fragmentation of your data on disk. I don't use MyISAM tables regularly, but I believe it is recommended to run 'OPTIMIZE TABLE' with certain usage patterns. See the MySQL docs on OPTIMIZE TABLEfor some good info on both MyISAM and InnoDB.
无需“重建”索引。它们始终保持最新状态。也许他指的是重建桌子。根据您的使用模式和架构，您可以在 InnoDB 中获取碎片化页面，我认为在 MyISAM 中也是如此。重建表可以通过消除磁盘上数据的碎片来提高性能。我不经常使用 MyISAM 表，但我认为建议使用某些使用模式运行“优化表”。有关MyISAM 和 InnoDB 的一些好信息，请参阅OPTIMIZE TABLE上的 MySQL 文档。
I'm not as familiar with MyISAM intricacies, but with InnoDB it is true that statistics can get out of date. The database keeps estimated statistics on how your data is distributed for a given index, and it's possible for those to get out-of-date, but MySQL/InnoDB has some built in functionality to try to keep statistics current. You usually don't have to worry about it.
我不太熟悉 MyISAM 的复杂性，但是对于 InnoDB，统计数据确实会过时。数据库保留关于给定索引的数据分布方式的估计统计信息，并且这些信息可能会过时，但 MySQL/InnoDB 具有一些内置功能来尝试使统计信息保持最新。您通常不必担心。
So if you are using InnoDB, the answer is no, you typically don't need to actively do anything to keep your indexes performing well. I'm not as sure with MyISAM, I think it's more common to need to optimize those tables regularly.
因此，如果您正在使用 InnoDB，答案是否定的，您通常不需要主动做任何事情来保持索引性能良好。我对 MyISAM 不太确定，我认为需要定期优化这些表更为常见。
回答by Eli
It's usually a good idea to set up a cronjob to optimize indexes and check for errors.
设置一个 cronjob 来优化索引和检查错误通常是一个好主意。
See mysqlcheck. A typical cron job looks something like mysqlcheck -Aaos, which checks all tables in all databases for errors, optimizes indexes, and only outputs on error.
请参阅mysqlcheck。一个典型的 cron 作业看起来像mysqlcheck -Aaos，它检查所有数据库中的所有表是否有错误，优化索引，并且仅在出现错误时输出。
回答by staticsan
The answer you linked to about "regular maintenance" was in the specific context of a temporary table that gets truncated and repopulated regularly. You don't need to do this to the vast majority of MySQL database installs.
您链接到的有关“定期维护”的答案是在临时表的特定上下文中，该表被定期截断和重新填充。您不需要对绝大多数 MySQL 数据库安装执行此操作。
