MySQL :: MySQL 8.0 リファレンスマニュアル

9.1 English
9.0 English
8.4 English
8.0 English
5.7 English

MySQL 8.0 リファレンスマニュアル / ... / ORDER BY の最適化

このページは機械翻訳したものです。

8.2.1.16 ORDER BY の最適化

このセクションでは、MySQL が ORDER BY 句を満たすためにインデックスを使用できるタイミング、インデックスを使用できない場合に使用される filesort 操作、および ORDER BY に関するオプティマイザから使用可能な実行計画情報について説明します。

セクション8.2.1.19「LIMIT クエリーの最適化」で説明されているように、LIMIT を使用する場合と使用しない場合で ORDER BY が異なる順序で行を返すことがあります。

ORDER BY を満たすためのインデックスの使用

場合によっては、MySQL でインデックスを使用して ORDER BY 句を満たし、filesort 操作の実行に伴う余分なソートを回避できます。

インデックスのすべての未使用部分と追加の ORDER BY カラムが WHERE 句の定数であるかぎり、ORDER BY がインデックスと完全に一致しない場合でもインデックスを使用できます。クエリーによってアクセスされるすべてのカラムがインデックスに含まれていない場合、インデックスアクセスが他のアクセス方法よりも安い場合にのみインデックスが使用されます。

(key_part1, key_part2) にインデックスがあると仮定すると、次のクエリーではインデックスを使用して ORDER BY 部分を解決できます。オプティマイザが実際にこれを行うかどうかは、インデックスに含まれていないカラムも読み取る必要がある場合に、インデックスの読取りがテーブルスキャンよりも効率的かどうかによって異なります。

このクエリーでは、(key_part1, key_part2) のインデックスにより、オプティマイザはソートを回避できます:
```
SELECT * FROM t1
  ORDER BY key_part1, key_part2;
```
ただし、クエリーでは、key_part1 および key_part2 よりも多くのカラムを選択できる SELECT * が使用されます。その場合、インデックス全体をスキャンしてテーブルの行を検索し、インデックスにないカラムを検索すると、テーブルをスキャンして結果をソートするよりコストがかかる可能性があります。その場合、オプティマイザはおそらくインデックスを使用しません。 SELECT * がインデックスカラムのみを選択した場合、インデックスが使用され、ソートは回避されます。

t1 が InnoDB テーブルの場合、テーブルの主キーは暗黙的にインデックスの一部であり、インデックスを使用してこのクエリーの ORDER BY を解決できます:
```
SELECT pk, key_part1, key_part2 FROM t1
  ORDER BY key_part1, key_part2;
```
このクエリーでは、key_part1 は定数であるため、インデックスを介してアクセスされるすべての行は key_part2 の順序であり、WHERE 句が選択的でテーブルスキャンよりも安価なインデックスレンジスキャンを行うことができる場合、(key_part1, key_part2) のインデックスはソートを回避します:
```
SELECT * FROM t1
  WHERE key_part1 = constant
  ORDER BY key_part2;
```
次の 2 つのクエリーでは、インデックスを使用するかどうかが、前述の DESC を使用しない同じクエリーと類似しています:
```
SELECT * FROM t1
  ORDER BY key_part1 DESC, key_part2 DESC;

SELECT * FROM t1
  WHERE key_part1 = constant
  ORDER BY key_part2 DESC;
```
ORDER BY の 2 つのカラムは、同じ方向 (ASC または両方の DESC) または反対方向 (ASC、一方の DESC) でソートできます。インデックスの使用条件は、インデックスの均一性は同じである必要があるが、実際の方向は同じである必要がないことです。

クエリーで ASC と DESC が混在している場合、インデックスで対応する昇順と降順の混合カラムも使用されていれば、オプティマイザはカラムにインデックスを使用できます:
```
SELECT * FROM t1
  ORDER BY key_part1 DESC, key_part2 ASC;
```
key_part1 が降順で key_part2 が昇順の場合、オプティマイザは (key_part1、key_part2) のインデックスを使用できます。 key_part1 が昇順で key_part2 が降順の場合は、これらのカラムにインデックスを使用することもできます (バックワードスキャンを使用)。セクション8.3.13「降順インデックス」を参照してください。
次の 2 つのクエリーでは、key_part1 が定数と比較されます。インデックスは、テーブルスキャンよりもインデックスレンジスキャンの方が安くなるように、WHERE 句が選択的である場合に使用されます:
```
SELECT * FROM t1
  WHERE key_part1 > constant
  ORDER BY key_part1 ASC;

SELECT * FROM t1
  WHERE key_part1 < constant
  ORDER BY key_part1 DESC;
```
次のクエリーでは、ORDER BY は key_part1 を指定しませんが、選択されたすべての行には定数の key_part1 値があるため、インデックスは引き続き使用できます:
```
SELECT * FROM t1
  WHERE key_part1 = constant1 AND key_part2 > constant2
  ORDER BY key_part2;
```

場合によっては、MySQL はインデックスを使用して ORDER BY を解決できませんが、インデックスを使用して WHERE 句に一致する行を見つけることができます。例:

このクエリーでは、異なるインデックスで ORDER BY を使用します:
```
SELECT * FROM t1 ORDER BY key1, key2;
```
クエリーでは、インデックスの連続していない部分で ORDER BY を使用します:
```
SELECT * FROM t1 WHERE key2=constant ORDER BY key1_part1, key1_part3;
```
行のフェッチに使用されるインデックスは、ORDER BY で使用されるインデックスとは異なります:
```
SELECT * FROM t1 WHERE key2=constant ORDER BY key1;
```
クエリーでは、インデックスカラム名以外の用語を含む式を使用して ORDER BY を使用します:
```
SELECT * FROM t1 ORDER BY ABS(key);
SELECT * FROM t1 ORDER BY -key;
```
クエリーによって多数のテーブルが結合され、ORDER BY のカラムは、行の取得に使用される最初の非定数テーブルのすべてではありません。 (これは EXPLAIN 出力で、const 結合型を持たない最初のテーブルです。)
クエリーの ORDER BY 式と GROUP BY 式が異なります。
ORDER BY 句で指定されたカラムの接頭辞にのみインデックスがあります。この場合、インデックスを使用してソート順序を完全には解決できません。たとえば、CHAR(20) カラムの最初の 10 バイトのみがインデックス付けされている場合、インデックスでは 10 バイトを超える値を区別できず、filesort が必要です。
インデックスには、行は順番に格納されません。たとえば、これは、MEMORY テーブルの HASH インデックスに当てはまります。

インデックスをソートに使用できるかどうかは、カラムエイリアスの使用によって影響を受けることがあります。カラム t1.a にインデックスが設定されているとします。次のステートメントでは、選択リスト内のカラム名は a です。 ORDER BY 内の a への参照と同様に、t1.a を参照するため、t1.a 上のインデックスを使用できます:

SELECT a FROM t1 ORDER BY a;

次のステートメントでも、選択リスト内のカラム名は a ですが、これはエイリアス名です。 ORDER BY 内の a への参照と同様に、ABS(a) を参照するため、t1.a 上のインデックスは使用できません:

SELECT ABS(a) AS a FROM t1 ORDER BY a;

次のステートメントでは、ORDER BY は、選択リスト内のカラムの名前でない名前を参照しています。ただし、t1 には a という名前のカラムがあるため、ORDER BY は t1.a を参照し、t1.a のインデックスを使用できます。 (当然ながら、結果のソート順序は、ABS(a) の順序とはまったく異なる可能性があります。)

SELECT ABS(a) AS b FROM t1 ORDER BY a;

以前は (MySQL 5.7 以下)、GROUP BY は特定の条件下で暗黙的にソートされていました。 MySQL 8.0 では発生しなくなったため、暗黙的ソートを抑制するために最後に ORDER BY NULL を指定する必要はなくなりました (前述のとおり)。ただし、クエリー結果は以前の MySQL バージョンとは異なる場合があります。特定のソート順序を生成するには、ORDER BY 句を指定します。

filesort を使用して ORDER BY を満たす

インデックスを使用して ORDER BY 句を満たすことができない場合、MySQL はテーブルの行を読み取ってソートする filesort 操作を実行します。 filesort は、クエリーの実行時に追加のソートフェーズを構成します。

MySQL 8.0.12 の時点で、filesort 操作用のメモリーを取得するために、オプティマイザは、MySQL 8.0.12 より前に行われた一定量の sort_buffer_size バイトを割り当てるのではなく、sort_buffer_size システム変数で指定されたサイズまで必要に応じて増分的にメモリーバッファを割り当てます。これにより、ユーザーは小さいソートに過剰なメモリー使用を考慮せずに、大きいソートを高速化するために sort_buffer_size を大きい値に設定できます。 (この利点は、マルチスレッド malloc が弱い Windows での複数の同時ソートでは発生しない場合があります。)

結果セットが大きすぎてメモリーに収まらない場合、filesort 操作は必要に応じて一時ディスクファイルを使用します。一部のタイプのクエリーは、完全にインメモリー filesort 操作に特に適しています。たとえば、オプティマイザは filesort を使用して、一時ファイルを使用せずに、次の形式のクエリー (およびサブクエリー) に対する ORDER BY 操作をメモリー内で効率的に処理できます:

SELECT ... FROM single_table ... ORDER BY non_index_column [DESC] LIMIT [M,]N;

このようなクエリーは、より大きな結果セットの少数の行のみを表示する web アプリケーションで一般的です。例:

SELECT col1, ... FROM t1 ... ORDER BY name LIMIT 10;
SELECT col1, ... FROM t1 ... ORDER BY RAND() LIMIT 15;

ORDER BY 最適化への影響

filesort が使用されていない低速な ORDER BY クエリーの場合は、max_length_for_sort_data システム変数を filesort のトリガーに適した値に下げてみてください。 (この変数の値を著しく高く設定すると、高いディスクアクティビティーと低い CPU アクティビティーの組み合わせが見られます。) この方法は、MySQL 8.0.20 の前にのみ適用されます。 8.0.20 では、max_length_for_sort_data は非推奨になりました。これは、オプティマイザの変更によって廃止され、効果がないためです。

ORDER BY 速度を向上するには、MySQL で、追加のソートフェーズではなく、インデックスを使用させることができるかどうかをチェックします。これが不可能な場合は、次の方法を試してください:

sort_buffer_size 変数値を増やします。理想的には、(ディスクへの書込みおよびマージパスを回避するために) 結果セット全体がソートバッファに収まるように値を十分に大きくする必要があります。

ソートバッファーに格納されているカラム値のサイズは、max_sort_length システム変数値の影響を受けることを考慮してください。たとえば、タプルに長い文字列カラムの値が格納されていて、max_sort_length の値を増やすと、ソートバッファータプルのサイズも増加し、sort_buffer_size を増やす必要がある場合があります。

(一時ファイルをマージするための) マージパスの数を監視するには、Sort_merge_passes ステータス変数を確認します。
一度に読み取られる行が増えるように、read_rnd_buffer_size 変数の値を増やします。
tmpdir システム変数を変更して、大量の空き領域のある専用ファイルシステムを指すようにします。変数値には、ラウンドロビン方式で使用される複数のパスをリストできます。この機能を使用して、複数のディレクトリに負荷を分散できます。パスは、Unix ではコロン文字 (:) で区切り、Windows ではセミコロン文字 (;) で区切ります。パスには、同じディスク上の異なるパーティションではなく、異なる物理ディスクにあるファイルシステム内のディレクトリを指定してください。

ORDER BY 実行計画情報使用可能

EXPLAIN (セクション8.8.1「EXPLAIN によるクエリーの最適化」を参照) では、MySQL がインデックスを使用して ORDER BY 句を解決できるかどうかを確認できます:

EXPLAIN 出力の Extra カラムに Using filesort が含まれていない場合、インデックスが使用され、filesort は実行されません。
EXPLAIN 出力の Extra カラムに Using filesort が含まれている場合、インデックスは使用されず、filesort が実行されます。

また、filesort が実行されると、オプティマイザのトレース出力に filesort_summary ブロックが含まれます。例:

"filesort_summary": {
  "rows": 100,
  "examined_rows": 100,
  "number_of_tmp_files": 0,
  "peak_memory_used": 25192,
  "sort_mode": "<sort_key, packed_additional_fields>"
}

peak_memory_used は、ソート中に一度に使用される最大メモリーを示します。これは、sort_buffer_size システム変数の値までの値ですが、必ずしも大きくなるとはかぎりません。 MySQL 8.0.12 より前の出力では、かわりに sort_buffer_size の値を示す sort_buffer_size が表示されます。 (MySQL 8.0.12 より前では、オプティマイザは常に sort_buffer_size バイトをソートバッファーに割り当てます。 8.0.12 の時点では、オプティマイザは、少量から始まり、必要に応じて sort_buffer_size バイトまで、ソートバッファメモリーを増分的に割り当てます。)

sort_mode 値は、ソートバッファー内のタプルの内容に関する情報を提供します:

<sort_key, rowid> : これは、ソートバッファータプルが、元のテーブル行のソートキー値と行 ID を含むペアであることを示します。タプルはソートキー値でソートされ、行 ID は、テーブルからの行の読み取りに使用されます。
<sort_key, additional_fields> : これは、ソートバッファータプルにソートキー値とクエリーによって参照されるカラムが含まれていることを示します。タプルはソートキー値でソートされ、カラム値は、タプルから直接読み取られます。
<sort_key, packed_additional_fields> : 前のバリアントと同様ですが、追加のカラムは固定長エンコーディングを使用するかわりに密接にパックされます。

EXPLAIN は、オプティマイザがメモリー内で filesort を実行するかどうかを区別しません。インメモリー filesort の使用は、オプティマイザのトレース出力で確認できます。 filesort_priority_queue_optimization を探します。オプティマイザのトレースについては、「MySQL Internals: Tracing the Optimizer」を参照してください。

PREV HOME UP NEXT