美文网首页
让关系型数据库查询再飞一会儿

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

作者: AlbertCheng | 来源:发表于2018-11-30 22:18 被阅读0次

    背景

    有一个系统的业务正在膨胀中,某一些报表(报表数据在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连接有这么几个可用参数,这些参数的含义参考附录链接。
    numPartitionspartitionColumnlowerBoundupperBound
    值得注意的是,partitionColumn 必须为数值类型,日期或者时间戳。lowerBoundupperBound必须为数字。在上面的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

    相关文章

      网友评论

          本文标题:让关系型数据库查询再飞一会儿

          本文链接:https://www.haomeiwen.com/subject/phvkcqtx.html