如何改进 MySQL 子查询中的 LIMIT 子句?

2022-01-23 00:00:00 performance sql limit subquery mysql

我有两个表:posts 有 10k 行和 comments，我需要为特定数量的 posts 选择所有 comments 也就是通过posts 表实现分页，并获取其中的所有comments.为此，我有下一个查询:


I have two tables : posts with 10k rows and comments and I need to select all comments for particular numbers of posts in other words implement the pagination by posts table and get all comments thereof. For that purpose I have the next query:
select * from  comments c 
inner join (select post_id from posts o order by post_id  limit 0, 10) p 
on c.post_id = p.post_id;

查询的性能对我来说也很重要.但是这个查询的 Explain 很奇怪，因为 LIMIT 子句遍历 9976 行 而不是像我预期的那样遍历 10 行:

Also it is very important for me the performance of query. But the Explain of this query is very strange because LIMIT clause iterate through 9976 rows but not through 10 rows as I expect:

同时，当我单独运行子查询时，它可以很好地按预期遍历 10 行:

At the same time when I run subquery separately it works great with iterating through  10 rows as expected:
explain select post_id from posts o order by post_id  limit 0, 10


posts(post_id)、comments(comment_id)、comments(post_id) 上还有 indexes.我不明白该查询有什么问题，因此它遍历了帖子表中的所有记录.如果有人帮助我解决这个问题，我将非常感激.

Also there is indexes on posts(post_id), comments(comment_id), comments(post_id). 
I don't understand what is the problem with that query so it iterate through all records in posts table. I will be very thankful if somebody help me with that issue.
推荐答案
9976 (vs 10000) 已经是一个改进——在 5.6 之前，行"通常会减少多达 2 倍.现在统计数据是更准确，更稳定.

9976 (vs 10000) is already an improvement -- before 5.6, "Rows" was often off by as much as a factor of 2.  Now the statistics are more accurate, and more stable.
真正的答案是EXPLAIN 并不完美."

The real answer is "EXPLAIN is less than perfect."
5.7 将有一些的改进.与此同时，我们还被10 vs 9976"之类的谜团所困.

5.7 will have some improvements.  Meanwhile, we are stuck with mysteries like "10 vs 9976".
当使用 LIMIT 时，它大多被破坏.它以另一种方式体现在 EXPLAIN EXTENDED 的已过滤"列中.

It is mostly broken when LIMIT is used.  It manifests in another way in the "Filtered" column of EXPLAIN EXTENDED.
试试 EXPLAIN FORMAT=JSON ... 以获得更多信息.

Try out EXPLAIN FORMAT=JSON ... to get a little more information.
使用 MariaDB(版本 10.0?)，有 ANALYZE SELECT ... 可以为您提供实际计数.它通过运行查询，然后抛出结果集并保留统计信息来做到这一点.

With MariaDB (version 10.0?), there is ANALYZE SELECT ... which will give you actual counts.  It does this by running the query, then tossing the resultset and keeping the statistics.


	
		相关文章