sharding-jdbc实现分库分表

介绍

定位为轻量级 Java 框架，在 Java 的 JDBC 层提供的额外服务。 它使用客户端直连数据库，以 jar 包形式提供服务，无需额外部署和依赖，可理解为增强版的 JDBC 驱动，完全兼容 JDBC 和各种 ORM 框架。

• sharding-jdbc是一个分布式的关系型数据库中间件轻量级的Java框架，以jar的方式提供服务
• 适用于任何基于 JDBC 的 ORM 框架，如：JPA, Hibernate, Mybatis, Spring JDBC Template 或直接使用 JDBC。
• 支持任何第三方的数据库连接池,，如：DBCP, C3P0, BoneCP, Druid, HikariCP 等。
• 支持任意实现 JDBC 规范的数据库，目前支持 MySQL，Oracle，SQLServer，PostgreSQL 以及任何遵循 SQL92 标准的数据库。

• 服务配置

Java API
yaml
properties
spring命名空间

• mycat 区别

MyCat是服务端的代理，Sharding-Jdbc是客户端代理
实际开发中如果企业有DBA建议使用MyCat，都是开发人员建议使用sharding-jdbc
MyCat不支持在一个库内进行水平分表，而sharding-jdbc支持在同一个数据库中进行水平分表

• 表概念

数据节点：存储数据的MySQL节点
绑定表：相当于MyCat中的子表
广播表：相当于MyCat中的全局表

环境

• 两个数据库

分库分表实现

创建数据库

两个数据库分别建两个表

CREATE TABLE `order_info_1` (
  `id` int(11) NOT NULL,
  `order_amount` decimal(10,2) DEFAULT NULL,
  `order_status` int(255) DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `order_info_2` (
  `id` int(11) NOT NULL,
  `order_amount` decimal(10,2) DEFAULT NULL,
  `order_status` int(255) DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

依赖

<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
    <version>4.0.0-RC2</version>
</dependency>
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-jdbc</artifactId>
    <version>5.2.7.RELEASE</version>
</dependency>
<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.16</version>
</dependency>
<dependency>
    <groupId>com.zaxxer</groupId>
    <artifactId>HikariCP</artifactId>
    <version>2.6.1</version>
    <exclusions>
        <exclusion>
            <artifactId>slf4j-api</artifactId>
            <groupId>org.slf4j</groupId>
        </exclusion>
    </exclusions>
</dependency>

配置

# 配置数据源
spring.shardingsphere.datasource.names=ds0,ds1
spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds0.jdbcUrl=jdbc:mysql://192.168.56.132:3306/shard_order
spring.shardingsphere.datasource.ds0.username=guanzc
spring.shardingsphere.datasource.ds0.password=123456
# 数据源
spring.shardingsphere.datasource.ds1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds1.jdbcUrl=jdbc:mysql://192.168.56.134:3306/shard_order
spring.shardingsphere.datasource.ds1.username=guanzc
spring.shardingsphere.datasource.ds1.password=123456

# 具体的分片规则,基于数据节点
spring.shardingsphere.sharding.tables.order_info.actual-data-nodes=ds$->{0..1}.order_info_$->{1..2}
# 分库的规则
spring.shardingsphere.sharding.tables.order_info.database-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.order_info.database-strategy.inline.algorithm-expression=ds$->{id % 2}
# 分表的规则
spring.shardingsphere.sharding.tables.order_info.table-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.tables.order_info.table-strategy.inline.algorithm-expression=order_info_$->{user_id % 2 + 1}

• 按照id奇偶数分库，user_id 奇偶分表

插入、查询数据

@Autowired
private JdbcTemplate jdbcTemplate;

@Test
void contextLoads() {
    String sql = "insert into order_info(id,order_amount,order_status,user_id) values(1,23.12,1,2)";
    int i = jdbcTemplate.update(sql);
    System.out.println("新增数据："+i);
	sql ="select * from order_info";
    List<Map<String, Object>> result = jdbcTemplate.queryForList(sql);
        System.out.println("条数："+result.size());
}

按照分库分表规则， 执行插入sql 数据会保存到，ds1的 order_info_1表中。

广播表

字典表，在每个数据库中都需要配置

sql语句

CREATE TABLE `province_info` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

配置广播表

spring.shardingsphere.sharding.broadcast-tables=province_info

测试

 @Test
 public void testBroadTables(){
     String sql = "insert into province_info(id, name) values(1, '北京')";
     int i = jdbcTemplate.update(sql);
     System.out.println("插入条数："+i);
     //查询
     sql ="select * from province_info";
     List<Map<String, Object>> result = jdbcTemplate.queryForList(sql);
     for (Map<String, Object> map : result){
         System.out.println(map.get("id")+"-------------- "+map.get("name"));
     }
 }

• 插入时，会分别想两个数据插入一样的数据。实际是两条数据
• 查询时，只会查询出一条数据

绑定表

父子表

sql 语句

分别创建 order_info_1、2 的绑定表 order_item_1、2

CREATE TABLE `order_item_1` (
  `id` int(11) DEFAULT NULL,
  `product_name` varchar(255) DEFAULT NULL,
  `order_id` int(11) DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `order_item_2` (
  `id` int(11) DEFAULT NULL,
  `product_name` varchar(255) DEFAULT NULL,
  `order_id` int(11) DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

配置分库分表

#绑定表-分片规则
spring.shardingsphere.sharding.tables.order_item.actual-data-nodes=ds$->{0..1}.order_item_${1..2}
spring.shardingsphere.sharding.tables.order_item.database-strategy.inline.sharding-column=order_id
spring.shardingsphere.sharding.tables.order_item.database-strategy.inline.algorithm-expression=ds$->{order_id % 2}
#绑定表-分表规则
spring.shardingsphere.sharding.tables.order_item.table-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.order_item.table-strategy.inline.algorithm-expression=order_item_$->{id % 2 + 1}
# 配置绑定表
spring.shardingsphere.sharding.binding-tables=order_info,order_item

测试

@Test
void bangding(){
    String sql = "insert into order_item(id, product_name, order_id, user_id) values(1, 'HUAWEI',2,2),(2, 'HUAWEI',3,3)";
    int i = jdbcTemplate.update(sql);
    System.out.println("插入条数："+i);

}

• 根据分片、分表规则，
• id=1 数据存储在 ds0的 order_item_2表中
• id=2 数据存储在 ds1的 order_item_1表中

读写分离

配置，至少需要配置三个服务器，两主一从。配置按照两主两从。

配置数据库

# 指定主从的配置节点
spring.shardingsphere.datasource.names=master0,master0slave0,master1,master1slave0
数据源配置...

# 读写分离主从关系绑定
spring.shardingsphere.sharding.master-slave-rules.ds0.master-data-source-name=master0
spring.shardingsphere.sharding.master-slave-rules.ds0.slave-data-source-names=master0slave0
spring.shardingsphere.sharding.master-slave-rules.ds0.load-balance-algorithm-type=round_robin

spring.shardingsphere.sharding.master-slave-rules.ds1.master-data-source-name=master1
spring.shardingsphere.sharding.master-slave-rules.ds1.slave-data-source-names=master1slave0
spring.shardingsphere.sharding.master-slave-rules.ds1.load-balance-algorithm-type=random

测试

@Test
void contextLoads() {
    String sql = "insert into order_info(id,order_amount,order_status,user_id) values(1,243.18,1,2)";
    int i = jdbcTemplate.update(sql);
    System.out.println("新增数据："+i);
}

• 干掉一个数据库后，插入数据，测试