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毫秒。

在运行“解释”时，我看到以下内容：

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条记录(由于输入)，然后对它们进行排序并选择最后的50条。至于我的问题--怎么可能排序15,000条记录比扫描一张1.5米的桌子和搜索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必须对表中的所有列进行洗牌。这个重构中的子查询只是提供了您想要的50个id值。它仍然必须对它们进行排序，但是排序一串整数比排序一堆行要快。

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

外部查询检索这50个ids中的每个ids的整行，这应该比较快。

这里有些值得注意的地方。

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)

看看它是否能大大提高查询速度。