likes
comments
collection
share

「MySQL 优化」MySQL 分页查询优化实例

作者站长头像
站长
· 阅读数 18

🙏废话不多说系列，直接开整🙏

「MySQL 优化」MySQL 分页查询优化实例

分页操作通常会使用limit加上偏移量的办法实现，同时再加上合适的order by子句。但这会出现一个常见问题：当偏移量非常大的时候，它会导致MySQL扫描大量不需要的行然后再抛弃掉。

一、准备数据

（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 comment "唯一ID",
    empno 	mediumint unsigned not null default 0 comment "员工编号",
    empname varchar(20) not null default "" comment "员工名",
    job 	varchar(9) not null default "" comment "工作岗位",
    mgr 	mediumint unsigned not null default 0,
    hiredate datetime not null comment "受雇日期",
    sal 	decimal(7,2) not null comment "薪资",
    comn 	decimal(7,2) not null,
    depno 	mediumint unsigned not null default 0 comment "部门编号"
);
（2）插入模拟数据
① 准备随机函数

准备两个用于随机员工名和部门编号的两个随机函数：随机字符串函数 和 随机部门编号。

-- 1.产生随机字符串的函数
delimiter $
drop function if exists rand_string;
create function rand_string(n int) returns varchar(255)
begin
	declare chars_str varchar(100) default 'abcdefghijklmnopqrstuvwxyyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
    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;

-- 2.产生随机部门编号的函数
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() * 100);
    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);
（3）建立关键字段的索引

这边是跑完数据（即插入模拟的500W的数据）之后再建立索引，会导致建立索引耗时长，但是跑数据就会快一些。

-- 建立关键字段的索引：排序、条件
create index idx_emp_id on emp(id);
create index idx_emp_depno on emp(depno);
create index idx_dep_depno on dep(depno);

二、测试演示

-- 偏移量为 100，取25 （测试结果：0.001s）
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（测试结果：6.531s）
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;

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

三、解决方案[🌟]

（1）使用【索引覆盖+子查询优化】

    主键ID有了主键索引，所以可以先在索引树中找到开始位置的 id 值，再根据找到的 id 值查询行数据。

-- 子查询获取偏移量100条位置的id，在这个位置上往后取25条数据（结果：0.013s）
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条数据（结果：1.101s）
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;

可以看到有之前的 6.531s 减少到 1.101s。

（2）起始位置重定义

此方法是 查找结果的主键位置，避免使用偏移量 offset。

-- 记住了上次的分页的最后一天数据的id是100，这边就直接跳过100，从101开始扫描表
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 > 100 -- 核心
order by a.id limit 25;

-- 记住了上次的分页的最后一天数据的id是4800000，这边就直接跳过4800000，从4800001开始扫描表
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 > 4800000 -- 核心
order by a.id limit 25;

测试结果分别为：【100:0.007s 】和 【4800000:0.003s】

优劣分析：

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

    但是有个问题，只适合一页一页的分页，这样才能记住前一个分页的最后Id。如果用户跳着分页就有问题了，比如刚刚刷完第25页，马上跳到35页，数据就会不对。《MySQL 开发 36 条军规》有必要看下。

    适合场景：类似百度搜索或者腾讯新闻那种滚轮往下拉（瀑布式数据展示的业务模块） ，不断拉取不断加载的情况。这种延迟加载会保证数据不会跳跃着获取。

（3）降级策略
  • 配置 limit 的偏移量  和  获取数一个最大值，超过这个最大值，就返回空数据。（觉得超过这个值你已经不是在分页了，而是在刷数据了，如果确认要找数据，应该输入合适条件来缩小范围，而不是一页一页分页。）
  • 实质：通过多条件筛查来快速查找。

Request 的时候 如果 offset大于某个数值就先返回一个4xx 的错误。(前端判断是否超过offset最大值，后端限制——双保险方案实施此策略)

总结

    推荐使用方案三降级策略，让用户通过条件快速查询；系统对offset做一个限流，超过某个值，就返回空值。当然，方案一和方案二是有必要对程序和数据库脚本进一步优化的。

    当然如果一位的考虑接纳用户的极端情况的查询也不行的，系统可以做些该有的 限流、降级。（例如：工具多线程调用，在短时间频率内8000次调用，可以使用计数服务判断并反馈用户调用过于频繁，直接给予断掉。）

转载自:https://juejin.cn/post/7389924114867896358
评论
请登录