绑定完请刷新页面
取消
刷新

分享好友

×
取消 复制
在 MySQL 创造类似 PipelineDB 的流视图(continuous view)
2022-03-01 14:38:11

公司的系统采用的是 Google Cloud SQL 提供的 MySQL 数据库,由于历史原因,数据库成本极高,需要对它进行优化缩减成本。

相比 PostgresSQL,MySQL 主要缺少以下特性,导致优化难度极高:

1. 缺少部分索引。部分索引可以将一亿行数据中活跃的那部分数据(往往只有几百万行)隔离出来。

2. 缺少计算索引。MySQL 的索引不能是表达式。

3. 不支持并行。MySQL不能让一个查询分布到多个核,从而缩短查询执行时间。由于只能用一个核,运行时间较长的的SQL很容易造成锁超时,各个服务日志里充满了锁超时错误。

弃暗投明换PG或者上Lambda架构等做法都过于复杂,不考虑。

在优化过程中我发现,如 MySQL 能支持类似流视图,很多主要语句的执行问题可以迎刃而解。

什么是流视图?

基于 Postgresql 的 PipelineDB 支持 Continuous View,使数据库具备了类似流式计算的效果。

    流式数据库PipelineDB(集成Kafka) - 简书 

    流计算风云再起 - PostgreSQL携PipelineDB力挺IoT(物联网), 大幅提升性能和开发效率-云栖社区-阿里云

采用速度层对新增数据执行计算后放入快速存储,而不是在查询时开始计算,这是 Lambda 之类大数据架构的精髓思想。流视图将这套架构包装为一个直观易懂的数据库新事物,和关系理论又不相违,是很巧妙的设计。显然,流视图在很多场景都可以取代 Spark 之类大数据架构,为用户提供更实时的统计查询服务。

羡慕是没用的,没有就自己搞,如何实现流视图?

流视图的原理可以想见:流视图本身是一个表,程序侦听视图相关的各个表的变化,将变化更新到相应的流视图的数据行。

侦听数据变化是实现流视图的必备基础。

在SQL中侦听变化的方式主要是触发器。给每个表建一个触发器很不明智,干出来也不漂亮。我想到一个很好的方案:BINLOG。找了一下,目前已有若干支持 binlog 转 MQ 的方案。我选择了 canal,备选 maxwell。在实施过程中发现,canal 往 MQ 推送的消息没有都是字符串且不支持枚举,而 maxwell 虽然消息很棒,还支持 js 做 filter,但是经常出错崩溃,CPU高,处理不了太大的 binlog,终还是采用了 canal。相比来说 canal 一直在阿里实用,运行非常稳定,CPU 耗费低,从来不出错。

有了 BINLOG,后面就是搞发明创造了,做架构写代码,终成果如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
class HV_user_gender(HotView):
    VIEW_SQL = '''
            SELECT a.id, a.name, b.gender,b.age FROM a, b ON a.id = b.id WHERE b.age < 22
            %s
    '''
 
    def __init__(self, view_db: Engine, logger: logging.Logger, cache_db:Engine = None):
        meta = MetaData()
        mapping = [
            TableMapping('a', meta,
                         ColumnMapping('id', cond_alias="a.id", view_alias="id", is_fixed=True, primary_key=True),
                         ColumnMapping('name', view_alias="name", is_fixed=False),
                         trace_insert=True),
 
            TableMapping('b', meta,
                         ColumnMapping('id', cond_alias="b.id", is_output=False, primary_key=True),
                         ColumnMapping('gender', view_alias="gender", is_fixed=True),
                         ColumnMapping('age', view_alias="age", is_fixed=False),
                         predicate=lambda row: int(row['age']) < 22
                         )
        ]
        super().__init__(view_db, 'hot_user_gender', topics=['a''b'],
                         logger=logger,
                         primary_key="id",
                         view_sql=HV_user_gender.VIEW_SQL,
                         mapping=mapping,cache_db= cache_db)
 
    def init_view(self):
        sql = '''
            insert into hot_user_gender(id, name, gender, age)
        ''' + (self.view_sql % '')
        = self.view_db.execute(sql)
        self.logger.info('init view insert %s items' % r.rowcount)

 

这里实现了一个基于 a b 两表的联合查询,b.age < 22 过滤条件的流视图。当 b.age 有变化时,如 b.age >= 22,视图行自动删除,如 b.age < 22,相应视图行更新。a 有增删时,视图行同步变化行。

该视图的代码不到100行,声明式代码风格。

目前对一个常用查询做了流视图改造,CPU 立即降低 15%!随处可见的 Read Lock timeout 之类异常也不见了。

流视图的改造成本很低,对于用 mybaits/ibatis 之类框架的程序来说,只要调整相关 SQL 代码,将原来的复杂查询挪到 python 中,替换成单表流视图的名称即可,原有的技术堆栈还能继续使用。什么难出的报表、加载耗时漫长的 dashboard,homepage,等等,都可以通过流视图改为实时秒开,不但用户体验上升,数据库压力也骤然下降,实乃 SQLer 的灵丹妙药,MySQL 这种弱爆了的数据库也有枯木逢春之感。

分享好友

分享这个小栈给你的朋友们,一起进步吧。

PipelineDB
创建时间:2022-02-28 14:06:00
PipelineDB
展开
订阅须知

• 所有用户可根据关注领域订阅专区或所有专区

• 付费订阅:虚拟交易,一经交易不退款;若特殊情况,可3日内客服咨询

• 专区发布评论属默认订阅所评论专区(除付费小栈外)

技术专家

查看更多
  • 飘絮絮絮丶
    专家
戳我,来吐槽~