ShardingSphere基本概念及快速入门

MySQL ShardingSphere 分库分表
笔记
发布日期:   2023-10-20
更新日期:   2023-10-20
文章字数:   3.3k
阅读时长:   15 分
阅读次数:  

Apache ShardingSphere 是一套开源的分布式数据库解决方案组成的生态圈，它由 JDBC、Proxy 和 Sidecar（规划中）这 3 款既能够独立部署，又支持混合部署配合使用的产品组成。 它们均提供标准化的数据水平扩展、分布式事务和分布式治理等功能，可适用于如 Java 同构、异构语言、云原生等各种多样化的应用场景。

ShardingSphere 已于2020年4月16日成为 Apache 软件基金会的顶级项目。

ShardingSphere 主要有3个产品，ShardingJDBC、ShardingProxy和ShardingSidecar

  • ShardingJDBC：客户端分库分表的产品，轻量级 Java 框架，以 jar 包形式提供服务
  • ShardingProxy：服务端分库分表的产品，封装了数据库⼆进制协议的服务端版本，⽤于完成对异构语⾔的⽀持。⽬前提供 MySQL 和 PostgreSQL 版本，它可以使⽤任何兼容 MySQL/PostgreSQL 协议的访问客⼾端
  • ShardingSidecar：规划中

ShardingJDBC和ShardingProxy对比

ShardingJdbc就是一个jar包，在jar包里面提供了分库分表的逻辑（个别逻辑需自己实现），优点是灵活性大，支持数据库较多（只要是通过Jdbc连接的）。缺点是业务侵入大，需要自己实现分库分表逻辑。

ShardingProxy是一个独立的服务，业务无侵入，客户端只需自己连接ShardingProxy就可以进行分库分表，客户端感知不到其实已经分库分表。但这优点也代表着它不够灵活，只有固定的功能，不可以定制开发，支持的数据库也比较少。

Sharding-JDBC Sharding-Proxy
数据库 任意 MySQL/PostgreSQL
连接消耗数 高 低
异构语言 仅java 任意
性能 损耗低 损耗略高
无中心化 是 否
静态入口 无 有

ShardingJdbc基本概念

ShardingJdbc主要职责是读写分离和数据分片，客户端可以自己定制规则通过Jdbc访问多数据源，不用关心数据的具体分布情况。

相关概念

概览 解释
逻辑表 水平拆库的相同数据结构的表的总和
真实表 数据库存在的真实表
数据节点 有数据源和数据表组成
绑定表 分片规则一致的主表和子表
广播表 公共表，所有的分片都存在的表
分片键 用于分片的数据库字段，是将数据库(表)进行水平拆分的关键字段。SQL中若没有分片字段，将会执行全路由，性能会很差
分片算法 通过分片算法将数据进行分片，支持通过=、BETWEEN和IN分片。分片算法需要自行实现，可实现的灵活度非常高
分片策略 分片键+分片算法

ShardingJdbc快速实战

引入依赖,其他springboot,mybatis依赖自行引入

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

SQL代码自行实现，springboot我们这里只要编写配置文件就可以了

## 分库分表配置
# 配置ds0 和ds1两个数据源
spring.shardingsphere.datasource.names=ds0,ds1
# ds0 数据源配置
spring.shardingsphere.datasource.ds0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds0.url=jdbc:mysql://xx.xx.xx.xx:3306/shop_ds_0?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
spring.shardingsphere.datasource.ds0.username=xx
spring.shardingsphere.datasource.ds0.password=xx
# ds1 数据源配置
spring.shardingsphere.datasource.ds1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds1.url=jdbc:mysql://xx.xx.xx.xx:3306/shop_ds_1?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
spring.shardingsphere.datasource.ds1.username=xx
spring.shardingsphere.datasource.ds1.password=xx

# 分库策略 根据id取模确定数据进哪个数据库
spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression=ds$->{user_id % 2}
# 绑定表
spring.shardingsphere.sharding.binding-tables[0]=t_order,t_order_item

# 广播表
spring.shardingsphere.sharding.broadcast-tables=t_dict
spring.shardingsphere.sharding.tables.t_dict.key-generator.column=dict_id
spring.shardingsphere.sharding.tables.t_dict.key-generator.type=SNOWFLAKE

# 具体分表策略
# 节点 ds0.t_order_0,ds0.t_order_1,ds1.t_order_0,ds1.t_order_1
spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=ds$->{0..1}.t_order_$->{0..1}
# 分表字段id
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.sharding-column=order_id
# 分表策略 根据id取模,确定数据最终落在那个表中
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.algorithm-expression = t_order_$->{order_id % 2}
# t_order使用SNOWFLAKE算法生成主键
spring.shardingsphere.sharding.tables.t_order.key-generator.column=order_id
spring.shardingsphere.sharding.tables.t_order.key-generator.type=SNOWFLAKE
spring.shardingsphere.sharding.tables.t_order.key-generator.props.worker.id=1

# 节点 ds0.t_order_item_0,ds0.t_order_item_1,ds1.t_order_item_0,ds1.t_order_item_1
spring.shardingsphere.sharding.tables.t_order_item.actual-data-nodes=ds$->{0..1}.t_order_item_$->{0..1}
# 分表字段id
spring.shardingsphere.sharding.tables.t_order_item.table-strategy.inline.sharding-column=order_id
# 分表策略 根据id取模,确定数据最终落在那个表中
spring.shardingsphere.sharding.tables.t_order_item.table-strategy.inline.algorithm-expression=t_order_item_$->{order_id % 2}
# t_order_item使用SNOWFLAKE算法生成主键
spring.shardingsphere.sharding.tables.t_order_item.key-generator.column=order_item_id
spring.shardingsphere.sharding.tables.t_order_item.key-generator.type=SNOWFLAKE
spring.shardingsphere.sharding.tables.t_order_item.key-generator.props.worker.id=123

## 查看SQL
spring.shardingsphere.props.sql.show = true

这个配置文件的意思就是有4个order表分别是shop_ds_0.t_order_0、shop_ds_0.t_order_1、shop_ds_1.t_order_0、shop_ds_1.t_order_1

分片策略是user_id对2取模结果便是分的库；order_id对2取模结果是分的order表；主键生产策略是雪花算法

t_order_item表和上诉同理。

项目启动直接跑SQL便可分库分表了。

注意查询可能会有笛卡尔积现象，需要手动编写策略实现指定查询的数据节点

ShardingJdbc分片算法

在上面的测试代码用的是inline分片算法即提供一个分片键和一个分片表达式来制定分片算法，针对一些复杂的分片策略，inline就不够使用了。ShardingSphere提供了其他几种分片策略

NoneShardingStrategy

不分片

InlineShardingStrategy

分片表达式分片，范围查询不支持

配置参数：

  1. inline.shardingColumn分片键
  2. inline.algorithmExpression：分片表达式
## =======分片表达式分片======
## 分表字段id
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.sharding-column=order_id
## 分表策略 根据id取模,确定数据最终落在那个表中
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.algorithm-expression = t_order_$->{order_id % 2}

StandardShardingStrategy

支持单分片键的标准分片策略。

配置参数：

  1. standard.sharding-column：分片键
  2. standard.precise-algorithm-class-name：精确分片算法类名，实现类继承PreciseShardingAlgorithm接口
  3. standard.range-algorithm-class-name：范围分片算法类名，实现类继承RangeShardingAlgorithm接口
## =======单分片键分片=======
## 分表字段id
spring.shardingsphere.sharding.tables.t_order.table-strategy.standard.sharding-column=order_id
## 精确分片算法类
spring.shardingsphere.sharding.tables.t_order.table-strategy.standard.precise-algorithm-class-name=com.dm.shardingsphere.algorithem.TableStrategyStandardPreciseAlgorithm
## 范围分片算法类
spring.shardingsphere.sharding.tables.t_order.table-strategy.standard.range-algorithm-class-name=com.dm.shardingsphere.algorithem.TableStrategyStandardRangeAlgorithm

public class TableStrategyStandardPreciseAlgorithm implements PreciseShardingAlgorithm<Long> {

    @Override
    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Long> shardingValue) {
        // 数据节点
        System.out.println("TableStrategyStandardPreciseAlgorithm:=====================================================");
        System.out.println("availableTargetNames：" + String.join(";", availableTargetNames));
        // 逻辑表名 t_order
        String logicTableName = shardingValue.getLogicTableName();
        // 分片键名 order_id
        String columnName = shardingValue.getColumnName();
        // 传进来的 order_id值，根据这个值可以定制分片策略
        Long value = shardingValue.getValue();
        System.out.println("logicTableName：" + logicTableName + ";columnName：" + columnName + ";value：" + value);

        // 确认分片
        long sharding = value % 2;
        String key = logicTableName.concat("_").concat(String.valueOf(sharding));
        if (availableTargetNames.contains(key)){
            return key;
        }

        throw new UnsupportedOperationException("未找到分片");
    }
}

public class TableStrategyStandardRangeAlgorithm implements RangeShardingAlgorithm<Long> {

    @Override
    public Collection<String> doSharding(Collection<String> availableTargetNames, RangeShardingValue<Long> shardingValue) {
        // 数据节点
        System.out.println("TableStrategyStandardRangeAlgorithm:=====================================================");
        System.out.println("availableTargetNames：" + String.join(";", availableTargetNames));
        // 逻辑表名 t_order
        String logicTableName = shardingValue.getLogicTableName();
        // 分片键名 order_id
        String columnName = shardingValue.getColumnName();
        // 传进来的 查询范围，根据范围可以定制分片策略，指定查询表
        Long start = shardingValue.getValueRange().lowerEndpoint();
        Long end = shardingValue.getValueRange().upperEndpoint();

        System.out.println("logicTableName：" + logicTableName + ";columnName：" + columnName + ";start：" + start + ";end：" + end);

        // 确认范围
        // 这里直接返回所有,可以根据start,end定制范围，比如表一0,500表二501-1000这里返回对应表就可以了
        return availableTargetNames;
        // 这里表示范围查询只能查t_order_1表
//        return Collections.singletonList("t_order_1");
    }
}

ComplexShardingStrategy

支持多分片键的复杂分片策略

配置参数：

  1. complex.sharding-columns：分片键(多个)
  2. complex.algorithm-class-name：分片算法实现类，实现类继承ComplexKeysShardingAlgorithm接口
## =======多分片键的复杂分片策略=======
# 分表字段id
spring.shardingsphere.sharding.tables.t_order.table-strategy.complex.sharding-columns=order_id
# 分片算法实现类
spring.shardingsphere.sharding.tables.t_order.table-strategy.complex.algorithm-class-name=com.dm.shardingsphere.algorithem.TableStrategyComplexAlgorithm

public class TableStrategyComplexAlgorithm implements ComplexKeysShardingAlgorithm<Long> {

    @Override
    public Collection<String> doSharding(Collection<String> availableTargetNames, ComplexKeysShardingValue<Long> shardingValue) {
        System.out.println("TableStrategyComplexAlgorithm:=====================================================");
        // 数据节点
        System.out.println("availableTargetNames：" + String.join(";", availableTargetNames));
        // 分片键Map,key-键名如order_id,value-键名对应的值如1231131231414
        Map<String, Collection<Long>> columnNameAndShardingValuesMap = shardingValue.getColumnNameAndShardingValuesMap();
        // 这里返回的是个列表所以in操作也是可以的
        Collection<Long> orderIds = columnNameAndShardingValuesMap.get("order_id");
        Collection<Long> userIds = columnNameAndShardingValuesMap.get("user_id");
//        System.out.println("orderIds：" + orderIds.stream().map(String::valueOf).collect(Collectors.joining(";")) +
//                ";userIds：" + userIds.stream().map(String::valueOf).collect(Collectors.joining(";")));

        // 范围查询,key-键名如order_id,value-Range类型,key对应范围
        Map<String, Range<Long>> columnNameAndRangeValuesMap = shardingValue.getColumnNameAndRangeValuesMap();
        Range<Long> orderIdRange = columnNameAndRangeValuesMap.get("order_id");
        Range<Long> userIdRange = columnNameAndRangeValuesMap.get("user_id");

        // 分片
        List<String> shardList = new ArrayList<>();
        if(CollectionUtils.isEmpty(orderIds)){
            return availableTargetNames;
        }
        for(Long orderId: orderIds){
            long sharding = orderId % 2;

            shardList.add(shardingValue.getLogicTableName().concat("_").concat(String.valueOf(sharding)));
        }
        return shardList;
    }
}

HintShardingStrategy：不需要分片键的强制分片策略

配置参数：

  1. hint.algorithm-class-name：分片算法实现类，实现类继承HintShardingAlgorithm接口
## =======不需要分片键的强制分片策略=======
## 分片算法实现类
spring.shardingsphere.sharding.tables.t_order.table-strategy.hint.algorithm-class-name=com.dm.shardingsphere.algorithem.TableStrategyComplexAlgorithm

public class TableStrategyHintAlgorithm implements HintShardingAlgorithm<Long> {
    @Override
    public Collection<String> doSharding(Collection<String> availableTargetNames, HintShardingValue<Long> shardingValue) {
        System.out.println("TableStrategyHintAlgorithm:=====================================================");
        // 数据节点
        System.out.println("availableTargetNames：" + String.join(";", availableTargetNames));
        String logicTableName = shardingValue.getLogicTableName();
        String columnName = shardingValue.getColumnName();
        Collection<Long> values = shardingValue.getValues();

        if (CollectionUtils.isEmpty(values)){
            return availableTargetNames;
        }
        return values.stream().map(value -> logicTableName.concat("_").concat(String.valueOf(value))).collect(Collectors.toList());
    }
}

public List<TOrder> queryOrderByHint() {
    HintManager hintManager = HintManager.getInstance();
    try {
        hintManager.addTableShardingValue("t_order", (long)1);
        return orderMapper.select(TOrder.builder().userId((long) 1).build());
    } finally {
        hintManager.close();
    }
}

绑定表

SELECT b.address_id,a.* FROM t_order_item a LEFT JOIN t_order b ON a.order_id = b.order_id

类似这种SQL通过分片策略执行结束后

image-20210205172009398

出现了笛卡尔积现象，会消耗大量的性能。

解决这种办法就是让t_order和t_order_item走同样的分片策略，并且绑定在一起

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

t_order_0查t_order_item_0;t_order_1查t_order_item_1

广播表

# 广播表
spring.shardingsphere.sharding.broadcast-tables=t_dict
spring.shardingsphere.sharding.tables.t_dict.key-generator.column=dict_id
spring.shardingsphere.sharding.tables.t_dict.key-generator.type=SNOWFLAKE

ShardingSphere的SQL使用限制

官方SQL限制说明

支持的SQL

SQL 必要条件
SELECT * FROM tbl_name
SELECT * FROM tbl_name WHERE (col1 = ? or col2 = ?) and col3 = ?
SELECT * FROM tbl_name WHERE col1 = ? ORDER BY col2 DESC LIMIT ?
SELECT COUNT(*), SUM(col1), MIN(col1), MAX(col1), AVG(col1) FROM tbl_name WHERE col1 = ?
SELECT COUNT(col1) FROM tbl_name WHERE col2 = ? GROUP BY col1 ORDER BY col3 DESC LIMIT ?, ?
INSERT INTO tbl_name (col1, col2,…) VALUES (?, ?, ….)
INSERT INTO tbl_name VALUES (?, ?,….)
INSERT INTO tbl_name (col1, col2, …) VALUES (?, ?, ….), (?, ?, ….)
INSERT INTO tbl_name (col1, col2, …) SELECT col1, col2, … FROM tbl_name WHERE col3 = ? INSERT表和SELECT表必须为相同表或绑定表
REPLACE INTO tbl_name (col1, col2, …) SELECT col1, col2, … FROM tbl_name WHERE col3 = ? REPLACE表和SELECT表必须为相同表或绑定表
UPDATE tbl_name SET col1 = ? WHERE col2 = ?
DELETE FROM tbl_name WHERE col1 = ?
CREATE TABLE tbl_name (col1 int, …)
ALTER TABLE tbl_name ADD col1 varchar(10)
DROP TABLE tbl_name
TRUNCATE TABLE tbl_name
CREATE INDEX idx_name ON tbl_name
DROP INDEX idx_name ON tbl_name
DROP INDEX idx_name
SELECT DISTINCT * FROM tbl_name WHERE col1 = ?
SELECT COUNT(DISTINCT col1) FROM tbl_name
SELECT subquery_alias.col1 FROM (select tbl_name.col1 from tbl_name where tbl_name.col2=?) subquery_alias

不支持的SQL

SQL 不支持原因
INSERT INTO tbl_name (col1, col2, …) VALUES(1+2, ?, …) VALUES语句不支持运算表达式
INSERT INTO tbl_name (col1, col2, …) SELECT * FROM tbl_name WHERE col3 = ? SELECT子句暂不支持使用*号简写及内置的分布式主键生成器
REPLACE INTO tbl_name (col1, col2, …) SELECT * FROM tbl_name WHERE col3 = ? SELECT子句暂不支持使用*号简写及内置的分布式主键生成器
SELECT * FROM tbl_name1 UNION SELECT * FROM tbl_name2 UNION
SELECT * FROM tbl_name1 UNION ALL SELECT * FROM tbl_name2 UNION ALL
SELECT SUM(DISTINCT col1), SUM(col1) FROM tbl_name 详见DISTINCT支持情况详细说明
SELECT * FROM tbl_name WHERE to_date(create_time, ‘yyyy-mm-dd’) = ? 会导致全路由
(SELECT * FROM tbl_name) 暂不支持加括号的查询
SELECT MAX(tbl_name.col1) FROM tbl_name 查询列是函数表达式时,查询列前不能使用表名;若查询表存在别名,则可使用表的别名

DISTINCT支持情况详细说明

支持的SQL

SQL
SELECT DISTINCT * FROM tbl_name WHERE col1 = ?
SELECT DISTINCT col1 FROM tbl_name
SELECT DISTINCT col1, col2, col3 FROM tbl_name
SELECT DISTINCT col1 FROM tbl_name ORDER BY col1
SELECT DISTINCT col1 FROM tbl_name ORDER BY col2
SELECT DISTINCT(col1) FROM tbl_name
SELECT AVG(DISTINCT col1) FROM tbl_name
SELECT SUM(DISTINCT col1) FROM tbl_name
SELECT COUNT(DISTINCT col1) FROM tbl_name
SELECT COUNT(DISTINCT col1) FROM tbl_name GROUP BY col1
SELECT COUNT(DISTINCT col1 + col2) FROM tbl_name
SELECT COUNT(DISTINCT col1), SUM(DISTINCT col1) FROM tbl_name
SELECT COUNT(DISTINCT col1), col1 FROM tbl_name GROUP BY col1
SELECT col1, COUNT(DISTINCT col1) FROM tbl_name GROUP BY col1

不支持的SQL

SQL 不支持原因
SELECT SUM(DISTINCT tbl_name.col1), SUM(tbl_name.col1) FROM tbl_name 查询列是函数表达式时,查询列前不能使用表名;若查询表存在别名,则可使用表的别名

分库分表建议

从上面SQL限制也可以看出，分库分表带来的必然是SQL使用上很难，并且系统复杂度增加了。所以尽量不要分库分表。

其实分库分表主要解决问题是数据库单机容量问题和查询性能问题。其实对于性能我们可以用很多方式解决，比如读写分离，利用缓存。对于单机容量我们可以使用分布式存储的数据库Hbase,Hive,ES等。

如果确定分库分表需要在项目初期就要开始，不然后期数据维护很难。

文章作者: dm
文章链接: https://dmsupine.com/2023/10/20/shardingsphere-ji-ben-gai-nian-ji-kuai-su-ru-men/
版权声明: 本博客所有文章除特別声明外，均采用 CC BY 4.0 许可协议。转载请注明来源 dm !
MySQL ShardingSphere 分库分表
赏
评论
 上一篇
ShardingSphere数据分片内核原理及源码流程 ShardingSphere数据分片内核原理及源码流程
ShardingSphere数据分片这一部分内核主要是解析引擎、路由引擎、改写引擎、执行引擎、归并引擎五部分。这里主要是对这五部分进行剖析。 在数据分片这块Sharding-JDBC和Sharding-Proxy内核原理这一块是一致的。 官
2023-11-13 笔记
MySQL ShardingSphere 分库分表
下一篇 
ShardingProxy安装及使用 ShardingProxy安装及使用
ShardingProxy搭建ShardingProxy下载这里使用的是ShardingProxy4.1.0 ShardingProxy下载有多种方式，可以选择官网也可以选择镜像 官网地址：https://shardingsphere.a
2023-10-05 安装手册
安装 MySQL ShardingSphere 分库分表
  目录