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. 如果您查找姓氏为Smith的人，则可以轻松找到他们，因为这本书是按姓氏排序的。

2. 如果您查找名字叫John的人，则电话簿无济于事，因为Johns散布在整个电话簿中。您必须扫描整个电话簿才能找到全部内容。

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

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

这就解释了查找精确值的情况，但是如果您要按值范围查找怎么办？假设您想查找所有姓氏为John且姓氏以’S’开头的人（史密斯，桑德斯，斯坦顿，谢尔曼等）。约翰在每个姓氏中的’J’下排序，但是如果您希望所有以’S’开头的姓氏都包含约翰，那么约翰就不会分组在一起。它们又被分散了，所以您最终不得不浏览所有以’S’开头的姓氏。而如果电话簿是按照名字然后按照姓氏来组织的，那么您会发现所有的John在一起，然后在John内将所有“
S”姓氏组合在一起。

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

您可以阅读我的演示“如何设计索引”，了解更多信息。