这样分表,你还会觉得难么?

原创 先哭个一天再笑     发表于  2021-03-17 01:14       181

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

/**
*
自动创建分表
*@param
maySubTable
*/
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<>());
}
}

/**
*
添加数据
*@param
model
*/
public voidinsert(DeviceDataListModel model) {
this.dataListDAO.insert(model);
}

/**
*
查询
*@param
provider
*@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<>();

/**
*
组装名称
*@param
tableName
*@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;
}

/**
*
反名称
*@param
tableName
*@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());
}

/**
*
判断分表是否创建
*@param
maySubTable
*@paramnext
*/
public static voidifAbsent(String maySubTable,Consumer<Boolean> next) {
if(!EXITS_MAP.containsKey(maySubTable)) {
next. accept(true);
}
}

/**
*
缓存表key
*@param
maySubTable
*/
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)));
}

}

 

 

执行结果后:

 

先哭个一天再笑
文章数 2 阅读量 293
广告

更多头条文章

关注"Obatis资讯"公众号