应该何时重建数据库索引？ [英] When should database indexes be rebuilt?

问题描述

我正在阅读有关通过此处重构大型慢速SQL查询的问题，以及当前最高版本响应来自Mitch Wheat，他希望确保查询使用主要选择的索引，并提及：

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:

我会做的第一件事检查以确保定期运行活动索引维护作业。如果没有，请重建所有现有索引，或者如果不可能，至少更新统计信息。

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.

我只是业余DBA，并且我做了一些程序自由职业者，基本上是Java桌面客户端，有时候是MySQL后端。当我设置系统时，我知道要在将要查询的列上创建索引，有一个varchar CaseID和一个varchar CustName。

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.

但是，我设置了几个月前这个系统让客户端运行它，我相信索引应该随着数据的输入而增长，我相信一切都还能正常运行。我担心这些索引应该定期重建，因为今天我已经读到应该有一个主动维护工作。我设置的唯一维护工作是夜间备份。

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.

我想向社区询问数据库可能需要的定期维护。是否需要重建索引？我可以信任MySQL后端，只要没有人混淆它并且数据保持在几千兆字节之外吗？

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?

推荐答案

没有必要'重建'索引。它们始终保持最新状态。也许他指的是重建桌子。根据您的使用模式和架构，您可以在InnoDB中获取碎片页面，我想在MyISAM中也是如此。重建表可以通过消除磁盘上的数据碎片来提高性能。我不定期使用MyISAM表，但我相信建议使用某些使用模式运行'OPTIMIZE TABLE'。有关一些好消息，请参阅 OPTIMIZE TABLE 上的MySQL文档在MyISAM和InnoDB上。

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 TABLE for some good info on both MyISAM and InnoDB.

我对MyISAM的复杂性并不熟悉，但对于InnoDB，统计数据可能已经过时了。数据库保存有关如何为给定索引分配数据的估计统计数据，并且这些数据可能会过时，但MySQL / InnoDB具有一些内置功能，可以使统计数据保持最新状态。你通常不用担心它。

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.

所以如果你使用InnoDB，答案是否定的，你通常不需要积极做任何事情来保持你的索引表现良好。我对MyISAM不太确定，我认为需要定期优化这些表格更为常见。

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.

这篇关于应该何时重建数据库索引？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！