[Database] Paging query with index (1)

Project Description

2021年の夏休み期間中、KNOWMARKETというプロジェクトが行われました.簡単に言えば、このプロジェクトは私が通っている慶北大学の在校生のために団体購入サービスを提供しています.
簡単な例を挙げると、一人で出前を注文するとき、最小の注文金額に悩んでしまいます.また、宅配便で生活必需品を購入しようとする場合は、必要なものですが、1つだけ売るよりもセットで販売することが多いです.もちろん単独でいくつか買うより安いです...
いずれにしても、このような理由で、私たちの学校の周りにかつて一人暮らしの学生や寄宿生が共同で購入できるオープンKakaotalk群がありましたが、このサービス化して、より便利に共同で購入できれば、どうなるのでしょうか.(思いつかなかった…!)

Features

アプリケーションの主な機能では、特定のアイテムを共同で購入するために、文章を募集し、バックグラウンドで対応するAPIを実現する必要があります.UIを簡単に見てみましょう.

POST /api/v1/posts

GET /api/v1/posts?pageNo

GET /api/v1/posts/{post_uid}

DeleteやUpdate機能も作成されていますが、記事で議論するわけではないのでスキップしましょう.

Paging Query

団体購入の求人記事リストデータに返信するページング機能が表示されます.当時実施されていた頃は、急遽投入されたプロジェクトだったので、性能を考えずに実現しました.コードでこの方法を理解してみましょう.

getPosts = async (skipValue: number) => {
        const posts = await getRepository(Post)
            .find({
                select: ['post_uid', 'title', 'created_at'],
                order: {
                    created_at: 'DESC'
                },
                skip: skipValue,
                take: 20                
            })
        return posts;
    }

上のコードをSQLクエリーに変更します.次のようにします.

SELECT post_uid, title, created_at FROM post
ORDER BY created_at DESC
LIMIT 20 OFFSET skipValue;

簡単に言えばskipValueはスキップする文章の数を表し,Limitはインポートする文章の数を表す.skipValueは変数にすぎず、実際にクエリを実行する際に20、40、60などのクライアントから受け取ったpageNoクエリ値に基づいて定数として決定される.
いずれにしても、上記の方法でページを実現することは、作成したアプリケーションでは問題ありません.ポストテーブルのrowはまだ1000個足らず…!しかし、私たちのアプリケーションのリクエストトラフィックの中で最も多くの部分を占めており、データが蓄積されるほど、後で問題になると思います.案の定、パッケージ化の過程でページ照会方式を学んでいますが、上記の方式では問題があります.

What is Problem?

だから問題はいったい何ですか?開発バックエンドで有名な李東旭の記事を参考にしました!
https://jojoldu.tistory.com/528

Offset Problem

東旭の記事を参考にして分かるように、Offsetを適用すれば、Offsetの大きさで検索するのではなく、Offset+Limitでどれだけ読んで、Offsetの大きさで結果から削除するのか.つまり、前に読んだローが要求されるたびに、そのローが読み出される.すなわち,データが1億回であると仮定すると,1億行近くの全表スキャンが可能となる.

Solution

このような非効率的な方法を解決するために,インデックスを利用したNo Offset法がある.

SELECT post_uid, title, created_at FROM post
ORDER BY created_at DESC
LIMIT 20 OFFSET skipValue;

上記のクエリを使用してページングを行った場合、前のページの最後の記事のpk post uidを使用してインデックスを作成し、次のページの最初の記事をすばやく検索できます.

SELECT post_uid, title, created_at FROM post
WHERE post_uid < '직전 페이지의 마지막 post_uid 값'
ORDER BY post_uid DESC
LIMIT 20;

この方法を私が行ったプロジェクトに適用するにはpost uidを増分number値に変換する必要があるかもしれません.
整理する
ページングクエリを効率的に処理するためにNo Offsetメソッドを知っていて本当に面白かったです.データベースレッスンの時、インデックスが何なのか、重要性は分かりましたが、どのように正しく使うのか分かりませんでしたが、第一歩で東旭のいい文章を見てよかったです.インデックスもいろいろありますが、学部で学んだ内容に合わせて整理しておきましょう.