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

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

1. 环境准备

• MySql 5.7
• apache-shardingsphere-4.1.1-sharding-proxy-bin.tar.gz
• jdk 1.8
• mysql-connector-java-5.1.49.jar

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\', \'wp-content/uploads/2022/02/https://www.173top.cn/wp-content/uploads/2022/02/1645343437-20220220075037-6211f2cd28d62.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\', \'wp-content/uploads/2022/02/https://www.173top.cn/wp-content/uploads/2022/02/1645343437-20220220075037-6211f2cd28d62.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\', \'wp-content/uploads/2022/02/https://www.173top.cn/wp-content/uploads/2022/02/1645343437-20220220075037-6211f2cd28d62.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\', \'wp-content/uploads/2022/02/https://www.173top.cn/wp-content/uploads/2022/02/1645343437-20220220075037-6211f2cd28d62.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 连接中，新建 products 和 products-proxy数据库

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

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

• 打开 mysql 连接下的 products 数据库，就会发现 sekills_0 和 seckills_1 两张拆分的表

分表原理解析

• 根据什么原理来分表：表的字段值
• 如何根据字段值分表：
  • 取模运算（整数类型）
  • hash 运算：先对字符串进行 hash 得到一个值，然后根据 hash 值取模
  • 范围值：0 ~ 10000,10001 ~ 20000，...

来源：https://www.cnblogs.com/Run2948/p/ShardingSphere_Proxy_MySql.html
图文来源于网络，如有侵权请联系删除。