結果をページ付けする

大きなクエリ結果をページングするには、必要な部分を「ページ分け」方式で取得できます。

クエリ結果をページ分割する

TiDBでは、 LIMIT文を使ってクエリ結果をページ分割できます。例えば：

SELECT * FROM table_a t ORDER BY gmt_modified DESC LIMIT offset, row_count;

offsetはレコードの開始番号、 row_countページあたりのレコード数を示します。TiDBはLIMIT row_count OFFSET offset構文もサポートしています。

ページ区切りを使用する場合、データをランダムに表示する必要がない限り、 ORDER BYステートメントを使用してクエリ結果を並べ替えることをお勧めします。

例えば、アプリケーション書店のユーザーが最新の出版済み書籍をページ分けして閲覧できるようにするには、 LIMIT 0, 10ステートメントを使用します。このステートメントは、結果リストの最初のページを返します。このページには、最大 10 件のレコードが含まれます。2 ページ目を取得するには、ステートメントをLIMIT 10, 10に変更します。

SELECT *
FROM books
ORDER BY published_at DESC
LIMIT 0, 10;

アプリケーション開発において、バックエンドプログラムはフロントエンドからoffset番目のパラメータではなく、 page_numberのパラメータ（リクエストされているページ番号）とpage_sizeパラメータ（ページあたりのレコード数を制御するパラメータ）を受け取ります。そのため、クエリを実行する前にいくつかの変換を行う必要がありました。

public List<Book> getLatestBooksPage(Long pageNumber, Long pageSize) throws SQLException {
    pageNumber = pageNumber < 1L ? 1L : pageNumber;
    pageSize = pageSize < 10L ? 10L : pageSize;
    Long offset = (pageNumber - 1) * pageSize;
    Long limit = pageSize;
    List<Book> books = new ArrayList<>();
    try (Connection conn = ds.getConnection()) {
        PreparedStatement stmt = conn.prepareStatement("""
        SELECT id, title, published_at
        FROM books
        ORDER BY published_at DESC
        LIMIT ?, ?;
        """);
        stmt.setLong(1, offset);
        stmt.setLong(2, limit);
        ResultSet rs = stmt.executeQuery();
        while (rs.next()) {
            Book book = new Book();
            book.setId(rs.getLong("id"));
            book.setTitle(rs.getString("title"));
            book.setPublishedAt(rs.getDate("published_at"));
            books.add(book);
        }
    }
    return books;
}

単一フィールドの主キーテーブルのページングバッチ

通常、ページネーションSQL文では、主キーまたは一意のインデックスを使用して結果をソートし、 LIMIT句のoffsetキーワードを使用して指定した行数でページを分割します。これにより、ページは独立したトランザクションにラップされ、柔軟なページング更新が実現します。しかし、欠点も明らかです。主キーまたは一意のインデックスをソートする必要があるため、オフセットが大きいほど、特にデータ量が多い場合は、より多くのコンピューティングリソースを消費します。

以下に、より効率的なページングバッチ処理方法を紹介します。

まず、データを主キーでソートし、ウィンドウ関数row_number()呼び出して各行の行番号を生成します。次に、集計関数を呼び出して、指定されたページサイズで行番号をグループ化し、各ページの最小値と最大値を計算します。

SELECT
    floor((t.row_num - 1) / 1000) + 1 AS page_num,
    min(t.id) AS start_key,
    max(t.id) AS end_key,
    count(*) AS page_size
FROM (
    SELECT id, row_number() OVER (ORDER BY id) AS row_num
    FROM books
) t
GROUP BY page_num
ORDER BY page_num;

結果は次のとおりです。

+----------+------------+------------+-----------+
| page_num | start_key  | end_key    | page_size |
+----------+------------+------------+-----------+
|        1 |     268996 |  213168525 |      1000 |
|        2 |  213210359 |  430012226 |      1000 |
|        3 |  430137681 |  647846033 |      1000 |
|        4 |  647998334 |  848878952 |      1000 |
|        5 |  848899254 | 1040978080 |      1000 |
...
|       20 | 4077418867 | 4294004213 |      1000 |
+----------+------------+------------+-----------+
20 rows in set (0.01 sec)

次に、 WHERE id BETWEEN start_key AND end_key文を使用して各スライスのデータをクエリします。データをより効率的に更新するには、データの変更時に上記のスライス情報を使用できます。

1 ページ目にあるすべての書籍の基本情報を削除するには、上記の結果のstart_keyとend_key 1 ページ目の値に置き換えます。

DELETE FROM books
WHERE
    id BETWEEN 268996 AND 213168525
ORDER BY id;

Javaで、ページのメタ情報を保存するクラスをPageMeta定義します。

public class PageMeta<K> {
    private Long pageNum;
    private K startKey;
    private K endKey;
    private Long pageSize;

    // Skip the getters and setters.

}

ページメタ情報リストを取得するgetPageMetaList()メソッドを定義し、次にページメタ情報に従ってデータを一括削除するdeleteBooksByPageMeta()メソッドを定義します。

public class BookDAO {
    public List<PageMeta<Long>> getPageMetaList() throws SQLException {
        List<PageMeta<Long>> pageMetaList = new ArrayList<>();
        try (Connection conn = ds.getConnection()) {
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("""
            SELECT
                floor((t.row_num - 1) / 1000) + 1 AS page_num,
                min(t.id) AS start_key,
                max(t.id) AS end_key,
                count(*) AS page_size
            FROM (
                SELECT id, row_number() OVER (ORDER BY id) AS row_num
                FROM books
            ) t
            GROUP BY page_num
            ORDER BY page_num;
            """);
            while (rs.next()) {
                PageMeta<Long> pageMeta = new PageMeta<>();
                pageMeta.setPageNum(rs.getLong("page_num"));
                pageMeta.setStartKey(rs.getLong("start_key"));
                pageMeta.setEndKey(rs.getLong("end_key"));
                pageMeta.setPageSize(rs.getLong("page_size"));
                pageMetaList.add(pageMeta);
            }
        }
        return pageMetaList;
    }

    public void deleteBooksByPageMeta(PageMeta<Long> pageMeta) throws SQLException {
        try (Connection conn = ds.getConnection()) {
            PreparedStatement stmt = conn.prepareStatement("DELETE FROM books WHERE id >= ? AND id <= ?");
            stmt.setLong(1, pageMeta.getStartKey());
            stmt.setLong(2, pageMeta.getEndKey());
            stmt.executeUpdate();
        }
    }
}

次のステートメントは、ページ 1 のデータを削除します。

List<PageMeta<Long>> pageMetaList = bookDAO.getPageMetaList();
if (pageMetaList.size() > 0) {
    bookDAO.deleteBooksByPageMeta(pageMetaList.get(0));
}

次のステートメントは、ページングによってすべての書籍データを一括削除します。

List<PageMeta<Long>> pageMetaList = bookDAO.getPageMetaList();
pageMetaList.forEach((pageMeta) -> {
    try {
        bookDAO.deleteBooksByPageMeta(pageMeta);
    } catch (SQLException e) {
        e.printStackTrace();
    }
});

この方法は、頻繁なデータソート操作によるコンピューティングリソースの浪費を回避することで、バッチ処理の効率を大幅に向上させます。

複合主キーテーブルのページングバッチ

非クラスター化インデックステーブル

非クラスター化インデックステーブル (「非インデックス構成テーブル」とも呼ばれます) の場合、内部フィールド_tidb_rowidページ区切りキーとして使用でき、ページ区切りの方法は単一フィールドの主キーテーブルの場合と同じです。

ヒント：
SHOW CREATE TABLE users;ステートメントを使用して、テーブルの主キーがクラスター化インデックス使用しているかどうかを確認できます。

例えば：

SELECT
    floor((t.row_num - 1) / 1000) + 1 AS page_num,
    min(t._tidb_rowid) AS start_key,
    max(t._tidb_rowid) AS end_key,
    count(*) AS page_size
FROM (
    SELECT _tidb_rowid, row_number () OVER (ORDER BY _tidb_rowid) AS row_num
    FROM users
) t
GROUP BY page_num
ORDER BY page_num;

結果は次のとおりです。

+----------+-----------+---------+-----------+
| page_num | start_key | end_key | page_size |
+----------+-----------+---------+-----------+
|        1 |         1 |    1000 |      1000 |
|        2 |      1001 |    2000 |      1000 |
|        3 |      2001 |    3000 |      1000 |
|        4 |      3001 |    4000 |      1000 |
|        5 |      4001 |    5000 |      1000 |
|        6 |      5001 |    6000 |      1000 |
|        7 |      6001 |    7000 |      1000 |
|        8 |      7001 |    8000 |      1000 |
|        9 |      8001 |    9000 |      1000 |
|       10 |      9001 |    9990 |       990 |
+----------+-----------+---------+-----------+
10 rows in set (0.00 sec)

クラスター化インデックステーブル

クラスター化インデックステーブル (「インデックス構成テーブル」とも呼ばれます) の場合、 concat関数を使用して複数の列の値をキーとして連結し、ウィンドウ関数を使用してページング情報を照会できます。

この時点ではキーは文字列であり、 minとmax集約関数を介したスライスで正しいstart_keyとend_key取得するには、文字列の長さが常に一定であることを確認する必要があります。文字列連結のフィールドの長さが固定でない場合は、 LPAD関数を使用してパディングすることができます。

たとえば、次のようにして、テーブルratingsのデータのページングバッチを実装できます。

以下のステートメントを使用してメタ情報テーブルを作成します。5 種類のbook_idとuser_id連結したキーは同じ長さに変換できないため、 bigintの最大ビット数である 19 ビットに合わせてbigint関数LPAD使用して長さを0で埋めます。

SELECT
    floor((t1.row_num - 1) / 10000) + 1 AS page_num,
    min(mvalue) AS start_key,
    max(mvalue) AS end_key,
    count(*) AS page_size
FROM (
    SELECT
        concat('(', LPAD(book_id, 19, 0), ',', LPAD(user_id, 19, 0), ')') AS mvalue,
        row_number() OVER (ORDER BY book_id, user_id) AS row_num
    FROM ratings
) t1
GROUP BY page_num
ORDER BY page_num;

注記：
上記のSQL文はTableFullScanとして実行されます。データ量が多いとクエリが遅くなるため、 TiFlashを使用する高速化できます。

結果は次のとおりです。

+----------+-------------------------------------------+-------------------------------------------+-----------+
| page_num | start_key                                 | end_key                                   | page_size |
+----------+-------------------------------------------+-------------------------------------------+-----------+
|        1 | (0000000000000268996,0000000000092104804) | (0000000000140982742,0000000000374645100) |     10000 |
|        2 | (0000000000140982742,0000000000456757551) | (0000000000287195082,0000000004053200550) |     10000 |
|        3 | (0000000000287196791,0000000000191962769) | (0000000000434010216,0000000000237646714) |     10000 |
|        4 | (0000000000434010216,0000000000375066168) | (0000000000578893327,0000000002167504460) |     10000 |
|        5 | (0000000000578893327,0000000002457322286) | (0000000000718287668,0000000001502744628) |     10000 |
...
|       29 | (0000000004002523918,0000000000902930986) | (0000000004147203315,0000000004090920746) |     10000 |
|       30 | (0000000004147421329,0000000000319181561) | (0000000004294004213,0000000003586311166) |      9972 |
+----------+-------------------------------------------+-------------------------------------------+-----------+
30 rows in set (0.28 sec)

ページ 1 のすべての評価レコードを削除するには、上記の結果のstart_keyとend_keyページ 1 の値に置き換えます。

SELECT *
FROM ratings
WHERE (
        268996 = 140982742
        AND book_id = 268996
        AND user_id >= 92104804
        AND user_id <= 374645100
    )
    OR (
        268996 != 140982742
        AND (
            (
                book_id > 268996
                AND book_id < 140982742
            )
            OR (
                book_id = 268996
                AND user_id >= 92104804
            )
            OR (
                book_id = 140982742
                AND user_id <= 374645100
            )
        )
    )
ORDER BY book_id, user_id;

ヘルプが必要ですか?

不和またはスラック、あるいはサポートチケットを送信するについてコミュニティに質問してください。

結果をページ付けする

クエリ結果をページ分割する

単一フィールドの主キーテーブルのページングバッチ

複合主キーテーブルのページングバッチ

非クラスター化インデックステーブル

クラスター化インデックステーブル

ヘルプが必要ですか?

このページは役に立ちましたか？