高品質で高性能な SQL クエリ ステートメントを作成する方法

1. まず、実行計画とは何なのかを理解する必要があります。

実行プランは、SQL ステートメントと関連テーブルの統計情報に基づいてデータベースによって作成されるクエリ プランです。このプランはクエリ オプティマイザによって自動的に分析されます。たとえば、SQL ステートメントが100,000 レコードを抽出する テーブル内の 1 つのレコードを検索するために、クエリ オプティマイザーは「インデックス検索」方法を選択します。テーブルがアーカイブされ、5,000 レコードしか残っていない場合、クエリ オプティマイザーは計画を変更し、「フル テーブル スキャン」を使用します。 " "方法。

実行計画は固定されたものではなく、「個別化」されていることがわかります。正しい「実行計画」を生成するには、次の 2 つの重要なポイントがあります。

(1) SQL ステートメントはクエリ オプティマイザーに何を実行したいかを明確に伝えていますか?

(2) クエリ オプティマイザーによって取得されたデータベース統計は最新かつ正確ですか?

2. SQL ステートメントの統一された記述方法

次の 2 つの SQL ステートメントについて、プログラマはそれらが同じであると考えますが、データベース クエリ オプティマイザーはそれらが異なると考えます。

select*from Dual

select*From Dual

実際には、ケースが異なります。クエリ アナライザーは、これを 2 つの異なる SQL ステートメントとみなして、2 回解析する必要があります。 . . 2 つの実行プランを生成します。したがって、プログラマは、同じクエリ ステートメントがどこでも一貫していることを確認する必要があります。スペースが 1 つ多くても機能しません。

3. SQL ステートメントを作成する際に注意すべき問題

次に、SQL ステートメントを作成する際に注意すべきいくつかの問題について詳しく説明します。これらの where 句では、一部の列にインデックスがある場合でも、記述された SQL の品質が低いため、システムは SQL ステートメントの実行時にインデックスを使用できず、テーブル全体のスキャンも使用するため、応答速度が非常に遅くなります。削減。

1.IS NULL と IS NOT NULL

null をインデックスとして使用することはできません。また、null 値を含む列はインデックスに含まれません。インデックスに複数の列がある場合でも、そのうちの 1 つの列に null が含まれている限り、その列はインデックスから除外されます。つまり、列に NULL 値がある場合、その列にインデックスを構築してもパフォーマンスは向上しません。

where 句で is null または is not null を使用するステートメント オプティマイザーは、インデックスを使用できません。

2. 互換性のないデータ型の使用は避けてください。

互換性のないデータ型はテーブル全体の取得データの型変換を表しており、アクセスはテーブル全体のスキャンになります

select * fromemployee where last_name = 100; last_name が varchar であることに注意してくださいtype

3. 列の結合

結合のある列の場合、最終的な結合値が静的な値であっても、オプティマイザーはインデックスを使用しません。一緒に例を見てみましょう。従業員テーブル (employee) があるとします。従業員の姓名は 2 つの列 (FIRST_NAME と LAST_NAME) に格納されます。次に、Bill Cliton という名前の従業員をクエリしたいとします。

以下は結合クエリを使用した SQL ステートメントです。

select * fromEmployss where first_name||''||last_name ='Beill Cliton';

上記はこのステートメントは、Bill Cliton という名前の従業員がいるかどうかを完全にクエリできますが、システム オプティマイザは last_name に基づいて作成されたインデックスを使用しないことに注意してください。

次の SQL ステートメントを記述すると、Oracle システムは last_name に基づいて作成されたインデックスを使用できるようになります。

*** where first_name ='Beill' and last_name ='Cliton';

4. ワイルドカード文字 (%) で始まる文のような文です

現在の需要は次のようなものですthis は、名前に clton が含まれる人々について従業員テーブルを照会するようリクエストしています。次のクエリ SQL ステートメントを使用できます:

select * fromemployee where last_name like '%cliton%'. ワイルドカード文字 (%) が検索語の先頭に表示されるため、Oracle システムはワイルドカード文字 (%) を使用しません。 last_name のインデックス。ただし、文字列内の別の場所にワイルドカードが出現すると、オプティマイザはインデックスを利用できます。インデックスは次のクエリで使用されます:

select * fromemployee where last_name like 'c%'

5. インデックス フィールドに対して操作を実行すると、インデックスが無効になります。

WHERE 句内のフィールドに対して関数または式の操作を実行しないようにしてください。これにより、エンジンがインデックスの使用を断念し、テーブル全体のスキャンが実行されます。

例: SELECT * FROM T1 WHERE F1/2=100 を次のように変更する必要があります: SELECT * FROM T1 WHERE F1=100*2

6. ステートメントによる順序

ORDER BY ステートメントは、返されたクエリ結果を Oracle がどのように並べ替えるかを決定します。 Order by ステートメントには、並べ替える列に特別な制限はなく、関数 (結合や追加など) を列に追加することもできます。 Order by ステートメント内の非インデックス項目または計算式があると、クエリの速度が低下します。

order by ステートメントを注意深くチェックして、インデックスのない項目や式を見つけてください。パフォーマンスが低下します。この問題の解決策は、インデックスを使用するように order by ステートメントを書き直すことです。使用する列に別のインデックスを作成することもできます。同時に、order by 句で式を使用することは絶対に避けてください。

7. NOT

クエリを実行するときに、where 句で、より大きい、より小さい、等しい、等しくないなどの論理式をよく使用します。 （そして）、または（または）、そしてそうではありません（そうではありません）。 NOT は、論理演算記号を否定するために使用できます。以下は NOT 句の例です:

... where not (status ='VALID')

NOT を使用する場合は、否定句の前にかっこを置き、句の前に NOT 演算子を置く必要があります。 NOT 演算子は、別の論理演算子 (不等号 (<>) 演算子) 内に含まれています。つまり、NOT 単語がクエリの where 句に明示的に追加されていない場合でも、演算子には NOT が含まれています。次の例を参照してください。

... where status <>'INVALID ';

このクエリは NOT:

select * fromemployee where給与3000;

を使用せずに書き直すことができますが、これら 2 つのクエリの結果は次のようになります。同様に、2 番目のクエリ プランは最初のクエリ プランよりも高速になります。 2 番目のクエリでは、Oracle は給与列にインデックスを使用できますが、最初のクエリではインデックスを使用できません。

8. IN と EXISTS

列が一連の値と比較されることがあります。最も簡単な方法は、where 句でサブクエリを使用することです。 where 句では 2 つの形式のサブクエリを使用できます。

最初の形式は、IN 演算子を使用することです:

...where column in(select * from ... where ...);

2 番目の形式形式は EXIST 演算子を使用することです:

... where names (select 'X' from ...where ...);

ほとんどの人は最初の演算子を使用すると思います。 1 つは書きやすいため、2 番目の形式は実際には最初の形式よりもはるかに効率的です。 Oracle では、ほぼすべての IN 演算子のサブクエリを EXISTS を使用したサブクエリとして書き換えることができます。

2 番目の形式では、サブクエリは「select 'X'」で始まります。 EXISTS 句を使用すると、サブクエリがテーブルからどのようなデータを抽出するかに関係なく、where 句のみが参照されます。このように、オプティマイザはテーブル全体を走査する必要がなく、インデックスのみに基づいて作業を完了できます (これは、where ステートメントで使用される列にインデックスがあることを前提としています)。 IN 句と比較すると、EXISTS は接続されたサブクエリを使用しますが、これは IN サブクエリよりも構築が困難です。

EXIST を使用すると、Oracle システムは最初にメイン クエリをチェックし、最初の一致が見つかるまでサブクエリを実行するため、時間が節約されます。 Oracle システムが IN サブクエリを実行するときは、まずサブクエリを実行し、取得した結果リストをインデックス付き一時テーブルに格納します。サブクエリを実行する前に、システムはメイン クエリを一時停止し、サブクエリが完了した後、一時テーブルに格納されてからメイン クエリが実行されます。これが、一般的なクエリに IN を使用するよりも EXISTS を使用した方が高速である理由です。

同時に、可能な限り NOT IN ではなく NOT EXISTS を使用する必要があります。両方とも NOT を使用しますが (インデックスを使用すると速度が低下します)、NOT EXISTS は NOT IN クエリよりも効率的です。 。

9. 条件を接続するために where 句で または を使用することは避けてください。そうしないと、エンジンはインデックスの使用を断念し、テーブル全体のスキャンを実行します。

例: select id fromemployee where num= 10 または num=20

は次のようにクエリできます: select id fromemployee where num=10 Union all select ID fromemployee where num=20

10. ペアリングを避けるようにしてください。 where 句のフィールド 式操作の実行

これにより、エンジンはインデックスの使用を断念し、テーブル全体のスキャンを実行します。例: select id from t where num/2=100 を次のように変更する必要があります: select id from t where num=100*2

11. where 句内のフィールドに対して関数演算を実行することは避けてください

これにより、エンジンはインデックスの使用を断念し、テーブル全体のスキャンを実行します。例: select id from t where substring(name,1,3)='abc'、名前が abc で始まる ID は次のように変更する必要があります:

select id from t where name like 'abc%'

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

13. インデックス フィールドを条件として使用する場合、インデックスが複合インデックスの場合、インデックスの最初のフィールドを条件として使用して、システムが確実にインデックスを使用する必要があります。そうでない場合、インデックスはは使用されず、フィールドの順序はインデックスの順序とできる限り一致する必要があります。

14. インデックスは多ければ多いほど良いです。

インデックスを使用すると、対応する選択の効率が向上しますが、挿入や更新の際にエラーが発生する可能性があるため、挿入と更新の効率も低下します。インデックスを再構築するため、インデックスの構築方法は特定の状況に応じて慎重に検討する必要があります。 1 つのテーブルに 6 つを超えるインデックスを持たないことが最善ですが、多すぎる場合は、一般的に使用されない一部の列にインデックスを構築する必要があるかどうかを検討する必要があります。

15. 数値フィールドを使用するようにしてください。フィールドに数値情報のみが含まれる場合は、文字フィールドとして設計しないようにしてください。これにより、クエリと接続のパフォーマンスが低下し、ストレージのオーバーヘッドが増加します。これは、エンジンがクエリや接続を処理するときに文字列内の各文字を 1 つずつ比較し、数値型の場合は 1 回の比較だけで十分であるためです。

16. char/nchar の代わりに varchar/nvarchar をできるだけ使用してください。これは、第一に、可変長フィールドの記憶領域が小さく、記憶領域を節約できるからです。第 2 に、クエリの検索効率が比較的高くなります。小さなフィールドは高い、明らかに高い。

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

以上が高品質で高性能な SQL クエリ ステートメントを作成する方法の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。