効率的で合理的なMySQLクエリ文の設計
5875 ワード
ほとんどのシステムの応用例から見ると、クエリー操作が様々なデータベース#データベース#操作で占める割合が最も大きく、クエリー操作に基づいたSELECT文はSQL文の中で最も代価の大きい文である.例えば、銀行の口座データベースのテーブル情報が百万件以上、千万件以上の記録に蓄積されるなど、データの量がある程度蓄積されると、テーブル全体のスキャンには数十分、数時間かかることが多い.全テーブルスキャンよりも優れたクエリー・ポリシーを使用すると、クエリー時間を数分に短縮することがよくあります.これにより、クエリー最適化技術の重要性がわかります.
筆者はアプリケーションプロジェクトの実施において、多くのプログラマーがPowerBuilder、Delphiなどのフロントエンドデータベース開発ツールを利用してデータベースアプリケーションを開発する際、ユーザーインタフェースの華やかさだけを重視し、クエリー文の効率問題を重視せず、発行されたアプリケーションシステムの効率が低下し、資源の浪費が深刻であることを発見した.したがって、効率的で合理的なクエリー文をどのように設計するかが重要です.本文は応用例を基礎として、データベース理論と結びつけて、クエリー最適化技術の現実システムにおける運用を紹介する.
問題を分析する
多くのプログラマは、クエリーの最適化がDBMS(データベース管理システム)のタスクであり、プログラマが作成したSQL文とはあまり関係ないと考えています.これはエラーです.良いクエリー計画は、プログラムのパフォーマンスを数十倍に向上させることが多い.クエリー・プランは、ユーザーがコミットしたSQL文の集合です.クエリー・プランは、最適化処理後に生成された文の集合です.DBMSがクエリー計画を処理する過程は、クエリー文の文法、文法検査を終えた後、DBMSのクエリー・オプティマイザに文を提出し、オプティマイザが代数最適化とアクセス・パスの最適化を終えた後、モジュールによって文を前コンパイルして処理し、クエリー計画を生成し、適切な時間にシステム処理実行に提出する.最後に、実行結果をユーザーに返します.実際のデータベース製品(Oracle、Sybaseなど)の高バージョンでは、システム辞書テーブルから得られた情報に基づいて異なるクエリー・プランのコストを推定し、より優れたプランを選択するコスト・ベースの最適化方法が採用されています.現在のデータベース製品はクエリーの最適化においてますます優れているが、ユーザーが提出したSQL文はシステムの最適化の基礎であり、もともと悪いクエリー計画がシステムの最適化を経て効率的になるとは考えにくいため、ユーザーが書いた文の優劣が重要である.システムが行ったクエリーの最適化については、しばらく議論しません.次に、ユーザークエリー計画の改善に関する解決イニシアチブについて重点的に説明します.
問題を解決する
次に、リレーショナル・データベース・システムInformixを例に、ユーザー・クエリー・プランを改善する方法について説明します.
1.インデックスの合理的な使用
インデックスはデータベース内の重要なデータ構造であり、クエリーの効率化を根本的に目的としています.現在、ほとんどのデータベース製品はIBMで最初に提案されたISAMインデックス構造を採用しています.インデックスの使用は、次のように原則的に適切に行われます.
・頻繁に接続されますが、外部キーとして指定されていないカラムにインデックスが作成されます.頻繁に接続されていないフィールドは、オプティマイザによって自動的にインデックスが生成されます.
・インデックスは、頻繁にソートまたはグループ化される(すなわちgroup byまたはorder by操作される)列に作成される.
・条件式でよく使用される値の多いカラムで検索を行い、値の少ないカラムではインデックスを作成しない.たとえば、従業員テーブルの「性別」列には「男性」と「女性」の2つの異なる値しかないため、インデックスを作成する必要はありません.インデックスを作成すると、クエリーの効率が向上するだけでなく、更新速度が大幅に低下します.
・並べ替えられる列が複数ある場合、これらの列に複合インデックス(compound index)を作成することができる.
●システムツールを使用する.Informixデータベースにtbcheckツールがある場合は、不審なインデックスでチェックできます.一部のデータベースサーバでは、インデックスが無効になったり、頻繁に操作されたりして読み取り効率が低下する可能性があります.インデックスを使用するクエリーが不明に遅くなった場合は、tbcheckツールを使用してインデックスの整合性を確認し、必要に応じて修復してみてください.また、データベース・テーブルが大量のデータを更新した後、インデックスを削除して再構築すると、クエリーの速度が向上します.
2.ソートの回避または簡略化
大規模なテーブルの並べ替えを簡略化または回避する必要があります.インデックスを使用して出力を自動的に適切な順序で生成できる場合、オプティマイザはソートのステップを回避します.以下に影響要因を示します.
・インデックスには、1つまたは複数のソート対象列が含まれていない.
●group byまたはorder by句の列の順序がインデックスの順序と異なる.
・ソートされた列は、異なるテーブルから作成されます.
不要なソートを回避するには、インデックスを正しく増設し、データベース・テーブルを合理的に統合する必要があります(テーブルの正規化に影響を与える場合がありますが、効率の向上には価値があります).ソートが避けられない場合は、ソートされたカラムの範囲を縮小するなど、ソートを簡略化しようとします.
3.大規模なテーブル・ロー・データへのシーケンシャル・アクセスの排除
ネストされたクエリーでは、テーブルへの順序アクセスがクエリー効率に致命的な影響を及ぼす可能性があります.たとえば、シーケンス・アクセス・ポリシーを使用して、3つの階層をネストしたクエリーで、各層が1000行クエリーすると、このクエリーは10億行のデータをクエリーします.このような状況を回避する主な方法は、接続されたカラムをインデックスすることです.たとえば、2つの表:学生表(学番、名前、年齢......)と選択科目表(学番、課程番号、成績).2つのテーブルを接続する場合は、「学号」という接続フィールドにインデックスを作成します.
パラレル・セットを使用して、シーケンス・アクセスを回避することもできます.すべてのチェックカラムにインデックスがありますが、いくつかの形式のwhere句は、オプティマイザにシーケンスアクセスを強制します.次のクエリはordersテーブルの順序操作を強制します.
customerではnumとorder_numにはインデックスが作成されていますが、上記の文ではオプティマイザはシーケンスアクセスパスを使用してテーブル全体をスキャンします.この文は分離された行の集合を取得するため、次の文に変更する必要があります.
これにより、インデックスパスを使用してクエリーを処理できます.
4.関連サブクエリの回避
1つのカラムのラベルがプライマリ・クエリとwhere句のクエリで同時に表示される場合、プライマリ・クエリのカラム値が変更された後、サブクエリは再クエリする必要があります.クエリのネストされた階層が多いほど、効率が低下するため、サブクエリはできるだけ回避する必要があります.サブクエリが避けられない場合は、サブクエリでできるだけ多くのローをフィルタします.
5.困難を避ける正規表現
MATCHESとLIKEキーワードはワイルドカードマッチングをサポートし、技術的には正規表現と呼ばれている.しかし、このマッチングには特に時間がかかります.例えば、SELECT*FROM customer WHERE zipcode LIKE「98_」
zipcodeフィールドにインデックスが確立されていても、この場合はシーケンススキャン方式が採用されます.文をSELECT*FROM customer WHERE zipcode>「98000」に変更すると、クエリの実行時にインデックスを利用してクエリが行われ、大幅に高速化されることが明らかになります.
また,非開始のサブストリングも避ける.例えば文:SELECT*FROM customer WHERE zipcode[2,3]>"80"は、where句に非開始サブ列が採用されているため、この文もインデックスを使用しない.
6.テンポラリ・テーブルを使用したクエリーの高速化
テーブルのサブセットをソートしてテンポラリ・テーブルを作成すると、クエリーが高速化される場合があります.複数のソート操作を回避し、オプティマイザの作業を簡素化できます.例:
このクエリが複数回にわたって実行される場合は、すべての未決済の顧客を1つのテンポラリ・ファイルに検索して、顧客の名前でソートできます.
次に、テンポラリ・テーブルで次のようにクエリーします.
テンポラリ・テーブルのローは、プライマリ・テーブルのローよりも少なく、物理的な順序が要求される順序であり、減少します.
ディスク I/Oのため、クエリー作業量を大幅に削減できます.
注意:テンポラリ・テーブルの作成後は、プライマリ・テーブルの変更は反映されません.プライマリ・テーブルでデータが頻繁に変更されている場合は、データが失われないように注意してください.
7.非順序アクセスの代わりにソート
非順序ディスクアクセスは、ディスクアクセスアームの往復移動に現れる最も遅い操作です.SQL文はこの状況を隠しており、アプリケーションを書くときに大量の非順序ページにアクセスするクエリーを簡単に書くことができます.非順序アクセスの代わりにデータベース#データベース#のソート機能を使用すると、クエリーが改善される場合があります.
筆者はアプリケーションプロジェクトの実施において、多くのプログラマーがPowerBuilder、Delphiなどのフロントエンドデータベース開発ツールを利用してデータベースアプリケーションを開発する際、ユーザーインタフェースの華やかさだけを重視し、クエリー文の効率問題を重視せず、発行されたアプリケーションシステムの効率が低下し、資源の浪費が深刻であることを発見した.したがって、効率的で合理的なクエリー文をどのように設計するかが重要です.本文は応用例を基礎として、データベース理論と結びつけて、クエリー最適化技術の現実システムにおける運用を紹介する.
問題を分析する
多くのプログラマは、クエリーの最適化がDBMS(データベース管理システム)のタスクであり、プログラマが作成したSQL文とはあまり関係ないと考えています.これはエラーです.良いクエリー計画は、プログラムのパフォーマンスを数十倍に向上させることが多い.クエリー・プランは、ユーザーがコミットしたSQL文の集合です.クエリー・プランは、最適化処理後に生成された文の集合です.DBMSがクエリー計画を処理する過程は、クエリー文の文法、文法検査を終えた後、DBMSのクエリー・オプティマイザに文を提出し、オプティマイザが代数最適化とアクセス・パスの最適化を終えた後、モジュールによって文を前コンパイルして処理し、クエリー計画を生成し、適切な時間にシステム処理実行に提出する.最後に、実行結果をユーザーに返します.実際のデータベース製品(Oracle、Sybaseなど)の高バージョンでは、システム辞書テーブルから得られた情報に基づいて異なるクエリー・プランのコストを推定し、より優れたプランを選択するコスト・ベースの最適化方法が採用されています.現在のデータベース製品はクエリーの最適化においてますます優れているが、ユーザーが提出したSQL文はシステムの最適化の基礎であり、もともと悪いクエリー計画がシステムの最適化を経て効率的になるとは考えにくいため、ユーザーが書いた文の優劣が重要である.システムが行ったクエリーの最適化については、しばらく議論しません.次に、ユーザークエリー計画の改善に関する解決イニシアチブについて重点的に説明します.
問題を解決する
次に、リレーショナル・データベース・システムInformixを例に、ユーザー・クエリー・プランを改善する方法について説明します.
1.インデックスの合理的な使用
インデックスはデータベース内の重要なデータ構造であり、クエリーの効率化を根本的に目的としています.現在、ほとんどのデータベース製品はIBMで最初に提案されたISAMインデックス構造を採用しています.インデックスの使用は、次のように原則的に適切に行われます.
・頻繁に接続されますが、外部キーとして指定されていないカラムにインデックスが作成されます.頻繁に接続されていないフィールドは、オプティマイザによって自動的にインデックスが生成されます.
・インデックスは、頻繁にソートまたはグループ化される(すなわちgroup byまたはorder by操作される)列に作成される.
・条件式でよく使用される値の多いカラムで検索を行い、値の少ないカラムではインデックスを作成しない.たとえば、従業員テーブルの「性別」列には「男性」と「女性」の2つの異なる値しかないため、インデックスを作成する必要はありません.インデックスを作成すると、クエリーの効率が向上するだけでなく、更新速度が大幅に低下します.
・並べ替えられる列が複数ある場合、これらの列に複合インデックス(compound index)を作成することができる.
●システムツールを使用する.Informixデータベースにtbcheckツールがある場合は、不審なインデックスでチェックできます.一部のデータベースサーバでは、インデックスが無効になったり、頻繁に操作されたりして読み取り効率が低下する可能性があります.インデックスを使用するクエリーが不明に遅くなった場合は、tbcheckツールを使用してインデックスの整合性を確認し、必要に応じて修復してみてください.また、データベース・テーブルが大量のデータを更新した後、インデックスを削除して再構築すると、クエリーの速度が向上します.
2.ソートの回避または簡略化
大規模なテーブルの並べ替えを簡略化または回避する必要があります.インデックスを使用して出力を自動的に適切な順序で生成できる場合、オプティマイザはソートのステップを回避します.以下に影響要因を示します.
・インデックスには、1つまたは複数のソート対象列が含まれていない.
●group byまたはorder by句の列の順序がインデックスの順序と異なる.
・ソートされた列は、異なるテーブルから作成されます.
不要なソートを回避するには、インデックスを正しく増設し、データベース・テーブルを合理的に統合する必要があります(テーブルの正規化に影響を与える場合がありますが、効率の向上には価値があります).ソートが避けられない場合は、ソートされたカラムの範囲を縮小するなど、ソートを簡略化しようとします.
3.大規模なテーブル・ロー・データへのシーケンシャル・アクセスの排除
ネストされたクエリーでは、テーブルへの順序アクセスがクエリー効率に致命的な影響を及ぼす可能性があります.たとえば、シーケンス・アクセス・ポリシーを使用して、3つの階層をネストしたクエリーで、各層が1000行クエリーすると、このクエリーは10億行のデータをクエリーします.このような状況を回避する主な方法は、接続されたカラムをインデックスすることです.たとえば、2つの表:学生表(学番、名前、年齢......)と選択科目表(学番、課程番号、成績).2つのテーブルを接続する場合は、「学号」という接続フィールドにインデックスを作成します.
パラレル・セットを使用して、シーケンス・アクセスを回避することもできます.すべてのチェックカラムにインデックスがありますが、いくつかの形式のwhere句は、オプティマイザにシーケンスアクセスを強制します.次のクエリはordersテーブルの順序操作を強制します.
SELECT * FROM orders WHERE (customer_num=104 AND order_num>1001) OR order_num=1008customerではnumとorder_numにはインデックスが作成されていますが、上記の文ではオプティマイザはシーケンスアクセスパスを使用してテーブル全体をスキャンします.この文は分離された行の集合を取得するため、次の文に変更する必要があります.
SELECT * FROM orders WHERE customer_num=104 AND order_num>1001
UNION
SELECT * FROM orders WHERE order_num=1008これにより、インデックスパスを使用してクエリーを処理できます.
4.関連サブクエリの回避
1つのカラムのラベルがプライマリ・クエリとwhere句のクエリで同時に表示される場合、プライマリ・クエリのカラム値が変更された後、サブクエリは再クエリする必要があります.クエリのネストされた階層が多いほど、効率が低下するため、サブクエリはできるだけ回避する必要があります.サブクエリが避けられない場合は、サブクエリでできるだけ多くのローをフィルタします.
5.困難を避ける正規表現
MATCHESとLIKEキーワードはワイルドカードマッチングをサポートし、技術的には正規表現と呼ばれている.しかし、このマッチングには特に時間がかかります.例えば、SELECT*FROM customer WHERE zipcode LIKE「98_」
zipcodeフィールドにインデックスが確立されていても、この場合はシーケンススキャン方式が採用されます.文をSELECT*FROM customer WHERE zipcode>「98000」に変更すると、クエリの実行時にインデックスを利用してクエリが行われ、大幅に高速化されることが明らかになります.
また,非開始のサブストリングも避ける.例えば文:SELECT*FROM customer WHERE zipcode[2,3]>"80"は、where句に非開始サブ列が採用されているため、この文もインデックスを使用しない.
6.テンポラリ・テーブルを使用したクエリーの高速化
テーブルのサブセットをソートしてテンポラリ・テーブルを作成すると、クエリーが高速化される場合があります.複数のソート操作を回避し、オプティマイザの作業を簡素化できます.例:
SELECT cust.name，rcVBles.balance，……other columns
FROM cust，rcvbles
WHERE cust.customer_id = rcvlbes.customer_id
AND rcvblls.balance>0
AND cust.postcode>“98000”
ORDER BY cust.nameこのクエリが複数回にわたって実行される場合は、すべての未決済の顧客を1つのテンポラリ・ファイルに検索して、顧客の名前でソートできます.
SELECT cust.name，rcvbles.balance，……other columns
FROM cust，rcvbles
WHERE cust.customer_id = rcvlbes.customer_id
AND rcvblls.balance>0
ORDER BY cust.name
INTO TEMP cust_with_balance次に、テンポラリ・テーブルで次のようにクエリーします.
SELECT * FROM cust_with_balance
WHERE postcode>“98000”テンポラリ・テーブルのローは、プライマリ・テーブルのローよりも少なく、物理的な順序が要求される順序であり、減少します.
ディスク I/Oのため、クエリー作業量を大幅に削減できます.
注意:テンポラリ・テーブルの作成後は、プライマリ・テーブルの変更は反映されません.プライマリ・テーブルでデータが頻繁に変更されている場合は、データが失われないように注意してください.
7.非順序アクセスの代わりにソート
非順序ディスクアクセスは、ディスクアクセスアームの往復移動に現れる最も遅い操作です.SQL文はこの状況を隠しており、アプリケーションを書くときに大量の非順序ページにアクセスするクエリーを簡単に書くことができます.非順序アクセスの代わりにデータベース#データベース#のソート機能を使用すると、クエリーが改善される場合があります.