β

MySQL分页优化

Sean's Notes 62 阅读

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

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

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页的数据:
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大抵有三种方法:

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

利用索引来排序

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

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 要快。

利用覆盖索引避免回表

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

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

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

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

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

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还出现了它的一个变种:

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翻页

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

第一页:(降序)
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条:

最后一页:(降序)
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 场景:

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

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

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

SELECT * FROM t_tel_record
WHERE f_Starttime >= &apos;2018-03-16 08:00:01&apos; AND f_Starttime < &apos;2018-03-16 08:05:01&apos;
ORDER BY f_Starttime DESC

分页优化陷阱

order by id 与 order by f_Starttime

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

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

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

第一页和最后一页非常慢

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

sql1:

- 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>=&apos;2017-10-25 00:00:00&apos; and f_Starttime<=&apos;2017-10-26 00:00:00&apos;) ORDER BY f_id ASC LIMIT 1000

sql2:

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>=&apos;2017-08-01 00:00:00&apos; and f_creat_time<=&apos;2017-08-02 00:00:00&apos;) ORDER BY f_log_id ASC LIMIT 1000

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

我们分析一下sql1慢的原因

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

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

sql2慢的原因有点不一样:

优化办法:

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

参考


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


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

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

CREATE TABLE `t_tel_record` (
`f_id` bigint(20) unsigned NOT NULL DEFAULT &apos;0&apos; COMMENT &apos;流水号&apos;,
`f_qiye_id` bigint(20) NOT NULL DEFAULT &apos;0&apos; COMMENT &apos;企业&apos;,
`f_callno` varchar(20) DEFAULT NULL COMMENT &apos;主叫号码&apos;,
`f_calltono` varchar(30) DEFAULT NULL COMMENT &apos;被叫号码&apos;,
`f_Starttime` datetime NOT NULL COMMENT &apos;开始时间&apos;,
`f_Endtime` datetime DEFAULT NULL COMMENT &apos;结束时间&apos;,
`f_Calltime` mediumint(8) unsigned NOT NULL DEFAULT &apos;0&apos; COMMENT &apos;通话时间&apos;,
`f_user_id` bigint(20) NOT NULL COMMENT &apos;员工用户&apos;,
`f_path` varchar(200) DEFAULT NULL COMMENT &apos;语音文件路径&apos;,
`f_crm_id` bigint(20) NOT NULL DEFAULT &apos;0&apos; COMMENT &apos;客户库id&apos;,
`f_call_type` tinyint(4) unsigned NOT NULL DEFAULT &apos;0&apos; COMMENT &apos;0:未知,1:为呼入类型,2:呼出类型&apos;,
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页的数据:
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大抵有三种方法:

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

利用索引来排序

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

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 要快。

作者:Sean's Notes
小样儿の部落阁
原文地址:MySQL分页优化, 感谢原作者分享。

发表评论