xx数据库单表数据扛爆炸机制[上]

背景说明

在传统企业中,应用架构是处于比较封闭的状态,不如互联网企业的开放和大胆. 所以很多方案不能生拉硬拽式应用,必须在其基础上进行优化改造. 本次整体思路是针对不能引入互联网等中间件前提下,所做的努力方案机制.

情况说明

在查询过程中,由于单表过大,查询返回花费1分钟左右.如果是连表查询,即使是小表去驱动大表,查询花费时间也是3分钟左右,这是在业务低峰的查询情况.如果是正常业务时间那就是翻倍甚至是3倍,而且会引起级联反应,比如死锁/session过多,服务器cpu/io占比超出100等不良情况.

技术类型特征总结

单表数据量巨大，95%冷数据，5%热数据。
单表查询，不做联表查询

数据收集

数据库类型

数据类型是必须要的,目前的主流大部分都是支持一下方案的.但是,总有意外,比如mysql的5.0以下版本是不支持.故，大家最好先确定好db实例是否支持.比如表分区/双主or一主一从.

慢sql分析

通过慢sql排序进行查询,这没有套路哈,就是对慢sql进行导出分析即可.在本次问题中,总结下来就是单表过大,索引也无法进行很好的区分查询,大表

数据量及整体分布情况分析

一周生产的数据情况

企业微信截图_17150828146347.png

可以从几个维度来结合分析,没有硬性要求怎么样.大家结合业务情况分析才有意义!!!

表维度
- 自增id情况(粗暴简易)
该参数只适用于表完整度很高,没有过度修饰数据(即没有删除过多数据). 笔者单表自增id达到1792586174540419074,
- 存储大小
直接查看磁盘大小即可
- 无需过多跨区/跨表查询
sql就是简单的单表查询或者是非多层sql查询
业务维度
- 冷热情况

可通过埋点情况来分析,如果没有埋点系统可以简单通过nginx请求流量来做简易分析. 根据上面分析情况,关键是分析日常请求量中是否只关注当天or最近几天的访问情况,历史数据基本都是躺平状态,死水数据(不能删除的)

- 业务表情况

我们主要业务大表是类似设备物联网心跳数据,每秒都会有大量推送,也可以类比成系统的业务登录日志/埋点行为信息等,所以，归属于单表多字段大数据量，基本上都是走全表查询的技术模态

实战操作

历史数据归档表

简单来说,这个归档表是对大表进行每天归档,按照日期作为维度,新增一张新表来存前天数据,类比起来的话，类似分表.这样每次访问的数据都是小表数据.

1.编写过程

过程里面的即为我们的业务逻辑,就是表复制的动作

2.1 过程

CREATE OR REPLACE PROCEDURE {库名}.p_t_gps_pos_trace_day(pi_date date)
as
         v_sql1 VARCHAR(2000);
         v_sql2 VARCHAR(2000);
         v_sql3 VARCHAR(2000);
         v_sql4 VARCHAR(2000);
         v_where VARCHAR(20);
BEGIN
        select date_format(pi_date,'%Y%m%d') into v_where;

         SET  v_sql1=concat('
         CREATE TABLE {库名}.t_gps_pos_trace_',v_where,'
        (
        "c_id" BIGINT NOT NULL,
        "c_fid" BIGINT NOT NULL,
        "c_client_key" VARCHAR(64) NOT NULL,
        "c_client_ip" VARCHAR(64),
        "c_client_port" INT,
        "c_speed" REAL,
        "c_degree" REAL,
        "c_star_number" INT,
        "c_long" DOUBLE,
        "c_lat" DOUBLE,
        "c_abs_x" DOUBLE,
        "c_abs_y" DOUBLE,
        "c_last_long" DOUBLE,
        "c_last_lat" DOUBLE,
        "c_last_abs_x" DOUBLE,
        "c_last_abs_y" DOUBLE,
        "c_gps_device_date" TIMESTAMP(0),
        "c_last_gps_device_date" TIMESTAMP(0),
        "c_gps_date" TIMESTAMP(0),
        "c_last_gps_date" TIMESTAMP(0),
        "c_db_create_date" TIMESTAMP(0),
        "c_last_create_date" TIMESTAMP(0),
        "c_over_area_alarm_state" SMALLINT,
        "c_gsm_signal_intensity" SMALLINT,
        "c_state" SMALLINT,
        "c_acc_state" SMALLINT,
        "c_power_state" SMALLINT,
        "c_up_model" SMALLINT,
        "c_up_type" SMALLINT,
        "c_source" INT,
        "c_obj_type" SMALLINT,
        "c_obj_id" INT,
        "c_avg2_speed" INT,
        "c_avg5_speed" INT,
        "c_last_point_interval" INT,
        "c_last_point_distance" REAL,
        "c_is_in_section" TINYINT,
        "c_last_is_in_section" TINYINT,
        "c_is_in_section_area" TINYINT,
        "c_last_in_section_area" TINYINT,
        "c_work_start_time" TIMESTAMP(0),
        "c_work_is_valid" TINYINT,
        "c_car_work_status" TINYINT,
        "c_is_work_line" TINYINT,
        "c_last_fid" BIGINT,
        "c_is_fade_zone" TINYINT,
        "c_last_is_fade_zone" TINYINT,
        "c_total_mileage" INT,
        "c_level_ratio1" REAL,
        "c_level_ratio2" REAL,
        "c_work_line_id" INT,
        "c_car_work_data_id" BIGINT,
        "c_work_add_time" INT,
        "c_is_open_potential" TINYINT,
        "c_oil_rate" DEC(10,2),
        "c_fence_id" INT,
        "c_is_fence" TINYINT,
        "c_station_obj_type" TINYINT,
        "c_station_obj_id" INT,
        "c_oil_ratio" REAL,
        "c_movement_state" TINYINT,
        "c_section_area_id" INT,
        "c_voltage_level" TINYINT,
        "c_weight" REAL,
        "c_last_weight" REAL,
        "c_altitude" INTEGER,
        "c_lng_scr" DOUBLE,
        "c_lat_src" DOUBLE,
        "c_last_lng_scr" DOUBLE,
        "c_last_lat_src" DOUBLE,
        NOT CLUSTER PRIMARY KEY("c_id")) STORAGE(ON "{库名}", CLUSTERBTR)');
        
        SET  v_sql2=concat('CREATE  INDEX idx_t_gps_pos_trace_createdate_id_',v_where,' ON {库名}.t_gps_pos_trace_',v_where,'("c_db_create_date" ASC) STORAGE(ON "{库名}", CLUSTERBTR)') ;

        SET  v_sql3=concat('CREATE  INDEX idx_t_gps_pos_trace_coll_id_',v_where,' ON {库名}.t_gps_pos_trace_',v_where,'("c_obj_type" ASC,"c_obj_id" ASC,"c_gps_date" DESC) STORAGE(ON "{库名}", CLUSTERBTR)') ;
        
        --SET  v_sql4=concat('grant SELECT on {库名}.t_gps_pos_trace_',v_where,' to "{库名}"');
        
        EXECUTE IMMEDIATE v_sql1;
        EXECUTE IMMEDIATE v_sql2;
        EXECUTE IMMEDIATE v_sql3;
        --EXECUTE IMMEDIATE v_sql4;
END;

2.利用db本身自带的任务/触发器

这里采用任务来生成

2.1 写法内容

call SP_CREATE_JOB('更新数据',1,0,'',0,0,'',0,'更新数据【每天2点执行一次】');

2.2 效果

3.效果

企业微信截图_17162595167559.png

表分区

表分区是依托于数据库本身的能力,原理也是分而治之.通过对大表进行二次切割,分成一个个小表

1.通过

1.1 写法

PARTITION BY RANGE("分区键")


				
CREATE TABLE "{库名}"."t_gps_pos_trace"
(
"c_id" BIGINT NOT NULL,
"c_fid" BIGINT NOT NULL,
"c_client_key" VARCHAR(64) NOT NULL,
"c_client_ip" VARCHAR(64),
"c_client_port" INT,
"c_speed" REAL,
"c_degree" REAL,
"c_star_number" INT,
"c_long" DOUBLE,
"c_lat" DOUBLE,
"c_abs_x" DOUBLE,
"c_abs_y" DOUBLE,
"c_last_long" DOUBLE,
"c_last_lat" DOUBLE,
"c_last_abs_x" DOUBLE,
"c_last_abs_y" DOUBLE,
"c_gps_device_date" TIMESTAMP(0),
"c_last_gps_device_date" TIMESTAMP(0),
"c_gps_date" TIMESTAMP(0),
"c_last_gps_date" TIMESTAMP(0),
"c_db_create_date" TIMESTAMP(0),
"c_last_create_date" TIMESTAMP(0),
"c_over_area_alarm_state" SMALLINT,
"c_gsm_signal_intensity" SMALLINT,
"c_state" SMALLINT,
"c_acc_state" SMALLINT,
"c_power_state" SMALLINT,
"c_up_model" SMALLINT,
"c_up_type" SMALLINT,
"c_source" INT,
"c_obj_type" SMALLINT,
"c_obj_id" INT,
"c_avg2_speed" INT,
"c_avg5_speed" INT,
"c_last_point_interval" INT,
"c_last_point_distance" REAL,
"c_is_in_section" TINYINT,
"c_last_is_in_section" TINYINT,
"c_is_in_section_area" TINYINT,
"c_last_in_section_area" TINYINT,
"c_work_start_time" TIMESTAMP(0),
"c_work_is_valid" TINYINT,
"c_car_work_status" TINYINT,
"c_is_work_line" TINYINT,
"c_last_fid" BIGINT,
"c_is_fade_zone" TINYINT,
"c_last_is_fade_zone" TINYINT,
"c_total_mileage" INT,
"c_level_ratio1" REAL,
"c_level_ratio2" REAL,
"c_work_line_id" INT,
"c_car_work_data_id" BIGINT,
"c_work_add_time" INT,
"c_is_open_potential" TINYINT,
"c_oil_rate" DEC(10,2),
"c_fence_id" INT,
"c_is_fence" TINYINT,
"c_station_obj_type" TINYINT,
"c_station_obj_id" INT,
"c_oil_ratio" REAL,
"c_movement_state" TINYINT,
"c_section_area_id" INT,
"c_voltage_level" TINYINT,
"c_weight" REAL,
"c_last_weight" REAL,
"c_altitude" INTEGER,
"c_lng_scr" DOUBLE,
"c_lat_src" DOUBLE,
"c_last_lng_scr" DOUBLE,
"c_last_lat_src" DOUBLE,
NOT CLUSTER PRIMARY KEY("c_id"))
PARTITION BY RANGE("c_gps_date")
INTERVAL(NUMTODSINTERVAL(1, 'DAY'))
(
PARTITION  "P1"  VALUES LESS THAN(DATETIME'2024-05-20 00:00:00') STORAGE(ON "{库名}", CLUSTERBTR) 
) STORAGE(ON "{库名}", CLUSTERBTR) ;

3.效果

企业微信截图_17162576796849.png

企业微信截图_17162595061866.png

读写分离/分库分表【跳转到下篇文章】

落地总结

历史数据归档表

优点

可以在单个db实例上,做到冷热分离，整体qps的响应数据压缩至50ms(单次循环500次,循环次数30次,业务高峰/低峰/常态情况各3次,持续周期5天)
无需要过多的人为介入,一次编写任务即可

弊端

维护性差

业务代码需要改,表名变成动态同时也变成了由请求参数来驱动sql查询，间接埋雷(竞品可以破坏),需要做好前端参数防护,同时也是引入更多的防护机制.程序趋向不稳定.

耦合变高

数据查询中为了完整性和隔离，会采用过程查询。无需多言，直接和数据库直接耦合了,换一个数据库我们就要改过程.

不用过程的话，那么业务代码就需要按照年月日来分离查询，无论是代码维护日期还是表维护，这个代码必须要耦合xx第三方来维护

分区表

优点

数据库帮我们做好了分表，省心省力，简单配置就好了
分完小表，进行维护也是很简单，定向维护索引
简单有效，适用原则

弊端

查询受限

查询时必须要先命中分区键,才能开始命中我们的业务索引.否则,查询性能巨慢

分区表类型受限

RANGE分区采用业务id划分，数据就会乱写入，读写比不高.基本都是采用日期切割.

LIST 分区生成大量趋同数据,间接导致索引失效(执行器优化做的).必须要强制指定索引

手动扩容

这个弊端是达梦才有的，mysql/orcale都是有自动化扩容机制的.

参考内容

分区表优秀博文

分区表优秀博文2

分区表优秀博文3

求文推荐

觉得文章对你有帮助，记得点赞收藏关注，一键三连

单表数据扛暴击

xx数据库单表数据扛爆炸机制[上]

背景说明

情况说明

技术类型特征总结

数据收集

数据库类型

慢sql分析

数据量及整体分布情况分析

实战操作

历史数据归档表

1.编写过程

2.1 过程

2.利用db本身自带的任务/触发器

2.1 写法内容

2.2 效果

3.效果

表分区

1.通过

1.1 写法

3.效果

读写分离/分库分表【跳转到下篇文章】

落地总结

历史数据归档表

优点

弊端

分区表

优点

弊端

参考内容

求文推荐