前言
前文已经介绍了ShardingSphere及相关组件，Sharding-JDBC是ShardingSphere的第一个产品，也是ShardingSphere的前身。本文主要通过代码实例介绍如何使用Sharding-JDBC 。
第一篇文章介绍是官网对于sharding-jdbc的解释和介绍，其实说的直白一点，就是包含了分库分表功能的JDBC，因此我们可以直接把sharding-jdbc当做普通的jdbc来进行使用。
本系列文章项目基于SpringBoot构建，采用ShardingSphere v4.1.1，由于5.X版本还在公测，可能存在诸多Bug。
Sharding-JDBC 实现读写分离
0、读写分离
MySQL读写分离基本原理是让master数据库处理写操作，slave数据库处理读操作。master将写操作的变更同步到各个slave节点。
MySQL读写分离能提高系统性能的原因在于：
1、物理服务器增加，机器处理能力提升。拿硬件换性能。
2、主从只负责各自的读和写，极大程度缓解X锁和S锁争用。
3、slave可以配置myiasm引擎，提升查询性能以及节约系统开销。
4、master直接写是并发的，slave通过主库发送来的binlog恢复数据是异步。
5、slave可以单独设置一些参数来提升其读的性能。
6、增加冗余，提高可用性。
关联知识：
经过分库分表及广播表的示例可以发现，所有的操作都是配置上的问题，本文具体讲配置。
1、环境构建
创建一个SpringBoot项目，引入如下依赖：
<?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">
<parent>
<groupId>cn.zwqh</groupId>
<artifactId>sharding-sphere-4.1.1</artifactId>
<version>1.0-SNAPSHOT</version>
</parent>
<modelVersion>4.0.0</modelVersion>
<groupId>cn.zwqh</groupId>
<artifactId>sharding-sphere-demo-5</artifactId>
<version>${parent.version}</version>
<packaging>jar</packaging>
<name>sharding-sphere-demo-5</name>
<description>Demo project for Spring Boot</description>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
<exclusions>
<exclusion>
<groupId>org.junit.vintage</groupId>
<artifactId>junit-vintage-engine</artifactId>
</exclusion>
</exclusions>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-compiler-plugin</artifactId>
<version>3.8.1</version>
<configuration>
<source>1.8</source>
<target>1.8</target>
<encoding>UTF-8</encoding>
</configuration>
</plugin>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
<configuration>
<mainClass>cn.zwqh.shardingspheredemo5.ShardingSphereDemo5Application</mainClass>
</configuration>
<executions>
<execution>
<id>repackage</id>
<goals>
<goal>repackage</goal>
</goals>
</execution>
</executions>
</plugin>
</plugins>
</build>
</project>
2、在mysql 127.0.0.1:3306创建数据库ds2（写库）、mysql 127.0.0.1:3307数据库ds2（读库），创建表t_user
CREATE TABLE `t_user` (
`user_id` int NOT NULL COMMENT '用户id',
`user_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '用户名称',
PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
3、创建实体类
@Data
public class UserEntity {
private Integer userId;
private String userName;
}
4、创建Mapper类
@Mapper
public interface UserMapper {
@Insert("insert into t_user(user_id,user_name) values(#{userId},#{userName})")
void insertUserInfo(UserEntity userEntity);
@Select("select * from t_user where user_id=#{userId}")
@Results({
@Result(property = "userId", column = "user_id", jdbcType = JdbcType.INTEGER),
@Result(property = "userName", column = "user_name", jdbcType = JdbcType.VARCHAR)
})
UserEntity getUserInfo(Integer userId);
}
5、创建配置文件
serser.port=8080
spring.application.name=spring-boot-shardingsphere
#配置数据源的名称
spring.shardingsphere.datasource.names=master,slave
#配置数据源的具体内容，
spring.shardingsphere.datasource.master.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.master.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.master.url=jdbc:mysql://127.0.0.1:3306/ds2?serverTimezone=UTC
spring.shardingsphere.datasource.master.username=root
spring.shardingsphere.datasource.master.password=123456
spring.shardingsphere.datasource.slave.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.slave.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.slave.url=jdbc:mysql://127.0.0.1:3307/ds2?serverTimezone=UTC
spring.shardingsphere.datasource.slave.username=root
spring.shardingsphere.datasource.slave.password=123456
# 主库从库逻辑定义
spring.shardingsphere.masterslave.name=ms
spring.shardingsphere.masterslave.master-data-source-name=master
spring.shardingsphere.masterslave.slave-data-source-names=slave
#显示sql
spring.shardingsphere.props.sql.show=true
6、创建测试类
@SpringBootTest
public class UserTests {
@Resource
private UserMapper userMapper;
@Test
public void insertUserInfo() {
for (int i = 1; i <= 10; i++) {
UserEntity userEntity = new UserEntity();
userEntity.setUserId(i);
userEntity.setUserName("user" + i);
userMapper.insertUserInfo(userEntity);
}
}
@Test
public void getUserInfo() {
System.out.println(userMapper.getUserInfo(1));
}
}
7、执行结果
可以看到数据从master新增，从slave读取。
评论区