关于数据库分页查询的话题，网上谈论的很多，但开发人员在使用上还是习惯以往的思路。

比如我们有个电话记录表：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
CREATE TABLE `t_tel_record` (
  `f_id` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '流水号',
  `f_qiye_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '企业',
  `f_callno` varchar(20) DEFAULT NULL COMMENT '主叫号码',
  `f_calltono` varchar(30) DEFAULT NULL COMMENT '被叫号码',
  `f_Starttime` datetime NOT NULL COMMENT '开始时间',
  `f_Endtime` datetime DEFAULT NULL COMMENT '结束时间',
  `f_Calltime` mediumint(8) unsigned NOT NULL DEFAULT '0' COMMENT '通话时间',
  `f_user_id` bigint(20) NOT NULL COMMENT '员工用户',
  `f_path` varchar(200) DEFAULT NULL COMMENT '语音文件路径',
  `f_crm_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '客户库id',
  `f_call_type` tinyint(4) unsigned NOT NULL DEFAULT '0' COMMENT '0:未知，1:为呼入类型，2:呼出类型',
  PRIMARY KEY (`f_id`),
  KEY `idx_endtime_userid` (`f_Endtime`,`f_user_id`,`f_qiye_id`),
  KEY `idx_crmid` (`f_crm_id`),
  KEY `idx_qiye_user_calltime` (`f_qiye_id`,`f_Starttime`),
  KEY `idx_calltono` (`f_calltono`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
查询第1页的数据：
 SELECT * FROM t_tel_record
 WHERE f_qiye_id=xxx
 ORDER BY f_Starttime DESC
 LIMIT 0,100
 
 
当数据量很大，需要查询第10000页的数据：
 SELECT * FROM t_tel_record
 WHERE f_qiye_id=xxx
 ORDER BY f_Starttime DESC
 LIMIT 999900,100  -- 或者 OFFSET 999900 LIMIT 100

MySQL的 limit m,n 工作原理就是先读取符合where条件的前面m+n条记录，然后抛弃前m条，返回后面n条，所以m越大，偏移量越大，性能就越差。这也是大部分ORM框架生成的分页sql。

还有数据不准确的问题产生。

要优化这类sql大抵有三种方法：

利用索引来排序
利用覆盖索引避免回表
想办法去掉大offset

利用索引来排序

这是写sql的基础的优化手段，利用二级索引的有序性，避免filesort。考虑索引 KEY a_b_c (a, b, c) :

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
ORDER may get resolved using Index
    – ORDER BY a
    – ORDER BY a,b
    – ORDER BY a, b, c
    – ORDER BY a DESC, b DESC, c DESC
     
WHERE and ORDER both resolved using index:
    – WHERE a = const ORDER BY b, c
    – WHERE a = const AND b = const ORDER BY c
    – WHERE a = const ORDER BY b, c
    – WHERE a = const AND b > const ORDER BY b, c
 
ORDER will not get resolved uisng index (file sort)
    – ORDER BY a ASC, b DESC, c DESC /* mixed sort direction */
    – WHERE g = const ORDER BY b, c /* a prefix is missing */
    – WHERE a = const ORDER BY c /* b is missing */
    – WHERE a = const ORDER BY a, d /* d is not part of index */

当然不是说利用索引排序性能就一定好，由于MySQL优化器的局限性，也会出现选择选择糟糕的index scan执行计划，见 MySQL order by limit 走错索引(range->indexscan) ，using filesort也有可能比 index scan 要快。

利用覆盖索引避免回表

我们先来理解一下回表的概念：

MySQL是一个B+Tree结构的聚集索引组织表
每一行记录都有个rowid，要么是主键，要么是非空唯一索引，要么是内部分配的ROWID
二级索引是在表的一个或多个字段联合起来，创建的用于快速检索数据行的“字典”，这个有序的字典结构也是B+Tree
每个二级索引元组的结构后面，都会自带存储相应行记录的rowid，以便定位数据物理位置
如果一个查询采用的索引上，包含了 select 之后所需要返回的列，那么MySQL可直接从索引上返回数据；如果select 要返回的字段只要有一行没在索引中，则需要根据索引对应的rowid，进行回表获取数据。这部分数据有可能在内存，也有可能在磁盘。

当然实际情况要比上面的复杂，比如MySQL内部有ICP和MRR、BKA优化访问的手段，覆盖索引也就不需要使用了。

如果你的分页SQL where条件和select返回列刚好都在同一个索引上，那在这一部分讲的方法没什么好优化的了。由此也应该可以得到启发，** select只返回需要的行，不要返回多余的行，禁止select * **，这些开发规范都是有依据的。

利用Covering index优化分页的方法，先用一个子查询把符合条件主键id集合查出来，然后与原表join取出其它列：

1
2
3
4
5
6
7
8
9
SELECT * FROM
 t_tel_record t1
INNER JOIN (
 SELECT f_id
 FROM t_tel_record
 WHERE f_qiye_id = xxx
 ORDER BY f_id DESC
 LIMIT 999900, 100
) t2 ON t1.f_id = t2.f_id

子查询部分利用覆盖索引只返回主键(rowid)，但不是每次都有好运气，原where条件放到子查询就能很快，毕竟它还是需要过滤999900条数据。

上面的sql还出现了它的一个变种：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
min_id = SELECT f_id
FROM t_tel_record
WHERE f_qiye_id = xxx
ORDER BY f_id DESC
LIMIT 999900, 1
 
 
SELECT * FROM
  t_tel_record t1
WHERE f_qiye_id = xxx
AND f_id < {min_id} + 1
ORDER BY f_id DESC
LIMIT 100

第一条语句利用覆盖索引获取到该页最小id(如果是升序就是最大id)

第二条语句利用主键和其它过滤条件，获取该页数据。

上面两种方式都有一定的优化效果，具体还是要看业务本身的复杂度。

无offset翻页

上面的变种已经提供了一个很好的思路：

程序端或者客户端，保留当前页的最小id、最大id（id是主键），这并不是什么难事
降序情况下，每次提取下一页的数据时，f_id < min_id order by f_id desc limit 100; 上一页 f_id > max_id order by f_id desc limit 100

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
第一页：（降序）
SELECT * FROM t_tel_record t1
WHERE f_qiye_id = xxx
ORDER BY f_id DESC LIMIT 100
 
 
获取结果集最大最小id：一般是第一条和最后一条，或者 max_id=max(f_id), min_id=min(f_id)
下一页（如果有）：
SELECT * FROM t_tel_record t1
WHERE f_qiye_id = xxx
AND f_id < {min_id}  -- min_id变量
ORDER BY f_id DESC LIMIT 100
 
 
上一页（如果有）：
SELECT * FROM t_tel_record t1
WHERE f_qiye_id = xxx
AND f_id > {max_id}  -- max_id变量
ORDER BY f_id DESC LIMIT 100

没有第几页之说，更不存在【跳转x页】这种深度分页，只有【上一页】【下一页】，所以用户体验上有差别。这种分页方式，使用f_id过滤数据，而f_id是主键，速度是很快的，性能不会随着页数的增大而变慢。

反转分页

降序分页的时候，如果用户直接点击最后一页，或者上面的第10000页实际就是倒数第2页，那就没有必要取这么大的offset，转换成升序，性能就与正向前几页效率一样高了。

如下图所示，很容易理解。

在几万页的情况下翻到最后一页，用户不太关心最后一页是100条还是99条：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
最后一页：（降序）
SELECT * FROM (
  SELECT * FROM t_tel_record t1
  WHERE f_qiye_id = xxx
  ORDER BY f_id ASC LIMIT 100) AS t
ORDER BY f_id DESC
 
 
倒数第二页：(以此类推)
SELECT * FROM (
  SELECT * FROM t_tel_record t1
  WHERE f_qiye_id = xxx
  ORDER BY f_id ASC LIMIT 100, 100) AS t
ORDER BY f_id DESC

分页不存在大一统的绝对优化方法，有时候需要产品层面来回避技术难题，比如前5页显示页号，便于跳页，实现上用offset；大于5页只能上下翻页，实现上用无offset方法；最后几页使用反转翻页实现：

包括前面所有优化方法，都没有提供记录总数这样的显示，大数据量count对MySQL来说实在不擅长。即使是Google搜索引擎也只提供 约xx条结果 。

抛开数据量谈实现，也就太天真的。

不精确分页

其实再想想 order by xxx limit m,n 场景：

展示列表或搜索结果
内部统计或者导出业务

第2种场景，比如扫描一组数据或者全部数据，业务批量导出数据，并不是严格的分页，换句话讲，开发的目的是将数据分批取出，每批的数据量是不是一样并不重要，甚至顺序也不重要，而在批量取数据的实现反而引入了两个可能限制性能的条件。

比如有个扫描全表统计数据的功能，范围、等值条件比较复杂，无法很好的使用索引（比如范围搜索可能会使索引其它列失效）。下面直接按 f_Starttime 每5分钟切片，可以较好的利用(f_qiye_id,f_Starttime)索引

1
2
3
SELECT * FROM t_tel_record
WHERE f_Starttime >= '2018-03-16 08:00:01' AND f_Starttime < '2018-03-16 08:05:01'
ORDER BY f_Starttime DESC

分页优化陷阱

order by id 与 order by f_Starttime

按照主键与按照二级索引排序，它们对优化器的影响是非常大的。

在order by 主键时，MySQL优化器很“喜欢”直接用主键，而放弃where条件可能具有更好过滤效果（但有filesort）的执行计划。
在order by 二级索引的某个字段时，MySQL优化器表现比较正常，虽然遇到where条件的范围索引容易失去索引排序，但好歹有可能采用覆盖索引。

正因为第1点的影响，所有再某些分页sql优化时，可考虑 order by id 改成具有接近排序效果的其它字段，比如id是自增，时间字段也是增长。

第一页和最后一页非常慢

考虑下面两个sql: （都采用上面的 f_id > max_id 的优化方法）

sql1:

1
2
3
4
- f_Starttime上没有可用索引
SELECT f_qiye_id,f_id,f_money,f_type,f_callno,f_calltono,f_Starttime,f_Endtime, f_Calltime,f_status, f_num,f_result,f_time,f_user_id,f_is400,f_crm_log,f_code, f_path,f_crm_id,f_telbox_id,f_mp3_len, f_in_out_type, f_call_type FROM d_ec_telecom7.t_tel_record_201710
WHERE f_id > 0
  and (f_Starttime>='2017-10-25 00:00:00' and f_Starttime<='2017-10-26 00:00:00') ORDER BY f_id ASC LIMIT 1000

sql2:

1
2
3
SELECT f_log_id,f_crm_id,f_user_id,f_qiye_id,f_creat_time,f_send_time,f_end_time,f_do_type, f_static_time,f_go_web,f_type,f_contact_num,f_share,f_record_type,f_provice,f_city,f_is_addclient, f_is_customer,f_ontime_flag,f_msg_type,f_id,f_style,f_operate_type,f_from,f_sendmsg FROM d_ec_contact.t_crm_contact_at201708
WHERE f_log_id > 3815923707
  and (f_creat_time>='2017-08-01 00:00:00' and f_creat_time<='2017-08-02 00:00:00') ORDER BY f_log_id ASC LIMIT 1000

都是导出性质的sql，开发反应每当扫描开始的第一页（如sql1）和最后一页（如sql2），都变的贼慢，然而中间页的数据拉取都在几十毫秒级别。

我们分析一下sql1慢的原因

f_Starttime上没有索引，执行计划如下：
f_id是个全局id，单调递增。应该说与f_Starttime是保持相同的趋势增长的，因为正是这样决定了中间页都较快
仔细观察f_Starttime条件，是从10月份表中获取 10.25 日全天数据。执行计划几乎就是根据主键全表扫描，因为f_id>0的条件需要扫描10.1，10.2，… 10.24，之后终于在10.25的第一个f_id找到，通过where条件过滤。可想而知有多慢
第一页取出后，拿f_id的最大值，去取第二页，因为f_id与f_Starttime是保持相同趋势增长，所以扫过的所有记录都满足where条件，很快返回（另外主键扫描是顺序IO，取1000是很快的）
最后一页也是同样，当最后一页不满1000条时，f_id已经超出f_Starttime范围右边了，后面虽然已经没有满足f_Starttime条件的记录，但根据主键还是要一扫到底。（要判断行数是否小于1000，如果不判断还会拿最后一页的最大值去查询，返回结果0，无谓的耗时）

优化第一页和优化最后一页的方法其实是类似的。第一页在f_Starttime无索引的情况下，无论如何都是很难提高效率的，除非你用专门的一段代码去猜f_id的起始值。我们这个例子还特殊点，f_id的组成是 timestamp + incr_1 ，根据f_Starttime范围直接就得到 f_id 的近似值作为下界。

同理，最后一页根据f_Starttime得到最大值，转换为 timestamp × 1000000000 + 99999 作为上界。

sql2慢的原因有点不一样：

f_creat_time上是有一个索引 (f_creat_time,f_operate_type) 的，看一下它的执行计划：
像本文前面说的，一个范围条件，再 order by 主键，优化器选择了主键扫描，而非范围扫描再filesort （一天数据量约320万）

优化办法：

比如去7月份表查个最大值
…use index(idx_creattime) … order by f_creat_time ASC

关于分页优化，具体问题具体分析。

** 参考 **

本文链接地址：http://xgknight.com/2018/03/21/mysql-pagination-no-offset/

利用索引来排序#

利用覆盖索引避免回表#

无offset翻页#

反转分页#

不精确分页#

分页优化陷阱#

order by id 与 order by f_Starttime#

第一页和最后一页非常慢#