Mysql深分页优化

mysql的大分页在实际生产中应该使用的场景不多,很少有人查看几十页以后的数据
但是如果真的需要大分页可以参考阿里开发手册的mysql分页.
mysql的分页一般使用的是 limit [offset] ,[N] offset和m是变量, offset是从第多少行开始,N是需要获取数据的行数
MySQL 并不是跳过 offset 行，而是取 offset+N 行，然后返回放弃前 offset 行，返回 N 行，那当
offset 特别大的时候，效率就非常的低下，要么控制返回的总页数，要么对超过特定阈值的页数进行 SQL改写。
首先是创建大数据(从网上拷贝的存储过程,时间太久忘了网址)
mysql 版本5.7.32
创建表

create table `depttest` (
`id` int unsigned primary key auto_increment,
`deptno` mediumint unsigned not null default 0,
`dname` varchar(20) not null default "",
`ioc` varchar(13) not null default ""
) engine = innodb default charset = gbk;
create table `emp` (
`id` int unsigned primary key auto_increment,
`empno` mediumint unsigned not null default 0,
`ename` varchar(20) not null default "",
`job` varchar(9) not null default "",
`mgr` mediumint unsigned not null default 0,
`hiredate` date not null,
`sal` decimal(7,2) not null,
`comm` decimal(7,2) not null,
`deptno` mediumint unsigned not null default 0
) engine = innodb default charset = gbk;

set global log_bin_trust_function_creators =1;
show variables like 'log_bin_trust_function_creators';
创建存储过程

delimiter $$
create function rand_string(n int) returns varchar(255)
begin
declare chars_str varchar(100) default 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
declare return_str varchar(255) default '';
declare i int default 0;
while i<n do
set return_str =concat(return_str ,substring(chars_str , floor(1+rand()*52),1));
set i=i+1;
end while;
return return_str;
end$$

delimiter $$
create function rand_num() returns int(5)
begin
declare i int default 0;
set i=floor (100+rand()*10);
return i;
end $$
delimiter $$
create procedure insert_emp (in start int(10),in max_num int(10))
begin
declare i int default 0;
set autocommit =0;
repeat
set i=i+1;
insert into emp (empno,ename ,job,mgr,hiredate,sal,comm,deptno) values ((start+i),rand_string(6),'SALESMAN',0001,CURDATE(),2000,400
,rand_num());
until i=max_num
end repeat;
commit;
end $$

delimiter $$
create procedure insert_dept(in start int(10),in max_num int(10))
begin
declare i int default 0;
set autocommit=0;
repeat
set i=i+1;
insert into depttest(deptno,dname,ioc) values ((start+i),rand_string(10),rand_string(8));
until i=max_num
end repeat;
commit;
end $$

执行存储过程,存放了5百万条数据

delimiter ;
call insert_dept(100,10);
delimiter ;
call insert_emp (10001,500000);

数据查询时间对比
1.select * from emp limit 4000000,5;
2.select * from emp e , (select id from emp limit 4000000,5) e1 where e1.id = e.id

image.png
image.png

当然这里的并非真正的全表扫描,因为也没有order by排序字段(非索引字段),真实扫面的数据是 4000005条数据
使用优化的分页格式

image.png
仔细对比时间发现,数据量越大,才会有优势
image.png
首先创建一个子查询,再与主数据结合,中间多了一步,导致在分页数少的时候,反而没有优势,但是当分页数据达到一定量级,因为子查询使用到了 empno这个索引,可以看到 extra是useing index 也就是没有回表,效率高,关联主表的时候,主表也会命中主键索引.

总结:

在分页数不多的时候,可以直接使用limit的方法,程序判断如果大分页的情况可以使用优化后的格式,之所以优化后的sql快,是因为子查询返回id字段,可以命中索引,不需要回表,得到的临时表的id和原表的id关联,也会命中 主表的主键索引,myql也会使用小表驱动大表的原则,使关联的效率高.但是分页数据量级小的时候,因为多了几个操作,导致优化后的sql时间反而长.