关于mysql:大数据表性能优化

背景

业务库中有三张表数据量比拟大，别离是 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;}
}

测试利用中可能失常的通过 cloudboxDataSource 数据源操作和拜访 cloudbox_data 数据库中的表。

数据双写

实现三张表的数据在 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

总结

大数据表的优化，解决思维就是一个“拆”字，要么拆一个新库，要么拆表。

如果并发量比拟高，给数据库压力比拟大，有可能会影响到整个零碎，这种状况个别抉择拆分新库，否则就没有必要拆库了。

对于单表的大数据，优先选择归档历史数据，这种对利用代码的影响最小。其次思考分表，分表须要依照理论的业务数据抉择适合的分片策略，具体情况须要具体分析。