ShardingSphere分表

docker创建数据库

安装指定版本mysql

sudo docker pull mysql:8.0.29

启动order1-3310

docker run -d \
-p 3310:3306 \
-v /study/shardingsphere/mysql/order1/conf:/etc/mysql/conf.d \
-v /study/shardingsphere/mysql/order1/data:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=123456 \
--name dk-mysql-order1 \
mysql:8.0.29

重启MySQL容器

docker restart dk-mysql-order1

设置mysql容器在docker启动的时候启动

docker update dk-mysql-order1 --restart=always

使用命令行登录MySQL服务器：

#进入容器：env LANG=C.UTF-8 避免容器中显示中文乱码
docker exec -it dk-mysql-order1 env LANG=C.UTF-8 /bin/bash
#进入容器内的mysql命令行
mysql -uroot -p
#修改默认密码校验方式(不执行，navicat无法连接
ALTER USER ‘root’@‘%’ IDENTIFIED WITH mysql_native_password BY ‘123456’;

ShardingSphere接入进行分表插入和查询

官网地址：

https://shardingsphere.apache.org/document/5.1.1/cn/user-manual/shardingsphere-jdbc/spring-boot-starter/rules/sharding/

pom.xml

同下面这个地址
https://blog.youkuaiyun.com/qq_41082640/article/details/131097769
OrderPo

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Builder;
import lombok.Data;

import java.math.BigDecimal;

@Data
    @TableName("t_order")
    @Builder
    public class OrderPo {
        // @TableId(type = IdType.ASSIGN_ID)//分布式id
        @TableId(type = IdType.AUTO)
        private Long id;
        private String orderNo;
        private Long userId;
        private BigDecimal amount;
    }

OrderMapper

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.dk.shardingjdbcdemo.dto.OrderPo;
import org.apache.ibatis.annotations.Mapper;

@Mapper
public interface OrderMapper extends BaseMapper<OrderPo> {
}

application.properties

spring.application.name=ShardingJdbcDemo
server.servlet.context-path=/shardingJdbcDemo

spring.shardingsphere.props.sql-show=true

#内存模式
spring.shardingsphere.mode.type=Memory

# 配置真实数据源
spring.shardingsphere.datasource.names=order1

# 配置第 1 个数据源
spring.shardingsphere.datasource.order1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.order1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.order1.jdbc-url=jdbc:mysql://192.168.229.128:3310/db_order
spring.shardingsphere.datasource.order1.username=root
spring.shardingsphere.datasource.order1.password=123456

# 由数据源名 + 表名组成，以小数点分隔。多个表以逗号分隔，支持 inline 表达式。缺省表示使用已知数据源与逻辑表名称生成数据节点，用于广播表（即每个库中都需要一个同样的表用于关联查询，多为字典表）或只分库不分表且所有库的表结构完全一致的情况
#spring.shardingsphere.rules.sharding.tables.t_order.actual-data-nodes=order1.t_order_0,order1.t_order_1
spring.shardingsphere.rules.sharding.tables.t_order.actual-data-nodes=order1.t_order$->{0..1}

##-----------------------------------行表达式分片（INLINE）算法进行分表配置-------------------------------------------
##------------------------分库策略
## 分片列名称
#spring.shardingsphere.rules.sharding.tables.t_order.table-strategy.standard.sharding-column=user_id
## 分片算法名称
#spring.shardingsphere.rules.sharding.tables.t_order.table-strategy.standard.sharding-algorithm-name=alg_inline_userid
##------------------------分片算法配置  ## 行表达式分片算法
## 分片算法类型
#spring.shardingsphere.rules.sharding.sharding-algorithms.alg_inline_userid.type=INLINE
## 分片算法属性配置
#spring.shardingsphere.rules.sharding.sharding-algorithms.alg_inline_userid.props.algorithm-expression=t_order$->{user_id % 2}

#-----------------------------------HASH分片算法进行分表配置-------------------------------------------
# 标准分片表配置
# 分片列名称，多个列以逗号分隔
spring.shardingsphere.rules.sharding.tables.t_order.table-strategy.standard.sharding-column=order_no
# 分片算法名称
spring.shardingsphere.rules.sharding.tables.t_order.table-strategy.standard.sharding-algorithm-name=alg_hash
##------------------------分片算法配置  ## HASH_MOD算法
# 分片算法配置
spring.shardingsphere.rules.sharding.sharding-algorithms.alg_hash.type=HASH_MOD
# 分片算法属性配置
spring.shardingsphere.rules.sharding.sharding-algorithms.alg_hash.props.sharding-count=2

测试行表达式分片（INLINE）分片算法

OrderMapperTest-测试插入

import com.dk.shardingjdbcdemo.dto.OrderPo;
import org.junit.jupiter.api.Test;
import org.springframework.boot.test.context.SpringBootTest;

import javax.annotation.Resource;

@SpringBootTest
public class OrderMapperTest {
    @Resource
    private  OrderMapper orderMapper;

    @Test
    public void testInsertUser(){
        for (long i = 0; i < 4; i++) {
            orderMapper.insert(OrderPo.builder().userId(i).build());
        }
    }
}

插入结果

可以看出是已经根据userid进行分表插入了

测试查询

import org.junit.jupiter.api.Test;
import org.springframework.boot.test.context.SpringBootTest;

import javax.annotation.Resource;

@SpringBootTest
public class OrderMapperTest {
    @Resource
    private  OrderMapper orderMapper;

    @Test
    public void testInsertUser(){
        // for (long i = 0; i < 4; i++) {
        //     orderMapper.insert(OrderPo.builder().userId(i).build());
        // }
        for (int i = 0; i < 4; i++) {
            orderMapper.selectList(null);
        }
    }
}

测试查询结果

可以看出查询时，shardingsphere进行了union结果

测试HASH分片算法

插入测试类代码

for (long i = 0; i < 4; i++) {
    orderMapper.insert(OrderPo.builder().orderNo("orderNo00"+i).build());
}

测试插入结果

也根据order_no进行了hash取模运算

测试查询结果

和inline一样，都是把结果合并返回