hyperf对接ShardingSphere-Proxy完整流程

Hyperf 对接 ShardingSphere-Proxy 完整实战 下面给你一份复制即跑的完整方案,从 Docker 启动到业务代码全部到位。 --- 一、大白话先讲清楚架构 Hyperf 应用 │(用普通 MySQL 协议连3307)▼ ShardingSphere-Proxy(3307)← 它伪装成一个 MySQL │(自动改写 SQL,路由到真正的库)├──→ MySQL ds_0(3306)──→ t_order_0, t_order_1, t_order_2, t_order_3 └──→ MySQL ds_1(3306)──→ t_order_0, t_order_1, t_order_2, t_order_3 核心思路: - 真实数据库有2个(ds_0、ds_1),每个库里建4张表(t_order_0 ~ t_order_3),共8张物理表。 - Hyperf 永远只写 t_order(逻辑表),SQL 发给 Proxy 后,Proxy 根据 user_id 选库、根据 order_id 选表。 - 主键 order_id 用雪花算法自动生成,Proxy 帮你生成,业务代码不用管。 为什么这么分? - 按 user_id 分库:同一个用户的所有订单落在同一个库,查"我的订单"不用跨库。 - 按 order_id 分表:防止单表过大,雪花 ID 在4张表里均匀分布。 --- 二、目录结构 hyperf-sharding/ ├── docker/ │ ├── docker-compose.yml │ ├── sharding/ │ │ ├── server.yaml │ │ ├── global.yaml │ │ └── database-sharding.yaml │ └── mysql/ │ └── init.sql ├── app/ │ ├── Controller/OrderController.php │ ├── Model/Order.php │ └── Service/OrderService.php ├── config/autoload/databases.php ├── composer.json └── .env --- 三、Docker 一键启动 Proxy + 两个 MySQL docker/docker-compose.yml: version:'3.8'services: mysql-ds0: image: mysql:8.0 container_name: mysql-ds0 environment: MYSQL_ROOT_PASSWORD: root MYSQL_DATABASE: ds_0 ports: -"33060:3306"command: --default-authentication-plugin=mysql_native_password volumes: - mysql_ds0:/var/lib/mysql - ./mysql/init.sql:/docker-entrypoint-initdb.d/init.sql mysql-ds1: image: mysql:8.0 container_name: mysql-ds1 environment: MYSQL_ROOT_PASSWORD: root MYSQL_DATABASE: ds_1 ports: -"33061:3306"command: --default-authentication-plugin=mysql_native_password volumes: - mysql_ds1:/var/lib/mysql - ./mysql/init.sql:/docker-entrypoint-initdb.d/init.sql sharding-proxy: image: apache/shardingsphere-proxy:5.4.1 container_name: sharding-proxy ports: -"3307:3307"volumes: - ./sharding:/opt/shardingsphere-proxy/conf environment: -PORT=3307depends_on: - mysql-ds0 - mysql-ds1 volumes: mysql_ds0: mysql_ds1: docker/mysql/init.sql(两个库都执行,各建4张分表): CREATE TABLE IF NOT EXISTS t_order_0(order_id BIGINT NOT NULL PRIMARY KEY, user_id BIGINT NOT NULL, amount DECIMAL(12,2)NOT NULL, status VARCHAR(20)NOT NULL DEFAULT'pending', created_at DATETIME NOT NULL, KEY idx_user(user_id));CREATE TABLE IF NOT EXISTS t_order_1 LIKE t_order_0;CREATE TABLE IF NOT EXISTS t_order_2 LIKE t_order_0;CREATE TABLE IF NOT EXISTS t_order_3 LIKE t_order_0;CREATE TABLE IF NOT EXISTS t_user(user_id BIGINT NOT NULL PRIMARY KEY, name VARCHAR(64)NOT NULL, created_at DATETIME NOT NULL);大白话: t_order_* 是要分片的表,每个库都要预建;t_user 是广播表(后面解释),每个库都有完整数据。 --- 四、ShardingSphere-Proxy 配置(核心)1. docker/sharding/server.yaml(全局认证)authority: users: - user: root@% password: root privilege: type: ALL_PERMITTED mode: type: Standalone repository: type: JDBC 解释: 用户名密码,Hyperf 连 Proxy 时用这个;Standalone 单机模式(集群模式才需要 etcd/zk)。2. docker/sharding/global.yaml(SQL 显示、雪花 ID worker 配置)props: sql-show:true# 打印改写后的真实 SQL,调试必开proxy-default-port:3307proxy-frontend-database-protocol-type: MySQL3. docker/sharding/database-sharding.yaml(最重要)databaseName: sharding_db dataSources: ds_0: url: jdbc:mysql://mysql-ds0:3306/ds_0?useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true username: root password: root connectionTimeoutMilliseconds:30000idleTimeoutMilliseconds:60000maxLifetimeMilliseconds:1800000maxPoolSize:50minPoolSize:5ds_1: url: jdbc:mysql://mysql-ds1:3306/ds_1?useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true username: root password: root maxPoolSize:50minPoolSize:5rules: -!SHARDING tables: t_order: actualDataNodes: ds_${0..1}.t_order_${0..3}databaseStrategy: standard: shardingColumn: user_id shardingAlgorithmName: db_mod tableStrategy: standard: shardingColumn: order_id shardingAlgorithmName: table_mod keyGenerateStrategy: column: order_id keyGeneratorName: snowflake broadcastTables: - t_user# 广播表:每个库都有完整数据shardingAlgorithms: db_mod: type: MOD props: sharding-count:2# 2 个库table_mod: type: MOD props: sharding-count:4# 每库 4 张表keyGenerators: snowflake: type: SNOWFLAKE props: worker-id:1大白话逐行讲: - actualDataNodes: ds_${0..1}.t_order_${0..3}→ 物理表清单:ds_0.t_order_0、ds_0.t_order_1、...、ds_1.t_order_3,共8张。 - databaseStrategy → 按 user_id %2选库。 - tableStrategy → 按 order_id %4选表。 - keyGenerateStrategy: snowflake → INSERT 不传 order_id,Proxy 自动生成雪花 ID。 - broadcastTables:[t_user]→ 用户表数据量小,每个库存一份,JOIN 时不跨库,爽得很。 启动:cddockerdockercompose up-ddockercompose logs-fsharding-proxy# 看到 "ShardingSphere-Proxy Standalone mode started successfully" 就成了测试 Proxy 通不通: mysql-h127.0.0.1-P3307-uroot-proot-e"SHOW DATABASES;"# 看到 sharding_db 就 OK--- 五、Hyperf 工程1. composer.json(只列关键依赖){"require":{"php":">=8.1","hyperf/framework":"^3.1","hyperf/http-server":"^3.1","hyperf/database":"^3.1","hyperf/db-connection":"^3.1","hyperf/model-cache":"^3.1","hyperf/redis":"^3.1"}}composerinstall2. .envAPP_NAME=hyperf-shardingAPP_ENV=devDB_DRIVER=mysqlDB_HOST=127.0.0.1DB_PORT=3307# ← Proxy 端口,不是真实 MySQLDB_DATABASE=sharding_dbDB_USERNAME=rootDB_PASSWORD=rootDB_CHARSET=utf8mb4DB_COLLATION=utf8mb4_unicode_ci3. config/autoload/databases.php<?php declare(strict_types=1);return['default'=>['driver'=>env('DB_DRIVER','mysql'),'host'=>env('DB_HOST','localhost'),'port'=>env('DB_PORT',3307),'database'=>env('DB_DATABASE','sharding_db'),'username'=>env('DB_USERNAME','root'),'password'=>env('DB_PASSWORD','root'),'charset'=>env('DB_CHARSET','utf8mb4'),'collation'=>env('DB_COLLATION','utf8mb4_unicode_ci'),'prefix'=>'','pool'=>['min_connections'=>5,'max_connections'=>50, // 协程并发高,池子开大'connect_timeout'=>10.0,'wait_timeout'=>3.0,'heartbeat'=>-1,'max_idle_time'=>60.0,],'options'=>[// 关键:让 PDO 把所有字段当字符串返回,避免雪花 ID 在 PHP 里被转 float 丢精度 PDO::ATTR_STRINGIFY_FETCHES=>true, PDO::ATTR_EMULATE_PREPARES=>true,],],];⚠️重点踩坑: - 雪花 ID 是64位 BIGINT,JSON 输出和 JS 前端解析时精度丢失,后端一律用字符串传输。 - max_connections 不要太小,Hyperf 协程下50起步;Proxy 那边也有连接池,前后呼应。4. 模型 app/Model/Order.php:<?php declare(strict_types=1);namespace App\Model;use Hyperf\DbConnection\Model\Model;class Order extends Model{protected ?string$table='t_order';public bool$timestamps=false;// 我们自己处理 created_at protected array$fillable=['order_id','user_id','amount','status','created_at',];protected array$casts=['order_id'=>'string', // ← 雪花 ID 转字符串!'user_id'=>'int','amount'=>'decimal:2',];// 关键:告诉 Eloquent 主键不是自增,而是 Proxy 生成 public$incrementing=false;protected$primaryKey='order_id';protected$keyType='string';}app/Model/User.php:<?php declare(strict_types=1);namespace App\Model;use Hyperf\DbConnection\Model\Model;class User extends Model{protected ?string$table='t_user';public bool$timestamps=false;protected array$fillable=['user_id','name','created_at'];public$incrementing=false;protected$primaryKey='user_id';protected$keyType='int';}5. Service app/Service/OrderService.php:<?php declare(strict_types=1);namespace App\Service;use App\Model\Order;use App\Model\User;use Hyperf\DbConnection\Db;use Throwable;class OrderService{/** * 创建订单(不传 order_id,Proxy 自动生成雪花 ID)*/ publicfunctioncreate(int$userId, float$amount): Order{$order=new Order();$order->user_id=$userId;$order->amount=$amount;$order->status='pending';$order->created_at=date('Y-m-d H:i:s');$order->save();return$order->fresh();}/** * 查用户所有订单 - 只查一个库(同 user_id 在同库)* SQL: WHERE user_id=? → Proxy 路由到 ds_(user_id %2)*/ publicfunctionlistByUser(int$userId, int$page=1, int$size=20){returnOrder::query()->where('user_id',$userId)->orderByDesc('created_at')->forPage($page,$size)->get();}/** * 查单个订单 - 必须同时给 user_id 和 order_id,精准路由到1张表 */ publicfunctionfind(int$userId, string$orderId): ?Order{returnOrder::query()->where('user_id',$userId)->where('order_id',$orderId)->first();}/** * 跨库聚合(管理员看全平台订单总数)* 没带分片键 → Proxy 会广播到所有8张表 → 慢,生产慎用 */ publicfunctiontotalAmount(): string{$row=Db::selectOne('SELECT SUM(amount) AS total FROM t_order WHERE status = ?',['paid']);return(string)($row->total ??'0');}/** * 订单 + 用户名(广播表,JOIN 不跨库)*/ publicfunctionlistWithUser(int$userId){returnDb::table('t_order as o')->join('t_user as u','u.user_id','=','o.user_id')->where('o.user_id',$userId)->select('o.*','u.name')->orderByDesc('o.created_at')->get();}/** * 分布式事务示例(单库事务,因为 user_id 固定 → 同一个库)*/ publicfunctionpaySingleUser(int$userId, string$orderId): bool{try{Db::transaction(function()use($userId,$orderId){Order::query()->where('user_id',$userId)->where('order_id',$orderId)->update(['status'=>'paid']);});returntrue;}catch(Throwable$e){returnfalse;}}}6. Controller app/Controller/OrderController.php:<?php declare(strict_types=1);namespace App\Controller;use App\Service\OrderService;use Hyperf\Di\Annotation\Inject;use Hyperf\HttpServer\Annotation\Controller;use Hyperf\HttpServer\Annotation\GetMapping;use Hyperf\HttpServer\Annotation\PostMapping;use Hyperf\HttpServer\Contract\RequestInterface;#[Controller(prefix: '/orders')]class OrderController{#[Inject]protected OrderService$service;#[PostMapping('')]publicfunctioncreate(RequestInterface$request): array{$userId=(int)$request->input('user_id');$amount=(float)$request->input('amount');$order=$this->service->create($userId,$amount);return['code'=>0,'data'=>['order_id'=>(string)$order->order_id, // 一定要 string!'user_id'=>$order->user_id,'amount'=>$order->amount,'status'=>$order->status,],];}#[GetMapping('/user/{userId}')]publicfunctionlistByUser(int$userId): array{return['code'=>0,'data'=>$this->service->listByUser($userId)];}#[GetMapping('/find')]publicfunctionfind(RequestInterface$request): array{$userId=(int)$request->input('user_id');$orderId=(string)$request->input('order_id');return['code'=>0,'data'=>$this->service->find($userId,$orderId)];}#[GetMapping('/total')]publicfunctiontotal(): array{return['code'=>0,'total'=>$this->service->totalAmount()];}}--- 六、跑起来 + 验证# 1. 启动 Hyperfphp bin/hyperf.php start# 2. 创建几个用户(广播表,会在两个库各写一份)mysql-h127.0.0.1-P3307-uroot-prootsharding_db-e" INSERT INTO t_user (user_id, name, created_at) VALUES (1001, 'alice', NOW()), (1002, 'bob', NOW()), (1003, 'carol', NOW()); "# 3. 下单curl-XPOST http://127.0.0.1:9501/orders\-H'Content-Type: application/json'\-d'{"user_id":1001,"amount":99.99}'# {"code":0,"data":{"order_id":"1234567890123456789","user_id":1001,"amount":"99.99","status":"pending"}}curl-XPOST http://127.0.0.1:9501/orders-d'user_id=1002&amount=50'curl-XPOST http://127.0.0.1:9501/orders-d'user_id=1003&amount=20'# 4. 看 Proxy 的日志,会打印改写后的 SQLdockercompose logs sharding-proxy|grep"Actual SQL"# Actual SQL: ds_1 ::: INSERT INTO t_order_2 ... ← user_id=1001 % 2 = 1 → ds_1# Actual SQL: ds_0 ::: INSERT INTO t_order_0 ... ← user_id=1002 % 2 = 0 → ds_0# 5. 直连真实 MySQL,验证数据确实分到不同库mysql-h127.0.0.1-P33060-uroot-prootds_0-e"SELECT * FROM t_order_0; SELECT * FROM t_order_1; SELECT * FROM t_order_2; SELECT * FROM t_order_3;"mysql-h127.0.0.1-P33061-uroot-prootds_1-e"SELECT * FROM t_order_0; SELECT * FROM t_order_1; SELECT * FROM t_order_2; SELECT * FROM t_order_3;"--- 七、必须知道的几个坑 ┌────────────────────────────────┬─────────────────────────────┬─────────────────────────────────────────────────┐ │ 坑 │ 原因 │ 解决 │ ├────────────────────────────────┼─────────────────────────────┼─────────────────────────────────────────────────┤ │ JSON 输出 order_id │ JS Number 精度不够 │ 后端永远转 string 返回 │ │ 变科学计数法 │ │ │ ├────────────────────────────────┼─────────────────────────────┼─────────────────────────────────────────────────┤ │ WHERE 不带分片键,SQL │ Proxy 不知道路由到哪 │ 业务设计时确保查询路径都带 user_id │ │ 变成扫全部8张表 │ │ │ ├────────────────────────────────┼─────────────────────────────┼─────────────────────────────────────────────────┤ │ 跨库分页慢 │ Proxy │ 大数据量用 ES / ClickHouse 做查询库 │ │ │ 要把每库结果合并再排序 │ │ ├────────────────────────────────┼─────────────────────────────┼─────────────────────────────────────────────────┤ │ 跨库事务 │ 默认是 LOCAL,不保证一致 │ 配 XA 或 BASE 事务,见下文 │ ├────────────────────────────────┼─────────────────────────────┼─────────────────────────────────────────────────┤ │ │ │ 用 flyway / sql-migrate │ │ DDL 改表要在每个真实库都执行 │ Proxy 不会广播 DDL │ 对每个真实库跑一遍,或者用 ShardingSphere 的 │ │ │ │ DistSQL │ ├────────────────────────────────┼─────────────────────────────┼─────────────────────────────────────────────────┤ │ 协程下连接泄漏 │ 用 Db::beginTransaction │ 一律用 Db::transaction(fn()=>...)│ │ │ 没提交/回滚 │ │ └────────────────────────────────┴─────────────────────────────┴─────────────────────────────────────────────────┘ --- 八、可选增强1. 读写分离(Proxy 配置加一段)-!READWRITE_SPLITTING dataSources: readwrite_ds_0: writeDataSourceName: ds_0 readDataSourceNames:[ds_0_slave_1, ds_0_slave_2]loadBalancerName: random loadBalancers: random: type:RANDOM业务零改动,SELECT 自动走从库。2. 分布式事务(跨库写)-!TRANSACTION defaultType: XA providerType: Atomikos 业务代码改一行: Db::transaction(function(){/* 跨多个 user_id 的操作 */});// Proxy 自动用 XA 协议保证原子性3. SQL Hint(强制路由)某些场景不想用分片键路由,可以提示: Db::statement("/* SHARDINGSPHERE_HINT: ds_0 */ SELECT * FROM t_order LIMIT 10");--- 九、一句话总结 ▎ Hyperf 当 Proxy 是普通 MySQL,业务代码几乎不用动;所有"分库分表"的复杂逻辑全在 Proxy 配置文件里,改路由规则不发版。