参考:
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.DruidDataSourcedriver-class-name: com.mysql.cj.jdbc.Driverurl: jdbc:mysql://127.0.0.1:3306/test?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf8&useSSL=false&allowMultiQueries=trueusername: rootpassword: 123456initial-size: 1 #初始化时建立物理连接的个数max-active: 16 #最大连接池数量min-idle: 1 #最小连接池数量max-wait: 6000 #获取连接时最大等待时间,单位毫秒filters: stat,wall #开启监控统计,防御sql注入过滤器ds-slave:type: com.alibaba.druid.pool.DruidDataSourcedriver-class-name: com.mysql.cj.jdbc.Driverurl: jdbc:mysql://127.0.0.1:3306/test?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf8&useSSL=false&allowMultiQueries=trueusername: rootpassword: 123456initial-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
SELECTp.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
FROMperson pLEFT JOIN person_detail d ON p.id = d.person_id
WHEREp.id = '1652202828696948736'
结果(用一个id,查出了三份,可以在sql中也设置 d.person_id = XXXX, 但这方法有点傻,看下面方法)
问 : 为什么不是 3 * 3 表笛卡尔9条查询,而是三条
设置绑定表
结果只有一条,sql也只有一条查询