复合索引在 MySQL 中有方向吗? [英] Does compound index have direction in MySQL?

问题描述

什么时候需要以下内容:

When will the below be necessary:

create index i_t_a_b on t(a,b);

create index i_t_b_a on t(b,a);

推荐答案

当你想要最大的检索速度并且在 join 或 where 条件下有两列时，但有时列 a 的选择性更高，有时列 b 的选择性更高，并且您想从单一索引中利用这一事实.

When you want the maximum retrieval speed and have both columns in the join or where conditions, BUT sometimes column a has higher selectivity and sometimes column b has higher selectivity, and you want to capitalize on that fact from single index.

此外，我认为您的数据大小/机器性能的比率应该相当高，同时您必须(猜测)愿意将任何改进称为必要(即使只有几个百分比).

Also I think your ratio of data size / performance of the machine should be quite high and at the same time you will have to (guesstimating) be willing to call any improvement a necessity (even if only by a few percentages).

不过，经验告诉我们，事情取决于很多因素；借助特定的 RDBMS 和应用程序环境，您可以更好地运行自己的基准测试.

Still, experience teaches that things depend on lot of factors; with specific RDBMS and application environments you better run your own benchmarks.

关于复合索引的进一步说明.来自维基百科:
在索引定义中列出列的顺序很重要.可以仅使用第一个索引列来检索一组行标识符.但是，检索是不可能的或高效的(在大多数数据库上)仅使用第二个或更大索引列的行标识符集.
例如，假设电话簿首先按城市、姓氏和名字组织.如果给定了城市，则可以轻松提取该城市的所有电话号码列表.但是，在此电话簿中查找给定姓氏的所有电话号码将非常乏味.您必须在每个城市的部分中查找具有该姓氏的条目."

Further explanation on composite indexes. from wikipedia:
"The order in which columns are listed in the index definition is important. It is possible to retrieve a set of row identifiers using only the first indexed column. However, it is not possible or efficient (on most databases) to retrieve the set of row identifiers using only the second or greater indexed column.
For example, imagine a phone book that is organized by city first, then by last name, and then by first name. If you are given the city, you can easily extract the list of all phone numbers for that city. However, in this phone book it would be very tedious to find all the phone numbers for a given last name. You would have to look within each city's section for the entries with that last name."

维基百科的解释可能过于简化，但它为您提供了基本概念(类比时请记住电话簿通常具有聚集索引，而这不会是您的通用数据库索引).

Wikipedia's explanations is maybe overly simplified, but it gives you the basic idea (as analogies go keep in mind that phone books usually have clustered indexes and that wouldn't be your general database index).

根据索引的大小、数据结构的大小、可用内存、索引第一列的选择性，使用错误排序的索引仍然可能比使用表扫描便宜得多.

Depending on the size of the index vs size of the data structure vs available memory vs selectivity on the first column of the index it still might be much less expensive to use wrongly ordered index then to use table scans.

啊，刚刚想到了一个更好的类比，你正在寻找一个例子想象一下一本不错的教科书，它会有一个包含章节和子章节的目录以及它们所在的页数(这是一个非聚集索引，它包含指向数据记录 - 页的指针).现在想象一下教科书是关于 SQL-92 标准的，那么 TOC 中的大部分术语都是 SQL 术语(确实持有这个假设).您还会在书的末尾有另一个索引，其中会按字母顺序(假设有主要章节名称)和页码列出所有有趣的术语.

Ah, just thought of a better analogy with an example you are looking for Imagine a nice textbook, it would have table of contents with chapters and subchapter and number of the pages at which they are at (which is a non clustered index which hold pointers to data records - pages). Now imagine that the textbook is on SQL-92 standard, then most of the terms in TOC would be SQL terms (do hold this assumption). You would also have another index at the end of the book which would list all the interesting terms in alphabetical orders (let's assume with major chapter names) and page numbers.

对于诸如告诉我出现 DISTINCT 的所有章节"，您将使用第二个索引.(因为后面的字段选择性高)

For question such as 'Tell me all the chapters under which DISTINCT appears' you would use the second index. (because the selectivity of the later field is high)

对于诸如'告诉我出现在第一章下的术语的数量'你将使用 TOC

For question such as 'Tell me the number of the terms that appear under first chapter' you would use the TOC

所以对于诸如在 DML 章节中是否描述了 SELECT?"您可以使用其中一个索引.(因为两个领域的选择性都很高)但是，如果 DML 的 TOC 本身有 3 页长，而索引中的 SELECT 条目只有 15 行，您可能会转到第二行，这就是您从两个索引中受益的示例.

So for questions such as 'Is SELECT described under DML chapter?' you might use either of the indexes. (because selectivity of both fields is high) However if TOC of DML itself is 3 pages long and the SELECT entry in the index has only fifteen lines you would probably go to the second one, and that is an example of when you benefit from both indexes.

现在，如果您认为这太过分了，请考虑使用扫描过的国会图书馆的数据库.:)

Now, if you think that's too far fetched do take a database of the scanned library of congress into consideration. :)

正如我之前所说，所有计划都很好，但最后还是要运行您自己的基准测试.

As I said before, all the planning is fine, but at the end do run your own benchmarks.

这篇关于复合索引在 MySQL 中有方向吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！