ウィンドウ関数の高速化? PawSQL のインデックスマジックが明らかに-mysql チュートリアル-php.cn

ウィンドウ関数の高速化? PawSQL のインデックスマジックが明らかに

DDD

リリース： 2024-09-12 20:16:28

オリジナル

780 人が閲覧しました

導入

データベースのパフォーマンス最適化の領域では、ウィンドウ関数を含む SQL クエリには特有の課題が存在します。この記事では、高度な SQL 最適化ツールである PawSQL が、インテリジェントなインデックス推奨を通じてそのようなクエリのパフォーマンスを大幅に向上させる方法について説明します。このアプローチのプロセスと利点を説明するために、特定のケーススタディを検討します。

ケーススタディ: 複雑なクエリの分析

特定の日付における各顧客の最低注文金額を見つけることを目的とした次の SQL クエリについて考えてみましょう。

SELECT *
FROM (
    SELECT o.o_custkey, o.o_totalprice,
           RANK() OVER (PARTITION BY o.o_custkey ORDER BY o.o_totalprice) AS rn
    FROM orders AS o
    WHERE o.o_orderdate = '1996-06-20'
) AS A
WHERE A.rn = 1

ログイン後にコピー

このクエリは簡単そうに見えますが、特に大規模なデータセットの場合、パフォーマンスの問題を引き起こす可能性があります。 PawSQL がこれらの課題にどのように対処するかを見てみましょう。

PawSQL の最適化戦略

クエリを分析した後、PawSQL は次の最適化を提案しました。

新しいインデックスの作成

   CREATE INDEX PAWSQL_IDX1878194728 ON public.orders(o_orderdate, o_custkey, o_totalprice);

ログイン後にコピー

そして PawSQL の出力は:

となります。

Faster Window Functions? PawSQL

パフォーマンス検証は:

です。

Faster Window Functions? PawSQL

50 倍高速になる理由を理解する

PawSQL の推奨事項により、クエリパフォーマンスが 5181.55% という大幅な向上につながりました。この大幅な機能強化は、いくつかの要因によるものです。

1. 正確なインデックスマッチング

新しく作成されたインデックス PAWSQL_IDX1878194728 は、クエリの要件に合わせて調整されています。

o_orderdate を先頭列として使用すると、効率的なフィルタリングが容易になります。
o_custkey と o_totalprice を含めることで、ウィンドウ関数の分割と順序付けの操作がサポートされます。

2. ソート操作の廃止

インデックス構造は本質的に必要な並べ替え順序を提供するため、クエリの実行中に追加の並べ替え操作を行う必要がなくなります。

3. カバリングインデックス手法の活用

必要な列をすべて含めることで、新しいインデックスはカバーインデックスとして機能します。これにより、データベースは必要なデータをすべてインデックスから直接取得できるようになり、I/O 操作が大幅に削減されます。

4. 実行計画の最適化

実行計画を比較すると、最適化の影響がわかります。

最適化前:

ビットマップインデックススキャンとヒープスキャンを利用しました
追加の並べ替え操作が必要です
実行時間: 0.485 ミリ秒

最適化後:

採用されたインデックスのみのスキャン
追加の並べ替えの必要性がなくなりました
実行時間が 0.088 ミリ秒に短縮されました

ベストプラクティスと考慮事項

このような最適化の利点を最大化するには、次のベストプラクティスを考慮してください。

定期的なパフォーマンス分析: 特にウィンドウ関数を含む複雑なクエリに対して、日常的なクエリ分析を実装します。
インデックス作成に対するバランスの取れたアプローチ: 新しいインデックスは読み取りパフォーマンスを大幅に向上させることができますが、書き込み操作とストレージ要件への影響を考慮してください。
インデックスのメンテナンス: 最適なデータベースのパフォーマンスを維持するために、冗長なインデックスを定期的に確認して削除します。
全体的な最適化戦略: 最適化を実装するときは、アプリケーションの全体的なクエリパターンを考慮します。