SQL分页查询情景中，大值offset的优化探索

首先明确 limit 和 offset 的使用方式：

• limit y 表示: 读取 y 条记录
• limit x, y 表示: 跳过 x 条记录，读取 y 条记录
• limit y offset x 表示: 跳过 x 条记录，读取 y 条记录

例子：

• 从第1000个开始，获取10条记录

 select * from test limit 1000, 10; 
 select * from test limit 10 offset 1000;  

• 从第5个开始，获取1条记录

 select * from test limit 5, 1; 
 select * from test limit 1 offset 5;  

使用子查询优化大值offset

建表语句

-- ----------------------------
-- Table structure for test
-- ----------------------------
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test`  (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `name` varchar(200) ,
 `pwd` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
 PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

使用Mybatis批量插入3484000条记录

INSERT INTO `uexam`.`test`(`id`, `name`, `pwd`) VALUES (1, 'test999', 'pwd999');

-- 耗时 0.970s
select * from test limit 1 offset 3000000  

-- 耗时 0.78s
SELECT * FROM test WHERE id >= ( 
  SELECT id FROM test ORDER BY id 
  LIMIT 3000000,1
) LIMIT 1  

使用Mybatis批量插入13549000条记录

-- 耗时 4.282s
select * from test limit 1 offset 13000000

-- 耗时 3.480s
SELECT * FROM test WHERE id >= ( 
 SELECT id FROM test ORDER BY id 
 LIMIT 13000000,1
) LIMIT 1  

总结
在有序主键上进行子查询优化可以提高80%左右的查询效率