如何改进MySQL中的Limit子句
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了如何改进MySQL中的Limit子句，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含4142字，纯文字阅读大概需要6分钟。
内容图文
我有10k行的帖子表,我想通过它创建分页.所以我为此目的进行了下一个查询：
SELECT post_id
FROM posts
LIMIT 0, 10;
当我解释该查询时,我得到了下一个结果：
所以我不明白为什么MySql需要通过9976行迭代才能找到10个第一行？如果有人帮我优化这个查询,我将非常感激.
我也知道该主题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
更新
@ pala_的解决方案适用于上述简单案例,但现在我正在使用内部联接测试更复杂的查询.我的目的是将评论表与帖子表联系起来,不幸的是,当我解释新查询仍在迭代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)
关于你对评论加入的最后评论.你有评论索引(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)
内容总结
以上是互联网集市为您收集整理的如何改进MySQL中的Limit子句全部内容，希望文章能够帮你解决如何改进MySQL中的Limit子句所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。