Albert Cheng's blog

让关系型数据库查询再飞一会儿

发表于 | 分类于 | | 阅读次数

背景

有一个系统的业务正在膨胀中，某一些报表（报表数据在mysql中）数据量增长比较厉害，报表页面已经处于卡爆了的状态。中间经过mysql本身的优化，已经到了当前系统架构+存储模型的瓶颈。本文提供一种优化思路，抛砖引玉。

任务分析

以一条sql的优化为例（这条sql里面的字段随便改了改，不保证正确性）。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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