MySQL索引-需要多少个？

Question

我正在尝试微调我的MySQL服务器，因此我检查了我的设置，分析了慢查询日志，并在可能的情况下简化了我的查询。

有时正确索引就足够了，有时不是。我曾经在某个地方读到过（如果这是愚蠢的说法，请纠正我），比我需要的更多的索引会产生相同的效果，就像我没有任何索引一样。

有多少索引足够呢？你可以说这取决于数百个因素，但我想知道如何清理我的mysql-slow.log以减少服务器负载。

此外，我看到了一些“有趣”的日志条目，例如：

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

这个表格恰好包含22行，索引设置在orderid上。为什么这个查询会出现在日志中呢？如果只有22行，为什么要检查44行呢？

- fabrik

---

我猜它进行了排序，所以它以某种方式多次检查了同一行 :x - Lliane

对于 EXPLAIN SELECT * FROM categories ORDER BY orderid ASC ，返回的是什么？ - Powerlord

@R. Bemrose：如果我改正它，它会返回一个Extra：Using filesort。也许这就是问题所在？ - fabrik

Answer 1

索引的数量和过度索引的程度会受到很多因素的影响。对于像“categories”表这样的小型表，通常不需要索引，反而可能影响性能。原因是读取索引需要I/O（即时间），然后需要更多的I/O和时间来检索与匹配行相关的记录。例外情况是，当您仅查询包含在索引中的列时。

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

在添加索引时，需要平衡磁盘空间、查询性能以及更新和插入表的性能。对于不太改变的静态表而言，可以在其中使用更多的索引，而不是每天进行数百万个更新的表。在那时，您将开始感受到索引维护的影响。不过，在您的环境中可接受的是什么，只能由您和您的组织来确定。

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

- RC.

---

谢谢！这对我来说是最清晰和有帮助的答案！ - fabrik

如何将表格“固定”在内存中？ - Matthew James Taylor

如何告诉MySQL将一个表“固定”在内存中？ - satoru

一种方法是使用内存存储引擎来操作表格。请注意，此方法有特定的用例和注意事项，通常最好确保您有足够的缓冲空间将最频繁访问的数据保存在数据库缓存中，并使用InnoDB等引擎。 - RC.

我阅读过的许多文档都说要避免在“小表”上创建索引。那么什么是小表： <100条记录，<1,000，<10,000，<100,000？ - LWSChad

Answer 2

一般而言，你应该在所有主键（这不是你的选择）、所有外键和任何其他常用于获取行的字段上建立索引。

例如，如果我经常按用户名查找用户，则应对其进行索引，即使用户ID是主键。

- Powerlord

Answer 3

多少个索引完全取决于您运行的查询、执行的联接类型（如果有）、表中存储的数据类型以及表的大小（以及许多其他因素）。这确实没有确切的科学方法。优化查询的最强大工具是explain。使用explain，您可以找出正在执行哪种联接，可能使用哪些键以及哪个键（如果有）被使用，以及每个表在联接中检查了多少行。

利用这些信息，您可以决定如何为表设置键和/或修改查询以使其更有效。explain的语法非常简单。

EXPLAIN SELECT * FROM `categories` ORDER BY `orderid` ASC;

请注意，解释（explain）实际上并不运行查询。因此，如果您使用它来调试需要5分钟才能运行的查询，则解释仍然非常快。

但是，当添加索引时，您需要小心，因为它们会使插入和更新变慢，在非常大的表上，这种性能损失可能会变得更加明显。特别是如果同一张表用于大量读取。虽然添加许多索引通常不会影响查询的性能，但仍应该根据需要仔细添加。

- Steven Surowiec

Answer 4

请记住，MySQL每个select语句最多只会使用一个索引（如果您使用了join，则可以为每个join使用一个索引）。因此，仅仅因为建立索引是浪费磁盘空间的，而且会减慢数据库写入速度。如果您经常在两个列上使用where语句，请建立一个包含这两个列的索引，它比仅建立一个索引要快得多。

- scotts

Answer 5

索引可以加速SELECT查询，但会减慢INSERT / UPDATE / DELETE查询，因为它们也需要更新索引，而不仅仅是行。

这只是个人意见（我没有事实来支持它），但我认为如果有一个查询需要很长时间，并且索引可以加速它-那就去做吧！“太多”索引是指添加了没有任何好处的索引（例如，没有查询可以加快）。例如，一个愚蠢的做法是在每个列上放置一个索引“只是因为这样”。

- Vilx-

---

“一个愚蠢的做法就是仅仅因为这样而在每一列上放置索引” - 这完全可以，但我想尽可能减少慢查询日志条目。否则感谢您对INSERT / UPDATE / DELETE的想法！ - fabrik

Answer 6

没有一个“最好”的索引数量的魔法数字。基本规则是：为经常使用和/或需要快速运行的查询添加索引。

拥有“太多”索引不应该减慢查询速度，但每个添加的索引都会增加一点时间来添加/更新数据库中的项目（因为它也会修改索引）和一小部分空间。然而，如果你只是根据需要添加索引，这可能不是一个大问题。

- Beska