MySQL中的分库分表框架-ShardingSphere

y_bccl27

1442人浏览 · 2022-11-07 21:49:40

y_bccl27 · 2022-11-07 21:49:40 发布

一、前言

ShardingSphere，大家多少都有听过吧，Apache顶级项目，国内大佬的巨作，Java中用的最多的一个分库分表框架，如果你们的系统中需要分库分表，强烈建议使用，完全可以满足你的所有需求。

本文并不会介绍什么是分库分表，而是通过大量案例，让你了解ShardingSphere可以做什么？如何做？以及SpringBoot中如何使用它等等。

ShardingSphere的Git官网地址：

https://github.com/apache/shardingsphere

ShardingSphere目前最新版本5.X了，大版本之间变化比较大，本次以4.1.1为例来介绍。

如果对分库分表没有概念，可以先去下面这个地址看看，然后再继续向下看。

https://shardingsphere.apache.org/document/legacy/4.x/document/cn/overview/

原文地址：

http://itsoku.com/course/25/410

二、纯Java API代码案例

1）引入shardingsphere的maven配置

<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-core</artifactId>
    <version>4.1.1</version>
</dependency>

2）完整的maven配置如下

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.7.1</version>
		<relativePath/> <!-- lookup parent from repository -->
	</parent>
	<groupId>com.bc</groupId>
	<artifactId>demo</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<name>demo</name>
	<description>Demo project for Spring Boot</description>
	<properties>
		<java.version>1.8</java.version>
	</properties>
	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter</artifactId>
		</dependency>

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

		<dependency>
			<groupId>org.mybatis.spring.boot</groupId>
			<artifactId>mybatis-spring-boot-starter</artifactId>
			<version>2.2.2</version>
		</dependency>

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

		<dependency>
			<groupId>org.projectlombok</groupId>
			<artifactId>lombok</artifactId>
			<optional>true</optional>
		</dependency>

		<dependency>
			<groupId>org.apache.shardingsphere</groupId>
			<artifactId>sharding-jdbc-core</artifactId>
			<version>4.1.1</version>
		</dependency>
	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
		</plugins>
	</build>

</project>

其完整的项目结构如下所示：

2.1、案例1：单库多表

需求：一个库中有2个订单表，按照订单id取模，将数据路由到指定的表。

在db1数据库中创建表t_order_0和t_order_1：

drop database if exists db1;
create database db1;
use db1;

drop table if exists t_order_0;
create table t_order_0(
    order_id bigint not null primary key,
    user_id bigint not null,
    price bigint not null
);

drop table if exists t_order_1;
create table t_order_1(
    order_id bigint not null primary key,
    user_id bigint not null,
    price bigint not null
);

Java代码：

package com.bc.demo.apidemo;

import com.zaxxer.hikari.HikariDataSource;
import org.apache.shardingsphere.api.config.sharding.ShardingRuleConfiguration;
import org.apache.shardingsphere.api.config.sharding.TableRuleConfiguration;
import org.apache.shardingsphere.api.config.sharding.strategy.InlineShardingStrategyConfiguration;
import org.apache.shardingsphere.shardingjdbc.api.ShardingDataSourceFactory;
import org.apache.shardingsphere.underlying.common.config.properties.ConfigurationPropertyKey;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;

public class Demo1 {

    private static DataSource dataSource() {
        HikariDataSource dataSource = new HikariDataSource();
        dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
        dataSource.setJdbcUrl("jdbc:mysql://127.0.0.1:3306/db1?characterEncoding=UTF-8");
        dataSource.setUsername("admin");
        dataSource.setPassword("123456");
        return dataSource;
    }

    public static void main(String[] args) throws SQLException {
        // 1.配置真实数据源
        Map<String, DataSource> dataSourceMap = new HashMap<>();
        dataSourceMap.put("ds0", dataSource());

        // 2.配置表的规则
        TableRuleConfiguration orderTableRuleConfig = new TableRuleConfiguration("t_order", "ds0.t_order_$->{0..1}");
        // 指定表的分片策略（分片字段+分片算法）
        orderTableRuleConfig.setTableShardingStrategyConfig(new InlineShardingStrategyConfiguration("order_id", "t_order_$->{order_id % 2}"));

        // 3.分片规则
        ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
        //将表的分片规则加入到分片规则列表
        shardingRuleConfig.getTableRuleConfigs().add(orderTableRuleConfig);

        // 4.配置一些属性，例如输出sql
        Properties props = new Properties();
        props.put(ConfigurationPropertyKey.SQL_SHOW.getKey(), true);

        // 5.创建数据源
        DataSource dataSource = ShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfig, props);

        // 6.获取连接，执行sql
        Connection connection = dataSource.getConnection();
        connection.setAutoCommit(false);

        // 7.测试向t_order表插入4条数据，4条数据会分散到2个表
        PreparedStatement ps = connection.prepareStatement("insert into t_order (order_id,user_id,price) values (?,?,?)");
        for (long i = 1; i <= 4; i++) {
            int j = 1;
            ps.setLong(j++, i);
            ps.setLong(j++, i);
            ps.setLong(j, 100 * i);
            System.out.println(ps.executeUpdate());
        }

        connection.commit();
        ps.close();
        connection.close();
    }
}

执行上述案例的mian方法，控制台其输出结果如下所示：

查看表t_order_0中的数据：

查看表t_order_1中的数据：

2.2、案例2：多库多表

需求：当前有两个库db1和db2，2个库中都包含了表t_order_0和t_order_1，根据 user_id%2 路由库，根据 order_id%2路由表。

在db1数据库中创建表t_order_0和t_order_1：

drop database if exists db1;
create database db1;
use db1;

drop table if exists t_order_0;
create table t_order_0(
    order_id bigint not null primary key,
    user_id bigint not null,
    price bigint not null
);

drop table if exists t_order_1;
create table t_order_1(
    order_id bigint not null primary key,
    user_id bigint not null,
    price bigint not null
);

在db2数据库中创建表t_order_0和t_order_1：

drop database if exists db2;
create database db2;
use db2;

drop table if exists t_order_0;
create table t_order_0(
    order_id bigint not null primary key,
    user_id bigint not null,
    price bigint not null
);

drop table if exists t_order_1;
create table t_order_1(
    order_id bigint not null primary key,
    user_id bigint not null,
    price bigint not null
);

Java代码：

package com.bc.demo.apidemo;

import com.zaxxer.hikari.HikariDataSource;
import org.apache.shardingsphere.api.config.sharding.ShardingRuleConfiguration;
import org.apache.shardingsphere.api.config.sharding.TableRuleConfiguration;
import org.apache.shardingsphere.api.config.sharding.strategy.InlineShardingStrategyConfiguration;
import org.apache.shardingsphere.shardingjdbc.api.ShardingDataSourceFactory;
import org.apache.shardingsphere.underlying.common.config.properties.ConfigurationPropertyKey;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;

public class Demo2 {

    private static DataSource dataSource1() {
        HikariDataSource dataSource = new HikariDataSource();
        dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
        dataSource.setJdbcUrl("jdbc:mysql://127.0.0.1:3306/db1?characterEncoding=UTF-8");
        dataSource.setUsername("admin");
        dataSource.setPassword("123456");
        return dataSource;
    }

    private static DataSource dataSource2() {
        HikariDataSource dataSource = new HikariDataSource();
        dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
        dataSource.setJdbcUrl("jdbc:mysql://127.0.0.1:3306/db2?characterEncoding=UTF-8");
        dataSource.setUsername("admin");
        dataSource.setPassword("123456");
        return dataSource;
    }

    public static void main(String[] args) throws SQLException {
        // 1.配置真实数据源
        Map<String, DataSource> dataSourceMap = new HashMap<>();
        dataSourceMap.put("ds0", dataSource1());
        dataSourceMap.put("ds1", dataSource2());

        // 2.配置表的规则
        TableRuleConfiguration orderTableRuleConfig = new TableRuleConfiguration("t_order", "ds$->{0..1}.t_order_$->{0..1}");
        // 指定db的分片策略（分片字段+分片算法）
        orderTableRuleConfig.setDatabaseShardingStrategyConfig(new InlineShardingStrategyConfiguration("user_id", "ds$->{user_id % 2}"));
        // 指定表的分片策略（分片字段+分片算法）
        orderTableRuleConfig.setTableShardingStrategyConfig(new InlineShardingStrategyConfiguration("order_id", "t_order_$->{order_id % 2}"));

        // 3.分片规则
        ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
        //将表的分片规则加入到分片规则列表
        shardingRuleConfig.getTableRuleConfigs().add(orderTableRuleConfig);

        // 4.配置一些属性，例如输出sql
        Properties props = new Properties();
        props.put(ConfigurationPropertyKey.SQL_SHOW.getKey(), true);

        // 5.创建数据源
        DataSource dataSource = ShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfig, props);

        // 6.获取连接，执行sql
        Connection connection = dataSource.getConnection();
        connection.setAutoCommit(false);
        PreparedStatement ps = connection.prepareStatement("insert into t_order (order_id,user_id,price) values (?,?,?)");
        // 插入4条数据测试,每个表会落入1条数据
        for (long user_id = 1; user_id <= 2; user_id++) {
            for (long order_id = 1; order_id <= 2; order_id++) {
                int j = 1;
                ps.setLong(j++, order_id);
                ps.setLong(j++, user_id);
                ps.setLong(j, 100);
                System.out.println(ps.executeUpdate());
            }
        }

        connection.commit();
        ps.close();
        connection.close();
    }
}

3行关键代码，配置t_order分片规则，包含db和table的分片策略：

执行上述案例的mian方法，控制台其输出结果如下所示：

查看db1库中各个表的数据：

查看db2库中各个表的数据：

2.3、案例3：单库无分表规则

若表未指定分片规则，则直接路由到对应的表。

在db1数据库中创建表t_user：

DROP DATABASE IF EXISTS db1;
CREATE DATABASE db1;
USE db1;

DROP TABLE IF EXISTS t_user;
CREATE TABLE t_user(
 id BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT,
 NAME VARCHAR(128) NOT NULL
);

Java代码：

package com.bc.demo.apidemo;

import com.zaxxer.hikari.HikariDataSource;
import org.apache.shardingsphere.api.config.sharding.ShardingRuleConfiguration;
import org.apache.shardingsphere.shardingjdbc.api.ShardingDataSourceFactory;
import org.apache.shardingsphere.underlying.common.config.properties.ConfigurationPropertyKey;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;

public class Demo3 {

    private static DataSource dataSource() {
        HikariDataSource dataSource = new HikariDataSource();
        dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
        dataSource.setJdbcUrl("jdbc:mysql://127.0.0.1:3306/db1?characterEncoding=UTF-8");
        dataSource.setUsername("admin");
        dataSource.setPassword("123456");
        return dataSource;
    }

    public static void main(String[] args) throws SQLException {

        // 1.配置真实数据源
        Map<String, DataSource> dataSourceMap = new HashMap<>();
        dataSourceMap.put("ds0", dataSource());

        // 2、无配置表的规则

        // 3、分片规则
        ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();

        // 4.配置一些属性，例如输出sql
        Properties props = new Properties();
        props.put(ConfigurationPropertyKey.SQL_SHOW.getKey(), true);

        // 5.创建数据源
        DataSource dataSource = ShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfig, props);

        // 6.获取连接，执行sql
        Connection connection = dataSource.getConnection();
        connection.setAutoCommit(false);
        PreparedStatement ps = connection.prepareStatement("insert into t_user (name) values (?)");
        ps.setString(1, "张三");
        System.out.println(ps.executeUpdate());

        connection.commit();
        ps.close();
        connection.close();
    }
}

执行上述案例的mian方法，控制台其输出结果如下所示：

2.4、案例4：多库无分表规则

需求：现有2个库db1和db2，两个库中都包含t_user表，t_user表不指定路由规则的情况下，向t_user表写入数据会落入哪个库呢？

在db1数据库中创建表t_user：

DROP DATABASE IF EXISTS db1;
CREATE DATABASE db1;
USE db1;

DROP TABLE IF EXISTS t_user;
CREATE TABLE t_user(
 id BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT,
 NAME VARCHAR(128) NOT NULL
);

在db2数据库中创建表t_user：

DROP DATABASE IF EXISTS db2;
CREATE DATABASE db2;
USE db2;

DROP TABLE IF EXISTS t_user;
CREATE TABLE t_user(
 id BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT,
 NAME VARCHAR(128) NOT NULL
);

Java代码：

package com.bc.demo.apidemo;

import com.zaxxer.hikari.HikariDataSource;
import org.apache.shardingsphere.api.config.sharding.ShardingRuleConfiguration;
import org.apache.shardingsphere.shardingjdbc.api.ShardingDataSourceFactory;
import org.apache.shardingsphere.underlying.common.config.properties.ConfigurationPropertyKey;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.LinkedHashMap;
import java.util.Map;
import java.util.Properties;

public class Demo4 {

    private static DataSource dataSource1() {
        HikariDataSource dataSource = new HikariDataSource();
        dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
        dataSource.setJdbcUrl("jdbc:mysql://172.81.243.6:3306/db1?characterEncoding=UTF-8");
        dataSource.setUsername("admin");
        dataSource.setPassword("Abc_123456");
        return dataSource;
    }

    private static DataSource dataSource2() {
        HikariDataSource dataSource = new HikariDataSource();
        dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
        dataSource.setJdbcUrl("jdbc:mysql://127.0.0.1:3306/db2?characterEncoding=UTF-8");
        dataSource.setUsername("admin");
        dataSource.setPassword("123456");
        return dataSource;
    }

    public static void main(String[] args) throws SQLException {
        // 1.配置真实数据源
        Map<String, DataSource> dataSourceMap = new LinkedHashMap<>();
        dataSourceMap.put("ds0", dataSource1());
        dataSourceMap.put("ds1", dataSource2());

        // 2、无配置表的规则

        // 3、分片规则
        ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();

        // 4.配置一些属性，例如输出sql
        Properties props = new Properties();
        props.put(ConfigurationPropertyKey.SQL_SHOW.getKey(), true);

        // 5、创建数据源
        DataSource dataSource = ShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfig, props);

        // 6.获取连接，执行sql
        Connection connection = dataSource.getConnection();
        connection.setAutoCommit(false);
        //插入4条数据，测试效果
        for (int i = 0; i < 4; i++) {
            PreparedStatement ps = connection.prepareStatement("insert into t_user (name) values (?)");
            ps.setString(1, "张三");
            System.out.println(ps.executeUpdate());
        }

        connection.commit();
        connection.close();
    }
}

执行上述案例的mian方法，控制台其输出结果如下所示，落入的库是不确定的：

三、分片问题

上面介绍的案例，db的路由、表的路由都是采用取模的方式，这种方式存在一个问题：

当查询条件是>、<、>=、<=、BETWEEN AND的时候，就无能为力了。

此时要用其它的分片策略来解决，下面来看看如何解决。

四、分片介绍

4.1、分片键

用于分片的数据库字段，是将数据库(表)水平拆分的关键字段。例：将订单表中的订单主键的尾数取模分片，则订单主键为分片字段。SQL中如果无分片字段，将执行全路由，性能较差。除了对单分片字段的支持，ShardingSphere也支持根据多个字段进行分片。

4.2、分片算法

通过分片算法将数据分片，支持通过=、>=、<=、>、<、BETWEEN和IN分片。分片算法需要应用方开发者自行实现，可实现的灵活度非常高。

目前提供4种分片算法，由于分片算法和业务实现紧密相关，因此并未提供内置分片算法，而是通过分片策略将各种场景提炼出来，提供更高层级的抽象，并提供接口让应用开发者自行实现分片算法。

精确分片算法：对应PreciseShardingAlgorithm，用于处理使用单一键作为分片键的=与IN进行分片的场景，需要配合StandardShardingStrategy使用。

范围分片算法：对应RangeShardingAlgorithm，用于处理使用单一键作为分片键的BETWEEN AND、>、<、>=、<=进行分片的场景。需要配合StandardShardingStrategy使用。

复合分片算法：对应ComplexKeysShardingAlgorithm，用于处理使用多键作为分片键进行分片的场景，包含多个分片键的逻辑较复杂，需要应用开发者自行处理其中的复杂度，需要配合ComplexShardingStrategy使用。

Hint分片算法：对应HintShardingAlgorithm，用于处理使用Hint行分片的场景，需要配合HintShardingStrategy使用。

4.3、5种分片策略

包含分片键和分片算法，由于分片算法的独立性，将其独立抽离。真正可用于分片操作的是分片键 + 分片算法，也就是分片策略，目前提供5种分片策略。

行表达式分片策略：

对应InlineShardingStrategy。使用Groovy的表达式，提供对SQL语句中的=和IN的分片操作支持，只支持单分片键。对于简单的分片算法，可以通过简单的配置使用，从而避免繁琐的Java代码开发，如：t_user_$->{u_id % 8}表示t_user表根据u_id模8，而分成8张表，表名称为t_user_0到t_user_7。

标准分片策略：

对应StandardShardingStrategy。提供对SQL语句中的=、>、<、>=、<=、IN和BETWEEN AND的分片操作支持。StandardShardingStrategy只支持单分片键，提供PreciseShardingAlgorithm和RangeShardingAlgorithm两个分片算法。PreciseShardingAlgorithm是必选的，用于处理=和IN的分片。RangeShardingAlgorithm是可选的，用于处理BETWEEN AND, >, <, >=, <=分片，如果不配置RangeShardingAlgorithm，SQL中的BETWEEN AND将按照全库路由处理。

复合分片策略：

对应ComplexShardingStrategy。复合分片策略。提供对SQL语句中的=、>、<、>=、<=、IN和BETWEEN AND的分片操作支持。ComplexShardingStrategy支持多分片键，由于多分片键之间的关系复杂，因此并未进行过多的封装，而是直接将分片键值组合以及分片操作符透传至分片算法，完全由应用开发者实现，提供最大的灵活度。

Hint分片策略：

对应HintShardingStrategy。通过Hint指定分片值而非从SQL中提取分片值的方式进行分片的策略。

不分片策略：

对应NoneShardingStrategy。不分片的策略。

4.4、SQL Hint

对于分片字段非SQL决定，而由其他外置条件决定的场景，可使用SQL Hint灵活的注入分片字段。例：内部系统，按照员工登录主键分库，而数据库中并无此字段。SQL Hint支持通过Java API和SQL注释(待实现)两种方式使用。

4.5、白话解释分片策略

当我们使用分库分表的时候，目标库和表都存在多个，此时执行sql，那么sql最后会落到哪个库？那个表呢？

这就是分片策略需要解决的问题，主要解决2个问题：

1、sql应该到哪个库去执行？这个就是数据库路由策略决定的

2、sql应该到哪个表去执行呢？这个就是表的路由策略决定的

所以如果要对某个表进行分库分表，需要指定则两个策略：

1、db路由策略，通过TableRuleConfiguration#setDatabaseShardingStrategyConfig进行设置

2、table路由策略，通过TableRuleConfiguration#setTableShardingStrategyConfig进行设置

天工造物局

北京人形旗下天工造物具身智能开源社区，聚焦具身天工与慧思开物两大平台

更多推荐

deck.gl TripsLayer 出租车轨迹动画示例实战：从示例运行到着色器原理

TripsLayer 是 deck.gl 中用于渲染**随时间流动的轨迹动画**（如出租车行程、物流路径、迁徙路线）的核心图层。本文以仓库中的最小独立示例 [examples/website/trips](https://link.gitcode.com/i/1442af8f442f4cff39ff546e050fceb5) 为骨架，带你完成从安装依赖、启动应用、理解曼哈顿出租车数据格式，到深入

天工造物局

Foundry Anvil 中 eth_simulateV1 的 Blob 交易与 Blob Gas 字段规范化：原理、实现与测试验证

本篇技术指南以 [anvil-simulate-blob-transactions.md](https://link.gitcode.com/i/3d2ebe227262de169335eb290af80da8) 这条 changelog 条目为起点，深入剖析 Foundry Anvil 对 `eth_simulateV1` RPC 返回结果中 blob（EIP-4844）交易与 blob gas

天工造物局

LMCache CLI 实战：用 `lmcache kvcache clear` 管理运行中服务的 KV Cache 状态

本文是一份针对 LMCache 官方 CLI 命令 `lmcache kvcache` 的完整技术指南。该命令用于在**运行中的 LMCache 服务（MP HTTP server）**上管理 KV cache 状态，当前提供 `clear` 子命令，用于清空 L1（CPU 内存）层中缓存的全部 KV 数据。读完本文，你将掌握该命令的完整参数体系、退出码语义、终端/JSON/静默三种输出模式，并能