本文介绍了如何改善MySQL子查询中的LIMIT子句?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我有两个表:具有10k行和commentsposts,我需要为posts的特定编号选择所有comments,换句话说,通过posts表实现分页并获取所有.为此,我有下一个查询:

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 rows而不是像我期望的那样遍历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 ...以获得更多信息.

对于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.

这篇关于如何改善MySQL子查询中的LIMIT子句?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-26 08:12
查看更多