# 千万记录，如何快速分页

首先先来看下面几个语句的查询时间

select id,name from t_test limit 100,100;
select id,name from t_test limit 10000,100;
select id,name from t_test limit 1000000,100;
select id,name from t_test limit 5000000,100;

1
2
3
4

那么先创建测试数据表和测试数据

创建测试表

create table t_test
(
    id     int unsigned primary key not null,
    `name` varchar(200)             not null
)

1
2
3
4
5

插入测试数据

 @Test
    public void fun2() {
        try (BufferedWriter writer = Files.newBufferedWriter(Paths.get("/Users/mrcode/Desktop/test.sql"))) {
            for (int i = 1; i <= 10000000; i++) {
               // writer.append("insert into t_test(id,`name`) values (" + i + ",'" + UUID.randomUUID().toString() + "');\n");
              // 这是利用工具导入，只输出列值
              writer.append(i + "," + UUID.randomUUID().toString() + "\n");
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

1
2
3
4
5
6
7
8
9
10
11
12
13

编写了一个产生 1000 万行的随机数据，通过工具导入；笔者测试过以下两种方式：

生成 insert into 语句，通过 navicat 执行 sql 文件导入

耗时 30 分钟，结果还把数据库搞死了。

原因是：使用这种方式是带事务执行的，特别慢
生成数据，用 DataGrip 导入

只用了 4 分钟。这种方式是不带事务的带入。

看看查询时间信息

neti> select id,name from t_test limit 100,100
[2020-06-07 20:09:52] 100 rows retrieved starting from 1 in 37 ms (execution: 9 ms, fetching: 28 ms)
neti> select id,name from t_test limit 10000,100
[2020-06-07 20:10:06] 100 rows retrieved starting from 1 in 48 ms (execution: 15 ms, fetching: 33 ms)
neti> select id,name from t_test limit 1000000,100
[2020-06-07 20:10:15] 100 rows retrieved starting from 1 in 362 ms (execution: 336 ms, fetching: 26 ms)
neti> select id,name from t_test limit 5000000,100
[2020-06-07 20:10:21] 100 rows retrieved starting from 1 in 1 s 244 ms (execution: 1 s 220 ms, fetching: 24 ms)
neti> select id,name from t_test limit 9000000,100
[2020-06-07 20:11:44] 100 rows retrieved starting from 1 in 2 s 247 ms (execution: 2 s 221 ms, fetching: 26 ms)

1
2
3
4
5
6
7
8
9
10

越往后，时间越长

# 优化办法

利用主键索引来加速分页查询；（前提：主键连续）

写法一：

neti> select id,name from t_test where id > 9000000 limit 100
[2020-06-07 20:13:52] 100 rows retrieved starting from 1 in 60 ms (execution: 26 ms, fetching: 34 ms)

1
2

可以看到值用了 60 毫秒；

写法二：

neti> select id,name from t_test where id > 9000000 and id <= 9000000+100
[2020-06-07 20:15:33] 100 rows retrieved starting from 1 in 42 ms (execution: 10 ms, fetching: 32 ms)

1
2

这个耗时更少。

如果主键值不连续，怎么分页？
- 使用逻辑删除，不会造成主键不连续
- 利用主键索引加速，再做表连接查询
下面是这个方案的写法
```
neti> select t.id, t.name
      from t_test t
               join(select id from t_test limit 9000000,100) tmp on t.id = tmp.id
[2020-06-07 20:24:02] 100 rows retrieved starting from 1 in 1 s 956 ms (execution: 1 s 935 ms, fetching: 21 ms)
```
1
2
3
4
5
这个语句是想用子查询先利用 主键索引 过滤出 100 条数据，再关联出这 100 条的数据。不过这个语句在执行计划里面并不是我们希望的这样去查询的。

虽然是这样，但是比 limit 还是要快上几百毫秒
其他解决办法

业务上限定不可以查询早期数据，或则不给出直接跳到多少条之后的入口

← 逻辑删除还是物理删除读多写少和读多写多 →