MySQL Order By インデックスの最適化方法

ORDER BY がインデックスの順序と正確に一致しなくても、未使用のインデックス部分とすべての追加の ORDER BY フィールドが WHERE 句に含まれている限り、インデックスは引き続き使用できます。

インデックスを使用した MySQL 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 は ORDER を満たすためにインデックスを使用できません ただし、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=定数 ORDER BY key1
* 多数のテーブルが結合されており、読み取られたレコードの ORDER BY 内のフィールドのすべてが最初の非定数テーブル (つまり、EXPLAIN 内の) からのものではありません。 分析結果の最初のテーブルの結合タイプは const ではありません)。
* 異なる ORDER BY 式と GROUP BY 式が使用されています。
* テーブルインデックス内のレコードは順番に格納されません。たとえば、HASH テーブルや HEAP テーブルがこれに当てはまります。

EXPLAIN SELECT ... ORDERを実行することにより BY、MySQL がクエリでインデックスを使用するかどうかがわかります。 Extra フィールドの値が「Using filesort」の場合、MySQL はインデックスを使用できません。詳細は「7.2.1」を参照してください。 EXPLAIN 構文 (SELECT に関する情報の取得)」。結果を並べ替える必要がある場合、MySQL 4.1 より前では次の構文が使用されていました。 filesort アルゴリズム:

1. 根据索引键读取记录，或者扫描数据表。那些无法匹配 WHERE 分句的记录都会被略过。 
2. 在缓冲中每条记录都用一个‘对'存储了2个值（索引键及记录指针）。缓冲的大小依据系统变量 sort_buffer_size 的值而定。 
3. 当缓冲慢了时，就运行 qsort（快速排序）并将结果存储在临时文件中。将存储的块指针保存起来（如果所有的‘对'值都能保存在缓冲中，就无需创建临时文件了）。 
4. 执行上面的操作，直到所有的记录都读取出来了。 
5. 做一次多重合并，将多达 MERGEBUFF（7）个区域的块保存在另一个临时文件中。重复这个操作，直到所有在第一个文件的块都放到第二个文件了。 
6. 重复以上操作，直到剩余的块数量小于 MERGEBUFF2 (15)。 
7. 在最后一次多重合并时，只有记录的指针（排序索引键的最后部分）写到结果文件中去。 
8. 通过读取结果文件中的记录指针来按序读取记录。想要优化这个操作，MySQL将记录指针读取放到一个大的块里，并且使用它来按序读取记录，将记录放到缓冲中。
缓冲的大小由系统变量 read_rnd_buffer_size 的值而定。这个步骤的代码在源文件 `sql/records.cc' 中。

この近似アルゴリズムの問​​題は、データベースがレコードを 2 回読み取ることです。1 回目は WHERE を推定するためです。 文章を分割するとき、2回目は並べ替えるときです。 1 回目はレコードが正常に読み取られましたが (たとえば、全テーブル スキャンが実行された)、2 回目はランダムに読み取られました (インデックス キーは並べ替えられましたが、レコードは並べ替えられませんでした)。 MySQL 4.1 では 以降のバージョンでは、ファイルソート最適化アルゴリズムを使用して、インデックス キーの値とレコードの位置だけでなく、クエリに必要なフィールドも記録します。これにより、レコードを 2 回読み取る必要がなくなります。改良されたファイルソート アルゴリズムはおおよそ次のとおりです:
1. 前と同様に、WHERE 句に一致するレコードを読み取ります。
2. 各レコードについて、インデックス キー値、レコードの場所、クエリに必要なすべてのフィールドなど、対応する情報の「タプル」が記録されます。
3. インデックスキーに従って「タプル」情報をソートします。
4. レコードを順番に読み取ることは、データテーブルからレコードを再度読み取るのではなく、単に「タプル」のソートされたリストからレコードを読み取ることです。

改良されたファイルソートを使用する 元のアルゴリズムと比較すると、「タプル」は「ペア」よりも長いスペースを占有し、ソート バッファーに正確に収まることはほとんどありません (バッファーのサイズは sort_buffer_size によって決まります)。 値によって決定されます)。したがって、より多くの I/O 操作が必要となり、改良されたアルゴリズムの速度が遅くなる可能性があります。処理速度の低下を避けるため、この最適化は、ソートされた「タプル」内の追加フィールドのサイズの合計がシステム変数を超える場合にのみ使用されます。 max_length_for_sort_data の状況 (この変数の値を高く設定しすぎる場合の症状の 1 つは、ディスク負荷が高く、CPU 負荷が低いことです)。 ORDER BYを改善したい 速度はまず、MySQL が追加の並べ替えプロセスの代わりにインデックスを使用できるかどうかによって決まります。インデックスを使用できない場合は、次の戦略に従ってみてください:
* sort_buffer_size の値を増やします。
* read_rnd_buffer_size の値を増やします。
* tmpdir を変更して、多くの空き領域のある専用のファイル システムを指すようにします。
MySQL 4.1 以降を使用している場合、このオプションによりループ形式で複数のパスが許可されます。 Unix では、各パスはコロン (「:」) で区切られます。 Windows、NetWare、OS/2 セミコロン (';') を使用してください。この機能を使用すると、複数のディレクトリ間で負荷を均等に分散できます。注: これらのパスは、同じ物理ディスク上の異なるディレクトリではなく、異なる物理ディスクに分散されたディレクトリである必要があります。