数据库性能优化:大数据表的压力

背景

业务库中有三张表数据量比较大,分别是cloud_box_event_state(事件信息表)、cloud_box_workhour(工时汇总表)、cloud_box_workhour_detail(工时明细表)。随着业务发展数据量还在增加,这些表用于存储智能硬件上报的事件信息,以及记录安装了智能硬件的设备的工作时长等。由于这三张大表的存在,业务库存储空间已经吃紧,另外智能硬件上报数据的高并发请求给业务库也带来了很大的压力。业务库已经出现响应变慢,甚至主从延迟的问题,主从延迟对业务系统影响比较大,这几张大表引发出来的问题需要解决。

分库

为了缓解业务库空间吃紧和高并发请求带来的压力,需要把大表从业务数据库拆分出去,新建一个数据库存储数据。从原来的数据库cloudbox中把三张大表cloud_box_event_state、cloud_box_workhour和cloud_box_workhour_detail拆分出来,放在cloudbox_data数据库中。此时先不要从cloudbox业务库中删除三张大表,拆分示意图如下:

image.png

多数据源

使用sharding-jdbc组件创建和管理多数据源,应用中生成两个数据源,分别是dataSource和cloudboxDataSource,其中dataSource用来访问cloudbox库,cloudboxDataSource用来访问cloudbox_data库。使用sharding-jdbc加载与创建cloudboxDataSource数据源的代码如下:

@Configuration
public class SpringBootShardingJdbcConfiguration implements EnvironmentAware {
    private static Logger logger = LoggerFactory.getLogger(SpringBootShardingJdbcConfiguration.class);
    private Environment environment;
    private final String MASTER_SLAVE_PREFIX = "cloudboxdata.sharding.jdbc.config.masterslave";
    private final String SHARDING_RULE = "cloudboxdata.sharding.jdbc.config";
    private final String DATASOURCE_PREFIX = "cloudboxdata.sharding.jdbc.datasource.";

    @Bean
    @ConfigurationProperties(prefix = MASTER_SLAVE_PREFIX)
    public YamlMasterSlaveRuleConfiguration cloudBoxDataMasterSlaveProperties() {
        return new YamlMasterSlaveRuleConfiguration();
    }

    @Bean
    @ConfigurationProperties(prefix = SHARDING_RULE)
    public YamlShardingRuleConfiguration cloudBoxDataShardingProperties() {
        return new YamlShardingRuleConfiguration();
    }

    @Bean
    public CloudBoxDataSourceFactory cloudBoxDataSourceFactory() {
        CloudBoxDataSourceFactory cloudBoxDataSourceFactory = new CloudBoxDataSourceFactory();
        try {
            cloudBoxDataSourceFactory.setCloudBoxDataSource(getCloudBoxDataSource());
        } catch (SQLException e) {
            logger.error("initialize CloudBoxDataSourceFactory error", e);
        }
        return cloudBoxDataSourceFactory;
    }

    private DataSource getCloudBoxDataSource() throws SQLException {
        return Objects.isNull(cloudBoxDataMasterSlaveProperties().getMasterDataSourceName())
                ? ShardingDataSourceFactory.createDataSource(getDataSourceMap(DATASOURCE_PREFIX), cloudBoxDataShardingProperties().getShardingRuleConfiguration(), cloudBoxDataShardingProperties().getConfigMap(), cloudBoxDataShardingProperties().getProps())
                : MasterSlaveDataSourceFactory.createDataSource(getDataSourceMap(DATASOURCE_PREFIX), cloudBoxDataMasterSlaveProperties().getMasterSlaveRuleConfiguration(), cloudBoxDataMasterSlaveProperties().getConfigMap(), cloudBoxDataMasterSlaveProperties().getProps());
    }

    private Map<String, DataSource> getDataSourceMap(String prefix) {
        Map<String, DataSource> dataSourceMap = new LinkedHashMap<>();
        String dataSources = environment.getProperty(prefix + "names");
        for (String each : dataSources.split(",")) {
            try {
                Map<String, Object> dataSourceProps = PropertyUtil.handle(environment, prefix + each, Map.class);
                Preconditions.checkState(!dataSourceProps.isEmpty(), "Wrong datasource properties!");
                DataSource dataSource = DataSourceUtil.getDataSource(dataSourceProps.get("type").toString(), dataSourceProps);
                dataSourceMap.put(each, dataSource);
            } catch (final ReflectiveOperationException ex) {
                throw new ShardingException("Can't find datasource type!", ex);
            }
        }
        return dataSourceMap;
    }

    @Override
    public final void setEnvironment(final Environment environment) {
        this.environment = environment;
    }
}

数据双写

实现三张表的数据在cloudbox库和cloudbox_data库双写,确保两个数据库中的数据一模一样。
配置中心增加开关,用来实现三张表数据的查询可以在cloudbox库和cloudbox_data库之间切换,如果查询cloudbox_data库出现问题,可以很快的切换到cloudbox数据库上。如图:

image.png

数据迁移

1、在cloudbox_data库中新建copy表

2、将cloudbox库中cloud_box_workhour_detail和cloud_box_workhour数据迁移到cloudbox_data中的copy表
-- cloud_box_workhour <= 2020-07-28 00:00:00
select id,imei,statistics_time,workhours,avg_workhours,source,real_query,car_id,workminites from cloud_box_workhour where statistics_time <= 1595865600000;

-- cloud_box_workhour_detail <= 2020-07-28 23:59:59
select id,imei,start_time,end_time,source,car_id from cloud_box_workhour_detail where start_time <= 1595951999000;
注意:从cloudbox库中导出的数据文件中,表名是cloud_box_workhour和cloud_box_workhour_detail,需要批量替换为cloud_box_workhour_copy和cloud_box_workhour_detail_copy
可以使用linux系统中的命令:
sed -i 's/cloud_box_workhour/cloud_box_workhour_copy/g' cloud_box_workhour_copy.sql
上面命令表示把cloud_box_workhour_copy.sql文件中的字符串cloud_box_workhour替换为字符串cloud_box_workhour_copy

3.停止8084,8087,8089三个数据处理服务

4.将cloudbox_data库中cloud_box_workhour_detail和cloud_box_workhour当天的数据导入copy表,执行附件20200729_2.sql
-- cloud_box_workhour_copy = 2020-07-29 00:00:00
insert into 
cloud_box_workhour_copy(imei,statistics_time,workhours,avg_workhours,source,real_query,car_id,workminites)
select 
imei,statistics_time,workhours,avg_workhours,source,real_query,car_id,workminites
from cloud_box_workhour where statistics_time = 1595952000000;

-- cloud_box_workhour_detail_copy >= 2020-07-29 00:00:00
insert into cloud_box_workhour_detail_copy(imei,start_time,end_time,source,car_id)
select
imei,start_time,end_time,source,car_id
from cloud_box_workhour_detail where start_time >= 1595952000000;

5.将cloudbox_data库中cloud_box_workhour_detail和cloud_box_workhour与copy表名称互换,执行附件20200729_3.sql
rename table cloud_box_workhour to cloud_box_workhour_copy2;
rename table cloud_box_workhour_detail to cloud_box_workhour_detail_copy2;
rename table cloud_box_workhour_copy to cloud_box_workhour;
rename table cloud_box_workhour_detail_copy to cloud_box_workhour_detail;

6.重启8084,8087,8089三个服务

线上观察

线上数据查询切换到cloudbox_data数据库,对系统数据进行验证观察,如果出现数据不正确,立马切换到cloudbox数据库,如果系统运行正常,数据正确,把三张表的数据写入切换到只操作cloudbox_data数据库。观察一周时间,没有出现问题,把cloudbox数据库中的三张表删除即可。

相关阅读  详解在Linux中同时安装配置并使用 MySQL5.7 和 MySQL8.0

归档历史

分库方案解决了业务库数据库的存储压力和访问压力,但对于cloudbox_data数据库来说,本身还是存在性能问题,大表的数据写入和更新较慢,服务的吞吐能力因此有打折扣,由于表基数比较大,单表的查询性能也不是很好。

这几张大表中存储的是设备的工作时长数据,从业务情况来看,用户一般比较关注的就是最近一个月的工作时长,比较久远的数据几乎不会有很多的查看,这些较早的工时数据也不再会发生改变,所以考虑对历史数据进行归档。

进行数据归档,主表仅保留最近一个季度的数据,大概是720万左右的数据量,一个季度之前的数据归档到历史数据表中。

归档操作

1、创建一个和原表一样的历史数据表(索引都保留)
2、选择一个读库,写一个存储过程,分批查询数据写入历史数据表中,在晚上数据库相对空闲的时候执行

应用改造

做一个开关配置,对历史数据的查询可以实现切换,如果线上查询归档表出现问题,可以回滚到原表进行查询,原表的数据还是完整的。
按照时间条件分情况:
1、对一个季度之前的工时数据查询,切换到历史数据表做按月查询
2、对最近一个季度的查询,切换到原表查询
3、如果有跨原表和历史表的时间窗口,就需要做下处理,将两个表的查询结果合并返回
注意:查询必须带上时间窗口,因为后端是按照时间窗口来判断要从哪张表查询数据。

数据删除

当系统运行正常后,就需要对原表中的数据进行删除,对原表中的数据按照时间进行分批删除,直到删除完毕仅保留最近一个季度的数据。

当原表中的历史数据删除完成,为了释放存储空间和优化索引结构,因为系统可以接受暂时停服,所以直接新建一个临时表,然后把原表复制到临时表中,再把原表改名,最后把临时表的表名改成正式表。这样,相当于手工把订单表重建了一次。大概操作流程如下:


-- 新建一个临时表
create table cloud_box_workhour_temp like cloud_box_workhour;


-- 把当前表复制到临时表中
insert into cloud_box_workhour_temp
  select * from cloud_box_workhour
  where timestamp >= SUBDATE(CURDATE(),INTERVAL 3 month);


-- 修改替换表名
rename table cloud_box_workhour to cloud_box_workhour_to_be_droppd, cloud_box_workhour_temp to cloud_box_workhour;


-- 删除旧表
drop table cloud_box_workhour_to_be_droppd

总结

大数据表的优化,处理思想就是一个“拆”字,要么拆一个新库,要么拆表。

如果并发量比较高,给整个业务数据库压力比较大,有可能会影响到所有的业务系统,这种情况一般选择拆分新库。

对于单表的大数据,优先选择归档历史数据,这种对应用代码的影响最小。其次考虑分表,分表需要按照实际的业务数据选择合适的分片策略,具体情况需要具体分析。


AD:【腾讯云服务器大降价】2核4G 222元/3年 1核2G 38元/年

本文来自一日成建站教程网https://www.yiricheng.cn/),为广大编程爱好者提供零基础快速搭建博客网站的方法,分享计算机网络、HTML、CSS、JavaScript、PHP、响应式设计以及Python自动化办公的博客文章,另有各类小工具、电子书、免费教程。 版权声明:本文知识版权归原作者所有,该文观点仅代表作者本人。本站仅提供信息存储空间服务,非用于商业用途,不拥有所有权,未对原文做任何修改,不承担相关法律责任,仅用于知识分享。如若本站内容侵犯了原著者的合法权益,可联系我们进行处理。 原文链接:https://segmentfault.com/a/1190000041785385