SQL提高效率:限制FILESORT的数量

Question

我将用这样的查询来解释一下：(post_id=主，blog_id=index )

SELECT post_id FROM posts WHERE blog_id IN (2,3,...) ORDER BY post_id DESC LIMIT 10

更新: IN()中的ids可能很多。如果DB使用blog_id作为查询的键，它必须进行文件排序，因为索引将如下所示：

(blog_id,post_id)-> (1,55) (1,59) (1,69) (2,57) (2,71) (2,72) (3,12)

如果您只搜索一个id blog_id = 2而不是IN()，则不需要执行任何文件排序，因为所有匹配都已经按顺序进行了。

问题是，我认为它正在发生，不是100%确定，只是通过查看查询执行时间，如果我添加一个限制10，有效的方法是只捕获和文件排序每个blog_id索引键匹配的最后10个IN，也许它已经这样做了，但看起来像IN (2,3,4) ORDER BY post_id DESC LIMIT 10，它将文件排序数千个IN而不是30个。

我希望我是大错特错的，因为如果我错了，那将是一个可怕的低效错误。如果我是对的，有没有什么我可以做的引擎或改变？甚至可以更改数据库。目前我在10.1.13-MariaDB上，表是InnoDB

Answer 1

不幸的是，MySQL没有可以让你做你想做的事情的索引。

但是，您可以重写已有的查询并使用现有索引：

SELECT p.post_id
FROM ((SELECT post_id
       FROM posts
       WHERE blog_id = 2
       ORDER BY post_id DESC
       LIMIT 10
      ) UNION ALL
      (SELECT post_id
       FROM posts
       WHERE blog_id = 3
       ORDER BY post_id DESC
       LIMIT 10
      )
     ) p
ORDER BY post_id DESC
LIMIT 10;

每个子查询都将使用索引。对20个元素进行排序是相当快的。

Answer 2

看看EXPLAIN SELECT ...；看看它是否显示“文件排序”。

执行以下操作以获取详细信息，即使是小数据集也是如此：

FLUSH STATUS;
SELECT ...;
SHOW SESSION STATUS LIKE 'Handler%';

您确实需要INDEX(blog_id, post_id)。如果您使用的是InnoDB，并且该表具有

PRIMARY KEY(post_id),
INDEX(blog_id)

那么你就有了这个综合指数。这是因为每个二级索引都隐式包含主键的列。

既然您正在使用MariaDB，那么看看是否会做您询问的另一件事。

当优化器看到这一点时：

WHERE blog_id IN (2,3)
ORDER BY post_id DESC LIMIT 10

它同时拥有INDEX(blog_id)和INDEX(post_id)，它根据有限的统计数据做出决定，决定走哪条路：

计划A:根据blog_id +文件排序进行过滤，或者

计划B:按post_id顺序扫描，希望很快找到10行。

任何一个都是有风险的。计划A，如果大多数或所有行都是(2,3)，将有一个大排序。当匹配行少于10行时，计划B将扫描整个表(或索引)。