基于ShardingSphere-JDBC的MySQL分库分表

https://shardingsphere.apache.org/

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、单表

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

sharding_0、sharding_1

在数据库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个表）已完成，如下图所示。

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

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

每个库有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

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

这里插入数据1500w数据，每张表大概150w数据量

注：ShardingSpere-jdbc无法支持批量操作，最终还是一条一条插入，效率极低。

由于要设置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:
    @Override
    public Collection doSharding(Collection availableTargetNames, ComplexKeysShardingValue shardingValue) {
        Map> columnNameAndShardingValuesMap = shardingValue.getColumnNameAndShardingValuesMap();
        //System.out.println("所有的sharding列的值为" + columnNameAndShardingValuesMap);
        Set 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;
    }
}

实现接口：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 {

    
    @Override
    public Collection doSharding(Collection availableTargetNames, ComplexKeysShardingValue shardingValue) {
        Map columnNameAndShardingValuesMap = shardingValue.getColumnNameAndShardingValuesMap();
        //System.out.println("所有的sharding列的值为" + columnNameAndShardingValuesMap);
        Set 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;
    }
}

sharding-jdbc版本为4.1.1

			org.apache.shardingsphere
			sharding-jdbc-spring-boot-starter
			4.1.1
		

		
			org.springframework.boot
			spring-boot-starter
		

		
			mysql
			mysql-connector-java
			runtime
		

		
			com.baomidou
			mybatis-plus-boot-starter
			3.1.0
		

		
			org.springframework.boot
			spring-boot-starter-test
			test
		

		
			org.projectlombok
			lombok
		

		
		
			org.springframework.boot
			spring-boot-starter-web
		

		
			com.github.pagehelper
			pagehelper-spring-boot-starter
			1.2.3
			
				
					org.mybatis
					mybatis
				
				
					org.mybatis
					mybatis-spring
				
			
		

select * from t_user

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

1、ShardingSphere-JDBC框架原因

2、Oracle、MySQL数据库原因

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

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

取出逻辑表成绩排名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作为参数，查到对应的详情列表

select * from t_user where id = ‘950084434298667033’

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

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

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

        return result;
    }

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}

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

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

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