SQLの最適化では間違いがよく起こります。それはMySQLのExplain Planの使い方を理解していないからです。

1. 準備

キャラクターテーブル、装備テーブル、基本データテーブルの 3 つのテーブルを用意します。ここではチュートリアルで必要なフィールドのみを示します。ゲーム開発の過程では、これらのフィールド以外にも多くのフィールドが存在するはずです。皆さんも理解していると思います。

役割表:

テーブル `role` を作成します (
  `n_role_id` int デフォルト NULL,
  `s_name` varchar(255) 文字セット utf8mb4 COLLATE utf8mb4_bin デフォルト NULL
) エンジン=InnoDB デフォルト文字セット=utf8mb4 COLLATE=utf8mb4_bin;

装備リスト:

テーブル `equip` を作成します (
  `n_equip_id` int デフォルト NULL,
  `s_equip_name` varchar(255) 文字セット utf8mb4 COLLATE utf8mb4_bin デフォルト NULL,
  `n_config_id` int デフォルト NULL
) エンジン=InnoDB デフォルト文字セット=utf8mb4 COLLATE=utf8mb4_bin;

機器構成表

テーブル `dict_equip` を作成します (
  `n_equip_id` int デフォルト NULL,
  `s_desc` varchar(255) 文字セット utf8mb4 COLLATE utf8mb4_bin デフォルト NULL
) エンジン=InnoDB デフォルト文字セット=utf8mb4 COLLATE=utf8mb4_bin;

2. 説明計画の概要

説明プランを表示するには、次の 2 つの方法があります。

1. コマンド方式: explain sql、または desc sql、どちらのコマンドでも問題ありません。言葉が非常に直接的なので、explain を覚えた方が良いと思います。

2. Navicat ツールを使用します (他のツールについてはよく知りませんが、他にもいくつかあると思います)。クエリ ウィンドウで [Explain] をクリックします。キーワード explain を追加する必要はありません。

結果には多くの列が含まれており、その一部は null で、一部は値を持っていることがわかります。説明プランを理解していれば、SQL をターゲットに合わせて最適化できます。

3. フィールドの詳細な説明

説明プランには非常に多くのフィールドがあります。Navicat は 12 個のフィールドを表示します。そのうちのいくつかには特別な注意を払う必要があり、それらのいくつかで何が起こっているかを知るだけでも良いでしょう。

公式ドキュメントの説明: https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

1. id実行の順序

id は select の実行順序です。id が大きいほど優先度が高くなり、先に実行されます。id が同じ場合は、次のものが先に実行されます。

その理由は、サブクエリを実行するときに、最初に内部レイヤーがチェックされ、次に外部レイヤーがチェックされるためです。

選択
    de.*
から
    dict_equip で
どこ
    de.n_equip_id = (
        SELECT n_equip_id FROM equip e WHERE
            e.n_role_id = (
                SELECT n_role_id FROM role r WHERE r.s_name = 'Coriander' )
    ） 

上記の実行プランから、最初に role テーブルのクエリが実行され、次に equip が実行され、最後に dict_equip が実行されることがわかります。

2. select_type 選択タイプ

3. テーブルクエリにはテーブルまたは派生テーブルが含まれます

現在出力に使用されているテーブルの種類は次のとおりです。

<union M , N > ： 行データは結合後のデータです。IDはmからnの間です。

<derived*N*>: 派生テーブル

<サブクエリN >: サブクエリ

4. パーティションクエリにはパーティションが含まれます

パーティションテーブルを使用する場合にのみ使用できます。この高度な機能はまだ使用されていません。

5. クエリの種類

MySQL がテーブル内の必要な行を見つける方法を示します。これは「アクセス タイプ」とも呼ばれます。一般的なタイプは次のとおりです。

パフォーマンス: all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const

左から右へ、最悪から最高へ

最適化時に、クエリされるデータの量が多い場合は、フルテーブルスキャンを使用してインデックスの使用を回避できます。

少量のデータのみをクエリする場合は、インデックスを使用してみてください。

6. possible_keys: 使用されると予想されるインデックス

他のテーブルに関連付けられていない場合、クエリテーブルは使用できるインデックスです。

7. キー: 実際のクエリ処理で使用されるインデックス

クエリでMySQLが実際に使用するインデックスを表示します。インデックスが使用されていない場合はNULLとして表示されます。

8. キーの長さ

インデックスで使用されるバイト数を示します。この列は、クエリで使用されるインデックスの長さを計算するために使用できます。

9. ref は、テーブルのインデックスフィールドがどのテーブルのどのフィールドに関連付けられているかを示します。

注: equipテーブルとdict_equipテーブルの両方にインデックスを追加しました。インデックス列はn_equip_idです。

上記の実行プランからわかるように、インデックスが最初に使用されます。

10. 行: テーブルの統計と選択に基づいて、検索または読み取るレコードまたは行の数を大まかに見積もってください。値が小さいほど良いです。

たとえば、ある列にインデックスがなくても、一意です。このとき、検索中にテーブル全体を読み込むと、その値はテーブル内のデータ量と同じになります。このとき、最適化する必要があるのは、できるだけ少ないテーブルを読み込むことです。インデックスを追加することで、読み込まれる行数を減らすことができます。

11. フィルター: 読み取られた行のパーセンテージとして返される行のパーセンテージ。値が大きいほど、優れています。

たとえば、テーブル全体に 100 件のレコードが含まれている場合、テーブル内のすべてのデータが読み取られる可能性がありますが、一致するレコードは 1 つだけです。この場合、パーセンテージは 1 です。したがって、この比率をできるだけ大きくする必要があります。つまり、読み取られるデータはできるだけ有用である必要があり、IO には非常に時間がかかるため、未使用のデータの読み取りは避ける必要があります。

12. 追加

以下は最も一般的なものです

use filesort: MySQL では、行をソート順に取得する方法を判断するために追加のパスが必要です。この値が true の場合、インデックスは最適化される必要があります。

一時テーブルを使用する: クエリを解決するには、MySQL は結果を保持するための一時テーブルを作成する必要があります。典型的なケースとしては、クエリに、異なるケースで列をリストする GROUP BY 句と ORDER BY 句が含まれている場合です。

インデックスの使用: 実際の行をさらに検索して読み取ることなく、インデックス ツリーの情報のみを使用してテーブルから列情報を取得します。この戦略は、クエリが単一のインデックスの一部である列のみを使用する場合に使用できます。

where句を使用する: where句は行を制限するために使用されます

要約する

SQL 最適化の原則は、正確性を確保しながら時間を短縮することです。目標は明確です。目標を押し下げることで、迅速に実行したい場合は、できるだけ少ないデータを読み取らなければならないことがわかります。読み取るデータ量を減らすには、フィルタリングとインデックスの使用の 2 つの主要な方法しかありません。このようなルールの範囲内で最適化しますが、インデックスは余分なスペースを占有するため、2 つの関係のバランスを取る必要があることに注意してください。

MySQL の説明プランの使い方がわからないために発生する SQL 最適化エラーに関するこの記事はこれで終わりです。SQL 最適化と MySQL の説明プランの詳細については、123WORDPRESS.COM の以前の記事を検索するか、次の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。