mysql最適化推奨(回転)

ysql大量のデータを処理する際のクエリー速度の最適化方法
最近、作業の必要性から、Mysqlデータベースのselectクエリ文に関する最適化方法に注目し始めました.
参加した実際のプロジェクトでmysqlテーブルのデータ量が百万レベルに達すると、通常のSQLクエリーの効率が直線的に低下し、whereのクエリー条件が多い場合、クエリーの速度はまったく許容できません.400万件以上のレコード(インデックス付き)を含むテーブルに対して条件付きクエリーを実行することをテストしたことがあります.クエリー時間は40数秒に達し、このようなクエリーの遅延が高いと、どのユーザーも狂ってしまうと信じています.したがって,sql文のクエリ効率をどのように向上させるかが重要である.以下は、ネット上で広く流布されている30種類のSQLクエリ文の最適化方法です.
1、できるだけwhere句で使わないようにしましょう!=または<>オペレータ.そうしないと、エンジンはインデックスの使用を放棄してテーブル全体をスキャンします.
2、クエリーを最適化し、できるだけ全表スキャンを避けるべきで、まずwhereとorder byに関連する列にインデックスを確立することを考慮しなければならない.
3、where句でフィールドをnull値で判断しないようにしてください.そうしないと、エンジンがインデックスの使用を放棄して全テーブルスキャンを行うことになります.

select id from t where num is null

numにデフォルト値0を設定して、テーブルのnum列にnull値がないことを確認し、クエリーを実行できます.

select id from t where num=0

4、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

5.次のクエリによって、テーブル全体がスキャンされます.(パーセンテージは前置きできません)

select id from t where name like ‘�c%’

効率を向上させるには,全文検索を考慮することができる.
6、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

7、where句でパラメータを使用すると、テーブル全体がスキャンされます.SQLは実行時にのみローカル変数を解析するため、オプティマイザはアクセス計画の選択を実行時に遅らせることはできません.コンパイル時に選択する必要があります.ただし、コンパイル時にアクセス・プランが確立されている場合、変数の値は不明であり、インデックス選択の入力項目として使用できません.次の文では、フルテーブルスキャンを行います.

select id from t where num=@num

インデックスの使用を強制するように変更できます.

select id from t with(index(   )) where num=@num

8、where句でフィールドを式で操作しないようにしてください.これにより、エンジンはインデックスの使用を放棄して全テーブルスキャンを行います.次のようになります.

select id from t where num/2=100

次のように変更します.

select id from t where num=100*2

9、where句でフィールドを関数的に操作しないようにしてください.これにより、エンジンはインデックスの使用を放棄して全テーブルスキャンを行います.次のようになります.

select id from t where substring(name,1,3)=’abc’–name abc   id
select id from t where datediff(day,createdate,’2005-11-30′)=0–’2005-11-30′   id

次のように変更します.

select id from t where name like ‘abc%’
select id from t where createdate>=’2005-11-30′ and createdate<’2005-12-1′

10、where句の「=」の左側で関数、算術演算、または他の式の演算を行わないでください.そうしないと、インデックスが正しく使用されない可能性があります.
11、インデックスフィールドを条件として使用する場合、インデックスが複合インデックスである場合、システムがインデックスを使用することを保証するには、インデックスの最初のフィールドを条件として使用する必要があります.そうしないと、インデックスは使用されず、できるだけフィールドの順序がインデックスの順序と一致するようにします.
12、意味のないクエリーを書かないでください.空のテーブル構造を生成する必要がある場合は、次のようにします.

select col1,col2 into #t from t where 1=0

このようなコードは結果セットを返さないが、システムリソースを消費するので、次のように変更します.

create table #t(…)

13、多くの場合、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)

14、すべてのインデックスがクエリーに対して有効であるわけではありません.SQLはテーブルのデータに基づいてクエリーの最適化を行います.インデックス列に大量のデータが重複している場合、SQLクエリーはインデックスを利用しない可能性があります.例えば、テーブルにフィールドsexがあり、male、femaleがほぼ半分ずつある場合、sexにインデックスを作成してもクエリーの効率には役立ちません.
15、インデックスは多ければ多いほど良いわけではありません.インデックスは相応のselectの効率を高めることができますが、同時にinsertとupdateの効率を下げることができます.insertやupdateの時にインデックスを再構築する可能性があるので、どのようにインデックスを構築するかは慎重に考慮する必要があります.具体的な状況によって異なります.1つのテーブルのインデックス数は6つを超えないほうがいいです.多すぎる場合は、あまり使用されないカラムに作成されたインデックスが必要かどうかを考慮する必要があります.
16.clusteredインデックスデータ列の更新はできるだけ避けるべきである.clusteredインデックスデータ列の順序はテーブルレコードの物理記憶順序であり、カラム値が変更されると、テーブルレコード全体の順序の調整にかなりのリソースがかかるからである.アプリケーションシステムがclusteredインデックスデータ列を頻繁に更新する必要がある場合は、そのインデックスをclusteredインデックスとして構築するかどうかを考慮する必要があります.
17、できるだけ数字型のフィールドを使用し、数値情報のみを含むフィールドをできるだけ文字型に設計しないと、クエリーと接続の性能が低下し、ストレージのオーバーヘッドが増加する.これは、エンジンがクエリーと接続を処理するときに文字列の各文字を1つずつ比較し、数値型では1回だけ比較すれば十分であるためです.
18、できるだけvarchar/nvarcharをchar/ncharの代わりに使用する.まず長くなるフィールドの記憶空間が小さくなるため、記憶空間を節約することができ、次にクエリーにとって、比較的小さいフィールド内で検索効率が明らかに高い.
19、どこでもselect*from tを使用せず、「*」の代わりに具体的なフィールドリストを使用し、使用できないフィールドを返さないでください.
20.テンポラリ・テーブルの代わりに、できるだけテーブル変数を使用します.テーブル変数に大量のデータが含まれている場合は、インデックスが非常に限られていることに注意してください(プライマリ・キー・インデックスのみ).
21.テンポラリ・テーブルの頻繁な作成と削除を避け、システム・テーブル・リソースの消費を減らす.
22、テンポラリ・テーブルは使用不可ではありません.適切に使用すると、大規模なテーブルまたは共通テーブルのデータセットを繰り返し参照する必要がある場合など、いくつかのインスタンスをより効果的にすることができます.ただし、使い捨てイベントの場合は、エクスポートテーブルを使用することが望ましい.
23、テンポラリテーブルを新規作成する際、一度にデータを挿入する量が大きい場合は、create tableの代わりにselect intoを使用して、大量のlogを回避し、速度を高めることができる.データ量が少ない場合は、システムテーブルのリソースを緩和するために、まずcreate tableを作成し、それからinsertします.
24.テンポラリ・テーブルを使用する場合、ストレージ・プロシージャの最後に必ずすべてのテンポラリ・テーブルを明示的に削除し、truncate tableを先に削除し、drop tableを実行することで、システム・テーブルの長時間のロックを回避できます.
25、カーソルの効率が悪いので、カーソル操作のデータが1万行を超える場合は、書き換えを考慮する必要があります.
26、カーソルベースのメソッドまたはテンポラリ・テーブル・メソッドを使用する前に、セット・ベースのソリューションを探して問題を解決する必要があります.通常、セット・ベースのメソッドはより効果的です.
27.テンポラリ・テーブルと同様に、カーソルが使用できないわけではありません.小型データセットにFAST_を使用FOrWARDカーソルは、通常、必要なデータを取得するためにいくつかのテーブルを参照する必要がある場合に、他の逐行処理方法よりも優れています.結果セットに合計を含めるルーチンは、通常、カーソルを使用して実行するよりも高速です.開発時間が許可されている場合、カーソルベースのメソッドとセットベースのメソッドのどちらが効果的かを試してみてください.
28、全ての記憶プロセスとトリガの開始にSET NOCOUNT ONを設定し、終了時にSET NOCOUNT OFFを設定する.ストレージ・プロシージャとトリガの各文を実行した後、クライアントにDONE_を送信する必要はありません.IN_PROCメッセージ.
29、できるだけクライアントにビッグデータ量を返すことを避け、データ量が大きすぎる場合、相応の需要が合理的かどうかを考慮しなければならない.
30、できるだけ大きな事務操作を避け、システムの同時能力を高める.