一.Sharding-JDBC水平分表
1.数据库表
2.Maven依赖
<!-- sharding分片 -->
<!-- 分库分表 -->
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.0.0-RC1</version>
</dependency>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-namespace</artifactId>
<version>4.0.0-RC1</version>
</dependency>
<!-- https://mvnrepository.com/artifact/javax.xml.bind/jaxb-api -->
<dependency>
<groupId>javax.xml.bind</groupId>
<artifactId>jaxb-api</artifactId>
<version>2.3.0-b170201.1204</version>
</dependency>
<!-- https://mvnrepository.com/artifact/javax.activation/activation -->
<dependency>
<groupId>javax.activation</groupId>
<artifactId>activation</artifactId>
<version>1.1</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.glassfish.jaxb/jaxb-runtime -->
<dependency>
<groupId>org.glassfish.jaxb</groupId>
<artifactId>jaxb-runtime</artifactId>
<version>2.3.0-b170127.1453</version>
</dependency>
<dependency>
<groupId>dom4j</groupId>
<artifactId>dom4j</artifactId>
<version>1.1</version>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.0.5</version>
</dependency>
3.yaml配置
spring:
main:
allow-bean-definition-overriding: true
shardingsphere:
datasource:
#数据库名称
names: saas
saas:
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://xxxx:3300/course_db?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&characterSetResults=utf8&useSSL=false&verifyServerCertificate=false&autoReconnct=true&autoReconnectForPools=true&allowMultiQueries=true
password: root
type: com.zaxxer.hikari.HikariDataSource
username: root
#打印sql
props:
sql:
show: true
sharding:
tables:
#数据分表规则
#指定所需分的表
course:
actual-data-nodes: saas.course_$->{1..2}
# 使用SNOWFLAKE算法生成主键
key-generator:
column: cid
type: SNOWFLAKE
table-strategy:
inline:
#分表规则
algorithm-expression: course_$->{cid % 2 + 1}
#指定主键
sharding-column: cid
4.实体类
package com.example.sharding.entity;
import com.baomidou.mybatisplus.annotation.TableId;
import lombok.Data;
@Data
public class Course {
@TableId
private Long cid;
private String cname;
private Long userId;
private String cstatus;
}
5. 测试
/**
* 添加操作方法测试
*/
@Test
public void addCourse() {
for (int i = 0; i < 100; i++) {
Course course = new Course();
course.setCname("springcloud");
course.setUserId(100L);
course.setCstatus("Normal");
courseMapper.insert(course);
}
}
/**
* 添加操作方法测试
*/
@Test
public void findCourse() {
Course course = courseMapper.selectOne(new QueryWrapper<Course>().eq("cid",1374278112328744962L));
System.out.println(course);
}
- =====================================================================================================
- =====================================================================================================
二.Sharding-JDBC水平分库分表
1.yaml配置
spring:
main:
allow-bean-definition-overriding: true
shardingsphere:
datasource:
#数据库名称
names: ds0,ds1
ds0:
driver-class-name: com.mysql.cj.jdbc.Driver
type: com.zaxxer.hikari.HikariDataSource
jdbc-url: jdbc:mysql://xxx:3300/edu_db_2?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&characterSetResults=utf8&useSSL=false&verifyServerCertificate=false&autoReconnct=true&autoReconnectForPools=true&allowMultiQueries=true
password: root
username: root
ds1:
driver-class-name: com.mysql.cj.jdbc.Driver
type: com.zaxxer.hikari.HikariDataSource
jdbc-url: jdbc:mysql://127.0.0.1:3306/edu_db_1?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&characterSetResults=utf8&useSSL=false&verifyServerCertificate=false&autoReconnct=true&autoReconnectForPools=true&allowMultiQueries=true
password: root
username: root
#打印sql
props:
sql:
show: true
sharding:
#分表
tables:
#数据分表规则
#指定所需分的数据库和表的分布情况
course: #表前缀
actual-data-nodes: ds$->{0..1}.course_$->{1..2}
# 使用SNOWFLAKE算法生成主键
key-generator:
column: cid
type: SNOWFLAKE
table-strategy:
inline:
#分表规则
algorithm-expression: course_$->{cid % 2 + 1}
#指定主键
sharding-column: cid
#分库
database-strategy:
inline:
algorithm-expression: ds$->{user_id % 2}
sharding-column: user_id
2.测试方法
package com.example.demo3;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.example.demo3.entity.Course;
import com.example.demo3.mapper.CourseMapper;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
@SpringBootTest
class Demo3ApplicationTests {
@Autowired
private CourseMapper courseMapper;
/**
* 添加操作方法测试
*/
@Test
public void addCourse() {
for (int i = 0; i < 100; i++) {
Course course = new Course();
course.setCname("springcloud");
course.setUserId(100L+i);
course.setCstatus("Normal"+i);
courseMapper.insert(course);
}
}
/**
* 添加操作方法测试
*/
@Test
public void findCourse() {
Course course = courseMapper.selectOne(new QueryWrapper<Course>().eq("cid",1374314159688237057L));
System.out.println(course);
}
}
- =====================================================================================================
- =====================================================================================================
三.Sharding-JDBC垂直分库
1.yaml配置
spring:
main:
allow-bean-definition-overriding: true
shardingsphere:
datasource:
#数据库名称
names: ds0,ds1,ds2
ds0:
driver-class-name: com.mysql.cj.jdbc.Driver
type: com.zaxxer.hikari.HikariDataSource
jdbc-url: jdbc:mysql://8.131.119.145:3300/edu_db_2?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&characterSetResults=utf8&useSSL=false&verifyServerCertificate=false&autoReconnct=true&autoReconnectForPools=true&allowMultiQueries=true
password: root
username: root
ds1:
driver-class-name: com.mysql.cj.jdbc.Driver
type: com.zaxxer.hikari.HikariDataSource
jdbc-url: jdbc:mysql://127.0.0.1:3306/edu_db_1?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&characterSetResults=utf8&useSSL=false&verifyServerCertificate=false&autoReconnct=true&autoReconnectForPools=true&allowMultiQueries=true
password: root
username: root
ds2:
driver-class-name: com.mysql.cj.jdbc.Driver
type: com.zaxxer.hikari.HikariDataSource
jdbc-url: jdbc:mysql://127.0.0.1:3306/user_db?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&characterSetResults=utf8&useSSL=false&verifyServerCertificate=false&autoReconnct=true&autoReconnectForPools=true&allowMultiQueries=true
password: root
username: root
#打印sql
props:
sql:
show: true
sharding:
#分表
tables:
#数据分表规则
#指定所需分的数据库和表的分布情况
t_user: #表前缀
actual-data-nodes: ds$->{2}.t_user
# 使用SNOWFLAKE算法生成主键
key-generator:
column: user_id
type: SNOWFLAKE
table-strategy:
inline:
#分表规则(无规则就是这样,专库专表)
algorithm-expression: t_user
#指定主键
sharding-column: user_id
course: #表前缀
actual-data-nodes: ds$->{0..1}.course_$->{1..2}
# 使用SNOWFLAKE算法生成主键
key-generator:
column: cid
type: SNOWFLAKE
table-strategy:
inline:
#分表规则
algorithm-expression: course_$->{cid % 2 + 1}
#指定主键
sharding-column: cid
#分库
database-strategy:
inline:
algorithm-expression: ds$->{user_id % 2}
sharding-column: user_id
2.实体类
@Data
@TableName(value = "t_user")
public class User {
@TableId
private Long userId;
private String username;
private String ustatus;
}
3.测试类
/**
* 添加操作方法测试
*/
@Test
public void addUser() {
//for (int i = 0; i < 100; i++) {
User user = new User();
user.setUsername("lucy");
user.setUstatus("n");
userMapper.insert(user);
//}
}
/**
* 添加操作方法测试
*/
@Test
public void findUser() {
User user = userMapper.selectOne(new QueryWrapper<User>().eq("user_id",1374321115400331266L));
System.out.println(user);
}
四. Sharding-JDBC公共表操作
1.yaml配置
spring:
main:
allow-bean-definition-overriding: true
shardingsphere:
datasource:
#数据库名称
names: ds0,ds1,ds2
ds0:
driver-class-name: com.mysql.cj.jdbc.Driver
type: com.zaxxer.hikari.HikariDataSource
jdbc-url: jdbc:mysql://8.131.119.145:3300/edu_db_2?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&characterSetResults=utf8&useSSL=false&verifyServerCertificate=false&autoReconnct=true&autoReconnectForPools=true&allowMultiQueries=true
password: root
username: root
ds1:
driver-class-name: com.mysql.cj.jdbc.Driver
type: com.zaxxer.hikari.HikariDataSource
jdbc-url: jdbc:mysql://127.0.0.1:3306/edu_db_1?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&characterSetResults=utf8&useSSL=false&verifyServerCertificate=false&autoReconnct=true&autoReconnectForPools=true&allowMultiQueries=true
password: root
username: root
ds2:
driver-class-name: com.mysql.cj.jdbc.Driver
type: com.zaxxer.hikari.HikariDataSource
jdbc-url: jdbc:mysql://127.0.0.1:3306/user_db?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&characterSetResults=utf8&useSSL=false&verifyServerCertificate=false&autoReconnct=true&autoReconnectForPools=true&allowMultiQueries=true
password: root
username: root
#打印sql
props:
sql:
show: true
sharding:
#分表
tables:
#数据分表规则
#指定所需分的数据库和表的分布情况
t_user: #表前缀
actual-data-nodes: ds$->{2}.t_user
# 使用SNOWFLAKE算法生成主键
key-generator:
column: user_id
type: SNOWFLAKE
table-strategy:
inline:
#分表规则(无规则就是这样,专库专表)
algorithm-expression: t_user
#指定主键
sharding-column: user_id
course: #表前缀
actual-data-nodes: ds$->{0..1}.course_$->{1..2}
# 使用SNOWFLAKE算法生成主键
key-generator:
column: cid
type: SNOWFLAKE
table-strategy:
inline:
#分表规则
algorithm-expression: course_$->{cid % 2 + 1}
#指定主键
sharding-column: cid
#分库
database-strategy:
inline:
algorithm-expression: ds$->{user_id % 2}
sharding-column: user_id
#公共表配置
t_udict:
# 使用SNOWFLAKE算法生成主键
key-generator:
column: dictid
type: SNOWFLAKE
#公共表配置
broadcast-tables: t_udict
2.实体类
package com.example.demo3.entity;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;
@Data
@TableName(value = "t_udict")
public class Udict {
@TableId
private Long dictid;
private String ustatus;
private String uvalue;
}
3.测试代码
/**
* 添加操作方法测试
*/
@Test
public void addUdict() {
//for (int i = 0; i < 100; i++) {
Udict udict = new Udict();
udict.setUstatus("a");
udict.setUvalue("已启用");
udictMapper.insert(udict);
//}
}
/**
* 删除操作方法测试
*/
@Test
public void deleteUdict() {
udictMapper.delete(new QueryWrapper<Udict>().eq("dictid",1374329986848129025L));
}
五.Sharding-JDBC主从实战
1.MySQL主从配置
- docker 启动两个数据库服务器
- 在Master配置文件
vim my.ini
#开启日志
log-bin = mysql-bin
#选择模式
binlog_format=ROW
#设置服务id,主从不一致
server_id = 1
#需同步的数据库
binlog-do-db=user_db
#不需要同步的数据库
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
mysql -h localhost -uroot -p
GRANT REPLICATION SLAVE ON *.* TO 'db_sync'@'%' IDENTIFIED BY 'db_sync';
FLUSH PRIVILEGES;
show master status;
- 在Slave配置文件
#开启日志
log-bin = mysql-bin
relay_log=mysql-relay-bin
#选择模式
binlog_format=ROW
server
#设置服务id,主从不一致
server_id = 2
#需同步的数据库
replicate_wild_do_table=user_db.%
#不需要同步的数据库
replicate_wild_ignore_table=mysql
replicate_wild_ignore_table=information_schema.%
replicate_wild_ignore_table=performance_schema.%
mysql -h localhost -uroot -p
STOP SLAVE;
CHANGE MASTER TO
master_host='xxxx',
master_user='db_sync',
master_password='db_sync',
master_log_file='mysql-bin.000002',
master_log_pos = 107;
START SLAVE;
#产看IO_Runing SQL_Runing都为yes才行
show slave status
#注意:如果之前此从库已有主库指向 需要先执行以下命令清空
STOP SLAVE IO_THREAD FOR CHANNEL '';
reset slave all;
2.Sharding-JDBC实现读写分离
spring:
main:
allow-bean-definition-overriding: true
shardingsphere:
datasource:
#数据库名称
names: ds0,s0
#主服务器
ds0:
driver-class-name: com.mysql.cj.jdbc.Driver
type: com.zaxxer.hikari.HikariDataSource
jdbc-url: jdbc:mysql://8.131.119.145:3300/user_db?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&characterSetResults=utf8&useSSL=false&verifyServerCertificate=false&autoReconnct=true&autoReconnectForPools=true&allowMultiQueries=true
password: root
username: root
#从服务器
s0:
driver-class-name: com.mysql.cj.jdbc.Driver
type: com.zaxxer.hikari.HikariDataSource
jdbc-url: jdbc:mysql://127.0.0.1:3306/user_db?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&characterSetResults=utf8&useSSL=false&verifyServerCertificate=false&autoReconnct=true&autoReconnectForPools=true&allowMultiQueries=true
password: root
username: root
#打印sql
props:
sql:
show: true
sharding:
master-slave-rules:
#load-balance-algorithm-type: round_robin
#指向的主数据库名称
ds0:
master-data-source-name: ds0
#多个丛用逗号隔开
slave-data-source-names: s0
tables:
t_user:
actual-data-nodes: ds0.t_user
六.总结
- 注:可以通过Seata进行分布式事务控制