因产品推广发展,业务对数据库的访问压力变大(数据库为虚拟机),单表已无力支撑,综合考虑对主要的大表做了分库分表。本文简要介绍分库分表实现过程。
一、分库分表技术选型
数据库使用的是MySQL,分库分表方式最终从“基于Mycat中间件分库分表”和“基于ShardingJDBC分库分表”中二选一。因业务目标TPS较高,考虑到Mycat本身也可能成为分库分表的瓶颈(访问模型如图),最终采用了ShardingJDBC做分库分表。
二、设计实现
1.拆分设计
业务表中量不大的表,例如不超过百万记录的表,不做分库分表,固定在一个分库的表中。
业务表中量大的表做分库分表,此处以一张表为例。根据极限目标数据存储量100亿、单表存放最多1000万数据等考虑,拆分成128张表,因服务器有限,MySQL采用4主4从的部署方式,每台MySQL部署4个库(后续也可拆分成8主8从或16主16从,只需迁移数据及更新应用配置的访问数据库URL即可),每个库中分8张表,共计4*4*8=128张表。
2.分库分表实现
注:项目框架为SpringCloud,DAO层框架为Mybatis,数据库连接采用的Druid。
(1)建库建表
原单库表中不需要分库分表的表不做改动(确保最小改动),作为默认库表(假定原名为db),另建15个MySQL分库(如命名为db1 ~ db15),合计16个MySQL库。每个库中建8张分表(如命名为device0 ~ device7)。
(2)主要引入ShardingJDBC工具包
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.1.1</version>
</dependency>
(3)分库配置
spring:
shardingsphere:
datasource:
names: db,db1,db2,db3,db4,db5,db6,db7,db8,db9,db10,db11,db12,db13,db14,db15
db0:
driver-class-name: com.mysql.jdbc.Driver
username: xxxx
password: '{cipher}xxxxxxxxxxx'
url: jdbc:mysql://{MySQL1的IP:端口}/db?zeroDateTimeBehavior=convertToNull&useUnicode=true&characterEncoding=utf8&useSSL=false
type: com.alibaba.druid.pool.DruidDataSource
initialSize: 4
minIdle: 8
maxActive: 128
maxWait: 1500
timeBetweenEvictionRunsMillis: 60000
minEvictableIdleTimeMillis: 300000
validationQuery: SELECT 1 FROM DUAL
testWhileIdle: true
testOnBorrow: false
testOnReturn: false
sql-script-encoding: UTF-8
stat-view-servlet:
enabled: false
db1:
driver-class-name: com.mysql.jdbc.Driver
username: xxxx
password: '{cipher}xxxxxxxxxxx'
url: jdbc:mysql://{MySQL1的IP:端口}/db1?zeroDateTimeBehavior=convertToNull&useUnicode=true&characterEncoding=utf8&useSSL=false
type: com.alibaba.druid.pool.DruidDataSource
initialSize: 4
minIdle: 8
maxActive: 128
maxWait: 1500
timeBetweenEvictionRunsMillis: 60000
minEvictableIdleTimeMillis: 300000
validationQuery: SELECT 1 FROM DUAL
testWhileIdle: true
testOnBorrow: false
testOnReturn: false
sql-script-encoding: UTF-8
stat-view-servlet:
enabled: false
db2:
driver-class-name: com.mysql.jdbc.Driver
username: xxxx
password: '{cipher}xxxxxxxxxxx'
url: jdbc:mysql://{MySQL1的IP:端口}/db2?zeroDateTimeBehavior=convertToNull&useUnicode=true&characterEncoding=utf8&useSSL=false
type: com.alibaba.druid.pool.DruidDataSource
initialSize: 4
minIdle: 8
maxActive: 128
maxWait: 1500
timeBetweenEvictionRunsMillis: 60000
minEvictableIdleTimeMillis: 300000
validationQuery: SELECT 1 FROM DUAL
testWhileIdle: true
testOnBorrow: false
testOnReturn: false
sql-script-encoding: UTF-8
stat-view-servlet:
enabled: false
db3:
driver-class-name: com.mysql.jdbc.Driver
username: xxxx
password: '{cipher}xxxxxxxxxxx'
url: jdbc:mysql://{MySQL1的IP:端口}/db3?zeroDateTimeBehavior=convertToNull&useUnicode=true&characterEncoding=utf8&useSSL=false
type: com.alibaba.druid.pool.DruidDataSource
initialSize: 4
minIdle: 8
maxActive: 128
maxWait: 1500
timeBetweenEvictionRunsMillis: 60000
minEvictableIdleTimeMillis: 300000
validationQuery: SELECT 1 FROM DUAL
testWhileIdle: true
testOnBorrow: false
testOnReturn: false
sql-script-encoding: UTF-8
stat-view-servlet:
enabled: false
db4:
driver-class-name: com.mysql.jdbc.Driver
username: xxxx
password: '{cipher}xxxxxxxxxxx'
url: jdbc:mysql://{MySQL2的IP:端口}/db4?zeroDateTimeBehavior=convertToNull&useUnicode=true&characterEncoding=utf8&useSSL=false
type: com.alibaba.druid.pool.DruidDataSource
initialSize: 4
minIdle: 8
maxActive: 128
maxWait: 1500
timeBetweenEvictionRunsMillis: 60000
minEvictableIdleTimeMillis: 300000
validationQuery: SELECT 1 FROM DUAL
testWhileIdle: true
testOnBorrow: false
testOnReturn: false
sql-script-encoding: UTF-8
stat-view-servlet:
enabled: false
db5:
driver-class-name: com.mysql.jdbc.Driver
username: xxxx
password: '{cipher}xxxxxxxxxxx'
url: jdbc:mysql://{MySQL2的IP:端口}/db5?zeroDateTimeBehavior=convertToNull&useUnicode=true&characterEncoding=utf8&useSSL=false
type: com.alibaba.druid.pool.DruidDataSource
initialSize: 4
minIdle: 8
maxActive: 128
maxWait: 1500
timeBetweenEvictionRunsMillis: 60000
minEvictableIdleTimeMillis: 300000
validationQuery: SELECT 1 FROM DUAL
testWhileIdle: true
testOnBorrow: false
testOnReturn: false
sql-script-encoding: UTF-8
stat-view-servlet:
enabled: false
db6:
driver-class-name: com.mysql.jdbc.Driver
username: xxxx
password: '{cipher}xxxxxxxxxxx'
url: jdbc:mysql://{MySQL2的IP:端口}/db6?zeroDateTimeBehavior=convertToNull&useUnicode=true&characterEncoding=utf8&useSSL=false
type: com.alibaba.druid.pool.DruidDataSource
initialSize: 4
minIdle: 8
maxActive: 128
maxWait: 1500
timeBetweenEvictionRunsMillis: 60000
minEvictableIdleTimeMillis: 300000
validationQuery: SELECT 1 FROM DUAL
testWhileIdle: true
testOnBorrow: false
testOnReturn: false
sql-script-encoding: UTF-8
stat-view-servlet:
enabled: false
db7:
driver-class-name: com.mysql.jdbc.Driver
username: xxxx
password: '{cipher}xxxxxxxxxxx'
url: jdbc:mysql://{MySQL2的IP:端口}/db7?zeroDateTimeBehavior=convertToNull&useUnicode=true&characterEncoding=utf8&useSSL=false
type: com.alibaba.druid.pool.DruidDataSource
initialSize: 4
minIdle: 8
maxActive: 128
maxWait: 1500
timeBetweenEvictionRunsMillis: 60000
minEvictableIdleTimeMillis: 300000
validationQuery: SELECT 1 FROM DUAL
testWhileIdle: true
testOnBorrow: false
testOnReturn: false
sql-script-encoding: UTF-8
stat-view-servlet:
enabled: false
db8:
driver-class-name: com.mysql.jdbc.Driver
username: xxxx
password: '{cipher}xxxxxxxxxxx'
url: jdbc:mysql://{MySQL3的IP:端口}/db8?zeroDateTimeBehavior=convertToNull&useUnicode=true&characterEncoding=utf8&useSSL=false
type: com.alibaba.druid.pool.DruidDataSource
initialSize: 4
minIdle: 8
maxActive: 128
maxWait: 1500
timeBetweenEvictionRunsMillis: 60000
minEvictableIdleTimeMillis: 300000
validationQuery: SELECT 1 FROM DUAL
testWhileIdle: true
testOnBorrow: false
testOnReturn: false
sql-script-encoding: UTF-8
stat-view-servlet:
enabled: false
db9:
driver-class-name: com.mysql.jdbc.Driver
username: xxxx
password: '{cipher}xxxxxxxxxxx'
url: jdbc:mysql://{MySQL3的IP:端口}/db9?zeroDateTimeBehavior=convertToNull&useUnicode=true&characterEncoding=utf8&useSSL=false
type: com.alibaba.druid.pool.DruidDataSource
initialSize: 4
minIdle: 8
maxActive: 128
maxWait: 1500
timeBetweenEvictionRunsMillis: 60000
minEvictableIdleTimeMillis: 300000
validationQuery: SELECT 1 FROM DUAL
testWhileIdle: true
testOnBorrow: false
testOnReturn: false
sql-script-encoding: UTF-8
stat-view-servlet:
enabled: false
db10:
driver-class-name: com.mysql.jdbc.Driver
username: xxxx
password: '{cipher}xxxxxxxxxxx'
url: jdbc:mysql://{MySQL3的IP:端口}/db10?zeroDateTimeBehavior=convertToNull&useUnicode=true&characterEncoding=utf8&useSSL=false
type: com.alibaba.druid.pool.DruidDataSource
initialSize: 4
minIdle: 8
maxActive: 128
maxWait: 1500
timeBetweenEvictionRunsMillis: 60000
minEvictableIdleTimeMillis: 300000
validationQuery: SELECT 1 FROM DUAL
testWhileIdle: true
testOnBorrow: false
testOnReturn: false
sql-script-encoding: UTF-8
stat-view-servlet:
enabled: false
db11:
driver-class-name: com.mysql.jdbc.Driver
username: xxxx
password: '{cipher}xxxxxxxxxxx'
url: jdbc:mysql://{MySQL3的IP:端口}/db11?zeroDateTimeBehavior=convertToNull&useUnicode=true&characterEncoding=utf8&useSSL=false
type: com.alibaba.druid.pool.DruidDataSource
initialSize: 4
minIdle: 8
maxActive: 128
maxWait: 1500
timeBetweenEvictionRunsMillis: 60000
minEvictableIdleTimeMillis: 300000
validationQuery: SELECT 1 FROM DUAL
testWhileIdle: true
testOnBorrow: false
testOnReturn: false
sql-script-encoding: UTF-8
stat-view-servlet:
enabled: false
db12:
driver-class-name: com.mysql.jdbc.Driver
username: xxxx
password: '{cipher}xxxxxxxxxxx'
url: jdbc:mysql://{MySQL4的IP:端口}/db12?zeroDateTimeBehavior=convertToNull&useUnicode=true&characterEncoding=utf8&useSSL=false
type: com.alibaba.druid.pool.DruidDataSource
initialSize: 4
minIdle: 8
maxActive: 128
maxWait: 1500
timeBetweenEvictionRunsMillis: 60000
minEvictableIdleTimeMillis: 300000
validationQuery: SELECT 1 FROM DUAL
testWhileIdle: true
testOnBorrow: false
testOnReturn: false
sql-script-encoding: UTF-8
stat-view-servlet:
enabled: false
db13:
driver-class-name: com.mysql.jdbc.Driver
username: xxxx
password: '{cipher}xxxxxxxxxxx'
url: jdbc:mysql://{MySQL4的IP:端口}/db13?zeroDateTimeBehavior=convertToNull&useUnicode=true&characterEncoding=utf8&useSSL=false
type: com.alibaba.druid.pool.DruidDataSource
initialSize: 4
minIdle: 8
maxActive: 128
maxWait: 1500
timeBetweenEvictionRunsMillis: 60000
minEvictableIdleTimeMillis: 300000
validationQuery: SELECT 1 FROM DUAL
testWhileIdle: true
testOnBorrow: false
testOnReturn: false
sql-script-encoding: UTF-8
stat-view-servlet:
enabled: false
db14:
driver-class-name: com.mysql.jdbc.Driver
username: xxxx
password: '{cipher}xxxxxxxxxxx'
url: jdbc:mysql://{MySQL4的IP:端口}/db14?zeroDateTimeBehavior=convertToNull&useUnicode=true&characterEncoding=utf8&useSSL=false
type: com.alibaba.druid.pool.DruidDataSource
initialSize: 4
minIdle: 8
maxActive: 128
maxWait: 1500
timeBetweenEvictionRunsMillis: 60000
minEvictableIdleTimeMillis: 300000
validationQuery: SELECT 1 FROM DUAL
testWhileIdle: true
testOnBorrow: false
testOnReturn: false
sql-script-encoding: UTF-8
stat-view-servlet:
enabled: false
db15:
driver-class-name: com.mysql.jdbc.Driver
username: xxxx
password: '{cipher}xxxxxxxxxxx'
url: jdbc:mysql://{MySQL4的IP:端口}/db15?zeroDateTimeBehavior=convertToNull&useUnicode=true&characterEncoding=utf8&useSSL=false
type: com.alibaba.druid.pool.DruidDataSource
initialSize: 4
minIdle: 8
maxActive: 128
maxWait: 1500
timeBetweenEvictionRunsMillis: 60000
minEvictableIdleTimeMillis: 300000
validationQuery: SELECT 1 FROM DUAL
testWhileIdle: true
testOnBorrow: false
testOnReturn: false
sql-script-encoding: UTF-8
stat-view-servlet:
enabled: false
(4)分片策略
分片原理:根据需要操作的字段devId hash到对应的分库号(0 ~ 15)即找到对应的分库,再根据devIdhash到对应的表号(0 ~ 7)即找到对应的表。
分库规则:(int)(Math.abs(devId.hashCode()) % 128 / 8),即将待操作的字段hashCode绝对值与128取余,得到0~127间的值,再除以8,则得到0~15间的值,即分库号。
分表规则:(int)(Math.abs(devId.hashCode()) % 128 % 8),即将待操作的字段hashCode绝对值与128取余,得到0~127间的值,再与8取余,则得到0~7间的值,即分表号。
示例采用的注解方式注入数据源,详细代码如下:
import java.io.IOException;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;
import javax.sql.DataSource;
import org.apache.shardingsphere.api.config.sharding.ShardingRuleConfiguration;
import org.apache.shardingsphere.api.config.sharding.TableRuleConfiguration;
import org.apache.shardingsphere.api.config.sharding.strategy.InlineShardingStrategyConfiguration;
import org.apache.shardingsphere.shardingjdbc.api.ShardingDataSourceFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.support.ResourcePatternResolver;
import com.alibaba.druid.pool.DruidDataSource;
import com.google.common.collect.Lists;
/**
* @author magiczrl@foxmail.com
* @date 2020年3月4日 下午9:08:04
* @version
*/
@Configuration
@MapperScan(basePackages = "com.cn.iot.dao.mapper.report", sqlSessionTemplateRef = "sqlSessionTemplate")
public class Dal {
@Autowired
private ResourcePatternResolver resourceLoader;
/**
* 数据库分片规则
* @return
*/
@Bean(name = "shardingRuleConfig")
//@ConfigurationProperties(prefix = "spring.shardingsphere.sharding.tables")
public ShardingRuleConfiguration shardingRuleConfig() {
/*
* 配置device表分片规则 - 16个分库(db0 ~ db15),每个库中分8张表(device0 ~ device7)
*/
TableRuleConfiguration deviceTableRuleConfig = new TableRuleConfiguration("device",
"db${0..15}.device${0..7}");
// 配置分库 + 分表策略
deviceTableRuleConfig
.setDatabaseShardingStrategyConfig(new InlineShardingStrategyConfiguration("devId",
"db${(int)(Math.abs(devId.hashCode()) % 128 / 8)}"));
deviceTableRuleConfig
.setTableShardingStrategyConfig(new InlineShardingStrategyConfiguration("devId",
"device${(int)(Math.abs(devId.hashCode()) % 128 % 8)}"));
ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
// 设置默认数据源
shardingRuleConfig.setDefaultDataSourceName("db0");
shardingRuleConfig.setTableRuleConfigs(Lists.newArrayList(gatewayTableRuleConfig,
macIndexTableRuleConfig, deviceTableRuleConfig));
return shardingRuleConfig;
}
//---------------------------------默认分库db0---------------------------------------
/**
* 默认数据库 db0
* @return
*/
@Bean(name = "dataSource0", destroyMethod = "close")
@Primary
@ConfigurationProperties(prefix = "spring.shardingsphere.datasource.db0")
public DataSource druid() {
return new DruidDataSource();
}
//---------------------------------分库db 1 ~ 15---------------------------------------
/**
* db1
* @return
*/
@Bean(name = "dataSource1", destroyMethod = "close")
@ConfigurationProperties(prefix = "spring.shardingsphere.datasource.db1")
public DataSource druid1() {
return new DruidDataSource();
}
/**
* @return
*/
@Bean(name = "dataSource2", destroyMethod = "close")
@ConfigurationProperties(prefix = "spring.shardingsphere.datasource.db2")
public DataSource druid2() {
return new DruidDataSource();
}
/**
* @return
*/
@Bean(name = "dataSource3", destroyMethod = "close")
@ConfigurationProperties(prefix = "spring.shardingsphere.datasource.db3")
public DataSource druid3() {
return new DruidDataSource();
}
/**
* @return
*/
@Bean(name = "dataSource4", destroyMethod = "close")
@ConfigurationProperties(prefix = "spring.shardingsphere.datasource.db4")
public DataSource druid4() {
return new DruidDataSource();
}
/**
* @return
*/
@Bean(name = "dataSource5", destroyMethod = "close")
@ConfigurationProperties(prefix = "spring.shardingsphere.datasource.db5")
public DataSource druid5() {
return new DruidDataSource();
}
/**
* @return
*/
@Bean(name = "dataSource6", destroyMethod = "close")
@ConfigurationProperties(prefix = "spring.shardingsphere.datasource.db6")
public DataSource druid6() {
return new DruidDataSource();
}
/**
* @return
*/
@Bean(name = "dataSource7", destroyMethod = "close")
@ConfigurationProperties(prefix = "spring.shardingsphere.datasource.db7")
public DataSource druid7() {
return new DruidDataSource();
}
/**
* @return
*/
@Bean(name = "dataSource8", destroyMethod = "close")
@ConfigurationProperties(prefix = "spring.shardingsphere.datasource.db8")
public DataSource druid8() {
return new DruidDataSource();
}
/**
* @return
*/
@Bean(name = "dataSource9", destroyMethod = "close")
@ConfigurationProperties(prefix = "spring.shardingsphere.datasource.db9")
public DataSource druid9() {
return new DruidDataSource();
}
/**
* @return
*/
@Bean(name = "dataSource10", destroyMethod = "close")
@ConfigurationProperties(prefix = "spring.shardingsphere.datasource.db10")
public DataSource druid10() {
return new DruidDataSource();
}
/**
* @return
*/
@Bean(name = "dataSource11", destroyMethod = "close")
@ConfigurationProperties(prefix = "spring.shardingsphere.datasource.db11")
public DataSource druid11() {
return new DruidDataSource();
}
/**
* @return
*/
@Bean(name = "dataSource12", destroyMethod = "close")
@ConfigurationProperties(prefix = "spring.shardingsphere.datasource.db12")
public DataSource druid12() {
return new DruidDataSource();
}
/**
* @return
*/
@Bean(name = "dataSource13", destroyMethod = "close")
@ConfigurationProperties(prefix = "spring.shardingsphere.datasource.db13")
public DataSource druid13() {
return new DruidDataSource();
}
/**
* @return
*/
@Bean(name = "dataSource14", destroyMethod = "close")
@ConfigurationProperties(prefix = "spring.shardingsphere.datasource.db14")
public DataSource druid14() {
return new DruidDataSource();
}
/**
* @return
*/
@Bean(name = "dataSource15", destroyMethod = "close")
@ConfigurationProperties(prefix = "spring.shardingsphere.datasource.db15")
public DataSource druid15() {
return new DruidDataSource();
}
/**
*
* @param dataSource0
* @param dataSource1
* @param dataSource2
* @param dataSource3
* @param dataSource4
* @param dataSource5
* @param dataSource6
* @param dataSource7
* @param dataSource8
* @param dataSource9
* @param dataSource10
* @param dataSource11
* @param dataSource12
* @param dataSource13
* @param dataSource14
* @param dataSource15
* @param shardingRuleConfig
* @return
* @throws SQLException
*/
@Bean(name = "buildDataSource")
public DataSource buildDataSource(@Qualifier("dataSource0") DataSource dataSource0,
@Qualifier("dataSource1") DataSource dataSource1,
@Qualifier("dataSource2") DataSource dataSource2,
@Qualifier("dataSource3") DataSource dataSource3,
@Qualifier("dataSource4") DataSource dataSource4,
@Qualifier("dataSource5") DataSource dataSource5,
@Qualifier("dataSource6") DataSource dataSource6,
@Qualifier("dataSource7") DataSource dataSource7,
@Qualifier("dataSource8") DataSource dataSource8,
@Qualifier("dataSource9") DataSource dataSource9,
@Qualifier("dataSource10") DataSource dataSource10,
@Qualifier("dataSource11") DataSource dataSource11,
@Qualifier("dataSource12") DataSource dataSource12,
@Qualifier("dataSource13") DataSource dataSource13,
@Qualifier("dataSource14") DataSource dataSource14,
@Qualifier("dataSource15") DataSource dataSource15,
@Qualifier("shardingRuleConfig") ShardingRuleConfiguration shardingRuleConfig)
throws SQLException {
Map<String, DataSource> dataSourceMap = new HashMap<String, DataSource>();
dataSourceMap.put("db0", dataSource0);
dataSourceMap.put("db1", dataSource1);
dataSourceMap.put("db2", dataSource2);
dataSourceMap.put("db3", dataSource3);
dataSourceMap.put("db4", dataSource4);
dataSourceMap.put("db5", dataSource5);
dataSourceMap.put("db6", dataSource6);
dataSourceMap.put("db7", dataSource7);
dataSourceMap.put("db8", dataSource8);
dataSourceMap.put("db9", dataSource9);
dataSourceMap.put("db10", dataSource10);
dataSourceMap.put("db11", dataSource11);
dataSourceMap.put("db12", dataSource12);
dataSourceMap.put("db13", dataSource13);
dataSourceMap.put("db14", dataSource14);
dataSourceMap.put("db15", dataSource15);
return ShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfig,
new Properties());
}
/**
*
* @param buildDataSource
* @return
* @throws Exception
*/
@Bean(name = "sqlSessionTemplate")
@Primary
public SqlSessionTemplate sqlSessionTemplate(@Qualifier("buildDataSource") DataSource buildDataSource)
throws Exception {
SqlSessionFactoryBean factory = new SqlSessionFactoryBean();
factory.setDataSource(buildDataSource);
factory.setConfigLocation(
resourceLoader.getResource("classpath:mybatis/sqlmap-config.xml"));
try {
factory.setMapperLocations(
resourceLoader.getResources("classpath*:mybatis/*Mapper.xml"));
} catch (IOException never) {
throw new RuntimeException(never);
}
factory.afterPropertiesSet();
SqlSessionTemplate template = new SqlSessionTemplate(factory.getObject());
return template;
}
}
总结
基于ShardingJDBC分库分表需要结合业务进行设计实现,在性能上相比Mycat有优势,主要是不用担心Mycat瓶颈问题,但有少量代码改造。关于分片策略,本文仅采用了行表达式分片策略,实际的分片策略还有多种,具体可参考官网http://shardingsphere.apache.org/index_zh.html。