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生产环境以下是建议的优化调整
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 协议》,转载必须注明作者和本文链接
推荐文章: