分库分表之sharding-proxy

一、环境介绍

• windows10
• mysql 版本8.0.16
• sharding-proxy版本5.0.0 ：https://archive.apache.org/dist/shardingsphere/5.0.0/apache-shardingsphere-5.0.0-shardingsphere-proxy-bin.tar.gz

二、安装

• 将shardingsphere解压，保证lib中的jar包文件名称完整，同时下载mysql-connection-java-8.0.16 jar包放到lib目录下，修改conf中的server.yaml 和 config-sharding.yaml

rules:
 - !AUTHORITY
   users:
     - [email protected]%:xrq123
     - [email protected]:sharding
   provider:
     type: ALL_PRIVILEGES_PERMITTED
 - !TRANSACTION
   defaultType: XA
   providerType: Atomikos

props:
 max-connections-size-per-query: 1
 kernel-executor-size: 16  # Infinite by default.
 proxy-frontend-flush-threshold: 128  # The default value is 128.
 proxy-opentracing-enabled: false
 proxy-hint-enabled: false
 sql-show: false
 check-table-metadata-enabled: false
 show-process-list-enabled: false
   # Proxy backend query fetch size. A larger value may increase the memory usage of ShardingSphere Proxy.
   # The default value is -1, which means set the minimum value for different JDBC drivers.
 proxy-backend-query-fetch-size: -1
 check-duplicate-table-enabled: false
 sql-comment-parse-enabled: false
 proxy-frontend-executor-size: 0 # Proxy frontend executor size. The default value is 0, which means let Netty decide.
   # Available options of proxy backend executor suitable: OLAP(default), OLTP. The OLTP option may reduce time cost of writing packets to client, but it may increase the latency of SQL execution
   # if client connections are more than proxy-frontend-netty-executor-size, especially executing slow SQL.
 proxy-backend-executor-suitable: OLAP
 proxy-frontend-max-connections: 0 # Less than or equal to 0 means no limitation.
 sql-federation-enabled: false

schemaName: sharding_db

dataSources:
ds_0:
url: jdbc:mysql://127.0.0.1:3306/demo_ds_0?serverTimezone=UTC&useSSL=false
username: root
password: xrq123
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
ds_1:
url: jdbc:mysql://127.0.0.1:3306/demo_ds_1?serverTimezone=UTC&useSSL=false
username: root
password: xrq123
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1

rules:
- !SHARDING
tables:
t_order:
actualDataNodes: ds_${0..1}.t_order_${0..1}
tableStrategy:
standard:
shardingColumn: order_id
shardingAlgorithmName: t_order_inline
keyGenerateStrategy:
column: order_id
keyGeneratorName: snowflake
t_order_item:
actualDataNodes: ds_${0..1}.t_order_item_${0..1}
tableStrategy:
standard:
shardingColumn: order_id
shardingAlgorithmName: t_order_item_inline
keyGenerateStrategy:
column: order_item_id
keyGeneratorName: snowflake
bindingTables:
- t_order,t_order_item
defaultDatabaseStrategy:
standard:
shardingColumn: user_id
shardingAlgorithmName: database_inline
defaultTableStrategy:
none:

shardingAlgorithms:
database_inline:
type: INLINE
props:
algorithm-expression: ds_${user_id % 2}
t_order_inline:
type: INLINE
props:
algorithm-expression: t_order_${order_id % 2}
t_order_item_inline:
type: INLINE
props:
algorithm-expression: t_order_item_${order_id % 2}

keyGenerators:
snowflake:
type: SNOWFLAKE
props:
       worker-id: 123

 这里使用的分片算法是行表达式分片算法  INLINE，分片键即主键。如果分片键是非主键，取模的话，可以用MOD分片算法，增加属性值sharding-count

分片算法参考链接：

https://shardingsphere.apache.org/document/current/cn/user-manual/shardingsphere-jdbc/builtin-algorithm/sharding/#%E8%87%AA%E5%8A%A8%E5%88%86%E7%89%87%E7%AE%97%E6%B3%95

三、启动

• 新建demo_ds_0、demo_ds_1数据库
• 进入bin目录 ，cmd 执行start.bat 3316

• 新启动一个cmd执行命令

mysql -h 127.0.0.1 -P 3316 -uroot -pxrq123 -A

show schemas;
use sharding_db;

CREATE TABLE IF NOT EXISTS t_order (order_id BIGINT NOT NULL AUTO_INCREMENT, user_id INT NOT NULL, status VARCHAR(50), PRIMARY KEY (order_id));

insert into t_order(user_id,status) values(1,'ok'),(2,'fail'),(3,'hello world'),(4,'2022-04-21'),(5,'Hangzhou king of volume'),(6,'In learning sub - library sub - table');

mysql> select * from t_order;
+--------------------+---------+---------------------------------------+
| order_id           | user_id | status                                |
+--------------------+---------+---------------------------------------+
| 742032272861409280 |       1 | ok                                    |
| 742032272861409281 |       2 | fail                                  |
| 742032272861409282 |       3 | hello world                           |
| 742032272861409283 |       4 | 2022-04-21                            |
| 742032272861409284 |       5 | Hangzhou king of volume               |
| 742032272861409285 |       6 | In learning sub - library sub - table |
+--------------------+---------+---------------------------------------+

• 在两个数据库中分别可以看见t_order_0、t_order_1表，数据如下：

参考链接： https://shardingsphere.apache.org/document/current/cn/user-manual/shardingsphere-jdbc/builtin-algorithm/

如果对大家有帮助的话，点赞支持哈，欢迎批评指正，相互交流！