案例描述

微保WeSure是腾讯首家控股的保险平台,它携手国内知名保险公司为用户提供优质的保险服务,让用户可以在微信与QQ这两个国民级的生活服务平台上进行保险购买、查询以及理赔,让保险触手可及。

作为一个保险平台,微保会为投保用户每月初定期统计用户的所有保单及履行情况。这个功能在上线初期,运行比较轻松,但因需要统计所有用户的所有保单,随着时间推移,数据量越来越大,运行时间达到了十多个小时,期间CPU一直处于高位运行。

从抓取的信息以及慢查询分析看,主要由以下SQL导致(因信息敏感问题,这里做了SQL和名称简化处理,但不影响分析。为了减少篇幅,用*代替具体字段,实际不建议写select *):

SELECT  a.*,b.* FROM order_table a,relation_table bWHERE a.uid=b.uidORDER BY  a.uid,a.oid limit xxx,50000;相关表的索引如下:

order_table

PRIMARY KEY (`uid`,`oid`),KEY `idx_create_time` (`create_time`)relation_table

PRIMARY KEY (`id`),KEY `idx_uid_oid` (`uid`,`oid`)从上述信息可以看到,SQL虽然有表关联,但关联列有合适的索引,从执行计划中也确认索引并非导致性能问题的主因。

从SQL语义看,这个SQL的目的是分页取数据,每次取5万行记录;跟开发同学确认,这个需求是需要取出所有用户的订单数据进行分析,因数据量较大,一次取出来内存存不下,因此进行分批取数据,因此这个问题可以看做是一个典型的分页查询问题。

原因分析

这个SQL是使用limit OFFSET,PAGE_SIZE方式分页,这种方式分页是最简单的实现方式,也是性能最差的方式。

使用OFFSET方式进行分页,一般情况下取前面页数会比较快,但越往后越慢,原因是读取后面页数时需要先做排序(有时排序可省),扫描前面P页的数据,然后再丢弃P-1页的数据,只保留所需页的数据。这种方式下,取后面的页的一页数据基本上需要扫描完全表数据,做大量的无用功。如果扫描用到的索引不是主键,那效率会比全表扫描还要慢很多。

我们简化一下模型,假设需要分页读取表的所有数据,如果表的记录数为N,每一页要取的记录数为S,则直接使用OFFSET方法要扫描的总记录数约为(忽略最后一页取不满的影响):

S * (1 + 2 + 3 + …… + N/S) = N(N/S+1)/2 = N^2/2S + N/2

按照这个公式计算,时间复杂度主要受N^2/2S影响,这意味着:

每提高10倍步长(S),总计算量下降为原来的1/10

如假设表数据量有100万行,如果每次取1000行,则需要扫描的记录总数50000万,放大倍数为500倍;如果每次取10000行,则需要扫描的总记录数为5000万,放大倍数为50倍。

但每增加10倍数据量(N),总计算量增加为原来的100倍

如假设每页大小不变保持10000行时时,数据量从100万增加为1000万行,则需要扫描的总记录数为万,总扫描行数增加了100倍,相对全表扫描数据放大倍数为500倍。

在上面的例子中,功能上线一段时间后,数据量增加不到一倍,但是运行时间增加了数倍,原因就跟上面分析一样。

常用分片优化方法

在讲本案例的优化方法前,我们先聊聊一般的数据分片处理优化方法。

简化后的一般分页处理标准SQL是:

select a.* from a [where OTHER_COL_NAME='xxx'][order by ORDER_BY_COLUMNS]limit OFFSET,PAGE_SIZE在这个做法中,条件部分不是关键因素,只是影响了数据的范围,对整体的分页优化方法没有影响。(在实际使用,可以建一个[OTHER_COL_NAME,ORDER_BY_COLUMNS]的联合索引来提升效率,但这个是另外的话题)。

排序部分比较关键,排序用到的字段的数据必须具备唯一性,否则因排序不稳定性,可能会导致不同的页取到相同的数据。 基于以上两点,为了简化描述,本文介绍的优化方法把条件过滤部分去掉,因此,最终我们是基于以下SQL谈对应的优化方法:

select a.* from a [order by ORDER_BY_COLUMNS]limit OFFSET,PAGE_SIZElimit <OFFSET>,<PAGE_SIZE>方式分页慢的主要原因上面已经分析过了,要解决这个问题,本质上就是要控制扫描记录数的放大倍数,让放大倍数接近或者等于1,要做到这个就要摒弃传统的limit OFFSET的方式。

下面就具体谈谈对于分页如何做优化。

首先需要理解,分页其实就是对数据做分片,区别就在于分页一般要求除最后一页外,每一页记录数相同;而一般的数据分片则不一定能保证每一片记录数相同,也就是说,分页是数据分片的特殊场景。

对于实际的应用来说,有时用一般的数据分片就可以满足需求,有时需要用分页才能满足需求。在很多情况,分页需求其实可以退化为普通的数据分片,尤其是后台类服务。

传统的分页应用(如前台需要显示总页数、有详细的每一页列表,可以跳到任意一页)在实施优化上有更多的限制,也更复杂,不在本文的讨论范围内,以后有机会会专门写一个专题来讲这种情况的优化。

本文主要讨论后台服务需要遍历所有满足条件的数据,如何做数据分片处理。

从上面的计算公式可以知道,page size越大,放大倍数越少,要扫描的总行数就越少,当page size等于符合条件的记录数时,就相当于做一个一次性获取数据,放大倍数最小,但是page size过大会导致DB瞬时压力过大(根据经验值,在线上一般每秒扫描记录数超过20万行会导致时耗波动明显),对客户端也会有很大压力(例如客户端不一定有足够的内存来存放数据),一般不建议设定太大的page size。综合考虑个方面影响,建议page size不要超过10万行。

这个方法的思路主要类似于:

select a.* from a t1,(select PK_COL from a where OTHER_COL_NAME='xxx' order by INDEX_COL limit OFFSET,PAGE_SIZE) t2 where t1.PK_COL=t2.PK_COL这种方式是先根据索引分页取到分页对应的主键,再关联原表取数据。基于这种思路还可以用另一种写法:

select a.* from a t1 where t1.PK_COL > (select t2.PK_COL from a t2 where OTHER_COL_NAME='xxx' order by t2.INDEX_COL limit OFFSET,1) limit PAGE_SIZE以上两种写法的时间复杂度和传统的OFFSET分页没有区别,但是都利用了InnoDB的辅助索引特性(辅助索引存储主键值),通过比表数据更小的索引来快速获取数据并避免排序,比起直接limit offset方式要快,但本质还是适用offset的方式取数据,没有质的提升。

需要特别注意的是:本方法只适用于InnoDB引擎。

本案例中,排序字段已经是主键字段,因此这个方法不适用本案例。

探索法不用OFFSET的方式来跳过数据,直接使用where条件来跳过数据;与传统的OFFSET分页不一样,探索法取分片数据,每一个分片where条件都是变化的,在不断探索中取数据,因此叫探索法。这种方法取数据主要步骤如下:

1) 确定排序字段,获取需要排序字段的最大值MAX_VALUE(降序)或最小值MIN_VALUE(升序)

最大或最小值可以基于数据特点去一个极大值或者极小值就可以了,无需真正去用max()/min()获取

因MySQL有排序不稳定性影响,以及分片对唯一性的要求,用于做排序的字段应是唯一的,否则会漏取数据或重复取数据。

如果没有排序字段,或没有要求排序,则建议使用主键作为排序字段

如果对数据有排序要求,则需要把业务排序字段和主键一起作为排序字段(但这样会变得很慢)

2) 获取首页数据

- 倒序SELECT a.* FROM table_name a WHERE a.OTHER_COL_NAME = 'xxx' AND a.ORDER_BY_COLUMNS < MAX_VALUE ORDER BY a.ORDER_BY_COLUMNS desc LIMIT PAGE_SIZE;-升序SELECT a.* FROM table_name a WHERE a.OTHER_COL_NAME = 'xxx' AND a.ORDER_BY_COLUMNS > MIN_VALUE ORDER BY a.ORDER_BY_COLUMNS LIMIT PAGE_SIZE;从上述SQL中获取的结果集中去出最后一行对应的ORDER_BY_COLUMNS值:SEEK_VALUE,作为下一个数据分片获取的起始值。

如果排序字段有null值,则需要使用isnull之类的函数对null值做处理

3) 获取下一页数据

- 倒序SELECT a.* FROM table_name a WHERE a.OTHER_COL_NAME = 'xxx' AND a.ORDER_BY_COLUMNS < SEEK_VALUE ORDER BY a.ORDER_BY_COLUMNS desc LIMIT N;-升序SELECT a.* FROM table_name a WHERE a.OTHER_COL_NAME = 'xxx' AND a.ORDER_BY_COLUMNS > SEEK_VALUE ORDER BY a.ORDER_BY_COLUMNS LIMIT N;从结果集的最后一行中取出下一页的SEEK_VALUE,给下一循环使用。

4) 循环第三步,直接取出来的行数小于N,说明数据已经取完

使用这种方式做数据分片,扫描行数放大倍数为1,性能非常好。

上面三种方法本质上还是分页的优化思路,在特定场景下,可以使用更容易理解,更简单的数据分片法来拉取数据。

数据分片法是基于数据特点做数据分片,这种方法就是根据某一个字段做数据切片,把数据拆分为N个小片,取数据时,每次把某一分片的数据全部取完,无需排序,无需分页,在特定场景下,效率是最高的。

一般分片的方法有:

1)按自增主键ID分片

这种方法根据页数(page number)和每页大小(page size)来计算每一页的ID理论的取值范围,示例SQL如下:

select a.* from a where PK_COL >= (PAGE_NUMBER - 1)>* PAGE_SIZEand PK_COL < (PAGE_NUMBER>* PAGE_SIZE)and OTHER_COL_NAME='xxx' 适用场景:

ID为int/bigint类型递增

中间无大空洞(ID空洞很大的话,最大ID会很大,循环次数会很多,且存在大量的空跑)

应用举例:全表更新

用户表的主键为自增主键,且表很大。现在业务需要更新用户表的一个字段,涉及所有数据

这个表读写比较频繁,需严格控制单次更新规模

假设每次最多允许更新1000条,则更新SQL可以简单实现为:

update t set col='xx' where id between 0 and 1000;

update t set col='xx' where id between 1001 and 2000;

2)按时间分片

按时间对数据集进行切分,示例SQL如:

select a.* from a where a.TIME_COL betewwn 'START_TIME1' and 'END_TIME1';select a.* from a where a.TIME_COL betewwn 'START_TIME2' and 'END_TIME2';   ......适用场景:

有合适的时间字段

时间字段有索引

数据按时间分布比较平滑,不会出现在一小段时间内数据猛增的情况

应用举例:订单下载

需要下载某一个保险公司的某一险种在指定时间段的所有订单

如果选择的时间段比较长,订单量会比较大。下载服务器内存有限,不能一次性下载所有订单放到内存中处理,需要分批处理

假设订单交易比较平滑,可以把这个大的下载任务按天分片进行下载,SQL转换为:

select * from t where t.createtime between '2018-01-01 00:00:00' and '2018-01-01 23:59:59';

select * from t where t.createtime between '2018-01-02 00:00:00' and '2018-01-02 23:59:59';

3)按业务特性分片

适用场景:

使用这种数据分片方式和按时间分片很类似,只不过把字段从时间字段换为业务特征字段。

比较明显的业务区分字段

各业务对应的数据相对均匀

总结一下,数据分片法的优点是:

比较直观,容易理解,实现也简单

无须排序

使用数据分片法,也有一些缺陷:

用于分片的字段应有索引

每一个分片的返回记录数不固定

如果数据分布不均匀,如某一天数据非常多,会导致单个分片数据暴增,达不到分片的效果

不能按指定排序返回数据

以上介绍的几种方法核心优化思路是减少扫描记录数量,尽量避免排序。这些方法各有优缺点,有不同的适用场景和约束,在实际使用中,需要根据实际情况选择最合适的方法。同时,以上几种方法并非非一即二,在某些场景是可以配合使用来解决问题的。最后,以上的介绍方法是基础用法,实际使用是可以基于这些方法做变通的。

本案例优化方案

跟开发聊过后,发现这个功能还有一个特殊需求:业务统计的主要维度是用户,要求相同用户返回的数据要在一个批次中有序返回(一个用户会对应多条数据),如果不能保证这点,需要在业务代码中合并数据做计算,开发量过大,不是最佳方案。

基于这些需求逐个匹配上面的优化方案,在本案例中,page size已经设置为一个5W,已经是比较大的值,不太适合继续增加page size值了。按照主键先分页再关联的方法也不适用本例。

对于后台类取数据的需求来说,分片法一般是可用的,分片可以解决性能问题,那么现在问题的重点变为分片后如何保证相同的用户在一个分片中有序返回。基于这个思路,可以考虑结合上面的探索法和分片法来解决:

通过探索法来高效获取用户ID分片。这里需要对上面提到的探索法做一下优化,因为uid是可重复的,而我们目标是取到不重复的uid,因此需要对返回的uid做排重

基于获取到的用户ID分片来获取订单的分片数据

先看如何使用探索法对uid进行分片。这里建议的方法如下:

如果是第一次,则使用'0000'作为起始的_uid:

select distinct uid from order_table where uid>'0000' order by uid  limit 49999,1;如果不是第一次,则uid起始值取上一批次获取到的uid

select distinct uid from order_table where uid>'xxxxx' order by uid  limit 49999,1;如果返回空,说明uid已经遍历完了。

大概每5W个uid作为一个分片,得到一个范围区间,最终得到的区间列表如下: [uid1,uid2] [uid2,uid3] … [uidN,uid_MAX]

获取明细数据的SQL就可以基于这个uid做分片,原SQL可以变为:

SELECT  a.*,b.* FROM order_table a,relation_table bWHERE a.uid=b.uidAND a.uid>='start_uid' AND a.uid<='stop_uid'order by a.uid,a.oid;这里最大的变化是去掉了offset和limit,并且使用uid>='startuid' 和uid<='stopuid'来限制单个分片数据扫描的范围。

最终使用这个方法优化完之后,整个统计程序的运行时间在数据量增加50%情况下,运行时间从10多个小时下降到4个多小时(在这个数据量下,原方案预期会运行几十个小时),性能提升10倍以上,且运行时间跟数据量呈线性增长,而不是接近指数的增长。

BTW: 整个统计程序包含若干个步骤,这里只优化了其中取数据的步骤,如果只对比取数这个步骤,从业务的实际数据量和取数步长测算,性能提升会有200倍左右。在DB处理性能足够的情况下,还可以基于分片结果做并发处理,进一步提升效率。

本文从遇到的一个实际案例着手,分析了常用的数据分片的解决方法以及其适用场景,并最终灵活使用这些方法解决问题。

对于后台类遍历取数的分页需求,大部分情况都可以变为普通的数据分片,使用上述介绍的方法,一般都可以比较容易解决。 对于前台页面的分页需求,一般很难用普通的分片思路解决问题,建议的解决方法是:

只提供上一页/下一页

使用探索法

如果业务必须统计总数、显示所有的页码,那么虽然有优化的方法,但是比较复杂,还需要根据业务场景具体分析和优化。