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 条件中,否则会全表路由,影响性能。

七、参考资料


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

  • 服务器 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生产环境以下是建议的优化调整

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

    # 修改为适合24G的配置(约占用总内存的70%DEFAULT_JAVA_MEM_COMMON_OPTS=" -Xmx2g -Xms2g -Xmn1g "
  2. 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 协议》,转载必须注明作者和本文链接
《L04 微信小程序从零到发布》
从小程序个人账户申请开始,带你一步步进行开发一个微信小程序,直到提交微信控制台上线发布。
《L01 基础入门》
我们将带你从零开发一个项目并部署到线上,本课程教授 Web 开发中专业、实用的技能,如 Git 工作流、Laravel Mix 前端工作流等。
讨论数量: 0
(= ̄ω ̄=)··· 暂无内容!

讨论应以学习和精进为目的。请勿发布不友善或者负能量的内容,与人为善,比聪明更重要!