大量データテーブルの最適化スキーム

1、クエリーを最適化し、全テーブルスキャンをできるだけ避けるには、whereおよびorder byに関連するカラムにインデックスを作成することをまず考慮する必要があります.2、where句でフィールドをnull値で判断しないようにしてください.そうしないと、エンジンがインデックスの使用を放棄して全テーブルスキャンを行うことになります.

select id from t where num is null

データベースにNULLを残さないで、できるだけNOT NULLを使ってデータベースを埋めます.
コメント、説明、コメントなどはNULLに設定できますが、その他はNULLは使用しないほうがいいです.
NULLにスペースは必要ないと思わないでください.例えば、char(100)型は、フィールドの作成時にスペースが固定され、値(NULLも含む)を挿入するかどうかにかかわらず、100文字のスペースを占有します.varcharのような長くなるフィールドであれば、nullはスペースを占有しません.
numにデフォルト値0を設定して、テーブルのnum列にnull値がないことを確認し、クエリーを実行できます.

select id from t where num=0

3、where句での使用はできるだけ避けるべきです!=または<>オペレータ.そうしないと、エンジンはインデックスの使用を放棄してテーブル全体をスキャンします.
4、where句でorを使用して条件を接続することはできるだけ避けなければならない.1つのフィールドにインデックスがある場合、1つのフィールドにインデックスがない場合、エンジンはインデックスの使用を放棄して全表スキャンを行う.例えば:

select id from t where num=10 or Name='admin'

は、次のように問い合わせることができます.

select id from t where num=10 union all select id from t where Name='admin'

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

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

、次のクエリにより、テーブル全体がスキャンされます.

select id from t where name like ‘%abc%’

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

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

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

は次のように変更する必要があります.

select id from t where name like'abc%'
select id from t where createdate>='2005-11-30' and createdate

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

select col1,col2 into #t from t where1=0

のようなコードは結果セットを返さないが、システムリソースを消費する場合は、次のように変更する必要があります.

create table #t(…)

、Update文では、1、2フィールドのみを変更し、Updateのすべてのフィールドを使用しないでください.そうしないと、頻繁に呼び出されると、パフォーマンスが大幅に消費され、ログが大量に発生します.
14、複数のビッグデータ量(ここでは数百本でも大きい)のテーブルJOINについては、まずページを分けてからJOINしなければならない.そうしないと論理読みが高く、性能が悪い.
15、select count(*) from table;このように条件のないcountは全表スキャンを引き起こし、ビジネス上の意味がなく、必ず根絶しなければならない.
16、インデックスは多ければ多いほど良いわけではありません.インデックスは相応のselectの効率を高めることができますが、同時にinsertとupdateの効率を下げることができます.insertやupdateの時にインデックスを再構築する可能性があるので、どのようにインデックスを構築するかは慎重に考慮する必要があります.具体的な状況によって異なります.1つのテーブルのインデックス数は6つを超えないほうがいいです.多すぎる場合は、あまり使用されないカラムに作成されたインデックスが必要かどうかを考慮する必要があります.
17、clusteredインデックスデータ列の更新はできるだけ避けるべきである.clusteredインデックスデータ列の順序は表記録の物理記憶順序であり、この列の値が変更されると、表記録全体の順序の調整になり、かなりのリソースがかかるからである.アプリケーションシステムがclusteredインデックスデータ列を頻繁に更新する必要がある場合は、そのインデックスをclusteredインデックスとして構築するかどうかを考慮する必要があります.
18、できるだけ数字型のフィールドを使用し、数値情報のみを含むフィールドをできるだけ文字型に設計しないと、クエリーと接続のパフォーマンスが低下し、ストレージのオーバーヘッドが増加します.これは、エンジンがクエリーと接続を処理するときに文字列の各文字を1つずつ比較し、数値型では1回だけ比較すれば十分であるためです.
19、できるだけvarchar/nvarcharをchar/ncharの代わりに使用します.まず長くなるフィールドの記憶領域が小さくなるため、記憶領域を節約することができます.次に、クエリーにとって、比較的小さいフィールド内での検索効率は明らかに高いです.
20.どこも使わない

select * from t 

は「*」の代わりに特定のフィールドリストを使用し、使用できないフィールドを返さないでください.
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、できるだけクライアントにビッグデータ量を返すことを避け、データ量が大きすぎる場合、相応の需要が合理的かどうかを考慮しなければならない.
実例分析:大きいDELETE或いはINSERT文を分割して、大量にSQL文を提出する
オンラインのWebサイトで大きなDELETEやINSERTクエリーを実行する必要がある場合は、Webサイト全体を停止させないように注意する必要があります.この2つの操作は時計をロックするので、時計がロックされると、他の操作は入れません.
Apacheにはサブプロセスやスレッドがたくさんあります.そのため、かなり効率的に動作しますが、私たちのサーバもサブプロセス、スレッド、データベースリンクをあまり望んでいません.これはサーバリソース、特にメモリを大きく占めています.
もしあなたのテーブルをしばらくロックして、例えば30秒であれば、高いアクセス量を持つサイトにとって、この30秒で蓄積されたアクセスプロセス/スレッド、データベースリンク、開いたファイルの数は、WEBサービスを崩壊させるだけでなく、サーバー全体をすぐに停止させる可能性があります.
だから、大きな処理があれば、必ず分割して、LIMIT oracle(rownum)、sqlserver(top)条件を使うのが良い方法です.次にmysqlの例を示します.

while (1)
{
    //    1000 
    mysql_query(“delete from logs where log_date <= ’2015-11-01’ limit 1000”);
    if (mysql_affected_rows() == 0)
    {
       //     ，  ！break；
    }
    //         ，        /    。
    usleep(50000)
}