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

2021-12-21 00:00:00 indexing performance sql mysql

我知道索引的重要性以及连接顺序如何改变性能.我已经阅读了大量与多列索引相关的内容，但还没有找到我的问题的答案.

I know the importance of indexes and how order of joins can change performance. I've done a bunch of reading related to multi-column indexes and haven't found the answer to my question.

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

I'm curious if I do a multi-column index, if the order that they are specified matters at all. My guess is that it would not, and that the engine would treat them as a group, where ordering doesn't matter. But I wish to verify.

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

For example, from mysql's 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)
);

在任何情况下，以下会更好还是等效?

Would there be any benifit in any cases where the following would be better, or is it equivalent?

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)

推荐答案

在讨论多列索引时，我使用了电话簿的类比.电话簿基本上是姓氏索引，然后是名字索引.所以排序顺序是由哪个列"在前决定的.搜索分为几类:

When discussing multi-column indexes, I use an analogy to a telephone book. A telephone book is basically an index on last name, then first name. So the sort order is determined by which "column" is first. Searches fall into a few categories:

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

  1. If you look up people whose last name is Smith, you can find them easily because the book is sorted by last name.

如果您查找名字为约翰的人，电话簿无济于事，因为约翰分散在整本书中.你必须扫描整个电话簿才能找到它们.

If you look up people whose first name is John, the telephone book doesn't help because the Johns are scattered throughout the book. You have to scan the whole telephone book to find them all.

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

If you look up people with a specific last name Smith and a specific first name John, the book helps because you find the Smiths sorted together, and within that group of Smiths, the Johns are also found in sorted order.

如果您的电话簿按名字和姓氏排序，则电话簿的排序将在上述情况 #2 和 #3 中对您有所帮助，但在情况 #1 中没有帮助.

If you had a telephone book sorted by first name then by last name, the sorting of the book would assist you in the above cases #2 and #3, but not case #1.

这解释了查找精确值的情况，但如果您按值范围查找呢?假设您想查找所有名字为 John 且姓氏以S"开头的人(Smith、Saunders、Staunton、Sherman 等).约翰在每个姓氏中的J"下排序，但如果您希望所有姓氏的所有约翰都以S"开头，则约翰不会分组在一起.它们再次分散，因此您最终不得不扫描所有姓氏以S"开头的姓名.而如果电话簿按名字然后按姓氏组织，你会发现所有的约翰在一起，然后在约翰内，所有的S"姓会被分组在一起.

That explains cases for looking up exact values, but what if you're looking up by ranges of values? Say you wanted to find all people whose first name is John and whose last name begins with 'S' (Smith, Saunders, Staunton, Sherman, etc.). The Johns are sorted under 'J' within each last name, but if you want all Johns for all last names starting with 'S', the Johns are not grouped together. They're scattered again, so you end up having to scan through all the names with last name starting with 'S'. Whereas if the telephone book were organized by first name then by last name, you'd find all the Johns together, then within the Johns, all the 'S' last names would be grouped together.

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

So the order of columns in a multi-column index definitely matters. One type of query may need a certain column order for the index. If you have several types of queries, you might need several indexes to help them, with columns in different orders.

您可以阅读我的演示文稿如何设计索引，真的 了解更多信息.

You can read my presentation How to Design Indexes, Really for more information.

相关文章