Spielt die Feldreihenfolge im mehrspaltigen Index in MySQL eine Rolle?

Question

Ich weiß, wie wichtig Indizes sind und wie sich die Join-Reihenfolge auf die Leistung auswirkt. Ich habe viel über mehrspaltige Indizes gelesen, aber keine Antwort auf meine Frage gefunden.

Ich bin gespannt, ob die Reihenfolge, in der sie angegeben werden, wichtig ist, wenn ich einen mehrspaltigen Index erstelle. Meine Vermutung ist nein, und die Engine wird sie als eine Gruppe behandeln, bei der die Reihenfolge keine Rolle spielt. Aber ich möchte es überprüfen.

Zum Beispiel von der MySQL-Website (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)
);

Wäre Folgendes auf jeden Fall besser oder gleichwertig und hätte es einen Nutzen?

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)
);

Im Einzelnen:

INDEX name (last_name,first_name)

Vergleich

INDEX name (first_name,last_name)

Answer 1

两个索引不同。这在 MySQL 和其他数据库中都是如此。 MySQL 在文档中解释了不同之处.

考虑两个索引：

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），但在其他数据库中可能会发生这种情况。

Answer 2

在讨论多列索引时，我用电话簿进行类比。电话簿基本上是先姓后名的索引。因此排序顺序是由哪个“列”在前决定的。搜索分为以下几类：

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

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

3. 如果您查找具有特定姓氏史密斯和特定名字约翰的人，这本书会有所帮助，因为您会发现史密斯一家排序在一起，并且在史密斯一家中，约翰一家也按排序顺序找到.

如果您有一本按名字排序的电话簿，然后按姓氏排序，则该电话簿的排序将在上述情况#2 和#3 中为您提供帮助，但不会在情况#1 中为您提供帮助。

这解释了查找精确值的情况，但是如果您按值范围查找怎么办？假设您想要查找名字为 John、姓氏以“S”开头的所有人员（Smith、Saunders、Staunton、Sherman 等）。约翰在每个姓氏中按“J”排序，但如果您想要所有以“S”开头的姓氏的所有约翰，则约翰不会分组在一起。他们又分散了，所以你最终不得不扫描所有姓氏以“S”开头的名字。然而，如果电话簿按名字然后按姓氏进行组织，您会发现所有约翰都在一起，然后在约翰中，所有“S”姓氏将被分组在一起。

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

您可以阅读我的演示文稿真正如何设计索引 了解更多信息，或观看视频。