04.数据库分库分表-shardingjdbc

摘要

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

• 适用于任何基于JDBC的ORM框架，如：JPA, Hibernate, Mybatis, Spring JDBC Template或直接使用JDBC。
• 支持任何第三方的数据库连接池，如：DBCP, C3P0, BoneCP, Druid, HikariCP等。
• 支持任意实现JDBC规范的数据库。目前支持MySQL，Oracle，SQLServer，PostgreSQL以及任何遵循SQL92标准的数据库。
  

应用

以springboot为例，介绍使用当时以及效果。

集成

1. maven依赖

<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
    <version>4.0.1</version>
</dependency>
<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-core-common</artifactId>
    <version>4.0.1</version>
</dependency>

2. 配置

#数据源名称
spring.shardingsphere.datasource.names=ds0
#数据源配置
spring.shardingsphere.datasource.ds0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.p6spy.engine.spy.P6SpyDriver
spring.shardingsphere.datasource.ds0.url=jdbc:p6spy:mysql://localhost:3306/test_ym?useunicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai
spring.shardingsphere.datasource.ds0.username=root
spring.shardingsphere.datasource.ds0.password=root
#t_user表分片
spring.shardingsphere.sharding.tables.t_user.actualDataNodes=ds0.t_user_$->{['0_0','0_1','9_0','9_1']}
#t_user表分表键
spring.shardingsphere.sharding.tables.t_user.tableStrategy.standard.shardingColumn=school_id
#t_user表分表策略
spring.shardingsphere.sharding.tables.t_user.tableStrategy.standard.preciseAlgorithmClassName=com.ym.config.CustomShardingAlgorithm

#t_class表分片
spring.shardingsphere.sharding.tables.t_class.actualDataNodes=ds0.t_class_$->{['0_0','0_1','9_0','9_1']}
#t_class表分表键
spring.shardingsphere.sharding.tables.t_class.tableStrategy.standard.shardingColumn=school_id
#t_class表分表策略
spring.shardingsphere.sharding.tables.t_class.tableStrategy.standard..preciseAlgorithmClassName=com.ym.config.CustomShardingAlgorithm
#绑定表
spring.shardingsphere.sharding.binding-tables[0]=t_user,t_class

3. 启动
由于druid连接池启动时会自动创建数据源DataSource。需要springboot启动时停用druid默认创建数据源。

@EnableAutoConfiguration(exclude = DruidDataSourceAutoConfigure.class)

4.分表策略
上一篇已经说明，我们自己写了一个服务(sharding-center)，实现了根据逻辑表名+键值获取具体物理表表名。此处分表策略只需要通过dubbo调用sharding-center中的ShardingRouterApi.getRouterTable（）服务即可。
使用ShardingRouterTemplate 将dubbo服务封装

@Component
public class ShardingRouterTemplate {
    @Reference(check = false)
    ShardingRouterApi shardingRouterApi;

    public String getRouterTable(String logicTableName, String shardingValue){
        Result result = shardingRouterApi.getRouterTable(logicTableName,shardingValue);
        if(ResultCode.SUCCESS==result.getCode()){
            return String.valueOf(result.getData());
        }
        throw new IllegalArgumentException();
    }
}

分表策略中直接调用ShardingRouterTemplate获取物理表名：

public class CustomShardingAlgorithm implements PreciseShardingAlgorithm<String> {
    @Override
    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<String> shardingValue) {
        ShardingRouterTemplate template=SpringBeanFactory.getBean(ShardingRouterTemplate.class);
        return template.getRouterTable(shardingValue.getLogicTableName(),shardingValue.getValue());

    }
}

ShardingRouterTemplate 代码服务

效果

配置的分表规则如下：

id	type	logic_table_name	physic_table_name	rule	deleted
1	normal	t_user	t_user_0_0	range:1-3	0
2	normal	t_user	t_user_0_1	single:4,5	0
4	default	t_user	t_user_9_*	mod:2	0
5	normal	t_class	t_class_0_0	range:1-3	0
6	normal	t_class	t_class_0_1	single:4,5	0
8	default	t_class	t_class_9_*	mod:2	0

规则为：
school_id=1、2、3时对应物理表为：t_user_0_0
school_id=4、5时对应物理表为：t_user_0_0
school_id=其他时对应物理表为：t_user_9_0或者t_user_9_1
使用p6spy插件直接打印最终执行的sql语句查看数据入库情况。
1. 插入数据
插入school_id=1-9的数据

insert into t_class_0_0(id,class_name,school_id) values(164, '班级-1-1', '1')
insert into t_class_0_0(id,class_name,school_id) values(165, '班级-1-2', '1')
insert into t_class_0_0(id,class_name,school_id) values(166, '班级-2-1', '2')
insert into t_class_0_0(id,class_name,school_id) values(167, '班级-2-2', '2')
insert into t_class_0_0(id,class_name,school_id) values(168, '班级-3-1', '3')
insert into t_class_0_0(id,class_name,school_id) values(169, '班级-3-2', '3')
insert into t_class_0_1(id,class_name,school_id) values(170, '班级-4-1', '4')
insert into t_class_0_1(id,class_name,school_id) values(171, '班级-4-2', '4')
insert into t_class_0_1(id,class_name,school_id) values(172, '班级-5-1', '5')
insert into t_class_0_1(id,class_name,school_id) values(173, '班级-5-2', '5')
insert into t_class_9_0(id,class_name,school_id) values(174, '班级-6-1', '6')
insert into t_class_9_0(id,class_name,school_id) values(175, '班级-6-2', '6')
insert into t_class_9_1(id,class_name,school_id) values(176, '班级-7-1', '7')
insert into t_class_9_1(id,class_name,school_id) values(177, '班级-7-2', '7')
insert into t_class_9_0(id,class_name,school_id) values(178, '班级-8-1', '8')
insert into t_class_9_0(id,class_name,school_id) values(179, '班级-8-2', '8')
insert into t_class_9_1(id,class_name,school_id) values(180, '班级-9-1', '9')
insert into t_class_9_1(id,class_name,school_id) values(181, '班级-9-2', '9')

2. 查询
查询school_id =1、2、3、4的数据

select * from t_user_0_0 where school_id in  ('1','2','3','4')
select * from t_user_0_1 where school_id in  ('1','2','3','4')

3. 连表查询
未配置表绑定时连表查询会进行笛卡尔积查询

select * from t_user_0_0 u inner join t_class_9_1 c on u.class_id=c.id where u.school_id = '1'
select * from t_user_0_0 u inner join t_class_9_0 c on u.class_id=c.id where u.school_id = '1'
select * from t_user_0_0 u inner join t_class_0_0 c on u.class_id=c.id where u.school_id = '1'
select * from t_user_0_0 u inner join t_class_0_1 c on u.class_id=c.id where u.school_id = '1'

配置了表绑定时直接绑定查询

spring.shardingsphere.sharding.binding-tables[0]=t_user,t_class

select * from t_user_0_0 u inner join t_class_0_0 c on u.class_id=c.id where u.school_id = '1'