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

1. 環(huán)境準(zhǔn)備

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

2. 數(shù)據(jù)庫(kù)腳本準(zhǔn)備

# 創(chuàng)建商品數(shù)據(jù)庫(kù)
CREATE DATABASE IF NOT EXISTS `products` DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_general_ci;
# 創(chuàng)建商品代理數(shù)據(jù)庫(kù)
CREATE DATABASE IF NOT EXISTS `products-proxy` DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_general_ci;

# 創(chuàng)建商品秒殺表
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;

# 插入秒殺商品數(shù)據(jù)
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 包名稱過長(zhǎng)導(dǎo)致解壓失敗，運(yùn)行時(shí)會(huì)缺包報(bào)錯(cuò)，如：

    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 系統(tǒng)下通過 tar -zxvf apache-shardingsphere-4.1.1-sharding-proxy-bin.tar.gz 命令解壓

• 復(fù)制 mysql-connector-java-5.1.49.jar 到 ShardingSphere 的 bin 目錄中

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

  # 3. 創(chuàng)建客戶端連接庫(kù)
  schemaName: products-proxy
  
  # 1. 設(shè)置 MySQL 數(shù)據(jù)源
  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. 設(shè)置分片規(guī)則 - 分表
  shardingRule:
    tables:
      seckills: # 邏輯表名
        actualDataNodes: ds.seckills_${0..1} # 分 2 張表
        tableStrategy: # 分表策略
          inline:
            shardingColumn: ProductId # 分表字段
            algorithmExpression: seckills_${ProductId % 2} # 對(duì) 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

• 啟動(dòng) ShardingSphere-Proxy

  D:\Program\Java\apache-shardingsphere-4.1.1-sharding-proxy-bin\bin>start.bat
  
  # 通過啟動(dòng)日志查看代理數(shù)據(jù)庫(kù)的默認(rèn)端口是 3307
  
  # 新建 mysql 和  mysql-proxy 兩個(gè)連接備用

• 在 mysql 連接中，新建 products 和 products-proxy數(shù)據(jù)庫(kù)

• 刷新 mysql-proxy 連接，就會(huì)看到數(shù)據(jù)庫(kù)已經(jīng)同步過來

• 打開 mysql-proxy 連接下的 products-proxy 數(shù)據(jù)庫(kù)，執(zhí)行創(chuàng)建 seckills 表的語(yǔ)句

• 打開 mysql 連接下的 products 數(shù)據(jù)庫(kù)，就會(huì)發(fā)現(xiàn) sekills_0 和 seckills_1 兩張拆分的表

分表原理解析

• 根據(jù)什么原理來分表：表的字段值
• 如何根據(jù)字段值分表：
  • 取模運(yùn)算（整數(shù)類型）
  • hash 運(yùn)算：先對(duì)字符串進(jìn)行 hash 得到一個(gè)值，然后根據(jù) hash 值取模
  • 范圍值：0 ~ 10000,10001 ~ 20000，...

到此這篇關(guān)于使用ShardingSphere-Proxy實(shí)現(xiàn)分表分庫(kù)的文章就介紹到這了。希望對(duì)大家的學(xué)習(xí)有所幫助，也希望大家多多支持腳本之家。

最新評(píng)論