深度分页的优化

深度分页的优化

我们日常会使用MySQL的limit做分页，但是大部分人却很少会留意其工作原理。在数据量日益增长的情况下，深度分页最终会造成慢查询。那么，数据量大的情况下真的不能分页查询了吗？

limit的怎么返回数据的？

对于limit 1000, 10大家会不会觉得只是跳过100页找到101页的数据呢？如果你这么想就太过简单了，MySQL这种情况下需要先扫描前1000行记录，这是需要时间的。打个比方，你如果要找到全校综合成绩降序排列的第101-200名同学，你能快速知道谁是第100名吗？我们可以快速知道谁考了620分，因为我们会在分数这一列建索引，但是我们不能快速知道谁是第100名，因为这不是显式的一列，我们只有从高往低数，数到100时就找到了第100名的同学。

回到MySQL这个例子，现在要数1000行记录找到第1000行记录，这样才知道第1001行记录是哪一条。假如再继续深度分页，每次深层查询都会给MySQL带来很大的压力。

优化1——使用覆盖索引扫描

首先我们需要知道返回1000行并不可怕，如果需要该查就查。但是limit深度分页的可怕之处关键在于这1000行返回的信息都是无用的，这种浪费是不能接受的。那么，我们一个最直观的想法就应该是减少浪费，就算还是会丢弃1000行，查看所有列的信息后丢弃（SELECT *)要比查看主键后丢弃(SELECT id)糟糕得多。而且查看主键(SELECT id)往往可以利用覆盖索引不用回表，这样就能显著提高效率。举个例子：

SELECT * FROM 
t1 JOIN
(
    SELECT id FROM t1 ORDER BY score LIMIT 1000, 10
) t2
ON t1.id = t2.id;

优化2——禁止跨页

解决浪费的最好办法是不要浪费。有没有办法我们能够避免扫描那些无用的行呢？当然有，只不过我们需要做一些取舍。假如我们只需要知道下一页的数据，那么下一页的起点就是当前页的终点。比如我们还是要找到全校成绩排序101-200的同学，我们首先是得到排名1-100的同学：

SELECT * FROM student ORDER BY score DESC LIMIT 100;

我们记录第100名同学的分数，假设为633，则要找到排名第101-200的同学很简单：

SELECT * FROM student WHERE score > 633 ORDER BY score DESC LIMIT 100;

相当于我们在score对应的二级索引B+树找到633对应的叶子节点，然后只需要顺着指针往后数100条就行，效率很高且不会扫描无用的行。该优化手段虽然只能回顾上一页、下一页，但是许多时候用户也只是关心开头那几页（比如查看评论区），而且APP端的上拉/下拉刷新页面也同样无需跨页，所以这种分页查询优化还是十分常见的。

优化3——预先存储排名

最后如果我们对分页和排序的要求非常多，性能也不能差，那么单独做一个排名列就可以满足所有要求。我们在排名列建立索引，而且我们预先知道某一页对应的排名范围，就可以利用MySQL范围查询直接得到结果了。比如我们在student表里建立一列“分数排名”，则找到第101-200的同学如下：

SELECT * FROM student WHERE score_rank BETWEEN 101 AND 200 ORDER BY score DESC;

显然，这个优化最大的弊端就是还要维护一列，相当于以空间换时间。另外，如果后插入/更新的数据行会影响已有行的排名，那么每次插入/更新一行需要更新所有行该列的值，代价还是非常大的。当然我们还可以再优化一下，把主键id和需要排序的列单独存储到一张表，这样分页查询的时候通过join关联起来，这样可以避免频繁更新影响主表的性能。

---

如今互联网上各类文章满天飞，但是大部分要不是寥寥数语，让人过目即忘；要不是过多细枝末节又没有实操，让人不知所云。我将从个人学习和工作经历出发，给大家带来深入浅出的技术解析。我的文章力求简短精悍，尽量结合实战，以便大家在碎片时间即可充分吸收，后续还能学以致用。

欢迎大家关注我的微信公众号，所有文章第一时间更新~