MySQL単表クエリ最適化技術まとめ
3108 ワード
csdn原文：http://blog.csdn.net/zhu19774279/article/details/46473981
本文の原文の住所はここです。https://www.percona.com/blog/2015/04/27/indexing-101-optimizing-mysql-queries-on-a-single-table/以下は翻訳文です。
-----------------------------------------------------------------------------------------------------
最近、性能が悪いMySQLのリストにたくさん会った。理由は簡単です。インデックスが正しく作成されていないため、実行計画の性能が低下しています。以下は表検索の性能を最適化するためのポイントです。
免責声明：私はいくつかのポイントを提供しますが、すべての可能性を含むつもりはありません。私は100%あなたが私のポイントに合わないケースを見つけられると信じていますが、ほとんどの場合、私が書いたこれらのポイントがあなたを助けてくれると信じています。簡単のために、私もいくつかのMySQL 5.6+バージョンのいくつかの新しい特性を議論しません。これらの新しい特性は応答時間に大きな影響を与えることに注意してください。
インデックスは何ができますか？
インデックスは主に3つのことをします。フィルタリング、ソートまたはグループ化（sort/group）、カバー（cover）。前の二つは何も言いませんが、すべての人が知っているわけではありません。実はこれは簡単なものです。
基本クエリのワークフローは以下の通りです。
1.マッチしたレコードを検索するために索引を使用し、データのポインタを取得します。
2.関連データのポインタを使用します。
3.検索したレコードを返します。
インデックスを上書きできると、クエリーのすべてのフィールドがインデックスによって上書きされますので、第二のステップはスキップされます。したがって、クエリーの流れは以下のようになります。
1.マッチしたレコードを検索するために索引を使用します。
2.検索したレコードを返します。
ほとんどの場合、インデックスは小さいので、メモリにロードできますが、データは大きくて、全部メモリに保存できません。インデックスを上書きすると、多くのディスクの操作が避けられます。そのため、性能も大幅に改善されます。
いくつかのよくある照会例を見てみましょう。
単一イコールクエリ(Single equality)
これは最も基本的なシーンです。
[sql] view plin
copy
SELECT * FROM t WHERE c = 100 疑いのない場合は、cフィールドにインデックスを作成します。注意したいのは、検索条件が精確でない場合（if the criteria is not selective enough）、最適化器は全テーブルクエリを選択する可能性が高いです。
このような単一の等しいクエリーは、すべてのフィールドではなく、クエリーの一部フィールドだけをも含みます。
[sql] view plin
copy
SELECT c 1, c 2 FROM t WHERE c = 100 ここではインデックスをカバーするので、インデックスを作成します。作成ではありません（c 1、c 2、c）。これもインデックスをカバーしていますが、フィルタリングにはあまり役立ちません。
複数はクエリに等しい(Multiple equalities)
[sql] view plin
copy
SELECT * FROM t WHERE c = 100 and d = 'xyz この場合も容易に最適化される：インデックス（c，d）または（d，c）を作成する。
最も一般的なエラーは二つのインデックスを作成することです。一つはc、一つはdです。MySQLにもかかわらず、index_mergeアルゴリズムはこの二つのインデックスを同時に使うことができますが、それでもやはり悪い選択です。（詳細は以下の文章を参照してください。https://www.percona.com/blog/2009/09/19/multi-column-indexes-vs-index-merge/を選択しますhttps://www.percona.com/blog/2012/12/14/the-optimization-that-often-isnt-index-merge-intersection/を選択しますhttps://www.percona.com/blog/2014/01/03/multiple-column-index-vs-multiple-indexes-with-mysql-56/)
イコールとイコールではないクエリ(Equality and inequality)
[sql] view plin
copy
SELECT * FROM t WHERE c > 100 and d = 'xyz このような場合は、計算に等しくない列がある限り、他の列でインデックスを使用するのを阻止するので注意しなければなりません。
したがって、私たちはインデックス（d，c）を作成する必要があります。この場合、cとdの両方の条件はインデックスを移動します。これも私たちが欲しい結果です。
私たちが作成した索引が(c，d)であれば、c列の索引だけが利用され、効率が低いです。
したがって、インデックス内のフィールドの順序は、このような類似／非等辺のクエリに大きな影響を与えます。
複数はクエリに等しくないです。
[sql] view plin
copy
SELECT * FROM t WHERE c > 100 and b and d = 'xyz ここには二つのイコールではないです。前に言ったのはインデックスクエリを終了するという意味ではないので、b、c、dはインデックスで覆われています。
コメント1
）を作成するか、インデックス（d，b）を作成するか、それともインデックス（d，c）を作成するかを決めなければなりません。
表と表の具体的なデータが分からない場合、上のいずれを作成しても構いません。一番重要なのは、必ず条件（ここではd）の位置に等しい列をインデックスの一番左に置くことです。
注1：実際には、フィールドbパーティション（partition on b
本文の原文の住所はここです。https://www.percona.com/blog/2015/04/27/indexing-101-optimizing-mysql-queries-on-a-single-table/以下は翻訳文です。
-----------------------------------------------------------------------------------------------------
最近、性能が悪いMySQLのリストにたくさん会った。理由は簡単です。インデックスが正しく作成されていないため、実行計画の性能が低下しています。以下は表検索の性能を最適化するためのポイントです。
免責声明：私はいくつかのポイントを提供しますが、すべての可能性を含むつもりはありません。私は100%あなたが私のポイントに合わないケースを見つけられると信じていますが、ほとんどの場合、私が書いたこれらのポイントがあなたを助けてくれると信じています。簡単のために、私もいくつかのMySQL 5.6+バージョンのいくつかの新しい特性を議論しません。これらの新しい特性は応答時間に大きな影響を与えることに注意してください。
インデックスは何ができますか？
インデックスは主に3つのことをします。フィルタリング、ソートまたはグループ化（sort/group）、カバー（cover）。前の二つは何も言いませんが、すべての人が知っているわけではありません。実はこれは簡単なものです。
基本クエリのワークフローは以下の通りです。
1.マッチしたレコードを検索するために索引を使用し、データのポインタを取得します。
2.関連データのポインタを使用します。
3.検索したレコードを返します。
インデックスを上書きできると、クエリーのすべてのフィールドがインデックスによって上書きされますので、第二のステップはスキップされます。したがって、クエリーの流れは以下のようになります。
1.マッチしたレコードを検索するために索引を使用します。
2.検索したレコードを返します。
ほとんどの場合、インデックスは小さいので、メモリにロードできますが、データは大きくて、全部メモリに保存できません。インデックスを上書きすると、多くのディスクの操作が避けられます。そのため、性能も大幅に改善されます。
いくつかのよくある照会例を見てみましょう。
単一イコールクエリ(Single equality)
これは最も基本的なシーンです。
[sql] view plin
copy
SELECT * FROM t WHERE c = 100 疑いのない場合は、cフィールドにインデックスを作成します。注意したいのは、検索条件が精確でない場合（if the criteria is not selective enough）、最適化器は全テーブルクエリを選択する可能性が高いです。
このような単一の等しいクエリーは、すべてのフィールドではなく、クエリーの一部フィールドだけをも含みます。
[sql] view plin
copy
SELECT c 1, c 2 FROM t WHERE c = 100 ここではインデックスをカバーするので、インデックスを作成します。作成ではありません（c 1、c 2、c）。これもインデックスをカバーしていますが、フィルタリングにはあまり役立ちません。
複数はクエリに等しい(Multiple equalities)
[sql] view plin
copy
SELECT * FROM t WHERE c = 100 and d = 'xyz この場合も容易に最適化される：インデックス（c，d）または（d，c）を作成する。
最も一般的なエラーは二つのインデックスを作成することです。一つはc、一つはdです。MySQLにもかかわらず、index_mergeアルゴリズムはこの二つのインデックスを同時に使うことができますが、それでもやはり悪い選択です。（詳細は以下の文章を参照してください。https://www.percona.com/blog/2009/09/19/multi-column-indexes-vs-index-merge/を選択しますhttps://www.percona.com/blog/2012/12/14/the-optimization-that-often-isnt-index-merge-intersection/を選択しますhttps://www.percona.com/blog/2014/01/03/multiple-column-index-vs-multiple-indexes-with-mysql-56/)
イコールとイコールではないクエリ(Equality and inequality)
[sql] view plin
copy
SELECT * FROM t WHERE c > 100 and d = 'xyz このような場合は、計算に等しくない列がある限り、他の列でインデックスを使用するのを阻止するので注意しなければなりません。
したがって、私たちはインデックス（d，c）を作成する必要があります。この場合、cとdの両方の条件はインデックスを移動します。これも私たちが欲しい結果です。
私たちが作成した索引が(c，d)であれば、c列の索引だけが利用され、効率が低いです。
したがって、インデックス内のフィールドの順序は、このような類似／非等辺のクエリに大きな影響を与えます。
複数はクエリに等しくないです。
[sql] view plin
copy
SELECT * FROM t WHERE c > 100 and b and d = 'xyz ここには二つのイコールではないです。前に言ったのはインデックスクエリを終了するという意味ではないので、b、c、dはインデックスで覆われています。
コメント1
）を作成するか、インデックス（d，b）を作成するか、それともインデックス（d，c）を作成するかを決めなければなりません。
表と表の具体的なデータが分からない場合、上のいずれを作成しても構いません。一番重要なのは、必ず条件（ここではd）の位置に等しい列をインデックスの一番左に置くことです。
注1：実際には、フィールドbパーティション（partition on b
)， （d，c）， c (partition onc）に ってインデックス（d，b）を するというすべての を に たす があります。この は、 の の を えていますが、これもこの の です。