パフォーマンスの最適化を教える 52 個の SQL 文

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

2. where 句のフィールドで NULL 値の判断を避けるようにしてください。テーブルを作成するときのデフォルト値は NULL ですが、ほとんどの場合、NOT NULL を使用するか、デフォルト値として 0 や -1 などの特別な値を使用する必要があります。

3. where 句では != または <> 演算子の使用を避けてください。MySQL は、<、<=、=、>、>=、BETWEEN、IN、および場合によっては LIKE の演算子に対してのみインデックスを使用します。

4. where 句で条件を接続するために または を使用しないでください。そうしないと、エンジンはインデックスの使用を放棄し、完全なテーブルスキャンを実行します。クエリを組み合わせるには UNION を使用できます: select id from t where num=10 union all select id from t where num=20

5. in は注意して使用してください。そうしないと、テーブル全体のスキャンが発生します。連続した値の場合は、in ではなく between を使用します。Select id from t where num between 1 and 3

6. 次のクエリも完全なテーブル スキャンになります: select id from t where name like '%abc%' または select id from t where name like '%abc'。効率を向上させるには、フルテキスト検索を検討してください。インデックスは、名前が「abc%」のようなtからIDを選択する場合にのみ使用されます。

7. where 句でパラメータが使用されている場合は、テーブル全体のスキャンも実行されます。

8. where 句内のフィールドに対する式操作を可能な限り避け、where 句内のフィールドに対する関数操作を可能な限り避けます。

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

10. インデックスは対応する選択の効率を確実に向上させますが、挿入や更新中にインデックスが再構築される可能性があるため、挿入や更新の効率も低下します。したがって、インデックスの構築方法は、具体的な状況に応じて慎重に検討する必要があります。テーブルのインデックスの数は 6 を超えないようにしてください。数が多すぎる場合は、頻繁に使用されない列のインデックスが必要かどうかを検討する必要があります。

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

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

13. 可能な限り、char/nchar ではなく varchar/nvarchar を使用します。まず、可変長フィールドはストレージ スペースをあまり占有しないため、ストレージ スペースを節約できます。次に、クエリの場合、比較的小さなフィールドで検索する方が明らかに効率的です。

14. 「すべてを返す: t から選択し、* を特定のフィールド リストに置き換え、未使用のフィールドを返さない」は使用しないことをお勧めします。

15. 大量のデータをクライアントに返さないようにしてください。データの量が多すぎる場合は、対応する要求が妥当かどうかを検討してください。

16. テーブル エイリアスを使用する: SQL ステートメントで複数のテーブルを接続する場合は、テーブル エイリアスを使用し、各列にエイリアスのプレフィックスを付けます。これにより、解析時間が短縮され、列のあいまいさによって発生する構文エラーが軽減されます。

17. 中間結果を保存するには「一時テーブル」を使用する

SQL ステートメントを簡素化する重要な方法は、一時テーブルを使用して中間結果を一時的に保存することです。ただし、一時テーブルの利点はそれだけではありません。一時結果を一時テーブルに一時的に保存することで、後続のクエリは tempdb に格納され、プログラム内でメイン テーブルを複数回スキャンする必要がなくなり、プログラム実行中に「更新ロック」をブロックする「共有ロック」が大幅に削減され、ブロックが軽減され、同時実行パフォーマンスが向上します。

18. 一部の SQL クエリ ステートメントには nolock を追加する必要があります。読み取りと書き込みは相互にブロックされます。同時実行パフォーマンスを向上させるために、一部のクエリに nolock を追加して、読み取り中に書き込みを許可することができます。ただし、コミットされていないダーティ データが読み取られる可能性があるという欠点があります。 nolock を使用するには 3 つの原則があります。 「挿入、削除、変更」に使用されるクエリ結果は、nolock では追加できません。クエリ対象のテーブルはページ分割が頻繁に発生するテーブルなので、nolock は注意して使用してください。一時テーブルを使用すると、「データの以前のイメージ」を保存することもできます。これは、Oracle の UNDO テーブルスペースに似た機能を持っています。一時テーブルを使用して同時実行パフォーマンスを向上できる場合は、nolock を使用しないでください。

19. 一般的な簡素化のルールは次のとおりです。テーブル接続 (JOIN) は 5 つ以下にし、中間結果を格納するために一時テーブルまたはテーブル変数の使用を検討します。サブクエリの使用頻度を減らし、ビューを深くネストしすぎないようにしてください。一般的に、2 つ以上のビューをネストしないことをお勧めします。

20. クエリする結果を事前に計算してテーブルに格納し、クエリ時に選択します。これはSQL7.0以前は最も重要な手段でした。例えば入院費の計算など。

21. OR 句は複数のクエリに分解し、UNION を介して接続できます。速度はインデックスが使用されているかどうかにのみ関係します。クエリで結合インデックスを使用する必要がある場合は、UNION all を使用する方が効率的です。複数の OR 句はインデックスを使用しないため、UNION の形式で書き換えられ、インデックスとの一致が試みられます。重要な問題は、インデックスを使用するかどうかです。

22. INの後の値のリストでは、最も頻繁に発生する値を先頭に、最も頻繁に発生しない値を末尾に配置して、判断回数を減らします。

23. ストアド プロシージャを使用するなどして、データ処理をサーバー上に配置して、ネットワークのオーバーヘッドを削減します。ストアド プロシージャは、コンパイルされ、最適化された SQL ステートメントを実行プランに編成してデータベースに保存します。これは制御フロー言語の集合であり、もちろん高速です。繰り返し実行される動的 SQL では、Tempdb に配置される一時ストアド プロシージャ (一時テーブル) を使用できます。

24. サーバーに十分なメモリがある場合は、スレッド数 = 最大接続数 + 5 に設定して、最大の効率を実現します。そうでない場合は、構成されたスレッド数 < 最大接続数を使用して、SQL SERVER のスレッド プールを有効にして問題を解決します。数が依然として = 最大接続数 + 5 である場合、サーバーのパフォーマンスが著しく低下します。

25. クエリが書き込み順序に関連付けられる順序

a.personMemberID を選択し、* chineseresume a、personmember b から、 personMemberID = b.referenceid かつ a.personMemberID = 'JCNPRH39681' (A = B、B = 'Number') の場合

a.personMemberID を選択し、* chineseresume a、personmember b から、a.personMemberID = b.referenceid かつ a.personMemberID = 'JCNPRH39681' かつ b.referenceid = 'JCNPRH39681' (A = B、B = 'Number'、A = 'Number') となります。

a.personMemberID、* を chineseresume a、personmember b から選択します。ここで、b.referenceid = 'JCNPRH39681' かつ a.personMemberID = 'JCNPRH39681' (B = 'Number'、A = 'Number')

26. レコードが存在するかどうかを判断するには、select count(1) ではなく exists を使用してください。count 関数はテーブル内の行数をカウントするためにのみ使用され、count(1) は count(*) よりも効率的です。

27. 「>」の代わりに「>=」を使用するようにしてください。

28. インデックスの使用仕様: インデックスの作成は、アプリケーションと連動して検討する必要があります。大規模な OLTP テーブルには、6 個を超えるインデックスを含めないようにすることをお勧めします。インデックス フィールド、特にクラスター化インデックスを可能な限りクエリ条件として使用します。必要に応じて、インデックス index_name を通じて指定されたインデックスを強制できます。大規模なテーブルをクエリするときは、テーブル スキャンを避けてください。必要に応じて、新しいインデックスの作成を検討してください。インデックス フィールドを条件として使用する場合、インデックスが結合インデックスである場合は、インデックスの最初のフィールドを条件として使用して、システムがインデックスを使用するようにする必要があります。そうしないと、インデックスは使用されません。インデックスのメンテナンスに注意し、定期的にインデックスを再構築し、ストアド プロシージャを再コンパイルします。

29. 次の SQL 条件文の列には適切なインデックスがありますが、実行速度が非常に遅いです。

SELECT * FROM record WHERE substring(card_no,1,4)='5378' (13秒)

SELECT * FROM record WHERE amount/30< 1000 (11秒)

SELECT * FROM record WHERE convert(char(10),date,112)='19991201' (10秒)

分析:

WHERE 句の列に対する操作の結果は、SQL の実行時に列ごとに計算されるため、列のインデックスを使用せずにテーブル検索を実行する必要があります。クエリのコンパイル時にこれらの結果を取得できる場合は、SQL オプティマイザーによって最適化され、インデックスを使用してテーブル検索を回避できるため、SQL は次のように書き換えられます。

SELECT * FROM record WHERE card_no like '5378%' (< 1 秒)

SELECT * FROM record WHERE amount< 1000*30 (< 1 秒)

SELECT * FROM record WHERE date= '1999/12/01' (< 1 秒)

30. データのバッチを挿入または更新する場合は、バッチ挿入またはバッチ更新を使用し、各レコードを 1 つずつ更新しないでください。

31. すべてのストアド プロシージャの中で、SQL ステートメントで実装できるものをループを使用して実装することは決してありません。

(たとえば、前月のすべての日を一覧表示するには、connect by を使用して再帰的にクエリを実行し、前月の最初の日から最終日までのループは使用しません)

32. 最も効率的なテーブル名の順序を選択します (ルールベースのオプティマイザーでのみ有効)。

Oracle のパーサーは、FROM 句のテーブル名を右から左に処理します。FROM 句の最後のテーブル (ベース テーブル駆動テーブル) が最初に処理されます。FROM 句に複数のテーブルが含まれている場合は、レコード数が最も少ないテーブルをベース テーブルとして選択する必要があります。結合するテーブルが 3 つ以上ある場合は、交差テーブルをベース テーブルとして選択する必要があります。交差テーブルは、他のテーブルによって参照されるテーブルを参照します。

33. GROUP BY の前に不要なレコードを除外することで、GROUP BY ステートメントの効率を向上させます。次の 2 つのクエリは同じ結果を返しますが、2 番目のクエリの方がはるかに高速です。

非効率性:

ジョブを選択、平均(SAL)

EMPより

ジョブ別にグループ化

仕事を持つ = 「大統領」

または JOB = 'マネージャー'

効率的：

ジョブを選択、平均(SAL)

EMPより

職種 = '大統領'

または JOB = 'マネージャー'

ジョブ別にグループ化

34. Oracle は常に最初に SQL 文を解析し、実行前に小文字を大文字に変換するため、SQL 文は大文字で記述されます。

35. エイリアスの使用。エイリアスは、大規模なデータベース向けのアプリケーション テクニックです。テーブル名と列名は、クエリ内で 1 文字のエイリアスになります。クエリ速度は、結合テーブルを作成する場合よりも 1.5​​ 倍高速です。

36. ストアド プロシージャとトリガーで常に同じテーブルに同じ順序でアクセスしてデッドロックを回避します。トランザクションをできるだけ短くし、トランザクションに含まれるデータの量を最小限に抑えます。トランザクションでユーザー入力を待たないでください。

37. 一時テーブルの使用は避けてください。必要がない限り、一時テーブルの使用は避けてください。代わりに、テーブル変数を使用してください。ほとんどの場合 (99%)、テーブル変数はメモリ内に存在するため、一時テーブルよりも高速です。一時テーブルは TempDb データベースに存在するため、一時テーブルに対する操作にはデータベース間の通信が必要になり、当然ながら低速になります。

38. トリガーは使用しないことをお勧めします。トリガーをトリガーしてトリガー イベントを実行すること自体が、リソースを消費するプロセスです。制約を使用して実現できる場合は、トリガーを使用しないようにしてください。異なるトリガー イベント (挿入、更新、削除) に同じトリガーを使用しないでください。トリガーでトランザクション コードを使用しないでください。

39. インデックス作成ルール:

テーブルの主キーと外部キーにはインデックスが必要です。

300 を超えるデータ項目を持つテーブルにはインデックスが必要です。

他のテーブルに頻繁に接続されるテーブルの場合は、接続フィールドにインデックスを作成する必要があります。

Where 句に頻繁に出現するフィールド、特に大きなテーブル内のフィールドには、インデックスを作成する必要があります。

インデックスは選択性の高いフィールドに構築する必要があります。

インデックスは小さなフィールドに構築する必要があります。大きなテキスト フィールドや非常に長いフィールドにはインデックスを構築しないでください。

複合インデックスの作成には慎重な分析が必要であり、代わりに単一フィールド インデックスを検討する必要があります。

複合インデックス内のプライマリ列フィールド（通常は選択性の高いフィールド）を正しく選択します。

複合インデックスの複数のフィールドが AND 形式で Where 句に頻繁に出現しますか?単一フィールドクエリはほとんどないか、まったくありませんか?はいの場合は、複合インデックスを作成できます。それ以外の場合は、単一フィールド インデックスを検討してください。

複合インデックスに含まれるフィールドが Where 句に単独で頻繁に出現する場合、それらのフィールドは複数の単一フィールド インデックスに分解されます。

複合インデックスに 3 つ以上のフィールドが含まれている場合は、その必要性を慎重に検討し、複合フィールドの数を減らしてください。

これらのフィールドに単一フィールド インデックスと複合インデックスの両方がある場合、通常は複合インデックスを削除できます。

頻繁にデータ操作を実行するテーブルには、インデックスをあまり多く作成しないでください。

実行プランへの悪影響を避けるために、不要なインデックスを削除します。

テーブルに作成された各インデックスによってストレージのオーバーヘッドが増加し、インデックスによって挿入、削除、および更新操作の処理オーバーヘッドも増加します。さらに、単一フィールド インデックスがある場合、複合インデックスが多すぎると通常は意味がありません。逆に、データの追加や削除を行うときにパフォーマンスが低下します。特に、頻繁に更新されるテーブルの場合は、悪影響が大きくなります。

多数の繰り返し値を含むデータベースのフィールドにはインデックスを作成しないようにしてください。

40. MySQL クエリ最適化の概要: スロー クエリ ログを使用してスロー クエリを見つけ、実行プランを使用してクエリが正常に実行されているかどうかを判断し、常にクエリをテストして、クエリが最適な状態で実行されているかどうかを確認します。パフォーマンスは時間の経過とともに常に変化します。テーブル全体に count(*) を使用することは避けてください。テーブル全体がロックされる可能性があります。クエリの一貫性を保ち、後続の同様のクエリがクエリ キャッシュを使用できるようにします。

適切な場合は DISTINCT の代わりに GROUP BY を使用し、WHERE、GROUP BY、および ORDER BY 句でインデックス付きの列を使用し、インデックスをシンプルに保ち、複数のインデックスに同じ列を含めないようにしてください。MySQL は間違ったインデックスを使用する場合があります。この場合は USE INDEX を使用してください。SQL_MODE=STRICT の使用に関する問題がないか確認してください。レコード数が 5 未満のインデックス フィールドの場合は、UNION で OR の代わりに LIMIT を使用してください。

UPDATE の前に SELECT が実行されないようにするには、UPDATE の代わりに INSERT ON DUPLICATE KEY または INSERT IGNORE を使用します。MAX は使用しないでください。インデックス付きフィールドと ORDER BY 句を使用します。LIMIT M, N は、場合によってはクエリの速度を低下させる可能性があります。控えめに使用してください。WHERE 句では、サブクエリの代わりに UNION を使用します。MySQL を再起動する前に、データがメモリ内にあり、クエリが高速であることを確認するために、データベースをウォームアップすることを忘れないでください。オーバーヘッドを削減するには、複数の接続ではなく永続的な接続を検討してください。サーバーの負荷を含め、クエリのベンチマークを実施します。サーバーの負荷が増加すると、単純なクエリが他のクエリに影響を与えることがあります。SHOW PROCESSLIST を使用して、低速で問題のあるクエリを確認します。開発環境で生成されたミラーリングされたデータに対して、疑わしいクエリをすべてテストします。

41.MySQL バックアップ プロセス:

セカンダリ レプリケーション サーバーからバックアップを取得します。データの依存関係と外部キー制約の不整合を回避するために、バックアップの進行中はレプリケーションを停止します。 MySQL を完全に停止し、データベース ファイルからバックアップを作成します。

バックアップに MySQL ダンプを使用する場合は、レプリケーションが中断されないようにバイナリ ログ ファイルもバックアップしてください。 LVM スナップショットは、将来的に問題を引き起こす可能性のあるデータの不整合を引き起こす可能性があるため、信頼しないでください。単一テーブルのリカバリを容易にするには、データが他のテーブルから分離されている場合は、テーブル単位でデータをエクスポートします。

mysqldump を使用する場合は --opt を使用します。バックアップする前にテーブルをチェックして最適化します。インポートを高速化するには、インポート中に外部キー制約を一時的に無効にします。

インポートを高速化するには、インポート中に一意性チェックを一時的に無効にします。データ サイズの増加をより適切に監視するために、各バックアップ後にデータベース、テーブル、およびインデックスのサイズを計算します。

自動スケジュール スクリプトを使用して、レプリケーション インスタンスのエラーと遅延を監視します。定期的にバックアップを実行してください。

42. クエリ バッファはスペースを自動的に処理しません。したがって、SQL ステートメントを記述するときは、特に SQL の先頭と末尾でスペースの使用を最小限に抑えるようにしてください (クエリ バッファは最初と最後のスペースを自動的にインターセプトしないため)。

43. メンバーがテーブルを分割するための基準として mid を使用する場合、クエリを実行すると便利ですか?一般的なビジネス ニーズでは、ユーザー名は基本的にクエリの基準として使用されます。通常、ユーザー名はテーブルを分割するためのハッシュ係数として使用する必要があります。テーブルを分割する場合は、コードに対して透過的な MySQL のパーティション関数が使用されます。

コードレベルで実装するのは無理があるようです。

44. データベース内の各テーブルの主キーとして ID を設定する必要があります。INT 型 (UNSIGNED を推奨) を使用し、AUTO_INCREMENT フラグを設定して自動的に増加するように設定するのが最善です。

45. すべてのストアド プロシージャとトリガーの先頭で SET NOCOUNT ON を設定し、最後に SET NOCOUNT OFF を設定します。

ストアド プロシージャおよびトリガー内の各ステートメントが実行された後に、クライアントに DONE_IN_PROC メッセージを送信する必要はありません。

46. MySQLクエリは高速クエリキャッシュを有効にすることができます。これは、データベースのパフォーマンスを向上させる効果的な MySQL 最適化方法の 1 つです。同じクエリが複数回実行される場合、キャッシュからデータを取得してデータベースから直接返す方がはるかに高速です。

47. EXPLAIN SELECTクエリは効果を追跡して表示するために使用されます

EXPLAIN キーワードを使用すると、MySQL が SQL ステートメントをどのように処理するかを確認できます。これは、クエリ ステートメントまたはテーブル構造のパフォーマンスのボトルネックを分析するのに役立ちます。 EXPLAIN クエリの結果には、インデックスの主キーがどのように使用されているか、データ テーブルがどのように検索およびソートされているかなどについても表示されます。

48. 1行のデータのみ必要な場合はLIMIT 1を使用する

テーブルをクエリするときに、結果が 1 つしかないことが既にわかっている場合でも、カーソルをフェッチする必要がある場合や、返されるレコードの数を確認する必要がある場合があります。この場合、LIMIT 1 を追加するとパフォーマンスが向上します。この方法では、MySQL データベース エンジンは、次の一致するレコードの検索を続行するのではなく、データの一部を見つけた後に検索を停止します。

49. テーブルに適切なストレージ エンジンを選択します。

MyISAM: 主に読み取りおよび挿入操作に使用され、更新および削除は少量のみで、高いトランザクション整合性および同時実行性は必要ありません。

Innodb: 同時実行条件下でのトランザクション処理とデータ一貫性の要件。挿入とクエリに加えて、これには多くの更新と削除が含まれます。 (Innodb は、削除や更新によって発生するロックを効果的に削減します)。トランザクションをサポートする InnoDB テーブルの場合、速度に影響を与える主な理由は、AUTOCOMMIT 設定がデフォルトでオンになっており、プログラムがトランザクションを開始するために BEGIN を明示的に呼び出さないため、挿入された各レコードが自動的にコミットされ、速度に重大な影響を与えることです。 SQL を実行する前に begin を呼び出すことができ、複数の SQL ステートメントを 1 つのトランザクションに結合することができるため (自動コミットがオンになっている場合でも)、パフォーマンスが大幅に向上します。

50. テーブルのデータ型を最適化し、適切なデータ型を選択します。

原則: 通常は小さい方がよい、シンプルな方がよい、すべてのフィールドにはデフォルト値が必要、null は避ける。

たとえば、データベース テーブルを設計するときは、できるだけ小さい整数型を使用して、ディスク領域を節約します。(int よりも mediumint の方が適しています)

たとえば、時間フィールドのdatetimeとtimestampでは、datetimeは8バイトを占めますが、timestampは4バイトでその半分しか占めず、timestampで表される範囲は1970～2037で、更新時間に適しています。

MySQL は大量のデータの保存とアクセスを適切にサポートできますが、一般的に、データベース内のテーブルが小さいほど、そのテーブルで実行されるクエリは高速になります。

したがって、テーブルを作成するときに、より良いパフォーマンスを得るために、テーブル内のフィールドの幅をできるだけ小さく設定することができます。例えば、

郵便番号フィールドを定義するときに、それを CHAR(255) に設定すると、明らかにデータベースに不要なスペースが追加されます。

CHAR(6) でも問題なく機能するため、VARCHAR を使用するのは冗長です。同様に、可能であれば、

整数フィールドを定義するには、BIGIN ではなく MEDIUMINT を使用する必要があります。

将来クエリを実行するときにデータベースが NULL 値を比較する必要がないように、フィールドを NOT NULL に設定するようにしてください。

「都道府県」や「性別」などの一部のテキスト フィールドは、ENUM 型として定義できます。 MySQL では、ENUM 型は数値データとして扱われるためです。

数値データはテキストデータよりもはるかに高速に処理できます。このようにして、データベースのパフォーマンスを再度向上させることができます。

51. 文字列データ型: char、varchar、テキスト選択の違い

52. データベース関数、計算式など、列に対するあらゆる操作はテーブル スキャンになります。クエリを実行するときは、操作を等号の右側に移動するようにしてください。

これで、パフォーマンスの最適化を教える 52 個の SQL ステートメントに関する記事は終了です。SQL 言語のパフォーマンス最適化に関する関連コンテンツについては、123WORDPRESS.COM で以前の記事を検索するか、以下の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。