MySQL 之分库分表技术ShardingJDBC

最新推荐文章于 2023-11-02 23:07:53 发布

yyangqqian

最新推荐文章于 2023-11-02 23:07:53 发布

阅读量388

点赞数

分类专栏： MySQL

本文链接：https://blog.csdn.net/const_/article/details/104256604

版权

MySQL 专栏收录该内容

15 篇文章 0 订阅

订阅专栏

ShardingJDBC简介

Sharding-JDBC是当当网研发的开源分布式数据库中间件，从 3.0 开始Sharding-JDBC被包含在 Sharding-Sphere 中，之后该项目进入Apache孵化器，4.0版本之后的版本为Apache版本。
ShardingSphere官方文档地址
在这里插入图片描述

分库分表概念

- 垂直分表
将一个表按照字段分成多表，每个表存储其中一部分字段，热门字段、冷门字段分开放在不同表中。
- 垂直分库
通过将表按业务分类，然后分布在不同数据库，并且可以将这些数据库部署在不同服务器上，从而达到多个服务器共同分摊压力的效果，但是依然没有解决单表数据量过大的问题。
- 水平分表
是在同一个数据库内，把同一个表的数据按一定规则拆到多个表中，解决了单一表数据量过大的问题，分出来的小表中只包含一部分数据，从而使得单个表的数据量变小，提高检索性能。
- 水平分库
把同一个表的数据按一定规则拆到不同的数据库中，每个库可以放在不同的服务器上。

ShardingJDBC概念

- 逻辑表
水平拆分的数据表的总称。例：订单数据表根据主键尾数拆分为10张表，分别是 t_order_0 、 t_order_1 到 t_order_9 ，他们的逻辑表名为 t_order 。
- 真实表
在分片的数据库中真实存在的物理表。即上个示例中的 t_order_0 到 t_order_9 。
- 数据节点
数据分片的最小物理单元。由数据源名称和数据表组成，例： ds_0.t_order_0 。
- 绑定表
指分片规则一致的主表和子表。例如： t_order 表和 t_order_item 表，均按照 order_id 分片,绑定表之间的分区键完全相同，则此两张表互为绑定表关系。绑定表之间的多表关联查询不会出现笛卡尔积关联，关联查询效率将大大提升。举例说明，如果SQL为：SELECT i.* FROM t_order o JOIN t_order_item i ON o.order_id=i.order_id WHERE o.order_id in (10, 11);
在不配置绑定表关系时，假设分片键为 order_id ，那么路由后的SQL 应该为4条，它们呈现为笛卡尔积：
在这里插入图片描述
在配置绑定表关系后，路由的SQL应该为2条：

- 广播表
指所有的分片数据源中都存在的表，表结构和表中的数据在每个数据库中均完全一致。适用于数据量不大且需要海量数据的表进行关联查询的场景，例如：字典表。
- 分片键
用于分片的数据库字段，是将数据库(表)水平拆分的关键字段。例：将订单表中的订单主键的尾数取模分片，则订单主键为分片字段。 SQL中如果无分片字段，将执行全路由，性能较差。除了对单分片字段的支持，Sharding- Jdbc也支持根据多个字段进行分片。
- 分片算法
通过分片算法将数据分片，支持通过 = 、 BETWEEN 和 IN 分片。分片算法需要应用方开发者自行实现，可实现的灵活度非常高。包括：精确分片算法、范围分片算法，复合分片算法等。例如：where order_id = ? 将采用精确分片算法，where order_id in (?,?,?)将采用精确分片算法，where order_id BETWEEN ? and ? 将采用范围分片算法，复合分片算法用于分片键有多个复杂情况。
- 分片策略
包含分片键和分片算法，由于分片算法的独立性，将其独立抽离。真正可用于分片操作的是分片键 + 分片算法，也就是分片策略。内置的分片策略大致可分为尾数取模、哈希、范围、标签、时间等。由用户方配置的分片策略则更加灵活，常用的使用行表达式配置分片策略，它采用Groovy表达式表示，如: t_user_$->{u_id % 8} 表示t_user 表根据u_id模8，而分成8张表，表名称为 t_user_0 到 t_user_7 。
- 自增主键生成策略
通过在客户端生成自增主键替换以数据库原生自增主键的方式，做到分布式主键无重复。

ShardingJDBC分表快速入门

需求说明

使用ShardingJDBC完成对订单表的水平分表。在order_db数据库中创建两张表，t_order_1和t_order_2，这两张表是订单表拆分后的表，通过ShardingJdbc向订单表插入数据，按照一定的分片规则，主键为偶数的进入t_order_1，另一部分数据进入t_order_2，通过ShardingJdbc 查询数据，根据 SQL语句的内容从t_order_1或t_order_2查询数据。

环境说明

数据库：MySQL5.7
JDK：JDK8
应用框架：SpringBoot 2.1.5，Mybatis3.5.0
ShardingJDBC：sharding-jdbc-spring-boot-starter-4.0.0

创建数据库和表

创建订单库order_db：

CREATE DATABASE `order_db` CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';

在order_db中创建t_order_1、t_order_2表：

DROP TABLE IF EXISTS `t_order_1`;
CREATE TABLE `t_order_1` (
  `order_id` bigint(20) NOT NULL COMMENT '订单id',
  `price` decimal(10,2) NOT NULL COMMENT '订单价格',
  `user_id` bigint(20) NOT NULL COMMENT '下单用户id',
  `status` varchar(50) NOT NULL COMMENT '订单状态',
  PRIMARY KEY (`order_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

DROP TABLE IF EXISTS `t_order_2`;
CREATE TABLE `t_order_2` (
  `order_id` bigint(20) NOT NULL COMMENT '订单id',
  `price` decimal(10,2) NOT NULL COMMENT '订单价格',
  `user_id` bigint(20) NOT NULL COMMENT '下单用户id',
  `status` varchar(50) NOT NULL COMMENT '订单状态',
  PRIMARY KEY (`order_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

引入maven依赖

<parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>2.1.5.RELEASE</version>        
</parent>

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-test</artifactId>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.46</version>
    <scope>runtime</scope>
</dependency>
<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>2.0.0</version>
</dependency>
<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
    <version>4.0.0</version>
</dependency>
<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-namespace</artifactId>
    <version>4.0.0</version>
</dependency>

分片规则配置

分片规则配置是ShardingJDBC进行对分库分表操作的重要依据，配置内容包括：数据源、主键生成策略、分片策略等。
在application.properties中配置：

# 打印sql
spring.shardingsphere.props.sql.show=true
spring.main.allow-bean-definition-overriding=true

# 定义数据源
spring.shardingsphere.datasource.names=m1

spring.shardingsphere.datasource.m1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.m1.jdbc-url=jdbc:mysql://localhost:3306/order_db?characterEncoding=utf-8&serverTimezone=UTC&useSSL=false
spring.shardingsphere.datasource.m1.username=root
spring.shardingsphere.datasource.m1.password=123456

# 逻辑表名：t_order
# 指定t_order表的数据分布情况，配置数据节点
spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=m1.t_order_$->{1..2}

# 指定t_order表的主键字段和生成策略
spring.shardingsphere.sharding.tables.t_order.key-generator.column=order_id
spring.shardingsphere.sharding.tables.t_order.key-generator.type=SNOWFLAKE

# 指定t_order表的分片策略，分片策略包括分片键和分片算法
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.sharding-column=order_id
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.algorithm-expression=t_order_$->{order_id % 2 + 1}

首先定义数据源m1，并对m1进行实际的参数配置
指定t_order表的数据分布情况，他分布在m1.t_order_1，m1.t_order_2
指定t_order表的主键生成策略为SNOWFLAKE，SNOWFLAKE是一种分布式自增算法，保证id全局唯一
定义t_order分片策略，order_id为偶数的数据落在t_order_1，为奇数的落在t_order_2，分表策略的表达式为 t_order_$->{order_id % 2 + 1}

编码

数据操作

import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Component;
import java.math.BigDecimal;
import java.util.List;
import java.util.Map;

@Mapper
@Component
public interface OrderDao {
    @Insert("insert into t_order(price,user_id,status)values(#{price},#{userId},#{status})")
    int insertOrder(@Param("price") BigDecimal price, @Param("userId") Long userId, @Param("status") String status);

    @Select("<script>" +
            "select" +
            " * " +
            " from t_order t " +
            " where t.order_id in " +
            " <foreach collection='orderIds' open='(' separator=',' close=')' item='id'>" +
            " #{id} " +
            " </foreach>" +
            "</script>")
    List<Map> selectOrderbyIds(@Param("orderIds") List<Long> orderIds);
}

单元测试

@RunWith(SpringRunner.class)
@SpringBootTest(classes = {ShardingJdbcDemoApplication.class})
public class ShardingJdbcTest {
    @Resource
    OrderDao orderDao;
    @Test
    public void testInsertOrder(){
        for(int i=1;i<20;i++){
            orderDao.insertOrder(new BigDecimal(i),1L,"SUCCESS");
        }
    }
    @Test
    public void testSelectOrderbyIds(){
        List<Long> ids = new ArrayList<>();
        ids.add(436669720939200512L);
        ids.add(436669722465927169L);
        List<Map> maps = orderDao.selectOrderbyIds(ids);
        System.out.println(maps);
    }
}

在这里插入图片描述

流程分析

ShardingJDBC在拿到用户要执行的sql之后流程分析：

解析sql，获取片键值，在本例中是order_id
Sharding-JDBC通过规则配置 t_order_$->{order_id % 2 + 1}，知道了当order_id为偶数时，应该往 t_order_1表插数据，为奇数时，往t_order_2插数据
于是Sharding-JDBC根据order_id的值改写sql语句，改写后的SQL语句是真实所要执行的SQL语句
执行改写后的真实sql语句
将所有真正执行sql的结果进行汇总合并，返回

ShardingJDBC分库分表快速入门

环境说明

数据库：MySQL5.7
JDK：JDK8
应用框架：SpringBoot 2.1.5，Mybatis-Plus
ShardingJDBC：sharding-jdbc-spring-boot-starter-4.0.0-RC1

导入依赖

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.46</version>
    <scope>runtime</scope>
</dependency>
<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.1.1</version>
</dependency>
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <version>1.18.8</version>
</dependency>
<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
    <version>4.0.0-RC1</version>
</dependency>
<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-namespace</artifactId>
    <version>4.0.0-RC1</version>
</dependency>

创建数据库和表

ds0
  ├── user_0
  └── user_1
ds1
  ├── user_0
  └── user_1

SQL脚本：
ds0

CREATE DATABASE IF NOT EXISTS `ds0`;
USE `ds0`;
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for user_0
-- ----------------------------
DROP TABLE IF EXISTS `user_0`;
CREATE TABLE `user_0` (
  `id` bigint(50) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Table structure for user_1
-- ----------------------------
DROP TABLE IF EXISTS `user_1`;
CREATE TABLE `user_1` (
  `id` bigint(50) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ds1

CREATE DATABASE IF NOT EXISTS `ds1`;
USE `ds1`;
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for user_0
-- ----------------------------
DROP TABLE IF EXISTS `user_0`;
CREATE TABLE `user_0` (
  `id` bigint(50) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Table structure for user_1
-- ----------------------------
DROP TABLE IF EXISTS `user_1`;
CREATE TABLE `user_1` (
  `id` bigint(50) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

分片规则配置

分库分表的分片规则配置，配置内容包括：数据源、主键生成策略、分片策略等。

# 数据源 ds0,ds1
spring.shardingsphere.datasource.names=ds0,ds1

# 第一个数据库
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://localhost:3306/ds0?characterEncoding=utf-8&serverTimezone=UTC&useSSL=false
spring.shardingsphere.datasource.ds0.username=root
spring.shardingsphere.datasource.ds0.password=123456

# 第二个数据库
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://localhost:3306/ds1?characterEncoding=utf-8&serverTimezone=UTC&useSSL=false
spring.shardingsphere.datasource.ds1.username=root
spring.shardingsphere.datasource.ds1.password=123456

# 水平拆分的数据库（表） 配置分库 + 分表策略 行表达式分片策略
# 分库策略列名称
spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=id
# 分库策略算法表达式
spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression=ds$->{id % 2}

# 分库分表节点，其中user为逻辑表
spring.shardingsphere.sharding.tables.user.actual-data-nodes=ds$->{0..1}.user_$->{0..1}
# 分表策略列名称
spring.shardingsphere.sharding.tables.user.table-strategy.inline.sharding-column=id
# 分表策略算法表达式
spring.shardingsphere.sharding.tables.user.table-strategy.inline.algorithm-expression=user_$->{id % 2}

# 主键生成策略
spring.shardingsphere.sharding.tables.user.key-generator.column=id
spring.shardingsphere.sharding.tables.user.key-generator.type=SNOWFLAKE

# 打印执行的数据库以及语句
spring.shardingsphere.props.sql.show=true
spring.main.allow-bean-definition-overriding=true

配置说明：

逻辑表 user
水平拆分的数据库（表）的相同逻辑和数据结构表的总称。例：用户数据根据主键尾数拆分为2张表，分别是user_0到user_1，他们的逻辑表名为user_。
真实表
在分片的数据库中真实存在的物理表。即上个示例中的user_0到user_1。
分片算法:
Hint分片算法
对应HintShardingAlgorithm，用于处理使用Hint行分片的场景。需要配合HintShardingStrategy使用。
分片策略:
行表达式分片策略
对应InlineShardingStrategy。使用Groovy的表达式，提供对SQL语句中的=和IN的分片操作支持，只支持单分片键。对于简单的分片算法，可以通过简单的配置使用，从而避免繁琐的Java代码开发，如: user$->{id % 2} 表示user表根据id模2，而分成2张表，表名称为user_0到user_1。

编码

实体类：

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import com.baomidou.mybatisplus.extension.activerecord.Model;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.experimental.Accessors;

@Data
@EqualsAndHashCode(callSuper = true)
@Accessors(chain = true)
@TableName("user")
public class User extends Model<User> {
    @TableId(type= IdType.AUTO)
    private Long id;
    private String name;
    private Integer age;
}

Mapper层：

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.entity.User;

public interface UserMapper extends BaseMapper<User> {
}

Service层：

// 接口
import com.baomidou.mybatisplus.extension.service.IService;
import com.example.entity.User;
import java.util.List;

public interface UserService extends IService<User> {
    @Override
    boolean save(User entity);
    List<User> getUserList();
}

// 实现类
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.example.entity.User;
import com.example.mapper.UserMapper;
import com.example.service.UserService;
import org.springframework.stereotype.Service;
import java.util.List;

@Service("userService")
public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements UserService {

    @Override
    public boolean save(User user) {
        return super.save(user);
    }

    @Override
    public List<User> getUserList() {
        return baseMapper.selectList(Wrappers.lambdaQuery());
    }
}

Controller层：

import com.example.entity.User;
import com.example.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;
import java.util.List;

@RestController
public class UserController {

    @Autowired
    private UserService userService;

    @GetMapping("/select")
    public List<User> select(){
        return userService.getUserList();
    }

    @GetMapping("/insert")
    public Boolean insert(User user) {
        return userService.save(user);
    }
}

测试

http://localhost:8080/insert?name=James&age=12
http://localhost:8080/insert?name=James&age=13
http://localhost:8080/insert?name=James&age=14
http://localhost:8080/insert?name=James&age=15

http://127.0.0.1:8080/select

执行自定义SQL

在application.properties添加配置：

mybatis-plus.mapper-locations=classpath:/mapper/**.xml

在启动类上添加注解：

@MapperScan("com.example.mapper")

创建UserMapper.xml：

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.mapper.UserMapper">
    <select id="selectUserForAge" resultType="com.example.entity.User" parameterType="java.lang.Integer">
        select * from user where age = #{age};
    </select>
</mapper>

UserMapper.java

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.entity.User;
import org.apache.ibatis.annotations.Param;
import java.util.List;

public interface UserMapper extends BaseMapper<User> {
    List<User> selectUserForAge(@Param("age") Integer age);
}

扩展代码

public static DataSource dataSource(String dbName) {
    HikariDataSource dataSource = new HikariDataSource();
    dataSource.setDriverClassName("com.mysql.jdbc.Driver");
    dataSource.setJdbcUrl(String.format("jdbc:mysql://127.0.0.1:3306/%s", dbName));
    dataSource.setPoolName("db:" + dbName + System.currentTimeMillis());
    dataSource.setUsername("root");
    dataSource.setPassword("123456");
    return dataSource;
}

DataSource dataSource = dataSource("ds0");
try (Connection conn = dataSource.getConnection();
     Statement statement = conn.createStatement()) {
    ResultSet resultSet = statement.executeQuery("SELECT `e_sys_sequence.nextval`('userId')");
    if (resultSet.next()){
        long aLong = resultSet.getLong(1);
    }
} catch (Exception e) {

}

ShardingJDBC分库分表（支持连接查询）

导入依赖

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-test</artifactId>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.46</version>
    <scope>runtime</scope>
</dependency>
<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>2.0.0</version>
</dependency>
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <version>1.18.8</version>
</dependency>
<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
    <version>4.0.0-RC1</version>
</dependency>
<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-namespace</artifactId>
    <version>4.0.0-RC1</version>
</dependency>

创建数据库和表

ds0
  ├── t_order0
  └── t_order1
  └── t_order_item0
  └── t_order_item1
  └── t_config
ds1
  ├── user_0
  └── user_1
  └── t_order_item0
  └── t_order_item1

CREATE TABLE `t_order0` (
  `order_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) NOT NULL,
  `name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `t_order1` (
  `order_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) NOT NULL,
  `name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `t_order_item0` (
  `order_id` bigint(20) NOT NULL,
  `item` varchar(100) DEFAULT NULL,
  `user_id` bigint(20) NOT NULL,
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `t_order_item1` (
  `order_id` bigint(20) NOT NULL,
  `item` varchar(100) DEFAULT NULL,
  `user_id` bigint(20) NOT NULL,
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `t_config` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) DEFAULT NULL,
  `config` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

配置

# 这里要注册所有的数据源
spring.shardingsphere.datasource.names=ds0,ds1

# 这是数据源0的配置
spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds0.jdbc-url=jdbc:mysql://localhost:3306/ds0?serverTimezone=UTC&useSSL=false
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds0.username=root
spring.shardingsphere.datasource.ds0.password=123456

# 这是数据源1的配置
spring.shardingsphere.datasource.ds1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds1.jdbc-url=jdbc:mysql://localhost:3306/ds1?serverTimezone=UTC&useSSL=false
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds1.username=root
spring.shardingsphere.datasource.ds1.password=123456

# 分库策略
spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=order_id
spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression=ds$->{order_id % 2}

# 分表策略
spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=ds$->{0..1}.t_order$->{0..1}
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.sharding-column=order_id
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.algorithm-expression=t_order$->{order_id % 2}
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_item.actual-data-nodes=ds$->{0..1}.t_order_item$->{0..1}
spring.shardingsphere.sharding.tables.t_order_item.table-strategy.inline.sharding-column=order_id
spring.shardingsphere.sharding.tables.t_order_item.table-strategy.inline.algorithm-expression=t_order_item$->{order_id % 2}

spring.shardingsphere.sharding.binding-tables=t_order, t_order_item

# 广播表, 其主节点是ds0
spring.shardingsphere.sharding.broadcast-tables=t_config
spring.shardingsphere.sharding.tables.t_config.actual-data-nodes=ds$->{0}.t_config

# 打印执行的数据库以及语句
spring.shardingsphere.props.sql.show=true
spring.main.allow-bean-definition-overriding=true

mybatis.mapper-locations=classpath:mapper/*Dao.xml

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@MapperScan("com.example.dao")
@SpringBootApplication
public class ShardingJdbcDemoApplication {

    public static void main(String[] args) {
        SpringApplication.run(ShardingJdbcDemoApplication.class, args);
    }

}

编码

实体类：

public class Order { 
    private Long orderId;
    private Long userId;
    private String name;
}
public class OrderItem {
    private Long orderId;
    private String item;
    private Long userId;
}
public class TConfig {
    private Integer id;
    private Long userId;
    private String config;    
}

Dao类：

public interface OrderDao {
    Order queryById(Long orderId);
    int insert(Order order);
}
public interface OrderItemDao {
    OrderItem queryById(Long orderId);
    int insert(OrderItem orderItem);
    //为了测试绑定表
    @Select(value = "select * from t_order t1 inner join t_order_item t2 on t1.order_id = t2.order_id where t1.order_id=#{orderId}")
    Optional<OrderItem> getOrderItemByOrderId(@Param("orderId") Long orderId);
}
public interface ConfigDao {
    TConfig queryById(Integer id);
    int insert(TConfig tConfig);
}

Xml配置：

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.dao.OrderDao">
    <resultMap type="com.example.entity.Order" id="TOrder0Map">
        <result property="orderId" column="order_id" jdbcType="INTEGER"/>
        <result property="userId" column="user_id" jdbcType="INTEGER"/>
        <result property="name" column="name" jdbcType="VARCHAR"/>
    </resultMap>
    <!--查询单个-->
    <select id="queryById" resultMap="TOrder0Map">
        select
          order_id, user_id, name
        from t_order
        where order_id = #{orderId}
    </select>
    <!--新增所有列-->
    <insert id="insert" keyProperty="orderId" useGeneratedKeys="true">
        insert into t_order(user_id, name)
        values (#{userId}, #{name})
    </insert>
</mapper>

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.dao.OrderItemDao">
    <resultMap type="com.example.entity.OrderItem" id="TOrderItem0Map">
        <result property="orderId" column="order_id" jdbcType="INTEGER"/>
        <result property="item" column="item" jdbcType="VARCHAR"/>
        <result property="userId" column="user_id" jdbcType="INTEGER"/>
    </resultMap>
    <!--查询单个-->
    <select id="queryById" resultMap="TOrderItem0Map">
        select
          order_id, item, user_id
        from t_order_item
        where order_id = #{orderId}
    </select>
    <!--新增所有列-->
    <insert id="insert">
        insert into t_order_item(order_id, item, user_id)
        values (#{orderId}, #{item}, #{userId})
    </insert>
</mapper>

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.dao.ConfigDao">
    <resultMap type="com.example.entity.TConfig" id="TConfigMap">
        <result property="id" column="id" jdbcType="INTEGER"/>
        <result property="userId" column="user_id" jdbcType="INTEGER"/>
        <result property="config" column="config" jdbcType="VARCHAR"/>
    </resultMap>
    <!--查询单个-->
    <select id="queryById" resultMap="TConfigMap">
        select
          id, user_id, config
        from t_config
        where id = #{id}
    </select>
    <!--新增所有列-->
    <insert id="insert" keyProperty="id" useGeneratedKeys="true">
        insert into t_config(user_id, config)
        values (#{userId}, #{config})
    </insert>
</mapper>

测试

@RunWith(SpringRunner.class)
@SpringBootTest(classes = {ShardingJdbcDemoApplication.class})
public class ShardingJdbcTest {
    @Resource
    OrderDao orderDao;
    @Resource
    OrderItemDao orderItemDao;
    @Test
    public void saveOrder(){
        for (int i=0; i<5; i++){
            Order order = new Order();
            order.setName("James");
            order.setUserId(i + 0L);
            orderDao.insert(order);
        }
    }
    @Test
    public void saveOrderItem(){
        OrderItem orderItem = new OrderItem();
        orderItem.setItem("Item1");
        orderItem.setOrderId(435929970817105921L);
        orderItem.setUserId(1001L);
        orderItemDao.insert(orderItem);
    }
    @Test
    public void saveOrderItemJoin(){
        Optional<OrderItem> optional = orderItemDao.getOrderItemByOrderId(435929970817105921L);
        System.out.println(optional);
    }
}

ShardingJDBC读写分离快速入门

在这里插入图片描述

创建数据库和表

CREATE DATABASE `user_db` CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';

DROP TABLE IF EXISTS `t_user`;
CREATE TABLE `t_user` (
  `user_id` bigint(20) NOT NULL COMMENT '用户id',
  `fullname` varchar(255) NOT NULL COMMENT '用户姓名',
  `user_type` char(1) DEFAULT NULL COMMENT '用户类型',
  PRIMARY KEY (`user_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

读写规则配置

# 打印sql
spring.shardingsphere.props.sql.show=true
spring.main.allow-bean-definition-overriding=true

# 定义数据源
spring.shardingsphere.datasource.names=m1,s1

spring.shardingsphere.datasource.m1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.m1.jdbc-url=jdbc:mysql://localhost:3380/user_db?characterEncoding=utf-8&serverTimezone=UTC&useSSL=false
spring.shardingsphere.datasource.m1.username=root
spring.shardingsphere.datasource.m1.password=123456

spring.shardingsphere.datasource.s1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.s1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.s1.jdbc-url=jdbc:mysql://localhost:3381/user_db?characterEncoding=utf-8&serverTimezone=UTC&useSSL=false
spring.shardingsphere.datasource.s1.username=root
spring.shardingsphere.datasource.s1.password=123456

# 主库从库逻辑数据源定义 ds0为user_db
spring.shardingsphere.sharding.master-slave-rules.ds0.master-data-source-name=m1
spring.shardingsphere.sharding.master-slave-rules.ds0.slave-data-source-names=s1

# t_user分表策略，固定分配至ds0的t_user真实表，ds0对应了m1和s1
spring.shardingsphere.sharding.tables.t_user.actual-data-nodes=ds0.t_user

编码

数据操作

import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Component;
import java.util.List;
import java.util.Map;

@Mapper
@Component
public interface UserDao {
    @Insert("insert into t_user(user_id, fullname) value(#{userId},#{fullname})")
    int insertUser(@Param("userId") Long userId, @Param("fullname") String fullname);
    @Select({"<script>",
            " select",
            " * ",
            " from t_user t ",
            " where t.user_id in",
            "<foreach collection='userIds' item='id' open='(' separator=',' close=')'>",
            "#{id}",
            "</foreach>",
            "</script>"
    })
    List<Map> selectUserbyIds(@Param("userIds") List<Long> userIds);
}

单元测试

@RunWith(SpringRunner.class)
@SpringBootTest(classes = {ShardingJdbcDemoApplication.class})
public class ShardingJdbcTest {
    @Resource
    private UserDao userDao;
    @Test
    public void insertUser(){
        for(long i=0; i<5; i++){
            userDao.insertUser(i, "James" + i);
        }
    }
    @Test
    public void selectUserbyIds(){
        List<Long> userIds = new ArrayList<>();
        userIds.add(0L);
        userIds.add(1L);
        userIds.add(2L);
        userDao.selectUserbyIds(userIds);
    }
}

综合案例

创建数据库和表

创建store_db、product_db_1、product_db_2数据库：

CREATE DATABASE `store_db` CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';
CREATE DATABASE `product_db_1` CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';
CREATE DATABASE `product_db_2` CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';

在store_db数据库中执行下面的脚本：

CREATE TABLE `store_info` (
  `id` bigint(20) NOT NULL COMMENT 'id',
  `store_name` varchar(100) DEFAULT NULL COMMENT '店铺名称',
  `reputation` int(11) DEFAULT NULL COMMENT '信誉等级',
  `region_code` varchar(50) DEFAULT NULL COMMENT '店铺所在地',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
INSERT INTO `store_info` VALUES ('1', 'XX零食店', '4', '110100');
INSERT INTO `store_info` VALUES ('2', 'XX饮品店', '3', '410100');

分别在store_db、product_db_1、product_db_2数据库中执行下面的脚本：

DROP TABLE IF EXISTS `region`;
CREATE TABLE `region` (
  `id` bigint(20) NOT NULL COMMENT 'id',
  `region_code` varchar(50) DEFAULT NULL COMMENT '地理区域编码',
  `region_name` varchar(100) DEFAULT NULL COMMENT '地理区域名称',
  `level` tinyint(1) DEFAULT NULL COMMENT '地理区域级别(省、市、县)',
  `parent_region_code` varchar(50) DEFAULT NULL COMMENT '上级地理区域编码',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
INSERT INTO `region` VALUES ('1', '110000', '北京', '0', null);
INSERT INTO `region` VALUES ('2', '410000', '河南省', '0', null);
INSERT INTO `region` VALUES ('3', '110100', '北京市', '1', '110000');
INSERT INTO `region` VALUES ('4', '410100', '郑州市', '1', '410000');

分别在product_db_1、product_db_2数据库中执行下面的脚本：

DROP TABLE IF EXISTS `product_info_1`;
CREATE TABLE `product_info_1` (
  `product_info_id` bigint(20) NOT NULL COMMENT 'id',
  `store_info_id` bigint(20) DEFAULT NULL COMMENT '所属店铺id',
  `product_name` varchar(100) DEFAULT NULL COMMENT '商品名称',
  `spec` varchar(50) DEFAULT NULL COMMENT '规 格',
  `region_code` varchar(50) DEFAULT NULL COMMENT '产地',
  `price` decimal(10,0) DEFAULT NULL COMMENT '商品价格',
  `image_url` varchar(100) DEFAULT NULL COMMENT '商品图片',
  PRIMARY KEY (`product_info_id`) USING BTREE,
  KEY `FK_Reference_1` (`store_info_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

DROP TABLE IF EXISTS `product_info_2`;
CREATE TABLE `product_info_2` (
  `product_info_id` bigint(20) NOT NULL COMMENT 'id',
  `store_info_id` bigint(20) DEFAULT NULL COMMENT '所属店铺id',
  `product_name` varchar(100) DEFAULT NULL COMMENT '商品名称',
  `spec` varchar(50) DEFAULT NULL COMMENT '规 格',
  `region_code` varchar(50) DEFAULT NULL COMMENT '产地',
  `price` decimal(10,0) DEFAULT NULL COMMENT '商品价格',
  `image_url` varchar(100) DEFAULT NULL COMMENT '商品图片',
  PRIMARY KEY (`product_info_id`) USING BTREE,
  KEY `FK_Reference_1` (`store_info_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

DROP TABLE IF EXISTS `product_descript_1`;
CREATE TABLE `product_descript_1` (
  `id` bigint(20) NOT NULL COMMENT 'id',
  `product_info_id` bigint(20) DEFAULT NULL COMMENT '所属商品id',
  `descript` longtext COMMENT '商品描述',
  `store_info_id` bigint(20) DEFAULT NULL COMMENT '所属店铺id',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `FK_Reference_2` (`product_info_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

DROP TABLE IF EXISTS `product_descript_2`;
CREATE TABLE `product_descript_2` (
  `id` bigint(20) NOT NULL COMMENT 'id',
  `product_info_id` bigint(20) DEFAULT NULL COMMENT '所属商品id',
  `descript` longtext COMMENT '商品描述',
  `store_info_id` bigint(20) DEFAULT NULL COMMENT '所属店铺id',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `FK_Reference_2` (`product_info_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

分片配置

既然是分库分表，那么就需要定义多个真实数据源，每一个数据库链接信息就是一个数据源定义，如：

spring.shardingsphere.datasource.m0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.m0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.m0.jdbc-url=jdbc:mysql://localhost:3380/store_db?characterEncoding=utf-8&serverTimezone=UTC&useSSL=false
spring.shardingsphere.datasource.m0.username=root
spring.shardingsphere.datasource.m0.password=123456

m0，就是这个真实数据源的名称，然后需要告诉Sharding-JDBC，咱们有哪些真实数据源，如：

spring.shardingsphere.datasource.names=m0,m1,m2,s0,s1,s2

如果需要配置读写分离，还需要告诉Sharding-JDBC，这么多真实数据源，那几个是一套读写分离？也就是定义主从逻辑数据源：

spring.shardingsphere.sharding.master-slave-rules.ds0.master-data-source-name=m0
spring.shardingsphere.sharding.master-slave-rules.ds0.slave-data-source-names=s0

若我们已经对m0和s0做了mysql主从同步，那我们需要告诉Sharding-JDBC，m0、s0为一组主从同步数据源，其中m0为主，s0为从，并且定义名称为ds0，这个ds0就是主从逻辑数据源。

最终Sharding-JDBC配置如下：

在这里插入代码片

参考

JAVA 关于分库分表的学习和DEMO
sharding-jdbc结合mybatis实现分库分表功能
 分库分表主键问题 - Sharding-Jdbc
sharding-jdbc分库分表
 SpringBoot使用Sharding-JDBC分库分表
 Sharding-JDBC 系列 01 - 分库分表
 详解Spring Boot中整合Sharding-JDBC读写分离示例
 Sharding-JDBC:查询量大如何优化？
利用ShardingSphere-JDBC实现分库分表

yyangqqian

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
MySQL 之分库分表技术ShardingJDBC

参考：https://shardingsphere.apache.org/document/current/cn/overview/https://www.jianshu.com/p/52ccdb7d5683https://mp.weixin.qq.com/s/M0e5u8V_c2sv6aYJHl0Y6Qhttps://zhuanlan.zhihu.com/p/41705760...
复制链接

扫一扫