一、简介
Sharding-jdbc官网：/
1.概述
a、Sharding-jdbc是一个开源的分布式的关系型数据库中间件
b、Sharding-jdbc是客户端代理模式
c、定位为轻量级的Java框架，以jar包提供服务；可以理解为增强版的jdbc驱动
d、完全兼容各种ORM框架，如Mybatis等
架构图：
2.与Mycat之间的差别
a、Mycat是服务端代理，sharding-jdbc是客户端代理
b、MyCat不支持同一库内的水平切分，Sharding-jdbc支持
二、使用
准备：使用前先准备两台Mysql数据库，作为分片节点
本项目使用的两台数据库节点分别为131和132
1.新建一个spring boot项目
a、通过idea创建一个springboot项目
b、通过Maven引入依赖
<dependencies><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-data-jpa</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>2.1.0</version></dependency><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><scope>runtime</scope></dependency><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId><optional>true</optional></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-test</artifactId><scope>test</scope></dependency><!--sharding-jdbc for spring --><!--<dependency>--><!--<groupId>org.apache.shardingsphere</groupId>--><!--<artifactId>sharding-jdbc-spring-namespace</artifactId>--><!--<version>4.0.0-RC2</version>--><!--</dependency>--><!--sharding-jdbc for springboot --><dependency><groupId>org.apache.shardingsphere</groupId><artifactId>sharding-jdbc-spring-boot-starter</artifactId><version>4.0.0-RC2</version></dependency></dependencies>
2.配置Sharding-jdbc
注意：a、Sharding-jdbc的配置在spring和springboot项目中是不同的
b、同时，在spring项目和spring-boot项目中，jar的引入方式也是不同的，请注意maven中sharding-jdbc的依赖包的引入方式
（1）第一种方式，使用spring名称空间的方式进行配置
a、创建sharding-jdbc.xml文件
文件位置：
文件内容：
<?xml version="1.0" encoding="UTF-8"?><beans xmlns="/schema/beans"xmlns:xsi="/2001/XMLSchema-instance"xmlns:p="/schema/p"xmlns:context="/schema/context"xmlns:tx="/schema/tx"xmlns:sharding="/schema/shardingsphere/sharding"xmlns:master-slave="/schema/shardingsphere/masterslave"xmlns:bean="/schema/util"xsi:schemaLocation="/schema/beans/schema/beans/spring-beans.xsd/schema/shardingsphere/sharding/schema/shardingsphere/sharding/sharding.xsd/schema/shardingsphere/masterslave/schema/shardingsphere/masterslave/master-slave.xsd/schema/context/schema/context/spring-context.xsd/schema/tx/schema/tx/spring-tx.xsd /schema/util /schema/util/spring-util.xsd"><!--第一个数据源 主--><bean name="ds0" class="com.zaxxer.hikari.HikariDataSource" destroy-method="close" ><property name="driverClassName" value="com.mysql.cj.jdbc.Driver"/><property name="username" value="root"/><property name="password" value="root" /><property name="jdbcUrl" value="jdbc:mysql://192.168.73.131/sharding_order?serverTimezone=Asia/Shanghai&useSSL=false"/></bean><!--第一个数据源 从--><bean id="slave0" class="com.zaxxer.hikari.HikariDataSource" destroy-method="close"><property name="driverClassName" value="com.mysql.cj.jdbc.Driver" /><property name="username" value="root" /><property name="password" value="root" /><property name="jdbcUrl" value="jdbc:mysql://192.168.73.130/sharding_order?serverTimezone=Asia/Shanghai&useSSL=false"/></bean><!--第二个数据源--><bean id="ms1" class="com.zaxxer.hikari.HikariDataSource" destroy-method="close"><property name="driverClassName" value="com.mysql.cj.jdbc.Driver" /><property name="username" value="root" /><property name="password" value="root" /><property name="jdbcUrl" value="jdbc:mysql://192.168.73.132/sharding_order?serverTimezone=Asia/Shanghai&useSSL=false"/></bean><!--主从之间的负载均衡策略--><master-slave:load-balance-algorithm id="msStrategy" type="RANDOM"/><sharding:data-source id="sharding-data-source"><!--data-source-names: 该规则表示针对哪几个数据源；--><sharding:sharding-rule data-source-names="ds0,slave0,ms1" default-data-source-name="ms0"><!--主从关系,在这里主从共同构建成为一个一体的数据源--><sharding:master-slave-rules><sharding:master-slave-rule id="ms0" master-data-source-name="ds0" slave-data-source-names="slave0"strategy-ref="msStrategy" /></sharding:master-slave-rules><!--针对表的规则--><sharding:table-rules><!--logic-table: sharding-jdbc 中的逻辑表actual-data-nodes: 真实的数据节点,内容格式：库名.表名$->：占位符，相当于spring中的${}database-strategy-ref：数据库的分片策略table-strategy-ref: 表的分片策略--><sharding:table-rule logic-table="t_order" actual-data-nodes="ms$->{0..1}.t_order_$->{1..2}"database-strategy-ref="databaseStrategy" table-strategy-ref="tableStrategy"key-generator-ref="uuid" /><sharding:table-rule logic-table="t_order_item" actual-data-nodes="ms$->{0..1}.t_order_item_$->{1..2}"database-strategy-ref="databaseStrategy" table-strategy-ref="tableOrderItemStrategy"key-generator-ref="uuid" /></sharding:table-rules><!--全局表配置--><sharding:broadcast-table-rules><sharding:broadcast-table-rule table="area"/></sharding:broadcast-table-rules><!--子表(绑定表) 在4.0.0-RC2 这个版本中，存在bug，绑定表无法使用，若要使用请关注sharding-jdbc的更新--><sharding:binding-table-rules><!--父表：t_orderorder_id(主键,且同一个库中，使用该字段进行分别)； user_id,入库时，使用user_id进行分库子表：t_order_item 关联字段：order_id(t_order的主键)，user_id，入库时使用该字段判断父表所在的库注意：sharding-jdbc不能指定绑定字段，因此，子表和父表必须要有相同的字段，并以该字段作为关联字段--><sharding:binding-table-rule logic-tables="t_order,t_order_item"/></sharding:binding-table-rules></sharding:sharding-rule></sharding:data-source><!--key 生成策略--><sharding:key-generator id="uuid" column="order_id" type="UUID"/><!--<sharding:key-generator id="snowflake" column="order_id" type="SNOWFLAKE" props-ref="snow"/>--><!--<bean:properties id="snow">--><!--<prop key="worker.id">678</prop>--><!--<prop key="max.tolerate.time.difference.milliseconds">10</prop>--><!--</bean:properties>--><!--sharding-column: 分片列algorithm-expression：表达式--><sharding:inline-strategy id="databaseStrategy" sharding-column="user_id"algorithm-expression="ms$->{user_id % 2}"/><!--分表策略--><!--<sharding:inline-strategy id="tableStrategy" sharding-column="order_id"--><!--algorithm-expression="t_order_$->{order_id % 2 + 1}"/>--><sharding:standard-strategy id="tableStrategy"sharding-column="order_id"precise-algorithm-ref="myShard"/><bean id="myShard" class="com.example.shardingjdbcdemo.sharding.MySharding"/><!--<sharding:inline-strategy id="tableOrderItemStrategy" sharding-column="order_id"--><!--algorithm-expression="t_order_item_$->{order_id % 2 + 1}"/>--><sharding:standard-strategy id="tableOrderItemStrategy"sharding-column="order_id"precise-algorithm-ref="myShard"/><!--接下来配置spring的SqlSessionFactory--><bean class="org.mybatis.spring.SqlSessionFactoryBean"><property name="dataSource" ref="sharding-data-source"/><property name="mapperLocations" value="classpath*:/mybatis/*.xml"/></bean><!--注意：以上配置完成后，请检查mapper中被分片的表的表名，不要使用实际表明，需要使用sharding:data-source配置的逻辑表名--></beans>
b、springBoot中引入该配置文件
c.整体项目结构
d、自定义的分片表达式处理类
package com.example.shardingjdbcdemo.sharding;import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;import java.util.Collection;/*** 自定义的处理分片表达式的类* 本次用例中，需要处理order_id 的分片规则* order_id 做为库内分片的字段，它既是t_order表的主键，同时也是子表t_order_item中的字段* order_id 使用了全局唯一主键 UUID*/public class MySharding implements PreciseShardingAlgorithm<String> {@Overridepublic String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<String> shardingValue) {String id = shardingValue.getValue();int mode = id.hashCode() % availableTargetNames.size();String[] strings = availableTargetNames.toArray(new String[0]);//取绝对值mode = Math.abs(mode);System.out.println(strings[0]+"---------"+strings[1]);System.out.println("mode="+mode);return strings[mode];}}
e.分布式id解决方案之雪花算法
概述：
snowFlake 时Twitter提出的分布式ID算法
一个64bit的long型数字
引入了时间戳，保持自增
基本概念
基本保持全局唯一，毫秒内并发最大4096个ID
时间回调可能会引起ID重复
可设置最大容忍回调时间
应用
配置：
<?xml version="1.0" encoding="UTF-8"?><beans xmlns="/schema/beans"xmlns:xsi="/2001/XMLSchema-instance"xmlns:p="/schema/p"xmlns:context="/schema/context"xmlns:tx="/schema/tx"xmlns:sharding="/schema/shardingsphere/sharding"xmlns:master-slave="/schema/shardingsphere/masterslave"xmlns:bean="/schema/util"xsi:schemaLocation="/schema/beans/schema/beans/spring-beans.xsd/schema/shardingsphere/sharding/schema/shardingsphere/sharding/sharding.xsd/schema/shardingsphere/masterslave/schema/shardingsphere/masterslave/master-slave.xsd/schema/context/schema/context/spring-context.xsd/schema/tx/schema/tx/spring-tx.xsd /schema/util /schema/util/spring-util.xsd"><bean id="ds0" class="com.zaxxer.hikari.HikariDataSource" destroy-method="close"><property name="driverClassName" value="com.mysql.cj.jdbc.Driver" /><property name="username" value="imooc" /><property name="password" value="Imooc@123456" /><property name="jdbcUrl" value="jdbc:mysql://192.168.73.131/sharding_order?serverTimezone=Asia/Shanghai&useSSL=false"/></bean><bean id="slave0" class="com.zaxxer.hikari.HikariDataSource" destroy-method="close"><property name="driverClassName" value="com.mysql.cj.jdbc.Driver" /><property name="username" value="imooc" /><property name="password" value="Imooc@123456" /><property name="jdbcUrl" value="jdbc:mysql://192.168.73.130/sharding_order?serverTimezone=Asia/Shanghai&useSSL=false"/></bean><bean id="ms1" class="com.zaxxer.hikari.HikariDataSource" destroy-method="close"><property name="driverClassName" value="com.mysql.cj.jdbc.Driver" /><property name="username" value="imooc" /><property name="password" value="Imooc@123456" /><property name="jdbcUrl" value="jdbc:mysql://192.168.73.132/shard_order?serverTimezone=Asia/Shanghai&useSSL=false"/></bean><master-slave:load-balance-algorithm id="msStrategy" type="RANDOM"/><sharding:data-source id="sharding-data-source"><sharding:sharding-rule data-source-names="ds0,slave0,ms1" ><sharding:master-slave-rules><sharding:master-slave-rule id="ms0" master-data-source-name="ds0" slave-data-source-names="slave0"strategy-ref="msStrategy"/></sharding:master-slave-rules><sharding:table-rules><sharding:table-rule logic-table="t_order" actual-data-nodes="ms$->{0..1}.t_order_$->{1..2}"database-strategy-ref="databaseStrategy" table-strategy-ref="standard"key-generator-ref="snowflake"/></sharding:table-rules><sharding:broadcast-table-rules><sharding:broadcast-table-rule table="area"/></sharding:broadcast-table-rules><!--<sharding:binding-table-rules>--><!--<sharding:binding-table-rule logic-tables="t_order,t_order_item" />--><!--</sharding:binding-table-rules>--></sharding:sharding-rule></sharding:data-source><sharding:key-generator id="snowflake" column="order_id" type="SNOWFLAKE" props-ref="snow"/><bean:properties id="snow"><prop key="worker.id">678</prop><prop key="max.tolerate.time.difference.milliseconds">10</prop></bean:properties><sharding:inline-strategy id="databaseStrategy" sharding-column="user_id"algorithm-expression="ms$->{user_id % 2}" /><bean id="myShard" class="com.example.shardingjdbcdemo.sharding.MySharding"/><sharding:standard-strategy id="standard" sharding-column="order_id" precise-algorithm-ref="myShard"/><sharding:inline-strategy id="tableStrategy" sharding-column="order_id"algorithm-expression="t_order_$->{order_id % 2 +1}" /><bean class="org.mybatis.spring.SqlSessionFactoryBean"><property name="dataSource" ref="sharding-data-source"/><property name="mapperLocations" value="classpath*:/mybatis/*.xml"/></bean></beans>
对应的自定义分片处理逻辑类
package com.example.shardingjdbcdemo.sharding;import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;import java.util.Collection;/*** 自定义的处理分片表达式的类* 本次用例中，需要处理order_id 的分片规则* order_id 做为库内分片的字段，它既是t_order表的主键，同时也是子表t_order_item中的字段* order_id 使用了全局唯一主键 雪花算法*/public class MySharding implements PreciseShardingAlgorithm<Long> {@Overridepublic String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Long> shardingValue) {Long id = shardingValue.getValue();long mode = id % availableTargetNames.size();String[] strings = availableTargetNames.toArray(new String[0]);//取绝对值mode = Math.abs(mode);System.out.println(strings[0]+"---------"+strings[1]);System.out.println("mode="+mode);return strings[(int) mode];}}
（2）第二种方式，使用springboot starter 的配置方式
a、注释关于spring名称空间的引用
b、修改maven依赖
c、修改application.properties文件如下
# 配置真实数据源spring.shardingsphere.datasource.names=ds0,ms1,slave0# 配置第 1 个数据源 -主库（131与130构成主从关系）spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSourcespring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.cj.jdbc.Driverspring.shardingsphere.datasource.ds0.jdbcUrl=jdbc:mysql://192.168.73.131/sharding_order?serverTimezone=Asia/Shanghai&useSSL=falsespring.shardingsphere.datasource.ds0.username=rootspring.shardingsphere.datasource.ds0.password=root#从库spring.shardingsphere.datasource.slave0.type=com.zaxxer.hikari.HikariDataSourcespring.shardingsphere.datasource.slave0.driver-class-name=com.mysql.cj.jdbc.Driverspring.shardingsphere.datasource.slave0.jdbcUrl=jdbc:mysql://192.168.73.130/sharding_order?serverTimezone=Asia/Shanghai&useSSL=falsespring.shardingsphere.datasource.slave0.username=rootspring.shardingsphere.datasource.slave0.password=root# 配置第 2 个数据源spring.shardingsphere.datasource.ms1.type=com.zaxxer.hikari.HikariDataSourcespring.shardingsphere.datasource.ms1.driver-class-name=com.mysql.cj.jdbc.Driverspring.shardingsphere.datasource.ms1.jdbcUrl=jdbc:mysql://192.168.73.132/sharding_order?serverTimezone=Asia/Shanghai&useSSL=falsespring.shardingsphere.datasource.ms1.username=rootspring.shardingsphere.datasource.ms1.password=root#读写分离配置spring.shardingsphere.sharding.master-slave-rules.ms0.master-data-source-name=ds0spring.shardingsphere.sharding.master-slave-rules.ms0.slave-data-source-names=slave0spring.shardingsphere.sharding.master-slave-rules.ms0.load-balance-algorithm-type=RANDOM# 配置 t_order 表规则spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=ms$->{0..1}.t_order_$->{0..1}# 配置分库策略spring.shardingsphere.sharding.tables.t_order.database-strategy.inline.sharding-column=user_id#相应的分片算法spring.shardingsphere.sharding.tables.t_order.database-strategy.inline.algorithm-expression=ms$->{user_id % 2}# 配置分表策略spring.shardingsphere.sharding.tables.t_order.table-strategy.standard.sharding-column=user_id#自定义的分片算法spring.shardingsphere.sharding.tables.t_order.table-strategy.standard.precise-algorithm-class-name=com.example.shardingjdbcdemo.sharding.MySharding#配置t_order的主键生成策略spring.shardingsphere.sharding.tables.t_order.key-generator.column=order_idspring.shardingsphere.sharding.tables.t_order.key-generator.type=UUID#全局表spring.shardingsphere.sharding.broadcast-tables=area#mybatis mapper 位置mybatis.mapper-locations=/mybatis/*.xml
d、自定义的分片表达式处理类
package com.example.shardingjdbcdemo.sharding;import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;import java.util.Collection;/*** 自定义的处理分片表达式的类* 本次用例中，需要处理order_id 的分片规则* order_id 做为库内分片的字段，它既是t_order表的主键，同时也是子表t_order_item中的字段* order_id 使用了全局唯一主键 UUID*/public class MySharding implements PreciseShardingAlgorithm<String> {@Overridepublic String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<String> shardingValue) {String id = shardingValue.getValue();int mode = id.hashCode() % availableTargetNames.size();String[] strings = availableTargetNames.toArray(new String[0]);//取绝对值mode = Math.abs(mode);System.out.println(strings[0]+"---------"+strings[1]);System.out.println("mode="+mode);return strings[mode];}}
e.分布式id解决方案之雪花算法
概述：
snowFlake 时Twitter提出的分布式ID算法
一个64bit的long型数字
引入了时间戳，保持自增
基本概念
基本保持全局唯一，毫秒内并发最大4096个ID
时间回调可能会引起ID重复
可设置最大容忍回调时间
应用
配置：
# 配置真实数据源spring.shardingsphere.datasource.names=ds0,ms1,slave0# 配置第 1 个数据源 -主库（131与130构成主从关系）spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSourcespring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.cj.jdbc.Driverspring.shardingsphere.datasource.ds0.jdbcUrl=jdbc:mysql://192.168.73.131/sharding_order?serverTimezone=Asia/Shanghai&useSSL=falsespring.shardingsphere.datasource.ds0.username=rootspring.shardingsphere.datasource.ds0.password=root#从库spring.shardingsphere.datasource.slave0.type=com.zaxxer.hikari.HikariDataSourcespring.shardingsphere.datasource.slave0.driver-class-name=com.mysql.cj.jdbc.Driverspring.shardingsphere.datasource.slave0.jdbcUrl=jdbc:mysql://192.168.73.130/sharding_order?serverTimezone=Asia/Shanghai&useSSL=falsespring.shardingsphere.datasource.slave0.username=rootspring.shardingsphere.datasource.slave0.password=root# 配置第 2 个数据源spring.shardingsphere.datasource.ms1.type=com.zaxxer.hikari.HikariDataSourcespring.shardingsphere.datasource.ms1.driver-class-name=com.mysql.cj.jdbc.Driverspring.shardingsphere.datasource.ms1.jdbcUrl=jdbc:mysql://192.168.73.132/sharding_order?serverTimezone=Asia/Shanghai&useSSL=falsespring.shardingsphere.datasource.ms1.username=rootspring.shardingsphere.datasource.ms1.password=root#读写分离配置spring.shardingsphere.sharding.master-slave-rules.ms0.master-data-source-name=ds0spring.shardingsphere.sharding.master-slave-rules.ms0.slave-data-source-names=slave0spring.shardingsphere.sharding.master-slave-rules.ms0.load-balance-algorithm-type=RANDOM# 配置 t_order 表规则spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=ms$->{0..1}.t_order_$->{0..1}# 配置分库策略spring.shardingsphere.sharding.tables.t_order.database-strategy.inline.sharding-column=user_id#相应的分片算法spring.shardingsphere.sharding.tables.t_order.database-strategy.inline.algorithm-expression=ms$->{user_id % 2}# 配置分表策略spring.shardingsphere.sharding.tables.t_order.table-strategy.standard.sharding-column=user_id#自定义的分片算法spring.shardingsphere.sharding.tables.t_order.table-strategy.standard.precise-algorithm-class-name=com.example.shardingjdbcdemo.sharding.MySharding#配置t_order的主键生成策略spring.shardingsphere.sharding.tables.t_order.key-generator.column=order_id#spring.shardingsphere.sharding.tables.t_order.key-generator.type=UUID 全局id生成策略 UUID#全局ID生成策略之雪花算法相关配置spring.shardingsphere.sharding.tables.t_order.key-generator.type=SNOWFLAKEspring.shardingsphere.sharding.tables.t_order.key-generator.props.worker.id=345spring.shardingsphere.sharding.tables.t_order.key-generator.props.max.tolerate.time.difference.milliseconds=10#全局表spring.shardingsphere.sharding.broadcast-tables=area#mybatis mapper 位置mybatis.mapper-locations=/mybatis/*.xml
对应的自定义分片处理逻辑类：
package com.example.shardingjdbcdemo.sharding;import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;import java.util.Collection;/*** 自定义的处理分片表达式的类* 本次用例中，需要处理order_id 的分片规则* order_id 做为库内分片的字段，它既是t_order表的主键，同时也是子表t_order_item中的字段* order_id 使用了全局唯一主键 雪花算法*/public class MySharding implements PreciseShardingAlgorithm<Long> {@Overridepublic String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Long> shardingValue) {Long id = shardingValue.getValue();long mode = id % availableTargetNames.size();String[] strings = availableTargetNames.toArray(new String[0]);//取绝对值mode = Math.abs(mode);System.out.println(strings[0]+"---------"+strings[1]);System.out.println("mode="+mode);return strings[(int) mode];}}
