java开发手册 之 利用子查询优化超多分页场景

解读 java开发手册

---

原话：MySQL 并不是跳过 offset 行，而是取 offset+N 行，然后返回放弃前 offset 行，返回 N 行

准备工作

mysql 8.0.26

网上随便找的一张表

CREATE TABLE `cs_area` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '唯一标识',
  `area_code` int unsigned NOT NULL COMMENT '区域编码',
  `city_code` int DEFAULT NULL COMMENT '父级市代码',
  `area_name` varchar(40) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '市名称',
  `short_name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '简称',
  `lng` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '经度',
  `lat` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '纬度',
  `sort` int DEFAULT NULL COMMENT '排序',
  `memo` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '备注',
  `data_state` int DEFAULT NULL COMMENT '状态',
  `tenant_code` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '租户ID',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后更新时间',
  `deleted` tinyint(1) DEFAULT '0' COMMENT '是否删除',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `inx_id_tenant_code` (`area_code`,`tenant_code`) USING BTREE,
  KEY `inx_city_code` (`city_code`) USING BTREE
) ENGINE=InnoDB

插入880万条记录

验证

查询第2页，100条数据，例：

  SELECT * FROM test.cs_area a limit 100,100;

COST/Fetch 0.0013 sec / 0.000052 sec 

现在查询第5万页数据，例：

  SELECT * FROM test.cs_area a limit 5000000,100;

COST/Fetch   3.894 sec / 0.000046 sec

接近4s，不出意外，用户会反馈问题了。

优化

先查询出指定数据的ID,然后关联出完整数据。From子表，例：

SELECT t1.* FROM test.cs_area  as t1 , (select id from test.cs_area LIMIT 5000000,100) as t2
 where t1.id = t2.id

COST/Fetch  0.650 sec / 0.000031 sec
0.6秒，只改SQL的小代价下，BUG修复。

延伸

我用IN查询子表呢？例：

 SELECT a.* FROM test.cs_area a 
where a.id in (select id from test.cs_area LIMIT 5000000,100)

Error Code: 1235. This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

不支持。

那我再套一张表呢，例：

 SELECT a.* FROM test.cs_area a 
where a.id in (select b.id from (select id from test.cs_area LIMIT 5000000,100) b )

SQL能执行，但  COST/Fetch   9.217 sec / 0.000038 sec

什么？where 使用子查询不起作用了，继续排查，上“执行计划”

(执行计划相关知识，请自行查阅)

 可以看出

#1，第一行，type=ALL, rows=7992924 ，居然走了全表扫描。

这里涉及到另一知识点，名名是主键，mysql不走索引，估计是类型不匹配。把子查询类型转换为integer

(mysql不走索引，请自行查阅)

例：

SELECT a.* FROM test.cs_area a 
where a.id in (select CAST( b.id as UNSIGNED INTEGER) from (select c.id from test.cs_area c  LIMIT 5000000,100) b ) 

COST/Fetch    0.671 sec / 0.000037 sec

他的执行计划

COST 和之前FROM使用子表查询时间差不多。说明WHERE子表和FROM子表，都可以达到优化效果。

结论

推荐使用FROM子表，少套一层，少写一次类型转换（字符串ID可能不需要）

话说，mysql为什么要转类型？