基于ShardingSphere-JDBC的MySQL分库分表

最新推荐文章于 2024-05-10 09:54:33 发布

1244497026

最新推荐文章于 2024-05-10 09:54:33 发布

阅读量2.6k

点赞数 1

分类专栏：分库分表数据库文章标签： ShardingSphere 分库分表

本文链接：https://blog.csdn.net/qq_25863973/article/details/123319250

版权

数据库同时被 2 个专栏收录

5 篇文章 1 订阅

订阅专栏

分库分表

1 篇文章 0 订阅

订阅专栏

ShardingSphere-JDBC分库分表

1、ShardingSphere-JDBC

1.1、官网地址

https://shardingsphere.apache.org/

1.2、概念

1.2.1、逻辑表

相同结构的水平拆分数据库（表）的逻辑名称，是 SQL 中表的逻辑标识。例：订单数据根据主键尾数拆分为 5 张表，分别是 t_order_0 到 t_order_4，他们的逻辑表名为 t_order

1.2.2、真实表

在水平拆分的数据库中真实存在的物理表。即上个示例中的 t_order_0 到 t_order_4

1.2.3、绑定表

指分片规则一致的主表和子表。使用绑定表进行多表关联查询时，必须使用分片键进行关联，否则会出现笛卡尔积关联或跨库关联，从而影响查询效率。

1.2.4、广播表

指所有的分片数据源中都存在的表，表结构及其数据在每个数据库中均完全一致。适用于数据量不大且需要与海量数据的表进行关联查询的场景，例如：字典表

1.2.5、单表

指所有的分片数据源中仅唯一存在的表。适用于数据量不大且无需分片的表。

2、数据准备

2.1、数据库

sharding_0、sharding_1

2.2、表结构

2.2.1、用户表t_user

名	类型	长度	注释
id	bigint	20	主键id，用户id
user_name	varchar	32	用户名
pass_word	varchar	32	密码
nick_name	varchar	32	昵称

2.2.2、订单表t_order

名	类型	长度	注释
id	bigint	20	主键id，订单id
user_id	bigint	20	用户id，t_user.id
price	decimal	(10，2)	订单总价
sign_time	varchar	20	下单时间

2.2.3、商品表t_order_item

名	类型	长度	注释
id	bigint	20	主键id，订单id
user_id	bigint	20	用户id，t_user.id
order_id	bigint	20	订单id，t_order.id
price	decimal	(10，2)	订单总价

2.3、初始化表

在数据库sharding_0、sharding_1中分别建表t_user_i、t_order_i、t_order_item_i，其中 0 <= i < 5。每个数据库，共计15张表。t_user、t_order、t_order_item称为逻辑表，每个逻辑表分为10个真实表，分布在两个数据库中，分库（2个库）分表（10个表）已完成，如下图所示。
在这里插入图片描述

2.4、数据初始化

2.4.1、数据路由

2.4.1.1、路由建议

相关联的数据，路由到同一个数据库中，如果可以，路由到同一分片中，如：用户user0在数据库sharding_0的表t_user_0中，他/她创建的订单和商品，可以路由到数据库sharding_0的表t_order_0和t_order_item_0中，这样在联表查询时，可以避免笛卡尔积，效果和单库单表查询一致，大幅度减少查询时间。

2.4.1.2、数据库路由

用户id为偶数时，路由到sharding_0，奇数时，路由到sharding_1，即用户id以：0，2，4，6，8结尾的数据，只会存在sharding_0数据库中，以：1，3，5，7，9结尾的数据，只会存在sharding_1数据库中。

2.4.1.3、表路由

每个库有5张真实表，将100分成5份：

[0-20)中的偶数，结尾的存入sharding_0的分片0，[0-20)中的奇数，结尾的存入sharding_1的分片0。

[20-40)中的偶数，结尾的存入sharding_0的分片1，[0-20)中的奇数，结尾的存入sharding_1的分片1。

[40-60)中的偶数，结尾的存入sharding_0的分片2，[0-20)中的奇数，结尾的存入sharding_1的分片2。

[60-80)中的偶数，结尾的存入sharding_0的分片3，[0-20)中的奇数，结尾的存入sharding_1的分片3。

[80-100)中的偶数，结尾的存入sharding_0的分片4，[0-20)中的奇数，结尾的存入sharding_1的分片4。

或

简单的分片规则：分片 = 最后一位 % 2 == 0 ? 分片/2 : (最后一位 - 1) / 2

0、1分别存入分片sharding_0、sharding_1的分片0

2、3分别存入分片sharding_0、sharding_1的分片1，

4、5分别存入分片sharding_0、sharding_1的分片2，

6、7分别存入分片sharding_0、sharding_1的分片3，

8、9分别存入分片sharding_0、sharding_1的分片4

只要路由规则定下来了，查询数据就不会有问题，但是要尽量保证数据均摊在所有的真实表中，这样才能压力分摊

2.4.2、插入定量数据

这里插入数据1500w数据，每张表大概150w数据量
在这里插入图片描述
注：ShardingSpere-jdbc无法支持批量操作，最终还是一条一条插入，效率极低。

3、分库分表配置

3.1、配置文件

由于要设置t_order和t_order_item的user_id属性，故将ShardingSphere自带的雪花算法注释掉了

spring.main.allow-bean-definition-overriding=true

# 配置真实数据源
spring.shardingsphere.datasource.names=ds0,ds1
spring.shardingsphere.props.sql.show=true
spring.shardingsphere.props.executor.size=20
spring.shardingsphere.props.max.connections.size.per.query=1000

# 分库ds0,ds1
# 配置第 1 个数据源
spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds0.jdbc-url=jdbc:mysql://192.168.159.128:3306/sharding_0?allowMultiQueries=true
spring.shardingsphere.datasource.ds0.username=root
spring.shardingsphere.datasource.ds0.password=123456
# 配置第 2 个数据源
spring.shardingsphere.datasource.ds1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds1.jdbc-url=jdbc:mysql://192.168.159.128:3306/sharding_1?allowMultiQueries=true
spring.shardingsphere.datasource.ds1.username=root
spring.shardingsphere.datasource.ds1.password=123456

# 分片0，1，2，3，4
# 数据源ds0,ds1，表t_user_0...t_user_4，共计10张表（数据源个数 * 表个数）
spring.shardingsphere.sharding.tables.t_user.actual-data-nodes=ds$->{0..1}.t_user_$->{0..4}
# id为雪花算法生成
#spring.shardingsphere.sharding.tables.t_user.key-generator.column=id
#spring.shardingsphere.sharding.tables.t_user.key-generator.type=SNOWFLAKE
# 自定义分片：分表（根据id分表），分表规则自定义（SnoWalkerComplexShardingTB）
spring.shardingsphere.sharding.tables.t_user.table-strategy.complex.sharding-columns=id
spring.shardingsphere.sharding.tables.t_user.table-strategy.complex..algorithm-class-name=com.softwarevax.sharding.sharding.config.SnoWalkerComplexShardingTB
# 自定义分片：分库（根据id分库），分库规则自定义（SnoWalkerComplexShardingDB）
spring.shardingsphere.sharding.tables.t_user.database-strategy.complex.sharding-columns=id
spring.shardingsphere.sharding.tables.t_user.database-strategy.complex.algorithm-class-name=com.softwarevax.sharding.sharding.config.SnoWalkerComplexShardingDB

# 分片0，1，2，3，4
# 数据源ds0,ds1，表t_order_0...t_order_4，共计10张表（数据源个数 * 表个数）
spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=ds$->{0..1}.t_order_$->{0..4}
# id为雪花算法生成
#spring.shardingsphere.sharding.tables.t_order.key-generator.column=id
#spring.shardingsphere.sharding.tables.t_order.key-generator.type=SNOWFLAKE
# 自定义分片：分表（根据user_id分表），分表规则自定义（SnoWalkerComplexShardingTB）
spring.shardingsphere.sharding.tables.t_order.table-strategy.complex.sharding-columns=user_id
spring.shardingsphere.sharding.tables.t_order.table-strategy.complex..algorithm-class-name=com.softwarevax.sharding.sharding.config.SnoWalkerComplexShardingTB
# 自定义分片：分库（根据user_id分库），分库规则自定义（SnoWalkerComplexShardingDB）
spring.shardingsphere.sharding.tables.t_order.database-strategy.complex.sharding-columns=user_id
spring.shardingsphere.sharding.tables.t_order.database-strategy.complex.algorithm-class-name=com.softwarevax.sharding.sharding.config.SnoWalkerComplexShardingDB

# 分片0，1，2，3，4
# 数据源ds0,ds1，表t_order_item_0...t_order_item_4，共计10张表（数据源个数 * 表个数）
spring.shardingsphere.sharding.tables.t_order_item.actual-data-nodes=ds$->{0..1}.t_order_item_$->{0..4}
# id为雪花算法生成
#spring.shardingsphere.sharding.tables.t_order_item.key-generator.column=id
#spring.shardingsphere.sharding.tables.t_order_item.key-generator.type=SNOWFLAKE
# 自定义分片：分表（根据user_id分表），分表规则自定义（SnoWalkerComplexShardingTB）
spring.shardingsphere.sharding.tables.t_order_item.table-strategy.complex.sharding-columns=user_id
spring.shardingsphere.sharding.tables.t_order_item.table-strategy.complex..algorithm-class-name=com.softwarevax.sharding.sharding.config.SnoWalkerComplexShardingTB
# 自定义分片：分库（根据user_id分库），分库规则自定义（SnoWalkerComplexShardingDB）
spring.shardingsphere.sharding.tables.t_order_item.database-strategy.complex.sharding-columns=user_id
spring.shardingsphere.sharding.tables.t_order_item.database-strategy.complex.algorithm-class-name=com.softwarevax.sharding.sharding.config.SnoWalkerComplexShardingDB

# 绑定表
spring.shardingsphere.sharding.binding-tables[0]=t_user,t_order,t_order_item

# mybatis-plus配置
mybatis-plus.mapper-locations=classpath:/*/*Mapper.xml
mybatis-plus.type-aliases-package=com.softwarevax.sharding.sharding.web.entity
mybatis-plus.configuration.map-underscore-to-camel-case=true
mybatis-plus.configuration.cache-enabled=true
mybatis-plus.configuration.lazy-loading-enabled=true
#开启的话，延时加载一个属性时会加载该对象全部属性，否则按需加载属性
mybatis-plus.configuration.multiple-result-sets-enabled=true
mybatis-plus.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl
pagehelper.helperDialect=mysql
pagehelper.reasonable=true
pagehelper.supportMethodsArguments=true
pagehelper.params=count=countSql

3.2、数据库路由

实现接口：ComplexKeysShardingAlgorithm，自定义数据库路由

2.4.1.2章节的代码实现

import lombok.extern.slf4j.Slf4j;
import org.apache.shardingsphere.api.sharding.complex.ComplexKeysShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.complex.ComplexKeysShardingValue;

import java.util.Collection;
import java.util.HashSet;
import java.util.Map;
import java.util.Set;

@Slf4j
public class SnoWalkerComplexShardingDB implements ComplexKeysShardingAlgorithm<Long> {


    /**
     *
     * @param availableTargetNames 配置的数据源(ds0,ds1)
     * @param shardingValue 分片的字段，及字段对应的值，由配置spring.shardingsphere.sharding.tables.t_user.key-generator.column决定
     * 该值可以配置成多个，
     * @return 数据要插入的数据库对应数据源的集合ds0,ds1
     */
    @Override
    public Collection<String> doSharding(Collection<String> availableTargetNames, ComplexKeysShardingValue<Long> shardingValue) {
        Map<String, Collection<Long>> columnNameAndShardingValuesMap = shardingValue.getColumnNameAndShardingValuesMap();
        //System.out.println("所有的sharding列的值为" + columnNameAndShardingValuesMap);
        Set<String> result = new HashSet<>();
        // 可以配置多个，此处只取第一个列，即用户id
        String key = columnNameAndShardingValuesMap.keySet().iterator().next();
        Collection idColl = columnNameAndShardingValuesMap.get(key);
        for (Object idObj: idColl) {
            if (idObj instanceof Integer || idObj instanceof Long) {
                long idTemp = Long.parseLong(idObj.toString());
                long target = idTemp % 2;
                for (Object targetNames : availableTargetNames) {
                    if (targetNames.toString().endsWith(target + "")) {
                        result.add(targetNames.toString());
                    }
                }
            }
        }
        return result;
    }
}

3.3、表路由

实现接口：ComplexKeysShardingAlgorithm，自定义表路由

2.4.1.3章节的代码实现

import lombok.extern.slf4j.Slf4j;
import org.apache.shardingsphere.api.sharding.complex.ComplexKeysShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.complex.ComplexKeysShardingValue;

import java.util.Collection;
import java.util.HashSet;
import java.util.Map;
import java.util.Set;

@Slf4j
public class SnoWalkerComplexShardingTB implements ComplexKeysShardingAlgorithm {

    /**
     *
     * @param availableTargetNames 配置的数据源(ds0,ds1)
     * @param shardingValue 分片的字段，及字段对应的值
     * @return 数据要插入的数据库对应数据源的集合ds0,ds1
     */
    @Override
    public Collection<String> doSharding(Collection availableTargetNames, ComplexKeysShardingValue shardingValue) {
        Map<String, Collection> columnNameAndShardingValuesMap = shardingValue.getColumnNameAndShardingValuesMap();
        //System.out.println("所有的sharding列的值为" + columnNameAndShardingValuesMap);
        Set<String> result = new HashSet<>();
        // 可以配置多个，此处只取第一个列，即用户id
        String key = columnNameAndShardingValuesMap.keySet().iterator().next();
        Collection idColl = columnNameAndShardingValuesMap.get(key);
        for (Object idObj: idColl) {
            if (idObj instanceof Integer || idObj instanceof Long) {
                long idTemp = Long.parseLong(idObj.toString());
                String idStr = String.valueOf(idTemp);
                String a = idStr.substring(idStr.length() - 2, idStr.length() - 1);
                Integer num = Integer.valueOf(a);
                String endWith = String.valueOf(num % 2 == 1 ? (num - 1) / 2 : num / 2);
                for (Object targetNames : availableTargetNames) {
                    if (targetNames.toString().endsWith(endWith + "")) {
                        result.add(targetNames.toString());
                    }
                }
            }
        }
        return result;
    }
}

3.4、pom文件配置

sharding-jdbc版本为4.1.1

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

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter</artifactId>
		</dependency>

		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<scope>runtime</scope>
		</dependency>

		<dependency>
			<groupId>com.baomidou</groupId>
			<artifactId>mybatis-plus-boot-starter</artifactId>
			<version>3.1.0</version>
		</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>

		<!-- SpringBoot Web容器 -->
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>

		<dependency>
			<groupId>com.github.pagehelper</groupId>
			<artifactId>pagehelper-spring-boot-starter</artifactId>
			<version>1.2.3</version>
			<exclusions>
				<exclusion>
					<groupId>org.mybatis</groupId>
					<artifactId>mybatis</artifactId>
				</exclusion>
				<exclusion>
					<groupId>org.mybatis</groupId>
					<artifactId>mybatis-spring</artifactId>
				</exclusion>
			</exclusions>
		</dependency>

3.5、数据查询

3.5.1、单个逻辑表查询

3.5.1.1、分页查询

select * from t_user

在这里插入图片描述

3.5.1.1.1、分页查询的弊端

在这里插入图片描述

当offset很大时，查询的时间太慢

3.5.1.1.2、查询慢的原因

1、ShardingSphere-JDBC框架原因

2、Oracle、MySQL数据库原因

如果有分页语句：select * from t_user limit 1000000,10

ShardingSphere-JDBC会查出10000010行内的所有数据，然后比较，而单表操作时，要先找到1000000的位置，然后取出后10条数据即可，当数据量较大时，定位到1000000本身需要一些时间，这个值越大，需要的时间越久。ShardingSphere-JDBC为什么需要查出所有数据，原因如下：

成绩t1	成绩t2
100	99
97	98
95	93
90	32

取出逻辑表成绩排名2，3名的，正确的结果为：99和98

select * from t1 limit 1,2（取出第一行后的2条数据，即97，95）

select * from t2 limit 1,2（取出第一行后的2条数据，即98，93）

在97，95，98，93，排序后为：98，97，95，93，，排名2，3为97，95，与实际要的99，98不符

所以框架选择比较所有的数据，实际的sql为：select * from t_user limit 0,1000010。如果查询的结果有1000010条数据，将全部数据加载到内存是不太现实的，

由于每个结果集的记录是有序的，因此 ShardingSphere 每次比较仅获取各个分片的当前结果集记录，驻留在内存中的记录仅为当前路由到的分片的结果集的当前游标指向而已，详见：

https://shardingsphere.apache.org/document/current/cn/features/sharding/use-norms/pagination/
在这里插入图片描述
解决分页查询慢的思路：假设列表有排序字段column1、column2、column3，则将数据id、column1、column2、column3存入mongodb或es，分页查询时，先使用mongodb或es排序，找到数据对应的id，然后将id作为参数，查到对应的详情列表

3.5.1.2、单条查询

select * from t_user where id = ‘950084434298667033’
在这里插入图片描述

分库分表的单条数据查询，和不分库分表的查询，差不多。但是两条以上的输入分组（需要查不同的表或数据库），会略有差异，详见：

ExecutorEngine
private <I, O> List<O> serialExecute(Collection<InputGroup<I>> inputGroups, GroupedCallback<I, O> firstCallback, GroupedCallback<I, O> callback) throws SQLException {
        Iterator<InputGroup<I>> inputGroupsIterator = inputGroups.iterator();
        // 先取出一条执行
        InputGroup<I> firstInputs = (InputGroup)inputGroupsIterator.next();
        List<O> result = new LinkedList(this.syncExecute(firstInputs, null == firstCallback ? callback : firstCallback));
        Iterator var7 = Lists.newArrayList(inputGroupsIterator).iterator();

        while(var7.hasNext()) {
            InputGroup<I> each = (InputGroup)var7.next();
            result.addAll(this.syncExecute(each, callback));
        }

        return result;
    }

3.5.2、多表联表查询

t_user、t_order、t_order_item三表联表查询，sql如下：

select
    u.*, o.*, i.*
from t_user u
    left join t_order o on o.user_id = u.id
    left join t_order_item i on i.user_id = u.id
where u.id = #{userId}

resultMap

<resultMap id="user_order_map" type="com.softwarevax.sharding.sharding.web.entity.vo.UserVO">
        <id column="id" property="id" />
        <result column="user_name" property="userName" />
        <result column="pass_word" property="passWord" />
        <result column="nick_name" property="nickName" />
        <collection property="orders" ofType="com.softwarevax.sharding.sharding.web.entity.vo.OrderVO">
            <id column="id" property="id" />
            <result column="user_id" property="userId" />
            <result column="price" property="price" />
            <result column="sign_time" property="signTime" />
            <collection property="items" ofType="com.softwarevax.sharding.sharding.web.entity.OrderItem">
                <id column="id" property="id" />
                <result column="user_id" property="userId" />
                <result column="order_id" property="orderId" />
                <result column="item_name" property="itemName" />
                <result column="price" property="price" />
            </collection>
        </collection>
    </resultMap>

多表联表查询时，表的绑定至关重要，即使传入了id（950078224832921602）

#spring.shardingsphere.sharding.binding-tables[0]=t_user,t_order,t_order_item

根据id，可以路由到数据库sharding_0，id=950078224832921602的用户，在t_user_0表中（根据2.4.1.3的[0-20)中的偶数，结尾的存入sharding_0的分片0），

但没有绑定时，无法确定t_order和t_order_item在那个分片中，只能全部都找一下，需要做 1 * 5(sharding_0的t_order的分片数) * 5(sharding_0的t_order_item的分片数) = 25次查询
在这里插入图片描述

绑定t_user和t_order，可以根据user_id（id=950078224832921602）确定，t_order也在同一分片中，因为t_user和t_order都使用的用户id进行分片的，且join是按照user_id关联的，但是不知道t_order_item，故需要做 1 * 1 * 5(sharding_0的t_order_item的分片数) = 5次查询

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

在这里插入图片描述

注：绑定表，指分片规则一致的主表和子表。使用绑定表进行多表关联查询时，必须使用分片键进行关联，否则会出现笛卡尔积关联或跨库关联，从而影响查询效率

绑定t_user和t_order和t_order_item，三张表都能确定分区，一次查询即可

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

在这里插入图片描述

查询结果如下：
在这里插入图片描述

4、项目地址

https://github.com/softwarevax/sharding

1244497026

关注

1
点赞
踩
9

收藏

觉得还不错? 一键收藏
0
评论
基于ShardingSphere-JDBC的MySQL分库分表

ShardingSphere-JDBC分库分表1、ShardingSphere-JDBC1.1、官网地址https://shardingsphere.apache.org/1.2、概念1.2.1、逻辑表相同结构的水平拆分数据库（表）的逻辑名称，是 SQL 中表的逻辑标识。例：订单数据根据主键尾数拆分为 5 张表，分别是 t_order_0 到 t_order_4，他们的逻辑表名为 t_order1.2.2、真实表在水平拆分的数据库中真实存在的物理表。即上个示例中的 t_order_0 到
复制链接

扫一扫