本文主要是介绍MySQL_分页优化，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

MySQL_分页优化

起因： offset+limit方式的分页查询，当数据表超过100w条记录，性能会很差。 主要原因是offset limit的分页方式是从头开始查询，然后舍弃前offset个记录，所以offset偏移量越大，查询速度越慢。

 

举例： 分页 limit 2000000，25，这个等同于数据库要扫描出 2000025 条数据，然后再丢弃前面的 20000000 条数据，返回剩下 25 条数据给用户，这种取法明显不合理。

《高性能 MySQL》第六章：查询性能优化，对这个问题有过说明：分页操作通常会使用 limit 加上偏移量的办法实现，同时再加上合适的 order by 子句。

1、数据模拟

①创建两个表：员工表和部门表

/*部门表,存在则进行删除 */
drop table if EXISTS dep;
create table dep(
    id int unsigned primary key auto_increment,
    depno mediumint unsigned not null default 0,
    depname varchar(20) not null default "",
    memo varchar(200) not null default ""
);

/*员工表,存在则进行删除*/
drop table if EXISTS emp;
create table emp(
    id int unsigned primary key auto_increment,
    empno mediumint unsigned not null default 0,
    empname varchar(20) not null default "",
    job varchar(9) not null default "",
    mgr mediumint unsigned not null default 0,
    hiredate datetime not null,
    sal decimal(7,2) not null,
    comn decimal(7,2) not null,
    depno mediumint unsigned not null default 0
);

②创建两个函数：生成随机字符串和随机编号（方便填充数据）

/* 产生随机字符串的函数*/
DELIMITER $ 
drop FUNCTION if EXISTS rand_string;
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN
    DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmlopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
    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;


/*产生随机部门编号的函数*/
DELIMITER $ 
drop FUNCTION if EXISTS rand_num;
CREATE FUNCTION rand_num() RETURNS INT(5)
BEGIN
    DECLARE i INT DEFAULT 0;
    SET i = FLOOR(100+RAND()*10);
    RETURN i;
END $
DELIMITER;

③编写存储过程，模拟 500W 的员工数据（过程有点慢）

 /*建立存储过程：往emp表中插入数据*/
 DELIMITER $
 drop PROCEDURE if EXISTS insert_emp;
 CREATE PROCEDURE insert_emp(IN START INT(10),IN max_num INT(10))
 BEGIN
     DECLARE i INT DEFAULT 0;
     /*set autocommit =0 把autocommit设置成0，把默认提交关闭*/
     SET autocommit = 0;
     REPEAT
     SET i = i + 1;
     INSERT INTO emp(empno,empname,job,mgr,hiredate,sal,comn,depno) VALUES ((START+i),rand_string(6),'SALEMAN',0001,now(),2000,400,rand_num());
     UNTIL i = max_num
     END REPEAT;
     COMMIT;
 END $
 DELIMITER;
 /*插入500W条数据*/
 call insert_emp(0,5000000);

④编写存储过程，模拟 120 的部门数据

/*建立存储过程：往dep表中插入数据*/
 DELIMITER $
 drop PROCEDURE if EXISTS insert_dept;
 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 dep( depno,depname,memo) VALUES((START+i),rand_string(10),rand_string(8));
     UNTIL i = max_num
     END REPEAT;
     COMMIT;
 END $
 DELIMITER;
 /*插入120条数据*/
 call insert_dept(1,120);

⑤建立关键字段的索引，这边是跑完数据之后再建索引，会导致建索引耗时长，但是跑数据就会快一些。

/*建立关键字段的索引:排序、条件*/
CREATE INDEX idx_emp_id ON emp(id);
CREATE INDEX idx_emp_depno ON emp(depno);
CREATE INDEX idx_dep_depno ON dep(depno); 

2、测试

测试一（直接分页）：

/*偏移量为100，取25*/
SELECT a.empno,a.empname,a.job,a.sal,b.depno,b.depname
from emp a left join dep b on a.depno = b.depno order by a.id desc limit 100,25;
/*偏移量为4800000，取25*/
SELECT a.empno,a.empname,a.job,a.sal,b.depno,b.depname
from emp a left join dep b on a.depno = b.depno order by a.id desc limit 4800000,25; 

执行结果：

[SQL]/*偏移量为100，取25*/
SELECT a.empno,a.empname,a.job,a.sal,b.depno,b.depname
from emp a left join dep b on a.depno = b.depno order by a.id desc limit 100,25;
受影响的行: 0
时间: 0.001s

[SQL]
/*偏移量为4800000，取25*/
SELECT a.empno,a.empname,a.job,a.sal,b.depno,b.depname
from emp a left join dep b on a.depno = b.depno order by a.id desc limit 4800000,25;
受影响的行: 0
时间: 9.325s

因为扫描的数据多，所以这个明显不是一个量级上的耗时。

测试二（使用索引覆盖+子查询优化）：

因为我们有主键 id，并且在上面建了索引，所以可以先在索引树中找到开始位置的 id 值，再根据找到的 id 值查询行数据。

 /*子查询获取偏移100条的位置的id，在这个位置上往后取25*/
 SELECT a.empno,a.empname,a.job,a.sal,b.depno,b.depname
 from emp a left join dep b on a.depno = b.depno
 where a.id >= (select id from emp order by id limit 100,1)
 order by a.id limit 25;

 /*子查询获取偏移4800000条的位置的id，在这个位置上往后取25*/
 SELECT a.empno,a.empname,a.job,a.sal,b.depno,b.depname
 from emp a left join dep b on a.depno = b.depno
 where a.id >= (select id from emp order by id limit 4800000,1)
 order by a.id limit 25;

执行结果：

[SQL] /*子查询获取偏移100条的位置的id，在这个位置上往后取25*/
 SELECT a.empno,a.empname,a.job,a.sal,b.depno,b.depname
 from emp a left join dep b on a.depno = b.depno
 where a.id >= (select id from emp order by id limit 100,1)
 order by a.id limit 25;
受影响的行: 0
时间: 0.014s

[SQL]
 /*子查询获取偏移4800000条的位置的id，在这个位置上往后取25*/
 SELECT a.empno,a.empname,a.job,a.sal,b.depno,b.depname
 from emp a left join dep b on a.depno = b.depno
 where a.id >= (select id from emp order by id limit 4800000,1)
 order by a.id limit 25;
受影响的行: 0
时间: 1.464s

测试三（起始位置重定义）：

记住上次查找结果的主键位置，避免使用偏移量 offset

 /*记住了上次的分页的最后一条数据的id是100，这边就直接跳过100，从101开始扫描表*/
 SELECT a.id,a.empno,a.empname,a.job,a.sal,b.depno,b.depname
 from emp a left join dep b on a.depno = b.depno
 where a.id > 100 order by a.id limit 25;

 /*记住了上次的分页的最后一条数据的id是4800000，这边就直接跳过4800000，从4800001开始扫描表*/
 SELECT a.id,a.empno,a.empname,a.job,a.sal,b.depno,b.depname
 from emp a left join dep b on a.depno = b.depno
 where a.id > 4800000
 order by a.id limit 25;

执行结果：

[SQL] /*记住了上次的分页的最后一条数据的id是100，这边就直接跳过100，从101开始扫描表*/
 SELECT a.id,a.empno,a.empname,a.job,a.sal,b.depno,b.depname
 from emp a left join dep b on a.depno = b.depno
 where a.id > 100 order by a.id limit 25;
受影响的行: 0
时间: 0.013s

[SQL]
 /*记住了上次的分页的最后一条数据的id是4800000，这边就直接跳过4800000，从4800001开始扫描表*/
 SELECT a.id,a.empno,a.empname,a.job,a.sal,b.depno,b.depname
 from emp a left join dep b on a.depno = b.depno
 where a.id > 4800000
 order by a.id limit 25;
受影响的行: 0
时间: 0.000s

这个效率是最好的，无论怎么分页，耗时基本都是一致的，因为他执行完条件之后，都只扫描了 25 条数据。

!>配置 limit 的偏移量和获取数一个最大值，超过这个最大值，就返回空数据。 因为他觉得超过这个值你已经不是在分页了，而是在刷数据了，如果确认要找数据，应该输入合适条件来缩小范围，而不是一页一页分页。( request 的时候如果 offset 大于某个数值就先返回一个 4xx 的错误。 )

这篇关于MySQL_分页优化的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！