背景
有一个系统的业务正在膨胀中,某一些报表(报表数据在mysql中)数据量增长比较厉害,报表页面已经处于卡爆了
的状态。中间经过mysql本身的优化,已经到了当前系统架构+存储模型的瓶颈。本文提供一种优化思路,抛砖引玉。
任务分析
以一条sql的优化为例(这条sql里面的字段随便改了改,不保证正确性)。
SELECT d.col, COUNT(DISTINCT risk.inst_id) AS `count`
FROM risk
INNER JOIN d
ON d.inst_id = risk.inst_id
AND d.id = risk.id
INNER JOIN b
ON b.business_key = d.id
AND d.type = b.type
INNER JOIN r
ON risk.inst_id = r.inst_id
AND risk.id = r.id
WHERE (r.visit_time >= '2018-10-27 00:00:00'
AND r.visit_time <= '2018-11-28 15:54:40'
AND d.id = '22821111115042'
AND b.business_key = concat('22821111115042', ''))
GROUP BY d.col
其中,risk表大小112MB,d表大小为9.5GB,b表208KB,r表大小为4.2GB。这个报表的生成逻辑中含有较多inner join。经过一些列的索引优化之后,该条sql的查询时间是36s,前端体验仍然不是很好,且随着报表时间范围的拉长,用户数据量的增长,查询时间会持续恶化。
这里就不讨论更改表结构、迁移数据来优化查询了。
优化思路
本身没有太多技术难度,但中间经过一段时间的摸索,直接说结论吧,希望对有需要的同学带来便利。
用SparkSQL分布式计算的能力来加速查询,SparkSQL原生支持通过jdbc连接外部存储。
首先,尝试了直接在sparksql的jdbc连接中执行上述sql,结果在意料之中,36秒左右。通过spark监控页面看到,该任务task数量为1,没有并发起来,SparkSQL将查询完全下推给mysql执行。
那么问题来了,如何提升并发度呢?
根据官方文档,使用jdbc连接有这么几个可用参数,这些参数的含义参考附录链接。
numPartitions
,partitionColumn
,lowerBound
,upperBound
值得注意的是,partitionColumn
必须为数值类型,日期或者时间戳。lowerBound
和upperBound
必须为数字。在上面的case中,我们可以对r表的visit_time进行分区,并根据范围设置上下界线。(时间戳转化成long型)
分别在SparkSQL load这4张表,其中对r表的visit_time进行分区,并分别在SparkSQL中注册临时表,在SparkSQL内执行上述SQL,上述SQL执行时间由36s降低到12s,如果调调SparkSQL的参数,性能可能会更好。
这个方法从理论上来说,适用于任何单机关系型数据库。
原理简单剖析
这里是将SparkSQL作为一个分布式查询引擎,mysql作为SparkSQL的一种数据源。SparkSQL内部有高度的统一抽象(DataFrame/DataSet)。SparkSQL从mysql中抽取数据然后根据自身的逻辑来进行运算。如果对细节感兴趣可以参考链接2。
参考文档
[1] http://spark.apache.org/docs/latest/sql-data-sources-jdbc.html
[2] http://spark.apache.org/docs/latest/sql-programming-guide.html
网友评论