问MySQL索引-最佳实践是什么？

EN

你绝对应该花一些时间阅读索引，有很多关于它的文章，了解正在发生的事情是很重要的。

从广义上讲，索引对表中的行进行排序。

为了简单起见，假设一个表只是一个很大的CSV文件。每当插入一行时，它都会插入到末尾。因此，表的“自然”顺序就是插入行的顺序。

假设您已经在一个非常初级的电子表格应用程序中加载了CSV文件。这个电子表格所做的一切就是显示数据，并按顺序对行进行编号。

现在假设您需要查找第三列中具有值"M“的所有行。考虑到您拥有的可用资源，您只有一个选择。您可以扫描该表，检查每行第三列的值。如果你有很多行，这种方法(“表扫描”)可能需要很长时间！

现在想象一下，除了这个表之外，您还有一个索引。这个特定的索引是第三列中值的索引。索引以某种有意义的顺序(例如，按字母顺序)列出第三列中的所有值，并为每个值提供出现该值的行号列表。

现在，您有了一个很好的策略来查找第三列的值为"M“的所有行。例如，您可以执行binary search！表扫描需要查看N行(其中N是行数)，而二进制搜索在最坏的情况下只需要查看log-n个索引项。哇，那肯定容易多了！

当然，如果您有这个索引，并且要向表中添加行(最后，因为这就是我们的概念表的工作方式)，那么每次都需要更新索引。所以当你写新行的时候，你需要做更多的工作，但是当你在搜索的时候，你节省了大量的时间。

因此，通常情况下，索引会在读取效率和写入效率之间进行权衡。在没有索引的情况下，插入可以非常快--数据库引擎只向表中添加一行。添加索引时，引擎必须在执行插入操作时更新每个索引。

另一方面，读取变得更快了。

希望这涵盖了您的前两个问题(正如其他人已经回答的那样--您需要找到合适的平衡点)。

您的第三个场景稍微复杂一些。如果你使用LIKE，索引引擎通常会帮助你将读取速度提高到第一个"%“。换句话说，如果您是SELECTing WHERE列，如'foo%bar%'，数据库将使用索引来查找列以"foo“开头的所有行，然后需要扫描该中间行集以查找包含"bar”的子集。选择...像'%bar%‘这样的WHERE列不能使用索引。我希望你能明白为什么。

最后，您需要开始考虑多个列上的索引。概念是相同的，其行为类似于类似的东西--本质上，如果您在(a，b，c)上有一个索引，引擎将尽可能地从左到右继续使用该索引。因此，对列a的搜索可能使用(a，b，c)索引，对(a，b)的搜索也是如此。但是，如果您要搜索b=5和c=1的位置，引擎将需要执行全表扫描)

希望这有助于阐明一些事情，但我必须重申，您最好花几个小时挖掘一些深入解释这些事情的好文章。阅读特定数据库服务器的文档也是一个好主意。查询规划者实现和使用索引的方式可能有很大的不同。

查看More Mastering the Art of Indexing等演示文稿。

2012年12月更新:我发布了一个新的演示文稿：How to Design Indexes, Really。我于2012年10月在圣克拉拉的ZendCon上展示了这一点，并于2012年12月在伦敦Percona Live上展示了这一点。

设计最好的索引是一个必须与应用程序中运行的查询相匹配的过程。

很难推荐任何关于哪些列最适合索引的通用规则，或者您是否应该索引所有列、不索引任何列、哪些索引应该跨越多个列等。这取决于您需要运行的查询。

是的，有一些开销，所以你不应该创建不必要的索引。但是，您应该创建有利于快速运行的查询的索引。索引的开销通常远远大于它的好处。

对于为VARCHAR(2500)的列，您可能希望使用FULLTEXT index或前缀索引：

CREATE INDEX i ON SomeTable(longVarchar(100));

请注意，如果您正在搜索可能位于长varchar中间的单词，则传统索引不能提供帮助。为此，请使用全文索引。

我不会在其他答案中重复一些好的建议，但会补充：

复合指数

您可以创建复合索引-包含多个列的索引。MySQL可以使用这些从left到right。所以，如果你有：

Table A
Id
Name
Category
Age
Description

如果您的复合索引按顺序包含Name/Category/Age，则这些WHERE子句将使用该索引：

WHERE Name='Eric' and Category='A'

WHERE Name='Eric' and Category='A' and Age > 18

但

WHERE Category='A' and Age > 18

不会使用该索引，因为所有内容都必须从左到右使用。

解释

使用Explain / Explain扩展来了解哪些索引可用于MySQL，以及它实际选择了哪些索引。MySQL将在每个查询中仅使用一个密钥。

EXPLAIN EXTENDED SELECT * from Table WHERE Something='ABC'

慢查询日志

打开slow query log以查看哪些查询运行速度较慢。

Wide Columns

如果您有一个宽列，其中大部分区别发生在前几个字符中，则只能在索引中使用前N个字符。示例:我们有一个定义为varchar(255)的ReferenceNumber列，但是在97%的情况下，引用数字是10个字符或更少。我更改了索引，只查看前10个字符，性能也有了很大提高。