在讨论多列索引时，我使用类似于电话簿的方法.电话簿基本上是姓氏，然后是名字的索引.因此，排序顺序由第一个列"确定.搜索分为以下几类:

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.