MySQL的多列索引的字段顺序是否重要 [英] Does Order of Fields of Multi-Column Index in MySQL Matter

问题描述

我知道索引的重要性以及联接的顺序如何改变性能.我已经完成了与多列索引相关的大量阅读，但仍未找到我的问题的答案.

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)

vs

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.

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

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'开头的人(史密斯，桑德斯，斯坦顿，谢尔曼等).约翰在每个姓氏中的'J'下排序，但是如果您希望所有以'S'开头的姓氏都包含约翰，那么约翰就不会分组在一起.它们又被分散了，所以您最终不得不浏览所有以'S'开头的姓氏.假设电话簿是按照姓氏然后按姓氏排列的，那么您会发现所有John在一起，然后在Johns中将所有"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.

这篇关于MySQL的多列索引的字段顺序是否重要的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！