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文件



	4.0.0
	
		org.springframework.boot
		spring-boot-starter-parent
		2.3.2.RELEASE
		 
	
	com.example
	docker-demo
	0.0.1-SNAPSHOT
	docker-demo
	Demo project for Spring Boot

	
		1.8
	

	
		
			org.springframework.boot
			spring-boot-starter-jdbc
		
		
			org.mybatis.spring.boot
			mybatis-spring-boot-starter
			2.1.3
		
		
			org.springframework
			spring-web
		
		
			com.alibaba
			druid
			1.1.23
		
		
			org.apache.shardingsphere
			sharding-jdbc-spring-boot-starter
			4.1.1
		
		
			mysql
			mysql-connector-java
			runtime
		
		
			
			
			
			
				
					
					
				
			
		
		
			org.springframework.boot
			spring-boot-starter-test
			test
		

		
			org.projectlombok
			lombok
		
	

	
		
			
				org.springframework.boot
				spring-boot-maven-plugin
			
			
				org.apache.maven.plugins
				maven-surefire-plugin
				
					true
				
			
		
	

	
		
			spring-snapshots
			Spring Snapshots
			https://repo.spring.io/snapshot
			
				true
			
		
		
			spring-milestones
			Spring Milestones
			https://repo.spring.io/milestone
			
				false
			
		
	
	
		
			spring-snapshots
			Spring Snapshots
			https://repo.spring.io/snapshot
			
				true
			
		
		
			spring-milestones
			Spring Milestones
			https://repo.spring.io/milestone
			
				false

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=utf8


CREATE 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;


@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;


@SpringBootTest
public class ShardingsphereDemoApplicationTests {

    @Autowired
    private OrdersMapper ordersMapper;

    @Test
    public 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);
        }
    }

    @Test
    public 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;



@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;


@SpringBootTest
public class ShardingsphereDemoApplicationTests {

    @Autowired
    private PersonMapper personMapper;

    @Test
    public void insertPerson(){
        Person person = new Person();
        person.setId(1l);
        person.setName("zhangsan");
        personMapper.insertPerson(person);
    }

    @Test
    public 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的主存同步，所以查出来的数据为空。假如配置好的话，查出的数据应该跟主库一样。

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

Java相关栏目本月热门文章