オンライン上では、注文一覧ページがページをめくるごとに固まり、1000ページ目までくるとリクエストに10秒以上かかるという動作のフィードバックが発生しました。
DBA はスロー クエリ ログをチェックし、SQL 構文に問題がないことを確認しました。
SELECT * FROM orders ORDER BY id LIMIT 100000, 10;
構文エラーはありませんが、クエリが遅いだけです。これは典型的なディープ ページングの問題です。
今日は、この問題について最初から最後まで説明します。遅さの原因は何か、一般的なソリューションの利点は何か、失敗のシナリオは何か、そして私の最終的な解決策について説明します。
1. まず、どこが遅いのかを特定します。
多くの人は 1 つの文だけでディープ ページングを理解できます。offset 大きいほど、より多くの行がスキャンされます。この言葉は真実ですが、半分しか真実ではありません。
MySQLの実行 LIMIT 100000, 10 実際のプロセス: インデックスに沿って最初の 100010 行を読み取り、最初の 100000 行を破棄し、最後の 10 行のみを返します。
この SQL でセカンダリ インデックスが使用されている場合 (たとえば、 create_time 並べ替え)、行が読み取られるたびに最初の 100,000 行をテーブルに返す必要があります。主キーを使用してクラスター化インデックス内の完全な行データをクエリし、テーブルに返されるたびにランダム IO が行われます。
つまり、ディープ ページングのコストは 2 倍になります。無効なスキャン + 無効なテーブルの戻り。
これは、一般的な現象も説明できます。つまり、同じオフセットが 100,000 行の場合、主キーによってソートされたページングは、セカンダリ インデックスによるソートよりもはるかに高速です。主キーインデックス自体はデータであるため、テーブルを返す必要はありません。
「スキャンされた行の数が多い」ということを覚えておいてください。解決策は限られています。テーブルの戻りコストを考慮すると、最適化のアイデアがさらに広がります。
2. カーソル方式: 高速ですが境界があります
最も古典的な最適化ソリューションはカーソル ページングです。オフセットは使用されなくなり、前のページの最後のレコードの ID がクエリの開始点として使用されます。
-- 示例
SELECT * FROM orders WHERE id > #{lastId} ORDER BY id LIMIT 10;
主キー インデックスは、10 行のみをスキャンして開始点を直接見つけることができ、オフセットによって引き起こされる大量のスキャンとテーブル リターンのオーバーヘッドがなくなります。実際に数千万の単一テーブル シナリオを測定したところ、当初は数秒かかっていたクエリが数ミリ秒に短縮されました。
しかし、計画を検討するとき、私はしばしばその限界を思い出します。それは、カーソル方式の適用条件が書き込み方式よりもはるかに厳しいということです。
- 並べ替えフィールドは主キーではありません (例:
create_time並べ替え: 時間をカーソルとして使用できますが、時間は繰り返されるため、組み合わせる必要があります。(create_time, id)ユニオンカーソル; - ビジネスは別のページにジャンプする必要があります。「前のページの最後の ID」を取得できず、カーソルは直接失敗します。カーソル メソッドは、操作のバックグラウンドで 500 ページに直接ジャンプする必要性をサポートしていません。
- フィルター条件は動的で変更可能です。オプションのクエリ次元が十数個あるため、すべての組み合わせに対して結合インデックスを確立するのは困難です。
- 主キーは Snowflake ID/UUID です。ID は連続した順序であることは保証されません。
id > ?クエリのセマンティクス自体には隠れた危険があります。
カーソル方式は、C 側の「次のページ、順次ブラウジング」シナリオに非常に適しています。パフォーマンスの限界は非常に高いですが、これは普遍的な解決策ではありません。
3. 遅延相関: ほとんどのシナリオに最適なソリューション
カーソルが使用できない場合、私が推奨する解決策は遅延関連付けです。核となるアイデア:主キー クエリと完全なデータ クエリを 2 つのステップに分割する。
サブクエリは、インデックスが事前に作成されている場合、インデックスの ID のみをクエリします。 (create_time, id) このタイプのジョイントはインデックスをカバーし、サブクエリはインデックスのみを使用し、プロセス全体でテーブルを返す必要はありません。 100,000 行がスキャンされた場合でも、インデックスは順次読み取られるため、オーバーヘッドは非常に低くなります。 10 個の主キーを取得した後、テーブルに 10 回戻ってすべてのフィールドを取得します。
SQL の例:
SELECT o.* FROM orders o
INNER JOIN (
SELECT id FROM orders ORDER BY create_time LIMIT 100000, 10
) t ON o.id = t.id;
元の書き込み方法と比較すると、100,000 行もスキャンされ、テーブルの戻り数が 100,000 から 10 倍に減少します。パフォーマンスは大幅に向上します。
遅延相関の利点:
- フロントエンドのインタラクションを変更したり、ページ ジャンプをサポートしたりする必要はありません。
- 動的フィルタリング条件と互換性があります。
- SQL レベルとインデックス レベルでのみ変更され、ビジネスへの侵入はほとんどありません。
これは最速のソリューションではありませんが、最も多用途です。ほとんどの深いページングの問題は、遅延関連付け + インデックスのカバーによって解決できます。
4. 選択: すべてのディープ ページングが最適化に値するわけではありません
最後に、見落とされがちな点について話しましょう。最適化の前提は次のとおりです。最適化する価値がある。
- 100 万以内のテーブル: 基本的にネイティブの制限で十分です。過度に設計しないでください。
- 数千万の単一テーブル、C 側の高頻度の順次ページ切り替え: 優先カーソル ページング、最高のパフォーマンス。
- 複数条件フィルタリング。ページ ジャンプをサポートする必要があります。遅延関連付け + インデックスをカバーします。
- データの量が多くなり、クエリのディメンションが非常に複雑になります。その場合は、ページング用に Elasticsearch や ClickHouse などの専用のクエリ エンジンにデータを同期することを検討してください。
ここでは、データ同期の遅延、一貫性の維持、追加のコンポーネントの運用および保守のコストなどのコストを考慮する必要があります。ページング インターフェイスを解決するためだけに ES を導入するのは、多くの場合過剰です。
また、直感に反する結論もあります。バックグラウンド管理システムは、月に数百ページしかめくらないなど、低頻度のクエリを実行し、2 秒の遅延は実際にはほとんど影響しません。リソースには、C サイド リストなどの高 QPS コア リンクが優先されます。
冒頭のオンライン事故に戻ります。
C 側の順序リストはカーソル ページングに変更され、管理バックグラウンドの複雑なフィルタリング ページは遅延関連付けを使用します。オンラインになってから 2 週間後、この遅い SQL はスロー クエリ ログに表示されなくなりました。
ディープページングの最適化に対する標準的な答えはなく、シナリオと境界があるだけです。
速度が遅い理由を次のように分類します。スキャンオーバーヘッド、テーブルリターンオーバーヘッド2 つのレイヤー、各ソリューションの適用可能な境界を明確にすることは、「制限の代わりに ID カーソルを使用する」ことを暗記するよりもはるかに価値があります。
