单数据库大表分表设计

背景

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

设计思路

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

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

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

3. 创建t_md_shard_table表

租户ID(tid)	应用ID(aid)	设备ID(eid)	逻辑表(l_table)	实际存储表(r_table)	开始时间戳（闭区间）(start_timestamp)
10201	746	E00001	t_gps	t_gps_A0	0
10202	747	E00002	t_gps	t_gps_B0	0
10203	748	ES0003	t_gps	t_gps_C0	0

结束时间戳（开区间）(end_timestamp)	是否完成滚动(is_finished_rolling)
+∞	N
+∞	N
+∞	N

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

4. t_md_statistic_table表

ID(id)	物理表(r_table)	表当前数据行(current_rows)
1	t_gps_A0	45,000,000
2	t_gps_B0	42,000,000
3	t_gps_C0	43,000,000

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

5. t_md_shard_confg表

ID(id)	每张表数据大小(table_max_size)
1	50,000,000

当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

ID(id)	物理表(r_table)	表当前数据行(current_rows)
1	t_gps_A0	45,000,000
2	t_gps_B0	42,000,000
3	t_gps_C0	43,000,000
4	t_gps_A1	1,000

• t_md_shard_table表数据

租户ID(tid)	应用ID(aid)	设备ID(eid)	逻辑表(l_table)	实际存储表(r_table)	开始时间戳（闭区间）(start_timestamp)
10201	746	E00001	t_gps	t_gps_A0	0
10202	747	E00002	t_gps	t_gps_B0	0
10203	748	ES0003	t_gps	t_gps_C0	0
10201	746	E00001	t_gps	t_gps_A1	2022-01-20 10:00:00

结束时间戳（开区间）(end_timestamp)	是否完成滚动(is_finished_rolling)
2022-01-20 10:00:00	Y
+∞	N
+∞	N
+∞	N

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语句要精确到查询字段，缩减查询时间范围。