# 分页查询
当查询结果数据量较大时，我们往往希望以“分页”的方式返回所需要的部分。
## 对查询结果进行分页
在 TiDB 当中，我们可以利用 `LIMIT` 语句来实现分页功能，常规的分页语句写法如下所示：
```sql
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` 语句指定查询结果的排序方式。
例如，在 Bookshop 应用当中，我们希望将最新书籍列表以分页的形式返回给用户，通过 `LIMIT 0, 10` 语句，我们便可以得到列表第 1 页的书籍信息，每页中最多有 10 条记录。获取第 2 页信息，则改成可以改成 `LIMIT 10, 10`，如此类推。
```sql
SELECT *
FROM books
ORDER BY published_at DESC
LIMIT 0, 10;
```
在应用程序开发当中，后端程序从前端接收到的参数页码 `page_number` 和每页的数据条数 `page_size`，而不是起始记录数 `offset`，因此在进行数据库查询前我们需要对其进行一些转换。
```java
public List 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 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()` 为每一行数据生成行号，接着调用聚合函数按照设置好的页面大小对行号进行分组，最终计算出每页的最小值和最大值。
```sql
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 *, 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 页上的所有书籍的基本信息，我们可以将上表第 1 页所对应的 `start_key` 和 `end_key` 填入 SQL 语句当中。
```sql
DELETE FROM books
WHERE
id BETWEEN 268996 AND 213168525
ORDER BY id;
```
在 Java 语言当中，我们可以定义一个 `PageMeta` 类来存储分页元信息。
```java
public class PageMeta
{
private Long pageNum;
private K startKey;
private K endKey;
private Long pageSize;
// Skip the getters and setters.
}
```
我们定义一个 `getPageMetaList()` 方法获取到分页元信息列表，然后定义一个可以根据页面元信息批量删除数据的方法 `deleteBooksByPageMeta()`。
```java
public class BookDAO {
public List> getPageMetaList() throws SQLException {
List> 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 *, row_number () OVER (ORDER BY id) AS row_num
FROM books
) t
GROUP BY page_num
ORDER BY page_num;
""");
while (rs.next()) {
PageMeta 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 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 页的数据，我们可以这样写：
```java
List> pageMetaList = bookDAO.getPageMetaList();
if (pageMetaList.size() > 0) {
bookDAO.deleteBooksByPageMeta(pageMetaList.get(0));
}
```
如果我们希望通过分页分批地删除所有书籍数据，可以这样写：
```java
List> pageMetaList = bookDAO.getPageMetaList();
pageMetaList.forEach((pageMeta) -> {
try {
bookDAO.deleteBooksByPageMeta(pageMeta);
} catch (SQLException e) {
e.printStackTrace();
}
});
```