本文正在参加「技术专题19期 漫谈数据库技术」活动
做WEB项目的时候，最长做的功能就是列表展示，做列表展示的时候，做的最基础的功能就是数据分页，毕竟将所有数据一次性展示在一个页面上不论是基于性能还是用户体验都是不合理的，分页好做但是如果数据量大的话就不好搞了，所以今天聊聊基于MySQL更加高效的实现数据分页的方法。
举例说明
首先，先聊一下最基本的分页sql,以一个新闻列表页为例:
| 字段 | 描述 |
|---|---|
| id | 文章编号 |
| title | 文章标题 |
| author | 文章作者 |
| publish_time | 出版时间 |
| summary | 文章描述 |
| content | 文章内容 |
mysql> select count(*) from news;
+----------+
| count(*) |
+----------+
| 9999999 |
+----------+
1 row in set (4.75 sec)
如果数据不多的情况下，我们可以直接使用limit（每页10条）:
mysql> select title,author,publish_time,summary from news limit 50,10;
+--------------+--------------+--------------------+------------------+
| title | author | publish_time | summary |
+--------------+--------------+--------------------+------------------+
| 标题_0000050 | 作者_0000050 | 1932-7-14 11:51:6 | 文章描述_0000050 |
.......
+--------------+--------------+--------------------+------------------+
10 rows in set (0.04 sec)
但是如果数据量是1亿，并且访问到后面的页面，比如第9999990页，那么这个时候为了10条数据需要便利前面的100000条数据，就有点冗余了，并且查询的速度会下来。
mysql> select title,author,publish_time,summary from news limit 9999990,10;
+--------------+--------------+--------------------+------------------+
| title | author | publish_time | summary |
+--------------+--------------+--------------------+------------------+
| 标题_0100000 | 作者_0100000 | 1208-8-7 19:55:7 | 文章描述_0100000 |
.......
10 rows in set (5.36 sec)
仔细琢磨，发现有两个可以优化的思路：
1、添加索引，加快搜索效率。
2、减少冗余数据遍历尤其是全局遍历的可能性。
所以可以这样试试：
select news.id,news.title,news.author,news.publish_time,news.summary from news inner join (
select id from news limit 9999990,10
) as lim using(id);
+--------------+--------------+--------------------+------------------+
| title | author | publish_time | summary |
+--------------+--------------+--------------------+------------------+
| 标题_0100000 | 作者_0100000 | 1208-8-7 19:55:7 | 文章描述_0100000 |
.......
10 rows in set (4.66 sec)
因为id设置有索引，所以先按照索引限定好范围，再基于这个访问去检索数据，这样会好很多。
当然如果可以确定id是连续的，那么可以使用范围限定来取代limit，也会好很多。
select title,author,publish_time,summary from news where id between 999980 and 999990;
+----------------+----------------+---------------------+--------------------+
| title | author | publish_time | summary |
+----------------+----------------+---------------------+--------------------+
| 标题_000999980 | 作者_000999980 | 1048-9-27 20:52:16 | 文章描述_000999980 |
............
| 标题_000999990 | 作者_000999990 | 1181-1-6 18:27:15 | 文章描述_000999990 |
+----------------+----------------+---------------------+--------------------+
11 rows in set (0.04 sec)
如果仔细看limit语句，限制查询速度的部分不是limit部分，而是再offset偏移量部分，所以，如果能解决offset遍历冗余数据问题也是可以的，比如检索最老的10条新闻：
select title,author,publish_time,summary from news limit 10;
#代替
select title,author,publish_time,summary from news order by publish_time desc limit 9999990,10;
而最新出现的10条新闻可以是
select title,author,publish_time,summary from news order by publish_time desc limit 10;
#代替
select title,author,publish_time,summary from news limit 99999990,10;
所以，如果遇到数据量比较大的查询的时候，尽可能的缩小查询范围，尤其是利于索引列，然后再进行查询应该可以提升查询的效率。
本文正在参加「技术专题19期 漫谈数据库技术」活动
