データベースのパフォーマンスの最適化について心配する必要がなかった時代は終わりました。 時間は静止していません。 新しいテクノロジー起業家は皆、入手できるすべてのデータを収集しようとしながら、次の Facebook を作りたいと考えています。 企業は、収益を上げるためにモデルをより適切にトレーニングするためにこのデータを必要とします。 このような状況では、プログラマーは、大量の情報を迅速かつ確実に処理できる API を作成する必要があります。
アプリケーションまたはデータベースのバックエンドを長期間設計している場合は、ページ分割されたクエリを実行するコードを作成したことがあるでしょう。 たとえば、次のようになります。
SELECT * FROM table_name LIMIT 10 OFFSET 40
それはどうですか？
しかし、これがページネーションの方法だった場合、最も効率的な方法でページネーションを行っていなかったと残念に思います。
私に反対したいですか？ . , и 彼らは、私が今日話したいテクニックをすでに使っています。
一度も使用したことのないバックエンド開発者の名前を少なくとも XNUMX 人挙げてください。 OFFSET и LIMIT ページ分割されたクエリを実行します。 MVP (Minimum Viable Product) および少量のデータが使用されるプロジェクトでは、このアプローチは非常に適用可能です。 いわば、それは「うまくいく」のです。
ただし、信頼性が高く効率的なシステムを最初から作成する必要がある場合は、そのようなシステムで使用されるデータベースのクエリの効率について事前に注意する必要があります。
今日は、ページ分割されたクエリ エンジンの一般的に使用される (あまりにもひどい) 実装の問題と、そのようなクエリを実行するときに高いパフォーマンスを達成する方法について説明します。
OFFSET と LIMIT の何が問題なのでしょうか?
すでに述べたように、 OFFSET и LIMIT 大量のデータを扱う必要のないプロジェクトで優れたパフォーマンスを発揮します。
この問題は、データベースのサイズがサーバーのメモリに収まらなくなるほど大きくなった場合に発生します。 ただし、このデータベースを操作する場合は、ページ分割されたクエリを使用する必要があります。
この問題が顕在化するには、DBMS が各ページ分割されたクエリに対して非効率な全テーブル スキャン操作を実行する状況がなければなりません (挿入操作や削除操作が発生する可能性があり、古いデータは必要ありません!)。
「フル テーブル スキャン」（または「シーケンシャル テーブル スキャン」、Sequential Scan）とは何ですか? これは、DBMS がテーブルの各行、つまりテーブルに含まれるデータを順番に読み取り、所定の条件に準拠しているかどうかをチェックする操作です。 このタイプのテーブル スキャンは最も遅いことが知られています。 実際、これが実行されると、サーバーのディスク サブシステムに関係する多くの入出力操作が実行されます。 ディスクに保存されているデータの操作に伴う遅延と、ディスクからメモリへのデータ転送がリソースを大量に消費する操作であるという事実により、状況はさらに悪化します。
たとえば、100000000 人のユーザーのレコードがあり、次の構成を使用してクエリを実行するとします。 OFFSET 50000000。 これは、DBMS がこれらのレコードをすべてロードし (必要さえありません!)、メモリに格納し、その後、たとえばレポートされた 20 件の結果を取得する必要があることを意味します。 LIMIT.
「50000 から 50020 から 100000 までの行を選択」のようになったとします。 つまり、システムはクエリを完了するために最初に 50000 行をロードする必要があります。 彼女がどれほど不必要な仕事をしなければならないかわかりますか?
私の言うことが信じられない場合は、この機能を使用して私が作成した例を見てください。 .
db-fiddle.com の例
そこ、左側の畑の中に Schema SQL、データベースに 100000 行を挿入するコードがあり、右側のフィールドに Query SQL、XNUMX つのクエリが表示されます。 最初の遅いものは次のようになります。
SELECT *
FROM `docs`
LIMIT 10 OFFSET 85000;
XNUMX 番目は、同じ問題に対する効果的な解決策で、次のようになります。
SELECT *
FROM `docs`
WHERE id > 85000
LIMIT 10;
これらのリクエストを満たすには、ボタンをクリックするだけです Run ページの上部にあります。 これを行った後、クエリの実行時間に関する情報を比較します。 効果のないクエリの実行には、30 番目のクエリの実行に比べて少なくとも 37 倍の時間がかかることがわかりました (この時間は実行ごとに異なります。たとえば、システムは最初のクエリが完了するまでに 1 ミリ秒かかったと報告する可能性がありますが、秒 - XNUMX ミリ秒）。
そして、より多くのデータがある場合、すべてはさらに悪化するでしょう（これを確信するには、私の 10万行）。
これまで説明してきたことから、データベース クエリが実際にどのように処理されるかについてある程度の洞察が得られるはずです。
値が大きいほど注意してください OFFSET — リクエストが完了するまでにかかる時間が長くなります。
OFFSET と LIMIT の組み合わせの代わりに何を使用すればよいですか?
組み合わせの代わりに OFFSET и LIMIT 次のスキームに従って構築された構造を使用する価値があります。
SELECT * FROM table_name WHERE id > 10 LIMIT 20
これは、カーソルベースのページネーションを使用したクエリ実行です。
現在のものをローカルに保存する代わりに OFFSET и LIMIT 各リクエストでそれらを送信するには、最後に受信した主キー (通常、これは ID）そして、 LIMIT, その結果、上記と同様のクエリが取得されます。
なぜ？ 重要なのは、最後に読み取られた行の識別子を明示的に指定することで、必要なデータの検索をどこから開始する必要があるかを DBMS に伝えることです。 さらに、キーを使用することで検索が効率的に実行され、指定範囲外の行にシステムが邪魔されることがなくなります。
次のさまざまなクエリのパフォーマンスの比較を見てみましょう。 以下は効果のないクエリです。
リクエストが遅い
そして、これがこのリクエストの最適化されたバージョンです。
クイックリクエスト
どちらのクエリもまったく同じ量のデータを返します。 ただし、最初の完了には 12,80 秒かかり、0,01 番目の完了には XNUMX 秒かかります。 違いを感じますか？
考えられる問題
提案されたクエリ方法が効果的に機能するには、整数識別子などの一意の連続インデックスを含む列 (複数の列) がテーブルに必要です。 特定のケースでは、これによって、そのようなクエリを使用してデータベースの操作速度を向上させることができるかどうかが決まります。
当然のことながら、クエリを構築するときは、テーブルの特定のアーキテクチャを考慮し、既存のテーブルで最適に機能するメカニズムを選択する必要があります。 たとえば、大量の関連データを使用するクエリを処理する必要がある場合は、次のことが興味深いかもしれません。 論文。
たとえば、多対多のリレーションシップを持つテーブルがある場合など、主キーが見つからないという問題に直面した場合は、次のような従来のアプローチを使用します。 OFFSET и LIMIT、私たちに合うことが保証されています。 ただし、これを使用すると、クエリが遅くなる可能性があります。 このような場合、ページ分割されたクエリを処理するためだけに必要な場合でも、自動インクリメント主キーを使用することをお勧めします。
このトピックに興味がある場合は、 , и - いくつかの便利な資料。
結果
私たちが導き出せる主な結論は、データベースのサイズに関係なく、クエリの実行速度を分析することが常に必要であるということです。 現在、ソリューションのスケーラビリティは非常に重要であり、特定のシステムでの作業の最初からすべてが正しく設計されていれば、将来的には開発者を多くの問題から救うことができます。
データベース クエリをどのように分析して最適化しますか?
出所： habr.com