3.Sharding-JDBC快速入门-水平分表

Sharding-JDBC快速入门-水平分表

需求说明

创建两张表，t_order_1和t_order_2，这两张表是订单表拆分后的表，通过Sharding-Jdbc向订单表插入数据，按照一定的分片规则，主键为偶数的进入t_order_1，另一部分数据进入t_order_2，通过Sharding-Jdbc 查询数据，根据SQL语句的内容从t_order_1或t_order_2查询数据。

环境搭建

环境说明

• 数据库：MySQL-5.7.37
• JDK：64位 jdk1.8.0_271
• 应用框架：spring-boot-2.5.5，Mybatis2.2.2
• Sharding-JDBC：sharding-jdbc-spring-boot-starter-4.1.1

创建数据库

创建订单库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) CHARACTER
SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '订单状态',
 PRIMARY KEY (`order_id`) USING BTREE
) ENGINE = INNODB CHARACTER
SET = utf8 COLLATE = utf8_general_ci 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) CHARACTER
SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '订单状态',
 PRIMARY KEY (`order_id`) USING BTREE
) ENGINE = INNODB CHARACTER
SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

引入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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.zqlwudi.sharding-jdbc</groupId>
    <artifactId>sharding-jdbc</artifactId>
    <version>1.0-SNAPSHOT</version>

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

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
            <version>2.5.5</version>
        </dependency>

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

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

        <!-- 不能使用druid-spring-boot-starter，会导致：Property 'sqlSessionFactory' or 'sqlSessionTemplate' are required 和Mapper接口找不到异常 -->
<!--        <dependency>-->
<!--            <groupId>com.alibaba</groupId>-->
<!--            <artifactId>druid-spring-boot-starter</artifactId>-->
<!--            <version>1.2.8</version>-->
<!--        </dependency>-->

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.2.8</version>
        </dependency>

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

        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.17</version>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.20</version>
        </dependency>
    </dependencies>
</project>

编写程序

分片规则配置

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

在application.properties中配置

spring.application.name=shardingjdbc
server.port=8080
mybatis.type-aliases-package=com.zqlwudi.po
mybatis.configuration.map-underscore-to-camel-case=true
spring.main.allow-bean-definition-overriding=true

# 以下是分片规则配置
# 定义数据源
spring.shardingsphere.datasource.names=m1
spring.shardingsphere.datasource.m1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.driver‐class‐name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.m1.url=jdbc:mysql://localhost:3306/order_db?useUnicode=true
spring.shardingsphere.datasource.m1.username=root
spring.shardingsphere.datasource.m1.password=123456

# 指定t_order表的数据分布情况，配置数据节点
spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=m1.t_order_$->{1..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

# 指定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}

#打开sql输出日志
spring.shardingsphere.props.sql.show=true

logging.level.root=info
logging.level.org.springframework.web=info
logging.level.com.itheima.dbsharding=debug
logging.level.druid.sql=debug

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

数据操作

@Mapper
public interface TOrderMapper {

    /**
     * 使用shardingjdbc的主键生成策略
     *
     * @param price  订单价格
     * @param userId 用户id
     * @param status 状态
     * @return
     */
    @Insert("INSERT INTO t_order (price,user_id,status) value(#{price},#{userId},#{status})")
    int addOrder(@Param("price") BigDecimal price, @Param("userId") Long userId, @Param("status") String status);

    /**
     * 自定义主键
     *
     * @param orderId 订单id
     * @param price   订单价格
     * @param userId  用户id
     * @param status  状态
     * @return
     */
    @Insert("INSERT INTO t_order (order_id,price,user_id,status) value(#{orderId},#{price},#{userId},#{status})")
    int addByOrderId(@Param("orderId") Long orderId, @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' item='id' open='(' separator=',' close=')'>" +
            " #{id} "
            + "</foreach>" +
            "</script>"})
    List<Map> selectOrderByIds(@Param("orderIds") List<Long> orderIds);
}

业务代码

编写service层

@Service
public class TOrderService {

    @Resource
    private TOrderMapper tOrderMapper;

    public Integer addOrder(BigDecimal price, Long userId, String status) {
        return tOrderMapper.addOrder(price, userId, status);
    }


    public Integer addByOrderId(Long orderId, BigDecimal price, Long userId, String status) {
        return tOrderMapper.addByOrderId(orderId, price, userId, status);
    }

    public List<Map> selectOrderByIds(List<Long> orderIds) {
        return tOrderMapper.selectOrderByIds(orderIds);
    }
}

编写controller层进行访问

@RestController
@RequestMapping(value = "order")
public class TOrderController {

    @Autowired
    private TOrderService tOrderService;

    @PostMapping("add")
    public Integer addOrder(@RequestParam("price") BigDecimal price, @RequestParam("userId") Long userId,
                            @RequestParam("status") String status) {
        return tOrderService.addOrder(price, userId, status);
    }

    @PostMapping("addByOrderId")
    public Integer addByOrderId(@RequestParam("orderId") Long orderId, @RequestParam("price") BigDecimal price,
                                @RequestParam("userId") Long userId, @RequestParam("status") String status) {
        return tOrderService.addByOrderId(orderId, price, userId, status);
    }

    @PostMapping("selectOrderByIds")
    public List<Map> selectOrderByIds(@RequestParam("orderIds") List<Long> orderIds) {
        return tOrderService.selectOrderByIds(orderIds);
    }
}

测试

添加数据

启动服务后多次访问该路径：http://localhost:8080/order/add?price=100.00&userId=1001&status=1

这里我访问了2次接口，通过日志可以发现order_id为奇数的被插入到t_order_2表，为偶数的被插入到t_order_1表，达到预期目标。绿圈中的id是通过shardingjdbc的主键生成策略SNOWFLAKE

查询数据

参数路径：http://localhost:8080/order/selectOrderByIds?orderIds=716711911474855936

通过日志可以发现，根据传入order_id的奇偶不同，sharding-jdbc分别去不同的表检索数据，达到预期目标。

流程分析

通过日志分析，Sharding-JDBC在拿到用户要执行的sql之后干了哪些事儿：

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