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

Question

我将用这样的查询来解释自己：(post_id=PRIMARY，blog_id=index)

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

更新：in()中的id可能很多。如果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()，则不需要进行任何文件排序，因为所有匹配项都已按顺序排列。

我认为它正在发生的问题是，如果我添加LIMIT 10，有效的方法是只捕获并对每个blog_id索引键匹配的最后10个id进行文件排序，可能它已经做到了，但对于IN(2，3，4)ORDER by post_id DESC LIMIT 10来说，它会对数千个id而不是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 ...；看看上面是否写着"filesort"。

执行以下操作以获取详细信息，即使是对于小型数据集：

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

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

PRIMARY KEY(post_id),
INDEX(blog_id)

那么你就有了那个综合指数。这是因为每个辅助索引都隐式地包含PK的列。

既然你在使用MariaDB，看看限制行检查是否会做你问过的其他事情。

优化器看到以下情况时：

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

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

计划A：按blog_id+filesort进行筛选，或者
计划B：按post_id顺序扫描，希望很快找到10行。

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