検索結果の出力とパフォーマンスの問題

私たちがよく知っているすべてのアプリケーションにおける典型的なシナリオの XNUMX つは、特定の基準に従ってデータを検索し、それを読みやすい形式で表示することです。 並べ替え、グループ化、ページングのための追加オプションがある場合もあります。 このタスクは理論的には簡単ですが、それを解決するときに多くの開発者が多くの間違いを犯し、それが後に生産性の低下を引き起こします。 この問題を解決するためのさまざまなオプションを検討し、最も効果的な実装を選択するための推奨事項を作成してみましょう。

ページング オプション #1

思い浮かぶ最も単純なオプションは、最も古典的な形式で検索結果をページごとに表示することです。

アプリケーションでリレーショナル データベースを使用しているとします。 この場合、このフォームで情報を表示するには、XNUMX つの SQL クエリを実行する必要があります。

• 現在のページの行を取得します。
• 検索条件に対応する総行数を計算します。これはページを表示するために必要です。

例としてテスト MS SQL データベースを使用して最初のクエリを見てみましょう アドベンチャーワークス 2016サーバー用。 この目的のために、Sales.SalesOrderHeader テーブルを使用します。

SELECT * FROM Sales.SalesOrderHeader
ORDER BY OrderDate DESC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY

上記のクエリは、追加日の降順で並べ替えられたリストの最初の 50 件の注文、つまり最新の 50 件の注文を返します。

テストベースでは高速に実行されますが、実行計画と I/O 統計を見てみましょう。

Table 'SalesOrderHeader'. Scan count 1, logical reads 698, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

クエリ ランタイムで SET STATISTICS IO ON コマンドを実行すると、各クエリの I/O 統計を取得できます。

実行計画からわかるように、最もリソースを消費するオプションは、ソース テーブルのすべての行を追加日で並べ替えることです。 そして問題は、テーブルに表示される行が増えるほど、並べ替えが「難しく」なるということです。 実際には、このような状況は避けるべきであるため、追加日にインデックスを追加して、リソース消費が変化したかどうかを確認してみましょう。

Table 'SalesOrderHeader'. Scan count 1, logical reads 165, physical reads 0, read-ahead reads 5, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

明らかに、かなり良くなりました。 しかし、すべての問題は解決されたのでしょうか？ 商品の総原価が 100 ドルを超える注文を検索するようにクエリを変更してみましょう。

SELECT * FROM Sales.SalesOrderHeader
WHERE SubTotal > 100
ORDER BY OrderDate DESC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY

Table 'SalesOrderHeader'. Scan count 1, logical reads 1081, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

面白い状況が発生しました。クエリ プランは前のプランよりもそれほど悪くありませんが、実際の論理読み取り数はフル テーブル スキャンのほぼ 165 倍になります。 解決策はあります。既存のインデックスから複合インデックスを作成し、商品の合計価格を XNUMX 番目のフィールドとして追加すると、再び XNUMX 個の論理読み取りが得られます。

CREATE INDEX IX_SalesOrderHeader_OrderDate_SubTotal on Sales.SalesOrderHeader(OrderDate, SubTotal);

この一連の例は長く続けることができますが、ここで表明したい主な考えは次の XNUMX つです。

• 検索クエリに新しい条件や並べ替え順序を追加すると、検索クエリの速度に大きな影響を与える可能性があります。
• ただし、検索語に一致するすべての結果ではなく、データの一部のみを差し引く必要がある場合、そのようなクエリを最適化する方法はたくさんあります。

次に、最初に説明した 100 番目のクエリ、つまり検索条件を満たすレコードの数をカウントするクエリに移りましょう。 同じ例を考えてみましょう - XNUMX ドルを超える注文を検索します。

SELECT COUNT(1) FROM Sales.SalesOrderHeader
WHERE SubTotal > 100

上記の複合インデックスを考慮すると、次が得られます。

Table 'SalesOrderHeader'. Scan count 1, logical reads 698, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SubTotal フィールドは最初の位置にないため、クエリではそれを使用できないため、クエリがインデックス全体を通過するという事実は驚くべきことではありません。 この問題は、SubTotal フィールドに別のインデックスを追加することで解決され、その結果、論理読み取りは 48 回のみになります。

数量を数えるリクエストの例をさらにいくつか挙げることができますが、本質は同じです。 データの受信と合計量のカウントは、根本的に異なる XNUMX つのリクエストです、それぞれに最適化のための独自の対策が必要です。 一般に、両方のクエリに対して同等に機能するインデックスの組み合わせを見つけることはできません。

したがって、このような検索ソリューションを開発するときに明確にする必要がある重要な要件の XNUMX つは、見つかったオブジェクトの総数を確認することが企業にとって本当に重要かどうかです。 いいえということがよくあります。 また、特定のページ番号によるナビゲーションは、ほとんどのページング シナリオが「次のページに進む」ように見えるため、私の意見では非常に範囲が狭いソリューションです。

ページング オプション #2

ユーザーは、見つかったオブジェクトの総数を知ることに関心がないと仮定しましょう。 検索ページを簡素化してみましょう。

実際、唯一変更されたのは、特定のページ番号に移動する方法がないことです。現在、このテーブルは表示するためにページ数を知る必要がありません。 しかし、疑問が生じます。(「次へ」リンクを正しく表示するために)、テーブルは次のページのデータがあるかどうかをどのようにして知るのでしょうか?

答えは非常に簡単です。表示に必要なレコードよりも 4 つ多くのレコードをデータベースから読み取ることができ、この「追加の」レコードの存在によって、次の部分があるかどうかがわかります。 この方法では、5 ページのデータを取得するために XNUMX つのリクエストを実行するだけで済み、パフォーマンスが大幅に向上し、そのような機能のサポートが容易になります。 私の実践では、レコードの総数のカウントを拒否すると、結果の提供が XNUMX ～ XNUMX 倍速くなるケースがありました。

このアプローチには、いくつかのユーザー インターフェイス オプションがあります。上記の例のような「戻る」コマンドと「進む」コマンド、表示された結果に新しい部分を追加するだけの「もっと読み込む」ボタン、機能する「無限スクロール」 「もっと読み込む」の原則に基づいていますが、次の部分を取得する合図は、ユーザーが表示されたすべての結果を最後までスクロールすることです。 どのような視覚的なソリューションであっても、データ サンプリングの原則は変わりません。

ページング実装の微妙な違い

上記のすべてのクエリ例では、クエリ自体が結果行の順序と返す必要がある行数を指定する場合、「オフセット + カウント」アプローチを使用しています。 まず、この場合にパラメータの受け渡しを整理する最適な方法を見てみましょう。 実際に、私はいくつかの方法を見つけました。

• 要求されたページのシリアル番号 (pageIndex)、ページ サイズ (pageSize)。
• 返される最初のレコードのシリアル番号 (startIndex)、結果内のレコードの最大数 (count)。
• 返される最初のレコードのシーケンス番号 (startIndex)、返される最後のレコードのシーケンス番号 (endIndex)。

一見すると、これは非常に初歩的で違いがないように思えるかもしれません。 しかし、これはそうではありません。最も便利で普遍的なオプションは XNUMX 番目 (startIndex、count) です。 これにはいくつかの理由があります。

• 上記の +1 エントリ校正アプローチの場合、pageIndex と pageSize を使用した最初のオプションは非常に不便です。 たとえば、50 ページあたり 1 件の投稿を表示したいとします。 上記のアルゴリズムによれば、必要以上に 1 レコード多く読み取る必要があります。 この「+51」がサーバーに実装されていない場合、最初のページでは 51 から 101 までのレコードを要求し、51 番目のページでは 52 から 102 までのレコードを要求する必要があることがわかります。 ページ サイズを XNUMX に指定し、pageIndex を増やすと、XNUMX 番目のページは XNUMX から XNUMX などに戻ります。 したがって、最初のオプションでは、次のページに移動するボタンを適切に実装する唯一の方法は、サーバーに「余分な」行を校正させることですが、これは非常に暗黙的なニュアンスになります。
• ほとんどのデータベースでクエリを実行するには、最後のレコードのインデックスではなくカウントを渡す必要があるため、XNUMX 番目のオプションはまったく意味がありません。 endIndex から startIndex を減算することは単純な算術演算かもしれませんが、ここでは不要です。

ここで、「オフセット + 数量」によるページングの実装の欠点について説明する必要があります。

• 後続の各ページの取得は、前のページよりもコストと時間がかかります。これは、データベースが検索と並べ替えの基準に従ってすべてのレコードを「最初から」調べてから、目的のフラグメントで停止する必要があるためです。
• すべての DBMS がこのアプローチをサポートできるわけではありません。

代替手段はありますが、それらも不完全です。 これらのアプローチの XNUMX つ目は「キーセット ページング」または「シーク メソッド」と呼ばれ、次のようなものです。一部を受信した後、ページの最後のレコードのフィールド値を記憶し、それらを使用してデータを取得できます。次の部分。 たとえば、次のクエリを実行しました。

SELECT * FROM Sales.SalesOrderHeader
ORDER BY OrderDate DESC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY

そして最後のレコードでは、注文日の値「2014-06-29」を取得しました。 次に、次のページを取得するには、次のことを試してください。

SELECT * FROM Sales.SalesOrderHeader
WHERE OrderDate < '2014-06-29'
ORDER BY OrderDate DESC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY

問題は、OrderDate が固有ではないフィールドであり、上で指定した条件では多くの必要な行が欠落する可能性があることです。 このクエリに明確さを追加するには、条件に一意のフィールドを追加する必要があります (75074 が最初の部分の主キーの最後の値であると仮定します)。

SELECT * FROM Sales.SalesOrderHeader
WHERE (OrderDate = '2014-06-29' AND SalesOrderID < 75074)
   OR (OrderDate < '2014-06-29')
ORDER BY OrderDate DESC, SalesOrderID DESC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY

このオプションは正しく機能しますが、条件に OR 演算子が含まれるため、一般に最適化が困難になります。 OrderDate が増加するにつれて主キーの値が増加する場合は、SalesOrderID によるフィルターのみを残すことで条件を簡素化できます。 ただし、主キーの値と結果の並べ替えに使用されるフィールドの間に厳密な相関関係がない場合、ほとんどの DBMS ではこの OR を回避できません。 私が知っている例外は PostgreSQL で、タプル比較を完全にサポートしており、上記の条件は「WHERE (OrderDate, SalesOrderID) < ('2014-06-29', 75074)」として記述できます。 これら XNUMX つのフィールドを持つ複合キーがあれば、このようなクエリはかなり簡単になるはずです。

XNUMX 番目の代替アプローチは、たとえば次のとおりです。 ElasticSearch スクロール API または コスモスDB — リクエストがデータに加えて、データの次の部分を取得できる特別な識別子を返す場合。 この識別子の存続期間が無制限である場合 (Comsos DB など)、これはページ間の連続遷移を伴うページングを実装する優れた方法です (前述のオプション #2)。 考えられる欠点: すべての DBMS でサポートされているわけではありません。 結果として得られる次のチャンク識別子の有効期間は限られている可能性があり、これは一般にユーザー インタラクション (ElasticSearch スクロール API など) の実装には適していません。

複雑なフィルタリング

タスクをさらに複雑にしてみましょう。 オンライン ストアでは誰もがよく知っている、いわゆるファセット検索を実装する必要があるとします。 この場合、orders テーブルに基づく上記の例はあまり説明的ではないため、AdventureWorks データベースの Product テーブルに切り替えてみましょう。

ファセット検索の背後にある考え方は何ですか? 実際には、各フィルター要素について、この基準を満たすレコードの数が表示されます。 他のすべてのカテゴリで選択されたフィルタを考慮して.

たとえば、この例で [Bikes] カテゴリと [Black] の色を選択すると、テーブルには黒いバイクのみが表示されます。

• カテゴリ グループの各基準について、そのカテゴリの製品の数が黒で表示されます。
• 「色」グループの各基準について、その色の自転車の数が表示されます。

このような条件での結果出力の例を次に示します。

「衣類」カテゴリーもチェックすると、在庫のある黒色の衣類も表に表示されます。 「カラー」セクションの黒色製品の数も新しい条件に従って再計算されますが、「カテゴリ」セクションのみ何も変わりません...これらの例が通常のファセット検索アルゴリズムを理解するのに十分であることを願っています。

ここで、これをリレーショナル ベースでどのように実装できるかを想像してみましょう。 カテゴリや色などの基準の各グループには、個別のクエリが必要になります。

SELECT pc.ProductCategoryID, pc.Name, COUNT(1) FROM Production.Product p
  INNER JOIN Production.ProductSubcategory ps ON p.ProductSubcategoryID = ps.ProductSubcategoryID
  INNER JOIN Production.ProductCategory pc ON ps.ProductCategoryID = pc.ProductCategoryID
WHERE p.Color = 'Black'
GROUP BY pc.ProductCategoryID, pc.Name
ORDER BY COUNT(1) DESC

SELECT Color, COUNT(1) FROM Production.Product p
  INNER JOIN Production.ProductSubcategory ps ON p.ProductSubcategoryID = ps.ProductSubcategoryID
WHERE ps.ProductCategoryID = 1 --Bikes
GROUP BY Color
ORDER BY COUNT(1) DESC

この解決策の何が問題なのでしょうか? それは非常に単純ですが、うまく拡張できません。 各フィルター セクションでは数量を計算するための個別のクエリが必要ですが、これらのクエリはそれほど簡単ではありません。 オンライン ストアでは、カテゴリによっては数十のフィルター セクションが存在する場合があり、これがパフォーマンスに重大な問題を引き起こす可能性があります。

通常、これらのステートメントの後に、いくつかの解決策が提供されます。

• すべての数量カウントを XNUMX つのクエリに結合します。 技術的には、これは UNION キーワードを使用して可能ですが、パフォーマンスにはあまり役立ちません。データベースは依然として各フラグメントを最初から実行する必要があります。
• キャッシュ量。 私が問題を説明するたびに、ほぼ毎回、このことが示唆されます。 注意していただきたいのは、これは一般的には不可能であるということです。 10 個の「ファセット」があり、それぞれに 5 つの値があるとします。 これは、同じオンラインストアで見られるものと比較すると、非常に「控えめな」状況です。 9 つのファセット要素の選択は、他の 50 つのファセット要素の量に影響します。つまり、基準の組み合わせごとに量が異なる可能性があります。 この例では、ユーザーが選択できる基準が合計 250 あるため、可能な組み合わせは 5 通りになりますが、このようなデータの配列を埋めるのに十分なメモリや時間がありません。 ここで、すべての組み合わせが現実であるわけではなく、ユーザーが 10 ～ XNUMX 個を超える基準を選択することはめったにないと反論することができます。 はい、遅延読み込みを実行して、これまでに選択されたものだけをキャッシュすることは可能ですが、選択の数が増えるほど、そのようなキャッシュの効率は低下し、応答時間の問題がより顕著になります (特に、データセットは定期的に変更されます)。

幸いなことに、このような問題には、大量のデータに対して予測どおりに機能する非常に効果的な解決策が長い間存在していました。 これらのオプションのいずれにおいても、ファセットの再計算と結果ページの受信をサーバーへの XNUMX つの並行呼び出しに分割し、ファセットによるデータのロードが表示を「妨げない」ようにユーザー インターフェイスを編成することが理にかなっています。の検索結果。

• 「ファセット」の完全な再計算を呼び出すのは、できるだけまれです。 たとえば、検索条件が変更されるたびにすべてを再計算するのではなく、現在の条件に一致する結果の総数を見つけて、ユーザーに「1425 件のレコードが見つかりました。表示しますか?」と表示するように求めます。 ユーザーは検索用語の変更を続けるか、「表示」ボタンをクリックすることができます。 XNUMX 番目のケースの場合のみ、結果を取得し、すべての「ファセット」で数量を再計算するすべてのリクエストが実行されます。 この場合、簡単にわかるように、結果の総数を取得するリクエストとその最適化に対処する必要があります。 この方法は、多くの小規模オンライン ストアで見られます。 明らかに、これはこの問題の万能薬ではありませんが、単純な場合には良い妥協策になる可能性があります。
• Solr、ElasticSearch、Sphinx などの検索エンジンを使用して結果を検索し、ファセットをカウントします。 これらはすべて「ファセット」を構築するように設計されており、逆インデックスによりこれを非常に効率的に実行できます。 検索エンジンがどのように機能するのか、そのような場合に汎用データベースよりも検索エンジンが有効である理由、どのような慣行と落とし穴があるのか​​、これは別の記事のトピックです。 ここで注意していただきたいのは、検索エンジンはメイン データ ストレージの代わりにはならないということです。検索エンジンは追加として使用され、検索に関連するメイン データベースの変更は検索インデックスに同期されます。 検索エンジンは通常、検索エンジンとのみ対話し、メイン データベースにはアクセスしません。 ここで最も重要な点の XNUMX つは、この同期を確実に行う方法です。 それはすべて「反応時間」の要件によって決まります。 メイン データベースの変更とその変更が検索で「現れる」までの時間が重要でない場合は、最近変更されたレコードを数分ごとに検索し、インデックスを作成するサービスを作成できます。 応答時間をできるだけ短くしたい場合は、次のようなものを実装できます。 トランザクション送信ボックス 検索サービスに更新を送信します。

所見

1. サーバー側のページングの実装は非常に複雑であり、急速に成長するデータ セットまたは単純に大規模なデータ セットの場合にのみ意味があります。 「大きい」または「急成長している」を評価する方法について完全に正確なレシピはありませんが、私は次のアプローチに従います。
   • サーバー時間とネットワーク送信を考慮して、データの完全なコレクションを受信することがパフォーマンス要件を正常に満たす場合、サーバー側でページングを実装する意味はありません。
   • データは少ないものの、データ収集は継続的に増加しているため、近い将来にはパフォーマンスの問題が発生しない状況が予想される場合があります。 将来のデータセットが前の点を満たさなくなる可能性がある場合は、すぐにページングを開始することをお勧めします。
2. ビジネス側に結果の総数を表示したり、ページ番号を表示したりするという厳格な要件がなく、システムに検索エンジンがない場合は、これらの点を実装せず、オプション #2 を検討することをお勧めします。
3. ファセット検索に対する明確な要件がある場合、パフォーマンスを犠牲にすることなく XNUMX つのオプションがあります。
   • 検索基準が変更されるたびにすべての数量を再計算しないでください。
   • Solr、ElasticSearch、Sphinx などの検索エンジンを使用します。 ただし、これはメイン データベースの代わりにはならず、検索問題を解決するためにメイン ストレージへの追加として使用する必要があることを理解する必要があります。
4. また、ファセット検索の場合、検索結果ページの取得とカウントを XNUMX つの並列リクエストに分割することが合理的です。 ユーザーにとっては結果の方が重要ですが、数量を数えることは結果を得るよりも時間がかかる場合があります。
5. 検索に SQL データベースを使用している場合、この部分に関連するコード変更は、適切な量のデータ (ライブ データベースの量を超える) でのパフォーマンスについて十分にテストする必要があります。 また、データベースのすべてのインスタンス、特に「ライブ」インスタンスでクエリ実行時間の監視を使用することをお勧めします。 開発段階ではクエリ プランに問題がなかったとしても、データ量が増加すると状況が著しく変化する可能性があります。

出所： habr.com