結論
100,000件のtestsテーブルからyearカラムに対してパーティションキーを設定して比較した結果はこちら
// クエリ
DB::table('tests')->where('year', 2023)->get();
// パーティションを設定した結果
結果: 0.47547888755798 seconds
// パーティションを設定していない結果
結果: 2.206062078476 seconds
約5倍 は早くなりました...タイトルとおりです。
パーティションキーとは
パーティション = 仕切り というようにデータを指定した値ごとに仕切ることで、読み込み速度が高速化されます。
2022年、2023年、2024年...ごとにデータが積まれていく場合、年カラムにパーティションキーを設定することで検索結果が早くなります。
※パーティションキーにはいくつかの種類がありますが、今回は特定の範囲に絞り込む「レンジパーティション」に絞り込んで紹介します。
migrationファイルで設定する
Laravelの機能でパーティションを設定することはできないので、DB::statemantでSQL文を記載します。
public function up()
{
Schema::create('tests', function (Blueprint $table) {
$table->bigInteger('id', false)->unsigned()->comment('ID');
$table->integer('year'); // パーティションの対象
$table->timestamps();
$table->softDeletes()->comment('削除日');
$table->comment('テスト');
});
//yearカラムに対して2020,2021,2022,2023,2024のパーティションを設定
DB::statement("
ALTER TABLE orders
PARTITION BY RANGE (year) (
PARTITION p2020 VALUES LESS THAN (2020),
PARTITION p2021 VALUES LESS THAN (2021),
PARTITION p2022 VALUES LESS THAN (2022),
PARTITION p2023 VALUES LESS THAN (2023),
PARTITION p2024 VALUES LESS THAN (2024)
);
");
}
public function down()
{
Schema::dropIfExists('tests');
}
設定してみた感想
使用する用途としては、データの作成更新を行わず検索のみ行う場合が最も使いやすいかなと思います。（自分の知識がないだけかもしれません...）
理由その１：idに自動インクリメントを設定している場合、パーティションを設定できないため
通常、idに対して自動インクリメントをオンにして使用することが多いと思います。
$table->bigInteger('id', true)->unsigned()->comment('ID');
しかしこの状態でパーティションを追加しようとすると以下のエラーが表示されます。
SQLSTATE[HY000]: General error: 1503 A PRIMARY KEY must include all columns in the table's partitioning function (prefixed columns are not considered).
パーティションキーにはプライマリーキーの指定が必要
↓
プライマリーキーはテーブルに対して1つのため、idを使用している場合複合キーとして設定する（今回はidとyear）
↓
複合キーに使用するキーは、自動インクリメントがついていると使えない
↓
id使えないやん...
というオチです。
そのため自動インクリメントを使用する場合は設定ができません。
理由その２：毎年の設定が必要
migrationファイルからわかるようにどの範囲までを仕切る対象とするかを記載しないといけません。
そのため、cron等で年を超えた際に新しく追加しなければいけません。
以上の理由から、ユーザーからのデータ作成や更新が行われないデータに対して設定する場合に限定されるかなと思いました。
ただ、検索処理は爆速になるため
- 過去データから新しくデータを作成する際、過去データに設定
- 年や月ごとの入会者数や決済数をカウントするために、バックアップデータを複製して設定
などのケースに使用したいなと思ってます。
最後に
こちらの神良本を読んでパーティションという機能があることを知りました...
よく紹介されていますが第二版が出版され、AWSといったクラウド環境におけるデータベースの取り扱いについての記載が増え、とても勉強になりました...
おすすめです！！