はじめに

前回の記事では、論理削除と物理削除のSQLパフォーマンスの違いについて書きました。

しかし論理削除と物理削除については、DBの製品によって違いで出る可能性があリます。
今回は一般的に最もよく使われることの多いMYSQLを使って実際にテーブルを作り、1000万件くらいダミーデータを入れて、計測・検証してみます。

また検証の前提条件として、いいね機能の実装を想定しています。

実行環境

OS： Windows 11 Pro
CPU：11th Gen Intel(R) Core(TM) i5-11320H @ 3.20GHz 2.50 GHz
Laravel：v8.83.27
PHP：v8.2.8
MySQL：v8.0.32
WSL： Ubuntu-22.04
Docker：v4.16.2
※Laravel sailで仮想環境を構築しています。

モデルを作成する

まず最初にテストを実行するテーブルのモデルを作成します。
今回はLaravelがデフォルトで準備してくれている、論理削除（ソフトデリート）の仕組みを利用したいので、必要な記述use SoftDeletesをモデルに加えます。
※物理削除をテストする際には、 use SoftDeletesを毎回、無効にする必要があります。

like.php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\SoftDeletes;

class Like extends Model
{
    use SoftDeletes;

    /**
     * @var string
     */
    protected $table = 'likes';
    /**
     * @var string[]
     **/
    protected $dates = ['deleted_at'];
    /**
     * @var string[]
     **/
    protected $fillable = [
        'user_id', 'likable_id', 'likable_type',
    ];
}

テストクラスの作成

モデルの作成が終わったら、次にユニットテスト用のクラスを作成します。
ユニットテストでは、いいねを消去した後、再度いいねを押すという一連の流れを想定しています。
論理削除と物理削除で、それぞれ処理件数が１件、5件、10件の場合で３通り作成しました。

※今回は、処理速度の計測ができれば良いので、assertionsは使いません。
※以下にコードを紹介しますが、長くなってしまうので5件と10件のケースは割愛いたします。

論理削除のテストクラス

SoftDeleteTest.php

class SoftDeleteTest extends TestCase
{
    use DatabaseTransactions;

    public function setUp(): void
    {
        parent::setUp();

        // 通常のデータベース接続を使用する
        $this->app['config']->set('database.default', 'mysql'); 
    }
    /**
     * @test
     */
    public function 論理削除()
    {
        // CRUD処理の速度を計測するために時間計測を開始
        $startTime = Date::now();

        // 論理削除の実行
        $matchThese = [
            'user_id' => 3300,
            'likable_id' => 8954,
            'likable_type' => 'post'
        ];
        $likes = Like::where($matchThese)->delete();

        // 削除したデータを復元
        $likes = Like::where($matchThese)->restore();

        // 時間計測を終了し、実行時間を取得
        $endTime = Date::now();
        $executionTime = $endTime->diffInMilliseconds($startTime);

        // 実行時間を出力
        echo "論理削除の実行時間: {$executionTime}ミリ秒\n";
    }
}

物理削除のテストクラス

HardDeleteTest.php

class HardDeleteTest extends TestCase
{
    use DatabaseTransactions;

    public function setUp(): void
    {
        parent::setUp();

        // 通常のデータベース接続を使用する
        $this->app['config']->set('database.default', 'mysql'); 
    }
    /**
     * @test
     */
    public function 物理削除()
    {
        // CRUD処理の速度を計測するために時間計測を開始
        $startTime = Date::now();

        // 物理削除の実行
        $matchThese = [
            'user_id' => 3300,
            'likable_id' => 8954,
            'likable_type' => 'post'
        ];
        $likes = Like::where($matchThese)->delete();

        // 削除したデータを再挿入
        $like = new Like();

        $like->create([
            'user_id' => 3300,
            'likable_id' => 8954,
            'likable_type' => 'post',
        ]);

        // 時間計測を終了し、実行時間を取得
        $endTime = Date::now();
        $executionTime = $endTime->diffInMilliseconds($startTime);

        // 実行時間を出力
        echo "物理削除の実行時間: {$executionTime}ミリ秒\n";
    }
}

▼参考記事
https://yutaro-blog.net/2022/03/10/laravel-softdelete/
https://qiita.com/kuropp/items/b7c6c068a90ec7bdcd73

MySQLで大量のダミーデータを作成

テストデータを作成する方法について調べていたところ、下記の記事を見つけました。

▼MySQLで大量のダミーデータを作成
https://zenn.dev/snowcait/scraps/78696bab371c6b

今回は、こちらの記事を参考にダミーデータを作成していきます。

①レコード数を指定するためのダミーテーブルを作成。（使い勝手がいいように 100 レコード生成）

mysql> CREATE TABLE dummy (
    -> id INT PRIMARY KEY AUTO_INCREMENT);
Query OK, 0 rows affected (0.19 sec)

mysql> INSERT INTO `dummy` () VALUES (),(),(),(),(),(),(),(),(),();
Query OK, 10 rows affected (0.01 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> INSERT INTO `dummy` () SELECT 0 FROM `dummy` `d1`, `dummy` `d2`;
Query OK, 100 rows affected (0.02 sec)
Records: 100  Duplicates: 0  Warnings: 0

mysql> DELETE FROM `dummy` LIMIT 10;
Query OK, 10 rows affected (0.04 sec)

mysql> SELECT COUNT(*) FROM `dummy`;
+----------+
| COUNT(*) |
+----------+
|      100 |
+----------+
1 row in set (0.01 sec)

②実際のテーブルにダミーデータを作成

mysql> INSERT INTO likes (user_id, likable_id, likable_type, created_at, updated_at)
    -> SELECT CEIL(RAND() * 10000), CEIL(RAND() * 10000), 'post', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
    -> FROM `dummy` `d1`, `dummy` `d2`, `dummy` `d3`;
Query OK, 1000000 rows affected (29.96 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

上記の通り、100万件のレコードを約30秒で作成することが出来ました。
likable_type = postとlikable_type = commentのデータをそれぞれ500万件ずつ作りたいので
②のSQLを計５回づつ実行し、合計1000万件のテストデータを生成します。

テストの実行結果

テストの準備が整ったので、実際にテストを実行します。
ユニットテストの処理速度にはバラつきがあるため、それぞれ３回づつ計測し平均値を算出しました。

物理削除

処理件数	INDEX1個	INDEX2個	INDEX3個	INDEX4個
1件	0.29	0.38	0.3	0.24
5件	0.83	2.03	1.66	2.36
10件	2.48	4.36	4.09	4.22

論理削除

処理件数	INDEX1個	INDEX2個	INDEX3個	INDEX4個
1件	0.05	0.16	0.15	0.15
5件	1.09	1.24	1.47	1.75
10件	1.96	7.21	6.12	6.11

まとめ

検証結果から以下の考察が得られました。

両者ともにINDEXの数が1個の時、最も処理速度が速い（論理削除の方が少しだけ速い）
両者ともにINDEXの数が2個以上になると、数を増やしても処理速度に有意な差は生じない
物理削除の傾きが直線的（リニア）であるのに対して、論理削除は非直線的（ノンリニア）
INDEXの数が2個以上必要で、且つ同時に処理する件数が多くなると想定される場合においては物理削除の方が望ましいと言えそう
いいねボタンの実装に関して言えば、同時に複数のいいねを処理することは想定していないため、どちらを選択しても問題なさそう

論理削除 vs 物理削除 PHP Laravelで検証してみた