问题:
在常规项目中最常规的需求就是分页查询。通常数据量较小的时候不会太大问题。但是本次项目涉及到存量客户1500万,分页查询需要30秒才能出结果,还不包括count的时间。
物理环境:单台Oracle,CPU不详,内存不详
执行分页查询:30秒。
执行SQL:
SELECT*
FROM(SELECT t.*, rownum AS rowNo
FROM (SELECT tab.CUST_TYPE AS custType,
tab.CERT_TYPE AS certType,
tab.ECIF_ID AS ecifId,
tab.CUST_NAME AS custName,
tab.CERT_NO AS certNo,
tab.CUST_ID AS custId,
tab.ID AS id
FROM (SELECT /*+PARALLEL(T_CUST_XXXXXX
4) */ CUST_TYPE,
CERT_TYPE,
ECIF_ID,
CUST_NAME,
CREATE_TIME,
CERT_NO,
CUST_ID,
ID
FROM T_CUST_XXXXXX
WHERE (1 = 1 AND DEL_FLAG ='0' AND instr(comcode_path, '/00000000') = 1)
OR ECIF_ID IS NULL
ORDER BY CREATE_TIME DESC)tab
WHERE 1 = 1) t)
WHERErowNo > 0
AND rowNo <= 10
指定PARALLEL没有太大效果。
解决方法:
1、HASH 分区:使用ID进行HASH分区,没有性能提升。(是否真的无效待后续再研究了,个人对Oracle不熟)
2、自行实现分片逻辑。
由于这个查询功能还算简单,只是对这张总表的多字段组合查询,正好可以符合自行进行数据分片(横向拆表)。
解决方案:
客户信息总表数据含有1500万数据量,分页查询性能为30秒。为了最大的提升分页查询性能,最好的方式就是采用MPP数据库或者分库分表。但是这两种方式在当前项目都不具备,最简单的方式就剩下自行实现数据分片(数据横向切分)。
数据分片的规则采用 ID(长整型) MOD分片数。要实现的功能就需要包括数据查询和数据插入。
优化后:
查询时间:4秒内(Count+查询)
数据查询:
执行数据查询时分为三步:
对所有分片执行并行查询Count检索,获取每个分片的Count结果(查询线程数与数据分片数相等,这个是示例是10);
根据各分片存在的结果数和分页查询的查询参数(查询页码和分页数),计算出 本次分页查询需要查询分片表名和各分片表内的起始RowNo;
根据计算得出的需要查询分片表名和各分片表内的起始RowNo,生成对应的SQL并执行查询。
查询SQL生成:
数据插入:
执行数据执行插入时分为三步:
将数据插入到总表中;
获取插入的全局ID,将全局ID进行MOD10获取需要插入数据指定的分片表名;
将数据插入到指定的分片表中;
*注:保留数据总表,会造成一定的存储浪费(全表30G)。但是带来的优点就是离线处理比较简单。
其他处理方式:
不再保留数据总表,使用一个全局序列,插入数据前线获取全局序列,执行MOD计算出分片表名后直接插入到对应的分片表