sharding-jdbc 取模分表 (一) - user_id 分表

最新推荐文章于 2024-04-19 19:02:51 发布

重生之我是一名程序员

最新推荐文章于 2024-04-19 19:02:51 发布

阅读量1.3k

点赞数

文章标签： sharding-jdbc

原文链接：https://editor.csdn.net/md?not_checkout=1&articleId=119300802

版权

sharding-jdbc 分表 - user_id 分表

准备

有表, 订单分3个表，订单明细 4 个表:

mysql> show tables;
+----------------------+
| Tables_in_shard_ds_0 |
+----------------------+
| t_order_0            |
| t_order_1            |
| t_order_2            |
| t_order_item_0       |
| t_order_item_1       |
| t_order_item_2       |
| t_order_item_3       |
| t_sequence           |
+----------------------+
8 rows in set (0.00 sec)

表结构：

# t_order_0
CREATE TABLE `t_order_0` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `order_no` varchar(50) DEFAULT NULL,
  `user_id` int DEFAULT NULL,
  `amount` int DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=220458 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

# t_order_item_0
CREATE TABLE `t_order_item_0` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '订单明细id',
  `order_id` bigint DEFAULT NULL COMMENT '订单id',
  `item_name` varchar(255) DEFAULT NULL COMMENT '商品名称',
  `item_no` varchar(255) DEFAULT NULL COMMENT '商品编号',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime DEFAULT NULL COMMENT '修改时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12501 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

分表策略

根据 t_order 中的 user_id
根据 t_order_item 中的 order_id

注意，如果按照 order_id 分表，需要保证 order_id 全局唯一，不然 join 查询时会有问题!!

项目依赖 - spring boot + mybatis plus

    <properties>
        <java.version>11</java.version>
        <sharding-sphere.version>4.1.1</sharding-sphere.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.2.0</version>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.4.2</version>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.2.6</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jdbc</artifactId>
        </dependency>

<!--        不要加，会和sharding-jdbc-spring-boot-starter 冲突!!!! -->
<!--        <dependency>-->
<!--            <groupId>com.alibaba</groupId>-->
<!--            <artifactId>druid-spring-boot-starter</artifactId>-->
<!--            <version>1.2.6</version>-->
<!--        </dependency>-->
        <!-- for spring boot -->
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>${sharding-sphere.version}</version>
        </dependency>

        <!-- for spring namespace -->
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-namespace</artifactId>
            <version>${sharding-sphere.version}</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>


        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper-spring-boot-starter</artifactId>
            <version>1.3.0</version>
        </dependency>

        <dependency>
            <groupId>com.github.jsqlparser</groupId>
            <artifactId>jsqlparser</artifactId>
            <version>4.1</version>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.76</version>
        </dependency>
    </dependencies>

启动类

@SpringBootApplication
@MapperScan("com.x.z.shardjdbc.persist.mapper")
public class ShardingJdbcSampleApplication {

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

}

application.properties 配置


spring.shardingsphere.datasource.names=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://localhost:3306/shard_ds_0
spring.shardingsphere.datasource.ds0.username=root
spring.shardingsphere.datasource.ds0.password=111111


spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=ds0.t_order_$->{0..2}
# 分片键
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.sharding-column=user_id
# 分片策略
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.algorithm-expression=t_order_$->{user_id % 3}


spring.shardingsphere.sharding.tables.t_order_item.actual-data-nodes=ds0.t_order_item_$->{0..3}
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 % 4}

# 输出 sharding jdbc 查询日志
spring.shardingsphere.props.sql.show=true
logging.level.com.x.z.shardjdbc.persist.mapper=debug
mybatis-plus.type-aliases-package=com.x.z.shardjdbc.persist.model

mapper 文件

public interface OrderModelMapper extends BaseMapper<OrderModel> {

    //单表查询
    @Select("<script>" +
            "select * from t_order where create_time  <![CDATA[>=]]>#{start} and create_time<![CDATA[<=]]>#{end}" +
            "<if test=\"userId!=null\"> and user_id=#{userId}</if>" +
            "</script>")
    List<OrderModel> list(OrderPageQuery query);

    // join 查询
    List<OrderDto> listWithItem(OrderPageQuery query);
}

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.x.z.shardjdbc.persist.mapper.OrderModelMapper">

    <resultMap id="listWithItemMap" type="com.x.z.shardjdbc.dto.OrderDto">
        <id property="id" column="id" jdbcType="BIGINT"/>
        <result property="orderNo" column="order_no" jdbcType="VARCHAR"/>
        <result property="userId" column="user_id" jdbcType="BIGINT"/>
        <result property="amount" column="amount" jdbcType="INTEGER"/>
        <result property="createTime" column="create_time" jdbcType="TIMESTAMP"/>
        <result property="updateTime" column="update_time" jdbcType="TIMESTAMP"/>
        <collection property="itemList" ofType="com.x.z.shardjdbc.dto.OrderItemDto" javaType="java.util.ArrayList">
            <id property="id" column="item_id" jdbcType="BIGINT"/>
            <result property="itemNo" column="item_no" jdbcType="VARCHAR"/>
            <result property="itemName" column="item_name" jdbcType="INTEGER"/>
            <result property="orderId" column="order_id" jdbcType="BIGINT"/>
            <result property="createTime" column="item_create_time" jdbcType="TIMESTAMP"/>
            <result property="updateTime" column="item_update_time" jdbcType="TIMESTAMP"/>
        </collection>
    </resultMap>

    <select id="listWithItem" resultMap="listWithItemMap">
        SELECT
        tor.*,
        toi.id as item_id,
        toi.item_name,
        toi.item_no,
        toi.create_time AS item_create_time,
        toi.update_time AS item_update_time,
        toi.order_id
        FROM
        t_order tor
        INNER JOIN t_order_item toi ON toi.order_id = tor.id
        WHERE
        tor.create_time <![CDATA[>=]]>#{start}
        AND tor.create_time <![CDATA[<=]]>#{end}
        <if test="userId!=null">
            and tor.user_id=#{userId}
        </if>
    </select>

</mapper>

测试用例

    /**
     * 分页查询
     */
    @Test
    public void pageQueryTest() {

        LocalDateTime now = LocalDateTime.now();
        OrderPageQuery query = new OrderPageQuery();
        query.setPageSize(10);
        query.setPageIndex(2);
        query.setStart(now.minusDays(30));
        query.setEnd(now);
        query.setUserId(1L);
        PageInfo<OrderModel> pageInfo = orderModelService.pageList(query);
        System.out.println(JSON.toJSON(pageInfo));
    }

    /**
     * 分页查询 with join
     */
    @Test
    public void pageQueryWithJoinTest() {

        LocalDateTime now = LocalDateTime.now();
        OrderPageQuery query = new OrderPageQuery();
        query.setPageSize(10);
        query.setPageIndex(1);
        query.setStart(now.minusDays(30));
        query.setEnd(now);
        query.setUserId(1L);
        PageInfo<OrderDto> pageInfo = orderModelService.pageListWithJoin(query);
        System.out.println(JSON.toJSON(pageInfo));
    }

普通分页查询

不带路由键 user_id

@Test
public void pageQueryTest() {

    LocalDateTime now = LocalDateTime.now();
    OrderPageQuery query = new OrderPageQuery();
    query.setPageSize(10);
    query.setPageIndex(2);
    query.setStart(now.minusDays(30));
    query.setEnd(now);
    // query.setUserId(1L);
    PageInfo<OrderModel> pageInfo = orderModelService.pageList(query);
    System.out.println(JSON.toJSON(pageInfo));
}

# 查询总数
Actual SQL: ds0 ::: SELECT count(0) FROM t_order_0 WHERE create_time >= ? AND create_time <= ? 
Actual SQL: ds0 ::: SELECT count(0) FROM t_order_1 WHERE create_time >= ? AND create_time <= ? 
Actual SQL: ds0 ::: SELECT count(0) FROM t_order_2 WHERE create_time >= ? AND create_time <= ? 

# 分页
Actual SQL: ds0 ::: select * from t_order_0 where create_time   >= ? and create_time <= ? LIMIT ?, ?  ::: [0, 20]
Actual SQL: ds0 ::: select * from t_order_1 where create_time   >= ? and create_time <= ? LIMIT ?, ?  ::: [0, 20]
Actual SQL: ds0 ::: select * from t_order_2 where create_time   >= ? and create_time <= ?LIMIT ?, ?  ::: [0, 20]

可以发现，分页查询总数时 sharding jdbc 对每个表都进行了计数查询，但是在分页时，实际上取的分页大小比我们传入的值要大，我设置的是10，但是sharding jdbc 取的 limit 是 20 (发现规律: limit 大小等于 pageIndexpageSize = 210) 。并且，不带路由键时，会查询所有表

带路由键 user_id

@Test
public void pageQueryTest() {

    LocalDateTime now = LocalDateTime.now();
    OrderPageQuery query = new OrderPageQuery();
    query.setPageSize(10);
    query.setPageIndex(2);
    query.setStart(now.minusDays(30));
    query.setEnd(now);
    // 打开注释！！
    query.setUserId(1L);
    PageInfo<OrderModel> pageInfo = orderModelService.pageList(query);
    System.out.println(JSON.toJSON(pageInfo));
}


# 查询总数 
Actual SQL: ds0 ::: SELECT count(0) FROM t_order_1 WHERE create_time >= ? AND create_time <= ? AND user_id = ? 

# 分页
select * from t_order_1 where create_time   >= ? and create_time <= ?  and user_id=? LIMIT ?, ?  ::: [10, 10]

可以发现，带路由键，统计的表明显要少得多，查询效率也比较快

分页查询 with join

Actual SQL: ds0 ::: SELECT count(0) FROM t_order_0 tor INNER JOIN t_order_item_1 toi ON toi.order_id = tor.id WHERE
Actual SQL: ds0 ::: SELECT count(0) FROM t_order_0 tor INNER JOIN t_order_item_0 toi ON toi.order_id = tor.id WHERE
Actual SQL: ds0 ::: SELECT count(0) FROM t_order_0 tor INNER JOIN t_order_item_3 toi ON toi.order_id = tor.id WHERE
Actual SQL: ds0 ::: SELECT count(0) FROM t_order_0 tor INNER JOIN t_order_item_2 toi ON toi.order_id = tor.id WHERE
Actual SQL: ds0 ::: SELECT count(0) FROM t_order_1 tor INNER JOIN t_order_item_1 toi ON toi.order_id = tor.id WHERE
Actual SQL: ds0 ::: SELECT count(0) FROM t_order_1 tor INNER JOIN t_order_item_0 toi ON toi.order_id = tor.id WHERE
Actual SQL: ds0 ::: SELECT count(0) FROM t_order_1 tor INNER JOIN t_order_item_3 toi ON toi.order_id = tor.id WHERE
Actual SQL: ds0 ::: SELECT count(0) FROM t_order_1 tor INNER JOIN t_order_item_2 toi ON toi.order_id = tor.id WHERE
Actual SQL: ds0 ::: SELECT count(0) FROM t_order_2 tor INNER JOIN t_order_item_1 toi ON toi.order_id = tor.id WHERE
Actual SQL: ds0 ::: SELECT count(0) FROM t_order_2 tor INNER JOIN t_order_item_0 toi ON toi.order_id = tor.id WHERE
Actual SQL: ds0 ::: SELECT count(0) FROM t_order_2 tor INNER JOIN t_order_item_3 toi ON toi.order_id = tor.id WHERE
Actual SQL: ds0 ::: SELECT count(0) FROM t_order_2 tor INNER JOIN t_order_item_2 toi ON toi.order_id = tor.id WHERE...

其实和上面一样，关联查询时, 未带上路由键，用的是迪卡尔积关联表查询总数，如果带上路由键，关联查询的表就要少一点

重生之我是一名程序员

关注

0
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
sharding-jdbc 取模分表 (一) - user_id 分表

sharding-jdbc 分表 - user_id 分表准备有表, 订单分3个表，订单明细 4 个表:mysql> show tables;+----------------------+| Tables_in_shard_ds_0 |+----------------------+| t_order_0 || t_order_1 || t_order_2 || t_order_item_0 ||
复制链接

扫一扫