Skip navigation links
5.2.9 MySQL による LIMIT の最適化 »
Section Navigation      [Toggle]

5.2.8. MySQL による ORDER BY の最適化

余分なソートを行わずに ORDER BY または GROUP BY の要求に応じるために、MySQL はインデックスを使用する場合があります。

全ての使用されていないインデックス部分と他の部分が WHERE 節内で定数であるカラムである場合、ORDER BY がインデックスに完全にマッチしない場合でもこのインデックスを使用できます。 次のクエリではインデックスを使用して ORDER BY / GROUP BY 部分を解決します。 

SELECT * FROM t1 ORDER BY key_part1,key_part2,...
SELECT * FROM t1 WHERE key_part1=constant ORDER BY key_part2
SELECT * FROM t1 WHERE key_part1=constant GROUP BY key_part2
SELECT * FROM t1 ORDER BY key_part1 DESC,key_part2 DESC
SELECT * FROM t1 WHERE key_part1=1 ORDER BY key_part1 DESC,key_part2 DESC

MySQL で ORDER BY の解決にインデックスを使用できない場合は以下のとおりです（この場合も MySQL は WHERE 節の条件に一致するレコードの検索にインデックスを使用します）。

  • 複数のキーに対して ORDER BY を実行する場合。

    SELECT * FROM t1 ORDER BY key1,key2

  • 連続しないキー部分に対して ORDER BY を実行する場合。

    SELECT * FROM t1 WHERE key2=constant ORDER BY key_part2

  • ASC と DESC が混在している場合。

    SELECT * FROM t1 ORDER BY key_part1 DESC,key_part2 ASC

  • レコードの取り出しに使用されるキーが ORDER BY の実行に使用されるキーと異なる場合。

    SELECT * FROM t1 WHERE key2=constant ORDER BY key1

  • ORDER BY で多くのテーブルとカラムを結合していて、それら全てがレコードの取り出しに使用される最初の非 const テーブルではない場合（これは EXPLAIN で出力される最初のテーブルで、かつ、const メソッドを使用していないテーブル）。

  • ORDER BY と GROUP BY 式が異なる場合。

  • 使用されたテーブルインデックスが、並び順にレコードを格納していないインデックスタイプの場合（HEAP テーブルの HASH インデックスなど）。

MySQL で結果のソートが必要な場合は、以下のアルゴリズムが使用されます。

  • キーまたはテーブルスキャンに従ってすべてのレコードが読み取られる。 WHERE 節に一致しないレコードはスキップされる。

  • ソートキーがバッファ（サイズ sort_buffer）に格納される。

  • バッファが満杯になると、qsort が実行され結果がテンポラリファイルに格納される。ポインタはソートブロックに保存される（すべてのレコードがソートバッファに適合する場合は、テンポラリファイルが作成されない）。

  • すべてのレコードが読み取られるまで上記項目が反復される。

  • MERGEBUFF（7）領域まで、別のテンポラリファイルの 1 ブロックにマルチマージが実行される。最初のファイルの全ブロックが 2 つめのファイルに配置されるまで反復される。

  • 残りが MERGEBUFF2（15）ブロック未満になるまで、以下が反復される。

  • 最終マルチマージでは、レコードに対するポインタ（ソートキーの最終部分）のみが結果ファイルに書き込まれる。

  • 次に、sql/records.cc のコードが使用され、結果ファイルのポインタによってソートされた順序で読み取りが行われる。これを最適化するためローポインタの大きなブロックを読み込み、そのソートを行ってからソートされた順序でレコードバッファにレコードを読み取る（read_rnd_buffer_size）。

EXPLAIN SELECT ... ORDER BY を使用すると、MySQL でインデックスを使用してクエリを解決できるかどうかをチェックできます。extra カラムに Using filesort が出力された場合は、MySQL で ORDER BY の解決にインデックスを使用できません。 See 項5.2.1. 「EXPLAIN 構文（SELECT に関する情報の取得）」。

さらに ORDER BY の速度を上げる必要がある場合はまず、ソートフェーズを実行する必要なく MySQL でインデックスを使用できるかどうかを調べます。これが不可能な場合は、以下を実行できます。

  • sort_buffer_size 変数の値を増やす。

  • read_rnd_buffer_size 変数の値を増やす。

  • tmpdir に空き領域が大量にある専用ディスク上のディレクトリを指定する。 MySQL 4.1 以降を使用している場合、tmpdir に対してコロン :（Windows の場合はセミコロン ;）で区切ったパスの一覧を設定することで、複数の物理ディスク間の負荷を分散させることができる。この物理ディスクは、ラウンドロビン方法で使用される。 注意: これらのパスは、同一ディスクの複数のパーティションではなく、異なる物理ディスクである必要がある。

デフォルトでは、クエリで ORDER BY x,y[,...] と指定した場合と同様に MySQL によってすべての GROUP BY x,y[,...] クエリがソートされます。ORDER BY 節を明示的に記述した場合、ソートは発生するものの、MySQL はスピードを損なうことなくそれを最適化します。 クエリに GROUP BY が含まれていて、もし結果のソートのオーバヘッドを回避したいならば、ORDER BY NULL を指定することでソートを抑止できます。 

INSERT INTO foo SELECT a,COUNT(*) FROM bar GROUP BY a ORDER BY NULL;

This is a translation of the MySQL Reference Manual that can be found at dev.mysql.com. The original Reference Manual is in English, and this translation is not necessarily as up to date as the English version.