存在唯一索引时，为什么 MySQL Innodb “创建排序索引”？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17812794/
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
Why does MySQL Innodb "Creating sort index" when unique index exists?
提问by ck_
On a simple but very large Innodb table, I have a unique index on column A and I want to get a list of (integer) column B in order of (integer) column A
在一个简单但非常大的 Innodb 表上，我在 A 列上有一个唯一索引，我想按照（整数）A 列的顺序获取（整数）B 列的列表
Very simple query, I am paging through millions of records.
非常简单的查询，我正在翻阅数百万条记录。
SELECT B FROM hugeTable ORDER BY A LIMIT 10000 OFFSET 500000
SELECT B FROM hugeTable ORDER BY A LIMIT 10000 OFFSET 500000
This takes 10 seconds per query on a very fast server?
在非常快的服务器上，每次查询需要 10 秒？
Filesort: Yes Filesort_on_disk: Yes Merge_passes: 9
Filesort: Yes Filesort_on_disk: Yes Merge_passes: 9
This makes no sense to me, why can it not use Index A ?
这对我来说毫无意义，为什么它不能使用 Index A ？
Explain shows simple, no possible keys and filesort.
解释显示简单，没有可能的键和文件排序。
回答by spencer7593
If the values for column B are not available in the index pages, then MySQL will need to access pages in the underlying table. Also there no predicate that filters which rows are being considered, and that means MySQL is seeing that ALL rows need to be returned. This could explain why the index is not being used.
如果 B 列的值在索引页中不可用，则 MySQL 将需要访问基础表中的页。也没有谓词过滤正在考虑的行，这意味着 MySQL 看到需要返回所有行。这可以解释为什么没有使用索引。
Also note that the LIMIToperations are processed at the end of the statement, as nearly the last step in the execution plan, with a some exceptions.
另请注意，LIMIT操作在语句的末尾处理，几乎是执行计划中的最后一步，但有一些例外。
8.2.1.3. Optimizing LIMIT Querieshttp://dev.mysql.com/doc/refman/5.5/en/limit-optimization.html
8.2.1.3. 优化 LIMIT 查询http://dev.mysql.com/doc/refman/5.5/en/limit-optimization.html
I suspect that your query could make use of a covering index, for example "ON hugetable (A,B)", to avoid the sort operation.
我怀疑您的查询可能会使用覆盖索引（例如“ ON hugetable (A,B)”）来避免排序操作。
Absent a covering index, you could try rewriting the query something like this, to see if this will make use of the index on column A, and avoid a sort operation on millions of rows (to get the first 510,000 rows returned in order):
如果没有覆盖索引，您可以尝试像这样重写查询，看看这是否会使用 A 列上的索引，并避免对数百万行进行排序操作（以获得按顺序返回的前 510,000 行）：
SELECT i.B
FROM ( SELECT j.A
FROM hugeTable j
ORDER
BY j.A
LIMIT 10000 OFFSET 500000
) k
JOIN hugetable i
ON i.A = k.A
ORDER
BY k.A
I suggest you do an EXPLAINon just the inline view query (aliased as k), and see if it shows "Using index."
我建议您EXPLAIN只对内联视图查询（别名为 k）进行查询，看看它是否显示“ Using index.”。
The outer query is likely to still have a "Using filesort" operation, but at least that will be on only 10,000 rows.
外部查询可能仍然有“ Using filesort”操作，但至少只有 10,000 行。
(NOTE: You may want to try an "ORDER BY i.A" in place of "k.A" on the outer query, and see if that makes a difference.)
（注意：您可能想在外部查询中尝试用“ ORDER BY i.A”代替“ k.A”，看看是否有所不同。）
ADDENDUM
附录
Not specifically addressing your question, but in terms of performance of that query, if this is "paging through" a set of rows, another option to consider, to get to the "next" page is to use the value of "A" from the last row retrieved on the previous query as a "starting point" for the next row.
没有专门解决你的问题，但在该查询性能方面，如果这是一组行的“通过分页”，另一个值得考虑的选择，去了“下一个”页面使用的“值A”，从在上一个查询中检索的最后一行作为下一行的“起点”。
The original query looks like it's getting "page 51" (10,000 rows per page, page 51 would be rows 510,001 thru 520,000).
原始查询看起来像是“第 51 页”（每页 10,000 行，第 51 页将是第 510,001 到 520,000 行）。
If you were to also return the value of 'A', and keep that for the last row. To get the "next" page, the query could actually be:
如果您还要返回“A”的值，并将其保留在最后一行。要获取“下一个”页面，查询实际上可以是：
SELECT i.B, k.A
FROM ( SELECT j.A
FROM hugeTable j
WHERE j.A > $value_of_A_from_row_520000
-- ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
ORDER BY j.A ASC
LIMIT 10000
) k
JOIN hugetable i
ON i.A = k.A
ORDER
BY k.A
If you also kept the value for A from the "first" row, you could use that for backing up a page. That would really only work for forward one page, or back one page. Jumping to a different page, would have to use the original form of the query, counting rows.
如果您还保留了“第一”行中 A 的值，则可以使用它来备份页面。那真的只适用于前进一页或后退一页。跳转到不同的页面，将不得不使用查询的原始形式，计算行数。
