BETA

[Laravel] MySQLでLaravel標準のSoftDeletesを使った論理削除とユニーク制約を両立させる方法

投稿日:2018-10-15
最終更新:2018-10-24
※この記事は外部サイト(https://qiita.com/mpyw/items/d3e25860ecaae...)からのクロス投稿です

スキーマの最適化

Before

Schema::create('users', function (BluePrint $table) {
    $table->bigIncrements();
    $table->string('screen_name')->index(); // ←論理削除があるので ->unique() にはできない
    $table->softDeletes();
});

After

MySQL5.7から使える生成カラムと,ユニーク制約がNULLには適用されないことを利用します。アプリケーション側のコードには,一切手を加える必要がありません。

Schema::create('users', function (BluePrint $table) {
    $table->bigIncrements();
    $table->string('screen_name');
    $table->softDeletes();

    // 論理削除されていれば NULL, されていなければ 1 になる生成カラムを定義
    $table->boolean('existence')->nullable()->storedAs('CASE WHEN deleted_at IS NULL THEN 1 ELSE NULL END');

    // screen_name と existence に対して複合ユニークインデックスを張る
    // (カーディナリティの低い existence を後ろに持ってくるべき)
    $table->unique(['screen_name', 'existence']);
});

カラムの命名に迷いますが, exists だと 10 を取りそうな雰囲気があるので,存在そのものを表す名詞形にして 1NULL っぽさを出すようにしてみました。

SELECTするSQLの最適化

Before

User::whereScreenName('mpyw')->first()

これだと

WHERE screen_name = 'mpyw' AND deleted_at IS NOT NULL

という条件がつくので,せっかく貼ったユニークインデックスがフルに利用されません。

After

User::whereScreenName('mpyw')->withTrashed()->whereExistence(true);

こっちなら

WHERE screen_name = 'mpyw' AND existence = 1

となり,理論上は最速のクエリになります。

参考資料

技術ブログをはじめよう Qrunch(クランチ)は、プログラマの技術アプトプットに特化したブログサービスです
駆け出しエンジニアからエキスパートまで全ての方々のアウトプットを歓迎しております!
or 外部アカウントで 登録 / ログイン する
クランチについてもっと詳しく

この記事が掲載されているブログ

@mpywの技術ブログ

よく一緒に読まれる記事

0件のコメント

ブログ開設 or ログイン してコメントを送ってみよう
目次をみる
技術ブログをはじめよう Qrunch(クランチ)は、プログラマの技術アプトプットに特化したブログサービスです
or 外部アカウントではじめる
10秒で技術ブログが作れます!