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)

- James Oravec

---

使用 VARCHAR，而不是 CHAR（除非名称始终恰好为30个字符）。 - Rick James

Answer 1

当讨论多列索引时，我会使用电话簿的比喻。电话簿基本上是按姓氏，然后是名字排列的索引。所以排序顺序由第一个“列”决定。搜索可以分为几类：

1. 如果您查找姓氏为Smith的人，您可以轻松地找到他们，因为电话簿按姓氏排序。 2. 如果您查找名字为John的人，电话簿无法帮助您，因为Johns散布在整个电话簿中。您必须扫描整个电话簿才能找到他们。 3. 如果您查找具有特定姓氏Smith和特定名字John的人，则电话簿有所帮助，因为您可以找到排在一起的Smith，并且在这群Smith中，Johns也按排序顺序排列。

如果您有按名字排序然后按姓氏排序的电话簿，则书的排序将在上述情况＃2和＃3中帮助您，但不是情况＃1。

这解释了查找精确值的情况，但是如果您正在查找范围值怎么办？例如，您想找到所有名字为John并且姓氏以'S'开头（Smith，Saunders，Staunton，Sherman等）的人。约翰逊在每个姓氏下按“J”排序，但是如果您想要所有姓氏以'S'开头的约翰逊，它们并不会被放在一起。他们又散落了，因此您最终不得不扫描所有以'S'开头的姓氏。而如果电话簿按名字然后按姓氏组织，您将找到所有的约翰逊，然后在约翰逊内部，所有以'S'结尾的姓氏将排在一起。

因此，多列索引中的列顺序确实很重要。一个类型的查询可能需要索引的某个列顺序。如果您有几种类型的查询，则可能需要几个索引来帮助它们，并且列的顺序也不同。你可以阅读我的演示文稿《如何真正设计索引》获取更多信息，或者观看视频。

- Bill Karwin

---

我非常喜欢“电话簿类比”的比喻。 - Pascal Klein

多列索引是否有助于多列排序？还是只有在多列约束条件下才有帮助？ - CMCDragonkai

请参考电话簿的类比。它是按多列键值排序的：姓和名。如果你进行一个查询，要求按姓和名进行排序，那么查询优化程序会说：“嘿！它已经按这个顺序存储了！我可以按其自然顺序读取它并将其发送给用户，无需重新排序！” - Bill Karwin

但如果排序是ASC和DESC或DESC和ASC，它就不起作用了，对吧？它只适用于ASC和ASC或DESC和DESC。 - CMCDragonkai

@CMCDragonkai，是的，那是一个问题。顺便说一下，你能很快地发现这个问题真的很棒。许多开发人员可能无法预见到这一点。MySQL 8.0正在开发一项功能来解决这个问题。当您创建索引时，可以声明哪些列是升序的，哪些是降序的。然后，如果以与该索引中列的“方向”匹配的相同组合进行搜索，则可以使用该索引优化查询。请参见http://mysqlserverteam.com/mysql-8-0-labs-descending-indexes-in-mysql/。 - Bill Karwin

Answer 2

这两个索引是不同的。这在MySQL和其他数据库中都是正确的。MySQL在documentation中对它们的不同进行了很好的解释。

考虑这两个索引：

create index idx_lf on name(last_name, first_name);
create index idx_fl on name(first_name, last_name);

这两个应该同样适用于：

where last_name = XXX and first_name = YYY

idx_lf将在以下条件下达到最佳状态：

where last_name = XXX
where last_name like 'X%'
where last_name = XXX and first_name like 'Y%'
where last_name = XXX order by first_name

idx_fl将对以下内容最优：

where first_name = YYY
where first_name like 'Y%'
where first_name = YYY and last_name like 'X%'
where first_name = XXX order by last_name

许多情况下，这两个索引可能都可以使用，但是其中一个更优。例如，考虑使用查询idx_lf：

where first_name = XXX order by last_name

MySQL可以使用idx_lf读取整个表，然后在order by之后进行过滤。我认为这在实践中不是MySQL的优化选项，但其他数据库可能会发生这种情况。

- Gordon Linoff

---

+1 对于 MySQL 文档链接和您的示例，进一步强调查询中顺序的重要性。 - Phil DD

感谢提到“order by”。 - Oleg

Answer 3

一般规则是，在多列索引中，您希望首先放置最具选择性的列，也就是会给出最少结果的列。因此，如果您正在为具有约10个可能值的status 列和一个dateAdded列的表创建多列索引，并且您通常编写以下类型的查询

SELECT * FROM myTable WHERE status='active' and dateAdded='2010-10-01'

如果你只想扫描少量的行而不是10%（或其他比例的“活动”行），那么你首先需要考虑dateAdded，这需要进行深思熟虑和调整；你应该查看Lahdenmaki和Leach的书。

- Graham Charles

---

我同意在进行选择时遵循顺序，但我的问题与多列索引中列的顺序有关。 - James Oravec

不，我也在谈论索引创建。但是你创建索引来支持数据库将执行的查询类型--因此这个例子。 - Graham Charles

在您的具体示例中，索引中的列顺序在大多数情况下可能并不重要，因为名字和姓氏的分布大致相等。但是，如果您在例如越南这样只有很少不同姓氏的地方，则应该将名字放在索引的第一位。 - Graham Charles

where子句中列条件的顺序无关紧要，你的情况完全错误。 - Jack Zhu

我会重复一遍，或者再重复一遍。我没有讨论WHERE子句中的列顺序。（解析器不关心。）多列索引中的列顺序*确实很重要。 - Graham Charles