shardingsphere-Proxy 初次使用
概述
shardingsphere-proxy 使用代理，什么意思呢，就是我只要发送给代理例如，
select * from t_order where id = 1;
的查询，而实际 shardingsphere-proxy 执行的分库分表中的 ：
select * from t_order_0 where id = 1; select * from t_order_1 where id = 1;
然后框架封装结果返回给用户。 分次测试环境 ： 数据库（MySQL）, 操作系统（Window）
下载启动
下载地址 ： https://mirror.bit.edu.cn/apache/shardingsphere/4.1.0/apache-shardingsphere-4.1.0-sharding-proxy-bin.tar.gz 下载完以后解压需要注意一下，window 环境不要用 7z 等解压工具（因为里面有些文件的文件名过长，解压软件会截断），window 环境下 cmd ,然后执行 ：
tar zxvf apache-shardingsphere-4.1.0-sharding-proxy-bin.tar.gz
然后修改 /config 中的两个文件，我的 config-sharding.yaml 文件修改如下 ：
#
# Licensed to the Apache Software Foundation (ASF) under one or more
# contributor license agreements. See the NOTICE file distributed with
# this work for additional information regarding copyright ownership.
# The ASF licenses this file to You under the Apache License, Version 2.0
# (the "License"); you may not use this file except in compliance with
# the License. You may obtain a copy of the License at
#
# http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.
#
######################################################################################################
#
# Here you can configure the rules for the proxy.
# This example is configuration of sharding rule.
#
# If you want to use sharding, please refer to this file;
# if you want to use master-slave, please refer to the config-master_slave.yaml.
#
######################################################################################################
#
#schemaName: sharding_db
#
#dataSources:
# ds_0:
# url: jdbc:postgresql://127.0.0.1:5432/demo_ds_0?serverTimezone=UTC&useSSL=false
# username: postgres
# password: postgres
# connectionTimeoutMilliseconds: 30000
# idleTimeoutMilliseconds: 60000
# maxLifetimeMilliseconds: 1800000
# maxPoolSize: 50
# ds_1:
# url: jdbc:postgresql://127.0.0.1:5432/demo_ds_1?serverTimezone=UTC&useSSL=false
# username: postgres
# password: postgres
# connectionTimeoutMilliseconds: 30000
# idleTimeoutMilliseconds: 60000
# maxLifetimeMilliseconds: 1800000
# maxPoolSize: 50
#
#shardingRule:
# tables:
# t_order:
# actualDataNodes: ds_${0..1}.t_order_${0..1}
# tableStrategy:
# inline:
# shardingColumn: order_id
# algorithmExpression: t_order_${order_id % 2}
# keyGenerator:
# type: SNOWFLAKE
# column: order_id
# t_order_item:
# actualDataNodes: ds_${0..1}.t_order_item_${0..1}
# tableStrategy:
# inline:
# shardingColumn: order_id
# algorithmExpression: t_order_item_${order_id % 2}
# keyGenerator:
# type: SNOWFLAKE
# column: order_item_id
# bindingTables:
# - t_order,t_order_item
# defaultDatabaseStrategy:
# inline:
# shardingColumn: user_id
# algorithmExpression: ds_${user_id % 2}
# defaultTableStrategy:
# none:
######################################################################################################
#
# If you want to connect to MySQL, you should manually copy MySQL driver to lib directory.
#
######################################################################################################
schemaName: sharding_db
# 分库分表的信息
dataSources:
ds_0:
url: jdbc:mysql://127.0.0.1:3306/demo_ds_0?serverTimezone=UTC&useSSL=false
username: root
password: 12345678
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
ds_1:
url: jdbc:mysql://127.0.0.1:3306/demo_ds_1?serverTimezone=UTC&useSSL=false
username: root
password: 12345678
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
# 分片规则
shardingRule:
tables:
t_order:
actualDataNodes: ds_${0..1}.t_order_${0..1}
tableStrategy:
inline:
shardingColumn: order_id
algorithmExpression: t_order_${order_id % 2}
keyGenerator:
type: SNOWFLAKE
column: order_id
t_order_item:
actualDataNodes: ds_${0..1}.t_order_item_${0..1}
tableStrategy:
inline:
shardingColumn: order_id
algorithmExpression: t_order_item_${order_id % 2}
keyGenerator:
type: SNOWFLAKE
column: order_item_id
bindingTables:
- t_order,t_order_item
defaultDatabaseStrategy:
inline:
shardingColumn: user_id
algorithmExpression: ds_${user_id % 2}
defaultTableStrategy:
none:
sever.yaml 文件 ：
#
# Licensed to the Apache Software Foundation (ASF) under one or more
# contributor license agreements. See the NOTICE file distributed with
# this work for additional information regarding copyright ownership.
# The ASF licenses this file to You under the Apache License, Version 2.0
# (the "License"); you may not use this file except in compliance with
# the License. You may obtain a copy of the License at
#
# http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.
#
######################################################################################################
#
# If you want to configure orchestration, authorization and proxy properties, please refer to this file.
#
######################################################################################################
#
#orchestration:
# orchestration_ds:
# orchestrationType: registry_center,config_center,distributed_lock_manager
# instanceType: zookeeper
# serverLists: localhost:2181
# namespace: orchestration
# props:
# overwrite: false
# retryIntervalMilliseconds: 500
# timeToLiveSeconds: 60
# maxRetries: 3
# operationTimeoutMilliseconds: 500
#
authentication:
users:
root:
password: root
sharding:
password: sharding
authorizedSchemas: sharding_db
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 语句
sql.show: true
# 该属性会允许范围查询，默认为 false ,要是我们分库分表是水平切分，可以想得到范围查询会像广播去查每一个表，比较耗性能能。
allow.range.query.with.inline.sharding: true
先在本地数据库建立两个分库，分别是 ： demo_ds_0 和 demo_ds_1 ，运行项目，有可能会发现缺少 mysql-connect-java 依赖,去maven 仓库找到jar 包扔到 lib 目录下，然后在 bin 目录启动 start.bat ,启动成功后，
然后打开本地数据库账号：root ,密码 ： root ,端口号 ： 3307 ，发现了里面有个 sharding_db，然后执行下面的建表语句 ：
CREATE TABLE `t_order` ( `order_id` bigint(20) NOT NULL AUTO_INCREMENT, `user_id` int(11) NOT NULL, `status` varchar(50) COLLATE utf8_bin DEFAULT NULL, PRIMARY KEY (`order_id`) ) ENGINE=InnoDB AUTO_INCREMENT=279205305122816001 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
然后再打开本地的 demo_ds_0 和 demo_ds_1 你会发现代理帮你创建好表了
然后再执行 ：
INSERT INTO `t_order` (`order_id`, `user_id`, `status`) VALUES (11, 0, '2'); INSERT INTO `t_order` (`order_id`, `user_id`, `status`) VALUES (12, 1, '2'); INSERT INTO `t_order` (`order_id`, `user_id`, `status`) VALUES (13, 0, '2');
你会发现对应的 demo_ds_0 和 demo_ds_1 数据库已经有分片好的数据！
参考资料
- https://www.cnblogs.com/yeyongjian/p/10107078.html
浙公网安备 33010602011771号