MySQL索引 - 有多少足够？ [英] MySQL indexes - how many are enough?

问题描述

我正在尝试微调我的MySQL服务器，以便检查我的设置，分析慢查询日志，并尽可能简化我的查询。

I'm trying to fine-tune my MySQL server so I check my settings, analyzing slow-query log, and simplify my queries if possible.

有时它如果我正确编制索引就足够了，有时候不是。我已经在某处阅读了（请纠正我，如果这是愚蠢的），比我需要的更多索引产生相同的效果，就像我没有任何索引一样。

Sometimes it is enough if I am indexing correctly, sometimes not. I've read somewhere (please correct me if this is stupidity) that more indexes than I need make the same effect, like if I don't have any of indexes.

多少个索引就足够了？你可以说它取决于数百个因素，但我很好奇如何清理我的 mysql-slow.log 足以减少服务器负载。

How many indexes are enough? You can say it depends on hundreds of factors, but I'm curious about how can I clean up my mysql-slow.log enough to reduce server load.

此外，我看到了一些像这样有趣的日志条目：

Furthermore, I saw some "interesting" log entries like this:

# Query_time: 0  Lock_time: 0  Rows_sent: 22  Rows_examined: 44
SELECT * FROM `categories` ORDER BY `orderid` ASC;

有问题的表包含22行，索引设置为 orderid 。为什么这个查询毕竟出现在日志中？为什么检查44行，如果它只包含22？

The table in question contains exactly 22 rows, index set in orderid. Why is this query showing up in the log after all? Why examine 44 rows if it only contains 22?

推荐答案

索引量和做太多的行将取决于a很多因素。在像您的类别表这样的小表上，您通常不需要或不需要索引，这实际上会损害性能。原因在于它需要I / O（即时间）来读取索引，然后需要更多I / O和时间来检索与匹配行相关联的记录。例外情况是，您只查询索引中包含的列。

The amount of indexing and the line of doing too much will depend on a lot of factors. On small tables like your "categories" table you usually don't want or need an index and it can actually hurt performance. The reason being is that it takes I/O (i.e. time) to read an index and then more I/O and time to retrieve the records associated with the matched rows. An exception being when you only query the columns contained within the index.

在您的示例中，您正在检索所有列，只有22行，它可能更快执行表扫描并对其进行排序，而不是使用索引。优化器可能/应该这样做并忽略索引。如果是这种情况，那么索引就是占用空间而没有任何好处。如果经常访问类别表，您可能需要考虑将其固定到内存中，以便数据库服务器可以随时访问它而无需一直转到磁盘。

In your example you are retrieving all the columns and with only 22 rows and it may be faster to just do a table scan and sort those instead of using the index. The optimizer may/should be doing this and ignoring the index. If that is the case, then the index is just taking up space with no benefit. If your "categories" table is accessed often, you may want to consider pinning it into memory so the db server keeps it accessible without having to goto the disk all the time.

添加索引时，需要平衡磁盘空间，查询性能以及更新和插入表的性能。您可以在静态表上获得更多索引，并且不会发生太大变化，而不是每天有数百万次更新的表。您将开始感受到索引维护的影响。您的环境中可接受的是，并且只能由您和您的组织确定。

When adding indexes you need to balance out disk space, query performance, and the performance of updating and inserting into the tables. You can get away with more indexes on tables that are static and don't change much as opposed to tables with millions of updates a day. You'll start feeling the affects of index maintenance at that point. What is acceptable in your environment though is and can only be determined by you and your organization.

进行分析时，请务必生成/更新您的表和索引统计信息这样您就可以确保准确计算。

When doing your analysis, be sure to generate/update your table and index statistics so that you can be assured of accurate calculations.

这篇关于MySQL索引 - 有多少足够？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！