这样分表，你还会觉得难么？

        在项目中，每当我们遇到因数据表记录过多导致读写缓慢，总会想到分库、分表等解决方案。而我在项目中，同样遇到了这样的情况，同时我想到了一种有趣的分表方案--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)));
}

}

执行结果后：