データベース システムは、経営情報システムの中核であり、データベース ベースのオンライン トランザクション処理 (OLTP) とオンライン分析処理 (OLAP) は、銀行、企業、政府、その他の部門で最も重要なコンピュータ アプリケーションの 1 つです。多くのシステムの適用例から判断すると、さまざまなデータベース操作のうちクエリ操作が最も大きな割合を占めており、クエリ操作の基となる SELECT 文は SQL 文の中で最もコストがかかる文です。たとえば、銀行の口座データベースのテーブル情報が数百万、さらには数千万のレコードに蓄積されるなど、データ量がある程度まで蓄積される場合、テーブル全体のスキャンには数十分、場合によっては数時間かかることがよくあります。フル テーブル スキャンよりも優れたクエリ戦略を採用すると、多くの場合、クエリ時間を数分に短縮できます。これは、アプリケーション プロジェクトの実装中に、多くのプログラマがクエリ最適化テクノロジを使用していることに気づきました。一部のフロントエンド データベース開発ツール (PowerBuilder、Delphi など) は、データベース アプリケーションを開発するときに、ユーザー インターフェイスの美しさのみに重点を置き、クエリ ステートメントの効率を考慮しないため、結果として効率が低くなります。開発されたアプリケーション システムと深刻なリソースの無駄遣い。したがって、効率的かつ合理的なクエリ ステートメントをどのように設計するかが非常に重要です。この記事では、アプリケーション例に基づいて、データベース理論と組み合わせて、実際のシステムにおけるクエリ最適化テクノロジのアプリケーションを紹介します。
問題の分析
多くのプログラマーは、クエリの最適化は DBMS (データベース管理システム) のタスクであり、プログラマーが作成した SQL ステートメントとはほとんど関係がないと信じています。これは間違いです。適切なクエリ プランを使用すると、プログラムのパフォーマンスが何十倍も向上することがよくあります。クエリ プランはユーザーによって送信された SQL ステートメントのコレクションであり、クエリ プランは最適化後に生成されたステートメントのコレクションです。 DBMS 処理クエリ プランのプロセスは次のとおりです。クエリ ステートメントの字句チェックと構文チェックが完了した後、ステートメントは DBMS クエリ オプティマイザに送信され、オプティマイザが代数最適化とアクセス パスの最適化を完了すると、プリコンパイルされたモジュールがクエリ プランを処理します。ステートメントを作成してクエリ プランを生成し、それをシステムに送信して適切なタイミングで処理および実行し、最後に実行結果をユーザーに返します。実際のデータベース製品 (Oracle、Sybase など) の上位バージョンでは、コストベースの最適化手法が使用され、システム ディクショナリ テーブルから取得した情報に基づいてさまざまなクエリ プランのコストを見積もり、選択することができます。より良い計画を立てる。現在のデータベース製品はクエリ最適化においてますます優れていますが、ユーザーが送信した SQL ステートメントがシステム最適化の基礎となるため、元々貧弱なクエリ プランがシステム最適化後に効率的になるとは考えにくいです。ユーザーが書いたステートメントは非常に重要です。ここでは、システムによって実行されるクエリの最適化については説明しません。以下では、ユーザーのクエリ プランを改善するソリューションに焦点を当てます。
問題を解決する
以下では、リレーショナル データベース システム Informix を例として、ユーザーのクエリ プランを改善する方法を紹介します。
1.インデックスの合理的な使用
インデックスはデータベース内の重要なデータ構造であり、その基本的な目的はクエリの効率を向上させることです。現在、ほとんどのデータベース製品は、IBM が最初に提案した ISAM インデックス構造を使用しています。インデックスの使用は適切である必要があり、使用原則は次のとおりです。
●頻繁に接続されるが外部キーとして指定されていない列にはインデックスを作成し、頻繁に接続されないフィールドにはオプティマイザーが自動的にインデックスを生成します。
●頻繁に並べ替えまたはグループ化される列にインデックスを作成します (つまり、group by または order by 操作)。
●条件式でよく使用される、さまざまな値が含まれる列に検索を作成します。異なる値がほとんどない列にはインデックスを作成しないでください。たとえば、従業員テーブルの「性別」列には「男性」と「女性」の 2 つの異なる値しかないため、インデックスを作成する必要はありません。インデックスを作成すると、クエリ効率が向上しないだけでなく、更新速度が大幅に低下します。
●並べ替える列が複数ある場合、これらの列に複合インデックスを作成できます。
●システムツールを利用する。たとえば、Informix データベースには、疑わしいインデックスをチェックできる tbcheck ツールがあります。一部のデータベース サーバーでは、インデックスが無効であるか、頻繁な操作によって読み取り効率が低下する可能性があります。明らかな理由もなくインデックスを使用したクエリが遅くなる場合は、tbcheck ツールを使用してインデックスの整合性をチェックしてみることができます。そして必要に応じて修理します。さらに、データベース テーブルが大量のデータを更新する場合、インデックスを削除して再構築すると、クエリ速度が向上します。
2.並べ替えを回避または簡素化する
大きなテーブルでの重複した並べ替えは簡略化するか回避する必要があります。オプティマイザは、インデックスを使用して適切な順序で出力を自動的に生成できる場合、ソート手順を回避します。影響する要因は次のとおりです。
● インデックスに並べ替えられる 1 つまたは複数の列が含まれていない
● group by 句または order by 句内の列の順序がインデックスの順序と異なる。ソートされた列は異なるテーブルからのものです。
不必要な並べ替えを避けるためには、インデックスを正しく追加し、データベース テーブルを合理的にマージする必要があります (テーブルの正規化に影響を与える場合もありますが、効率の向上には価値があります)。やむを得ず並べ替えを行う場合は、並べ替える列の範囲を狭めるなど、並べ替えを簡素化する必要があります。
3.大きなテーブル行データへのシーケンシャル アクセスを排除します
ネストされたクエリでは、テーブルへのシーケンシャル アクセスがクエリの効率に致命的な影響を与える可能性があります。たとえば、シーケンシャル アクセス戦略である 3 レベルのネストされたクエリを使用すると、各レベルが 1,000 行をクエリする場合、このクエリは 10 億行のデータをクエリすることになります。この状況を回避する主な方法は、結合された列にインデックスを付けることです。たとえば、学生テーブル (学生番号、名前、年齢...) とコース選択テーブル (学生番号、コース番号、成績) の 2 つのテーブルが考えられます。 2 つのテーブルを接続する場合は、接続フィールド「学籍番号」にインデックスを作成する必要があります。
union を使用してシーケンシャルアクセスを回避することもできます。すべてのチェック列にインデックスがありますが、一部の形式の where 句ではオプティマイザに順次アクセスの使用を強制します。次のクエリは、orders テーブルに逐次操作を強制します:
SELECT ＊ FROMorders WHERE (customer_num=104 AND order_num>1001) OR order_num=1008
customer_num と order_num に基づいて構築されたインデックスがありますが、上記のステートメントではオプティマイザーが引き続き、順次アクセス パスを使用してテーブル全体をスキャンします。このステートメントは別の行のセットを取得するため、次のステートメントに変更する必要があります:
SELECT ＊ FROMorders WHERE customer_num=104 AND order_num>1001
UNION
SELECT ＊ FROMorders WHERE order_num=1008
このようにインデックス パスを使用してクエリを処理できます。
4.相関サブクエリを避ける
列ラベルがメイン クエリと where 句のクエリの両方に表示される場合、メイン クエリの列値が変更されたときにサブクエリを再クエリする必要がある可能性があります。クエリのネストされたレベルが増えるほど効率が低下するため、サブクエリは可能な限り避ける必要があります。サブクエリが避けられない場合は、サブクエリ内のできるだけ多くの行をフィルタリングして除外します。
5.難しい正規表現は避けてください
MATCHES キーワードと LIKE キーワードは、技術的には正規表現と呼ばれるワイルドカード マッチングをサポートしています。しかし、この種のマッチングには特に時間がかかります。例: SELECT * FROM customer WHERE zipcode LIKE “98_ _ _”
郵便番号フィールドにインデックスが作成された場合でも、この場合は引き続き順次スキャンが使用されます。ステートメントを SELECT * FROM customer WHERE zipcode > "98000" に変更すると、クエリの実行時にインデックスがクエリに使用され、明らかに速度が大幅に向上します。
また、開始しない部分文字列も避けてください。たとえば、ステートメント SELECT * FROM customer WHERE zipcode[2, 3]> "80" は where 句で開始しない部分文字列を使用するため、このステートメントはインデックスを使用しません。
6．一時テーブルを使用してクエリを高速化します
テーブルのサブセットを並べ替えて一時テーブルを作成すると、クエリが高速化される場合があります。これにより、複数の並べ替え操作が回避され、オプティマイザーの作業が簡素化されます。たとえば、cust.name、rcvbles.balance、...他の列cust、cust、rcvbles cust.customer_id = rcvlbes.customer_id ;“98000 ”
ORDER BY cust.name
このクエリを 1 回だけではなく複数回実行する場合は、一時ファイルですべての未払い顧客を検索し、顧客名で並べ替えることができます:
SELECT cust .name ,rcvbles.balance,...他の列
FROM cust,rcvbles
WHERE cust.customer_id = rcvlbes.customer_id
AND rcvblls.balance>0
ORDER BY cust.name
INTO TEMP with_balance
then クエリ次の方法で一時テーブルに追加します:
SELECT ＊ FROM cust_with_balance
WHERE postcode>“98000”
一時テーブルの行はメインテーブルの行よりも少なく、物理的な順序は必要な順序を削減したものですディスク I/O が不要になるため、クエリのワークロードが大幅に軽減されます。
注: 一時テーブルは、作成後にメインテーブルの変更を反映しません。メインテーブルのデータを頻繁に変更する場合は、データが失われないように注意してください。
7．ソートを使用して非順次アクセスを置き換えます
非順次ディスク アクセスは最も遅い操作であり、ディスク アクセス アームの前後の動きに現れます。 SQL ステートメントはこの状況を隠し、アプリケーションを作成するときに多数の非順次ページへのアクセスを必要とするクエリを簡単に作成できるようにします。
場合によっては、非順次アクセスの代わりにデータベースの並べ替え機能を使用すると、クエリが改善されることがあります。
![[Web フロントエンド] Node.js クイック スタート](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
