Sharding-JDBC实战（水平分表，读写分离）

一. 简单介绍 

Sharding-JDBC是一个轻量级 Java 框架，在 Java 的 JDBC 层提供的额外服务， 它使用客户端直连数据库，以 jar 包形式提供服务，无需额外部署和依赖，可理解为增强版的 JDBC 驱动，完全兼容 JDBC 和各种 ORM 框架 在使用Sharding-JDBC首先要清楚以下几点：

它是一个轻量级的Java框架，可以理解是增强的JDBC驱动 Sharding-JDBC是不做分库分表的，库与表是由数据库工程师分好的，通过引入Sharding-JDBC.jar配置好配置，解决多数据源切换与多数据源的操作

读写分离也是一种很好的提升数据库性能的解决方案，在了解如何使用Sharding-JDBC之前首先需学会如何部署Mysql主从配置， Sharding-JDBC是不做主从复制数据同步的，主从复制需要通过配置Mysql实现，而Sharding-JDBC只是根据语义(CRUD)判断你是做增删改查的那种操作，并且给你路由到主库/从库

二.  SpringBoot配置

pom.xml文件

<?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.3.2.RELEASE</version><relativePath/> <!-- lookup parent from repository --></parent><groupId>com.example</groupId><artifactId>docker-demo</artifactId><version>0.0.1-SNAPSHOT</version><name>docker-demo</name><description>Demo project for Spring Boot</description><properties><java.version>1.8</java.version></properties><dependencies><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-jdbc</artifactId></dependency><dependency><groupId>org.mybatis.spring.boot</groupId><artifactId>mybatis-spring-boot-starter</artifactId><version>2.1.3</version></dependency><dependency><groupId>org.springframework</groupId><artifactId>spring-web</artifactId></dependency><dependency><groupId>com.alibaba</groupId><artifactId>druid</artifactId><version>1.1.23</version></dependency><dependency><groupId>org.apache.shardingsphere</groupId><artifactId>sharding-jdbc-spring-boot-starter</artifactId><version>4.1.1</version></dependency><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><scope>runtime</scope></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>--><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-test</artifactId><scope>test</scope></dependency><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId></dependency></dependencies><build><plugins><plugin><groupId>org.springframework.boot</groupId><artifactId>spring-boot-maven-plugin</artifactId></plugin><plugin><groupId>org.apache.maven.plugins</groupId><artifactId>maven-surefire-plugin</artifactId><configuration><skipTests>true</skipTests></configuration></plugin></plugins></build><repositories><repository><id>spring-snapshots</id><name>Spring Snapshots</name><url>https://repo.spring.io/snapshot</url><snapshots><enabled>true</enabled></snapshots></repository><repository><id>spring-milestones</id><name>Spring Milestones</name><url>https://repo.spring.io/milestone</url><snapshots><enabled>false</enabled></snapshots></repository></repositories><pluginRepositories><pluginRepository><id>spring-snapshots</id><name>Spring Snapshots</name><url>https://repo.spring.io/snapshot</url><snapshots><enabled>true</enabled></snapshots></pluginRepository><pluginRepository><id>spring-milestones</id><name>Spring Milestones</name><url>https://repo.spring.io/milestone</url><snapshots><enabled>false</enabled></snapshots></pluginRepository></pluginRepositories></project>

package com.example.demo;import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;@SpringBootApplication
@RestController
@MapperScan(basePackages = {"com.example.mapper"})
public class DemoApplication {@GetMapping("/")public String home() {return "Hello World!";}public static void main(String[] args) {SpringApplication.run(DemoApplication.class, args);}}

 三. 水平分表 

1. 创建2个表

CREATE TABLE `orders_1` (`id` int(11) NOT NULL,`orderType` int(11) DEFAULT NULL,`customerId` int(11) DEFAULT NULL,`amount` double DEFAULT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8CREATE TABLE `orders_2` (`id` int(11) NOT NULL,`orderType` int(11) DEFAULT NULL,`customerId` int(11) DEFAULT NULL,`amount` double DEFAULT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

 2. application.properties配置

修改application.properties增加shardingsphere配置在ShardingSphere官网用户手册=>ShardingSphere-JDBC=>配置手册中都有详细的说明，如下：

server.port=8090#整合mybatis
mybatis.type-aliases-package=com.example.mapper#配置数据源的名称
spring.shardingsphere.datasource.names=ds1#配置数据源的具体内容，
spring.shardingsphere.datasource.ds1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds1.url=jdbc:mysql://127.0.0.1:3306/mySww?useUnicode=true&characterEncoding=utf-8
spring.shardingsphere.datasource.ds1.username=root
spring.shardingsphere.datasource.ds1.password=123456sww#指定orders表的分布情况，配置表在哪个数据库中，表名称是什么
spring.shardingsphere.sharding.tables.orders.actual-data-nodes=ds1.orders_$->{1..2}
#指定orders表里主键id生成策略
spring.shardingsphere.sharding.tables.orders.key-generator.column=id
spring.shardingsphere.sharding.tables.orders.key-generator.type=SNOWFLAKE#指定分片策略。根据id的奇偶性来判断插入到哪个表
spring.shardingsphere.sharding.tables.orders.table-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.orders.table-strategy.inline.algorithm-expression=orders_${id%2+1}
#spring.shardingsphere.sharding.tables.orders.table-strategy.inline.algorithm-expression=orders_${id%2}#打开sql输出日志
spring.shardingsphere.props.sql.show=true

 3. 操作数据库

package com.example.mapper;import com.example.domain.Orders;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;/*** @auther: TF12778* @date: 2021/12/30 10:34* @description:*/
@Repository
@Mapper
public interface OrdersMapper {@Insert("insert into orders(id,orderType,customerId,amount) values(#{id},#{orderType},#{customerId},#{amount})")public void insert(Orders orders);@Select("select * from orders where id = #{id}")@Results({@Result(property = "id", column = "id"),@Result(property = "orderType", column = "orderType"),@Result(property = "customerId", column = "customerId"),@Result(property = "amount", column = "amount")})public Orders selectOne(Integer id);
}

4.单元测试

package com.example.demo;import com.example.domain.Orders;
import com.example.domain.Person;
import com.example.mapper.OrdersMapper;
import com.example.mapper.PersonMapper;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;/*** @auther: TF12778* @date: 2021/12/30 10:35* @description:*/
@SpringBootTest
public class ShardingsphereDemoApplicationTests {@Autowiredprivate OrdersMapper ordersMapper;@Testpublic void addOrders(){for (int i = 1; i <=10 ; i++) {Orders orders = new Orders();orders.setId(i);orders.setCustomerId(i);orders.setOrderType(i);orders.setAmount(1000.0*i);ordersMapper.insert(orders);}}@Testpublic void queryOrders(){Orders orders = ordersMapper.selectOne(1);System.out.println(orders);}
}

可以看到id为偶数的插入到了orders_1中，奇数插入到了orders_2中.

四. 读写分离

1. application.properties配置

修改application.properties增加shardingsphere配置在ShardingSphere官网用户手册=>ShardingSphere-JDBC=>配置手册中都有详细的说明

server.port=8090#整合mybatis
mybatis.type-aliases-package=com.example.mapper#配置数据源
spring.shardingsphere.datasource.names=ds1,ds2#配置第一个数据源
spring.shardingsphere.datasource.ds1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds1.url=jdbc:mysql://127.0.0.1:3306/mySww?serverTimezone=UTC
spring.shardingsphere.datasource.ds1.username=root
spring.shardingsphere.datasource.ds1.password=123456sww#配置第二个数据源
spring.shardingsphere.datasource.ds2.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds2.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds2.url=jdbc:mysql://127.0.0.1:3306/sww?serverTimezone=UTC
spring.shardingsphere.datasource.ds2.username=root
spring.shardingsphere.datasource.ds2.password=123456sww#主库从库逻辑定义
spring.shardingsphere.masterslave.name=ms
spring.shardingsphere.masterslave.master-data-source-name=ds1
spring.shardingsphere.masterslave.slave-data-source-names=ds2#显示执行的sql
spring.shardingsphere.props.sql.show=true

2. 操作数据库 

package com.example.mapper;import com.example.domain.Person;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;/*** @Auther: TF12778* @Date: 2021/12/30 16:14* @Description:*/@Repository
public interface PersonMapper {@Insert("insert into person(id,name) values(#{id},#{name})")public void insertPerson(Person person);@Select("select * from person where id = #{id}")public Person queryPerson(Long id);
}

3.单元测试 

package com.example.demo;import com.example.domain.Orders;
import com.example.domain.Person;
import com.example.mapper.OrdersMapper;
import com.example.mapper.PersonMapper;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;/*** @auther: TF12778* @date: 2021/12/30 10:35* @description:*/
@SpringBootTest
public class ShardingsphereDemoApplicationTests {@Autowiredprivate PersonMapper personMapper;@Testpublic void insertPerson(){Person person = new Person();person.setId(1l);person.setName("zhangsan");personMapper.insertPerson(person);}@Testpublic void queryPerson(){Person person = personMapper.queryPerson(1l);System.out.println(person);}
}

4. 日志输出：

2021-12-30 17:48:12.322  INFO 11576 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds1 ::: insert into person(id,name) values(?,?)

从上面的日志可以看出，写入的时候走的是主库ds1

2021-12-30 16:22:49.612  INFO 10135 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds2 ::: select * from person where id = ?
null

从上面的日志可以看出，查询的时候走的是存库ds2。这里因为我没配置mysql的主存同步，所以查出来的数据为空。假如配置好的话，查出的数据应该跟主库一样。