我有posts有 10k 行的表，我想通过它创建分页。因此，我为此目的有下一个查询：
SELECT post_id
FROM posts
LIMIT 0, 10;
When I Explain该查询我得到下一个结果：
所以我不明白为什么 MySql 需要迭代 9976 行才能找到前 10 行？如果有人帮助我优化这个查询，我将非常感激。
我也知道这个话题MySQL ORDER BY / LIMIT 性能：后期行查找 http://explainextended.com/2009/10/23/mysql-order-by-limit-performance-late-row-lookups/，但即使我将查询修改为下一个，问题仍然存在：
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
Update
@pala_ 的解决方案非常适合上述简单的情况，但现在当我正在测试更复杂的查询时inner join。我的目的是将评论表与帖子表连接起来，不幸的是，当我解释新查询时，它仍然迭代 9976 行。
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 出现这种行为的原因是什么吗？
尝试这个：
SELECT post_id
FROM posts
ORDER BY post_id DESC
LIMIT 0, 10;
分页通过LIMIT无论如何，如果不订购就没有多大意义，它应该可以解决您的问题。
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)？根据我的测试数据，我得到以下结果：
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)
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)
