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 中.为什么这个查询会出现在日志中?如果只包含 22 行，为什么要检查 44 行?

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?

推荐答案

索引的数量和行的过多将取决于很多因素.在像类别"表这样的小表上，您通常不需要或不需要索引，它实际上会损害性能.原因是读取索引需要 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屋！