关于ShardingJdbc的一些概念和说明,可以去官网文档上详细看;下面附上了官方地址链接
http://shardingsphere.apache.org/
1 : 读写分离
目前这个读写分离是需要在MySql之间配置好数据同步,也就是根据binlog来进行操作.具体的操作可以看我之前的博客进行配置.这里就简单的介绍下使用
引入Maven依赖
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.0.3.RELEASE</version>
<relativePath /> <!-- lookup parent from repository -->
</parent><properties>
<java.version>1.8</java.version>
</properties><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>1.3.2</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.10</version>
</dependency>
<dependency>
<groupId>io.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>3.1.0</version>
</dependency></dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
配置文件 application.yml
sharding:
jdbc:
dataSource:
names: db-test0,db-test1
# 配置主库
db-test0: #org.apache.tomcat.jdbc.pool.DataSource
type: com.alibaba.druid.pool.DruidDataSource
driverClassName: com.mysql.jdbc.Driver
url: jdbc:mysql://192.168.119.129:3306/cool?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT
username: root
password: meng
#最大连接数
maxPoolSize: 20
db-test1: # 配置第一个从库
type: com.alibaba.druid.pool.DruidDataSource
driverClassName: com.mysql.jdbc.Driver
url: jdbc:mysql://192.168.119.130:3306/cool?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false&serverTimezone=GMT
username: root
password: meng
maxPoolSize: 20
config:
masterslave: # 配置读写分离
load-balance-algorithm-type: round_robin # 配置从库选择策略,提供轮询与随机,这里选择用轮询//random 随机 //round_robin 轮询
name: db1s2
master-data-source-name: db-test0
slave-data-source-names: db-test1
props:
sql: # 开启SQL显示,默认值: false,注意:仅配置读写分离时不会打印日志!!!
show: trueserver:
port: 8989
mybatis:
config-location: classpath:META-INF/mybatis-config.xml这里说下配置文件的意思。
- names : 配置的数据源名字;可以是一个或者多个
- db-test0 和 db-test1就是你配置的名字,然后下面就是一些连接DB的配置信息
- config : masterslave 这里就是配置读写分离的
- load-balance-algorithm-type 配置从库选择策略
- master-data-source-name 和 slave-data-source-names 就是配置主从库 (从库有多个就配置多个)
- 代码就写一个简单的添加和查询就看到效果
- https://gitee.com/yang_16/boot-sharding-jdbc-master.git 代码地址
2 : 使用单库来实现数据分表
Maven的依赖是一致的
sharding:
jdbc:
dataSource:
names: db-test0
db-test0: #org.apache.tomcat.jdbc.pool.DataSource
type: com.alibaba.druid.pool.DruidDataSource
driverClassName: com.mysql.jdbc.Driver
url: jdbc:mysql://192.168.119.129:3306/cool?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT
username: root
password: meng
maxPoolSize: 20
props:
sql:
show: true
#sharding.jdbc.config.sharding.ds_0.data-source-name: db-test0
sharding.jdbc.config.sharding.tables.user.actual-data-nodes: db-test0.user_$->{0..3}
sharding.jdbc.config.sharding.tables.user.table-strategy.standard.sharding-column: id
sharding.jdbc.config.sharding.tables.user.table-strategy.standard.precise-algorithm-class-name: com.yang.table.config.MyPreciseShardingAlgorithmserver:
port: 8989
mybatis:
config-location: classpath:META-INF/mybatis-config.xml配置文件就是去掉了一个从的数据库,然后创建了user_0,user_1,user_2,user_3等四张表
com.yang.table.config.MyPreciseShardingAlgorithm这个类下面配置了表的添加配置
for (int i = 1000; i < 1500; i++) {
User user = new User(i, "Yang Ge" + i, "9527");
long resutl = userMapper.addUser(user);
}在UserController中可以多添加,就可以看到效果
3 : 多库分表
Maven是一样的
application.properties 配置文件
sharding.jdbc.datasource.names=ds-master-0,ds-master-1
sharding.jdbc.datasource.ds-master-0.type=com.alibaba.druid.pool.DruidDataSource
sharding.jdbc.datasource.ds-master-0.driver-class-name=com.mysql.jdbc.Driver
sharding.jdbc.datasource.ds-master-0.url=jdbc:mysql://47.102.44.217:3306/cool?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT
sharding.jdbc.datasource.ds-master-0.username=root
sharding.jdbc.datasource.ds-master-0.password=mengsharding.jdbc.datasource.ds-master-1.type=com.alibaba.druid.pool.DruidDataSource
sharding.jdbc.datasource.ds-master-1.driver-class-name=com.mysql.jdbc.Driver
sharding.jdbc.datasource.ds-master-1.url=jdbc:mysql://47.100.193.114:3306/cool?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT
sharding.jdbc.datasource.ds-master-1.username=root
sharding.jdbc.datasource.ds-master-1.password=mengsharding.jdbc.config.sharding.default-database-strategy.inline.sharding-column=id
sharding.jdbc.config.sharding.default-database-strategy.inline.algorithm-expression=ds-master-$->{id % 2}sharding.jdbc.config.sharding.tables.user.actual-data-nodes=ds-master-$->{0..1}.user_$->{0..3}
sharding.jdbc.config.sharding.tables.user.table-strategy.inline.sharding-column=id
sharding.jdbc.config.sharding.tables.user.table-strategy.inline.algorithm-expression=user_$->{id % 4}
sharding.jdbc.config.sharding.tables.user.key-generator-column-name=idserver.port=8989
mybatis.config-location=classpath:META-INF/mybatis-config.xml
就是在数据库的配置中根据 id % 2来进行配置操作添加和和单表的是一样的.
https://gitee.com/yang_16/boot-sharding-jdbc-many-table.git 代码地址
参考连接 :
https://blog.csdn.net/forezp/article/details/94343671
http://shardingsphere.apache.org/