如何改善MySQL中的Limit子句 [英] How to improve Limit clause in MySQL

问题描述

我有1万行的posts表，我想以此创建分页.为此，我要进行下一个查询:

I have the posts table with 10k rows and I want to create pagination by that. So I have the next query for that purpose:

SELECT post_id
    FROM posts
    LIMIT 0, 10;

当我Explain该查询时，我得到下一个结果:

When I Explain that query I get the next result:

所以我不明白为什么MySql需要遍历9976行才能找到前10个行?如果有人帮助我优化此查询，我将非常感谢.

So I don't understand why MySql need to iterate thru 9976 rows for finding the 10 first rows? I will be very thankful if somebody help me to optimize this query.

我也了解该主题

Also I know about that topic MySQL ORDER BY / LIMIT performance: late row lookups, but the problem still exist even if I modify the query to the next one:

SELECT  t.post_id
FROM    (
        SELECT  post_id
        FROM    posts
        ORDER BY
                post_id
        LIMIT 0, 10
        ) q 
JOIN    posts t 
ON      q.post_id = t.post_id

更新

@pala_的解决方案非常适合上述简单情况，但是现在，当我使用inner join测试更复杂的查询时.我的目的是将评论表与发布表连接起来，不幸的是，当我解释新查询仍然要遍历9976行时.

@pala_'s solution works great for above simple case but now while I am testing a more complex query with inner join. My purpose is to join comment table with post table and unfortunately when I Explain new query is still iterate through 9976 rows.

Select comm.comment_id 
from comments as comm 
    inner join (
        SELECT post_id 
        FROM posts 
        ORDER BY post_id 
        LIMIT 0, 10
    ) as paged_post on comm.post_id = paged_post.post_id;  

您知道这种MySQL行为的原因是什么吗?

Do you have some idea what is the reason of such MySQL behavior ?

推荐答案

尝试一下:

SELECT post_id
    FROM posts
    ORDER BY post_id DESC
    LIMIT 0, 10;

通过LIMIT进行分页没有任何意义就没有意义，它应该可以解决您的问题.

Pagination via LIMIT doesn't make much sense without ordering anyway, and it should fix your problem.

mysql> explain select * from foo;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | foo   | index | NULL          | PRIMARY | 4       | NULL |   20 | Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select * from foo limit 0, 10;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | foo   | index | NULL          | PRIMARY | 4       | NULL |   20 | Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select * from foo order by id desc limit 0, 10;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | foo   | index | NULL          | PRIMARY | 4       | NULL |   10 | Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)

关于您对评论加入的最后评论.您在comment(post_id)上有索引吗?通过测试数据，我得到了以下结果:

Regarding your last comments about the comment join. Do you have an index on comment(post_id)? with my test data I'm getting the following results:

mysql> alter table comments add index pi (post_id);
Query OK, 0 rows affected (0.15 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select c.id from  comments c inner join (select id from posts o order by id  limit 0, 10) p on c.post_id = p.id;
+----+-------------+------------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table      | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+------------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | PRIMARY     | <derived2> | ALL   | NULL          | NULL    | NULL    | NULL |   10 |                          |
|  1 | PRIMARY     | c          | ref   | pi            | pi      | 5       | p.id |    4 | Using where; Using index |
|  2 | DERIVED     | o          | index | NULL          | PRIMARY | 4       | NULL |   10 | Using index              |
+----+-------------+------------+-------+---------------+---------+---------+------+------+--------------------------+

并参考表大小:

mysql> select count(*) from posts;
+----------+
| count(*) |
+----------+
|    15021 |
+----------+
1 row in set (0.01 sec)

mysql> select count(*) from comments;
+----------+
| count(*) |
+----------+
|     1000 |
+----------+
1 row in set (0.00 sec)

这篇关于如何改善MySQL中的Limit子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！