MySQL DISTINCTの基本実装原理の詳細な説明

序文

DISTINCT は、GROUP BY 操作の実装と非常によく似ていますが、GROUP BY の後には各グループから 1 つのレコードのみが取得される点が異なります。したがって、DISTINCT の実装は基本的に GROUP BY の実装と同じであり、大きな違いはありません。同じことは、ルーズ インデックス スキャンまたはコンパクト インデックス スキャンでも実現できます。もちろん、インデックスのみを使用して DISTINCT を完了できない場合、MySQL は一時テーブルを通じてのみそれを完了できます。

ただし、GROUP BY との 1 つの違いは、DISTINCT ではソートが不要であることです。つまり、DISTINCT 操作のみを実行するクエリがインデックスのみを使用して操作を完了できない場合、MySQL は一時テーブルを使用してデータを「キャッシュ」しますが、一時テーブル内のデータに対してファイルソート操作は実行しません。

もちろん、GROUP BY を使って DISTINCT 実行時にグループ化を行ったり、MAX などの集計関数を使用したりする場合は、filesort を避けることはできません。

以下では、いくつかの簡単なクエリ例を使用して、DISTINCT の実装を説明します。

1. まず、ルーズ インデックス スキャンを通じて実行される DISTINCT 操作を見てみましょう。

sky@localhost : 例 11:03:41> EXPLAIN SELECT DISTINCT group_id 
  -> グループメッセージGから
************************** 1. 行 ****************************
      id: 1
 SELECT_type: シンプル
    テーブル: グループメッセージ
     タイプ: 範囲
可能なキー: NULL
     キー: idx_gid_uid_gc
   キーの長さ: 4
     参照: NULL
     行数: 10
    追加: グループ化にインデックスを使用する
セット内の 1 行 (0.00 秒)

実行プランの追加情報が「group-by にインデックスを使用」であることは明らかです。これは何を意味するのでしょうか? GROUP BY 操作を実行していないのに、実行プランで GROUP BY がインデックスを通じて実行されると表示されるのはなぜですか?

実は、これは DISTINCT の実装原則に関係しています。DISTINCT を実装するプロセスではグループ化も必要であり、その後、各グループから 1 つのデータが取得されてクライアントに返されます。ここでの追加情報は、MySQL がルーズ インデックス スキャンを使用して操作全体を完了することを示しています。

もちろん、MySQL クエリ オプティマイザーがもっとユーザーフレンドリーになって、ここの情報を「distinct にインデックスを使用する」に変更できれば、もっとわかりやすくなると思います。

2. コンパクト インデックスをスキャンする例を見てみましょう。

sky@localhost : 例 11:03:53> EXPLAIN SELECT DISTINCT user_id 
  -> グループメッセージから
  -> ここで、グループID = 2G
************************** 1. 行 ****************************
      id: 1
 SELECT_type: シンプル
    テーブル: グループメッセージ
     タイプ: ref
可能なキー: idx_gid_uid_gc
     キー: idx_gid_uid_gc
   キーの長さ: 4
     参照: 定数
     行数: 4
    追加: WHERE の使用; インデックスの使用
セット内の 1 行 (0.00 秒)

ここでの表示は、コンパクト インデックス スキャンを通じて GROUP BY を実装した場合とまったく同じです。実際、このクエリの実装中に、MySQL はストレージ エンジンに group_id = 2 のすべてのインデックス キーをスキャンさせてすべての user_id を取得し、インデックスのソートされた特性を使用して、user_id のインデックス キー値が変更されるたびに 1 つの情報を保持させます。group_id = 2 のすべてのインデックス キーがスキャンされると、DISTINCT 操作全体が完了します。

3. 次に、インデックスのみを使用して DISTINCT を実現できない場合に何が起こるかを見てみましょう。

sky@localhost : 例 11:04:40> EXPLAIN SELECT DISTINCT user_id 
  -> グループメッセージから
  -> WHERE グループID > 1 かつ グループID < 10G
************************** 1. 行 ****************************
      id: 1
 SELECT_type: シンプル
    テーブル: グループメッセージ
     タイプ: 範囲
可能なキー: idx_gid_uid_gc
     キー: idx_gid_uid_gc
   キーの長さ: 4
     参照: NULL
     行数: 32
    追加: WHERE の使用; インデックスの使用; 一時の使用
セット内の 1 行 (0.00 秒)

MySQL がインデックスのみに依存して DISTINCT 操作を完了できない場合、対応する操作を実行するために一時テーブルを使用する必要があります。しかし、MySQL が一時テーブルを使用して DISTINCT を完了する場合、GROUP BY の処理とは少し異なり、ファイルソートが行われないことがわかります。

実際、MySQL のグループ化アルゴリズムでは、グループ化操作を完了するために必ずしもソートする必要はありません。これについては、上記の GROUP BY 最適化のヒントですでに説明しました。実際、MySQL はグループ化を実装し、ソートせずに DISTINCT 操作を完了するため、filesort ソート操作が欠落しています。

4. 最後に、GROUP BY と組み合わせてみます。

sky@localhost : 例 11:05:06> EXPLAIN SELECT DISTINCT max(user_id) 
  -> グループメッセージから
  -> WHERE グループID > 1 かつ グループID < 10
  -> group_idG でグループ化
************************** 1. 行 ****************************
      id: 1
 SELECT_type: シンプル
    テーブル: グループメッセージ
     タイプ: 範囲
可能なキー: idx_gid_uid_gc
     キー: idx_gid_uid_gc
   キーの長さ: 4
     参照: NULL
     行数: 32
    追加: WHERE の使用; インデックスの使用; 一時の使用; ファイルソートの使用
セット内の 1 行 (0.00 秒)

最後に、集計関数を GROUP BY と一緒に使用する例を見てみましょう。上記の 3 番目の例と比較すると、MAX 関数を使用したため、ファイルソートのソート操作が追加されていることがわかります。グループ化後の MAX 値を取得するには、インデックスを使用して操作を完了することはできないため、ソートによってのみ実行できます。

DISTINCT の実装は基本的に GROUP BY と同じなので、この記事では実装プロセスを示す図は描きません。

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