使用ShardingSphere-Proxy实现分表分库
目录
- 1.环境准备
- 2.数据库脚本准备
- 3.配置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', '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
命令解压
- 有些 jar 包名称过长导致解压失败,运行时会缺包报错,如:
- 复制 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,...
到此这篇关于使用ShardingSphere-Proxy实现分表分库的文章就介绍到这了。希望对大家的学习有所帮助,也希望大家多多支持我们。
赞 (0)