ShardingSphereProxy:快速入门

使用 Docker 运行 ShardingSphere

在基于 Docker 安装 ShardingSphere 时，按照官方文档《使用 Docker :: ShardingSphere》所提供的步骤操作即可。

在运行ShardingSphereProxy之前，我们需要基于我们的测试场景修改配置文件，我测试场景中主要用了三张表，一个订单表，订单表基于user_id进行分表，还有一个user表和product表，这两张表未进行分表。基于这个场景，我们只需要修改两个配置文件，这两个配置文件分别是 global.yaml 和 database-sharding.yaml。

配置文件示例

global.yaml的配置

mode:type: Standalone # 单机模式repository:type: H2props:jdbcUrl: jdbc:h2:file:~/config_data # 元数据持久化数据库连接 URLauthority:users:- user: rootpassword: root- user: shardingpassword: shardingprivilege:type: ALL_PERMITTEDtransaction:defaultType: XAproviderType: AtomikossqlParser:sqlStatementCache:initialCapacity: 2000maximumSize: 65535parseTreeCache:initialCapacity: 128maximumSize: 1024logging:loggers:- loggerName: ShardingSphere-SQLadditivity: truelevel: INFOprops:enable: falsesqlFederation:sqlFederationEnabled: falseexecutionPlanCache:initialCapacity: 2000maximumSize: 65535props:system-log-level: INFOmax-connections-size-per-query: 1kernel-executor-size: 16  # Infinite by default.proxy-frontend-flush-threshold: 128  # The default value is 128.# sql-show is the same as props in logger ShardingSphere-SQL, and its priority is lower than logging rulesql-show: falsecheck-table-metadata-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: -1proxy-frontend-executor-size: 0 # Proxy frontend executor size. The default value is 0, which means let Netty decide.proxy-frontend-max-connections: 0 # Less than or equal to 0 means no limitation.proxy-default-port: 3307 # Proxy default port.proxy-netty-backlog: 1024 # Proxy netty backlog.cdc-server-port: 33071 # CDC server portproxy-frontend-ssl-enabled: falseproxy-frontend-ssl-cipher: ''proxy-frontend-ssl-version: TLSv1.2,TLSv1.3

database-sharding.yaml的配置

databaseName: shardingdataSources:ds:url: jdbc:mysql://10.10.37.108:13306/sharding?charset=utf8mb4&parseTime=True&loc=Localusername: {{你的用户名字}}password: {{你的密码}}connectionTimeoutMilliseconds: 30000idleTimeoutMilliseconds: 60000maxLifetimeMilliseconds: 1800000maxPoolSize: 50minPoolSize: 1rules:
- !SHARDINGtables:orders:actualDataNodes: ds.orders_${0..3}tableStrategy:standard:shardingColumn: user_idshardingAlgorithmName: orders_inlinekeyGenerateStrategy:column: user_idkeyGeneratorName: snowflakebindingTables:- ordersshardingAlgorithms:orders_inline:type: INLINEprops:algorithm-expression: orders_${user_id % 4}allow-range-query-with-inline-sharding: truekeyGenerators:snowflake:type: SNOWFLAKE

运行 Docker 容器

使用以下命令运行 ShardingSphere Proxy 的 Docker 容器：

docker run -d \-v /host/path/to/conf:/opt/shardingsphere-proxy/conf \-v /host/path/to/ext-lib:/opt/shardingsphere-proxy/ext-lib \-e PORT=3308 -p13308:3308 apache/shardingsphere-proxy:latest

数据库表结构

在上述配置中，我们对订单表（orders）基于 user_id 进行了分表操作，共分为四张表。而用户表（user）和产品表（product）则未进行分表处理，它们的建表语句如下：

DROP TABLE IF EXISTS `orders`;
CREATE TABLE `orders_0`  (`id` bigint NOT NULL,`order_id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,`user_id` int NOT NULL,`product_id` int NOT NULL,`order_date` bigint NOT NULL,PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (`id` bigint NOT NULL,`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

DROP TABLE IF EXISTS `product`;
CREATE TABLE `product`  (`id` bigint NOT NULL,`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

查看分表情况及连接操作

首先，我们可以使用 Navicat 直接连接 mysql 的 10.10.37.108:13306/sharding，通过这种方式便能查看到分表的具体情况。

使用 Navicat 或其他数据库管理工具，我们可以连接到 ShardingSphere Proxy。由于 Proxy 运行在 Docker 容器上，并且暴露了端口 13308，您应该使用以下连接信息：

主机：localhost 或 Docker 容器的 IP 地址
端口：13308
用户名：root 或 sharding（根据我们在 global.yaml 中的配置）
密码：对应的密码

连接成功后，我们将看到 ShardingSphere 自动创建的一些管理数据库和实例，以及我们配置的 sharding 数据库。在 sharding 数据库中，尽管我们实际上有多个 orders 分表，但 ShardingSphere 会将它们抽象为一个逻辑表，使我们能够像操作单个表一样进行操作。