mysql - 为什么存在唯一索引时MySQL Innodb "Creating sort index"？

Question

在一个简单但非常大的 Innodb 表上，我在 A 列上有一个唯一索引，我想按照(整数)列 A 的顺序获取(整数)列 B 的列表

非常简单的查询，我正在分页数百万条记录。

SELECT B FROM hugeTable ORDER BY A LIMIT 10000 OFFSET 500000

在非常快的服务器上每次查询需要 10 秒？

文件排序:是 Filesort_on_disk:是 Merge_passes:9

这对我来说毫无意义，为什么它不能使用 Index A ？

Explain 显示简单，没有可能的键和文件排序。

Answer 1

如果 B 列的值在索引页中不可用，则 MySQL 将需要访问基础表中的页。也没有过滤正在考虑哪些行的谓词，这意味着 MySQL 看到需要返回所有行。这可以解释为什么没有使用索引。

另请注意，LIMIT 操作在语句末尾处理，几乎是执行计划的最后一步，但有一些异常(exception)。

8.2.1.3. Optimizing LIMIT Queries http://dev.mysql.com/doc/refman/5.5/en/limit-optimization.html

我怀疑您的查询可能会使用覆盖索引，例如“ON hugetable (A,B)”，以避免排序操作。

如果没有覆盖索引，您可以尝试像这样重写查询，看看这是否会使用 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

我建议您只对内联 View 查询(别名为 k)执行 EXPLAIN，看看它是否显示“Using index”。

外部查询可能仍有“Using filesort”操作，但至少只有 10,000 行。

(注意:您可能想在外部查询中尝试用“ORDER BY i.A”代替“k.A”，看看这是否有所不同。 )

---

附录

没有具体解决您的问题，但就该查询的性能而言，如果这是“分页”一组行，要考虑的另一种选择是使用“下一页”的值A"从上一个查询中检索到的最后一行作为下一行的“起点”。

原始查询看起来是“第 51 页”(每页 10,000 行，第 51 页是第 510,001 到 520,000 行)。

如果您还返回“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

如果您还保留了“第一”行中 A 的值，则可以使用它来备份页面。这实际上只适用于前进一页或后退一页。跳转到不同的页面，将不得不使用查询的原始形式，计算行数。