【MySQL】【PHP】EXPLAIN ANALYZEの結果の簡易的なパーサー

　EXPLAIN ANALYZEは、SQLクエリの実行計画を解析するためのMySQLの機能でありクエリの最適化やボトルネックを特定するために便利です。
EXPLAIN ANALYZEはただのEXPLANと異なり、実際にクエリを実行してその結果についてまとめます。EXPLANは計画を、EXPLAIN ANALISYS は結果を見る感じです。

MySQL ：： MySQL 8.0 リファレンスマニュアル ：： 13.8.2 EXPLAIN ステートメント#explain-analyze
MySQL ：： MySQL EXPLAIN ANALYZE

EXPLAIN ANALYZE は結果を整理しにくいです、実際の使用例と結果が次です。

-- シーンに最後に使用された日時を付けて、最近使用された8件を取得するクエリです
-- 仮に高速化するならば、正規化を崩してscenesに最終使用日時を持たせるようにデータ構造を追加し、そちらを使うようにするのがベターです

-- クエリの実行計画と実際の実行結果を確認する	
EXPLAIN ANALYZE
-- scenesテーブルから情報を取得する
SELECT `scenes`.`scene_id`,                 -- シーンID
       `scenes`.`scene_name`,              -- シーン名
       `use_scene_histories`.`last_used_at` -- 最後に使用された日時
FROM `scenes`
-- use_scene_historiesテーブルから、それぞれのシーンに関して最後に使用された日時を取得するサブクエリ
LEFT JOIN (
    SELECT `scene_id`, MAX(created_at) AS last_used_at
    FROM `use_scene_histories`
    GROUP BY `scene_id`
) AS `use_scene_histories`
-- scenesのシーンIDとuse_scene_historiesのシーンIDが一致するものを結合
ON `scenes`.`scene_id` = `use_scene_histories`.`scene_id`
-- 削除されていないシーンのみを取得
WHERE `scenes`.`deleted_at` IS NULL
-- 最後に使用された日時の降順でソート
ORDER BY `use_scene_histories`.`created_at` DESC;

-> Limit: 8 row(s)  (actual time=20.418..20.419 rows=8 loops=1)
    -> Sort: use_scene_histories.created_at DESC, limit input to 8 row(s) per chunk  (actual time=20.409..20.410 rows=8 loops=1)
        -> Stream results  (cost=208390.60 rows=2062203) (actual time=6.549..19.802 rows=5589 loops=1)
            -> Nested loop left join  (cost=208390.60 rows=2062203) (actual time=6.543..18.292 rows=5589 loops=1)
                -> Filter: (scenes.deleted_at is null)  (cost=780.70 rows=556) (actual time=1.020..9.705 rows=5589 loops=1)
                    -> Table scan on scenes  (cost=780.70 rows=5557) (actual time=1.018..9.349 rows=5695 loops=1)
                -> Index lookup on use_scene_histories using  (scene_id=scenes.scene_id)  (actual time=0.000..0.000 rows=0 loops=5589)
                    -> Materialize  (cost=1128.30..1128.30 rows=3711) (actual time=7.744..7.874 rows=1005 loops=1)
                        -> Group aggregate: max(use_scene_histories.created_at)  (cost=757.20 rows=3711) (actual time=1.409..4.777 rows=1005 loops=1)
                            -> Index scan on use_scene_histories using use_scene_histories_scene_id_foreign  (cost=386.10 rows=3711) (actual time=1.345..4.342 rows=3711 loops=1)

　何をやっているのかは分かりますが、どこがネックとなって時間がかかっているかなどがわかりにくく、どこから手を付ければいいか悩みがちです。サブクエリとJOINを少し使っただけなので、これは比較的短いですがウィンドウ関数、JOIN、UNION、サブクエリなどが増えていくと大分巨大になります。このため EXPLAIN ANALYZE の結果をパースして各行を比べ安くすると便利です。実際にその様なパーサーの作成例が次です。

<?php


// 解析結果の行を表現するクラス
class ExplainAnalysisResultRow
{
    public function __construct(
        public readonly int        $line,          // 行番号
        public readonly string     $raw,           // 生のテキスト
        public readonly int        $indent,        // インデントの数
        public readonly float|null $cost,          // コスト
        public readonly int|null   $rows,          // 行数
        public readonly float      $actualTime,    // 実際の時間
        public readonly int        $actualRows,    // 実際の行数
        public readonly int        $actualLoops,   // 実際のループ数
    ){}
}

// 解析結果を解析・パースするクラス
class ExplainAnalysisResultParser
{
    // テキストから解析結果の行を解析し、そのリストを返す
    public function parseExplain(string|array $explainAnalysisResultRaw): array
    {
        // テキストを行ごとに分割する。もし行ごと分割済みならそのまま使う
        if(is_string($explainAnalysisResultRaw)){
            $lines = explode("\n", $explainAnalysisResultRaw);
        } else {
            $lines = $explainAnalysisResultRaw;
        }

        // 各行を解析し、解析結果の行のリストを作る
        $resultRowList = [];
        foreach($lines as $idx => $line) {
            // 正規表現を使って各行から必要な情報を抽出する
            preg_match("/^(?P<indent>\s*)->.*(?:\(cost=(?P<cost>\S+) rows=(?P<rows>\d+)\))? *\(actual time=(?P<actual_time>\S+) rows=(?P<actual_rows>\S+) loops=(?P<actual_loops>\d+)\)/", $line, $matches);
            // ちゃんとパースできるか怪しいので成功したか失敗したか都度表示する
            //　EXPLAIN ANALYZEの真の構文がわからないまま、自分が扱うクエリ全てに対応できればそれでいいものとして作っています
            if(!$matches) {
                echo "Failed to parse line: $line\n";
                continue;
            }
            echo "Success to parse line: $line\n";

            // パースした結果を比較しやすい値に変換します
            $matches['actual_time'] = $this->parseNumOrRangeDiff($matches['actual_time']);
            $matches['cost']        = isset($matches['cost']) ? $this->parseNumOrRangeAve($matches['cost']) : null;

            // 解析結果の行を表現するクラスに変換します
            $resultRow = new ExplainAnalysisResultRow(
                $idx + 1,
                $line,
                strlen($matches['indent']),
                $matches['cost'],
                isset($matches['rows']) ? (int)$matches['rows'] : null,
                $matches['actual_time'],
                $matches['actual_rows'],
                $matches['actual_loops']
            );
            // 解析結果の行をリストに追加します
            $resultRowList[] = $resultRow;
        }
        return $resultRowList;
    }

    // 数字または範囲の差分を解析する
    private function parseNumOrRangeDiff($v): float|int
    {
        if(is_numeric($v)) {
            return (float)$v;
        }
        $v = explode('..', $v);
        $v = array_map(function($v) {
            return (float)$v;
        }, $v);
        return $v[1] - $v[0];
    }

    // 数字または範囲の平均を解析する
    private function parseNumOrRangeAve($v): float|int
    {
        if(is_string($v) || is_numeric($v)) {
            return (float)$v;
        }
        $v = explode('..', $v);
        $v = array_map(function($v) {
            return (float)$v;
        }, $v);
        return ($v[1] + $v[0]) / 2;
    }
}

/***************
 *  使用例
 ***************/
// 解析結果文字列をこのスクリプトに直に埋め込みます
// 実行時には php このファイルのファイル名.php としてコンソールから実行します
$explainText = "
-> Limit: 8 row(s)  (actual time=20.418..20.419 rows=8 loops=1)
    -> Sort: use_scene_histories.created_at DESC, limit input to 8 row(s) per chunk  (actual time=20.409..20.410 rows=8 loops=1)
        -> Stream results  (cost=208390.60 rows=2062203) (actual time=6.549..19.802 rows=5589 loops=1)
            -> Nested loop left join  (cost=208390.60 rows=2062203) (actual time=6.543..18.292 rows=5589 loops=1)
                -> Filter: (scenes.deleted_at is null)  (cost=780.70 rows=556) (actual time=1.020..9.705 rows=5589 loops=1)
                    -> Table scan on scenes  (cost=780.70 rows=5557) (actual time=1.018..9.349 rows=5695 loops=1)
                -> Index lookup on use_scene_histories using <auto_key0> (scene_id=scenes.scene_id)  (actual time=0.000..0.000 rows=0 loops=5589)
                    -> Materialize  (cost=1128.30..1128.30 rows=3711) (actual time=7.744..7.874 rows=1005 loops=1)
                        -> Group aggregate: max(use_scene_histories.created_at)  (cost=757.20 rows=3711) (actual time=1.409..4.777 rows=1005 loops=1)
                            -> Index scan on use_scene_histories using use_scene_histories_scene_id_foreign  (cost=386.10 rows=3711) (actual time=1.345..4.342 rows=3711 loops=1)
";
// 解析結果を解析・パースします
$result = (new ExplainAnalysisResultParser())->parseExplain($explainText);
// 出てきた結果をやりたいことに合わせてなんやかんやします
// ここではクエリの中の特に時間がかかった処理を探すために、実行時間でソートしています
usort($result, function($a, $b) {
    return $a->actualTime <=> $b->actualTime;
});
// 実行結果を表示します。ここでは実行時間の昇順ソートなので
// コンソールの一番下に最も実行時間の長い枝についての情報が表示されます。
print_r($result);
//     [9] => ExplainAnalysisResultRow Object
//        (
//            [line] => 4
//            [raw] =>         -> Stream results  (cost=208390.60 rows=2062203) (actual time=6.549..19.802 rows=5589 loops=1)
//            [indent] => 8
//            [cost] => 0
//            [rows] => 0
//            [actualTime] => 13.253
//            [actualRows] => 5589
//            [actualLoops] => 1
//        )
// この4行目そのもの、あるいは4行目から繋がる枝葉にクエリの特に重い部分がある、と探しに行けます

　やっていることは EXPLAIN ANALYZE の結果を知りたいことを抜き出せる正規表現で分解して、それをインスタンスにして並び替えやすくしているだけです。EXPLAIN ANALYZE の詳しい構文がわからずとも見えているもの全てを扱える正規表現があれば、とりあえず解析の助けになります。

　この様な簡易的なパーサーを使用することで EXPLAIN ANALYZE の結果を効果的に解析・整理することができます。特にクエリの実行時間が長い場合や複数のテーブルやサブクエリが絡む複雑なクエリの場合にどの部分の最適化が必要なのかを迅速に特定することができます。これにより、ータベースのパフォーマンスチューニングの効率が向上します。

注意点として、このパーサーは簡易的なものであるため、すべてのEXPLAIN ANALYZEの結果に対応しているわけではありません。必要に応じて正規表現のパターンや解析ロジックを調整することで、より高度な解析が可能になります。