sql百万級データベース最適化方案の共有
4294 ワード
1.クエリーを最適化し、表全体のスキャンをできるだけ避けるべきで、まずwhere及びorder byに関する列にインデックスを作成することを考慮しなければならない。
2.Whereサブカルでフィールドのnull値の判断をできるだけ避けるべきで、そうでないとエンジンがインデックスの使用を放棄して全表スキャンを行うことになります。例えば、select id from t where num is null
numにデフォルト値0を設定してもいいです。num列にnull値がないことを確認して、このように調べます。select id from t where num=0
3.where子文では使わないようにしてください！または<>操作符を使用しないと、エンジンはインデックスを使用せずに全表スキャンを行います。
4.Where子文でorを使用して条件を接続しないとエンジンがインデックスを使用せずに全表スキャンを行います。例えば、select id from t where num=10 or num=20
このように問い合わせることができます。select id from t where num=10 union allselect id from t where num=20
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
6.次の検索も全表スキャンにつながる。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='abc'Cname abcで始まるidselect id from t where datediff(day，createdate，'2005-1-30')=0 C‘2005-1-30’で生成されるid
select id from t where name like'abc%select id from t where createdate='2005-1-30'and createdate''
10.where子文の「=」の左側で関数、算術演算、または他の表式演算を行わないと、システムは正しくインデックスを使用できないかもしれません。
11.インデックスフィールドを条件として使用する場合、このインデックスが複合インデックスである場合は、そのインデックスの最初のフィールドを条件として使用する必要があります。そうでないと、インデックスは使用されなくなり、可能な限り、フィールドの順序をロープ順と一致させる必要があります。
12.意味のない問い合わせは書かないでください。テーブル構造を生成する必要があります。
このようなコードは結果集には戻りませんが、システム資源が消耗するので、これに変えます。
13.existsをinの代わりに使うのはいい選択です。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はほぼ半分になります。
15.インデックスが多ければ多いほどいいというわけではないので、インデックスは相応のselectの効率を高めることができますが、insertとudateの効率を下げました。insertやudateの時にインデックスを再構築する可能性があるので、インデックスを作るにはどうすればいいかを慎重に考え、具体的な状況に応じて決めます。一つの表の索引の数は6つを超えないほうがいいです。多すぎると、あまり使われない列のインデックスが必要かどうかを考えなければなりません。
16.clusteredインデックスデータ列の更新は、clusteredインデックスデータ列の順序がテーブルレコードの物理的記憶順序であるため、列の値が変更されると、テーブルレコード全体の順序が調整され、かなりのリソースがかかります。アプリケーションシステムが頻繁にclusteredインデックスデータ列を更新する必要がある場合、そのインデックスをclusteredインデックスとして構築するべきかどうかを考慮する必要があります。
17.できるだけ数字フィールドを使用して、数値情報だけを含むフィールドはなるべく文字型にしないようにしてください。これはクエリーと接続の性能を低下させ、記憶オーバーヘッドを増加させます。これは、検索と接続を処理する際に、文字列のそれぞれの文字を一つ一つ比較しますが、数字型では一回だけ比較すればいいからです。
18.できるだけchar/ncharの代わりにvarhar/nvarrharを使用する。最初は長いフィールドの記憶空間が小さいので、記憶空間を節約することができ、次にクエリにとって比較的小さいフィールドでの検索効率は明らかに高い。
19.任意の場所でselect*from tを使用しないでください。具体的なフィールドリストを「*」の代わりに使いきれないフィールドに返さないでください。
20.臨時表の代わりにできるだけ表変数を使用します。テーブル変数に大量のデータが含まれている場合、インデックスは非常に限られています。
21.システムテーブルのリソースの消費を減らすために、頻繁に一時テーブルを作成し削除することを避ける。
22.臨時表は、使用できないわけではないので、それらを適切に使用すると、例えば大型表または常用表のいずれかのデータセットを重複して参照する必要がある場合に、いくつかのルーチンをより効果的にすることができる。ただし、一度のイベントには、エクスポートテーブルを使うのが望ましいです。
23.臨時表を新規作成する時、一度にデータを挿入する量が大きいなら、create tableの代わりにselect intoを使って、大量のlogsを起こさないようにして、スピードを上げる。データ量が少ない場合は、システムテーブルの資源を緩和するために、まずcreate tableを作成してからinsertを行うべきです。
24.臨時テーブルを使用すると、保存プロセスの最後に必ずすべての臨時テーブルが明示的に削除され、先にtruncateテーブルを削除し、その後drop tableを使用すると、システムテーブルの長期的なロックを回避することができる。
25.ラベルの使用はできるだけ避けてください。ビーコンの効率が悪いので、ビーコン操作のデータが1万行を超えたら、書き換えを考慮しなければなりません。
26.ラベルベースの方法または一時表方法を使用する前に、セットベースのソリューションを探して問題を解決するべきであり、セットベースの方法は一般により効果的である。
27.臨時表と同様に、遊標は使用できないわけではない。小型データセットにFAST_を使用するFOWARDラベルは通常他のプログレッシブ処理方法より優れています。特にいくつかのテーブルを参照してこそ必要なデータが得られます。結果集に「合計」を含むルーチンは、通常、遊覧標識を使用して実行するよりも速いです。開発時間が許容されるなら、どの方法の効果がより良いかを見るために、ツアーベースの方法とセットベースの方法を試してみてもいいです。
28.すべての記憶過程とトリガーの開始箇所にSET NOCOUNT ONを設定し、終了時にSET NOCOUNT OFFを設定します。ストレージプロセスとフリップフロップの各ステートメントを実行した後にクライアントにDONE_を送信する必要はありません。IN_PROCメッセージ。
29.大きな仕事の操作をできるだけ避け、システムの合併能力を高める。
30.クライアントに大きなデータ量を返さないようにします。データ量が大きすぎると、対応する需要が合理的かどうかを考慮しなければなりません。
2.Whereサブカルでフィールドのnull値の判断をできるだけ避けるべきで、そうでないとエンジンがインデックスの使用を放棄して全表スキャンを行うことになります。例えば、select id from t where num is null
numにデフォルト値0を設定してもいいです。num列にnull値がないことを確認して、このように調べます。select id from t where num=0
3.where子文では使わないようにしてください！または<>操作符を使用しないと、エンジンはインデックスを使用せずに全表スキャンを行います。
4.Where子文でorを使用して条件を接続しないとエンジンがインデックスを使用せずに全表スキャンを行います。例えば、select id from t where num=10 or num=20
このように問い合わせることができます。select id from t where num=10 union allselect id from t where num=20
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
6.次の検索も全表スキャンにつながる。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='abc'Cname abcで始まるidselect id from t where datediff(day，createdate，'2005-1-30')=0 C‘2005-1-30’で生成されるid
select id from t where name like'abc%select id from t where createdate='2005-1-30'and createdate''
10.where子文の「=」の左側で関数、算術演算、または他の表式演算を行わないと、システムは正しくインデックスを使用できないかもしれません。
11.インデックスフィールドを条件として使用する場合、このインデックスが複合インデックスである場合は、そのインデックスの最初のフィールドを条件として使用する必要があります。そうでないと、インデックスは使用されなくなり、可能な限り、フィールドの順序をロープ順と一致させる必要があります。
12.意味のない問い合わせは書かないでください。テーブル構造を生成する必要があります。
このようなコードは結果集には戻りませんが、システム資源が消耗するので、これに変えます。
13.existsをinの代わりに使うのはいい選択です。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はほぼ半分になります。
15.インデックスが多ければ多いほどいいというわけではないので、インデックスは相応のselectの効率を高めることができますが、insertとudateの効率を下げました。insertやudateの時にインデックスを再構築する可能性があるので、インデックスを作るにはどうすればいいかを慎重に考え、具体的な状況に応じて決めます。一つの表の索引の数は6つを超えないほうがいいです。多すぎると、あまり使われない列のインデックスが必要かどうかを考えなければなりません。
16.clusteredインデックスデータ列の更新は、clusteredインデックスデータ列の順序がテーブルレコードの物理的記憶順序であるため、列の値が変更されると、テーブルレコード全体の順序が調整され、かなりのリソースがかかります。アプリケーションシステムが頻繁にclusteredインデックスデータ列を更新する必要がある場合、そのインデックスをclusteredインデックスとして構築するべきかどうかを考慮する必要があります。
17.できるだけ数字フィールドを使用して、数値情報だけを含むフィールドはなるべく文字型にしないようにしてください。これはクエリーと接続の性能を低下させ、記憶オーバーヘッドを増加させます。これは、検索と接続を処理する際に、文字列のそれぞれの文字を一つ一つ比較しますが、数字型では一回だけ比較すればいいからです。
18.できるだけchar/ncharの代わりにvarhar/nvarrharを使用する。最初は長いフィールドの記憶空間が小さいので、記憶空間を節約することができ、次にクエリにとって比較的小さいフィールドでの検索効率は明らかに高い。
19.任意の場所でselect*from tを使用しないでください。具体的なフィールドリストを「*」の代わりに使いきれないフィールドに返さないでください。
20.臨時表の代わりにできるだけ表変数を使用します。テーブル変数に大量のデータが含まれている場合、インデックスは非常に限られています。
21.システムテーブルのリソースの消費を減らすために、頻繁に一時テーブルを作成し削除することを避ける。
22.臨時表は、使用できないわけではないので、それらを適切に使用すると、例えば大型表または常用表のいずれかのデータセットを重複して参照する必要がある場合に、いくつかのルーチンをより効果的にすることができる。ただし、一度のイベントには、エクスポートテーブルを使うのが望ましいです。
23.臨時表を新規作成する時、一度にデータを挿入する量が大きいなら、create tableの代わりにselect intoを使って、大量のlogsを起こさないようにして、スピードを上げる。データ量が少ない場合は、システムテーブルの資源を緩和するために、まずcreate tableを作成してからinsertを行うべきです。
24.臨時テーブルを使用すると、保存プロセスの最後に必ずすべての臨時テーブルが明示的に削除され、先にtruncateテーブルを削除し、その後drop tableを使用すると、システムテーブルの長期的なロックを回避することができる。
25.ラベルの使用はできるだけ避けてください。ビーコンの効率が悪いので、ビーコン操作のデータが1万行を超えたら、書き換えを考慮しなければなりません。
26.ラベルベースの方法または一時表方法を使用する前に、セットベースのソリューションを探して問題を解決するべきであり、セットベースの方法は一般により効果的である。
27.臨時表と同様に、遊標は使用できないわけではない。小型データセットにFAST_を使用するFOWARDラベルは通常他のプログレッシブ処理方法より優れています。特にいくつかのテーブルを参照してこそ必要なデータが得られます。結果集に「合計」を含むルーチンは、通常、遊覧標識を使用して実行するよりも速いです。開発時間が許容されるなら、どの方法の効果がより良いかを見るために、ツアーベースの方法とセットベースの方法を試してみてもいいです。
28.すべての記憶過程とトリガーの開始箇所にSET NOCOUNT ONを設定し、終了時にSET NOCOUNT OFFを設定します。ストレージプロセスとフリップフロップの各ステートメントを実行した後にクライアントにDONE_を送信する必要はありません。IN_PROCメッセージ。
29.大きな仕事の操作をできるだけ避け、システムの合併能力を高める。
30.クライアントに大きなデータ量を返さないようにします。データ量が大きすぎると、対応する需要が合理的かどうかを考慮しなければなりません。