SQL を最適化する 30 の一般的な方法-mysql チュートリアル-php.cn

mysql大量データ処理時の最適化クエリスピードメソッド

最近、仕事の都合上、Mysqlデータベースのselectクエリ文に関する最適化メソッドに注目し始めています。。

というのも、私が実際に参加したプロジェクトでは、mysqlテーブルのデータ量が数百万に達すると通常のSQLクエリの効率が急激に低下し、whereに多くのクエリ条件が存在するとクエリ速度が耐えられないほどになることが分かりました。。以前、(インデックスを使用した) 400 万件以上のレコードを含むテーブルで条件付きクエリをテストしたことがありますが、そのクエリ時間は 40 秒にも及びました。これほど長いクエリ遅延は、どんなユーザーも気が狂うほどだと思います。したがって、SQL文のクエリをいかに効率化するかが非常に重要になります。以下は、インターネット上で広く流通している 30 の SQL クエリステートメントの最適化方法です:

1. where 句で != または <>operator を使用しないようにしてください。使用しない場合、エンジンは、インデックスを作成し、テーブル全体のスキャンを続行します。

2. クエリを最適化するには、テーブル全体のスキャンを避けるようにしてください。まず、where と order に関係する列にインデックスを作成することを検討してください。免 3. where 句のフィールドで

Null

値の判断を避けるようにしてください。そうしないと、エンジンがインデックスの使用を放棄し、次のようにテーブル全体をスキャンします。 Select id from t where num is num Setテーブルの num 列に NULL 値がないことを確認するためにデフォルト値 0 を設定し、次のようにクエリします: SELECT ID from T WHERE NUM = 0

4. where 句での使用または接続条件の使用は避けてください。そうでない場合は、これにより、エンジンはインデックスの使用を放棄し、次のような完全なテーブルスキャンを実行します:
Select id from t where num=10 または num=20

次のようにクエリできます:

Select id from t where num= 10

union

all select id from t where num =20 5. 次のクエリも完全なテーブルスキャンになります: (パーセント記号の前に置くことはできません)
を使用します。の前にパーセント記号を付けて使用します。 . ‐ out out out of t のような名前で

。

6. in と not in も注意して使用する必要があります。そうしないと、次のような完全なテーブルスキャンが行われます。
Select id from t where num in (1,2,3)

連続値の場合、 between で使用できますが、Now では使用しないでください:

out out out out out out out out out outcoming in t where num は 1 から 3 までです。 SQL は実行時にのみローカル
変数
を解決するため、オプティマイザーはアクセスプランの選択を実行時に延期することはできず、コンパイル時に選択する必要があります。ただし、アクセスプランがコンパイル時に作成される場合、変数の値はまだ不明であるため、インデックス選択の入力として使用できません。たとえば、次のステートメントは完全なテーブルでスキャンされます:
Select ID from T WHERE NUM =@NUM

は、インデックスを使用した強制クエリに変更できます:

SELECT ID FROM TOTH フィールドに対して expression 操作を実行しないようにする必要があります。これにより、エンジンはインデックスの使用を断念し、テーブル全体のスキャンを実行します。例: c Select ID from T WHERE NUM/2 = 100
を次のように変更する必要があります:
Select ID from T WHERE NUM = 100*2

9. where 句内のフィールドに対して function 操作を実行しないようにしてください。これにより、エンジンがインデックスの使用を断念し、テーブル全体のスキャンが実行されます。例:
select id from t where substring(name,1,3)='abc' – abc で始まる名前 ID
select id from t wheredatediff(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' および createate<'2005-12-1'

10. where 句の "= の左側で関数、算術演算、またはその他の式演算を実行しないでください。システムインデックスが正しく使用できない可能性があります。

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

12. 無意味なクエリを書かないでください。たとえば、空のテーブル構造を生成する必要がある場合:

selectcol1,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 names(select 1 from b) where num=a.num)

14. すべてのインデックスがクエリに有効であるわけではありません。インデックス列に大量の重複データがある場合、SQL クエリは最適化されないことがあります。たとえば、テーブルに性別フィールドがあり、ほぼ半数が男性、半数が女性の場合、性別に基づいてインデックスが構築されたとしても、クエリの効率には影響しません。

15. インデックスは多いほど良いです。インデックスは対応する選択の効率を向上させますが、挿入または更新中にインデックスが再構築される可能性があるため、挿入と更新の効率も低下します。そのため、注意が必要です。インデックスの構築方法はケースバイケースで検討されます。テーブルに 6 つを超えるインデックスを持たないことをお勧めします。多すぎる場合は、一般的に使用されない一部の列にインデックスを構築する必要があるかどうかを検討する必要があります。

16. クラスター化インデックスデータ列の順序は、テーブルレコードの物理的な格納順序になるため、列の値が変更されると、テーブルレコード全体の順序が調整されるため、クラスター化インデックスデータ列の

更新

をできるだけ避けてください。、莫大なリソースがかかります。アプリケーションシステムがクラスター化インデックスのデータ列を頻繁に更新する必要がある場合は、インデックスをクラスター化インデックスとして構築する必要があるかどうかを検討する必要があります。

17. 数値フィールドを使用するようにしてください。フィールドに数値情報のみが含まれる場合は、クエリと接続のパフォーマンスが低下し、ストレージのオーバーヘッドが増加するように設計しないでください。これは、エンジンがクエリと接続を処理するときに string

内の各文字を 1 つずつ比較し、数値型の場合は 1 回の比較だけで十分であるためです。

18. できる限り char/nchar の代わりに varchar/nvarchar を使用してください。第一に、可変長フィールドはストレージ容量が小さく、ストレージ容量を節約できるからです。次に、クエリの場合、比較的小さなフィールドでの検索の方が明らかに効率的です。より効率的です。

19. select * from t をどこでも使用せず、「*」を特定のフィールドリストに置き換え、未使用のフィールドを返さないでください。

20. 一時テーブルの代わりにテーブル変数を使用してみてください。テーブル変数に大量のデータが含まれている場合は、インデックスが非常に制限される (主キーインデックスのみ) ことに注意してください。

21. システムテーブルリソースの消費を減らすために、一時テーブルの頻繁な作成と

削除

を避けてください。

22. 一時テーブルは使用できないわけではありません。一時テーブルを適切に使用すると、たとえば、大きなテーブルやよく使用されるテーブル内の特定のデータセットを繰り返し参照する必要がある場合など、特定のルーチンをより効率的に使用できます。ただし、1 回限りのイベントの場合は、エクスポートテーブルを使用することをお勧めします。

23. 一時テーブルを作成するときに、一度に大量のデータが挿入される場合は、create table の代わりに select into を使用すると、データ量が増加して高速化するために大量のログが発生することを避けることができます。システムテーブルの作成を容易にするため、大きくはありません。リソースの場合は、最初にテーブルを作成してから挿入する必要があります。 24. 一時テーブルを使用する場合は、システムテーブルの長期ロックを避けるために、

ストアドプロシージャ

の最後ですべての一時テーブルを明示的に削除し、最初にテーブルを切り捨ててからテーブルを削除する必要があります。 25.

カーソル操作

のデータが10,000行を超える場合は、カーソルの使用を避けるようにしてください。 26. カーソルベースの方法または一時テーブル方法を使用する前に、まずセットベースの解決策を探して問題を解決する必要があります。通常はセットベースの方法の方が効果的です。

27. 一時テーブルと同様に、カーソルも使用できないわけではありません。小規模なデータセットに使用する FAST_

FOR

WARD カーソルは、特に必要なデータを取得するために複数のテーブルを参照する必要がある場合、他の行ごとの処理方法よりも優れていることがよくあります。結果セットに「合計」を含むルーチンは、通常、カーソルを使用するよりも高速です。開発時間が許せば、カーソルベースの方法とセットベースの方法の両方を試して、どちらの方法がより効果的に機能するかを確認できます。 28. すべてのストアドプロシージャと

トリガー

の先頭で SET NOCOUNT ON を設定し、最後に SET NOCOUNT OFF を設定します。ストアドプロシージャとトリガーの各ステートメントの後に、クライアントに DONE_IN_PROC メッセージを送信する必要はありません。 29. クライアントに大量のデータを返さないようにしてください。データの量が大きすぎる場合は、対応する要件が妥当であるかどうかを検討する必要があります。

30. 大規模な

トランザクション操作

を回避し、システムの同時実行性を向上させるようにしてください。

以上がSQL を最適化する 30 の一般的な方法の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。