MySQLのクエリパフォーマンス最適化5(特定のタイプのクエリを最適化)
4309 ワード
この文書では、特定のタイプのクエリーを最適化する方法について説明します.
1.count()クエリーcount()集約関数を最適化し、その関数を使用したクエリーをどのように最適化するかは、mysqlで最も誤解されやすい最初の10の話題の1つであるcount()が特殊な関数であり、2つの非常に異なる役割を果たす可能性が高い.カラム値の数を統計したり、ローの数を統計したりできます.統計カラム値は、カラム値が空でないことを要求します.(nullを統計しない、すなわちnull値カウントは0)
count()のもう一つの用途は、結果セットのロー数を統計することです.mysqlがカッコの式値が空ではないことを確認すると、実際には統計行数になります.最も簡単なのはcount(*)を使用する場合、この場合、ワイルドカード*は私たちが予想したようにすべての列に拡張されません.実際には、すべての列を無視してすべての行数を直接統計します.同じクエリで同じカラムの値が異なるselect sum(if(color='blue',1,0))as BLUE,sum(if(color='red',1,0))as RED from itemsを統計すると
select count(color= 'blue' or null) blue, count(color= 'red' or null) red, from items2.関連クエリーの最適化という話題は頻繁に議論されていますが、ここでは特に、onまたはusing句の列にインデックスがあることを確認する必要があります.インデックスを作成するときは、関連付けの順序を考慮します.テーブルAとテーブルBがカラムcで関連付けられている場合、オプティマイザの関連順序がB,Aの場合、Bテーブルの対応するカラムにインデックスを作成する必要はありません.一般に、他の理由はありません.そうしないと、関連付けられた順序の2番目のテーブルの対応するカラムにインデックスを作成するだけです.groupbyとorder byの式が1つのテーブルのカラムにのみ関連していることを確認します.このようにmysqlはインデックスを使用してこのプロセスを最適化することができます.mysqlをアップグレードするときは、関連文法、演算子の優先度など、他の変更が発生する可能性がある点に注意する必要があります.3.サブクエリを最適化するには、可能な限り関連付けを使用します.
4.groupbyとdistinctの最適化多くのシーンでは、mysqlは同じ方法でこの2つのクエリーを最適化します.実際には、mysqlオプティマイザは内部処理時にこの2つのクエリーを相互に変換します.インデックスを使用して最適化できます.これも最も効果的な最適化方法です.groupby with rollupパケットクエリを最適化する変種は、mysqlに返されたパケット結果をもう一度スーパー集約するように要求することです.with rollupを使用して、この最適化を実現することができます.最善の方法はwith rollup機能をできるだけアプリケーション処理に移行することです.5.最適化limitとoffsetシステムがページング操作を行う必要がある場合、私たちは通常LIMITとOFFSETの方法を使用して実現し、同時に適切なorder by句を追加します.対応するインデックスがある場合、通常は効率的です.そうしないと、mysqlは大量のファイルソートを行う必要があります.OFFSETが大きいと、検索が大変になります.このクエリを最適化するには、ページ内でページング数を制限するか、大きなオフセットクエリのパフォーマンスを最適化します.このようなページング・クエリーを最適化する最も簡単な方法は、すべてのカラムをクエリーするのではなく、インデックスを使用してスキャンを上書きすることです.次に、1回の関連付け操作に基づいて必要なカラムを返します.オフセットが大きい場合、効率が大幅に向上します.
ここでの遅延クエリーは、mysqlができるだけ少ないページをスキャンし、アクセスするレコードを取得した後、関連する列に基づいて元のテーブルクエリーに必要な列に戻すクエリー効率を大幅に向上させます.このテクノロジーは、関連クエリのlimit句を最適化するためにも使用できます.
limitクエリーを既知の場所のクエリーに変換してmysqlが範囲スキャンで対応する結果を得ることもできます.
6.SQL_の最適化CALC_FOUND_ROWSがページングするとき、もう一つの一般的なテクニックはlimit文にSQL_を加えることです.CALC_FOUND_ROWSプロンプト(hint)は、limitを除いた後に条件を満たす行数を取得できるため、ページングの総数とすることができる.mysqlは非常に「深さ」の最適化を行い,ある方法で総行数を予測したようだ.しかし、実際には、mysqlは条件を満たすすべてのローをスキャンしてからロー数を知るので、このヒントを加えると、必要かどうかにかかわらず、mysqlは条件を満たすすべてのローをスキャンし、limitのロー数を満たすとスキャンを終了するのではなく、不要なローを捨てます.したがって、このヒントの代価は非常に高い可能性があります.
7.unionクエリーmysqlの最適化は、常に一時テーブルを作成して埋め込むことによってunionクエリーを実行します.そのため、多くの最適化ポリシーはunionクエリでうまく使用できません.オプティマイザがこれらの条件を十分に利用して最適化できるように、where、limit、order byなどの句をunionの各サブクエリに手動でプッシュすることがしばしば必要です.重複するローを削除するサーバが必要でない限り、union allを使用することが重要です.ALLキーがない場合、mysqlはテンポラリ・テーブルにdistinctオプションを追加し、テンポラリ・テーブル全体のデータを一意にチェックします.このような代価は非常に高い.ALLキーがある場合でもmysqlはテンポラリ・テーブルを使用して結果を格納します.実際、mysqlは常に結果を一時テーブルに入れて読み出し、クライアントに返す必要はありません.
8.静的クエリー分析
9.ユーザー定義変数set@one:=1
転載先:https://www.cnblogs.com/w2154/p/4705453.html
1.count()クエリーcount()集約関数を最適化し、その関数を使用したクエリーをどのように最適化するかは、mysqlで最も誤解されやすい最初の10の話題の1つであるcount()が特殊な関数であり、2つの非常に異なる役割を果たす可能性が高い.カラム値の数を統計したり、ローの数を統計したりできます.統計カラム値は、カラム値が空でないことを要求します.(nullを統計しない、すなわちnull値カウントは0)
count()のもう一つの用途は、結果セットのロー数を統計することです.mysqlがカッコの式値が空ではないことを確認すると、実際には統計行数になります.最も簡単なのはcount(*)を使用する場合、この場合、ワイルドカード*は私たちが予想したようにすべての列に拡張されません.実際には、すべての列を無視してすべての行数を直接統計します.同じクエリで同じカラムの値が異なるselect sum(if(color='blue',1,0))as BLUE,sum(if(color='red',1,0))as RED from itemsを統計すると
select count(color= 'blue' or null) blue, count(color= 'red' or null) red, from items2.関連クエリーの最適化という話題は頻繁に議論されていますが、ここでは特に、onまたはusing句の列にインデックスがあることを確認する必要があります.インデックスを作成するときは、関連付けの順序を考慮します.テーブルAとテーブルBがカラムcで関連付けられている場合、オプティマイザの関連順序がB,Aの場合、Bテーブルの対応するカラムにインデックスを作成する必要はありません.一般に、他の理由はありません.そうしないと、関連付けられた順序の2番目のテーブルの対応するカラムにインデックスを作成するだけです.groupbyとorder byの式が1つのテーブルのカラムにのみ関連していることを確認します.このようにmysqlはインデックスを使用してこのプロセスを最適化することができます.mysqlをアップグレードするときは、関連文法、演算子の優先度など、他の変更が発生する可能性がある点に注意する必要があります.3.サブクエリを最適化するには、可能な限り関連付けを使用します.
4.groupbyとdistinctの最適化多くのシーンでは、mysqlは同じ方法でこの2つのクエリーを最適化します.実際には、mysqlオプティマイザは内部処理時にこの2つのクエリーを相互に変換します.インデックスを使用して最適化できます.これも最も効果的な最適化方法です.groupby with rollupパケットクエリを最適化する変種は、mysqlに返されたパケット結果をもう一度スーパー集約するように要求することです.with rollupを使用して、この最適化を実現することができます.最善の方法はwith rollup機能をできるだけアプリケーション処理に移行することです.5.最適化limitとoffsetシステムがページング操作を行う必要がある場合、私たちは通常LIMITとOFFSETの方法を使用して実現し、同時に適切なorder by句を追加します.対応するインデックスがある場合、通常は効率的です.そうしないと、mysqlは大量のファイルソートを行う必要があります.OFFSETが大きいと、検索が大変になります.このクエリを最適化するには、ページ内でページング数を制限するか、大きなオフセットクエリのパフォーマンスを最適化します.このようなページング・クエリーを最適化する最も簡単な方法は、すべてのカラムをクエリーするのではなく、インデックスを使用してスキャンを上書きすることです.次に、1回の関連付け操作に基づいて必要なカラムを返します.オフセットが大きい場合、効率が大幅に向上します.
mysql> SELECT film_id, description FROM sakila.film ORDER BY title LIMIT 50, 5;
--
mysql> SELECT film.film_id, film.description
-> FROM sakila.film
-> INNER JOIN (
-> SELECT film_id FROM sakila.film
-> ORDER BY title LIMIT 50, 5
-> ) AS lim USING(film_id);ここでの遅延クエリーは、mysqlができるだけ少ないページをスキャンし、アクセスするレコードを取得した後、関連する列に基づいて元のテーブルクエリーに必要な列に戻すクエリー効率を大幅に向上させます.このテクノロジーは、関連クエリのlimit句を最適化するためにも使用できます.
limitクエリーを既知の場所のクエリーに変換してmysqlが範囲スキャンで対応する結果を得ることもできます.
6.SQL_の最適化CALC_FOUND_ROWSがページングするとき、もう一つの一般的なテクニックはlimit文にSQL_を加えることです.CALC_FOUND_ROWSプロンプト(hint)は、limitを除いた後に条件を満たす行数を取得できるため、ページングの総数とすることができる.mysqlは非常に「深さ」の最適化を行い,ある方法で総行数を予測したようだ.しかし、実際には、mysqlは条件を満たすすべてのローをスキャンしてからロー数を知るので、このヒントを加えると、必要かどうかにかかわらず、mysqlは条件を満たすすべてのローをスキャンし、limitのロー数を満たすとスキャンを終了するのではなく、不要なローを捨てます.したがって、このヒントの代価は非常に高い可能性があります.
7.unionクエリーmysqlの最適化は、常に一時テーブルを作成して埋め込むことによってunionクエリーを実行します.そのため、多くの最適化ポリシーはunionクエリでうまく使用できません.オプティマイザがこれらの条件を十分に利用して最適化できるように、where、limit、order byなどの句をunionの各サブクエリに手動でプッシュすることがしばしば必要です.重複するローを削除するサーバが必要でない限り、union allを使用することが重要です.ALLキーがない場合、mysqlはテンポラリ・テーブルにdistinctオプションを追加し、テンポラリ・テーブル全体のデータを一意にチェックします.このような代価は非常に高い.ALLキーがある場合でもmysqlはテンポラリ・テーブルを使用して結果を格納します.実際、mysqlは常に結果を一時テーブルに入れて読み出し、クライアントに返す必要はありません.
8.静的クエリー分析
9.ユーザー定義変数set@one:=1
転載先:https://www.cnblogs.com/w2154/p/4705453.html