如何通过 ShardingSphere-Proxy 落地分表分库?

参考:Sharding-Proxy的基本功能使用

1. 环境准备

2. 数据库脚本准备

# 创建商品数据库
CREATE DATABASE IF NOT EXISTS `products` DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_general_ci;
# 创建商品代理数据库
CREATE DATABASE IF NOT EXISTS `products-proxy` DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_general_ci;

# 创建商品秒杀表
CREATE TABLE IF NOT EXISTS `seckills` (
	`Id` INT(11) NOT NULL AUTO_INCREMENT,
	`SeckillType` INT(11) NOT NULL,
	`SeckillName` TEXT NULL,
	`SeckillUrl` TEXT NULL,
	`SeckillPrice` DECIMAL(18, 2) NOT NULL,
	`SeckillStock` INT(11) NOT NULL,
	`SeckillPercent` TEXT NULL,
	`TimeId` INT(11) NOT NULL,
	`ProductId` INT(11) NOT NULL,
	`SeckillLimit` INT(11) NOT NULL,
	`SeckillDescription` TEXT NULL,
	`SeckillIstop` INT(11) NOT NULL,
	`SeckillStatus` INT(11) NOT NULL,
PRIMARY KEY (`Id`),
INDEX `ProductId` (`ProductId`)
) COLLATE = 'utf8mb4_general_ci' ENGINE = INNODB AUTO_INCREMENT = 2;

# 插入秒杀商品数据
INSERT INTO `seckills`(`Id`, `SeckillType`, `SeckillName`, `SeckillUrl`, `SeckillPrice`, `SeckillStock`, `SeckillPercent`, `TimeId`, `ProductId`, `SeckillLimit`, `SeckillDescription`, `SeckillIstop`, `SeckillStatus`) VALUES (1, 1, '22', 'https://img2020.cnblogs.com/blog/1191201/202007/1191201-20200720143227139-1714696954.png', 12.00, 2222, '1', 3, 1, 1, 'iphone6是最好的', 1, 1);

INSERT INTO `seckills`(`Id`, `SeckillType`, `SeckillName`, `SeckillUrl`, `SeckillPrice`, `SeckillStock`, `SeckillPercent`, `TimeId`, `ProductId`, `SeckillLimit`, `SeckillDescription`, `SeckillIstop`, `SeckillStatus`) VALUES (2, 1, '22', 'https://img2020.cnblogs.com/blog/1191201/202007/1191201-20200720143227139-1714696954.png', 12.00, 2222, '1', 3, 2, 1, 'iphone6是最好的', 1, 1);

INSERT INTO `seckills`(`Id`, `SeckillType`, `SeckillName`, `SeckillUrl`, `SeckillPrice`, `SeckillStock`, `SeckillPercent`, `TimeId`, `ProductId`, `SeckillLimit`, `SeckillDescription`, `SeckillIstop`, `SeckillStatus`) VALUES (3, 1, '22', 'https://img2020.cnblogs.com/blog/1191201/202007/1191201-20200720143227139-1714696954.png', 12.00, 2222, '1', 3, 3, 1, 'iphone6是最好的', 1, 1);

INSERT INTO `seckills`(`Id`, `SeckillType`, `SeckillName`, `SeckillUrl`, `SeckillPrice`, `SeckillStock`, `SeckillPercent`, `TimeId`, `ProductId`, `SeckillLimit`, `SeckillDescription`, `SeckillIstop`, `SeckillStatus`) VALUES (4, 1, '22', 'https://img2020.cnblogs.com/blog/1191201/202007/1191201-20200720143227139-1714696954.png', 12.00, 2222, '1', 3, 4, 1, 'iphone6是最好的', 1, 1);

3. 配置 ShardingSphere-Proxy

  • 解压 ShardingSphere 到 apache-shardingsphere-4.1.1-sharding-proxy-bin 文件夹

    • 有些 jar 包名称过长导致解压失败,运行时会缺包报错,如:

      Starting the Sharding-Proxy ...
      Exception in thread "main" Cannot create property=orchestration for JavaBean=org.apache.shardingsphere.shardingproxy.config.yaml.YamlProxyServerConfiguration@1517365b
       in 'reader', line 24, column 1:
          orchestration:
          ^
      Type org.apache.shardingsphere.orchestration.center.yaml.config.YamlCenterRepositoryConfiguration not present
       in 'reader', line 25, column 3:
            orchestration_ds:
      
    • 推荐到 linux 系统下通过 tar -zxvf apache-shardingsphere-4.1.1-sharding-proxy-bin.tar.gz 命令解压

  • 复制 mysql-connector-java-5.1.49.jar 到 ShardingSphere 的 bin 目录中

  • 修改 conf 目录下的 config-sharding.yaml 配置文件:

    # 3. 创建客户端连接库
    schemaName: products-proxy
    
    # 1. 设置 MySQL 数据源
    dataSources:
      ds:
        url: jdbc:mysql://127.0.0.1:3306/products?serverTimezone=UTC&useSSL=false
        username: root
        password: 1010
        connectionTimeoutMilliseconds: 30000
        idleTimeoutMilliseconds: 60000
        maxLifetimeMilliseconds: 1800000
        maxPoolSize: 50
    
    # 2. 设置分片规则 - 分表
    shardingRule:
      tables:
        seckills: # 逻辑表名
          actualDataNodes: ds.seckills_${0..1} # 分 2 张表
          tableStrategy: # 分表策略
            inline:
              shardingColumn: ProductId # 分表字段
              algorithmExpression: seckills_${ProductId % 2} # 对 ProductId 取模分表
    
  • 修改 conf 目录下的 server.yaml 配置文件:

    authentication:
      users:
        root:
          password: 123456
        sharding:
          password: sharding 
          authorizedSchemas: products-proxy
    
    props:
      max.connections.size.per.query: 1
      acceptor.size: 16  # The default value is available processors count * 2.
      executor.size: 16  # Infinite by default.
      proxy.frontend.flush.threshold: 128  # The default value is 128.
        # LOCAL: Proxy will run with LOCAL transaction.
        # XA: Proxy will run with XA transaction.
        # BASE: Proxy will run with B.A.S.E transaction.
      proxy.transaction.type: LOCAL
      proxy.opentracing.enabled: false
      proxy.hint.enabled: false
      query.with.cipher.column: true
      sql.show: false
      allow.range.query.with.inline.sharding: false
    
  • 启动 ShardingSphere-Proxy

    D:\Program\Java\apache-shardingsphere-4.1.1-sharding-proxy-bin\bin>start.bat
    
    # 通过启动日志查看代理数据库的默认端口是 3307
    
    # 新建 mysql 和  mysql-proxy 两个连接备用
    
  • 在 mysql 连接中,新建 productsproducts-proxy数据库

  • 刷新 mysql-proxy 连接,就会看到数据库已经同步过来

  • 打开 mysql-proxy 连接下的 products-proxy 数据库,执行创建 seckills 表的语句

  • 打开 mysql 连接下的 products 数据库,就会发现 sekills_0seckills_1 两张拆分的表

分表原理解析

  • 根据什么原理来分表:表的字段值
  • 如何根据字段值分表:
    • 取模运算(整数类型)
    • hash 运算:先对字符串进行 hash 得到一个值,然后根据 hash 值取模
    • 范围值:0 ~ 10000,10001 ~ 20000,...
posted @ 2021-04-09 23:57  Run2948  阅读(134)  评论(0编辑  收藏  举报