Mysql分页查询性能分析

前言

【PS：原文手打，转载说明出处，博客园】

　　看过一堆的百度，最终还是自己做了一次实验，本文基于Mysql5.7.17版本，Mysql引擎为InnoDB，编码为utf8，排序规则为utf8_general_ci

网络搜索查询语句

　　Sql分页查询基于LIMIT，如下：

select * from SysTestUser  order by Id LIMIT 10;
select * from SysTestUser  order by Id LIMIT 1000,10;

　　网络上各说风云，有下面几种写法说速度快

　　1：如下子查询

 select * from SysTestUser where Id in( select Id from  (select Id from SysTestUser  order by Id LIMIT 1000,10) t );

　　2：如下图

select Id from SysTestUser  order by Id LIMIT 1000,10;
select * from SysTestUser where id in(1000,1001,1002,1003,1004,1005,1006,1007,1008,1009) order by Id ;

　　3：如下

select * from SysTestUser where id>=(select id from SysTestUser  order by id limit 1000,1) limit 10;

实践出真知

　　我们新建一张表（PS：不要在意每个字段类型，这张表只是测试使用。）

-- ----------------------------
-- Table structure for SysTestUser
-- ----------------------------
DROP TABLE IF EXISTS `SysTestUser`;
CREATE TABLE `SysTestUser` (
  `Id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Id',
  `UserName` varchar(50) NOT NULL COMMENT '姓名',
  `Sex` tinyint(4) NOT NULL COMMENT '性别',
  `Height` decimal(3,2) NOT NULL COMMENT '身高',
  `Age` smallint(6) NOT NULL COMMENT '年龄',
  `Brithday` datetime NOT NULL COMMENT '生日',
  `CreationTime` datetime NOT NULL COMMENT '创建时间',
  `LastModificationTime` datetime NOT NULL COMMENT '修改时间',
  `IsDeleted` bit(1) NOT NULL COMMENT '是否软删除',
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=35 DEFAULT CHARSET=utf8 COMMENT='测试用户表';

　　然后模拟了5038960条数据进库，分别进行查询，如下结果

　　无条件查询

 select * from SysTestUser  order by Id LIMIT 780000,10;
80万=0.325
140万=0.579
500万=2.0

 select * from SysTestUser where Id in( select Id from 
(select Id from SysTestUser  order by Id LIMIT 780000,10) t );
80万=0.65
140万=1.179
500万=4.0

select Id from SysTestUser  order by Id LIMIT 780000,10;
select * from SysTestUser where id in(780009,780010,780011,780012,780013,780014,780015,780016,780017,780018) order by Id ;
80万=0.225
14万=0.39
500万=1.368s

select * from SysTestUser where id>=(select id from SysTestUser  order by id limit 780000,1) limit 10;
80万=0.225
140万=0.397
500万=1.363

　　有条件查询（PS，字段没添加任何索引，由于批量新增，UserName=string+Id来着）

select * from SysTestUser where UserName like 'string%' and age=30 order by Id LIMIT 780000,10;
80万=0.4
140万=0.71
500万=2.5


 select * from SysTestUser where Id in( select Id from 
(select Id from SysTestUser where UserName like 'string%' and age=30 order by Id LIMIT 780000,10) t );
80万=0.33
140万=0.59
500万=2.06


select Id from SysTestUser where UserName like 'string%' and age=30 order by Id LIMIT 780000,10;
select * from SysTestUser where id in(780009,780010,780011,780012,780013,780014,780015,780016,780017,780018) order by Id ;
80万=0.33
140万=0.59
500万=2.07

select * from SysTestUser where id>=(select id from SysTestUser where UserName like 'string%' and age=30 order by id limit 780000,1) limit 10;
80万=0.33
140万=0.59
500万=2.046

测试结论

　　百万级数据分页的情况下，以上方式都能够忍受，当数据量达到五百万的时候，需要的查询时间直接让人无法忍受了

处理方式

　　百万级以内的分页，无所谓写法，随便写没啥区别，百万级以上的，又需要查询分页查询的，可以使用以下方式。

select * from SysTestUser where ID>5000000 limit 10
select * from SysTestUser where ID>5000000 and UserName like 'string%' limit 10

　　至于ID哪里拿，怎么拿我给的建议是，第一页分页不管以何种方式写都很快，此时数据显示到前端，当点击下一页时，带上maxId or minID，这个看排序情况

　　这个时候的上面的ID是大于还是小于也看排序情况，我就不一一的写出来了。

结尾

　　大表分页是一件很头疼的事情，在实际业务场景能避免尽量避免，如果避免不了的情况下，尽量借助扩展表，缓存的形式来处理

　　例如：查询总条数，这个是避免不了的消耗的情况，完全可以写个计数器

　　在例如查询用户订单列表，完全可以使用Redis缓存用户订单信息等

　　不要用小刀来杀牛，杀不动