mysql - MySQL中多列索引的字段顺序是否重要

Question

我知道索引的重要性以及连接顺序如何改变性能。我做了一堆与多列索引相关的阅读，但没有找到我的问题的答案。

我很好奇我是否做一个多列索引，如果指定它们的顺序是否重要。我的猜测是它不会，并且引擎会将它们视为一个组，其中排序无关紧要。但我想验证一下。

例如来自mysql的网站(http://dev.mysql.com/doc/refman/5.0/en/multiple-column-indexes.html)

CREATE TABLE test (
    id         INT NOT NULL,
    last_name  CHAR(30) NOT NULL,
    first_name CHAR(30) NOT NULL,
    PRIMARY KEY (id),
    INDEX name (last_name,first_name)
);

在以下情况下会更好还是等效的任何情况下会有什么好处？

CREATE TABLE test (
    id         INT NOT NULL,
    last_name  CHAR(30) NOT NULL,
    first_name CHAR(30) NOT NULL,
    PRIMARY KEY (id),
    INDEX name (first_name,last_name)
);

具体来说:

INDEX name (last_name,first_name)

对

INDEX name (first_name,last_name)

Answer 1

在讨论多列索引时，我将其比喻为电话簿。电话簿基本上是姓氏索引，然后是名字。所以排序顺序取决于哪个“列”在前。搜索分为几类:

1. 如果您查找姓氏为史密斯的人，您可以轻松找到他们，因为这本书是按姓氏排序的。

2. 如果您查找名字是约翰的人，电话簿并没有帮助，因为约翰分散在整本书中。你必须扫描整个电话簿才能找到它们。

3. 如果您查找具有特定姓氏 Smith 和特定名字 John 的人，这本书会有所帮助，因为您会发现 Smiths 被排序在一起，并且在该 Smiths 组中，Johns 也是按排序顺序找到的.

如果您的电话簿按名字排序，然后按姓氏排序，那么在上述情况 #2 和 #3 中，电话簿的排序将有助于您，但在情况 #1 中没有帮助。

这就解释了查找精确值的情况，但是如果您是按值的范围查找呢？假设您想查找名字为 John 且姓氏以“S”开头的所有人(Smith、Saunders、Staunton、Sherman 等)。 Johns 在每个姓氏中按“J”排序，但如果您希望所有姓氏以“S”开头的所有 Johns，则不会将 Johns 分组在一起。它们又分散了，所以你最终不得不扫描所有姓氏以“S”开头的名字。然而，如果电话簿是按名字然后按姓氏组织的，您会发现所有 Johns 在一起，然后在 Johns 中，所有“S”姓氏将被组合在一起。

因此，多列索引中的列顺序肯定很重要。一种类型的查询可能需要索引的特定列顺序。如果您有多种类型的查询，您可能需要多个索引来帮助它们，列的顺序不同。

您可以阅读我的演示文稿 How to Design Indexes, Really更多信息，或video .