MySQL Order Byインデックスの最適化方法
3476 ワード
ORDER BYはインデックスの順序と正確に一致しないが、インデックスは、使用されないインデックス部分とすべての追加のORDER BYフィールドがWHERE句に含まれる限り、使用されることができる.
インデックスを使用するMySQL Order Byの次のクエリでは、ORDER BYまたはGROUP BYセクションを解決するためにインデックスが使用されます.
インデックスを使用しないMySQL Order By別の場合、MySQLはORDER BYを満たすためにインデックスを使用できません.ただし、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のフィールドはすべて最初の非常数のテーブルからではありません(つまり、EXPLAIN分析の結果、最初のテーブルの接続タイプはconstではありません).
*異なるORDER BY式とGROUP BY式が使用されています.
*テーブルインデックスのレコードは、順序付けされていません.たとえば、HASHとHEAPテーブルはこうです.
EXPLAIN SELECTを実行することで...ORDER BYでは、MySQLがクエリーでインデックスを使用しているかどうかがわかります.Extraフィールドの値がUsing filesortの場合、MySQLはインデックスを使用できません.詳しくは「7.2.1 EXPLAIN Syntax(Get Information Abouta SELECT)」をご覧ください.結果をソートする必要がある場合、MySQL 4.1では以前、次のfilesortアルゴリズムが使用されていました.
この近似アルゴリズムの1つの問題は,データベースが2回のレコードを読み出したことである:1回はWHERE節を推定した場合,2回目はソート時である.1回目はレコードの読み取りに成功したが(例えば、全テーブルスキャンを1回行った)、2回目はランダムな読み取りであった(インデックスキーは順序が整っているが、記録はなかった).MySQL 4.1および更新バージョンでは、filesort最適化アルゴリズムは、インデックスキー値とレコードの場所だけでなく、クエリーで要求されるフィールドも記録に含めるために使用されます.これにより、記録の読み取りが2回必要になることを回避します.改善されたfilesortアルゴリズムの方法は、次のようになります.
1.以前と同様に、WHERE文に一致するレコードを読み出します.
2.各レコードに対して、1つの対応するレコードが記録されている.インデックスキー値、レコード位置、およびクエリーに必要なすべてのフィールドを含むメタグループ'情報.
3.インデックスキーに基づいて「タプル」情報をソートします.
4.レコードを順番に読み出すが、データテーブルからもう一度読み込むのではなく、ソートされた「メタグループ」リストからレコードを読み出す.
改良されたfilesortアルゴリズムを使用すると、「メタグループ」は「対」よりも長いスペースを必要とし、ソートバッファに置くのに適したサイズは少ない(バッファのサイズはsort_buffer_sizeの値によって決まる).このため、I/O操作をより多く行う必要があり、改善されたアルゴリズムがより遅くなる可能性があります.これを遅らせることを避けるために、この最適化方法は、「メタグループ」内の追加のフィールドのサイズの合計がシステム変数max_を超えるようにソートするためにのみ使用される.length_for_sort_dataの場合(この変数の値が高すぎる表象は、高ディスク負荷低CPU負荷である).ORDER BYの速度を向上させるには、まずMySQLが追加のソートプロセスではなくインデックスを使用できるかどうかを確認します.インデックスが使用できない場合は、次のポリシーに従ってみてください.
*sort_を追加buffer_sizeの値.
*read_を追加rnd_buffer_sizeの値.
*tmpdirを変更し、空き容量の多い専用ファイルシステムを指します.
MySQL 4.1または更新を使用する場合、このオプションでは、複数のパスをループ形式で使用できます.各パス間はUnixでコロン(':')で区切られ、Windows、NetWare、OS/2ではセミコロン(';')で区切られています.このプロパティを使用して、負荷をいくつかのディレクトリに平均的に割り当てることができます.注:これらのパスは、同じ物理ディスク上の異なるディレクトリではなく、異なる物理ディスク上に分散されたディレクトリでなければなりません.
インデックスを使用する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 BYを満たすためにインデックスを使用できません.ただし、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のフィールドはすべて最初の非常数のテーブルからではありません(つまり、EXPLAIN分析の結果、最初のテーブルの接続タイプはconstではありません).
*異なるORDER BY式とGROUP BY式が使用されています.
*テーブルインデックスのレコードは、順序付けされていません.たとえば、HASHとHEAPテーブルはこうです.
EXPLAIN SELECTを実行することで...ORDER BYでは、MySQLがクエリーでインデックスを使用しているかどうかがわかります.Extraフィールドの値がUsing filesortの場合、MySQLはインデックスを使用できません.詳しくは「7.2.1 EXPLAIN Syntax(Get Information Abouta 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' 。
この近似アルゴリズムの1つの問題は,データベースが2回のレコードを読み出したことである:1回はWHERE節を推定した場合,2回目はソート時である.1回目はレコードの読み取りに成功したが(例えば、全テーブルスキャンを1回行った)、2回目はランダムな読み取りであった(インデックスキーは順序が整っているが、記録はなかった).MySQL 4.1および更新バージョンでは、filesort最適化アルゴリズムは、インデックスキー値とレコードの場所だけでなく、クエリーで要求されるフィールドも記録に含めるために使用されます.これにより、記録の読み取りが2回必要になることを回避します.改善されたfilesortアルゴリズムの方法は、次のようになります.
1.以前と同様に、WHERE文に一致するレコードを読み出します.
2.各レコードに対して、1つの対応するレコードが記録されている.インデックスキー値、レコード位置、およびクエリーに必要なすべてのフィールドを含むメタグループ'情報.
3.インデックスキーに基づいて「タプル」情報をソートします.
4.レコードを順番に読み出すが、データテーブルからもう一度読み込むのではなく、ソートされた「メタグループ」リストからレコードを読み出す.
改良されたfilesortアルゴリズムを使用すると、「メタグループ」は「対」よりも長いスペースを必要とし、ソートバッファに置くのに適したサイズは少ない(バッファのサイズはsort_buffer_sizeの値によって決まる).このため、I/O操作をより多く行う必要があり、改善されたアルゴリズムがより遅くなる可能性があります.これを遅らせることを避けるために、この最適化方法は、「メタグループ」内の追加のフィールドのサイズの合計がシステム変数max_を超えるようにソートするためにのみ使用される.length_for_sort_dataの場合(この変数の値が高すぎる表象は、高ディスク負荷低CPU負荷である).ORDER BYの速度を向上させるには、まずMySQLが追加のソートプロセスではなくインデックスを使用できるかどうかを確認します.インデックスが使用できない場合は、次のポリシーに従ってみてください.
*sort_を追加buffer_sizeの値.
*read_を追加rnd_buffer_sizeの値.
*tmpdirを変更し、空き容量の多い専用ファイルシステムを指します.
MySQL 4.1または更新を使用する場合、このオプションでは、複数のパスをループ形式で使用できます.各パス間はUnixでコロン(':')で区切られ、Windows、NetWare、OS/2ではセミコロン(';')で区切られています.このプロパティを使用して、負荷をいくつかのディレクトリに平均的に割り当てることができます.注:これらのパスは、同じ物理ディスク上の異なるディレクトリではなく、異なる物理ディスク上に分散されたディレクトリでなければなりません.