使用ShardingSphere

使用ShardingSphere

目录1.环境准备2.数据库脚本准备3.配置ShardingSphere-Proxy分表原理解析

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

1. 环境准备

mysql 5.7apache-shardingsphere-4.1.1-sharding-proxy-bin.tar.gzjdk 1.8mysql-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`, `SeckillDeschttp://ription`, `SeckillIstop`, `SeckillStatus`) VALUES (1, 1, '22', 'https://img2020-blogs.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-blogs.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`, `Sechttp://killPercent`, `TimeId`, `ProductId`, `SeckillLimit`, `SeckillDescription`, `SeckillIstop`, `SeckillStatus`) VALUES (3, 1, '22', 'https://img2020-blogs.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-blogs.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.RxGGOOnhN 设置分片规则 - 分表

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，...

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。