ShardingJdbc 数据分片 + 读写分离 | Eddie'Blog
ShardingJdbc 数据分片 + 读写分离

ShardingJdbc 数据分片 + 读写分离

eddie 447 2020-10-28

目录

前提条件

DDL

与之前的文章,稍有修改,请按如下:

CREATE TABLE `area` (
  `id` int NOT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `t_order_1` (
  `order_id` int NOT NULL,
  `order_amount` decimal(10,2) NOT NULL,
  `order_status` int NOT NULL,
  `user_id` int NOT NULL,
  PRIMARY KEY (`order_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `t_order_2` (
  `order_id` int NOT NULL,
  `order_amount` decimal(10,2) NOT NULL,
  `order_status` int NOT NULL,
  `user_id` int NOT NULL,
  PRIMARY KEY (`order_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `t_order_item_1` (
  `id` int NOT NULL,
  `order_id` int NOT NULL,
  `pruduct_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `num` int NOT NULL,
  `user_id` int NOT NULL COMMENT '用户id',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `t_order_item_2` (
  `id` int NOT NULL,
  `order_id` int NOT NULL,
  `pruduct_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `num` int NOT NULL,
  `user_id` int NOT NULL COMMENT '用户id',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

全部库的DDL一模一样
图片.png

编码部分

一、Spring命名空间配置 - 读写分离

Maven 依赖

<dependency>
        <groupId>org.apache.shardingsphere</groupId>
        <artifactId>sharding-jdbc-spring-namespace</artifactId>
        <version>4.0.0-RC2</version>
</dependency>

sharding-jdbc.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:sharding="http://shardingsphere.apache.org/schema/shardingsphere/sharding"
       xmlns:master-slave="http://shardingsphere.apache.org/schema/shardingsphere/masterslave"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
                        http://www.springframework.org/schema/beans/spring-beans.xsd
                        http://shardingsphere.apache.org/schema/shardingsphere/sharding
                        http://shardingsphere.apache.org/schema/shardingsphere/sharding/sharding.xsd
                        http://shardingsphere.apache.org/schema/shardingsphere/masterslave
                        http://shardingsphere.apache.org/schema/shardingsphere/masterslave/master-slave.xsd
                        ">
    <!-- MySQL数据源 -->
    <bean id="ds0" class="com.zaxxer.hikari.HikariDataSource" destroy-method="close">
        <property name="driverClassName" value="com.mysql.cj.jdbc.Driver" />
        <property name="username" value="eddie" />
        <property name="password" value="Abc@123456" />
        <property name="jdbcUrl" value="jdbc:mysql://192.168.8.246/sharding_order?serverTimezone=Asia/Shanghai&amp;useSSL=false"/>
    </bean>
    <!-- MySQL已经搭建好主从关系, 并非ShardingJdbc做的 -->
    <bean id="slave0" class="com.zaxxer.hikari.HikariDataSource" destroy-method="close">
        <property name="driverClassName" value="com.mysql.cj.jdbc.Driver" />
        <property name="username" value="eddie" />
        <property name="password" value="Abc@123456" />
        <property name="jdbcUrl" value="jdbc:mysql://192.168.8.245/sharding_order?serverTimezone=Asia/Shanghai&amp;useSSL=false"/>
    </bean>
    <bean id="ms1" class="com.zaxxer.hikari.HikariDataSource" destroy-method="close">
        <property name="driverClassName" value="com.mysql.cj.jdbc.Driver" />
        <property name="username" value="eddie" />
        <property name="password" value="Abc@123456" />
        <property name="jdbcUrl" value="jdbc:mysql://192.168.8.247/shard_order?serverTimezone=Asia/Shanghai&amp;useSSL=false"/>
    </bean>

    <!-- 主从负载均衡, 策略:随机 -->
    <master-slave:load-balance-algorithm id="msStrategy" type="random" />

    <!-- sharding-jdbc数据源 -->
    <sharding:data-source id="sharding-data-source">
        <!-- 多个数据源用逗号分割 -->
        <sharding:sharding-rule data-source-names="ds0,slave0,ms1">
            <!-- 主从关系 -->
            <sharding:master-slave-rules>
                <sharding:master-slave-rule id="ms0" master-data-source-name="ds0" slave-data-source-names="slave0" strategy-ref=""/>
            </sharding:master-slave-rules>

            <sharding:table-rules>
                <!-- 逻辑表名称 logic-table="t_order" 对应Mapper.xml里面的表名 -->
                <!-- 数据节点 actual-data-nodes="ds$->{0..1}.t_order_$->{1..2}"  -->
                <!-- 数据库分片策略 database-strategy-ref="databaseStrategy" -->
                <!-- 数据表分片策略 table-strategy-ref="tableStrategy"  -->
                <sharding:table-rule logic-table="t_order"
                                     actual-data-nodes="ms$->{0..1}.t_order_$->{1..2}"
                                     database-strategy-ref="databaseStrategy"
                                     table-strategy-ref="tableStrategy"
                />
            </sharding:table-rules>
            <!-- 广播表规则列表 -->
            <sharding:broadcast-table-rules>
                <sharding:broadcast-table-rule table="area"/>
            </sharding:broadcast-table-rules>
        </sharding:sharding-rule>
    </sharding:data-source>

    <!-- 数据库分片规则 -->
    <sharding:inline-strategy id="databaseStrategy"
                              sharding-column="user_id"
                              algorithm-expression="ms$->{user_id % 2}"
    />

    <!-- 数据表分片规则 -->
    <!-- id取模,可能会出现寻找到 t_order_0 的表,但实际上是没有的,所以 +1  -->
    <sharding:inline-strategy id="tableStrategy"
                              sharding-column="id"
                              algorithm-expression="t_order_$->{id % 2 + 1}"
    />

    <!-- 设置 mybatis 数据源 -->
    <bean class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="sharding-data-source"/>
        <property name="mapperLocations" value="classpath*:/mybatis/*.xml" />
    </bean>
</beans>

二、SpringBoot Start 配置- 读写分离

三者关系:

  • ds0, slave0 读写分离
  • ds0,ms1 数据分片

Maven 依赖

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

application.properties

####################################################
#
#  博客:blog.eddilee.cn
#  备注:为了方便查看,使用 properties 而不是 yml 格式
#
####################################################

# 配置真实数据源
spring.shardingsphere.datasource.names=ds0,slave0,ms1

# 配置第 1 个数据源
spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds0.jdbcUrl=jdbc:mysql://192.168.8.246/sharding_order
spring.shardingsphere.datasource.ds0.username=eddie
spring.shardingsphere.datasource.ds0.password=Abc@123456

# 配置第 2 个数据源
spring.shardingsphere.datasource.slave0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.slave0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.slave0.jdbcUrl=jdbc:mysql://192.168.8.245/sharding_order
spring.shardingsphere.datasource.slave0.username=eddie
spring.shardingsphere.datasource.slave0.password=Abc@123456

# 配置第 3 个数据源
spring.shardingsphere.datasource.ms1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ms1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ms1.jdbcUrl=jdbc:mysql://192.168.8.247/shard_order
spring.shardingsphere.datasource.ms1.username=eddie
spring.shardingsphere.datasource.ms1.password=Abc@123456

# 配置 主从关系
spring.shardingsphere.sharding.master-slave-rules.ms0.master-data-source-name=ds0
spring.shardingsphere.sharding.master-slave-rules.ms0.slave-data-source-names=slave0

# 配置 负载均衡, 策略:随机
spring.shardingsphere.sharding.master-slave-rules.ms0.load-balance-algorithm-type=RANDOM

# 配置 t_order 表规则
spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=ms$->{0..1}.t_order_$->{1..2}

# 配置 t_order 分库策略
spring.shardingsphere.sharding.tables.t_order.database-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.tables.t_order.database-strategy.inline.algorithm-expression=ms$->{user_id % 2}

# 配置 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}

# 配置 广播表规则列表
spring.shardingsphere.sharding.broadcast-tables=area



# 配置 mybatis
mybatis.mapper-locations=/mybatis/*.xml
mybatis.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl

# 设置日志日期格式
logging.pattern.dateformat=yyyy-MM-dd HH:mm:ss.SSS

三、单元测试

无论哪种方式,都使用如下测试方式

    @Test
    public void testMsOrder() {
        OrderExample orderExample = new OrderExample();
        orderExample.createCriteria().andUserIdEqualTo(20).andOrderIdEqualTo(4);
        List<Order> orders = orderMapper.selectByExample(orderExample);
        for (int i = 0; i < 10; i++) {
            orders.forEach(item->{
                System.out.println("================");
                System.out.println("userId = " + item.getUserId());
                System.out.println("orderId = " + item.getOrderId());
                // 通过修改从库的金额,在遍历出来,发现全部是100.00.证明都是在从库读取数据的,并非是随机
                System.out.println("amount = " + item.getOrderAmount());
            });
        }
    }

# Java