首先明确 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%左右的查询效率