推荐书籍《金字塔原理》
本文使用的数据库是一主两从，实现数据分表，以及读写分离功能。
本文Java工程使用Maven搭建，基于SpringBoot框架，ORM框架使用Mybatis-Plus（建议自己先搭建下Demo工程）。
Sharding-JDBC支持Mybatis-Plus，不需要针对Mybatis-Plus进行额外的配置，只需要配置自身即可。
第1步：创建数据库表
先创建t_student表，然后再创建t_student_0 ... t_student_9 10张分表。
DROP TABLE if EXISTS t_student;
CREATE TABLE t_student (
`id` BIGINT NOT NULL auto_increment COMMENT '主键',
`name` VARCHAR(20) COMMENT '姓名',
`number` VARCHAR(10) COMMENT '学号',
PRIMARY KEY (`id`) USING BTREE,
INDEX `idx_number` (`number`) USING BTREE
) ENGINE=INNODB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 COLLATE = utf8_general_ci ROW_FORMAT=DYNAMIC COMMENT '学生表';
第2步：工程配置
pom.xml文件配置
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.7.6</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.devpotato.sharding-jdbc</groupId>
<artifactId>sharding-jdbc</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>sharding-jdbc</name>
<description>sharding-jdbc</description>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>3.5.2</version>
</dependency>
<!-- Sharding-jdbc的spring-boot依赖 -->
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-core</artifactId>
<version>4.1.1</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.21</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.25</version>
</dependency>
</dependencies>
</project>
application.yml文件配置
spring:
datasource:
type: com.alibaba.druid.pool.DruidDataSource
druid:
initial-size: 5
min-idle: 5
max-active: 20
max-wait: 60000
min-evictable-idle-time-millis: 300000
time-between-eviction-runs-millis: 60000
validation-query: SELECT 1
test-while-idle: true
test-on-borrow: false
test-on-return: false
pool-prepared-statements: true
max-pool-prepared-statement-per-connection-size: 20
connection-properties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=1000
default-auto-commit: true
jdbc:
ds_master_0:
driver: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://127.0.0.1:3306/test?useSSL=false&useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true
username: root
password: 12345678
ds_master_0_slave_0:
driver: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://127.0.0.1:3306/test?useSSL=false&useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true
username: root
password: 12345678
ds_master_0_slave_1:
driver: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://127.0.0.1:3306/test?useSSL=false&useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true
username: root
password: 12345678
mybatis-plus:
configuration:
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
第4步：Sharding-JDBC配置（基于JavaConfig格式配置）
- 使用 @ConfigurationProperties 读取datasource配置
@Data
@Component
@ConfigurationProperties(prefix = "spring.datasource.druid")
public class DataSourceProperties {
private Integer initialSize;
private Integer minIdle;
private Integer maxActive;
private Integer maxWait;
private Integer minEvictableIdleTimeMillis;
private Integer timeBetweenEvictionRunsMillis;
private String validationQuery;
private Boolean testWhileIdle;
private Boolean testOnBorrow;
private Boolean testOnReturn;
private Boolean poolPreparedStatements;
private Integer maxPoolPreparedStatementPerConnectionSize;
private String filter;
private String connectionProperties;
private Boolean defaultAutoCommit;
}
- 配置 Sharding-JDBC
@Configuration
public class ShardingJdbcConfiguration {
// ds_master_0
@Value("${jdbc.ds_master_0.driver}")
private String master0DriverName;
@Value("${jdbc.ds_master_0.url}")
private String master0Url;
@Value("${jdbc.ds_master_0.username}")
private String master0UserName;
@Value("${jdbc.ds_master_0.password}")
private String master0Password;
// ds_master_0_slave_0
@Value("${jdbc.ds_master_0_slave_0.driver}")
private String master0Slave0DriverName;
@Value("${jdbc.ds_master_0_slave_0.url}")
private String master0Slave0Url;
@Value("${jdbc.ds_master_0_slave_0.username}")
private String master0Slave0UserName;
@Value("${jdbc.ds_master_0_slave_0.password}")
private String master0Slave0Password;
// ds_master_0_slave_1
@Value("${jdbc.ds_master_0_slave_1.driver}")
private String master0Slave1DriverName;
@Value("${jdbc.ds_master_0_slave_1.url}")
private String master0Slave1Url;
@Value("${jdbc.ds_master_0_slave_1.username}")
private String master0Slave1UserName;
@Value("${jdbc.ds_master_0_slave_1.password}")
private String master0Slave1Password;
@Autowired
private DataSourceProperties dataSourceProperties;
@Bean
public DataSource getDataSource() throws SQLException {
// 配置分片规则（数据分片 + 读写分离）
ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
shardingRuleConfig.getTableRuleConfigs().add(getStudentTableRuleConfiguration());
shardingRuleConfig.setMasterSlaveRuleConfigs(getMasterSlaveRuleConfigurations());
return ShardingDataSourceFactory.createDataSource(createDataSourceMap(), shardingRuleConfig, getProperties());
}
// 配置读写分离规则
private List<MasterSlaveRuleConfiguration> getMasterSlaveRuleConfigurations() {
MasterSlaveRuleConfiguration masterSlaveRuleConfig1 = new MasterSlaveRuleConfiguration(
"ds_0",
"ds_master_0",
Arrays.asList("ds_master_0_slave_0", "ds_master_0_slave_1"));
return Lists.newArrayList(masterSlaveRuleConfig1);
}
private Map<String, DataSource> createDataSourceMap() throws SQLException {
Map<String, DataSource> dataSourceMap = new HashMap<>();
dataSourceMap.put("ds_master_0", getMasterDataSource());
dataSourceMap.put("ds_master_0_slave_0", getSlaveDataSource1());
dataSourceMap.put("ds_master_0_slave_1", getSlaveDataSource2());
return dataSourceMap;
}
// 配置t_student表规则（配置分库 + 分表策略）
// 基于number字段进行分表（10张表）
private TableRuleConfiguration getStudentTableRuleConfiguration() {
TableRuleConfiguration tableRuleConfiguration = new TableRuleConfiguration(
"t_student",
"ds_${0}.t_student_${0..9}");
tableRuleConfiguration.setTableShardingStrategyConfig(
new InlineShardingStrategyConfiguration(
"number",
"t_student_${(number.hashCode() & Integer.MAX_VALUE) % 10}"));
tableRuleConfiguration.setKeyGeneratorConfig(getKeyGeneratorConfiguration());
return tableRuleConfiguration;
}
// 主键的生成策略（雪花算法）
private KeyGeneratorConfiguration getKeyGeneratorConfiguration() {
KeyGeneratorConfiguration result = new KeyGeneratorConfiguration("SNOWFLAKE", "id");
return result;
}
private Properties getProperties() {
Properties properties = new Properties();
//是否打印SQL解析和改写日志
properties.put("sql.show", true);
return properties;
}
private DruidDataSource getMasterDataSource() throws SQLException {
DruidDataSource dataSource = getDruidDataSource();
dataSource.setUrl(master0Url);
dataSource.setDriverClassName(master0DriverName);
dataSource.setUsername(master0UserName);
dataSource.setPassword(master0Password);
return dataSource;
}
private DruidDataSource getSlaveDataSource1() throws SQLException {
DruidDataSource dataSource = getDruidDataSource();
dataSource.setUrl(master0Slave0Url);
dataSource.setDriverClassName(master0Slave0DriverName);
dataSource.setUsername(master0Slave0UserName);
dataSource.setPassword(master0Slave0Password);
return dataSource;
}
private DruidDataSource getSlaveDataSource2() throws SQLException {
DruidDataSource dataSource = getDruidDataSource();
dataSource.setUrl(master0Slave1Url);
dataSource.setDriverClassName(master0Slave1DriverName);
dataSource.setUsername(master0Slave1UserName);
dataSource.setPassword(master0Slave1Password);
return dataSource;
}
private DruidDataSource getDruidDataSource() throws SQLException {
DruidDataSource dataSource = new DruidDataSource();
dataSource.setInitialSize(dataSourceProperties.getInitialSize());
dataSource.setMinIdle(dataSourceProperties.getMinIdle());
dataSource.setMaxActive(dataSourceProperties.getMaxActive());
dataSource.setMaxWait(dataSourceProperties.getMaxWait());
dataSource.setTimeBetweenEvictionRunsMillis(dataSourceProperties.getTimeBetweenEvictionRunsMillis());
dataSource.setMinEvictableIdleTimeMillis(dataSourceProperties.getMinEvictableIdleTimeMillis());
dataSource.setValidationQuery(dataSourceProperties.getValidationQuery());
dataSource.setTestWhileIdle(dataSourceProperties.getTestWhileIdle());
dataSource.setTestOnBorrow(dataSourceProperties.getTestOnBorrow());
dataSource.setTestOnReturn(dataSourceProperties.getTestOnReturn());
dataSource.setPoolPreparedStatements(dataSourceProperties.getPoolPreparedStatements());
dataSource.setMaxPoolPreparedStatementPerConnectionSize(dataSourceProperties.getMaxPoolPreparedStatementPerConnectionSize());
dataSource.setFilters(dataSourceProperties.getFilter());
dataSource.setConnectionProperties(dataSourceProperties.getConnectionProperties());
dataSource.setDefaultAutoCommit(dataSourceProperties.getDefaultAutoCommit());
return dataSource;
}
}
参考
https://shardingsphere.apache.org/document/4.1.1/cn/overview/
