キー上のページネーションのツールによるサポートが必要なのはなぜですか?

こんにちは、みんな！ 私は Java + Spring でマイクロサービスを作成するバックエンド開発者です。 私はティンコフの社内製品開発チームの XNUMX つで働いています。

私たちのチームでは、DBMS でのクエリの最適化に関する問題が頻繁に発生します。 常に少しでも速くしたいと考えていますが、慎重に構築されたインデックスだけで常にうまくいくとは限りません。いくつかの回避策を探す必要があります。 データベースを操作する際の適切な最適化を求めて Web をさまよっているときに、次のことを発見しました。 Marcus Wynand の無限に役立つブログ、『SQL Performance Explained』の著者。 全記事を一気に読める珍しいタイプのブログです。

マーカスの短い記事を翻訳したいと思います。 これは、SQL 標準に従ったオフセット操作のパフォーマンスに関する古い、しかし依然として関連する問題に注意を向けようとする、ある意味マニフェストと呼ぶことができます。

いくつかの場所で、著者の説明とコメントを補足します。 このような箇所をすべて「約」と呼びます。 より明確にするために

簡単な紹介

オフセットによるページ選択の作業がいかに問題があり、遅いかは多くの人が知っていると思います。 これをより効率的な設計に簡単に置き換えることができることをご存知ですか?

したがって、offset キーワードは、リクエスト内の最初の n レコードをスキップするようにデータベースに指示します。 ただし、データベースは依然として最初の n 個のレコードを指定された順序でディスクから読み取る必要があり (注: ソートが指定されている場合はソートを適用します)、そうして初めて n+1 以降のレコードを返すことが可能になります。 最も興味深いのは、問題が DBMS の特定の実装にあるのではなく、標準に従った元の定義にあるということです。

…行は最初に次に従って並べ替えられます。 次に、で指定された行数を削除することで制限します。 最初から...
-SQL:2016、パート 2、4.15.3 派生テーブル (注: 現在最も使用されている標準)

ここで重要な点は、offset は XNUMX つのパラメータ (スキップするレコードの数) を受け取るだけであるということです。 この定義に従って、DBMS はすべてのレコードを取得し、不要なレコードを破棄することしかできません。 明らかに、このオフセットの定義により、余分な作業が必要になります。 SQL か NoSQL かは関係ありません。

もう少しだけ痛みが増す

オフセットの問題はそれだけではありません。その理由は次のとおりです。 ディスクから XNUMX ページのデータを読み取る間に、別の操作で新しいレコードが挿入された場合、この場合はどうなりますか?

オフセットを使用して前のページからレコードをスキップすると、異なるページの読み取りの間に新しいレコードを追加する状況で、重複が発生する可能性が高くなります (注: これは、order by 構造を使用してページごとに読み取るときに発生する可能性があります。出力の途中で新しいエントリが取得される可能性があります)。

この図はこの状況を明確に示しています。 ベースは最初の 10 レコードを読み取り、その後新しいレコードが挿入され、読み取られたすべてのレコードが 1 だけオフセットされます。次に、ベースは次の 10 レコードから新しいページを取得し、本来であれば 11 番目からではなく、最初のレコードから開始します。 10位、この記録を再現。 この表現の使用に関連する異常は他にもありますが、これが最も一般的です。

すでにわかったように、これらは特定の DBMS やその実装の問題ではありません。 問題は、SQL 標準に従ってページネーションを定義することにあります。 どのページをフェッチするか、スキップするレコードの数を DBMS に指示します。 情報が少なすぎるため、データベースはそのようなリクエストを最適化することができません。

これは特定のキーワードの問題ではなく、クエリのセマンティクスの問題であることを明確にすることも重要です。 問題のある性質において同一の構文がさらにいくつかあります。

• offset キーワードについては前述したとおりです。
• XNUMX つのキーワード limit [offset] の構造 (limit 自体はそれほど悪くありませんが)。
• 行番号付け (たとえば、row_number()、rownum など) に基づいた下限によるフィルター処理。

これらの式はすべて、スキップする行数を示すだけであり、追加の情報やコンテキストはありません。

この記事の後半では、これらすべてのオプションの概要として offset キーワードが使用されます。

OFFSETなしの生活

さて、これらすべての問題がなければ、私たちの世界がどのようなものになるかを想像してみましょう。 オフセットなしの生活はそれほど難しくないことがわかりました。select を使用すると、where の条件を使用して、まだ見ていない行 (注: つまり、前のページになかった行) のみを選択できます。

この場合、選択が順序付きセット (古き良き order by) で実行されるという事実から開始します。 順序付きセットがあるため、非常に単純なフィルターを使用して、前のページの最後のレコードの背後にあるデータのみを取得できます。

    SELECT ...
    FROM ...
    WHERE ...
    AND id < ?last_seen_id
    ORDER BY id DESC
    FETCH FIRST 10 ROWS ONLY

それがこのアプローチの原則全体です。 もちろん、多くの列で並べ替えるとさらに楽しくなりますが、考え方は同じです。 この設計は多くの用途に適用できることに注意することが重要です。 NoSQL-決定。

このアプローチは、シーク メソッドまたはキーセット ページネーションと呼ばれます。 これはフローティング結果の問題 (注: 前述のページ読み取り間の書き込み状況) を解決し、もちろん、誰もが気に入っていることですが、従来のオフセットよりも高速かつ安定して動作します。 安定性は、リクエストの処理時間がリクエストされたテーブルの数に比例して増加しないという事実にあります (注: ページネーションのさまざまなアプローチの作業について詳しく知りたい場合は、 著者のプレゼンテーションに目を通す。 さまざまな方法の比較ベンチマークもそこで見つけることができます)。

スライドの XNUMX つ それについて話しますもちろん、キーによるページネーションは万能ではなく、限界があります。 最も重要なのは、彼女にはランダムなページを読む能力がないことです (注: 一貫性がありません)。 しかし、無限スクロール（注：フロントエンド）の時代では、これはそれほど問題ではありません。 いずれにせよ、クリックするページ番号を指定するのは UI 設計において間違った決定です (注: 記事執筆者の意見)。

道具についてはどうですか？

キー上のページネーションは、この方法に対するツールのサポートが不足しているため、多くの場合適切ではありません。 さまざまなフレームワークを含むほとんどの開発ツールでは、ページネーションの実行方法を正確に選択することはできません。

説明されている方法では、DBMS からブラウザでの無限スクロールを伴う AJAX リクエストの実行まで、使用されるテクノロジのエンドツーエンドのサポートが必要であるという事実によって、状況はさらに悪化します。 ページ番号だけを指定する代わりに、すべてのページのキーのセットを一度に指定する必要があります。

ただし、キーのページネーションをサポートするフレームワークの数は徐々に増えています。 現時点での内容は次のとおりです。

• ジョーク Javaの場合。
• 注文クエリ ルビーの場合。
• チャンケーター и Django 無限スクロールのページネーション ジャンゴの場合。
• SQL Alchemy sqlakeyset Pythonの場合;
• ブレイズ持続性 — JPA実装用の基準API。
• DBIx::クラス::ラッパー Perlの場合;
• Massive.js、Node.js のマッパー キーセットのドキュメント.

(注: 翻訳時に一部のライブラリが 2017 年から 2018 年以降更新されていなかったため、一部のリンクが削除されました。興味がある場合は、元のソースを参照してください。)

今この瞬間にあなたの助けが必要です。 ページネーションを使用するフレームワークを開発またはサポートしている場合は、キーのページネーションのネイティブ サポートを提供するようお願いします。 ご質問がある場合やサポートが必要な場合は、喜んでお手伝いさせていただきます (форум, Twitter, お問い合わせフォーム) (注: マーカスとの私の経験から、彼はこのトピックを広めることに非常に熱心であると言えます)。

キーによるページネーションをサポートする価値があると思われる既製のソリューションを使用している場合は、リクエストを作成するか、可能であれば既製のソリューションを提供してください。 この記事へのリンクも可能です。

まとめ

キーによるページネーションのような単純で便利なアプローチが普及していない理由は、実装が技術的に難しいためでも、多大な労力を必要とするためでもありません。 主な理由は、多くの人がオフセットを見て作業することに慣れているためです。このアプローチは標準自体によって規定されています。

その結果、ページネーションへのアプローチを変更しようと考える人はほとんどおらず、そのため、フレームワークやライブラリからの手段によるサポートは十分に発展していません。 したがって、オフセットなしのページネーションのアイデアと目標が近い場合は、それを広めるのにご協力ください。

出所： https://use-the-index-luke.com/no-offset
著者: マーカス・ウィナンド

出所： habr.com