如何检测MySQL索引是必需的还是必需的？

Question

如何检测MySQL索引是必需的还是必需的？

我们的想法是，有些查询是可以改进的。我知道我可以潜入缓慢的查询日志中...但是我遇到了下面关于MySQL的帖子，我想知道是否有一种简单的方法来分析当前MySQL数据库是否需要索引(并且会立即提高速度)。

感谢您的帮助

MS SQL资源：https://dba.stackexchange.com/questions/56/how-to-determine-if-an-index-is-required-or-necessary

Answer 1

你不能这么做。

有一些方法可以在一段时间内检测是否使用了索引。但是，没有办法确保不使用索引。假设您有一个每月一次的任务，该任务对表进行一些主要维护。而且您确实需要一个特定的索引来防止任务锁定表和关闭应用程序。如果您检查了该月大部分时间的索引使用情况，但没有包括该使用情况，则可能会决定不需要该索引。然后你会删除索引。并感到抱歉。(这是一个真实的轶事。)

同时，有一些关于索引的简单规则...

如果你也有INDEX(a,b).

• INDEX(id)是不必要的，如果你也有5个或更多列的PRIMARY KEY(id)或UNIQUE(id).

• An索引，
• INDEX(a)是不必要的，但不太可能是“有用的”。(缩写it.)
• INDEX(a), INDEX(b)与INDEX(a,b).
• INDEX(b,a)不同，因为both.
• INDEX(flag)，与INDEX(a,b)不同；如果flag具有少量不同的值，则可能永远不会使用缩写--优化器将扫描表格。
• 在许多情况下，“前缀”索引(INDEX(foo(10)))是无用的。(但每一列都有许多exceptions.)
• "I索引“--这是一个糟糕的设计pattern.
• Often，，但并非总是如此，同时拥有一个PK和一个UNIQUE键意味着某些东西少于PK表确实应该有一个显式的PK隐式地包含在任何辅键中。因此，给定PRIMARY KEY(id)，INDEX(foo)实际上是INDEX(foo, id).
• Sometimes优化器将忽略WHERE子句，并为ORDER BY.
• Some查询使用索引，这些查询具有如此扭曲的属性，以至于优化器将根据不同的常量使用不同的索引。(我确实看到一个query.)
• "Index合并交集有多达6个不同的解释计划“几乎总是不如复合索引。
• 这些技巧中的大多数都有例外。

因此，我更喜欢采用所有查询(SELECTs、UPDATEs和DELETEs)，为每个查询确定最佳索引，消除冗余等，以便找到“最佳”索引集。参见my cookbook on creating an index, given a SELECT。

Answer 2

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

从广义上讲，和index对表中的行进行排序。

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

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

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

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

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

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

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

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