likes
comments
collection
share

大数据表优化（一）：分库

作者站长头像
站长
· 阅读数 41

背景

业务库中有三张表数据量比较大，分别是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业务库中删除三张大表，拆分示意图如下：

大数据表优化（一）：分库

多数据源

使用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数据库上。如图：

大数据表优化（一）：分库

数据迁移

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数据库中的三张表删除即可。

归档历史

分库方案解决了业务库数据库的存储压力和访问压力，但对于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

总结

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

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

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