はじめに
MySQL のパーティショニングを使用したので、備忘録として残す。
開発環境
- MySql：8.0
- Laravel：10
内容
パーティショニングとは
-
大きなテーブルを、小さな部分に分割して管理できる仕組みのこと。
-
ルール（パーティショニング関数）は4つあり。今回は1の
RANGEを使用
- RANGE（範囲） → 例：「日付ごとにデータを分ける」
- LIST（リスト） → 例：「都道府県ごとにデータを分ける」
- HASH（ハッシュ） → 例：「ユーザーIDで均等に分ける」
- KEY（キー） → 例：「主キーを使って自動で分ける」
パーティショニングはこの認識をさらに一歩進めて、必要に応じて多くの部分を設定できるルールに従って、個々のテーブルの部分をファイルシステムに配分できるようにしています。 それにより、テーブルの異なる部分が別個のテーブルとして別個の場所に格納されます。 データを分割するためにユーザーが選択するルールはパーティショニング関数と呼ばれ、MySQL では法、範囲セットまたは値リストに対する単純な照合、内部ハッシュ関数、または線形ハッシュ関数が使用されます。 関数は、ユーザーが指定したパーティショニングタイプに従って選択され、ユーザーが指定した式の値をパラメータとして取ります。 この式には、使用されるパーティショニングのタイプに応じて、カラム値、1 つ以上のカラム値を操作する関数、または 1 つ以上のカラム値のセットを指定できます。
「MySQL のパーティショニングの概要より引用」
パーティショニング追加
-
Laravelのマイグレーションにある程度沿った形式で書きたかったので以下のように記述。
-
Laravelのマイグレーションの書き方だと
PRIMARY KEYを複数設定できないので、SQLで追加。またPARTITION KEYは、PRIMARY KEYにしていないとエラーになるので注意。
2025_03_03_create_partition_to_xxx_table.php
<?php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;
return new class extends Migration {
/**
* Run the migrations.
*/
public function up(): void
{
Schema::create('xxx', function (Blueprint $table) {
$table->id('id')->comment('ID');
$table->unsignedInteger('year')->comment('年');
$table->string('name', 100)->comment('名前');
$table->string('body', 100)->comment('内容');
$table->timestamps();
});
//primary key設定
DB::statement("ALTER TABLE xxx ADD PRIMARY KEY (id,year);");
//パーティション
DB::statement("
ALTER TABLE class_datas
PARTITION BY RANGE (year) (
PARTITION p2024 VALUES LESS THAN (2025)
);
");
}
/**
* Reverse the migrations.
*/
public function down(): void
{
DB::statement("DROP TABLE IF EXISTS xxx;");
}
};
- 例えばcreated_atなどの日付をパーティションしたい場合は、以下のように記述。UNIXタイムスタンプ（整数値） に変換して、範囲パーティションを作成している。
2025_03_03_create_partition_to_xxx_table.php
<?php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;
return new class extends Migration {
/**
* Run the migrations.
*/
public function up(): void
{
Schema::create('xxx', function (Blueprint $table) {
$table->string('id')->comment('ID');
$table->string('name', 100)->comment('名前');
$table->string('body', 100)->comment('内容');
$table->timestamps();
});
//primary key設定
DB::statement("ALTER TABLE xxx ADD PRIMARY KEY (id,created_at);");
//created_at（4月始まり3月終わりで年度計算）でパーティション
DB::statement("
ALTER TABLE xxx
PARTITION BY RANGE (UNIX_TIMESTAMP(created_at)) (
PARTITION p2023 VALUES LESS THAN (UNIX_TIMESTAMP('2024-04-01 00:00:00')),
PARTITION p2024 VALUES LESS THAN (UNIX_TIMESTAMP('2025-04-01 00:00:00'))
);
");
}
/**
* Reverse the migrations.
*/
public function down(): void
{
DB::statement("DROP TABLE IF EXISTS xxx;");
}
};
注意する点
-
パーティションを使用したことにより、既存のデータで、あるパーティションのみのデータをデフォルトで取得するには、工夫が必要。今回はグローバルスコープを使用して対応
-
App\Scopes\CurrentYearScope.phpへグローバルスコープを作成。
App\Scopes\CurrentYearScope.php
<?php
namespace App\Scopes;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Scope;
use Carbon\Carbon;
class CurrentYearScope implements Scope
{
public function apply(Builder $builder, Model $model)
{
// 最新の年度（4月始まり3月終わり）を取得
$currentYear = Carbon::now()->month < 4 ? Carbon::now()->year - 1 : Carbon::now()->year;
$builder->where('created_at', '>=', "{$currentYear}-04-01 00:00:00")
->where('created_at', '<', ($currentYear + 1) . "-04-01 00:00:00");
}
}
- 対象のモデル（App\Models\Xxx.php）に スコープを適用
App\Models\Xxx.php
<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
use App\Scopes\CurrentYearScope;
class Xxx extends Model
{
use HasFactory;
protected $table = 'xxx';
protected static function booted()
{
static::addGlobalScope(new CurrentYearScope);
}
}
- 動作
$records = Xxx::all();
# SQL
SELECT * FROM xxx
WHERE created_at >= '2024-04-01 00:00:00'
AND created_at < '2025-04-01 00:00:00';
- 一時的にスコープを無効化したい場合は以下のように記述
$allRecords = Xxx::withoutGlobalScope(CurrentYearScope::class)->get();
- グローバルスコープは
joinには適用されないので、明示的にwhereで条件を指定する必要あり。
xxxIndexAction.php
$query = Xxx::query()
->select('name')
->join('yyy', 'xxx.id', '=', 'yyy.xxx_id')
->where('xxx.year', 2025)
->get();
まとめ
MySQLのパーティショニング使用した時の、備忘録として注意点も含めて記載いたしました。使用するときはパーティショニングに適用しているのか？も含めて検討した方が良さそうです。