单数据库大表分表设计

admin
2024-05-06 18:29

背景

我们业务系统GPS数据量非常庞大,一天1万辆设备大概存在3600万数据,这种规模的数据自身查询用mysql是存在问题的,所以我们采用的是clickhouse存储,但是CK对于批量修改了删除支持都不好,而且CK不支持事务,我们的场景是保存之后还需要做一些数据丰富,所以考虑把CK存储的数据拿回来放在mysql中,因为我们是一主一从模式(还没有多主同步),所以就存在单节点要保留所有的数据,这就是本文主题(单个数据库大表进行分拆表设计),分拆表可以扩展到多节点多主模式哈,大家注意:)可能有人会说用mycat来做分库分表,我们实际也论证过了,它对于我们的灵活性来说不高。分库分表我们自身使用的UDD引擎实际上也是可以支持的。

设计思路

1. 根据设备ID(eid)+租户ID(tid)进行分组管理

2. 每张表的数据为5000万滚动创建一个新的物理表

数据量大小可以在下面表配置配置

3. 创建t_md_shard_table表

t_md_shard_table初始阶段的时候,根据设备ID(eid)进行分组,每1000台设备数据存储到一张表,例如设备编号0到999存储在t_gps_A0,1000~1999存储在t_gps_B0,开始时间为0,结束时间为+∞

4. t_md_statistic_table表

t_md_statistic_table表 每一小时去统计每一行物理表(r_table)当前的数据存储

5. t_md_shard_confg表

当t_md_statistic_table.current_rows大于等于t_md_shard_confg.table_max_size,就给t_md_shard_table.r_table=t_gps_A0的所有数据把当前的时间戳打上。然后再自动创建一个新的物理表,从物理表t_gps_A0滚动到t_gps_A1,使用如下DDL语句:create table t_gps_A1 like t_gps_A0;这样就可以完整copy前一张的老表

6. 滚动之后的数据现状

  • t_md_statistic_table
  • t_md_shard_table表数据

7.处理逻辑规则

UDD对SQL进行拦截到,然后根据CRUD操作进行单独操作:

  1. Insert语句,查询t_md_shard_table表,根据租户ID(tid)+应用ID(aid)+设备ID(eid)+是否完成滚动(is_finished_rolling)[N] 来获取实际存储表,把数据插入到t_md_shard_table.实际存储表(r_table)

阐述下时间戳的用法:

在删除和修改操作,只支持精确的时间戳(换句话说:只支持单条数据操作,不支持多条数据操作)

  1. 获得单条数据的时间戳T1,然后匹配t_md_shard_table表里面的数据,根据 T1>=start_timestamp AND T1<end_timestamp 来定位到实际r_table的表,然后把数据定位到具体存储表的ID行来删除
    如果支持多行删除的话,那么存储时间戳范围分布在多个表中,有可能数据分布在一张表、多张表这种的情况
  2. Delete语句,查询t_md_shard_table表,根据租户ID(tid)+应用ID(aid)+设备ID(eid)+时间戳的范围来定位到删除数据行是落在哪个实际存储表(r_table)。
     
  3. Update语句,查询t_md_shard_table表,根据租户ID+应用ID+设备ID+时间戳的范围来定位到修改数据行是落在哪个实际存储表(r_table)。
     
  4. Select语句,查询t_md_shard_table表,根据租户ID+应用ID+设备ID和以下场景来处理:

    传的是一个时间戳范围T1-T2[这种查询场景对应的是对应的某一天A点到B点,某一个时刻A点到B点],可能存在返回一张r_table,二张r_table,N张r_table,那么在执行真实的SQL之前进行拆分:

    例如:SQL001=SELECT * FROM t_gps WHERE gps_start_time > "2022-01-21 10:00:00" AND gps_start_time < "2022-01-21 12:00:00"

    执行的时候对于需要分页的业务逻辑,在Service代码层面先导入Bean:DruidEngine这个类,把SQL001传入到engine.exec(sql)中,UDD引擎会根据传入的SQL语句,返回所有转换出来的SQL语句,如果SQL001的数据存储在t_gps_A0和t_gps_A1这二张表,那么会返回以下二条SQL:
    1. Subsql1 = SQL001=SELECT * FROM t_gps_A0 WHERE gps_start_time > "2022-01-21 10:00:00" AND gps_start_time < "2022-01-21 12:00:00"
    2. Subsql2 = SQL001=SELECT * FROM t_gps_A1 WHERE gps_start_time > "2022-01-21 10:00:00" AND gps_start_time < "2022-01-21 12:00:00"

应用层自身得到N个SQL语句(N≥1),然后进行并行多线程查询,自己组装数据排序并进行分页

注意:范围段越大,查询的r_table表就更多,需要消耗的内存就非常的大,所以建议SQL语句要精确到查询字段,缩减查询时间范围。

全部评论

2024-05-06 20:47
这个好像有点难度设计。