究極のガイド - より良い SQL クエリを作成するには?

コレクションおよびプログラムメソッドに基づくクエリ

逆モデルでは、クエリ構築に対するセットベースのアプローチとプログラムベースのアプローチには違いがあるという事実が暗黙的に示されています。

• クエリに対する手続き型アプローチは、プログラミングに非常によく似たアプローチです。何を行う必要があるか、そしてそれをどのように実行するかをシステムに指示します。たとえば、前の記事の例のように、1 つの関数を実行してから別の関数を呼び出すことによってデータベースにクエリを実行するか、ループ、条件、およびユーザー定義関数 (UDF) を含む論理的アプローチを使用して最終的なクエリ結果を取得します。このようにして、常に各レイヤーのデータのサブセットをリクエストしていることがわかります。このアプローチは、ステップバイステップまたは行ごとのクエリとも呼ばれることがよくあります。
• もう 1 つはコレクションベースの方法で、実行する必要がある操作を指定するだけで済みます。このメソッドで行う必要があるのは、クエリを通じて取得したい結果の条件と要件を指定することです。データを取得するとき、クエリを実装する内部メカニズムに注意を払う必要はありません。データベース エンジンがクエリを実行するための最適なアルゴリズムとロジックを決定します。

SQL はセットベースであるため、このアプローチは手続き型アプローチよりも効率的です。これが、場合によっては SQL がコードよりも高速に動作する理由の説明になります。

セットベースのクエリ手法も、データ マイニング分析業界で習得が求められるスキルです。これら 2 つの方法を切り替えるには熟練する必要があるからです。クエリに手続き型クエリが含まれていることがわかった場合は、この部分を書き直す必要があるかどうかを検討する必要があります。

クエリから実行計画まで

リバース モードは静的ではありません。 SQL 開発者になるにつれて、クエリのリバース モデルを回避し、クエリを書き直すのは困難な作業になる可能性があります。したがって、多くの場合、より構造化された方法でクエリを最適化するツールを使用する必要があります。

パフォーマンスについて考えるには、より構造化されたアプローチだけでなく、より深いアプローチも必要です。

ただし、この構造化された詳細なアプローチは主にクエリ プランに基づいています。クエリ プランは最初に「解析ツリー」に解析され、各操作にどのようなアルゴリズムが使用されるか、および操作がどのように調整されるかを正確に定義します。

クエリの最適化

クエリを最適化するときは、オプティマイザーによって生成されたプランを手動で検査する必要がある可能性が高くなります。この場合、クエリ プランを確認してクエリを再度分析する必要があります。

このようなクエリ プランをマスターするには、データベース管理システムが提供するいくつかのツールを使用する必要があります。次のツールのいくつかを使用できます:

• 一部のソフトウェア パッケージ機能ツールは、クエリ プランのグラフィカル表現を生成できます。
• 他のツールでは、クエリ プランのテキストによる説明を提供できます。

PostgreSQL を使用している場合は、さまざまな EXPLAIN を区別でき、プランを実行せずにプランナーがクエリを実行する方法の説明を取得するだけであることに注意してください。同時に、EXPLAIN ANALYZE はクエリを実行し、クエリ プランと実際のクエリ プランを評価する分析レポートを返します。一般に、実際の実行プランは実際にプランを実行しますが、評価された実行プランはクエリを実行せずにこの問題を解決できます。論理的には、実際の実行計画には、クエリの実行時に実際に何が起こったかに関する追加の詳細と統計が含まれているため、より有用です。

次に、XPLAIN と ANALYZE についてさらに詳しく学び、これら 2 つのコマンドを使用してクエリ プランとクエリ パフォーマンスをさらに理解する方法を学びます。これを行うには、one_million とhalf_million の 2 つのテーブルを使用していくつかの例を実行する必要があります。

EXPLAIN を使用して、one_million テーブルの現在の情報を取得できます。クエリを実行するときに必ず最初の場所に配置してください。実行が完了すると、情報はクエリ プランに返されます。

リーリー

上記の例では、クエリのコストが 0.00..18584.82、行数が 1025082、列幅が 36 であることがわかります。

同時に、ANALYZE を使用して統計情報を更新することもできます。

リーリー

EXPLAIN と ANALYZE に加えて、EXPLAIN ANALYZE を使用して実際の実行時間を取得することもできます。

リーリー

EXPLAIN ANALYZE を使用する欠点は、実際にクエリを実行する必要があることですが、これは注目に値します。

これまで見てきたすべてのアルゴリズムは、シーケンシャル スキャンまたはフル テーブル スキャンです。これは、テーブルの各行がシーケンシャル (逐次) 順序でスキャンされるデータベース上でスキャンを実行する方法です。読み取り時に、各列が基準を満たしているかどうかがチェックされます。パフォーマンスの観点から見ると、テーブル全体をスキャンする必要があるため、シーケンシャル スキャンは最適な実行プランではありません。ただし、遅いディスクを使用すると、シーケンシャル読み取りも高速になります。

他のアルゴリズムの例もいくつかあります:

リーリー

クエリ オプティマイザーがハッシュ結合を選択したことがわかります。この操作はクエリの時間計算量を評価するために使用する必要があるため、覚えておいてください。上記の例では、half_million.counter インデックスが存在しないことに気付きました。次の例では、インデックスを追加できます:

リーリー

インデックスを作成することにより、クエリ オプティマイザーはインデックスのスキャン時にマージ結合を見つける方法を決定します。

インデックス スキャンとフル テーブル スキャン (順次スキャン) の違いに注意してください。後者 (「テーブル スキャン」とも呼ばれます) は、すべてのデータをスキャンするか、すべてのページにインデックスを付けることによって適切な結果を見つけます。一方、前者は、テーブル内のすべての行のみをスキャンします。テーブル。

ここではチュートリアルの 2 番目の部分を紹介します。シリーズの最終記事「より良い SQL クエリの作成方法」が続きますので、お楽しみに。

転載元を明記してください: Grape City Control

以上が究極のガイド - より良い SQL クエリを作成するには?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。