MYSQLクエリ最適化:インデックスを使用する
5338 ワード
インデックスはクエリーの速度を上げるための最も重要なツールです.もちろん他にもいくつかの技術がありますが、最も大きな性能の違いを引き起こすのは索引の正しい使用です.MySQLメールのリストでは、クエリをより速く実行する方法を常に尋ねています.ほとんどの場合、データテーブルにインデックスがあるかどうかを疑って、インデックスを追加した直後に問題を解決します.もちろん、いつもこのように簡単に問題を解決できるわけではないです.最適化技術はもともといつも簡単ではないからです.しかし、インデックスを使用していない場合は、多くの場合、他の方法を使って性能を向上させようとするのは時間の無駄です.まずインデックスを使用して最大の性能向上を取得し、他の技術が有用かどうかを確認します.
この部分はインデックスが何であり、インデックスがどのようにクエリの性能を向上させるかを述べています.また、いくつかの環境でインデックスが性能を低下させる可能性があることを議論し、データテーブルのインデックスを賢明に選択するための指針を提供します.次の部分では、クエリを実行する最も効率的な方法を見つけるために、MySQLクエリ最適化器について議論します.いくつかの優れた化学器の知識を理解し、どのようにインデックスを作成するかの補充として、私たちにとって有益です.いくつかのクエリを作成する方法は、実際にはインデックスを機能させません.一般的には、このような状況の発生を避けるべきです.インデックスの利点は、インデックスがどのように動作しているかを知ることを開始します.まず、インデックスなしのデータテーブルがあります.索引なしの表は、無秩序なデータセットにすぎません.例えば、図1に示すリードテーブルはインデックスを持たないテーブルであるため、特定の会社を検索する必要がある場合は、テーブルのデータラインごとに目標値と一致するかどうかを確認しなければならない.これは一回の完全なデータ表スキャンを引き起こすことができて、この過程はとても遅くて、もしこの表がとても大きいならば、しかし少量の条件に合う記録だけを含んで、効率はとても低いことができます.
図1:インデックスなしのリードテーブル
図2は同じデータテーブルですが、ロードテーブルのcompany_が追加されました.numデータ列の索引.この索引にはリードテーブルの各データ行の項目が含まれていますが、索引の項目はcompany_によるものです.num値の並び替え.現在、私たちはマッチングしたデータ項目を検索するためにプログレッシブではなく、インデックスを使用しています.私たちが会社13のすべてのデータ行を検索すると仮定します.インデックスのスキャンを開始し、同社の3つの値を見つけました.次に私たちは会社14のインデックス値に出会いました.私たちが探している値より大きいです.インデックス値は順序付けされていますので、14を含むインデックスレコードを読んだら、もうこれ以上のマッチング記録がないことが分かります.クエリーの操作が終了します.したがって、インデックスを使用して得られた効果は、マッチするデータ行がどこで終了し、他のデータ行を無視することができますか?もう一つの機能は、インデックスヘッドから線形スキャンを実行する必要がなくて、位置決定アルゴリズムを使用して第一の一致項目を検索することから生じる.この方法を用いることで,最初の一致の値を迅速に定位でき,大量の探索時間を節約できた.データベースは様々な技術を用いてインデックス値を迅速に定位したが、本論文ではこれらの技術に関心がなかった.ポイントは、それらが実現でき、インデックスが良いものです.
図2:索引後のリード表
あなたが聞きたいのですが、私たちはなぜデータ行を並べ替えずにインデックスをなくしましたか?これで同じ検索速度の改善ができるのではないですか?はい、表が一つのインデックスだけであれば、同じ効果が得られます.しかし、2番目のインデックスを追加することができます.2つの異なる方法でデータ行を並べ替えることはできません.(例えば、顧客名にインデックスを作成し、顧客ID番号または電話番号に別のインデックスを作成することを望むかもしれません.)データ行から分離された項目を索引として解決し、複数の索引を作成することができます.また、索引の行は、データ行よりも一般的に短いです.新しい値を挿入または削除すると、より短いインデックス値は、長いデータ行を移動する順序よりも移動しやすいです.異なるMySQLストレージエンジンのインデックス実装の詳細情報は異なる.例えば、MyISAMデータテーブルに対しては、テーブルのデータ行がデータファイルに保存され、インデックス値がインデックスファイルに保存されています.一つのデータテーブルには複数のインデックスがありますが、それらは同じインデックスファイルに保存されます.インデックスファイルの各インデックスは、データファイルに素早くアクセスするための並べ替えキーレコードを含んでいます.これと対照的に、BBBとInnoDB記憶エンジンは、データ行とインデックス値を分離するためにこの方法を使用していないが、それらはインデックスを並べ替えられた値セットとしても動作する.デフォルトでは、BDBエンジンは、単一ファイル記憶データとインデックス値を使用する.InnoDBは単一のデータテーブル空間を使用して、テーブル空間ですべてのInnoDBテーブルのデータとインデックスを管理します.InnoDBは各テーブルを自分のテーブル空間で作成するように配置できますが、それでもデータテーブルのデータとインデックスは同じテーブル空間ファイルに保存されます.
前の議論は、単一のテーブルクエリ環境におけるインデックスの利点を説明したが、この場合、テーブル全体のスキャンを低減することにより、インデックスを使用して検索の速度を著しく向上させた.複数表連結に関するクエリを実行すると、インデックスの価値がより高くなります.単一テーブルクエリでは、各データ列で検査したい値の数はテーブル内のデータ行の数です.複数のテーブルクエリでは、この数は、これらのテーブルのデータラインの数によって発生するので、大幅に上昇することができます.3つのインデックスがないテーブルt 1、t 2、t 3を持っていると仮定すると、各テーブルはそれぞれデータ列i 1、i 2、i 3を含み、各テーブルは1000行のデータ列を含み、その番号は1から1000までである.いくつかの値が一致するデータ行の組み合わせを検索するクエリは、次のようになります.
SELECT t 1.i 1、t 2.i 2、t 3 FROM t 1、t 2、t 3 WHERE t 1.i 1=t 2.i 2 AND t 2.i 1=t 3;
このクエリの結果は1000行、各データ行は3つの等しい値を含むべきです.インデックスがない場合は、このクエリを処理します.これらのテーブルを全部スキャンしないと、どのデータ行にどのような値が含まれているか分かりません.したがって、すべての組み合わせを試して、WHEREの条件に合った記録を調べなければなりません.可能な組み合わせの数は1000 x 1000 x(10億!)で、それはレコードの数の百万倍にマッチしています.これは大量の仕事を浪費した.この例では、インデックスが使用されていない場合、テーブルの記録が増加するにつれて、これらのテーブルの連結を処理するのにかかる時間がより速くなり、性能が悪いということを示しています.インデックスは、テーブルt 1の最初の行を選択し、そのデータ行の値を確認するために、クエリーに以下のように処理されるので、これらのデータテーブルをインデックスすることによって著しく速度を上げることができる.2.表t 2のインデックスを使用して、直接t 1の値にマッチするデータ行に位置します.同様に、表t 3のインデックスを使用して、表t 2の値に一致するデータ行に直接的に位置します.3.処理テーブルt 1の次の行は、前のプロセスを繰り返す.このような動作は、t 1のすべてのデータ行がチェックされるまで実行される.この場合には、テーブルt 1に対して完全なスキャンを実行していますが、t 2およびt 3においてインデックス検索を実行して、これらのテーブルから直接にデータ行を取得することができます.理論的にはこの方法で上のクエリを実行すると100万倍になります.もちろんこの例は結論を出すために人工的に作られたものです.しかし、それが解決した問題は現実的で、インデックスがないテーブルにインデックスを追加すると、驚くべき性能が向上します.MySQLには、いくつかのインデックスを使用する方法があります.・上記のように、WHERE条件を向上させるためにインデックスが使用されているデータ行のマッチングまたは連結操作を実行する際に、他のテーブルのデータ行と合致する検索速度です.・MIN()またはMAX()関数を使用したクエリーに対しては、インデックスデータ列の最小値または最大値は、各データ行を確認することなくすぐに見つけることができます.・MySQLは、インデックスを利用して、ORDER BYとGROUTP BY文の並べ替えとグループ化動作を迅速に実行します.・MySQLはインデックスを利用してクエリーから得られたすべての情報を読み取る場合があります.MyISAMテーブルのインデックスされた数値列を選択したとします.データテーブルから他のデータ列を選択する必要はありません.この場合、MySQLはインデックスファイルからインデックス値を読み出し、データファイルを読み込むと得られた値と同じ値を得る.同じ値を二回読み取る必要はないので、データファイルを考慮する必要はない.
転載:http://dev.yesky.com/381/2108381.shtml?412
転載先:https://www.cnblogs.com/adforce/archive/2012/06/02/2532267.html
この部分はインデックスが何であり、インデックスがどのようにクエリの性能を向上させるかを述べています.また、いくつかの環境でインデックスが性能を低下させる可能性があることを議論し、データテーブルのインデックスを賢明に選択するための指針を提供します.次の部分では、クエリを実行する最も効率的な方法を見つけるために、MySQLクエリ最適化器について議論します.いくつかの優れた化学器の知識を理解し、どのようにインデックスを作成するかの補充として、私たちにとって有益です.いくつかのクエリを作成する方法は、実際にはインデックスを機能させません.一般的には、このような状況の発生を避けるべきです.インデックスの利点は、インデックスがどのように動作しているかを知ることを開始します.まず、インデックスなしのデータテーブルがあります.索引なしの表は、無秩序なデータセットにすぎません.例えば、図1に示すリードテーブルはインデックスを持たないテーブルであるため、特定の会社を検索する必要がある場合は、テーブルのデータラインごとに目標値と一致するかどうかを確認しなければならない.これは一回の完全なデータ表スキャンを引き起こすことができて、この過程はとても遅くて、もしこの表がとても大きいならば、しかし少量の条件に合う記録だけを含んで、効率はとても低いことができます.
図1:インデックスなしのリードテーブル
図2は同じデータテーブルですが、ロードテーブルのcompany_が追加されました.numデータ列の索引.この索引にはリードテーブルの各データ行の項目が含まれていますが、索引の項目はcompany_によるものです.num値の並び替え.現在、私たちはマッチングしたデータ項目を検索するためにプログレッシブではなく、インデックスを使用しています.私たちが会社13のすべてのデータ行を検索すると仮定します.インデックスのスキャンを開始し、同社の3つの値を見つけました.次に私たちは会社14のインデックス値に出会いました.私たちが探している値より大きいです.インデックス値は順序付けされていますので、14を含むインデックスレコードを読んだら、もうこれ以上のマッチング記録がないことが分かります.クエリーの操作が終了します.したがって、インデックスを使用して得られた効果は、マッチするデータ行がどこで終了し、他のデータ行を無視することができますか?もう一つの機能は、インデックスヘッドから線形スキャンを実行する必要がなくて、位置決定アルゴリズムを使用して第一の一致項目を検索することから生じる.この方法を用いることで,最初の一致の値を迅速に定位でき,大量の探索時間を節約できた.データベースは様々な技術を用いてインデックス値を迅速に定位したが、本論文ではこれらの技術に関心がなかった.ポイントは、それらが実現でき、インデックスが良いものです.
図2:索引後のリード表
あなたが聞きたいのですが、私たちはなぜデータ行を並べ替えずにインデックスをなくしましたか?これで同じ検索速度の改善ができるのではないですか?はい、表が一つのインデックスだけであれば、同じ効果が得られます.しかし、2番目のインデックスを追加することができます.2つの異なる方法でデータ行を並べ替えることはできません.(例えば、顧客名にインデックスを作成し、顧客ID番号または電話番号に別のインデックスを作成することを望むかもしれません.)データ行から分離された項目を索引として解決し、複数の索引を作成することができます.また、索引の行は、データ行よりも一般的に短いです.新しい値を挿入または削除すると、より短いインデックス値は、長いデータ行を移動する順序よりも移動しやすいです.異なるMySQLストレージエンジンのインデックス実装の詳細情報は異なる.例えば、MyISAMデータテーブルに対しては、テーブルのデータ行がデータファイルに保存され、インデックス値がインデックスファイルに保存されています.一つのデータテーブルには複数のインデックスがありますが、それらは同じインデックスファイルに保存されます.インデックスファイルの各インデックスは、データファイルに素早くアクセスするための並べ替えキーレコードを含んでいます.これと対照的に、BBBとInnoDB記憶エンジンは、データ行とインデックス値を分離するためにこの方法を使用していないが、それらはインデックスを並べ替えられた値セットとしても動作する.デフォルトでは、BDBエンジンは、単一ファイル記憶データとインデックス値を使用する.InnoDBは単一のデータテーブル空間を使用して、テーブル空間ですべてのInnoDBテーブルのデータとインデックスを管理します.InnoDBは各テーブルを自分のテーブル空間で作成するように配置できますが、それでもデータテーブルのデータとインデックスは同じテーブル空間ファイルに保存されます.
前の議論は、単一のテーブルクエリ環境におけるインデックスの利点を説明したが、この場合、テーブル全体のスキャンを低減することにより、インデックスを使用して検索の速度を著しく向上させた.複数表連結に関するクエリを実行すると、インデックスの価値がより高くなります.単一テーブルクエリでは、各データ列で検査したい値の数はテーブル内のデータ行の数です.複数のテーブルクエリでは、この数は、これらのテーブルのデータラインの数によって発生するので、大幅に上昇することができます.3つのインデックスがないテーブルt 1、t 2、t 3を持っていると仮定すると、各テーブルはそれぞれデータ列i 1、i 2、i 3を含み、各テーブルは1000行のデータ列を含み、その番号は1から1000までである.いくつかの値が一致するデータ行の組み合わせを検索するクエリは、次のようになります.
SELECT t 1.i 1、t 2.i 2、t 3 FROM t 1、t 2、t 3 WHERE t 1.i 1=t 2.i 2 AND t 2.i 1=t 3;
このクエリの結果は1000行、各データ行は3つの等しい値を含むべきです.インデックスがない場合は、このクエリを処理します.これらのテーブルを全部スキャンしないと、どのデータ行にどのような値が含まれているか分かりません.したがって、すべての組み合わせを試して、WHEREの条件に合った記録を調べなければなりません.可能な組み合わせの数は1000 x 1000 x(10億!)で、それはレコードの数の百万倍にマッチしています.これは大量の仕事を浪費した.この例では、インデックスが使用されていない場合、テーブルの記録が増加するにつれて、これらのテーブルの連結を処理するのにかかる時間がより速くなり、性能が悪いということを示しています.インデックスは、テーブルt 1の最初の行を選択し、そのデータ行の値を確認するために、クエリーに以下のように処理されるので、これらのデータテーブルをインデックスすることによって著しく速度を上げることができる.2.表t 2のインデックスを使用して、直接t 1の値にマッチするデータ行に位置します.同様に、表t 3のインデックスを使用して、表t 2の値に一致するデータ行に直接的に位置します.3.処理テーブルt 1の次の行は、前のプロセスを繰り返す.このような動作は、t 1のすべてのデータ行がチェックされるまで実行される.この場合には、テーブルt 1に対して完全なスキャンを実行していますが、t 2およびt 3においてインデックス検索を実行して、これらのテーブルから直接にデータ行を取得することができます.理論的にはこの方法で上のクエリを実行すると100万倍になります.もちろんこの例は結論を出すために人工的に作られたものです.しかし、それが解決した問題は現実的で、インデックスがないテーブルにインデックスを追加すると、驚くべき性能が向上します.MySQLには、いくつかのインデックスを使用する方法があります.・上記のように、WHERE条件を向上させるためにインデックスが使用されているデータ行のマッチングまたは連結操作を実行する際に、他のテーブルのデータ行と合致する検索速度です.・MIN()またはMAX()関数を使用したクエリーに対しては、インデックスデータ列の最小値または最大値は、各データ行を確認することなくすぐに見つけることができます.・MySQLは、インデックスを利用して、ORDER BYとGROUTP BY文の並べ替えとグループ化動作を迅速に実行します.・MySQLはインデックスを利用してクエリーから得られたすべての情報を読み取る場合があります.MyISAMテーブルのインデックスされた数値列を選択したとします.データテーブルから他のデータ列を選択する必要はありません.この場合、MySQLはインデックスファイルからインデックス値を読み出し、データファイルを読み込むと得られた値と同じ値を得る.同じ値を二回読み取る必要はないので、データファイルを考慮する必要はない.
：1、 、 ；
2、 300 ；
3、 ， ；
4、 Where ， ， ；
5、 ；
6、 ， ， ；
7、 ； ：
A、 ， ；
B、 AND Where ？ ？ ， ； ；
C、 Where ， ；
D、 3 ， ， ；
E、 ， ， ；
8、 ， ；
9、 ， ；
。 ， ， ， 。
、 ： ， 、 、 。
， ， ， ； ， ， ， 。転載:http://dev.yesky.com/381/2108381.shtml?412
転載先:https://www.cnblogs.com/adforce/archive/2012/06/02/2532267.html