30種類のSQLクエリ文の最適化方法

mysqlが百万級以上のデータを処理する際にクエリーの速度をどのように向上させるかについては、最近、作業の必要性から、Mysqlデータベースに対するselectクエリー文に関する最適化方法に注目し始めました.参加した実際のプロジェクトでmysqlテーブルのデータ量が百万レベルに達すると、通常のSQLクエリーの効率が直線的に低下し、whereのクエリー条件が多い場合、クエリーの速度はまったく許容できません.400万件以上のレコード(インデックス付き)を含むテーブルに対して条件付きクエリーを実行することをテストしたことがあります.クエリー時間は40数秒に達し、このようなクエリーの遅延が高いと、どのユーザーも狂ってしまうと信じています.したがって,sql文のクエリ効率をどのように向上させるかが重要である.
以下は、ネット上で広く流布されている30種類のSQLクエリ文の最適化方法です.

where句での使用はできるだけ避けるべきです!=または<>オペレータ.そうしないと、エンジンはインデックスの使用を放棄してテーブル全体をスキャンします.

クエリーを最適化するには、テーブル全体のスキャンを最小限に抑える必要があります.まず、whereおよびorder byに関連するカラムにインデックスを作成することを考慮します.

は、where句でフィールドをnull値で判断することをできるだけ避けなければならない.そうしないと、エンジンがインデックスの使用を放棄して全テーブルスキャンを行うことになる.例えば、select id from t where num is nullはnumにデフォルト値0を設定し、テーブルのnum列にnull値がないことを確認し、クエリ:select id from t where num=0

は、where句でorを使用して条件を接続することをできるだけ避ける.そうしないと、エンジンがインデックスの使用を放棄して全テーブルスキャンを行うことになる.例えば、select id from t where num=10 or num=20は、select id from t where num=10 union all select id from t where num=20

と問い合わせることができる.

の次のクエリも、(パーセンテージを前置できない)select id from t where name like ‘%c%’の効率を向上させるには、全文検索を考慮することができます.

inとnot inも慎重に使用しなければなりません.そうしないと、select id from t where num in(1,2,3)が連続した数値に対してbetweenを使用できる場合はinを使用しないでください.select id from t where num between 1 and 3

where句でパラメータを使用すると、全テーブルスキャンも発生します.SQLは実行時にのみローカル変数を解析するため、オプティマイザはアクセス計画の選択を実行時に遅らせることはできません.コンパイル時に選択する必要があります.ただし、コンパイル時にアクセス・プランが確立されている場合、変数の値は不明であり、インデックス選択の入力項目として使用できません.次の文がフルテーブルスキャンを行うように、select id from t where num=@numを強制クエリー使用インデックスに変更できます:select id from t with(index( )) where num=@num

は、where句でフィールドを式で操作することをできるだけ避けなければならない.これにより、エンジンがインデックスの使用を放棄して全テーブルスキャンを行うことになる.例えば:select id from t where num/2=100は:select id from t where num=100*2

に変更すべきである.

は、where句でフィールドを関数的に操作することをできるだけ避けるべきであり、エンジンがインデックスの使用を放棄して全テーブルスキャンを行うことになります.例えば、select id from t where substring(name,1,3)=’abc'によって生成されたidは、select id from t where name like ‘abc%’、例えば、select id from t where datediff(day,createdate,’2005-11-30′)=’2005-11-30′に変更すべきである:select id from t where createdate>=’2005-11-30′ and createdate

に変更すべきである.

where句の「=」の左側で関数、算術演算、または他の式の演算を行わないでください.そうしないと、インデックスが正しく使用されない可能性があります.

インデックスフィールドを条件として使用する場合、インデックスが複合インデックスである場合、システムがインデックスを使用することを保証するには、インデックスの最初のフィールドを条件として使用する必要があります.そうしないと、インデックスは使用されず、フィールドの順序がインデックスの順序と一致するようにします.

意味のないクエリーは書かないでください.空のテーブル構造を生成する必要がある場合は、select col1,col2 into #t from t where 1=0のようなコードは結果セットを返さないが、システムリソースを消費するので、create table#t(...)

に変更してください.

多くの場合、inの代わりにexistsを使用するのは良い選択です.select num from a where num in(select num from b)は次の文で置き換えられます.select num from a where exists(select 1 from b where num=a.num)

はすべてのインデックスがクエリーに対して有効ではありません.SQLはテーブルのデータに基づいてクエリーの最適化を行います.インデックス列に大量のデータが重複している場合、SQLクエリーはインデックスを利用しない可能性があります.例えば、テーブルにフィールドsexがあり、male、femaleがほぼ半分ずつある場合、sexにインデックスを作成してもクエリーの効率には役立ちません.

インデックスは多ければ多いほど良いわけではありません.インデックスは相応のselectの効率を高めることができますが、同時にinsertとupdateの効率を低下させます.insertやupdateの時にインデックスを再構築する可能性があるので、インデックスをどのように構築するかは慎重に考慮する必要があります.具体的な状況によって異なります.1つのテーブルのインデックス数は6つを超えないほうがいいです.多すぎる場合は、あまり使用されないカラムに作成されたインデックスが必要かどうかを考慮する必要があります.

は、clusteredインデックスデータ列の順序がテーブルレコードの物理的記憶順序であるため、clusteredインデックスデータ列の更新をできるだけ避けるべきである.カラム値が変更されると、テーブルレコード全体の順序が調整され、かなりのリソースが消費される.アプリケーションシステムがclusteredインデックスデータ列を頻繁に更新する必要がある場合は、そのインデックスをclusteredインデックスとして構築するかどうかを考慮する必要があります.

では、数値情報のみを含むフィールドを文字型に設計しないようにすると、クエリーと接続のパフォーマンスが低下し、ストレージのオーバーヘッドが増加します.これは、エンジンがクエリーと接続を処理するときに文字列の各文字を1つずつ比較し、数値型では1回だけ比較すれば十分であるためです.

は、char/ncharの代わりにvarchar/nvarcharを可能な限り使用します.まず、フィールドの記憶領域が小さくなるため、記憶領域を節約できます.次に、クエリーにとって、比較的小さなフィールドでの検索効率は明らかに高くなります.

はどこでもselect * from tを使用しないでください.“*”の代わりに具体的なフィールドリストを使用して、使用できないフィールドを返さないでください.

は、テンポラリ・テーブルの代わりにできるだけテーブル変数を使用します.テーブル変数に大量のデータが含まれている場合は、インデックスが非常に限られていることに注意してください(プライマリ・キー・インデックスのみ).

は、システム・テーブル・リソースの消費を減らすために、テンポラリ・テーブルの頻繁な作成と削除を回避します.

テンポラリ・テーブルは使用できないわけではありません.適切に使用すると、大規模なテーブルまたは共通テーブルのデータセットを繰り返し参照する必要がある場合など、いくつかのインスタンスをより効率的に使用できます.ただし、使い捨てイベントの場合は、エクスポートテーブルを使用することが望ましい.

テンポラリ・テーブルを新規作成する際、一度にデータを挿入する量が大きい場合は、select intoの代わりにcreate tableを使用して、大量のlogを回避し、速度を向上させることができる.データ量が少ない場合は、システム・テーブルのリソースを緩和するために、create table、insertが必要です.

テンポラリ・テーブルを使用すると、ストレージ・プロシージャの最後に必ずすべてのテンポラリ・テーブルが明示的に削除され、truncate tableからdrop tableになります.これにより、システム・テーブルの長時間のロックを回避できます.

カーソルの使用は、カーソルの効率が悪いため、カーソル操作のデータが1万行を超える場合は、書き換えを考慮する必要があります.

カーソルベースのメソッドまたはテンポラリ・テーブル・メソッドを使用する前に、セット・ベースのソリューションを探して問題を解決する必要があります.セット・ベースのメソッドは通常より効果的です.

はテンポラリ・テーブルと同様に、カーソルが使用できないわけではありません.小型データセットに対してFAST_FORWARDカーソルを使用することは、通常、他の逐行処理方法よりも優れています.特に、必要なデータを得るためにいくつかのテーブルを参照する必要がある場合です.結果セットに合計を含めるルーチンは、通常、カーソルを使用して実行するよりも高速です.開発時間が許可されている場合、カーソルベースのメソッドとセットベースのメソッドのどちらが効果的かを試してみてください.

は、全ての記憶プロセスおよびトリガの開始においてSET NOCOUNT ON、終了においてSET NOCOUNT OFFを設定する.ストレージ・プロシージャとトリガの各文を実行した後、クライアントにDONE_IN_PROCメッセージを送信する必要はありません.

クライアントに大きなデータ量を返すことをできるだけ避け、データ量が大きすぎる場合は、対応する需要が合理的かどうかを考慮しなければならない.

大きなトランザクション操作をできるだけ回避し、システムの同時実行能力を向上させる.

小生才疎浅陋、不当または不明な点があれば、ご容赦ください.