跨数据库实例以及复杂聚合查询数据分析方案调研

Jimmy Lee

|Mar 16, 2021|Last edited: 2022-7-22|
type
Post
status
Published
date
Mar 16, 2021
slug
cross-instance-query
summary
当微服务使用不同实例的数据库事,如何跨数据库联合查询统计数据呢?
tags
技术调研
CLICK-HOUSE
category
icon
Update time
Jul 22, 2022 12:59 PM
Internal status
password
文章来源说明

背景

  1. 目前BI需求繁多,且都是些复杂联表查询,很容易产生数据库性能问题,影响线上用户, 目前架构如下,虽然能够满足现在的需求,但面对未来更大的数据量,更加复杂的统计需求,将只能通过添加只读实例和升级配置解决
  1. 用户基础信息与交易信息不在同一个实例,数据分析需要联合表查询得到数据,目前先查用户id再去用户信息表取存在诸多不便,需要寻求更方便的形式
  1. 针对用户行为漏斗分析等场景,MySQL的支持很差,基本查不动,需要更合适的数据库

需求

  1. 支持更复杂的聚合查询,更快的查询速度
  1. 不影响用户体验
  1. 更少的成本

方案

方案A:metabase,使用matebase构建目前的bi.xxx.com,数据源自RDS,当进行复杂查询的时候存在性能问题,会影响生产环境
初代版本
notion image
演进版本
notion image

备选方案

B:使用阿里云DLA服务
测试过程
1:尝试云原生数据湖的联合查询多个MySql实例,通过https://help.aliyun.com/document_detail/107698.html?spm=a2c4g.11186623.6.837.4d7974f4GsP1S0
可以实现不同数据库的多表联合查询,但是咨询工单反馈,查询过程仍再原数据库中执行,在小数量的数据库查询中影响不大,但是在查询量较大较频繁时,无法
保证原数据库的性能。于是尝试其他方法。
2:尝试创建云原生数据湖分析中的T+1多库合并建仓,将数据库导入到DLA数据湖中,通过自己新创建的数据量很小的数据库导入,可以实现,并且不会占用大量性能。
但针对test库的多库合并建仓时,因为性能原因造成较大的影响。并且询问阿里云工单之后反馈,多库合并建仓再第一次入湖之后,再每一次的更新数据时是全量重新同步,意味着
每天进行同步都会遭遇很大的性能影响,并且数据存在一天延迟于是尝试其他方法。
优点
  1. 如果使用数据库联合查询方案,可以直接跨库查询
  1. 支持OSS直接上传离线数据文件进行分析
缺点
  1. 虽然可以实现跨库查询,但是占用的还是生产库的计算资源,当需要复杂查询的时候可能影响用户体验
  1. T+1方案存在数据延迟,且每天都是全量同步,在同步时对生产库有性能影响
C:使用clickhouse订阅多个mysql,实现数据聚合
notion image
方案优势
  1. clickhouse在推出了支持MySQL实时复制后,可以很方便的订阅binlog实现clickhouse的数据库更新同步,实现准实时的数据分析
  1. clickhouse数据库和用户使用的mysql数据库是完全隔离的,不会互相影响
  1. 无论是直接购买还是自建,相对其他大数据方案,成本较低
  1. 其他公司应用较多,携程,有赞等公司有在线上环境使用,参考资料较多
  1. 针对用户行为漏斗,留存等场景,有专有函数支持计算
  1. metabase支持clickhouse数据库,原来的使用习惯不用改变,还是可以通过bi.xxx.com看数据
测试过程
ClickHouse,通过购买建立ClickHouse集群,通过购买DTS数据同步,将RDS数据库中的数据导入到ClickHouse集群中,可以实现不同数据的不同表导入到同一个数据库中,实现快速查询。
ClickHouse ,并且可以实时更新数据。 现ClickHouse配置为4核8G配置 1000G 属于按量付费,约1.5RMB/小时,30RMB/天,如需长期使用建议转为按月付费约600RMB/月, DTS数据同步
现购买两个每个约0.5RMB/小时,两个共计20RMB/天,如需长期使用建议按月付费每个400RMB/月
初始成本1400/月,后续可以视业务规模扩容升配置
线上业务性能对比
实例配置对比  mysql5.7 16核64G内存      clickhouse 20.3  4核8G
查询速度对比:
MySQL语法
clickhouse语法
mysql
clickhouse
性能差异
select COUNT( *) FROM trade_order_asset
select COUNT( *) FROM trade_order_asset
4ms
1000倍
mysql: select COUNT(*) from steam_trade_db.trade_product_sell where create_time >=unix_timestamp('2021-03-01 00:00:00')
select COUNT(*) from steam_trade_db.trade_product_sell where create_time >=toUnixTimestamp('2021-03-01 00:00:00')
45ms
1001倍
-- 次月交易留存:指在上月有过交易的用户,在当月再次有交易行为,不包含系统账号SELECT count(DISTINCT l.user_id) as 月交易人数,count(DISTINCT r.user_id) as 上月留存交易用户FROM (SELECT DISTINCT buyer_user_id as user_id                        FROM trade_order_asset force index(IDX_CTIME)                        WHERE buyer_platform_id = 2 AND status = 10 AND is_import = 0                                AND create_time >= UNIX_TIMESTAMP('2021-02-01')                                AND create_time < UNIX_TIMESTAMP('2021-03-01')                                UNION                        SELECT DISTINCT seller_user_id as user_id                        FROM trade_order_asset force index(IDX_CTIME)                        WHERE seller_platform_id = 2 AND status = 10 AND is_import = 0                                AND create_time >= UNIX_TIMESTAMP('2021-02-01')                                AND create_time < UNIX_TIMESTAMP('2021-03-01')                        ) lLEFT JOIN (        SELECT DISTINCT buyer_user_id as user_id                                                FROM trade_order_asset force index(IDX_CTIME)                                                WHERE buyer_platform_id = 2 AND status = 10 AND is_import = 0                                                        AND create_time >= UNIX_TIMESTAMP('2021-01-01')                                                        AND create_time < UNIX_TIMESTAMP('2021-02-01')                                                        UNION                                                SELECT DISTINCT seller_user_id as user_id                                                FROM trade_order_asset force index(IDX_CTIME)                                                WHERE seller_platform_id = 2 AND status = 10 AND is_import = 0                                                        AND create_time >= UNIX_TIMESTAMP('2021-01-01')                                                        AND create_time < UNIX_TIMESTAMP('2021-02-01')                                        ) r ON l.user_id = r.user_idLEFT JOIN trade_user c ON l.user_id = c.idWHERE c.third_user_id not in (555136127);
-- 次月交易留存:指在上月有过交易的用户,在当月再次有交易行为,不包含系统账号SELECT count(DISTINCT l.user_id) as trade_num,count(DISTINCT r.user_id) as live_numFROM (SELECT DISTINCT buyer_user_id as user_id                        FROM trade_order_asset                         WHERE buyer_platform_id = 2 AND status = 10 AND is_import = 0                                AND create_time >= toUnixTimestamp('2021-02-01 00:00:00')                                AND create_time < toUnixTimestamp('2021-03-01 00:00:00')                                UNION ALL                        SELECT DISTINCT seller_user_id as user_id                        FROM trade_order_asset                         WHERE seller_platform_id = 2 AND status = 10 AND is_import = 0                                AND create_time >= toUnixTimestamp('2021-02-01 00:00:00')                                AND create_time < toUnixTimestamp('2021-03-01 00:00:00')                        ) lLEFT JOIN (        SELECT DISTINCT buyer_user_id as user_id                                                FROM trade_order_asset                                                 WHERE buyer_platform_id = 2 AND status = 10 AND is_import = 0                                                        AND create_time >= toUnixTimestamp('2021-01-01 00:00:00')                                                        AND create_time < toUnixTimestamp('2021-02-01 00:00:00')                                                        UNION ALL                                                SELECT DISTINCT seller_user_id as user_id                                                FROM trade_order_asset                                                 WHERE seller_platform_id = 2 AND status = 10 AND is_import = 0                                                        AND create_time >= toUnixTimestamp('2021-01-01 00:00:00')                                                        AND create_time < toUnixTimestamp('2021-02-01 00:00:00')                                        ) r ON l.user_id = toInt64(r.user_id)LEFT JOIN trade_user c ON l.user_id = toInt64(c.id)WHERE c.third_user_id not in (555136127);
2080ms
8倍

调研过程

1:尝试云原生数据湖的联合查询多个MySql实例,通过https://help.aliyun.com/document_detail/107698.html?spm=a2c4g.11186623.6.837.4d7974f4GsP1S0
可以实现不同数据库的多表联合查询,但是咨询工单反馈,查询过程仍再原数据库中执行,在小数量的数据库查询中影响不大,但是在查询量较大较频繁时,无法
保证原数据库的性能。于是尝试其他方法。
2:尝试创建云原生数据湖分析中的T+1多库合并建仓,将数据库导入到DLA数据湖中,通过自己新创建的数据量很小的数据库导入,可以实现,并且不会占用大量性能。
但针对test库的多库合并建仓时,因为性能原因造成较大的影响。并且询问阿里云工单之后反馈,多库合并建仓再第一次入湖之后,再每一次的更新数据时是全量重新同步,意味着
每天进行同步都会遭遇很大的性能影响,并且数据存在一天延迟于是尝试其他方法。
3:ClickHouse,通过购买建立ClickHouse集群,通过购买DTS数据同步,将RDS数据库中的数据导入到ClickHouse集群中,可以实现不同数据的不同表导入到同一个数据库中,实现
快速查询。在单表通过执行 select COUNT( *) FROM trade_order_asset   统计语句ClickHouse中查询完成需要约3-4ms,在原数据库中查询,约3000-4000ms,ClickHouse性能领先。
ClickHouse ,并且可以实时更新数据。 现ClickHouse配置为4核8G配置 1000G 属于按量付费,约1.5RMB/小时,30RMB/天,如需长期使用建议转为按月付费约600RMB/月, DTS数据同步
现购买两个每个约0.5RMB/小时,两个共计20RMB/天,如需长期使用建议按月付费每个400RMB/月
参考资料
clickhouse,postgresql对比:https://www.codenong.com/cs109208273/
致谢:
💡
有任何问题,欢迎您在底部评论区留言,一起交流~
 
 

开始订阅我的关于终生学习, 生产力以及知识管理的文章. 订阅后, 您将收到我的精选文章.

©2014-2024 Jimmy Lee. All rights reserved. 公众号: 技术管理方法论
Powered By My Lovely Children