MySQL索引的最佳实践是什么?

有效加载数据:索引加速检索，但减慢插入和删除，以及更新索引列中的值。也就是说，索引会降低大多数涉及写入的操作的速度。这是因为写入一行不仅需要写入数据行，还需要更改任何索引。一个表的索引越多，需要做的更改就越多，平均性能下降就越大。大多数表的读次数多，写次数少，但对于写次数高的表，索引更新的成本可能很大。

避免索引:如果你不需要一个特定的索引来帮助查询更好地执行，就不要创建它。

磁盘空间:一个索引占用磁盘空间，多个索引相应地占用更多空间。这可能会导致您比没有索引时更快地达到表大小限制。尽可能避免使用索引。

建议:不要过度索引

1/2)索引会加速某些选择操作，但会减慢其他操作，如插入、更新和删除。这可以是一个很好的平衡。

3)使用全文索引或狮身人面像

如果一个表有六列，他们都是可搜索的，我应该索引他们所有或没有

索引对性能的负面影响是什么

更新和插入将变慢。此外，还需要额外的存储空间，但这在如今通常并不重要。

如果我有一个varchar2500列，可以从我的网站的部分搜索，我应该索引它

不，除非它是UNIQUE(这意味着它已经被索引)，或者你只在该字段上搜索确切的匹配(不使用LIKE或mySQL的全文搜索)。

通常我把索引放在任何字段，我将搜索或选择使用WHERE子句

我通常会索引查询次数最多的字段，然后是int / boolean / enum，而不是VARCHARS字段。不要忘记，通常需要在组合字段上创建索引，而不是在单个字段上创建索引。使用EXPLAIN，并检查慢日志。

看看像更多掌握索引的艺术这样的演示。

2012年12月更新:我已经发布了我的新演示:如何设计索引，真的吗。2012年10月，我在圣克拉拉的ZendCon和12月的伦敦Percona Live上发表了这篇文章。

设计最佳索引的过程必须与你在应用程序中运行的查询相匹配。

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

是的，有一些开销，所以您不应该创建不必要的索引。但是你应该创建索引，为你需要快速运行的查询提供好处。索引的开销通常远远超过它的收益。

对于VARCHAR(2500)列，你可能想要使用全文索引或前缀索引:

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

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

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

复合指标

您可以创建复合索引—包含多个列的索引。MySQL可以从左到正确的使用这些。如果你有:

Table A
Id
Name
Category
Age
Description

如果你有一个复合索引，按顺序包含名字/类别/年龄，这些WHERE子句将使用索引:

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


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

但

WHERE Category='A' and Age > 18

不会使用这个索引，因为所有东西都是从左到右使用的。

解释

使用Explain / Explain Extended来了解MySQL可以使用哪些索引以及它实际选择了哪个索引。MySQL每次查询只使用ONE键。

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

慢速查询日志

打开慢查询日志查看哪些查询运行缓慢。

宽栏

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

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

一般来说，索引对表中的行施加了顺序。

为简单起见，假设一个表只是一个大的CSV文件。无论何时插入一行，它都会被插入在最后。因此，表的“自然”顺序就是插入行的顺序。

假设您将CSV文件加载到一个非常基本的电子表格应用程序中。这个电子表格所做的就是显示数据，并按顺序为行编号。

现在想象一下，您需要找到第三列中值为“M”的所有行。考虑到你所拥有的，你只有一个选择。扫描表，检查每一行的第三列的值。如果有很多行，这种方法(“表扫描”)可能会花费很长时间!

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

现在您有了一个很好的策略，可以找到所有第三列值为“M”的行。例如，你可以执行二分查找!尽管表扫描需要查看N行(其中N是行数)，但在最坏的情况下，二分搜索只需要查看log-n个索引项。哇，那肯定简单多了!

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

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

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

希望这涵盖了你的前两个问题(就像其他人回答的那样——你需要找到正确的平衡)。

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

最后，您需要开始考虑多个列上的索引。概念是相同的，行为类似于LIKE的东西——本质上，如果你在(a,b,c)上有一个索引，引擎将继续从左到右尽可能地使用索引。所以在列a上的搜索可能使用(a,b,c)索引，就像在(a,b)上的搜索一样。然而，如果你搜索WHERE b=5 AND c=1，引擎将需要做一个全表扫描。

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

一般来说，索引有助于加速数据库搜索，缺点是使用额外的磁盘空间和减慢INSERT / UPDATE / DELETE查询。使用EXPLAIN并读取结果来找出MySQL何时使用你的索引。

如果一个表有六列，他们都是可搜索的，我应该索引他们所有或没有他们?

索引所有六列并不总是最佳实践。

(a)在搜索特定信息时，你会使用这些列吗?

(b)这些列的选择性是什么(与表上记录的总量相比，存储了多少不同的值)?

MySQL使用基于成本的优化器，它在执行查询时试图找到“最便宜”的路径。选择性低的领域不是好的选择。

索引对性能的负面影响是什么?

已经回答:额外的磁盘空间，在插入-更新-删除期间性能较低。

如果我有一个varchar2500列，可以从我的网站的部分搜索，我应该索引它吗?

试试全文索引。