MySQL 亿级数据的迁移、清洗、与审计分析

作者: 刘一二 发布时间: 2022年7月24日 浏览: 916

职业生涯中,曾经有两年多时间从事IT内部审计的工作,需要介入公司大部分的业务系统,主要的数据库为 MySQL,特别是三年期审计,需要统计三年的数据素材(亿级以上)。对MySQL的性能有一些自已的理解,

一般对外的高负载的系统为了提升性能,极力规避MySQL直接面对用户,在用户和MySQL之间,有一层又一层的缓存,如果流量大量打到了MySQL上,我们称为缓存穿透,系统很容易就挂掉了,即使在缓存保护下的应用,也会极力限制复杂的 SQL查询,鲜少有 JOIN, GEOUP BY,而在内部系统中,因为用户规模可控。MySQL的各种极致用法都可能出现,我们计论MySQL性能,一般就是内部系统这种场景下。

IT 审计需要跟公司内部大部分的系统打交道,公司有上百个系统,很多是遗留系统,各种各样的架构设计,程序员在这个领域是超级个性化的,同一个系统功能,换一个人,就可能是完全不同的设计,某个系统中可能无差别全是分区表;另一个系统中单表无分区竞有两亿多多条数据,且运行稳定 ......

我对 MySQL数据量极限的理解

很多人认为 MySQL适合千万级以下的数据,或者其它量级,他们的结论大致是正确的,因为是他们的应用场景各不相同。

我将MySQL数据量极限划分为以下情况:

A 不分区

不分区时,性能主要影响因素就是:索引,MySQL表至少有一个主键索引,即使你没建,MySQL也会隐藏的创建一个自增ID

A 01 只有一个主键索引(亿级以上完全没有问题)

写入数据时数据量对写入性能影响比较小。仅当主键这个索引需要做二叉树平衡时,产生额外IO操作,大部分情况下,和空表插入性能无异。

超级大表读取数据时,只能用两种方式,1. 按主键读取,2 不排序或按主键排序 取前多少个。 其它方式不要想了(抓狂:几个小时无返回,show processlist 一直有这个进程,只能 kill 掉),

A 02 多个索引(极限数据量几千万)。

写入数据时,索引越多,索引需要平衡二叉树产生重建操作机率越大。性能下降越厉害,索引个数,类型,长度,行数据大小等影响很大,这个极限没有确定的值。在一个业务系统中,某个核心业务表,100多个字段,近 30 个索引,1000 万+数据,还能硬撑,不过MySQL服务器内存被迫一步步升到了 256G

读取数据时,只有一条路,一定要走索引,尽可能走唯一索引,尽可要走更精准的索引(基数更大的),LIKE 也可以用(但是一定是索引字段, 而且是 LIKE ’***%‘,不能是 %开头的),联合索引只用到了部分字段也可,但必须是按顺序最前的(如 A+B+C三个字段的联合索引,WHERE可以只有A字段,或 A AND B 字段,或三个都有),GROUP BY 也可以用,稍慢点,但一定是 GROUP BY 索引, WHERE条件要走索引,在千万级的 业务系统中,发现有GROUP BY 嵌套 (GROUP BY 的结果作为临时表再次GROUP BY ),效率也可接受。

B 分区表

分区表将数据分成区块存储,可存储到不同磁盘。最多 1024 个分区。和分表性能相当。每个分区可以认为和不分区单表的限制基本一致,如果单表稳定处理1000万复杂的业务数据,那1024个分区理论极限可以稳定处理 100 亿条数据

但分区表最大缺点:操作必须限定在一个分区内,即 WHERE 查询条件中 一定要包含分区字段,这极大地限制了分区表的应用,如果你不小心漏掉了,呵呵,去 KILL 进程吧。

数据迁移:

做IT审计,首先面临的问题是将数据定期从业务系统复制到内审系统中。主要因为:

  1. 业务系统有分区或分表,历史数据也可能封存到别处。
  2. 系统隔离,审计操作不影响业务系统,
  3. 数据需要清洗,加工,拆分或聚合。

早期尝试了 现成 ETL工具 的方案,主要是 kattle,效率还可以(每秒能处理上万条),主要障碍在数据清洗操作。比如订单中有  A+B 这样的组合商品,(我们称 A+B 为商品编码,其中 A, B 为 SKU,商品编码格式没有限制,在基础资料系统中和SKU关联起来即可),表示 两个东西捆绑销售。比如手持风扇加个18650电池。清洗逻辑很多,比如有 将商品编码打散成 SKU, 调用基础资料服务获取商品编码与SKU关系, 调用成本服务获取各SKU成本价,按成本价比例分摊售价。katttle  不擅长做这种复杂操作,实现起来很麻烦,且无法自动化部署,无版本控制。不易测试,最终决定自已开发迁移程序。

清洗后入库时对数据插入及更新的 SQL 处理:

1 SQL预编译后再批量执行

首先 编写占位符的 SQL

INSERT INTO table(a, b, c ... ) VALUE(?, ?, ? , )

预编译后批量注入清洗过数据,不同语言和服务器配置有些差距,但都能很轻松实现 1万条以上/秒的速度,包含清洗逻辑(清洗逻辑经过了极度优化,无处不缓存,耗时影响比较小)。

2 拼接SQL批量执行

INSERT INTO table(a, b, c ... ) VALIES
('1,' '2', '3' , ),
('1,' '2', '3' , ), 
('1,' '2', '3' , ),
......

VALUES 后边大量数据,我们一般用 5000 行数据拼成一个 SQL,注意数据需要主动做 escape 处理。这样的SQL一秒钟能执行5~6 条或更多,跟字段量多少有关,相当于一秒处理 3 万条以上的数据,1分钟处理 200 万,5分钟处理 1000 万,

但这并不是极限,我们发现 瓶颈 不在 MySQL身上,而是 网络带宽和磁盘的 IO写入速度首先达到了峰值。一旦我们的程序长时间启动,用不了多久,运维部门的同事就来关照我们了,以至于我们不得不限制下执行时段或速度。

不确定是插入还是更新情况的处理

REPLACE INTO  table(a, b, c ... ) VALIES
('1,' '2', '3' , ), 
('1,' '2', '3' , ),
('1,' '2', '3' , ),
......

MySQL 提供了独有的 REPLACE INTO, 和  INSERT INTO 语法一致,数据存在时更新,不存在时插入,和 ON DUMPLICATE KEY UPDATE 功能类似,但更方便。REPLACE INTO  操作的表一定要有有唯一键,如果没有,就是单纯的 INSERT INTO 了.

更新也可以几千条批量处理

UPDATE table SET a = CASE 
    WHEN id=1 THEN 'A1' 
    WHEN id=2 THEN 'A2' 
    WHEN id=3 THEN 'A3'  
    .....
END

批量更新相比 批量 INSERT INTO 和 REPLACE INTO 差很多,但也比单条更新快很多倍

至此,各业务系统的数据经过清洗,完整部署在了内审系统中,年度数据5000万行左右,三年 1.5亿, 因为清洗过的数据更有价值,这些数据被多方引用,如 Tableau 报表,财务部门,同时也有同步到 ES 和 ClickHouse 中做不同用途的运算。

数据应用:

外部审计公司进场后,会要求我们提供某些数据,各种维度的, 如:提供一年期前10销量国家的复购次数最多的前 100个用户的订单数据。这些需求都是一次性的,在指定时间段内提供即可,大部分需求直接编写SQL即可实现。编写出来的 SQL 一般都包含两层或三层 GROUP BY 嵌套, 或者几个 GROUP BY 进行 UNION ALL 后再次 GROUP BY。

为了提升效率,我们的数据冗余了两张表,一张不分区,一张按月份分区,数据完全一致,

  • 如果需求是月度数据(查询量几百万条),优先使用分区表,基本是秒级的查询。
  • 如果需求是几个月的数数(查询量几千万条条),优先使用分区表,几个月的的分段成单月 SQL 再 UNION ALL 后再聚合,基本是两三秒的时间。
  • 如果更长维度的话,没办法使用分区表,在总表下查询,要一分钟以上,某些复杂的查询可能出不来,这时候要去 ClickHouse 了( DBeaver 客户端)。又找到了秒级的感觉。

ClickHouse 几乎完全兼容 SQL 语句,只有部分 SQL 函数(如日期)需要特殊处理下,效率相当给力。没有以 ClickHouse 为中心是因为 MySQL 是基础应用,所有系统都是围续 MySQL 构建的,而 ClickHouse 主要适用于数据分析的场景,适合整表写入,数据更新效率很低,因此它的数据更新频率很低,不论数据量有多少,它的更新就是删表,然后整表导入,反而出奇的快。

最新文章
热门文章
导航