データベース システムは、経営情報システムの中核であり、データベース ベースのオンライン トランザクション処理 (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 つのテーブルを接続する場合は、接続フィールド「学籍番号」にインデックスを作成する必要があります。
共用体を使用して、順次アクセスを回避することもできます。すべてのチェック列にインデックスがありますが、一部の形式の where 句ではオプティマイザに順次アクセスの使用を強制します。次のクエリは、orders テーブルに対して順次操作を強制します: SELECT * FROMorders WHERE (customer_num=104 AND order_num>1001) OR order_num=1008
customer_num と order_num にはインデックスがありますが、オプティマイザは引き続きシーケンシャル アクセス パスを使用して、上記のステートメントのテーブル全体をスキャンします。このステートメントは行の個別のコレクションを取得するため、次のステートメントに変更する必要があります。
SELECT * FROM 注文 WHERE customer_num=104 AND order_num>1001
連合
SELECT * FROM 注文 WHERE order_num=1008
これにより、インデックス パスを使用してクエリを処理できるようになります。
4. 相関サブクエリを避ける
列ラベルがメイン クエリと where 句のクエリの両方に出現する場合、メイン クエリの列値が変更されたときにサブクエリを再クエリする必要がある可能性があります。クエリのネストされたレベルが増えるほど効率が低下するため、サブクエリは可能な限り避ける必要があります。サブクエリが避けられない場合は、サブクエリ内のできるだけ多くの行をフィルタリングして除外します。
5. 難しい正規表現を避ける
MATCHES キーワードと LIKE キーワードは、技術的には正規表現と呼ばれるワイルドカード一致をサポートします。ただし、この種のマッチングには特に時間がかかります。例: SELECT * FROM customer WHERE 郵便番号 LIKE “98_ _ _”
郵便番号フィールドにインデックスが作成された場合でも、この場合は引き続き順次スキャンが使用されます。ステートメントを SELECT * FROM customer WHERE zipcode > "98000" に変更すると、クエリの実行時にインデックスがクエリに使用され、明らかに速度が大幅に向上します。
また、開始しない部分文字列も避けてください。たとえば、ステートメント SELECT * FROM customer WHERE zipcode[2, 3]>"80" は、where 句で開始しない部分文字列を使用するため、このステートメントはインデックスを使用しません。
6. 一時テーブルを使用してクエリを高速化する
テーブルのサブセットを並べ替えて一時テーブルを作成すると、クエリが高速化される場合があります。これにより、複数の並べ替え操作が回避され、オプティマイザーの作業が簡素化されます。例: SELECT cust.name、rcVBles.balance、...その他の列
SELECT cust.name、rcVBles.balance、...その他の列
お客様から、rcvbles
WHERE cust.customer_id = rcvlbes.customer_id
かつ rcvblls.balance>0
かつ、顧客の郵便番号>「98000」
顧客名で注文
このクエリを 1 回だけではなく複数回実行する場合は、一時ファイルですべての未払い顧客を検索し、顧客名で並べ替えることができます: SELECT cust.name、rcvbles.balance、...その他の列
SELECT cust.name、rcvbles.balance、...その他の列
お客様から、rcvbles
WHERE cust.customer_id = rcvlbes.customer_id
かつ rcvblls.balance>0
顧客名で注文
INTO TEMP cust_with_balance
次に、次の方法で一時テーブルにクエリを実行します: SELECT * FROM cust_with_balance
WHERE 郵便番号>「98000」
一時テーブルの行数はメイン テーブルよりも少なく、物理的な順序が必要な順序であるため、ディスク I/O が削減され、クエリのワークロードが大幅に軽減されます。
注: 一時テーブルの作成後は、メイン テーブルの変更は反映されません。メインテーブルのデータを頻繁に変更する場合は、データが失われないように注意してください。
7. ソートを使用して非順次アクセスを置き換える
非順次ディスク アクセスは最も遅い操作であり、ディスク アクセス アームの前後の動きによって表されます。 SQL ステートメントはこの状況を隠し、アプリケーションを作成するときに多数の非順次ページへのアクセスを必要とするクエリを簡単に作成できるようにします。場合によっては、非順次アクセスの代わりにデータベースの並べ替え機能を使用すると、クエリが改善されることがあります。
-