mysql分页优化

简单分页

最开始，我们使用的分页查询SQL看起来像是这样的

select * from table order by id desc limit (pageNum -1) * pageSize ,  pageSize

在数据行不是很大的情况下 分页的查询时间还是在可以接受的。但是当我们的数据量越来越多，达到百万级的时候查询到中后一些的数据就比较慢了。于是就开始进行分页的查询优化

这里我准备了一个数据行2864338的表，表结构（activity的表，数据生成比较麻烦就直接使用了，把ID_字段的类型改成了bigint 避免比较的时候出现类型转换）

CREATE TABLE `act_hi_detail` (
  `ID_` bigint(22) NOT NULL,
  `TYPE_` varchar(255) COLLATE utf8_bin NOT NULL,
  `PROC_INST_ID_` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `EXECUTION_ID_` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `TASK_ID_` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `ACT_INST_ID_` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `NAME_` varchar(255) COLLATE utf8_bin NOT NULL,
  `VAR_TYPE_` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `REV_` int(11) DEFAULT NULL,
  `TIME_` datetime(3) NOT NULL,
  `BYTEARRAY_ID_` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `DOUBLE_` double DEFAULT NULL,
  `LONG_` bigint(20) DEFAULT NULL,
  `TEXT_` varchar(4000) COLLATE utf8_bin DEFAULT NULL,
  `TEXT2_` varchar(4000) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`ID_`),
  KEY `ACT_IDX_HI_DETAIL_PROC_INST` (`PROC_INST_ID_`),
  KEY `ACT_IDX_HI_DETAIL_ACT_INST` (`ACT_INST_ID_`),
  KEY `ACT_IDX_HI_DETAIL_TIME` (`TIME_`),
  KEY `ACT_IDX_HI_DETAIL_NAME` (`NAME_`),
  KEY `ACT_IDX_HI_DETAIL_TASK_ID` (`TASK_ID_`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

执行一下count(*)

子查询优化方案

这时候SQL看起来应该是这样的

select * from table where id >= (select id from table order by id limit (pageNum -1) * pageSize , 1) order by id limit pageSize

然后我们按照最开始的分页查询一下200W出的数据

可以看见 在数据量200W出的分页查询时间为2.62s。接下来使用子查询方式优化分页

结果真是非常amazing啊，只用了0.49秒。
这个效率提升还是非常明显的，这里可能有人说了 select * 可能有一定的影响，那在来一次select 一些字段作为比较



可以发现，使用子查询优化的分页时间都是比较短的 在接近300W的数量时都是秒级查询

jion查询优化方案

此方案来源https://www.cnblogs.com/scotth/p/7995856.html

select l.* from table as l inner join (select id from table order by id limit (pageNum-1)*pageSize , pageSize)
on l.id= r.id order by id

可以看到 查询速度也很快。

结论

在能预估数据量快速上升的表做分页的时候，最好使用优化后的分页查询。文中的两种方案在此次分析中均有不错的表现。