Sharding-jdbc学习记录

一起屠龙
385 阅读2分钟

分库分表用法

技术栈：

springboot、mybatis-plus、sharding-jdbc

官网文档地址： shardingsphere.apache.org/document/le…

SpringBoot整合sharding-jdbc

<!--sharding-jdbc-->
<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
    <version>4.0.0-RC1</version>
</dependency>
<!-- Druid连接池 -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.2.10</version>
</dependency>
<!-- Mysql驱动依赖 -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.0.5</version>
</dependency>

数据库相关SQl

goods_n 水平切分表 t_config 广播表

CREATE TABLE `goods_0` (
  `gid` bigint(20) unsigned NOT NULL,
  `gname` varchar(50) DEFAULT NULL COMMENT '商品名称',
  `user_id` bigint(20) DEFAULT NULL COMMENT '用户ID',
  `gstatus` varchar(50) DEFAULT NULL COMMENT '商品状态 已发布 or 未发布',
  PRIMARY KEY (`gid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4；


CREATE TABLE `t_config` (
  `cid` bigint(20) unsigned NOT NULL,
  `c_key` varchar(50) DEFAULT NULL,
  `c_val` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`cid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4；

单库分表配置（）

# 配置Sharding-JDBC的分片策略
# 配置数据源，给数据源起名g1,g2...此处可配置多数据源
spring.shardingsphere.datasource.names=g1
#
# 配置允许后面的Bean覆盖前面名称重复的Bean
spring.main.allow-bean-definition-overriding=true
#
# 配置数据源具体内容————————包含  连接池，驱动，地址，用户名，密码
# 由于上面配置数据源只有g1因此下面只配置g1.type,g1.driver-class-name,g1.url,g1.username,g1.password
spring.shardingsphere.datasource.g1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.g1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.g1.url=jdbc:mysql://localhost:3306/goods_db?characterEncoding=utf-8&useUnicode=true&useSSL=false&serverTimezone=UTC
spring.shardingsphere.datasource.g1.username=root
spring.shardingsphere.datasource.g1.password=tiger
#
# 配置表的分布，表的策略
spring.shardingsphere.sharding.tables.goods.actual-data-nodes=g1.goods_$->{0..2}
#
# 指定goods表 主键gid 生成策略为 SNOWFLAKE(雪花ID)
spring.shardingsphere.sharding.tables.goods.key-generator.column=gid
spring.shardingsphere.sharding.tables.goods.key-generator.type=SNOWFLAKE
#
# 指定分片策略 约定gid值是偶数添加到goods_1表，如果gid是奇数添加到goods_2表
spring.shardingsphere.sharding.tables.goods.table-strategy.inline.sharding-column=gid
#水平分表的表达式
spring.shardingsphere.sharding.tables.goods.table-strategy.inline.algorithm-expression=goods_$->{gid % 3}
#
# 最大容忍的时钟回拨毫秒数
spring.shardingsphere.sharding.tables.goods.key-generator.max.tolerate.time.difference.milliseconds=5
#
# 打开sql输出日志
spring.shardingsphere.props.sql.show=true

分库分表配置（含广播表）

# 配置Sharding-JDBC的分片策略
# 配置数据源
spring.shardingsphere.datasource.names=ds0,ds1
#
# 配置允许后面的Bean覆盖前面名称重复的Bean
spring.main.allow-bean-definition-overriding=true
#
#### 数据源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/goods_db_0?characterEncoding=utf-8&useUnicode=true&useSSL=false&serverTimezone=UTC
spring.shardingsphere.datasource.ds0.username=root
spring.shardingsphere.datasource.ds0.password=tiger
#### 数据源ds1配置
spring.shardingsphere.datasource.ds1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds1.url=jdbc:mysql://localhost:3306/goods_db_1?characterEncoding=utf-8&useUnicode=true&useSSL=false&serverTimezone=UTC
spring.shardingsphere.datasource.ds1.username=root
spring.shardingsphere.datasource.ds1.password=tiger
#
#### 分库策略
spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression=ds$->{user_id % 2}
#
### 分表策略
spring.shardingsphere.sharding.tables.goods.actual-data-nodes=ds$->{0..1}.goods_$->{0..1}
# 切分键
spring.shardingsphere.sharding.tables.goods.table-strategy.inline.sharding-column=gid
# 指定分片策略
spring.shardingsphere.sharding.tables.goods.table-strategy.inline.algorithm-expression=goods_$->{gid % 2}
# 指定goods表 主键gid 生成策略为 SNOWFLAKE(雪花ID)
spring.shardingsphere.sharding.tables.goods.key-generator.column=gid
spring.shardingsphere.sharding.tables.goods.key-generator.type=SNOWFLAKE
#
#
# 广播表, 其主节点是ds0
spring.shardingsphere.sharding.broadcast-tables=t_config
spring.shardingsphere.sharding.tables.t_config.actual-data-nodes=ds$->{0}.t_config
#
# 最大容忍的时钟回拨毫秒数
spring.shardingsphere.sharding.tables.goods.key-generator.max.tolerate.time.difference.milliseconds=5
#
# 打开sql输出日志
spring.shardingsphere.props.sql.show=true

测试

/**
 * 类描述
 *
 * @Author tulong
 **/
@RunWith(SpringRunner.class)
@SpringBootTest(classes = DbProviderApp.class, webEnvironment = SpringBootTest.WebEnvironment.RANDOM_PORT)
public class MainTest {

    @Resource
    private GoodsMapper goodsMapper;
    @Resource
    private TConfigMapper tconfigMapper;
    @Resource
    private Gson gson;

    /**
     *
     */
    @Test
    public void broadcastTables(){
        TConfig tconfig = new TConfig();
        tconfig.setCid(System.currentTimeMillis());
        tconfig.setCKey("key1");
        tconfig.setCVal("val1");
        System.out.println("广播表保存前："+gson.toJson(tconfig));
        int a = tconfigMapper.insert(tconfig);
        System.out.println("广播表保存结结果："+a);
    }

    /**
     * 查询数据(
     * in (切分键)：针对所属表进行查询，不会查询所有表；
     * in (切分键) and field = xx：针对所属表进行查询，不会查询所有表；
     * in (切分键) or field = xx：查询所有分表；
     */
    @Test
    public void selectIn() {
        QueryWrapper<Goods> query = new QueryWrapper<>();
        query.in("gid",736307568489529345L,736307559421444097L);
        query.or().eq("gname","商品名称ss");
        List<Goods> goods = goodsMapper.selectList(query);
        System.out.println("查询结果："+gson.toJson(goods));
    }
    /**
     * 查询数据(查询所有表)
     */
    @Test
    public void selectCon() {
        QueryWrapper<Goods> query = new QueryWrapper<>();
        query.eq("gname","商品名称AAA");
        List<Goods> goods = goodsMapper.selectList(query);
        System.out.println("查询结果："+gson.toJson(goods));
    }
    /**
     * 查询数据(根据分片建查询，能够精确查询所在表)
     */
    @Test
    public void selectById() {
        QueryWrapper<Goods> query = new QueryWrapper<>();
        query.eq("gid",1653489580075L);
        query.eq("user_id",2L);
        Goods goods = goodsMapper.selectOne(query);
        System.out.println("查询结果："+gson.toJson(goods));
    }

    /**
     * 保存数据
     */
    @Test
    public void insert() {
        Goods goods = new Goods();
        goods.setGid(System.currentTimeMillis());
        goods.setGname("菜瓜");
        goods.setGstatus("未发布");
        goods.setUserId(2L);

        System.out.println("before insert : "+gson.toJson(goods));
        int a = goodsMapper.insert(goods);
        System.out.println("after insert a="+a+",="+gson.toJson(goods));
    }
}

自定义ID生成

注意：如果切分键值不为null则，ID生成工具不生效；

自定义ID生成工具：

package com.gjw.deme.sharding.keygen;

import org.apache.shardingsphere.spi.keygen.ShardingKeyGenerator;

import java.util.Properties;

/**
 * 自定义生成ID
 */
public class MyKeyGenerator implements ShardingKeyGenerator {
    @Override
    public Comparable<?> generateKey() {
        long id = System.currentTimeMillis();
        System.out.println("自定义生成ID=" + id);
        return id;
    }

    @Override
    public String getType() {
        return "my_keygen";
    }

    @Override
    public Properties getProperties() {
        return null;
    }

    @Override
    public void setProperties(Properties properties) {

    }
}

SPI文件位置

META-INF\services\org.apache.shardingsphere.spi.keygen.ShardingKeyGenerator

配置内容：

com.gjw.deme.sharding.keygen.MyKeyGenerator

application-sharding.properties修改

spring.shardingsphere.sharding.tables.goods.key-generator.type=my_keygen
avatar
文章
阅读
粉丝