分页查询

分页查询

当查询结果数据量较大时，往往希望以“分页”的方式返回所需要的部分。

对查询结果进行分页

在 TiDB 当中，可以利用 LIMIT 语句来实现分页功能，常规的分页语句写法如下所示：

1. 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

例如，在 Bookshop 应用当中，希望将最新书籍列表以分页的形式返回给用户。通过 LIMIT 0, 10 语句，便可以得到列表第 1 页的书籍信息，每页中最多有 10 条记录。获取第 2 页信息，则改成可以改成 LIMIT 10, 10，如此类推。

1. SELECT *
2. FROM books
3. ORDER BY published_at DESC
4. LIMIT 0, 10;

在使用 Java 开发应用程序时，后端程序从前端接收到的参数页码 page_number 和每页的数据条数 page_size，而不是起始记录数 offset，因此在进行数据库查询前需要对其进行一些转换。

1. public List<Book> getLatestBooksPage(Long pageNumber, Long pageSize) throws SQLException {
2. pageNumber = pageNumber < 1L ? 1L : pageNumber;
3. pageSize = pageSize < 10L ? 10L : pageSize;
4. Long offset = (pageNumber - 1) * pageSize;
5. Long limit = pageSize;
6. List<Book> books = new ArrayList<>();
7. try (Connection conn = ds.getConnection()) {
8. PreparedStatement stmt = conn.prepareStatement("""
9. SELECT id, title, published_at
10. FROM books
11. ORDER BY published_at DESC
12. LIMIT ?, ?;
13. """);
14. stmt.setLong(1, offset);
15. stmt.setLong(2, limit);
16. ResultSet rs = stmt.executeQuery();
17. while (rs.next()) {
18. Book book = new Book();
19. book.setId(rs.getLong("id"));
20. book.setTitle(rs.getString("title"));
21. book.setPublishedAt(rs.getDate("published_at"));
22. books.add(book);
23. }
24. }
25. return books;
26. }

单字段主键表的分页批处理

常规的分页更新 SQL 一般使用主键或者唯一索引进行排序，再配合 LIMIT 语法中的 offset，按固定行数拆分页面。然后把页面包装进独立的事务中，从而实现灵活的分页更新。但是，劣势也很明显：由于需要对主键或者唯一索引进行排序，越靠后的页面参与排序的行数就会越多，尤其当批量处理涉及的数据体量较大时，可能会占用过多计算资源。

下面将介绍一种更为高效的分页批处理方案：

SQL
Java

使用 SQL 实现分页批处理，可以按照如下步骤进行：

首先将数据按照主键排序，然后调用窗口函数 row_number() 为每一行数据生成行号，接着调用聚合函数按照设置好的页面大小对行号进行分组，最终计算出每页的最小值和最大值。

1. SELECT
2. floor((t.row_num - 1) / 1000) + 1 AS page_num,
3. min(t.id) AS start_key,
4. max(t.id) AS end_key,
5. count(*) AS page_size
6. FROM (
7. SELECT id, row_number() OVER (ORDER BY id) AS row_num
8. FROM books
9. ) t
10. GROUP BY page_num
11. ORDER BY page_num;

查询结果如下：

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

接下来，只需要使用 WHERE id BETWEEN start_key AND end_key 语句查询每个分片的数据即可。修改数据时，也可以借助上面计算好的分片信息，实现高效的数据更新。

例如，假如想要删除第 1 页上的所有书籍的基本信息，可以将上表第 1 页所对应的 start_key 和 end_key 填入 SQL 语句当中。

1. DELETE FROM books
2. WHERE
3. id BETWEEN 268996 AND 213168525
4. ORDER BY id;

在 Java 语言当中，可以定义一个 PageMeta 类来存储分页元信息。

1. public class PageMeta<K> {
2. private Long pageNum;
3. private K startKey;
4. private K endKey;
5. private Long pageSize;

7. // Skip the getters and setters.

9. }

定义一个 getPageMetaList() 方法获取到分页元信息列表，然后定义一个可以根据页面元信息批量删除数据的方法 deleteBooksByPageMeta()。

1. public class BookDAO {
2. public List<PageMeta<Long>> getPageMetaList() throws SQLException {
3. List<PageMeta<Long>> pageMetaList = new ArrayList<>();
4. try (Connection conn = ds.getConnection()) {
5. Statement stmt = conn.createStatement();
6. ResultSet rs = stmt.executeQuery("""
7. SELECT
8. floor((t.row_num - 1) / 1000) + 1 AS page_num,
9. min(t.id) AS start_key,
10. max(t.id) AS end_key,
11. count(*) AS page_size
12. FROM (
13. SELECT id, row_number() OVER (ORDER BY id) AS row_num
14. FROM books
15. ) t
16. GROUP BY page_num
17. ORDER BY page_num;
18. """);
19. while (rs.next()) {
20. PageMeta<Long> pageMeta = new PageMeta<>();
21. pageMeta.setPageNum(rs.getLong("page_num"));
22. pageMeta.setStartKey(rs.getLong("start_key"));
23. pageMeta.setEndKey(rs.getLong("end_key"));
24. pageMeta.setPageSize(rs.getLong("page_size"));
25. pageMetaList.add(pageMeta);
26. }
27. }
28. return pageMetaList;
29. }

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

如果想要删除第 1 页的数据，可以这样写：

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

如果希望通过分页分批地删除所有书籍数据，可以这样写：

1. List<PageMeta<Long>> pageMetaList = bookDAO.getPageMetaList();
2. pageMetaList.forEach((pageMeta) -> {
3. try {
4. bookDAO.deleteBooksByPageMeta(pageMeta);
5. } catch (SQLException e) {
6. e.printStackTrace();
7. }
8. });

改进方案由于规避了频繁的数据排序操作造成的性能损耗，显著改善了批量处理的效率。

复合主键表的分页批处理

非聚簇索引表

对于非聚簇索引表（又被称为“非索引组织表”）而言，可以使用隐藏字段 _tidb_rowid 作为分页的 key，分页的方法与单列主键表中所介绍的方法相同。

小贴士

你可以通过 SHOW CREATE TABLE users; 语句查看表主键是否使用了聚簇索引。

例如：

1. SELECT
2. floor((t.row_num - 1) / 1000) + 1 AS page_num,
3. min(t._tidb_rowid) AS start_key,
4. max(t._tidb_rowid) AS end_key,
5. count(*) AS page_size
6. FROM (
7. SELECT _tidb_rowid, row_number() OVER (ORDER BY _tidb_rowid) AS row_num
8. FROM users
9. ) t
10. GROUP BY page_num
11. ORDER BY page_num;

查询结果如下：

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

聚簇索引表

对于聚簇索引表（又被称为“索引组织表”），可以利用 concat 函数将多个列的值连接起来作为一个 key，然后使用窗口函数获取分页信息。

需要注意的是，这时候 key 是一个字符串，你必须确保这个字符串长度总是相等的，才能够通过 min 和 max 聚合函数得到分页内正确的 start_key 和 end_key。如果进行字符串连接的字段长度不固定，你可以通过 LPAD 函数进行补全。

例如，想要对 ratings 表里的数据进行分页批处理。

先可以通过下面的 SQL 语句来在制造元信息表。因为组成 key 的 book_id 列和 user_id 列都是 bigint 类型，转换为字符串是并不是等宽的，因此需要根据 bigint 类型的最大位数 19，使用 LPAD 函数在长度不够时用 0 补齐。

1. SELECT
2. floor((t1.row_num - 1) / 10000) + 1 AS page_num,
3. min(mvalue) AS start_key,
4. max(mvalue) AS end_key,
5. count(*) AS page_size
6. FROM (
7. SELECT
8. concat('(', LPAD(book_id, 19, 0), ',', LPAD(user_id, 19, 0), ')') AS mvalue,
9. row_number() OVER (ORDER BY book_id, user_id) AS row_num
10. FROM ratings
11. ) t1
12. GROUP BY page_num
13. ORDER BY page_num;

注意

该 SQL 会以全表扫描 (TableFullScan) 方式执行，当数据量较大时，查询速度会变慢，此时可以使用 TiFlash 进行加速。

查询结果如下：

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

假如想要删除第 1 页上的所有评分记录，可以将上表第 1 页所对应的 start_key 和 end_key 填入 SQL 语句当中。

1. SELECT * FROM ratings
2. WHERE
3. (book_id > 268996 AND book_id < 140982742)
4. OR (
5. book_id = 268996 AND user_id >= 92104804
6. )
7. OR (
8. book_id = 140982742 AND user_id <= 374645100
9. )
10. ORDER BY book_id, user_id;

查询结果分页

分页查询

对查询结果进行分页

单字段主键表的分页批处理

复合主键表的分页批处理

非聚簇索引表

聚簇索引表