mysql 分页查询分析_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 forSysTestUser-- ----------------------------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='测试用户表';

View Code

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

无条件查询

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

80万=0.325140万=0.579500万=2.0

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

80万=0.65140万=1.179500万=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.22514万=0.39500万=1.368sselect * from SysTestUser where id>=(select id from SysTestUser order by id limit 780000,1) limit 10;

80万=0.225140万=0.397500万=1.363

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

select * from SysTestUser where UserName like 'string%' and age=30 order by Id LIMIT 780000,10;

80万=0.4140万=0.71500万=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.33140万=0.59500万=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.33140万=0.59500万=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.33140万=0.59500万=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缓存用户订单信息等

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