【SQL入門】SQL解析時間の短縮について #SQL

この記事では、《SQL解析時間の短縮》について、
業務を通して学習した内容を、備忘録としてまとめています。

『SQL解析』の仕組み
『SQL解析時間短縮』の対策

こういった内容についてまとめています。

※本記事は、自分で学習したことのまとめ用として書いています。
尚、解説で誤った点があれば、スローして頂ければ喜んでキャッチしますのでお願い致します。

遅くなる要因のメモリの使い方

➡︎ SQLが共有されていない

SQLの共有化について

SQLの解析処理には…

ハード解析
ソフト解析

の２種類があります。

この２つの解析は、以下のようなものとなっています。

--- ハード解析 ---

SQL解析に関連する全ての操作を１から処理していくもので…

文法チェック
構造解析
最適化（実行計画生成）
解析済みSQLのキャッシュ

といった処理を行っていきます。

要するに、『ハード解析』は…

実行計画を新たに生成する解析です。

生成した実行計画は、キャッシュされます。

--- ソフト解析 ---

すでに実行したSQLがあれば、プログラムコード (Pコード) もキャッシュされています。

それを使って実行するのが『ソフト解析』です。

すでに実行したSQLから一致するSQLを探索し、一致するSQLの実行計画をそのまま利用します。

『ハード解析』で行っていたSQL解析や、実行計画生成を行う必要がありません。

つまり・・・

『ハード解析』の割合を減らし、『ソフト解析』の割合を多くすることで…

キャッシュされている実行計画を利用する割合が大きくなります。

そのためには・・・

DBに、同じSQLと認識させ、実行計画をそのまま利用させる必要があります。

DBに "同じSQL" と認識させるには…？

大文字／小文字の使い方を統一する
インデントの入れ方を統一する

--- 例題 ---

SELECT * FROM EMPLOYEE;

select * from employee;

上記２つのSQLは・・・

実行結果は同じになりますが、データベース側では異なるSQLと判断されます。

【補足】DBは、ハッシュ値を比較してSQLを判断している

データベースでは、同じSQLか判断するために…

それぞれのSQLの『ハッシュ値』を比較して、判断しています。

ちなみに・・・

macOSでハッシュ値を計算すると、下記のようになりました。

$ echo "SELECT * FROM EMPLOYEE" | shasum;
ad47d3b42d84a6f793c461e34bfdb60317965f21

$ echo "select * from employee" | shasum;
e9082b866d6027bd5bd73127ba9bf43a98dac8c8

そのため・・・

下記のSQL解析が実行され、処理が遅くなります。

文法チェック
構造解析
最適化（実行計画生成）
解析済みSQLのキャッシュ

ですので・・・

大文字／小文字、インデントの使い方を統一し、ハッシュ値が変わらないようにする必要があります。

『SQL解析時間短縮』の対策【PHPの場合】

対策：『プリペアドステートメント』を使う

『プリペアドステートメント』を使い、実行したいSQL文をコンパイルすることで・・・

SQL文を固定し、ハッシュ値が変わらないようにします。

『プリペアドステートメントの使い方』について詳しく知りたい方は、
下記を参照してみて下さい。

》【PDO】PHPでデータベースに接続する方法【INSERT編】

プリペアドステートメント
SQL文で値がいつでも変更できるように、変更する箇所だけ変数のようにしたSQL文を作る仕組みのことです。通常『プリペアドステートメント』は『プレースホルダ』を使うために作られます。
プレースホルダ
ユーザが入力した内容を後から挿入するために、予め確保した場所のこと。

まとめ：基本コーディング

sample.php

<?php

try {

    // リクエストから得たスーパーグローバル変数をチェックするなどの処理
    $user_name = "ここにユーザー名が入ります";
    $password = "ここにパスワードが入ります";

    // データベース接続設定（MySQL）
    $pdo = new PDO(
        'mysql:host=サーバー名;dbname=DB名;charset=文字エンコード',
        'ユーザー名',
        'パスワード',
        [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
        ]
    );

        // パラメータ
        $spl = (
            'SELECT
                USER_NAME, PASSWORD
            FROM
                テーブル名
            WHERE
                AND USER_NAME = :USER_NAME
                AND PASSWORD = :PASSWORD'
        );

    // プリペアドステートメントを用意
    $stmt = $pdo->prepare($sql);

    // 値をバインドする
    $stmt->bindValue(':USER_NAME', $user_name, PDO::PARAMS_STR);
    $stmt->bindValue(':PASSWORD', $password, PDO::PARAMS_STR);

    // 結果を取得する
    $row = $prepare->fetch();

} catch (PDOException $e) {

    // エラー処理

}

【SQL入門】SQL解析時間の短縮について