说说那些MySQL分页里头能让效率蹭蹭往上涨的小技巧和思路

说到MySQL分页，大家最熟悉的就是用LIMIT子句，比如LIMIT 20, 10，意思是跳过前20条，取10条数据，当数据量小的时候，这么用一点问题都没有，但是一旦数据量上来了，比如要翻到第1000页，语句变成LIMIT 10000, 10，MySQL就需要先老老实实地把前10000条数据都找出来扔掉，然后再取10条给你，这个过程就像让你从一本一千页的书里找第1000页的内容，你却非得从第一页开始一页一页地翻,效率自然就低下了。

那怎么让分页效率“蹭蹭往上涨”呢？这里有几个非常实用的小技巧和思路。

第一个技巧，也是最核心的思路：使用“游标分页”或者叫“基于游标的分页”，也有人叫“最后记录分页”。

这个方法彻底抛弃了LIMIT offset, size这种需要计算偏移量的方式，它的核心思想是：不要告诉我你要跳过多条数据，而是告诉我你上次看到哪儿了,我从你上次看完的地方接着往下找。

具体怎么做呢？假设我们有一张文章表，主键是自增的id，我们想按id倒序分页。 第一页的查询很简单：SELECT * FROM articles ORDER BY id DESC LIMIT 10; 我们得到了10条记录，最后一条记录的id是，比如说是 100。

传统分页查第二页会是：SELECT * FROM articles ORDER BY id DESC LIMIT 10, 10; 而游标分页查第二页则是：SELECT * FROM articles WHERE id < 100 ORDER BY id DESC LIMIT 10;

看出区别了吗？第二种写法，利用WHERE id < 100这个条件，直接让MySQL在索引树上定位到了id=100的位置，然后往前（因为倒序）扫描10条就行了，它完全避免了扫描和丢弃前10条数据的巨大开销，无论你翻到多后面，这个查询速度都几乎是一样的,因为它只扫描你需要的那少量数据。

这个方法的关键是排序字段必须是唯一的、且是有序的，通常用主键id是最佳选择，如果按其他字段排序，比如create_time，那么一定要确保这个字段上有索引，并且如果create_time可能重复，就需要结合主键一起排序和查询，ORDER BY create_time DESC, id DESC，然后下一页的查询条件就是WHERE (create_time < '上次最后时间') OR (create_time = '上次最后时间' AND id < 上次最后ID)，这个技巧在各类大型互联网应用（如微博、电商商品列表）中非常常见，是应对深度分页的利器。（这个思路在《高性能MySQL》等书籍和高并发架构设计的文章中被反复强调）

第二个技巧，是关于“延迟关联”的优化。

有时候我们的查询比较复杂，可能SELECT *并且有很多WHERE条件，即使使用了上面的游标分页，MySQL可能仍然需要回表（根据索引再去主键索引里找完整数据行）很多次，如果WHERE条件过滤不掉太多数据,效率还是不高。

“延迟关联”的思路就是把分页操作和取详细数据的操作分开，先想办法快速地把符合条件的那一页数据的主键ID找出来,然后再根据这些ID去关联回原表取详细数据。

举个例子，一个低效的查询可能是： SELECT * FROM articles WHERE tag = 'mysql' ORDER BY id DESC LIMIT 10000, 10;

用延迟关联可以改写成：

SELECT a.* FROM articles a
INNER JOIN (
    SELECT id FROM articles
    WHERE tag = 'mysql'
    ORDER BY id DESC
    LIMIT 10000, 10
) AS tmp ON a.id = tmp.id;

这个写法的妙处在于，子查询SELECT id FROM articles ...只需要扫描(tag, id)这个联合索引（如果存在的话），因为索引体积小，而且只需要查id这一个字段，所以扫描速度极快，拿到10个目标ID后，再去主键索引里精准地取出10条完整记录，这个“两步走”的策略，往往比直接在大表上扫描、回表要高效得多,这个方法在Percona的官方博客和一些数据库优化案例中经常被提及。

第三个技巧，更像是一种设计思路：预先计算或缓存总数。

很多分页组件需要显示总页数，于是就会在分页查询后面跟一个SELECT COUNT(*)，这个COUNT(*)在没有良好索引的情况下，可能是一个全表扫描,非常耗时。

对于数据量巨大且更新不非常频繁的表,可以考虑：

1. 不显示精确的总数，只显示“上一页”、“下一页”，或者像谷歌搜索那样显示一个估算值，这是一种用户体验上的妥协,但能换来巨大的性能提升。
2. 使用单独的计数表：用一个很小的表来实时维护总数，通过触发器或者在业务代码中，在增删记录时同步更新这个计数表，这样查询总数就变成了一个简单的SELECT,几乎没有开销。
3. 使用Redis等缓存：将总数或热门查询条件下的总数缓存起来,设置一个短暂的过期时间。

第四个技巧，是结合业务进行“边界化”处理。

这需要和产品经理沟通，直接跳到第10000页的需求真的合理吗？通常用户只会耐心地看前几十页，可以人为设定一个最大翻页深度，比如只允许翻到100页，这样，我们就可以在业务逻辑层或者SQL层面（比如WHERE offset < 1000）进行限制,从根本上杜绝了深度分页查询的发生。

别忘了所有优化的基石：索引。

无论用哪种分页技巧，合适的索引都是前提，你的ORDER BY字段和WHERE条件字段，必须要有合适的索引支持，如果排序字段没有索引，MySQL就只能做全表扫描后使用临时表和文件排序，再好的分页技巧也无力回天,一定要为你的分页查询语句创建必要的联合索引。

让MySQL分页效率飙升，核心就是四件事：*一是用游标分页代替偏移量分页，避免扫描丢弃大量数据；二是用延迟关联减少回表开销；三是巧妙处理总数查询，避免`COUNT()`的坑；四是结合业务限制翻页深度，从源头解决问题。** 所有这些技巧都建立在正确使用索引的基础之上。