結果をページ分けする

大きなクエリ結果をページングするには、「ページ区切り」方式で目的の部分を取得できます。

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

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ステートメントを使用してクエリ結果を並べ替えることをお勧めします。

  • SQL
  • Java

たとえば、 書店アプリケーションのユーザーが最新の出版済み書籍をページ分けして表示できるようにするには、 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; }

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

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

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

  • SQL
  • Java

まず、データを主キーでソートし、ウィンドウ関数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_keyend_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関数を使用して複数の列の値をキーとして連結し、ウィンドウ関数を使用してページング情報を照会できます。

この時点ではキーは文字列であり、 minmax集計関数を介してスライス内の正しいstart_keyend_keyを取得するには、文字列の長さが常に同じであることを確認する必要があることに注意してください。文字列連結のフィールドの長さが固定されていない場合は、 LPAD関数を使用してパディングできます。

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

以下の文を使用してメタ情報テーブルを作成します。 bigint種類のbook_iduser_idを連結したキーは同じ長さに変換できないため、 LPAD関数を使用して、 bigintの最大ビット 19 に合わせて長さを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_keyend_keyをページ 1 の値に置き換えます。

SELECT * FROM ratings WHERE (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;

助けが必要?

TiDB コミュニティ 、またはサポートチケットを作成するについて質問します。

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