参考:
https://mp.weixin.qq.com/s/A6WS1CSjF7wvBE_gKLyp8w
https://shardingsphere.apache.org/document/legacy/4.x/document/cn/quick-start/sharding-jdbc-quick-start/
注意:
支持的sql项:
全面支持DML、DDL、DCL、TCL和部分DAL。支持分页、去重、排序、分组、聚合、关联查询(不支持跨库关联)
不支持sql项:
不支持CASE WHEN、HAVING、UNION (ALL),有限支持子查询
提示:
对于使用 jion on语法时,建议采用绑定表(指分片规则一致的主表和子表)
比如 user表(user_1, user_2)
和user_detail表(user_detail_1, user_detail_2),
两个的分片键都是 user_id,
那么查询的时候sql就会是
user_1 left jion user_detail_1 on u.id = d.id
user_2 left jion user_detail_2 on u.id = d.id
如果不是绑定表,那就是两个表的笛卡尔积(浪费查询,且参数需要两表都设置,不然会冗余,具体可查看下面的测试部分——表关联测试示例)
user_1 * user_detail_1,
user_1 * user_detail_2,
user_2 * user_detail_1,
user_2 * user_detail_2
常用功能:
(1)数据分片
(2)读写分离(主库仅仅支持单主库,从库支持多个)
是属于代理层框架,我们跟之前mybatis一样写代码即可,框架会根据设置的分库分表配置进行改造我们的sql,对于开发来说,其实是类似无感知的。
eg: 配置好shardingJdbc配置类后,正常写sql: select * from user whre id = 1111;
假设,用户表在数据库中是 user_1, user_2, user_3(水平分表)不需要有名字为user的表,分片方式是 (id % 3);
那么真正执行的sql流程是 1111 % 3 = 1 所以执行的sql会被改造成 select * from user_1
whre id = 1111; 那么执行 写入表是会作用于 user_1, 读的时候也会作用于 user_1, 如果扩表了,分片方式改成了
id % 4, 那么之前的数据就需要迁移处理,不然会找不到的。所以分片方式很重要!!!
关键点:分片方式
分片方式,分为两种常用场景
(1)数据量是可预知,比如未来十年肯定不会数量肯定不会超过5000W,那么直接分10张表,用id % 10 或者 Hash值 % 10, 每张表大约500W(会有数量量偏移,只要看id的生成策略)
(2)数据量无法或者不好预支,且是个不断增量的过程,那么可以采用数量的方式,但是id需要定制处理,考虑用redis, 比如id 从200000000000+ 递增,那么{id.mod(10000000000).intdiv(5000000) + 1}那么就能控制每张表固定数量,除非id挑号了,有序的id在电商等信息中不适合使用,容易别竞争对手根据id估算数据,但是大部分情况下还是适用的,且有序的id在处理数据上会很有用,比如某一段数据出问题,可以利用有序的范围阶段处
步骤概述
(1)引入shardingsphere依赖
(2)配置文件配置主从数据库
(3)注册数据源DataSource,配置读写分离和各个表的分表策略
(4)启动类移除默认数据库配置@SpringBootApplication(exclude = {DataSourceAutoConfiguration.class})
步骤详细
1、引入依赖:
<?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.0</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.example</groupId>
<artifactId>sharding-jdbc</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>sharding-jdbc</name>
<description>Demo project for Spring Boot</description>
<properties>
<java.version>11</java.version>
<hutool.version>5.8.15</hutool.version>
<pagehelper.version>1.4.2</pagehelper.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jdbc</artifactId>
</dependency>
<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.0.0</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<!-- https://mvnrepository.com/artifact/com.alibaba/druid-spring-boot-starter -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.2.16</version>
</dependency>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-core</artifactId>
<version>4.1.1</version>
</dependency>
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper-spring-boot-starter</artifactId>
<version>${pagehelper.version}</version>
</dependency>
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>${hutool.version}</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
</dependencies>
<build>
<resources>
<resource>
<directory>src/main/java</directory>
<includes>
<include>**/*.properties</include>
<include>**/*.xml</include>
</includes>
<filtering>false</filtering>
</resource>
<resource>
<directory>src/main/resources</directory>
<includes>
<include>**/*.properties</include>
<include>**/*.yml</include>
<include>**/*.xml</include>
</includes>
<filtering>false</filtering>
</resource>
</resources>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
2、配置:
# mysql
spring.datasource.druid:
ds-master:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://127.0.0.1:3306/test?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf8&useSSL=false&allowMultiQueries=true
username: root
password: 123456
initial-size: 1 #初始化时建立物理连接的个数
max-active: 16 #最大连接池数量
min-idle: 1 #最小连接池数量
max-wait: 6000 #获取连接时最大等待时间,单位毫秒
filters: stat,wall #开启监控统计,防御sql注入过滤器
ds-slave:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://127.0.0.1:3306/test?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf8&useSSL=false&allowMultiQueries=true
username: root
password: 123456
initial-size: 1 #初始化时建立物理连接的个数
max-active: 16 #最大连接池数量
min-idle: 1 #最小连接池数量
max-wait: 6000 #获取连接时最大等待时间,单位毫秒
filters: stat,wall #开启监控统计,防御sql注入过滤器
3、注册DataSource,配置读写和分表
如果喜欢采用yml方式:参考https://shardingsphere.apache.org/document/legacy/4.x/document/cn/manual/sharding-jdbc/configuration/config-yaml/
支持Apollo,建议Apollo Client 1.5.0及其以上版本
@Configuration
public class ShardingDataSourceConfigurer {
@Bean(name = "masterDataSource")
@ConfigurationProperties(prefix = "spring.datasource.druid.ds-master")
public DataSource masterDataSource() {
return DruidDataSourceBuilder.create().build();
}
@Bean(name = "slaveDataSource")
@ConfigurationProperties(prefix = "spring.datasource.druid.ds-slave")
public DataSource slaveDataSource() {
return DruidDataSourceBuilder.create().build();
}
@Bean(name = "shardingDataSource")
@Primary
public DataSource shardingDataSource(
@Qualifier("masterDataSource") DataSource masterDataSource,
@Qualifier("slaveDataSource") DataSource slaveDataSource) throws SQLException {
//1、指定数据源
Map<String, DataSource> dataSourceMap = new HashMap<>(2);
dataSourceMap.put("ds_master", masterDataSource);
dataSourceMap.put("ds_slave", slaveDataSource);
//2、配置读写分离规则
LoadBalanceStrategyConfiguration loadBalanceStrategyConfiguration = new LoadBalanceStrategyConfiguration("RANDOM");
MasterSlaveRuleConfiguration masterSlaveRuleConfig = new MasterSlaveRuleConfiguration(
"center", "ds_master",
Arrays.asList("ds_master", "ds_slave"), loadBalanceStrategyConfiguration);
//3、分库分表配置
ShardingRuleConfiguration shardingRuleConfig = getShardingRuleConfiguration();
//4、属性配置项,可以为以下属性
Properties propertie = new Properties();
//是否打印SQL解析和改写日志
propertie.setProperty("sql.show", Boolean.FALSE.toString());
//用于SQL执行的工作线程数量,为零则表示无限制
//propertie.setProperty("executor.size","8");
//每个物理数据库为每次查询分配的最大连接数量
propertie.setProperty("max.connections.size.per.query", "5");
//是否在启动时检查分表元数据一致性
propertie.setProperty("check.table.metadata.enabled", "false");
//添加读写分离配置
shardingRuleConfig.setMasterSlaveRuleConfigs(Collections.singletonList(masterSlaveRuleConfig));
return ShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfig, propertie);
}
private ShardingRuleConfiguration getShardingRuleConfiguration() {
//配置各个表的分库分表策略
ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
//message消息表
TableRuleConfiguration messageTableRuleConfig = new TableRuleConfiguration("message", "center.message_$->{1..15}");
messageTableRuleConfig.setTableShardingStrategyConfig(new InlineShardingStrategyConfiguration("id", "message_$->{unid.mod(10000000000).intdiv(5000000) + 1}"));
shardingRuleConfig.getTableRuleConfigs().add(messageTableRuleConfig);
// person表
TableRuleConfiguration personTableRuleConfig = new TableRuleConfiguration("person", "test.person_$->{1..3}");
personTableRuleConfig.setTableShardingStrategyConfig(new InlineShardingStrategyConfiguration("id", "person_$->{(id % 3) + 1}"));
shardingRuleConfig.getTableRuleConfigs().add(personTableRuleConfig);
// person_detail表
TableRuleConfiguration personDetailTableRuleConfig = new TableRuleConfiguration("person_detail", "test.person_detail_$->{1..3}");
personDetailTableRuleConfig.setTableShardingStrategyConfig(new InlineShardingStrategyConfiguration("person_id", "person_detail_$->{(person_id % 3) + 1}"));
shardingRuleConfig.getTableRuleConfigs().add(personDetailTableRuleConfig);
// 设置绑定表
shardingRuleConfig.getBindingTableGroups().add("person, person_detail");
// shardingRuleConfig.getBindingTableGroups().add("person_detail, person");
return shardingRuleConfig;
}
}
4、启动类移除spring默认配置
@SpringBootApplication(exclude = {DataSourceAutoConfiguration.class})
5、测试
保存测试
// 保存
@Test
void save() {
PersonDO Pers5、测试onDO = new PersonDO();
PersonDO.setId(IdUtil.getSnowflakeNextId());
PersonDO.setName("张三");
PersonDO.setSex("男");
PersonDO.setAge(11);
PersonDO.setDes("必读");
int insert = personMapper.insert(PersonDO);
System.out.println(insert);
}
分页测试
表关联测试
未设置绑定表
执行sql
SELECT
p.id AS id,
p.NAME AS NAME,
p.sex AS sex,
p.age AS age,
p.des AS des,
d.education AS education,
d.school,
d.graduation_time AS graduation_time
FROM
person p
LEFT JOIN person_detail d ON p.id = d.person_id
WHERE
p.id = '1652202828696948736'
结果(用一个id,查出了三份,可以在sql中也设置 d.person_id = XXXX, 但这方法有点傻,看下面方法)
问 : 为什么不是 3 * 3 表笛卡尔9条查询,而是三条
设置绑定表
结果只有一条,sql也只有一条查询