MySQLにおける(JOIN/ORDER BY)文のクエリ処理と最適化方法

EXPLAIN ステートメントは、MySQL クエリ ステートメント プロセスと EXPLAIN ステートメントの基本概念および最適化で紹介されており、遅いクエリの例が示されています。

上記のクエリでは、10,000 件を超えるレコードをチェックする必要があり、一時テーブルとファイルソートを使用していることがわかります。このようなクエリは、ユーザー数が急増すると悪夢になります。

このステートメントを最適化する前に、まず SQL クエリの基本的な実行プロセスを理解しましょう。

1. アプリケーションはMySQL APIを介してMySQLサーバーにクエリコマンドを送信し、それが解析されます。

2. 権限を確認し、MySQL オプティマイザーで最適化します。解析と最適化の後、クエリ コマンドは CPU で実行でき、キャッシュできるバイナリ クエリ プランにコンパイルされます。

3. インデックスがある場合は、まずインデックスをスキャンします。データがインデックスでカバーされている場合は、追加の検索は必要ありません。そうでない場合は、インデックスに基づいて対応するレコードを見つけて読み取ります。

4. 関連するクエリがある場合、クエリの順序は、最初のテーブルをスキャンして条件を満たすレコードを見つけ、次に2番目のテーブルをスキャンして、最初のテーブルと2番目のテーブルの関連するキー値に従って条件を満たすレコードを見つけ、この順序でループします。

5. クエリ結果を出力し、バイナリログを記録する

当然のことながら、適切なインデックスを使用すると、検索が大幅に簡素化され、高速化されます。上記のクエリ ステートメントを見てみましょう。条件付きクエリに加えて、関連クエリと ORDER BY (並べ替え操作) もあります。

それでは、結合と ORDER BY がどのように機能するかを詳しく見てみましょう。MySQL には、結合とデータの並べ替えを処理する 3 つの方法があります。

最初の方法はインデックスに基づき、2 番目は最初の非定数テーブルをファイルソート (クイックソート) し、3 番目は結合クエリの結果を一時テーブルに入れてからファイルソートを実行する方法です。

注 1: 定数テーブルの詳細については、『MySQL 開発者マニュアル: Consts and Constant Tables』を参照してください。
注 2: filesort とは何ですか? これは文字通りのファイル ソートではありません。filesort には 2 つのモードがあります。
1. モード 1: ソートされた要素が出力されるデータをカバーします。ソート結果は順序付けられたシーケンス要素の文字列であり、追加のレコードの読み取りは必要ありません。
2. モード 2: ソート結果はキーと値のペアのシーケンス <sort_key, row_id> であり、レコードはこれらの row_id を通じて読み取られます (ランダム読み取り、非効率的)。
注3: 一時テーブルの詳細については、MySQL開発マニュアル「MySQLが内部一時テーブルを使用する方法」を参照してください。

最初の方法は、ORDER BY が依存する列のインデックスが最初の非定数テーブルに存在する場合に使用されます。この場合、すでに順序付けされたインデックスを直接使用して、関連付けられたテーブルのデータを見つけることができます。この方法は、追加の並べ替えアクションが不要なため、最高のパフォーマンスを発揮します。

2 番目の方法は、ORDER BY が依存するすべての列が最初のクエリ テーブルに属し、インデックスがない場合に使用されます。この場合、最初に最初のテーブルのレコードに対してファイルソートを実行し (モードはモード 1 またはモード 2 のいずれか)、順序付けされた行インデックスを取得してから、関連するクエリを実行します。ファイルソートの結果は、システム変数 sort_buffer_size (通常は約 2M) に応じて、メモリ内またはハード ディスク上に存在する場合があります。

3 番目の方法は、ORDER BY の要素が最初のテーブルに属していない場合に使用されます。関連付けられているクエリの結果を一時テーブルに格納し、一時テーブルでファイルソートを実行する必要があります。

3 番目の方法の一時テーブルは、メモリ内テーブルまたはハードディスク上にある場合があります。通常、ハードディスク (ディスク上のテーブル) は次の 2 つの状況で使用されます。

（1）BLOBとTEXTデータ型の使用

（２）メモリテーブルの占有量がシステム変数tmp_table_size/max_heap_table_sizeの制限（通常16M程度）を超えているため、ハードディスク上にしか配置できない。

上記のクエリ実行プロセスと方法から、filesortの使用とtemporaryの使用がクエリのパフォーマンスに重大な影響を与える理由を明確に理解できるはずです。データ型やフィールド設計に問題がある場合、

クエリ対象のテーブルと結果に大きなデータ フィールドがあり、適切なインデックスが利用できない場合は、大量の IO 操作が生成される可能性があります。これがクエリ パフォーマンスの低下の根本的な原因です。

記事の冒頭のクエリ例に戻ると、明らかに最も効率の悪い 3 番目の方法が使用されています。実行して試す必要がある最適化方法は次のとおりです。

1. users.fl_noのインデックスを追加し、selectとwhereで使用されるフィールドのインデックスを作成します。

2. users.fl_noをuser_profileテーブルに転送するか、冗長フィールドとして追加します。

3. TEXT タイプのフィールドを削除します。TEXT は、中国語の場合は VARCHAR (65535) または VARCHAR (20000) に置き換えることができます。

4. filesort の使用を排除できない場合は、sort_buffer_size を増やして IO 操作の負荷を軽減します。

5. 最初のテーブルでカバーされているインデックスを使用してソートを試みます。それでもうまくいかない場合は、ソート ロジックを MySQL から PHP/Java プログラムに移動して実行します。

最適化方法 1、2、3 を実装した後、EXPLAIN の結果は次のようになります。

注: 簡単な PHP アプリケーションを作成し、Siege を使用してテストすると、クエリの効率が 3 倍以上向上します。

以上がこの記事の全内容です。皆様の勉強のお役に立てれば幸いです。また、123WORDPRESS.COM を応援していただければ幸いです。