PHP Laravel 使用ShardingSphere-Proxy 分表

Laravel + ShardingSphere 实现高性能分库分表实践

本文将手把手带你用 ShardingSphere Proxy 实现 Laravel 项目的分库分表，适合有 Laravel 基础、想要提升数据库性能和扩展性的开发者。

一、背景介绍

随着业务量的增长，单表数据量越来越大，数据库性能逐渐成为瓶颈。分库分表是常见的数据库水平扩展方案。ShardingSphere Proxy 作为一款优秀的分布式数据库中间件，支持多种分片策略，兼容 MySQL 协议，适合与 Laravel 等主流框架集成。

二、环境准备

1. 下载并解压 ShardingSphere Proxy

wget https://downloads.apache.org/shardingsphere/5.5.2/apache-shardingsphere-5.5.2-shardingsphere-proxy-bin.tar.gz
tar -zxvf apache-shardingsphere-5.5.2-shardingsphere-proxy-bin.tar.gz
cd apache-shardingsphere-5.5.2-shardingsphere-proxy-bin/
mkdir ext-lib

2. 添加 MySQL 驱动

将 mysql-connector-java-8.0.11.jar 下载到 ext-lib 目录（PostgreSQL 可省略）。

3. 安装 Java 8

curl -s "https://get.sdkman.io" | bash
source "$HOME/.sdkman/bin/sdkman-init.sh"
sdk install java 8.0.452-zulu
sdk default java 8.0.452-zulu
java -version

4. 启动 ShardingSphere Proxy

# 编辑 bin/start.sh
# JAVA_OPTS=" -Djava.awt.headless=true "
# 添加 -Duser.timezone=UTC 参数
JAVA_OPTS=" -Djava.awt.headless=true -Duser.timezone=UTC "
# 修改测试配置
DEFAULT_JAVA_MEM_COMMON_OPTS=" -Xmx1g -Xms512m -Xmn256m "

可自定义端口（如 3308）：

./bin/start.sh 3308

停止服务：

./bin/stop.sh

三、配置 ShardingSphere

1. 配置文件备份

cp conf/global.yaml conf/global.yaml.bak
cp conf/database-sharding.yaml conf/database-sharding_db.yaml.bak

2. 编辑 `global.yaml`

mode:
  type: Standalone # 单机
  repository:
    type: JDBC

authority:
  users:
    - user: sharding
      password: sharding
  privilege:
    type: ALL_PERMITTED

props:
  sql-show: true

3. 编辑 `database-sharding.yaml`

以订单（orders）、订单项（order_items）按月分表，会员（members）按 ID 取模分表为例：

databaseName: sharding_db

dataSources:
  ds_0:
    url: jdbc:mysql://127.0.0.1:3306/sharding?useSSL=false&characterEncoding=utf8&serverTimezone=UTC
    username: sharding
    password: sharding
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
    minPoolSize: 1

rules:
- !SHARDING
  tables:
    orders:
      actualDataNodes: ds_0.orders_${ (2025..2026).collect{ y -> (1..12).collect{ m -> sprintf('%d%02d', y, m) } }.flatten() }
      tableStrategy:
        standard:
          shardingColumn: created_at
          shardingAlgorithmName: orders_month_interval
      # 移除 keyGenerateStrategy,自己生成 ID
      #   keyGenerateStrategy:
      #     column: order_id
      #     keyGeneratorName: snowflake
      auditStrategy:
        auditorNames:
          - sharding_key_required_auditor
        allowHintDisable: true
    order_items:
      actualDataNodes: ds_0.order_items_${ (2025..2026).collect{ y -> (1..12).collect{ m -> sprintf('%d%02d', y, m) } }.flatten() }
      tableStrategy:
        standard:
          shardingColumn: created_at
          shardingAlgorithmName: order_items_month_interval
      #   keyGenerateStrategy:
      #     column: order_item_id
      #     keyGeneratorName: snowflake
    members:
      actualDataNodes: ds_0.members_${0..3}
      tableStrategy:
        standard:
          shardingColumn: member_id
          shardingAlgorithmName: members_inline
      # 由 ShardingSphere 自动生成 member_id
      #   keyGenerateStrategy:
      #     column: member_id  # 自动生成 ID 的字段
      #     keyGeneratorName: snowflake  # 使用雪花算法（生成整数 ID）
  bindingTables:
    - orders,order_items
  # 移除分库策略配置（单库不需要）
  #   defaultDatabaseStrategy:
  #     standard:
  #       shardingColumn: user_id
  #       shardingAlgorithmName: database_inline
  # 移除 database_inline 算法（单库不需要）
  shardingAlgorithms:
    # database_inline:
    #   type: INLINE
    #   props:
    #     algorithm-expression: ds_0
    # 按月分表算法
    orders_month_interval:
      type: INTERVAL
      props:
        datetime-pattern: "yyyy-MM-dd HH:mm:ss"
        datetime-lower: "2025-01-01 00:00:00"
        datetime-upper: "2099-12-31 00:00:00"
        sharding-suffix-pattern: "yyyyMM"
        datetime-interval-amount: "1"
        datetime-interval-unit: "MONTHS"
    order_items_month_interval:
      type: INTERVAL
      props:
        datetime-pattern: "yyyy-MM-dd HH:mm:ss"
        datetime-lower: "2025-01-01 00:00:00"
        datetime-upper: "2099-12-31 00:00:00"
        sharding-suffix-pattern: "yyyyMM"
        datetime-interval-amount: "1"
        datetime-interval-unit: "MONTHS"
    # members 表按 member_id 取模分片
    members_inline:
      type: INLINE
      props:
        algorithm-expression: members_${member_id % 4}
  keyGenerators:
    snowflake:
      type: SNOWFLAKE
  auditors:
    sharding_key_required_auditor:
      type: DML_SHARDING_CONDITIONS
- !BROADCAST
  tables:
    - users
    - sessions
    - cache
    - cache_locks
    - jobs
    - migrations
    - password_reset_tokens
    - member_email_index

四、Laravel 配置与迁移

1. 数据库连接

迁移时（直连物理库 3306）：

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=sharding
DB_USERNAME=sharding
DB_PASSWORD=sharding

业务访问（通过 Proxy 3307）：

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3307
DB_DATABASE=sharding_db
DB_USERNAME=sharding
DB_PASSWORD=sharding

注意： 所有业务 SQL 必须通过 Proxy 端口，直连物理库会导致分片规则失效。只有迁移（DDL）操作例外。

2. 分表迁移示例

2.1 会员表分片

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    private const SHARD_COUNT = 4;
    private function getShardTableNames(): array
    {
        return array_map(
            fn($i) => "members_{$i}",
            range(0, self::SHARD_COUNT - 1)
        );
    }
    public function up(): void
    {
        foreach (range(0, self::SHARD_COUNT - 1) as $index) {
            Schema::create("members_{$index}", function (Blueprint $table) {
                $table->unsignedBigInteger('member_id')->primary();
                $table->string('name');
                $table->string('email')->unique();
                $table->timestamp('email_verified_at')->nullable();
                $table->string('password');
                $table->rememberToken();
                $table->timestamps();
            });
        }
        Schema::create('member_email_index', function (Blueprint $table) {
            $table->string('email')->primary();
            $table->unsignedBigInteger('member_id');
            $table->tinyInteger('shard_index');
        });
    }
    public function down(): void
    {
        foreach ($this->getShardTableNames() as $tableName) {
            Schema::dropIfExists($tableName);
        }
        Schema::dropIfExists("member_email_index");
    }
};

2.2 订单分表

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    private $years = [2025,2026];
    private $months = ['01', '02', '03', '04', '05', '06', '07', '08', '09', '10', '11', '12'];
    public function up(): void
    {
        $this->createOrderTables();
        $this->createOrderItemTables();
    }
    private function createOrderTables()
    {
        foreach ($this->years as $year) {
            foreach ($this->months as $month) {
                $tableName = "orders_{$year}{$month}";
                Schema::create($tableName, function (Blueprint $table) {
                    $table->ulid('order_id')->primary();
                    $table->unsignedBigInteger('member_id');
                    $table->decimal('amount', 10, 2);
                    $table->timestamps();
                    $table->index('member_id');
                    $table->index('created_at');
                });
            }
        }
    }
    private function createOrderItemTables()
    {
        foreach ($this->years as $year) {
            foreach ($this->months as $month) {
                $tableName = "order_items_{$year}{$month}";
                Schema::create($tableName, function (Blueprint $table) {
                    $table->ulid('order_item_id')->primary();
                    $table->ulid('order_id');
                    $table->unsignedBigInteger('member_id');
                    $table->string('product_name', 255);
                    $table->integer('quantity');
                    $table->decimal('amount', 10, 2);
                    $table->timestamps();
                    $table->index('order_id');
                    $table->index('member_id');
                    $table->index('created_at');
                });
            }
        }
    }
    public function down(): void
    {
        $this->dropOrderTables();
        $this->dropOrderItemTables();
    }
    private function dropOrderTables()
    {
        foreach ($this->years as $year) {
            foreach ($this->months as $month) {
                $tableName = "orders_{$year}{$month}";
                Schema::dropIfExists($tableName);
            }
        }
    }
    private function dropOrderItemTables()
    {
        foreach ($this->years as $year) {
            foreach ($this->months as $month) {
                $tableName = "order_items_{$year}{$month}";
                Schema::dropIfExists($tableName);
            }
        }
    }
};

3. Eloquent 模型配置

Order 模型

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Str;

class Order extends Model
{
    protected $table = 'orders'; // ShardingSphere 逻辑表名
    protected $primaryKey = 'order_id';
    public $incrementing = false; // 使用分布式ID
    protected $fillable = [ 'member_id', 'amount' ];
    protected $casts = [ 'created_at' => 'datetime:Y-m-d H:i:s', ];
    public function items()
    {
        return $this->hasMany(OrderItem::class, 'order_id', 'order_id');
    }
    protected static function boot()
    {
        parent::boot();
        static::creating(function ($order) {
            $order->order_id = Str::ulid();
        });
    }
}

OrderItem 模型

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Str;

class OrderItem extends Model
{
    protected $table = 'order_items'; // ShardingSphere 逻辑表名
    protected $primaryKey = 'order_item_id';
    public $incrementing = false;
    protected $fillable = [ 'order_id', 'member_id', 'product_name', 'quantity', 'amount', ];
    protected $casts = [ 'created_at' => 'datetime:Y-m-d H:i:s', ];
    public function setCreatedAtAttribute($value)
    {
        $this->attributes['created_at'] = $value->timezone('UTC');
    }
    public function order()
    {
        return $this->belongsTo(Order::class, 'order_id', 'order_id');
    }
    protected static function boot()
    {
        parent::boot();
        static::creating(function ($orderItem) {
            $orderItem->order_item_id = Str::ulid();
        });
    }
}

Member 模型

<?php

namespace App\Models;

use Illuminate\Notifications\Notifiable;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Date;
use Illuminate\Support\Str;

class Member extends Model
{
    protected $table = 'members'; // ShardingSphere 逻辑表名
    protected $primaryKey = 'member_id';
    public $incrementing = false;
    protected $fillable = [ 'name', 'email', 'password', ];
    protected $hidden = [ 'password', 'remember_token', ];
    protected $casts = [ 'created_at' => 'datetime:Y-m-d H:i:s', ];
    protected static function boot()
    {
        parent::boot();
        static::creating(function ($model) {
            $timestamp = Date::now()->timestamp;
            $uuid = Str::uuid()->toString();
            $hex = str_replace('-', '', $uuid);
            $shortUuid = substr($hex, -8);
            $id = ($timestamp << 32) | hexdec($shortUuid);
            $model->member_id = $id;
        });
    }
}

MemberEmailIndex 模型

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class MemberEmailIndex extends Model
{
    protected $table = 'member_email_index';
    protected $primaryKey = 'email';
    public $incrementing = false;
    public $timestamps = false;
    protected $fillable = [ 'email', 'member_id', 'shard_index', ];
}

五、业务代码示例

以批量创建会员、订单为例：

<?php

namespace App\Http\Controllers;

use App\Models\Order;
use App\Models\OrderItem;
use App\Models\Member;
use App\Models\MemberEmailIndex;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Str;
use Illuminate\Support\Facades\Hash;
use Illuminate\Support\Carbon;

class IndexController extends Controller
{
    public function parse($ulid)
    {
        try {
            $parsedDate = Carbon::createFromId(strtoupper($ulid));
            return sprintf('%s_%s', (new Order)->getTable(), $parsedDate->format('Ym'));
        } catch (\Exception $e) {
            return false;
        }
    }

    public function index()
    {
        DB::connection()->enableQueryLog();

        DB::transaction(function () {
            for ($i = 0; $i < 4; $i++) {
                $member = Member::create([
                    'name' => fake()->name(),
                    'email' => fake()->unique()->safeEmail(),
                    'email_verified_at' => now(),
                    'password' => bcrypt('password'),
                    'remember_token' => Str::random(10),
                ]);

                MemberEmailIndex::create([
                    'email' => $member->email,
                    'member_id' => $member->member_id,
                    'shard_index' => $member->member_id % 4,
                ]);

                // 测试更新操作
                Member::where('member_id', $member->member_id)->update([
                    'name' => $member->name . 'xxx'
                ]);
            }
        });

        // 获取测试数据（最近2个会员）
        $members = Member::query()->orderBy('created_at', 'desc')->limit(2)->get();

        // 随机选择一个有效的会员ID
        $validMemberIds = Member::pluck('member_id')->toArray();
        if (empty($validMemberIds)) {
            // 极端情况：无会员时创建一个
            $fallbackMember = Member::create([
                'name' => 'Fallback Member',
                'email' => 'fallback@test.com',
                'password' => bcrypt('password'),
            ]);
            $targetMemberId = $fallbackMember->member_id;
        } else {
            $targetMemberId = $validMemberIds[array_rand($validMemberIds)];
        }

        // 创建订单（使用有效的会员ID）
        $order = Order::create([
            'member_id' => $targetMemberId,
            'amount' => 100,
        ]);

        // 创建订单项
        OrderItem::create([
            'order_id' => $order->order_id,
            'member_id' => $targetMemberId,
            'product_name' => 'Test Product',
            'quantity' => 1,
            'amount' => 100,
        ]);

        // 解析ULID（测试分片表名）
        $parseUlid = $this->parse($order->order_id);

        $startTime = now()->subMonth();
        $endTime = now();

        $orders = Order::where('member_id', $targetMemberId)
            ->whereBetween('created_at', [$startTime, $endTime])
            ->with(['items' => function ($query) use ($startTime, $endTime) {
                $query->whereBetween('created_at', [$startTime, $endTime]);
            }])
            ->orderBy('created_at', 'desc')
            ->paginate(1);

        // 关联会员信息（手动拼装，避免跨分片关联）
        $orderMemberIds = $orders->pluck('member_id')->unique()->toArray();
        $orderMembers = Member::whereIn('member_id', $orderMemberIds)->get(); // 按分片键查询，确保能命中

        $orders->each(function ($order) use ($orderMembers) {
            $order->member = $orderMembers->firstWhere('member_id', $order->member_id);
        });

        return response()->json([
            'parseUlid' => $parseUlid,
            'orders' => $orders,
            'test_members' => $members, 
            'used_member_id' => $targetMemberId,
            'sql_logs' => DB::getQueryLog(),
        ]);
    }
}

六、常见问题与注意事项

所有业务 SQL 必须通过 Proxy 端口（如 3307），否则分片规则不生效。
迁移（DDL）操作需直连物理库（如 3306）。
分片键（如 created_at、member_id）必须出现在 SQL 条件中，否则会全表路由，影响性能。

七、参考资料

ShardingSphere 官方文档

双服务器不停机更新配置步骤

服务器 A（主）：运行 3307 端口的 Proxy 实例，应用当前连接此实例
服务器 B（备）：运行 3308 端口的 Proxy 实例，待更新配置后切换流量
1. 更新备服务器（B）的配置
2. 验证备服务器（B）配置正确性
连接服务器 B 的 3308 端口，通过 show tables、插入测试数据等方式，验证新配置是否生效
3. 切换应用流量至备服务器（B）
修改数据库连接地址为 服务器B:3308
4. 更新主服务器（A）的配置
此时服务器 A 已无流量，可安全更新：
5. 切回主服务器（可选）
若需恢复主服务器流量，重复步骤 3，将应用连接切回服务器 A 的 3307 端口

2核4G生产环境以下是建议的优化调整

JVM内存配置调整 (在start.sh中)

# 修改为适合2核4G的配置（约占用总内存的70%）
DEFAULT_JAVA_MEM_COMMON_OPTS=" -Xmx2g -Xms2g -Xmn1g "

global.yaml关键配置优化

mode:
type: Standalone
repository:
 type: JDBC 
props:
sql-show: false  # 生产环境建议关闭SQL日志
max-connections-size-per-query: 5  # 根据实际查询并发调整
kernel-executor-size: 10  # 2核机器建议10-20之间
proxy-frontend-executor-size: 4  # 前端线程数
proxy-frontend-max-connections: 200  # 最大连接数

# 连接池相关优化
proxy-backend-query-fetch-size: -1
proxy-frontend-flush-threshold: 128

# 其他生产建议
check-table-metadata-enabled: false
proxy-netty-backlog: 2048

logging:
loggers:
 - loggerName: ShardingSphere-SQL
   additivity: true
   level: ERROR  # 生产环境建议设为ERROR级别
   props:
     enable: false  # 关闭详细SQL日志

3. 文件描述符限制

# 修改系统限制（需root）
echo "* soft nofile 65535" >> /etc/security/limits.conf
echo "* hard nofile 65535" >> /etc/security/limits.conf
ulimit -n 65535  # 当前会话生效

4. 内核参数调优

# /etc/sysctl.conf 追加
net.core.somaxconn = 4096
net.ipv4.tcp_max_syn_backlog = 4096
net.ipv4.tcp_tw_reuse = 1
vm.swappiness = 10

本作品采用《CC 协议》，转载必须注明作者和本文链接

王宣成

3 声望

暂无个人描述~

0 人点赞

从小程序个人账户申请开始，带你一步步进行开发一个微信小程序，直到提交微信控制台上线发布。

我们将带你从零开发一个项目并部署到线上，本课程教授 Web 开发中专业、实用的技能，如 Git 工作流、Laravel Mix 前端工作流等。

PHP Laravel 使用ShardingSphere-Proxy 分表

Laravel + ShardingSphere 实现高性能分库分表实践

一、背景介绍

二、环境准备

1. 下载并解压 ShardingSphere Proxy

2. 添加 MySQL 驱动

3. 安装 Java 8

4. 启动 ShardingSphere Proxy

三、配置 ShardingSphere

1. 配置文件备份

2. 编辑 `global.yaml`

3. 编辑 `database-sharding.yaml`

四、Laravel 配置与迁移

1. 数据库连接

2. 分表迁移示例

2.1 会员表分片

2.2 订单分表

3. Eloquent 模型配置

Order 模型

OrderItem 模型

Member 模型

MemberEmailIndex 模型

五、业务代码示例

六、常见问题与注意事项

七、参考资料

双服务器不停机更新配置步骤

1. 更新备服务器（B）的配置

2. 验证备服务器（B）配置正确性

3. 切换应用流量至备服务器（B）

4. 更新主服务器（A）的配置

5. 切回主服务器（可选）

2核4G生产环境以下是建议的优化调整

推荐文章：

社区赞助商

关于 LearnKu

资源推荐

服务提供商

其他信息

PHP Laravel 使用ShardingSphere-Proxy 分表

Laravel + ShardingSphere 实现高性能分库分表实践

一、背景介绍

二、环境准备

1. 下载并解压 ShardingSphere Proxy

2. 添加 MySQL 驱动

3. 安装 Java 8

4. 启动 ShardingSphere Proxy

三、配置 ShardingSphere

1. 配置文件备份

2. 编辑 global.yaml

3. 编辑 database-sharding.yaml

四、Laravel 配置与迁移

1. 数据库连接

2. 分表迁移示例

2.1 会员表分片

2.2 订单分表

3. Eloquent 模型配置

Order 模型

OrderItem 模型

Member 模型

MemberEmailIndex 模型

五、业务代码示例

六、常见问题与注意事项

七、参考资料

双服务器不停机更新配置步骤

1. 更新备服务器（B）的配置

2. 验证备服务器（B）配置正确性

3. 切换应用流量至备服务器（B）

4. 更新主服务器（A）的配置

5. 切回主服务器（可选）

2核4G生产环境以下是建议的优化调整

推荐文章：

社区赞助商

关于 LearnKu

资源推荐

服务提供商

其他信息

请登录

2. 编辑 `global.yaml`

3. 编辑 `database-sharding.yaml`