Mysql - 为什么 FileSort 需要比实际提取时间更长的时间？

Question

我在我的 Mysql 数据库上运行了一个 sql 查询。我有一张包含 150 万条记录的表。我正在尝试根据项目的创建者获取最近创建的 50 个项目。

这里是查询:

SELECT *
FROM `items`
WHERE `items`.`owner_id`
  IN (1, 2, 3, 4, 5, 6, 7, 8)
ORDER BY `items`.`id`
  DESC LIMIT 50

查询使用的是 owner_id 索引，这是有道理的。正确的？显然，使用这个索引需要将近 3 秒，而使用主索引需要 100 毫秒。

在运行 explain 时，我看到以下内容:

1   SIMPLE  items   range   idx_owner   idx_owner   4   NULL    56  Using index condition; Using filesort

但是当我运行以下查询时:

SELECT *
FROM `items` FORCE INDEX(PRIMARY)
WHERE `items`.`owner_id`
  IN (1, 2, 3, 4, 5, 6, 7, 8)
ORDER BY `items`.`id`
  DESC LIMIT 50

我得到以下解释:

1   SIMPLE  items   index   NULL    PRIMARY 4   NULL    50  Using where

这意味着我刚刚摆脱了文件排序，尽管我在 where 子句上丢失了索引。

查询似乎返回 15,000 条记录(由于 in)，然后对它们进行排序并选择最后 50 条。至于我的问题 - 排序 15,000 条记录的效率怎么可能低于扫描 1.5M 表并搜索 15,000 条记录的效率？排序不应该是一项如此困难的任务，而搜索要困难得多(没有索引!)我错过了什么？

附表索引:

items   0   PRIMARY 1   id  A   1444298 NULL    NULL        BTREE       
items   1   items_a951d5d6  1   slug    A   288859  767 NULL        BTREE       
items   1   category_id_refs_id_3b77a81e    1   category_id A   34  NULL    NULL    YES BTREE       
items   1   origin_id_refs_id_99b3fd12  1   origin_id   A   2   NULL    NULL    YES BTREE       
items   1   parent_id_refs_id_99b3fd12  1   parent_id   A   6   NULL    NULL    YES BTREE       
items   1   name    1   name    A   1444298 NULL    NULL        BTREE       
items   1   idx_owner   1   owner_id    A   722149  NULL    NULL        BTREE       

谢谢!

Answer 1

对数千行信息进行排序并不像您想象的那么便宜。另外，请注意 filesort 并不一定意味着文件系统中文件的排序。这意味着需要对派生表进行排序。

您正在查看的查询可以重构如下，结果很可能会执行得更好。

SELECT i.*
  FROM items AS i
  JOIN (
        SELECT id
          FROM items
         WHERE owner_id IN (1, 2, 3, 4, 5, 6, 7, 8)
         ORDER BY id DESC
         LIMIT 50
       ) AS j ON i.id = j.id
 ORDER BY i.id DESC

这是因为您的原始查询包含 SELECT *。为了满足该查询，MySQL 必须打乱表中的所有列。此重构中的子查询只是提供了您想要的五十个 id 值。它仍然需要对它们进行排序，但是对一堆整数进行排序比对一堆行进行排序要快。

        SELECT id
          FROM items
         WHERE owner_id IN (1, 2, 3, 4, 5, 6, 7, 8)
         ORDER BY id DESC
         LIMIT 50

外部查询为这五十个 id 中的每一个检索整行，这应该相对较快。

这里有一点需要注意。

WHERE owner_id BETWEEN 1 AND 8 

对于 MySQL 来说比

更容易满足

WHERE owner_id IN (1, 2, 3, 4, 5, 6, 7, 8)

因为服务器可以对 owner_id 上的索引进行单范围扫描。您可能无法在所有情况下都使用 BETWEEN，但如果可以，请使用。

如果此查询对性能至关重要，您可以尝试在

上创建复合索引

(owner_id, id)

看看它是否大大加快了查询速度。