SQL 复合索引如何工作？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/795031/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do composite indexes work?
提问by Joe Phillips
I've created composite indexes (indicesfor you mathematical folk) on tables before with an assumption of how they worked. I was just curious if my assumption is correct or not.
我创建综合指数（指数表上你的数学民间）之前，他们是如何工作的假设。我只是好奇我的假设是否正确。
I assume that when you list the order of columns for the index, you are also specifying how the indexes will be grouped. For instance, if you have columns a, b, and c, and you specify the index in that same order a ASC, b ASC, and c ASCthen the resultant index will essentially be many indexes for each "group" in a.
我假设当您列出索引的列顺序时，您还指定了索引的分组方式。例如，如果您有列a、b和c，并且您以相同的顺序指定索引a ASC，b ASC，c ASC那么结果索引实际上将是 中每个“组”的许多索引a。
Is this correct? If not, what will the resultant index actually look like?
这样对吗？如果没有，结果索引实际上会是什么样子？
回答by Rik
Composite indexes work just like regular indexes, except they have multi-values keys.
复合索引的工作方式与常规索引类似，只是它们具有多值键。
If you define an index on the fields (a,b,c) , the records are sorted first on a, then b, then c.
如果在字段 (a,b,c) 上定义索引，则记录首先按 a 排序，然后是 b，然后是 c。
Example:
例子：
| A | B | C |
-------------
| 1 | 2 | 3 |
| 1 | 4 | 2 |
| 1 | 4 | 4 |
| 2 | 3 | 5 |
| 2 | 4 | 4 |
| 2 | 4 | 5 |
回答by Quassnoi
Composite index is like a plain alphabet index in a dictionary, but covering two or more letters, like this:
复合索引就像字典中的普通字母索引，但覆盖两个或多个字母，如下所示：
AA - page 1
AB - page 12
etc.
等等。
Table rows are ordered first by the first column in the index, then by the second one etc.
表行首先按索引中的第一列排序，然后按第二列排序，依此类推。
It's usable when you search by both columns OR by first column. If your index is like this:
当您按两列或按第一列搜索时，它是可用的。如果你的索引是这样的：
AA - page 1
AB - page 12
…
AZ - page 245
BA - page 246
…
you can use it for searching on 2letters ( = 2columns in a table), or like a plain index on one letter:
你可以用它来搜索2字母（= 2表中的列），或者像一个字母上的普通索引：
A - page 1
B - page 246
…
Note that in case of a dictionary, the pages themself are alphabetically ordered. That's an example of a CLUSTEREDindex.
请注意，在字典的情况下，页面本身按字母顺序排列。这是一个CLUSTERED索引的例子。
In a plain, non-CLUSTEREDindex, the references to pages are ordered, like in a history book:
在普通的非CLUSTERED索引中，对页面的引用是有序的，就像在历史书中一样：
Gaul, Alesia: pages 12, 56, 78
Gaul, Augustodonum Aeduorum: page 145
…
Gaul, Vellaunodunum: page 24
Egypt, Alexandria: pages 56, 194, 213, 234, 267
Composite indexes may also be used when you ORDER BYtwo or more columns. In this case a DESCclause may come handy.
当您有ORDER BY两列或更多列时，也可以使用复合索引。在这种情况下，DESC条款可能会派上用场。
See this article in my blog about using DESCclause in a composite index:
请参阅我的博客中有关DESC在复合索引中使用子句的文章：
回答by Walter Mitty
The most common implementation of indices uses B-trees to allow somewhat rapid lookups, and also reasonably rapid range scans. It's too much to explain here, but here's the Wikipedia article on B-trees. And you are right, the first column you declare in the create index will be the high order column in the resulting B-tree.
索引的最常见实现使用 B 树来允许稍微快速的查找，以及相当快速的范围扫描。在这里解释太多了，但这是关于B-trees的维基百科文章。你是对的，你在创建索引中声明的第一列将是结果 B 树中的高阶列。
A search on the high order column amounts to a range scan, and a B-tree index can be very useful for such a search. The easiest way to see this is by analogy with the old card catalogs you have in libraries that have not yet converted to on line catalogs.
对高阶列的搜索相当于范围扫描，而 B 树索引对于此类搜索非常有用。看到这一点的最简单方法是类比图书馆中尚未转换为在线目录的旧卡片目录。
If you are looking for all the cards for Authors whose last name is "Clemens", you just go to the author catalog, and very quickly find a drawer that says "CLE- CLI" on the front. That's the right drawer. Now you do a kind of informal binary search in that drawer to quickly find all the cards that say "Clemens, Roger", or "Clemens, Samuel" on them.
如果您正在寻找姓氏为“Clemens”的作者的所有卡片，您只需转到作者目录，并很快找到一个前面写着“CLE-CLI”的抽屉。那是正确的抽屉。现在你在那个抽屉里做一种非正式的二分搜索，以快速找到所有写着“克莱门斯，罗杰”或“克莱门斯，塞缪尔”的卡片。
But suppose you want to find all the cards for the authors whose first name is "Samuel". Now you're up the creek, because those cards are not gathered together in one place in the Author catalog. A similar phenomenon happens with composite indices in a database.
但是假设您要查找名字为“Samuel”的作者的所有卡片。现在您已经到了河边，因为这些卡片没有聚集在作者目录中的一个地方。数据库中的复合索引也会发生类似的现象。
Different DBMSes differ in how clever their optimizer is at detecting index range scans, and accurately estimating their cost. And not all indices are B-trees. You'll have to read the docs for your specific DBMS to get the real info.
不同的 DBMS 的优化器在检测索引范围扫描和准确估计其成本方面的智能程度有所不同。并不是所有的索引都是 B 树。您必须阅读特定 DBMS 的文档才能获得真实信息。
回答by Mash
No. Resultant index will be single index but with compound key.
不。结果索引将是单个索引，但带有复合键。
KeyX = A,B,C,D; KeyY = 1,2,3,4;
KeyX = A,B,C,D; KeyY = 1,2,3,4;
Index KeyX, KeyY will be actually: A1,A2,A3,B1,B3,C3,C4,D2
索引 KeyX、KeyY 实际上是：A1、A2、A3、B1、B3、C3、C4、D2
So that in case you need to find something by KeyX andKeyY - that will be fast and will use single index. Something like SELECT ... WHERE KeyX = "B" AND KeyY = 3.
因此，如果您需要通过 KeyX和KeyY查找某些内容 - 这将很快并且将使用单个索引。类似于 SELECT ... WHERE KeyX = "B" AND KeyY = 3。
But it's important to understand: WHERE KeyX = ? requests willuse that index, while WHERE KeyY = ? will NOTuse such index at all.
但重要的是要了解：WHERE KeyX = ? 请求将使用该索引，而 WHERE KeyY = ? 根本不会使用这样的索引。
回答by tk_
My understanding is, Composite indexes work just like regular indexes, except they have multi-values keys. If you define an index on the fields (a,b,c) , Since the composite index will be stored in a BinaryTree therefore, your index will work only following combinations of searches.
我的理解是，复合索引的工作方式与常规索引一样，只是它们具有多值键。如果您在字段 (a,b,c) 上定义索引，由于复合索引将因此存储在 BinaryTree 中，您的索引将仅在搜索组合后起作用。
ABC
AB
A
For example creating a composite index for a,b and c field is equivalent to creating separate indexes for a, ab, and abc.
例如，为 a、b 和 c 字段创建复合索引等效于为 a、ab 和 abc 创建单独的索引。
