shardingsphere 实战_getcolumnnameandshardingvaluesmap

作者：空白诗007 | 2024-07-09 05:38:14

踩

getcolumnnameandshardingvaluesmap

前言

shardingsphere是一款分库分表的框架，可以简化我们写分库分表的逻辑

需求

存5000万订单数据数据
支持按订单ID，买家ID查询订单
可支持通用配置

技术设计

mysql单表存储超过2000W性能会明显下降，把数据拆分到4张表中，分别在2个库里。库的数量，是需要根据QPS来定，每个库的用户链接数是有上限的，如果QPS很高，需要增加分库数量
订单号的设计，最后几位采用买家ID，按买家ID分库分表，这样能同时支持按订单ID和卖家ID查询
每个库都放一个global_config表，作为字典表，存放相同数据

表结构设计


create database oms1;
 
create database oms2;
 
CREATE TABLE torder_1 (
	order_id BIGINT(20) PRIMARY KEY,
	status varchar(20) NOT NULL,
	gmt_create datetime NOT NULL,
	sku_code VARCHAR(50) NOT NULL,
	sku_name VARCHAR(50) NOT NULL,
	price DECIMAL(10, 2) NOT NULL,
	buyer_id BIGINT(20) NOT NULL
);
 
CREATE TABLE torder_2 (
	order_id BIGINT(20) PRIMARY KEY,
	status varchar(20) NOT NULL,
	gmt_create datetime NOT NULL,
	sku_code VARCHAR(50) NOT NULL,
	sku_name VARCHAR(50) NOT NULL,
	price DECIMAL(10, 2) NOT NULL,
	buyer_id BIGINT(20) NOT NULL
);
 
CREATE TABLE global_config (
	id BIGINT(20) PRIMARY KEY,
	status varchar(20) NOT NULL,
	gmt_create datetime NOT NULL,
	config_key VARCHAR(50) NOT NULL,
	config_value VARCHAR(50) NOT NULL,
	config_desc VARCHAR(100) NOT NULL
);

二方包


<!-- shardingJDBC核心依赖 -->
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>4.1.1</version>
        </dependency>
        <!--XA 分布式事务 -->
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-transaction-xa-core</artifactId>
            <version>4.1.1</version>
        </dependency>
 
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
        </dependency>
        <!-- 数据源连接池 -->
        <!--注意不要用这个依赖，他会创建数据源，跟上面ShardingJDBC的SpringBoot集成依赖有冲突 -->
        <!--        <dependency>-->
        <!--            <groupId>com.alibaba</groupId>-->
        <!--            <artifactId>druid-spring-boot-starter</artifactId>-->
        <!--            <version>1.1.20</version>-->
        <!--        </dependency>-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.20</version>
        </dependency>
        <!-- mysql连接驱动 -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <!-- mybatisplus依赖 -->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
        </dependency>

实体


package com.wgp.oms.dal.entity;
 
import java.math.BigDecimal;
import java.util.Date;
 
import com.baomidou.mybatisplus.annotation.TableId;
import lombok.Data;
 
/**
 * @author gangpeng.wgp
 * @date 2023/6/27 下午9:58
 */
@Data
//特别注意，不能踩坑：表名不能叫order，是mysql关键字。否则shardingsphere会启动失败
public class Torder {
    @TableId
    private Long orderId;
    private String status;
    private Date gmtCreate;
    private String skuCode;
    private String skuName;
    private BigDecimal price;
    private Long buyerId;
 
}

数据源

在application.properties中定义2个数据源m1、m2，分别对应2个库oms1、oms2


#各种分库分表策略
#配置多个数据源
spring.shardingsphere.datasource.names=m1,m2
 
spring.shardingsphere.datasource.m1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m1.url=jdbc:mysql://localhost:3306/oms1?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m1.username=root
spring.shardingsphere.datasource.m1.password=12345678
 
spring.shardingsphere.datasource.m2.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m2.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m2.url=jdbc:mysql://localhost:3306/oms2?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m2.username=root
spring.shardingsphere.datasource.m2.password=12345678
 
#真实表分布，分库，分表
spring.shardingsphere.sharding.tables.torder.actual-data-nodes=m$->{1..2}.torder_$->{1..2}

分片策略

分片策略总共有4种：

1、inline分片策略：支持按单列分片
2、standard标准分片策略：支持按单列分片，SQL语句中有>，>=, <=，<，=，IN 和 BETWEEN AND 操作符
3、complex复杂分片策略：支持多key，SQL语句中有>，>=, <=，<，=，IN 和 BETWEEN AND 操作符4、hint强制路由策略：不按SQL来路由，由代码强制指定

inline分片策略

这里的分库分表表达式，就是把数据能均衡的离散到2个库4张表里


#inline分片策略，支持单单key, 
spring.shardingsphere.sharding.tables.torder.database-strategy.inline.sharding-column=order_id
spring.shardingsphere.sharding.tables.torder.database-strategy.inline.algorithm-expression=m$->{((order_id%4+1).intdiv(3))+1}
 
spring.shardingsphere.sharding.tables.torder.table-strategy.inline.sharding-column=order_id
spring.shardingsphere.sharding.tables.torder.table-strategy.inline.algorithm-expression=torder_$->{((order_id%4+1)%2)+1}

stardard分片策略


#standard标准分片策略, 支持单key，SQL 语句中有>，>=, <=，<，=，IN 和 BETWEEN AND 操作符
spring.shardingsphere.sharding.tables.torder.table-strategy.standard.sharding-column=order_id
spring.shardingsphere.sharding.tables.torder.table-strategy.standard.precise-algorithm-class-name=com.wgp.oms.dal.algorithem.MyPreciseTableShardingAlgorithm
spring.shardingsphere.sharding.tables.torder.table-strategy.standard.range-algorithm-class-name=com.wgp.oms.dal.algorithem.MyRangeTableShardingAlgorithm
 
spring.shardingsphere.sharding.tables.torder.database-strategy.standard.sharding-column=order_id
spring.shardingsphere.sharding.tables.torder.database-strategy.standard.precise-algorithm-class-name=com.wgp.oms.dal.algorithem.MyPreciseDBShardingAlgorithm
spring.shardingsphere.sharding.tables.torder.database-strategy.standard.range-algorithm-class-name=com.wgp.oms.dal.algorithem.MyRangeDBShardingAlgorithm


package com.wgp.oms.dal.algorithem;
 
import java.util.Collection;
 
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;
 
/**
 * @author : gangpeng.wgp
 * @date : 2023/6/28
 */
public class MyPreciseDBShardingAlgorithm implements PreciseShardingAlgorithm<Long> {
 
    @Override
    public String doSharding(Collection<String> databaseNames, PreciseShardingValue<Long> shardingValue) {
 
        for (String databaseName : databaseNames) {
            //m$->{((order_id%4+1).intdiv(3))+1}
            String value = "m" + (((shardingValue.getValue() % 4 + 1)/3) +1);
            if (databaseName.equalsIgnoreCase(value)) {
                return databaseName;
            }
        }
        throw new IllegalArgumentException();
    }
}
 
 
package com.wgp.oms.dal.algorithem;
 
import java.util.Collection;
 
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;
 
/**
 * @author : gangpeng.wgp
 * @date : 2023/6/28
 */
public class MyPreciseTableShardingAlgorithm implements PreciseShardingAlgorithm<Long> {
 
    @Override
    public String doSharding(Collection<String> tableNames, PreciseShardingValue<Long> shardingValue) {
 
        for (String tableName : tableNames) {
            //torder_$->{((order_id%4+1)%2)+1}
            String value = "torder_" + (((shardingValue.getValue() % 4 + 1) % 2) + 1);
            if (tableName.equalsIgnoreCase(value)) {
                return tableName;
            }
        }
        throw new IllegalArgumentException();
    }
}

complex分片策略


spring.shardingsphere.sharding.tables.torder.table-strategy.complex.sharding-columns= order_id, buyer_id
spring.shardingsphere.sharding.tables.torder.table-strategy.complex.algorithm-class-name=com.wgp.oms.dal.algorithem.MyComplexTableShardingAlgorithm
 
spring.shardingsphere.sharding.tables.torder.database-strategy.complex.sharding-columns=order_id, buyer_id
spring.shardingsphere.sharding.tables.torder.database-strategy.complex.algorithm-class-name=com.wgp.oms.dal.algorithem.MyComplexDSShardingAlgorithm


package com.wgp.oms.dal.algorithem;
 
import java.util.Collection;
 
import org.apache.shardingsphere.api.sharding.complex.ComplexKeysShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.complex.ComplexKeysShardingValue;
 
public class MyComplexDSShardingAlgorithm implements ComplexKeysShardingAlgorithm<Long> {
 
    /**
     * @param availableTargetNames 目标数据源 或者 表 的值。
     * @param shardingValue        logicTableName逻辑表名 columnNameAndShardingValuesMap 分片列的精确值集合。 columnNameAndRangeValuesMap 分片列的范围值集合
     * @return
     */
    @Override
    public Collection<String> doSharding(Collection<String> availableTargetNames, ComplexKeysShardingValue<Long> shardingValue) {
        return availableTargetNames;
    }
}
 
 
package com.wgp.oms.dal.algorithem;
 
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
 
import org.apache.commons.collections4.CollectionUtils;
import org.apache.shardingsphere.api.sharding.complex.ComplexKeysShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.complex.ComplexKeysShardingValue;
 
/**
 * 复合分片
 * 使用场景：需要按多个字段进行分表，比如：既可以按order_id分表，也可以按buyer_id分表
 * @author : gangpeng.wgp
 * @date : 2023/6/28
 */
public class MyComplexTableShardingAlgorithm implements ComplexKeysShardingAlgorithm<Long> {
 
    @Override
    public Collection<String> doSharding(Collection<String> availableTargetNames, ComplexKeysShardingValue<Long> shardingValue) {
 
        Collection<Long> orderIdList = shardingValue.getColumnNameAndShardingValuesMap().get("order_id");
        Collection<Long> buyerIdList = shardingValue.getColumnNameAndShardingValuesMap().get("buyer_id");
 
        List<String> result = new ArrayList<>();
 
        if(CollectionUtils.isNotEmpty(orderIdList)){
            //实现自定义分片逻辑
            for (Long id : orderIdList) {
                //torder_$->{((order_id%4+1)%2)+1}
                String value = "torder_" + (((id % 4 + 1) % 2) + 1);
                result.add(value);
            }
        }
 
        if(CollectionUtils.isNotEmpty(buyerIdList)){
            //实现自定义分片逻辑
            for (Long id : buyerIdList) {
                //torder_$->{((buyer_id%4+1)%2)+1}
                String value = "torder_" + (((id % 4 + 1) % 2) + 1);
                if(!result.contains(value)){
                    result.add(value);
                }
            }
        }
 
        return result;
    }
}

hint分片策略

spring.shardingsphere.sharding.tables.torder.database-strategy.hint.algorithm-class-name=com.wgp.oms.dal.algorithem.MyHintTableShardingAlgorithm


package com.wgp.oms.dal.algorithem;
 
import java.util.ArrayList;
import java.util.Collection;
 
import org.apache.shardingsphere.api.sharding.hint.HintShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.hint.HintShardingValue;
 
 
public class MyHintTableShardingAlgorithm implements HintShardingAlgorithm<Long> {
    @Override
    public Collection<String> doSharding(
        Collection<String> availableTargetNames,
        HintShardingValue<Long> shardingValue) {
        // 添加分库或分表路由逻辑
        Collection<String> result = new ArrayList<>();
        for (String each : availableTargetNames) { //代表：分片目标，对哪些数据库、表分片。如果是对分库路由，表示ds0，ds1；
            for (Long value : shardingValue.getValues()) { // 代表：分片值; 可以HintManager设置多个分片值，所以是个集合。
                if (each.endsWith(String.valueOf(value  ))) { // 分库路由
                    result.add(each );
                }
            }
        }
        return result;
    }
}


/**
     * 强制指定库路由查询
     *
     * 注意：需在application.properties中开启hint查询策略，且注释掉其他查询策略
     */
    @Test
    public void queryOrderListWithHit() {
        //注意：入参的类型需要和MyHintTableShardingAlgorithm中的泛型类型一致，否则会报错
        HintManager.getInstance().setDatabaseShardingValue(1L);
        List<Torder> orderList = orderMapper.queryAllOrder();
        assertNotNull(orderList);
        orderList.forEach(System.out::println);
        System.out.println("结果数：" + orderList.size());
    }

单测


package com.wgp.oms.service;
 
import java.math.BigDecimal;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
 
import javax.annotation.Resource;
 
import com.wgp.oms.dal.entity.Torder;
import com.wgp.oms.dal.mapper.OrderMapper;
import org.apache.shardingsphere.api.hint.HintManager;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
 
import static org.junit.jupiter.api.Assertions.assertEquals;
import static org.junit.jupiter.api.Assertions.assertNotNull;
 
/**
 * @author gangpeng.wgp
 * @date 2023/6/27 下午10:40
 */
@RunWith(SpringRunner.class)
@SpringBootTest
public class TorderServiceTest {
    @Resource
    private OrderService orderService;
 
    @Resource
    private OrderMapper orderMapper;
 
    @Test
    public void queryOrderById() {
 
        Torder order = orderService.queryOrderById(1L);
        assertNotNull(order);
        System.out.println(order);
    }
 
    @Test
    public void queryOrderList() {
 
        List<Torder> orderList = orderMapper.queryAllOrder();
        assertNotNull(orderList);
        orderList.forEach(System.out::println);
    }
 
    @Test
    public void queryByOrderIdRange() {
 
        List<Torder> orderList = orderService.queryByOrderIdRange(1L, 2L);
        assertNotNull(orderList);
        orderList.forEach(System.out::println);
    }
 
    @Test
    public void queryByIds() {
 
        List<Torder> orderList = orderMapper.queryByIds(Arrays.asList(1L, 5L));
        assertNotNull(orderList);
        orderList.forEach(System.out::println);
    }
 
    /**
     * 配合Complex策略
     */
    @Test
    public void queryByBuyerIds() {
        List<Torder> orderList = orderMapper.queryByBuyerIds(Arrays.asList(1L, 5L));
        assertNotNull(orderList);
        orderList.forEach(System.out::println);
    }
 
    /**
     * 强制指定库路由查询
     *
     * 注意：需在application.properties中开启hint查询策略，且注释掉其他查询策略
     */
    @Test
    public void queryOrderListWithHit() {
        //注意：入参的类型需要和MyHintTableShardingAlgorithm中的泛型类型一致，否则会报错
        HintManager.getInstance().setDatabaseShardingValue(1L);
        List<Torder> orderList = orderMapper.queryAllOrder();
        assertNotNull(orderList);
        orderList.forEach(System.out::println);
        System.out.println("结果数：" + orderList.size());
    }
 
    //插入数据会进行分片
    @Test
    public void addOrder() {
        delete();
 
        for (int i = 1; i < 100; i++) {
            Torder c = new Torder();
            c.setBuyerId((long)i);
            c.setGmtCreate(new Date());
            c.setStatus("INIT");
            c.setPrice(BigDecimal.valueOf(1000.10));
            c.setSkuCode("sku_" + i);
            c.setSkuName("商品_" + i);
            c.setOrderId((long)i);
            orderMapper.insert(c);
        }
    }
 
    private void delete() {
        Map<String, Object> param = new HashMap<>();
        //param.putIfAbsent("cname", "java");
        orderMapper.deleteByMap(param);
    }
 
    @Test
    public void update() {
        Torder o = new Torder();
        o.setOrderId(1L);
        o.setPrice(BigDecimal.valueOf(9999.00));
        int cnt = orderMapper.updateById(o);
        assertEquals(cnt, 1);
 
        Torder order = orderService.queryOrderById(1L);
        assertNotNull(order);
        assertEquals(9999.00D, order.getPrice().doubleValue());
 
    }
}

字典表


#广播表配置（字典表，每个库都有一个，表名一样，数据一样）
spring.shardingsphere.sharding.broadcast-tables=global_config
spring.shardingsphere.sharding.tables.global_config.key-generator.column=id
spring.shardingsphere.sharding.tables.global_config.key-generator.type=SNOWFLAKE


package com.wgp.oms.dal.entity;
 
import java.util.Date;
 
import com.baomidou.mybatisplus.annotation.TableId;
import lombok.Data;
 
/**
 * 全局字典表
 * @author gangpeng.wgp
 * @date 2023/6/27 下午9:58
 */
@Data
public class GlobalConfig {
    @TableId
    private Long id;
    private String status;
    private String configKey;
    private String configValue;
    private String configDesc;
    private Date gmtCreate;
 
}

单测


package com.wgp.oms.service;
 
import java.util.Date;
import java.util.HashMap;
import java.util.Map;
 
import javax.annotation.Resource;
 
import com.wgp.oms.dal.entity.GlobalConfig;
import com.wgp.oms.dal.mapper.GlobalConfigMapper;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
 
import static org.junit.jupiter.api.Assertions.assertNotNull;
 
/**
 * 注意：这个测试类，不能放到com.wgp.oms.dal.mapper包下，否则会被当做mybatis的mapper
 *
 * @author gangpeng.wgp
 * @date 2023/6/28 3:11 下午
 */
@RunWith(SpringRunner.class)
@SpringBootTest
public class GlobalConfigTest {
 
    @Resource
    private GlobalConfigMapper globalConfigMapper;
 
    @Test
    public void selectById() {
 
        GlobalConfig order = globalConfigMapper.selectById(1L);
        assertNotNull(order);
        System.out.println(order);
    }
 
    //插入数据会进行分片
    @Test
    public void addOrder() {
        delete();
 
        for (int i = 1; i < 100; i++) {
            GlobalConfig c = new GlobalConfig();
            c.setStatus("INIT");
            c.setConfigKey("conf_" + i);
            c.setConfigValue("value_" + i);
            c.setConfigDesc("这是配置" + i);
            c.setGmtCreate(new Date());
            globalConfigMapper.insert(c);
        }
    }
 
    private void delete() {
        Map<String, Object> param = new HashMap<>();
        //param.putIfAbsent("cname", "java");
        globalConfigMapper.deleteByMap(param);
    }
}

注意事项

表名一定不要和mysql的关键字冲突！比如：订单表，开始起名为order，后来应用一直报错，浪费了半天时间
shardingsphere没有禁止全表扫描的功能，如果没有命中分片规则，可能会全表扫描，这在生产环境是致命的
对于使用自增主键的表，需要注意在分片键上不能使用该字段。因为自增主键在分布式系统下无法保证唯一性。

代码地址

GitHub - gangpwei/wgp-oms

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/空白诗007/article/detail/801595