PHP Laravel 使用ShardingSphere-Proxy分表

# 文档
https://shardingsphere.apache.org/document/current/cn/quick-start/

# 下载
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

# 下载放到ext-lib目录, PostgreSQL 数据库可以省略此步
https://repo1.maven.org/maven2/mysql/mysql-connector-java/8.0.11/mysql-connector-java-8.0.11.jar

# 安装 Java JRE 8
curl -s "https://get.sdkman.io" | bash
source "$HOME/.sdkman/bin/sdkman-init.sh"
sdk list java | grep 8
sdk install java 8.0.452-zulu
sdk default java 8.0.452-zulu
java -version

# 编辑 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 "

# 启动 默认3307, 指定端口
./bin/start.sh 3308

# 停止
./bin/stop.sh

# 备份
cp global.yaml global.yaml.bak
cp database-sharding.yaml database-sharding_db.yaml.bak

# global.yaml
mode:
  type: Standalone # 单机
  repository:
    type: JDBC

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

props:
 sql-show: true

# database-sharding.yaml
databaseName: sharding_db

dataSources:
  ds_0:
    #url: jdbc:mysql://127.0.0.1:3306/sharding?useSSL=false&serverTimezone=Asia/Shanghai&useLegacyDatetimeCode=false
    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.hashCode() & Integer.MAX_VALUE) % 4 }
        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 .env ,  执行 php artisan migrate 时配置
APP_TIMEZONE=UTC
DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=sharding
DB_USERNAME=sharding
DB_PASSWORD=sharding

# laravel .env  crud 业务时配置走代理
APP_TIMEZONE=UTC
DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3307
DB_DATABASE=sharding_db
DB_USERNAME=sharding
DB_PASSWORD=sharding

# laravel 迁移  php artisan make:migration create_members_table
<?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)
        );
    }
    
    /**
     * Run the migrations.
     */
    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');
        });
        
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        foreach ($this->getShardTableNames() as $tableName) {
            Schema::dropIfExists($tableName);
        }
        
        Schema::dropIfExists("member_email_index");
        
    }
};


# laravel 迁移  php artisan make:migration create_order_table
<?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'];
    
    /**
     * Run the migrations.
     */
    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');
                });
            }
        }
        
    }


    /**
     * Reverse the migrations.
     */
    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);
            }
        }
        
    }
};


<?php
namespace App\Http\Controllers;

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

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' => Hash::make('password'),
                    'remember_token' => Str::random(10),
                ]);
                
                MemberEmailIndex::create([
                    'email' => $member->email,
                    'member_id' => $member->member_id,
                    'shard_index' => $member->member_id % 4,
                ]);
                
                // 当 i=2 时抛出异常，触发回滚
                // if ($i == 2) {
                //     throw new \Exception("测试回滚！");
                // }
                
                Member::where('member_id',$member->member_id)->update([
                    'name' => $member->name . 'xxx'
                ]);
                
             }
        });
        
        $members = Member::query()->orderby('created_at','desc')->limit(2)->get();
        
        $order = Order::create([
            'member_id' => 1,
            'amount' => 100,
        ]);

        $parseUlid = $this->parse($order->order_id);
        
        OrderItem::create([
            'order_id' => $order->order_id,
            'member_id' => 1,
            'product_name' => 'test',
            'quantity' => 1,
            'amount' => 100,
        ]);
        
        // 必须包含分片键 created_at
        $startTime =  now()->subMonth();
        $endTime =  now();
        
        $orders = Order::where('member_id',request('member_id'))->whereBetween('created_at', [$startTime, $endTime])
            ->with(['items' => function ($query) use ($startTime, $endTime) {
                $query->whereBetween('created_at', [$startTime, $endTime]);
            }])
            ->orderby('created_at','desc')
            ->paginate(1);
                    
        $data = [
            'parseUlid' => $parseUlid,
            'orders' => $orders,
            'members' => $members,
            'sql' => DB::getQueryLog()
        ];
        
        return response()->json($data);
    }

}

<?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();
        
        // 在创建前自动生成 ID
        static::creating(function ($order) {
            $order->order_id = Str::ulid();
        });
    }
    
}

<?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)
    {
        // 确保存储为UTC时间
        $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();
        
        // 在创建前自动生成 ID
        static::creating(function ($orderItem) {
            $orderItem->order_item_id = Str::ulid();
        });
    }

}

<?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);
            
            // 截取 UUID 后几位，与时间戳组合
            $shortUuid = substr($hex, -8); // 取 UUID 后8个字符（32位）
            $id = ($timestamp << 32) | hexdec($shortUuid); // 组合时间戳和 UUID
            
            $model->member_id = $id;
        });
    }
    
}


<?php

namespace App\Models;

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

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',
    ];
      
}

特别注意：所有数据库操作必须通过Proxy（3307端口），直接连接物理库会导致分片规则失效。迁移操作除外，需直连物理库（3306端口）执行DDL。

最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
平台声明：文章内容（如有图片或视频亦包括在内）由作者上传并发布，文章内容仅代表作者本人观点，简书系信息发布平台，仅提供信息存储服务。

推荐阅读更多精彩内容