在项目中,每当我们遇到因数据表记录过多导致读写缓慢,总会想到分库、分表等解决方案。而我在项目中,同样遇到了这样的情况,同时我想到了一种有趣的分表方案--1::N分表策略。
1::N分表策略,适用于“N“表数据量过大导致的读写缓慢。直接上代码,基于Obatis ORM框架:
首先创建我们使用到的数据表:
CREATE TABLE `device_info` (
`id` decimal(28,0) NOT NULL COMMENT 'id',
`create_time` datetime NOT NULL COMMENT '创建时间',
`device_name` varchar(60) COLLATE utf8_bin NOT NULL COMMENT '设备名称',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='设备信息表';
CREATE TABLE `device_data_list` (
`id` decimal(28,0) NOT NULL COMMENT 'id',
`create_time` datetime NOT NULL COMMENT '创建时间',
`device_id` decimal(28,0) NOT NULL COMMENT '设备ID',
`h_value` int(5) NOT NULL COMMENT '高位值',
`other_values` varchar(100) COLLATE utf8_bin NOT NULL COMMENT '其他数据',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='设备数据列表';
Pom.xml:
<dependencies>
<dependency>
<groupId>com.obatis</groupId>
<artifactId>obatis-core</artifactId>
<version>2.2.1-release</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<version>2.2.4.RELEASE</version>
<scope>test</scope>
</dependency>
</dependencies>
项目结构:
在这里我们主要正对设备数据列表,根据obatis规范创建model后,又独立编写了part dao。
packagecom.csm.test.dao;
importcom.csm.test.model.DeviceDataListModel;
importcom.obatis.core.SubTable;
importcom.obatis.core.sql.QueryProvider;
importorg.springframework.stereotype.Repository;
importjavax.annotation.Resource;
importjava.util.*;
/**
*@Authchen shunmu
*@Date2021/3/16-下午10:28
*@ClassNameDeviceDataListSubDAO
*/
@Repository
public classDeviceDataListPartDAO {
@Resource
privateDeviceDataListDAOdataListDAO;
/**
*获取对应表的mapper
*@paramsubTable
*@return
*/
publicDeviceDataListPartDAOsubTable(Object subTable) {
String maySubTable = SubTable.fitSubTable(dataListDAO.getSourceTableName(),Objects.toString(subTable));
SubTable.ifAbsent(maySubTable,(res) -> {
this.createNotExist(maySubTable);
SubTable.add(maySubTable);
});
dataListDAO.setTableName(maySubTable);
return this;
}
/**
*自动创建分表
*@parammaySubTable
*/
private voidcreateNotExist(String maySubTable) {
Map<String,Object> mapBySql =this.dataListDAO.findMapBySql("SELECT COUNT(*) as exist FROM information_schema.TABLES WHERE table_name ='"+ maySubTable +"'", newArrayList<>());
if(Integer.parseInt(Objects.toString(mapBySql.get("exist"))) ==0) {
Map<String,Object> create =this.dataListDAO.findMapBySql("show create table "+dataListDAO.getSourceTableName(), newArrayList<>());
String create_table = create.get("Create Table").toString();
create_table = create_table.replace("`"+dataListDAO.getSourceTableName() +"`","`"+ maySubTable +"`");
this.dataListDAO.findMapBySql(create_table, newArrayList<>());
}
}
/**
*添加数据
*@parammodel
*/
public voidinsert(DeviceDataListModel model) {
this.dataListDAO.insert(model);
}
/**
*查询
*@paramprovider
*@return
*/
publicList<DeviceDataListModel>list(QueryProvider provider) {
return this.dataListDAO.list(provider);
}
}
SubTable是我们辅助的一个工具类,其中起到分表表名称策略和缓存操作记录:
packagecom.obatis.core;
importcom.obatis.tools.ValidateTool;
importjava.util.HashMap;
importjava.util.Map;
importjava.util.function.Consumer;
/**
*分表策略
*@Authchen shunmu
*@Date2020/12/24-上午12:04
*@ClassNameSubTable
*/
public classSubTable {
/**
*分表组装标识符
*/
public static finalStringSUB_TABLE_PREFIX="z_%s_sub_ii$";
public static finalStringSUB_TABLE_SPECIAL_END="_ii$";
/**
*缓存已创建的表
*/
private static finalMap<String,Object>EXITS_MAP=newHashMap<>();
/**
*组装名称
*@paramtableName
*@paramsubTable
*@return
*/
public staticStringfitSubTable(String tableName,String subTable) {
if(tableName.contains(SUB_TABLE_SPECIAL_END)) {
returntableName;
}
if(!ValidateTool.isEmpty(subTable)) {
returnString.format(SUB_TABLE_PREFIX,subTable) + tableName;
}
returntableName;
}
/**
*反名称
*@paramtableName
*@return
*/
public staticStringritSubTable(String tableName) {
if(!tableName.contains(SUB_TABLE_SPECIAL_END)) {
returntableName;
}
returntableName.substring(tableName.indexOf(SUB_TABLE_SPECIAL_END) +SUB_TABLE_SPECIAL_END.length());
}
/**
*判断分表是否创建
*@parammaySubTable
*@paramnext
*/
public static voidifAbsent(String maySubTable,Consumer<Boolean> next) {
if(!EXITS_MAP.containsKey(maySubTable)) {
next. accept(true);
}
}
/**
*缓存表key
*@parammaySubTable
*/
public static voidadd(String maySubTable) {
EXITS_MAP.put(maySubTable,"");
}
}
以下是我们注重编写的service代码:
packagecom.csm.test.service.impl;
importcom.csm.test.dao.DeviceDataListPartDAO;
importcom.csm.test.model.DeviceDataListModel;
importcom.csm.test.service.IDeviceDataListService;
importcom.obatis.core.sql.QueryProvider;
importorg.springframework.stereotype.Service;
importjavax.annotation.Resource;
importjava.math.BigInteger;
importjava.util.List;
/**
*设备数据业务实现
*@Authchen shunmu
*@Date2021/3/16-下午10:26
*@ClassNameDeviceDataListServiceImpl
*/
@Service
public classDeviceDataListServiceImplimplementsIDeviceDataListService {
@Resource
privateDeviceDataListPartDAOdataListPartDAO;
@Override
publicList<DeviceDataListModel>list(BigInteger deviceId) {
returndataListPartDAO.subTable(deviceId).list(newQueryProvider());
}
@Override
public voidinsert(BigInteger deviceId, inthValue,String otherValues) {
DeviceDataListModel model =newDeviceDataListModel();
model.setDeviceId(deviceId);
model.sethValue(hValue);
model.setOtherValues(otherValues);
this.dataListPartDAO.subTable(deviceId).insert(model);
}
}
以上,每当我们执行添加或者查询时,都会根据表结构和分表策略生成新的表。
以下为测试单元:
packagecom.csm.test;
importcom.alibaba.fastjson.JSON;
importcom.csm.test.model.DeviceDataListModel;
importcom.csm.test.service.IDeviceDataListService;
importcom.obatis.generator.NumberGenerator;
importorg.junit.Before;
importorg.junit.Test;
importorg.junit.runner.RunWith;
importorg.springframework.boot.test.context.SpringBootTest;
importorg.springframework.test.context.junit4.SpringRunner;
importjavax.annotation.Resource;
importjava.math.BigInteger;
importjava.util.ArrayList;
importjava.util.List;
importjava.util.UUID;
/**
*@Authchen shunmu
*@Date2021/3/15-下午11:05
*@ClassNameTestStart
*/
@SpringBootTest(classes= Test1NStart.class)
@RunWith(value= SpringRunner.class)
public classTestStart {
@Resource
privateIDeviceDataListServiceservice;
privateList<BigInteger>deviceIds=newArrayList<>();
@Before
public voidbeSet() {
longstartTime = System.currentTimeMillis();
for(inti =0;i <10;i++) {
deviceIds.add(NumberGenerator.getNumber());
}
longendTime = System.currentTimeMillis();
System.out.println(String.format(" create 1000number times:%d",(endTime - startTime)));
}
@Test
public voidtest() {
for(BigInteger deviceId :deviceIds) {
intcount = (int) (Math.random() *1000);
for(inti =0;i < count;i++) {
service.insert(deviceId,count,UUID.randomUUID().toString());
}
}
longstartTime = System.currentTimeMillis();
List<DeviceDataListModel> list =service.list(deviceIds.get(0));
longendTime = System.currentTimeMillis();
System.out.println(JSON.toJSONString(list));
System.out.println(String.format(" query times:%d",(endTime - startTime)));
}
}
执行结果后: