Laravel + MySQL Partition

MySQLのPartition機能を使って、処理速度を上げようとした際に詰まったところをメモ。

バージョン情報

• Laravel : 6.20
• MySQL : 8.0
• OS : Almalinux ( Windows 10 の docker上で稼働 )

Partitionを定義する

今回は既存のテーブルにパーティションを追加した。

以下のようなusersテーブルがあると仮定して、birthdayでpartitionを切るとする。

ALTER TABLEでpartitionを適用する

下記のmigrationでは、年度で区切ってみた。

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illumiante\Support\Facades\Schema;
use Illuminate\Support\Facades\DB;

clas AddPartitionUsers extends Migration
{
    /**
     * Run the migrations.
     * 
     * @return void
     */
    public function up()
    {
        Schema::table('users', function (Blueprint $table) {
            $ddl = "ALTER TABLE users " .
                " PARTITION BY RANGE COLUMNS(birthday) (" .
                " PARTITION p2020 VALUES LESS THAN ('2021-03-31')," .
                " PARTITION p2021 VALUES LESS THAN ('2022-03-31')," .
                " PARTITION p2022 VALUES LESS THAN ('2023-03-31')," .
                // (未来のどこまでパーティションを用意するかはお好みで)
                " PARTITION pmax VALUES LESS THAN MAXVALUE);";
            DB::getPdo()->exec($ddl);
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::table('users', function (Blueprint $table) {
            $ddl = "ALTER TABLE users REMOVE PARTITIONING;";
            DB::getPdo()->exec($ddl);
        });
    }
}

単純に年で区切る場合はYEAR()関数が使える。

    public function up()
    {
        Schema::table('users', function (Blueprint $table) {
            $ddl = "ALTER TABLE users " .
                " PARTITION BY RANGE COLUMNS(YEAR(birthday)) (" . // <- ココ
                " PARTITION p2020 VALUES LESS THAN 2021," .
                " PARTITION p2021 VALUES LESS THAN 2022," .
                " PARTITION p2022 VALUES LESS THAN 2023," .
                // (未来のどこまでパーティションを用意するかはお好みで)
                " PARTITION pmax VALUES LESS THAN MAXVALUE);";
            DB::getPdo()->exec($ddl);
        });
    }

パーティション設定の戻し方に注意

down()メソッドでPartitionを削除（Drop）してしまうと、Partitionに属するデータも消えてしまうので、パーティションの解除（Remove）を行うように設定する。これでmigrate:rollbackしてもデータが消える心配がない。rollbackしたら全データ消えるっていう怖い思いをしたくなければこの方式を採用した方が良い。

実際にクエリに組み込む　けど　落とし穴が。。

Eloquentで生成されたクエリが↓のようになればOK

select * from users partition (p2022);

というわけで、安易な考えでモデルを修正

App/Models/User.php

<?php
namespace App\Models;
class User extends Models
{
    protected $table = 'users';

    public function scopeWherePartition($partition = " partition (p2022) ")
    {
        return $this->from('users' . $partition)
            ->where('xxxxx', 'xxxxxx')
            ->where('yyyy', 'yyyy')
            :
    }
}

すると、実行時にエラーが。。。

users partition (p2022) なんてテーブルは存在しないぞ！

と。。
"$this->from('users' . $partition)" の部分が展開されて
"users partition (p2022)"となっている。
正解じゃん！って思うんだけど、後ろにwhereやらjoinやらが続いていると
どうやらpartition()までがテーブル名だと解釈されるらしい。
試しに、whereを付けないで実行したら動いた。何この中途半端な仕様。

このよくわからん動きを解決する為に1時間ぐらい浪費した。。。

解決方法

お待ちかねの答えです。

<?php
namespace App\Models;
use Illuminate\Support\Facades\DB;

class User extends Models
{
    protected $table = 'users';

    public function scopeWherePartition($partition = " partition (p2022) ")
    {
        $table = $this->getTable() . $partition;
        return $this->from(DB::raw($table))
            ->where('xxxxx', 'xxxxxx')
            ->where('yyyy', 'yyyy')
            :
    }
}

答えも意外と単純でした、困ったときの DB::raw() です！

これでテーブル名とpartitionの指定が独立しました。めでたしめでたし。

自分は検索条件によってpartitionのpXXXXを算出するような関数を組んで、
参照するpartitionを勝手に指定するように組んでいます。

これでパフォーマンスが上がるとうれしい。。。

ではでは。