6.2.12 ORDER BY最適化|無料CGI PHPサイト(新着情報、営業日カレンダーなど)

6.2.12. `ORDER BY`最適化

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

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

SELECT * FROM t1 
  ORDER BY key_part1,key_part2,... ;
    
SELECT * FROM t1 
  WHERE key_part1=constant 
  ORDER 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式が異なる場合。
使用されたテーブルインデックスが、並び順にレコードを格納していないインデックスタイプの場合。（MEMORYテーブルの HASHインデックスなど）。

EXPLAIN SELECT ... ORDER BYを使用すると、MySQL でインデックスを使用してクエリを解決できるかどうかをチェックできます。Extraカラムに Using filesortが出力された場合は、MySQL で ORDER BY の解決にインデックスを使用できません。項6.2.1. 「EXPLAINを使用して、クエリを最適化する」を参照してください。

ソートキー値と行ポジションだけでなく、クエリに必要なカラムまで記憶するfilesort最適化が使用されます。これにより行の2度読みを避けられます。filesortアルゴリズムは以下のように実行されます。

WHERE節とマッチする行を読む。
各行ごとに、クエリに必要なカラムとソートキー値と行ポジションを含むタプル値を記憶する。
ソートキー値でタプルを並べ替える
並べ替えられた順序で行を取得しますが、テーブルに2度アクセスするよりも、並べ替えられたタプルから必要なカラムを読み取ります。

MySQLの旧バージョンで使用されていたアルゴリズムよりも格段に改良されています。

遅滞を避けるため、この最適化はmax_length_for_sort_dataシステム変数の値をソートタプル内の余分なカラムのトータルサイズが超えない場合使用されます。(この変数が高く設定されると、活発なディスクアク活動に対して低いＣＰＵ活動といった状態が発生します。)

ORDER BY速度を上げたい場合、MySQLが余分な並び替えフレーズよりもインデックスを使用できるか確認してください。これが不可能な場合、以下の手段を試してみてください。

sort_buffer_size変数のサイズを大きくしてください。
read_rnd_buffer_size変数のサイズを大きくしてください。
tmpdirを変更することで、秋スペースの要領が多い専用のファイルシステムを示してください。この選択肢はラウンドロビン方式で複数のパスを受領します。Ｕｎｉｘでは、パスはコロンを含む文字(‘:’) で分けられ、ウィンドウズ、Netware, そして OS/2 ではセミコロンを含む文字(‘;’)で分けられるべきです。この特性を利用して複数のディレクトリに渡り負荷を分散することができます。注:パスは、同ディスクのパーティションで分けられた領域ではなく、異なる物理的なディスクのファイルシステム内のディレクトリに通じます。

ＭｙＳＱＬはデフォルトで、GROUP BY col1、col2, ...の全クエリをORDER BY col1、col2, ...で指定したかのように、クエリをソートします。同じカラムリストを含むORDER BY節を明示的に取り入れた場合、ソートが実行されるとはいえ、ＭｙＳＱＬは速度ペナルティなしに最適化します。クエリにGROUP BYが含まれていながら、結果のソートに費やすオーバーヘッドを避けたい場合、ORDER BY NULLを指定することでソートを実行しないようにすることができます。例 :

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

無料PHPプログラム

6.2.12. ORDER BY最適化

6.2.12. `ORDER BY`最適化