当数据量大时，该如何实现分页功能？

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

---

前言

随着系统使用的时间变长，数据库里的数据也越来越多，每次分页查询数据的时间也随之变长，这时候应该怎么做，才能让查询速度更快呢？

---

提示：以下是本篇文章正文内容，下面案例可供参考

一、准备数据？

1.创建表

CREATE TABLE `user_operation_log`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `ip` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `op_data` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `attr1` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `attr2` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `attr3` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `attr4` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `attr5` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `attr6` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `attr7` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `attr8` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `attr9` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `attr10` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `attr11` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `attr12` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

2.创建数据脚本

创建一千万条数据，建议使用数据库脚本插入（接近一小时），代码插入的话太慢了（一天都不一定可以插入完成）。

可以根据自身配置选择插入的数据数据，需要修改这一行代码

WHILE i<=10000000 DO

采用批量插入效率会快很多。

DELIMITER ;;
CREATE PROCEDURE batch_insert_log()
BEGIN
  DECLARE i INT DEFAULT 1;
  DECLARE userId INT DEFAULT 10000000;
 set @execSql = 'INSERT INTO `test`.`user_operation_log`(`user_id`, `ip`, `op_data`, `attr1`, `attr2`, `attr3`, `attr4`, `attr5`, `attr6`, `attr7`, `attr8`, `attr9`, `attr10`, `attr11`, `attr12`) VALUES';
 set @execData = '';
  WHILE i<=10000000 DO
   set @attr = "'测试很长很长很长很长很长很长很长很长很长很长很长很长很长很长很长很长很长的属性'";
  set @execData = concat(@execData, "(", userId + i, ", '10.0.69.175', '用户登录操作'", ",", @attr, ",", @attr, ",", @attr, ",", @attr, ",", @attr, ",", @attr, ",", @attr, ",", @attr, ",", @attr, ",", @attr, ",", @attr, ",", @attr, ")");
  if i % 1000 = 0
  then
     set @stmtSql = concat(@execSql, @execData,";");
    prepare stmt from @stmtSql;
    execute stmt;
    DEALLOCATE prepare stmt;
    commit;
    set @execData = "";
   else
     set @execData = concat(@execData, ",");
   end if;
  SET i=i+1;
  END WHILE;

END;;
DELIMITER ;

执行完这条脚本之后到 函数 里找到batch_insert_log执行

插入一千万条数据，执行了41分钟

二、开始测试

由于配置的不同，查询的时间也会有不同，所以以下数据仅供参考

1.普通分页查询

SELECT * FROM `user_operation_log` LIMIT 10000, 10

查询3次时间分别为：

• 0.031s
• 0.029s
• 0.028s

2.相同偏移量，不同数据量

SELECT * FROM `user_operation_log` LIMIT 10000, 10
SELECT * FROM `user_operation_log` LIMIT 10000, 100
SELECT * FROM `user_operation_log` LIMIT 10000, 1000
SELECT * FROM `user_operation_log` LIMIT 10000, 10000
SELECT * FROM `user_operation_log` LIMIT 10000, 100000
SELECT * FROM `user_operation_log` LIMIT 10000, 1000000

查询时间如下：

数量	第一次	第二次	第三次
10条	0.028s	0.027s	0.031s
100条	0.031s	0.030s	0.032s
1000条	0.043s	0.036s	0.039s
10000条	0.088s	0.084s	0.090s
100000条	0.846s	0.873s	0.894s
1000000条	5.811s	6.136s	6.126s

从结果来看：数据量越大，花费时间越长

3.相同数据量，不同偏移量

SELECT * FROM `user_operation_log` LIMIT 100, 100
SELECT * FROM `user_operation_log` LIMIT 1000, 100
SELECT * FROM `user_operation_log` LIMIT 10000, 100
SELECT * FROM `user_operation_log` LIMIT 100000, 100
SELECT * FROM `user_operation_log` LIMIT 1000000, 100

查询结果如下：

数量	第一次	第二次	第三次
100条	0.023s	0.024s	0.025s
1000条	0.022s	0.027s	0.026s
10000条	0.031s	0.029s	0.032s
100000条	0.400s	0.408s	0.382s
1000000条	2.360s	2.339s	2.343s

从结果来看：偏移量越大，花费时间越长

三、如何优化

针对两个问题，偏移量大以及数据量大，我们分别着手优化

1.优化偏移量大问题

①采用子查询方式

我们可以先定位偏移位置的id，然后在查询数据

sql1

SELECT * FROM `user_operation_log` LIMIT 1000000, 10

sql2

SELECT id FROM `user_operation_log` LIMIT 1000000, 1

sql3

SELECT * FROM `user_operation_log` WHERE id >= (SELECT id FROM `user_operation_log` LIMIT 1000000, 1) LIMIT 10

查询结果如下：

sql	花费时间
sql1	2.348s
sql2（无索引）	2.327s
sql3（无索引）	2.310s
sql2（有索引）	0.124s
sql3（有索引）	0.122s

从上面得出结论：

• 第一条花费时间最大，第三条比第一条稍微好点
• 子查询使用索引速度更快

缺点：只适用于id递增的情况

id非递增的情况可以使用以下写法，但这种缺点是分页查询只能放在子查询里面

SELECT * FROM `user_operation_log` WHERE id IN (SELECT t.id FROM (SELECT id FROM `user_operation_log` LIMIT 1000000, 10) AS t)

②采用id限定方式

这种方法要求更高些，id必须是连续递增，而且还得计算id的范围，然后使用between，sql如下

sql1

SELECT * FROM `user_operation_log` WHERE id between 1000000 AND 1000100 LIMIT 100

sql2

SELECT * FROM `user_operation_log` WHERE id >= 1000000 LIMIT 100

查询结果如下：

sql	花费时间
sql1	0.022s
sql2	0.025s

从结果可以看出这种方式非常快

注意：这里的 LIMIT 是限制了条数，没有采用偏移量

2.优化数据量大问题

返回结果的数据量也会直接影响速度

sql1

SELECT * FROM `user_operation_log` LIMIT 1, 1000000

sql2

SELECT id FROM `user_operation_log` LIMIT 1, 1000000

sql3

SELECT id, user_id, ip, op_data, attr1, attr2, attr3, attr4, attr5, attr6, attr7, attr8, attr9, attr10, attr11, attr12 FROM `user_operation_log` LIMIT 1, 1000000

查询结果如下：

sql	花费时间
sql1	11.983s
sql2	0.352s
sql3	10.535s

从结果看出来减少不需要的列，查询的效率也可以得到明显的提升

第一条和第三条花费时间差不多，为什么不直接使用*？

③为什么不推荐使用select *

主要两点：

• 用“select *” 数据库要解释更多的对象、字段、权限、属性等相关内容，在sql语句复杂，硬解析较多的情况下，会对数据库造成负担。
• 增大网络开销，*又是会误带上如log、IconMD5之类的无用且大文本字段，数据传输size会几何增长。特别是mysql不在同一台机器，这种开销非常明显！