MySQLが大量のデータを処理する際のクエリー速度の最適化方法

転載は出典を明記してください.http://www.54tianzhisheng.cn/2017/04/29/MySQL-select-good/
実際のプロジェクトでは、MySQLテーブルのデータ量が百万レベルに達すると、通常のSQLクエリーの効率は直線的に低下し、whereのクエリー条件が多い場合、クエリーの速度は許容できません.考えてみれば、もし私たちが宝を洗う注文の詳細を調べたら、検索時間が数十秒に達すると、このような高い検索が遅れると、どのユーザーも狂ってしまいます.そのため、SQL文のクエリー効率を向上させるには、どのようにするかが重要です.
クエリの速度が遅い理由
1、インデックスがない、またはインデックスが使用されていない(これはクエリが遅い最も一般的な問題であり、プログラム設計の欠陥である)
2、I/Oスループットが小さく、ボトルネック効果を形成した.
3、計算カラムが作成されていないため、クエリが最適化されません.
4、メモリ不足
5、ネットワーク速度が遅い
6.クエリーされたデータ量が大きすぎる(複数回のクエリーが可能で、その他の方法でデータ量を下げる)
7、ロックまたはデッドロック(これはクエリが遅い最も一般的な問題であり、プログラム設計の欠陥である)
8、sp_lock,sp_whoは、競合リソースの読み書きのため、アクティブなユーザーが表示します.
9、不要な行と列を返した
10、クエリ文が悪い、最適化されていない
SQLクエリ文の最適化方法:
1、できるだけwhere句で使わないようにしましょう!=または<>オペレータです.そうしないと、エンジンはインデックスの使用を放棄してテーブル全体をスキャンします.
2、where句でフィールドをnull値で判断しないようにしてください.そうしないと、エンジンがインデックスの使用を放棄して全テーブルスキャンを行うことになります.

select id from t where num is null;

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

select id from t where num = 0;

3、クエリーを最適化し、できるだけ全表スキャンを避けるべきで、まずwhereとorder byに関連する列にインデックスを確立することを考慮しなければならない.
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 '%abc%';

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

select xx,phone FROM send  a JOIN (
 select '13891030091' phone  union select '13992085916' …………  UNION  SELECT '13619100234' ) b
  on  a.Phone=b.phone
--               
in('13891030091','13992085916','13619100234'…………)

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、できるだけ大きな事務操作を避け、システムの同時能力を高める.