MySQL里分页怎么搞，感觉总有点绕不过去的那些坑和技巧

MySQL官方文档、高性能MySQL、阿里巴巴Java开发手册、众多技术社区如Stack Overflow和CSDN上的实践经验总结）

MySQL里分页查询,最常用的就是LIMIT关键字，看起来简单，但用不好确实会踩一堆坑，感觉怎么优化都不得劲，咱们就直接说重点，把这些“绕不过去”的东西讲明白。

第一个大坑：深度分页的性能悬崖

这大概是所有人最先碰到的,也是最头疼的问题，你的SQL可能这么写：SELECT * FROM articles ORDER BY id DESC LIMIT 1000000, 20;，意思是跳过100万条记录，取后面的20条，当数据量小的时候，没问题，但一旦偏移量（OFFSET）大到像100万这种级别，MySQL的麻烦就来了。

（来源：高性能MySQL）它的执行逻辑很“傻”：它真的会先读取1000000 + 20条完整的记录，然后在服务器内部把这前100万条扔掉，只返回最后20条，这“读取并丢弃”100万条记录的过程，消耗了大量的CPU和I/O资源，这就是为什么当页码越点越靠后时，页面加载速度会呈断崖式下降，数据库服务器压力巨大。

那怎么解决这个“深度分页”的坑呢？技巧来了：使用“游标分页”或者叫“seek method”。

（来源：技术社区广泛流传的优化方案）不要用LIMIT offset, size，而是利用索引的有序性，你的表主键是自增ID，要查“下一页”，你可以记住当前页最后一条记录的ID，假设上一页最后一条的ID是500，那么查询下一页的SQL可以写成： SELECT * FROM articles WHERE id > 500 ORDER BY id ASC LIMIT 20; 这样一来，MySQL直接通过主键索引定位到ID>500的位置，然后往后读20条就完事了，根本不需要知道前面到底有多少条数据被跳过了，效率极高。

这种方法的局限性是只能一页一页地连续翻,不能直接跳到第N页（比如从第1页直接跳到第50页），但在移动端App的瀑布流，或者“加载更多”的场景下，这是最佳实践。

*第二个常见的困惑：总数COUNT()到底要不要查？**

分页通常伴随着计算总记录数,好让前端显示总页数。SELECT COUNT(*) FROM table_name这个操作，在MyISAM引擎下很快，因为引擎内部有缓存，但现在基本都是InnoDB引擎了，InnoDB必须实时计算，在没有WHERE条件时还算快，一旦查询条件复杂，这个COUNT(*)可能会非常慢，因为它可能需要进行全表扫描或索引扫描。

（来源：阿里巴巴Java开发手册及相关实践）很多成熟的互联网应用会做一个取舍：如果数据量真的特别大，比如上亿条，它们会直接放弃精确的总数，取而代之的是：

1. 不显示具体总数，只提供“上一页/下一页”按钮，这就是上面游标分页的配套方案。
2. 做一个估算。SHOW TABLE STATUS语句或者查询information_schema数据库中的表，可以快速得到一个估算的行数，虽然不精确，但对用户来说感知不强。
3. 用缓存，如果业务对实时性要求不高，可以把总数缓存起来，比如每隔几分钟更新一次。

第三个技巧：ORDER BY和索引的配合

（来源：MySQL官方文档关于索引的说明）分页查询的效率，极度依赖于ORDER BY子句是否能用到索引，如果你的排序字段没有合适的索引，MySQL就不得不进行“文件排序”（filesort），这是一个非常耗时的内存或磁盘操作，即使你用了LIMIT，这个文件排序的过程也通常无法避免，它需要先把所有符合条件的数据排序好，然后再应用LIMIT。

一个关键技巧是：为分页查询中常用的ORDER BY字段组合建立索引，比如你总是按create_time DESC排序，那么给create_time字段加个索引，分页速度会有质的提升，如果是多字段排序，比如ORDER BY category_id, create_time DESC，那么建立一个(category_id, create_time)的复合索引效果最好。

第四个容易忽略的点：多表JOIN分页

当你的分页查询需要关联多张表时,情况更复杂。SELECT a.* FROM articles a LEFT JOIN users u ON a.user_id = u.id ORDER BY a.create_time DESC LIMIT 0, 20。

这里有个潜在的坑：如果JOIN操作导致结果集放大（比如一篇博文有多个标签，JOIN后可能一篇变多条），你直接用LIMIT分页，可能会截断本应属于同一篇文章的数据，导致数据不完整。

（来源：技术社区关于JOIN分页的讨论）常见的解决思路是：先分页，再JOIN，也就是先在一个子查询里，通过主键和排序条件完成分页，然后再去关联其他表获取额外信息，SQL可能长这样： SELECT a.*, u.username FROM (SELECT id FROM articles ORDER BY create_time DESC LIMIT 0, 20) AS tmp JOIN articles a ON tmp.id = a.id LEFT JOIN users u ON a.user_id = u.id; 这样能保证分页的核心逻辑是基于主表的主键和排序索引高效完成的，避免了JOIN带来的数据膨胀对分页准确性的影响。

MySQL分页的“绕”主要在于如何避开全量扫描和排序，核心思路就是尽可能地利用索引，记住这几点：

1. 深分页用游标（WHERE id > ?），告别OFFSET。
2. 总数查询要谨慎，非必要不精确COUNT。
3. 排序务必走索引，避免昂贵的filesort。
4. 多表关联先分页，保证数据不错乱。

把这些点琢磨透了,MySQL分页那点事儿基本就绕明白了。