MySQLにおけるSQL文クエリの最適化に関するいくつかの一般的な手法

スター · 掲載地 2017/08/04 16:08:46

1. クエリを最適化するために、テーブル全体をスキャンすることは避け、まずは関係する列にインデックスを作成し、順番を付けることを検討してください。

2. where節で!=や<>演算子の使用を避けるようにしてください。そうしないとエンジンがインデックスの使用を放棄し、テーブル全体を走査してしまいます。

3. where節のフィールドに対してヌル値判定を避けるようにしてください。そうしないと、エンジンはインデックスの使用を放棄し、例えば以下の通りの完全なテーブルスキャンを行います。
numがnullであるTからIDを選択します。
numのデフォルト値を0に設定し、テーブルのnum列にnull値がないか確認してから、次のようにクエリを行えます:
n=0 のから id を t(n)から選択します

4. 条件を結合するためにwhere節でORを使うのは避けるべきです。そうしないとエンジンがインデックスの使用を放棄し、例えば以下のような完全なテーブルスキャンを実行してしまいます。
TからIDを選択し、num=10またはnum=20
このように問い合わせることができます:
n=10 の T から ID を選択します
全員連合
n=20 の T から ID を選択します

5. 以下のクエリも完全なテーブルスキャンを行います:
名前は「%ABC%」のように名前のTからIDを選択します。
効率を高めるために、全文検索を検討してください。

6.in と not in も注意して使用してください。そうしないと、例えば次のような完全なテーブルスキャンにつながります。
tからidを選択し、num in(1,2,3)を選びます。
連続的な数値の場合、もし使えるなら in を使わないでください:
Tから1から3の間に数が入るIDを選択します。

7. where節にパラメータを使うと、テーブル全体スキャンも発生します。 SQLはローカル変数を実行時にのみ解決しますが、最適化者はアクセス計画の選択を実行時まで延期することはできません。コンパイル時に選択しなければなりません。しかし、コンパイル時にアクセス計画が設定されている場合、変数の値はまだ未知のままで、インデックス選択の入力項目として使用できません。以下の声明が全文をスキャンされます。
TからIDを選択し、ここでNUM=@num
クエリにインデックスを強制的に使うこともできます:
TからIDを選択します。ここでNUM=@num

8. where節でフィールドを表現しないよう注意してください。これにより、エンジンがインデックスの使用をやめてテーブル全体走査に切り替える原因となります。例えば：
n/2=100 のから ID を T(数/2=100)を選択する
以下に変更すべきです:
n=100*2 の T から ID を選択します

9. where節のフィールドに対して関数操作を行うのは避けるようにしてください。これにより、エンジンがインデックスの使用を放棄し、テーブル全体走査に切り替える原因となります。例えば：
TからIDを選択し、そこでサブストリング(name,1,3)='abc' --ABCで始まる名前ID
Datediff(Day, createDate, '2005-11-30')=0--'2005-11-30' 生成IDを Tから選択
以下に変更すべきです:
名前が「ABC%」のようにTからIDを選択します。
ここで CreateDate>='2005-11-30' および createdate<'2005-12-1' のから ID を T から選択してください

10. ここで節の「=」の左側で関数、算術演算、その他の式操作を行わないでください。そうしないと、システムがインデックスを正しく使えない可能性があります。

11. インデックスフィールドを条件として使用する場合、インデックスが複合インデックスの場合、インデックスの最初のフィールドを条件として使用しなければならず、そうでなければインデックスは使われず、フィールドの順序はできるだけインデックスの順序と整合している必要があります。

12. 空のテーブル構造を生成するような意味のないクエリを書かないでください:
tからcol1,col2を #t に選択し、ここで1=0
この種のコードは結果セットを返しませんが、システムリソースを消費するため、次のように変更すべきです。
テーブルの作成 #t(...)

13. 多くの場合、は存在するに置き換えるのが良い選択です。
Num in の A から NUM を選択(B から Num を選択)
次の文に置き換えます。
存在する A から NUM を選択します(num=a.num の場合は b から 1 を選ぶ)

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

15. インデックスが多いほど良いわけではない。インデックスは対応するセレクトの効率を確かに向上させることができるが、インサートや更新の際にインデックスが再構築される可能性があるため、インサートや更新の効率を低下させる。そのため、インデックスの構築方法は状況に応じて慎重に検討する必要がある。テーブルにインデックスを6つ以上持たないのが最適で、多すぎる場合はあまり使われない列にインデックスを作る必要があるか検討してください。

16. クラスタインデックスデータ列の更新はできるだけ避けてください。なぜなら、クラスタインデックスデータ列の順序はテーブルレコードの物理的な保存順序であり、列の値が変わるとテーブルレコード全体の順序が調整され、かなりのリソースを消費するためです。もしアプリケーションがクラスタインデックスの列を頻繁に更新する必要があるなら、インデックスをクラスタインデックスとして構築すべきかどうかを検討する必要があります。

17. 数値フィールドを使用し、数値情報のみを文字として含むフィールドは避けてください。これはクエリや接続の性能を低下させ、ストレージのオーバーヘッドを増加させます。これは、クエリや結合を処理する際にエンジンが文字列内の各文字を逐字比較するのに対し、数値型の場合は一度だけ比較すれば済むためです。

18. char/ncharの代わりにvarchar/nvarcharをできるだけ使いましょう。なぜなら、フィールドストレージが長いほどストレージ容量を節約できること、そしてクエリの場合、比較的小さなフィールドでの検索効率が明らかに高いからです。

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

20. 一時的なテーブルではなくテーブル変数を使うようにしましょう。テーブル変数に大量のデータが含まれている場合、インデックスは非常に制限されていることに注意してください(プライマリキーインデックスのみ)。

21. システムテーブル資源の消費を減らすために、一時テーブルの頻繁な作成や削除を避けること。

22. 一時テーブルは使えないものではなく、適切に使用することで、例えば大きなテーブルやよく使われるデータセットを繰り返し参照する必要がある場合など、一部のルーチンをより効果的にします。ただし、一度きりのイベントの場合はエクスポートテーブルを使うのが最善です。

23. 一時テーブルを作成する際、一度に挿入されるデータ量が多い場合は、テーブル作成の代わりに「Selectイン」を使うことで大量のログが発生しないように速度が向上します。データ量が少ない場合は、システムテーブルのリソースを軽減するために、まずテーブルを作成し、その後挿入してください。

24. 一時テーブルを使用する場合は、ストアドプロシージャの最後にすべての一時テーブルを明示的に削除し、まずテーブルを切り捨ててからテーブルを破棄し、システムテーブルが長期間ロックされるのを防ぎましょう。

25. カーソルの使用は避けてください。カーソルの効率が悪いため、カーソルで動作するデータが10,000行を超える場合は書き換えを検討すべきです。

26. カーソルベースや一時的なテーブル法を使う前に、集合ベースの解法を探すべきであり、これらの方法の方がより効果的なことが多いです。

27. 一時テーブルと同様に、カーソルは使えないものではありません。小さなデータセットに対してFAST_FORWARDカーソルを使う方が、特に必要なデータを得るために複数のテーブルを参照する必要がある場合、他の行ごとの処理方法よりも優れていることが多いです。結果セットに「total」を含むルーチンは、カーソルで実行されるものよりも通常高速です。開発時間に余裕があれば、カーソルベースと集合ベースの方法の両方を試して、どちらがより効果的かを試すことができます。

28. すべてのストアドプロシージャとトリガーの冒頭にSET NOCOUNT ONを設定し、最後にSET NOCOUNT OFFを配置します。ストアドプロシージャやトリガーの各文を実行した後にクライアントにDONE_IN_PROCメッセージを送る必要はない。

29. 大量のデータがクライアントに返却されないようにし、データ量が多すぎる場合は対応する需要が合理的かどうかを考慮してください。

30. 大規模なトランザクション操作を避け、システムの並行性を向上させること。

スター · 掲載地 2017/08/04 16:14:30

最適化の原則:小さなテーブルが大きなテーブルを駆動します。つまり、小さなデータセットが大きなデータセットを駆動します。

スター · 掲載地 2017/08/04 16:22:14

クエリ速度を向上させるために、合理的に以下のインデックスを作成しましょう。
a.goods_name,a.goods_number,a.goods_price,a.goods_track,b.payment,b.confirm_time,b.pay_status,b.id,b.sn,b.ware_house,b.total_amount,b.final_amount,b.cpns_amount,b.sale_amount,b.cost_amount,'goods_count','pay_status',b.ship_status,b.createtime,b.payment,b.order_status,b.pay_time,b.shr_name,b.shr_phone,b.shr_province,b.shr_ phone、b.shr_province、b.shr_city、b.shr_area、b.shr_address、b.remark、b.order_from、b.send_time、b.pay_sn、c.spec_info、c.cost_price、d.company、d.list、d.is_error、e.shop_sn goods_sn sh_order_goodsから左に join sh_order b on a.order_id=b.id 左にjoin sh_product c on a.product_id=c.id 左にjoin sh_send in d on on a.order_id=d.order_id 左にjoin in in sh_goods e on a.goods_ id=e.id id DESC による順序
この種のデータクエリは機能を実現できますが、数百万のデータ量に遭遇するとタイムアウトが発生し、サービス崩壊に深刻な原因となる可能性があります。原理は1000×1000×1000で、遅いと想像できます。単一のテーブルでデータをチェックし、加算を試してみてください。特に、eコマースは最もタブーな共同質問です。

[ヒント] MySQLにおけるSQL文クエリの最適化に関するいくつかの一般的な手法

スコア

閲覧したセクション