MySql深分页问题解决

目录
  • 1. 问题描述
  • 2. 问题分析
  • 3. 验证测试
    • 3.1 创建两个表
    • 3.2 创建两个函数
    • 3.3 编写存储过程
    • 3.4 编写存储过程
    • 3.5 创建索引
    • 3.6 验证测试
  • 4. 解决方案
    • 4.1 使用索引覆盖+子查询优化
    • 4.2 起始位置重定义
    • 4.3 降级策略
  • 5. 梳理总结

1. 问题描述

日常开发中经常会涉及到数据查询分页的问题,一般情况下都是根据前端传入页数与页码通过mysql的limit方式实现分页,对于数据量较小的情况下没有问题,但是如果数据量很大,深分页可能导致查询效率低下,接口超时的情况。

2. 问题分析

其实对于我们的 MySQL 查询语句来说,整体效率还是可以的,该有的联表查询优化都有,该简略的查询内容也有,关键条件字段和排序字段该有的索引也都在,问题在于他一页一页的分页去查询,查到越后面的页数,扫描到的数据越多,也就越慢。

我们在查看前几页的时候,发现速度非常快,比如 limit 200,25,瞬间就出来了。但是越往后,速度就越慢,特别是百万条之后,卡到不行,那这个是什么原理呢。先看一下我们翻页翻到后面时,查询的 sql 是怎样的:

select * from t_name where c_name1='xxx' order by c_name2 limit 2000000,25;

这种查询的慢,其实是因为 limit 后面的偏移量太大导致的。
比如像上面的 limit 2000000,25,这个等同于数据库要扫描出 2000025 条数据,然后再丢弃前面的 20000000 条数据,返回剩下 25 条数据给用户,这种取法明显不合理。

3. 验证测试

3.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
);

注意说明

  • mediumint是MySQL数据库中的一种整型,比INT小,比SMALLINT大,
  • 取值范围为:-8388608到8388607,无符号的范围是0到16777215。
  • 中等大小的整数,一位大小为3个字节。

3.2 创建两个函数

-- 创建两个函数:生成随机字符串和随机编号
-- 产生随机字符串的函数
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;

注意说明
-- 执行函数问题,This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its de
-- 这是我们开启了bin-log, 我们就必须指定我们的函数是否是,DETERMINISTIC 不确定的, NO SQL 没有SQl语句,当然也不会修改数据
-- 在MySQL中创建函数时出现这种错误的解决方法:set global log_bin_trust_function_creators=TRUE;
set global log_bin_trust_function_creators=TRUE;

3.3 编写存储过程

-- 编写存储过程,模拟 100W 的员工数据。
-- 建立存储过程:往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条数据,时间有点久,耐心等待,1409s
 call insert_emp(0,5000000);

-- 查询部门员工表
select * from emp LIMIT 1,10;

3.4 编写存储过程

-- 编写存储过程,模拟 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);

-- 查询部门员工表
select * from dep;

3.5 创建索引

-- 建立关键字段的索引,这边是跑完数据之后再建索引,会导致建索引耗时长,但是跑数据就会快一些。
-- 建立关键字段的索引:排序、条件
CREATE INDEX idx_emp_id ON emp(id);
CREATE INDEX idx_emp_depno ON emp(depno);
CREATE INDEX idx_dep_depno ON dep(depno);

3.6 验证测试

-- 验证测试
-- 偏移量为100,取25,Time: 0.011s
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,Time: 10.242s
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;

4. 解决方案

4.1 使用索引覆盖+子查询优化

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

-- 子查询获取偏移100条的位置的id,在这个位置上往后取25,Time: 0.04s
 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,Time: 1.549s
 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;

4.2 起始位置重定义

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

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

但是有个问题,只适合一页一页的分页,这样才能记住前一个分页的最后 id。如果用户跳着分页就有问题了,比如刚刚刷完第 25 页,马上跳到 35 页,数据就会不对。这种的适合场景是类似百度搜索或者腾讯新闻那种滚轮往下拉,不断拉取不断加载的情况。这种延迟加载会保证数据不会跳跃着获取。

-- 记住了上次的分页的最后一条数据的id是100,这边就直接跳过100,从101开始扫描表,Time: 0.006s
 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开始扫描表,Time: 0.046s
 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;

4.3 降级策略

看了网上一个阿里的 DBA 同学分享的方案:配置 limit 的偏移量和获取数一个最大值,超过这个最大值,就返回空数据。
因为他觉得超过这个值你已经不是在分页了,而是在刷数据了,如果确认要找数据,应该输入合适条件来缩小范围,而不是一页一页分页。

5. 梳理总结

深分页问题从理论上来说是存在的场景,但是从实际的业务场景考虑,深分页很多情况下缺少具体的业务场景做支撑,试想哪个业务会从480W页面,查询25条数据,如果需要搜索某条数据,使用最多的应该根据条件类型过滤吧。

每种方案各有优缺点,具体采用那种解决方案需要结合具体的业务场景,如果根据实际业务场景不需要深分页,可以采用降级策略,设置分页参数阈值。如果确实需要深分页问题可以覆盖子+子查询优化或者通过偏移量查询,如果能获取到偏移量的前提下优先选择偏移量的方案,否则采用覆盖索引+子查询。

无论是否深分页都应该考虑限流降级的问题,而且要考虑短时间内重复调用的问题,可以限制每秒执行次数,避免用户误点以及调用频繁带来的数据安全问题。

到此这篇关于MySql深分页问题解决的文章就介绍到这了,更多相关MySql深分页内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们!

(0)

相关推荐

  • 快速解决mysql深分页问题

    目录 背景 概括 1.limit深分页问题描述 2.sql慢原因分析 聚簇索引和非聚簇索引 常见解决方案 通过子查询优化 标签记录法 方案对比 实战案例 总结 背景 日常需求开发过程中,相信大家对于limit一定不会陌生,但是使用limit时,当偏移量(offset)非常大时,会发现查询效率越来越慢.一开始limit 2000时,可能200ms,就能查询出需要的到数据,但是当limit 4000 offset 100000时,会发现它的查询效率已经需要1S左右,那要是更大的时候呢,只会越来越慢.

  • MySQL深分页问题解决的实战记录

    目录 前言 limit深分页为什么会变慢? 通过子查询优化 回顾B+ 树结构 把条件转移到主键索引树 INNER JOIN 延迟关联 标签记录法 使用between...and... 手把手实战案例 一般思路的实现方式 实战优化方案 总结 前言 我们日常做分页需求时,一般会用limit实现,但是当偏移量特别大的时候,查询效率就变得低下.本文将分4个方案,讨论如何优化MySQL百万数据的深分页问题,并附上最近优化生产慢SQL的实战案例. limit深分页为什么会变慢? 先看下表结构哈: CREAT

  • MySQL深分页问题解决思路

    目录 一.MySQL深分页问题 1.limit 语法解读 2.回表 二.优化方案 一.MySQL深分页问题 我们在日常开发中,查询数据量比较大的时候,后端基本都会通过前端,移动端传过来的页码,每页数据行数,通过SQL中的 limit 进行分页,如果查询页数比较小的时候,不会出现太大问题,但是如果查询页码比较大的时候,性能就会出现急剧下降瓶颈 如: 假设有一个千万量级的表,取1到10条数据 select column_name1,column_name2... from table limit 0

  • 提高MySQL深分页查询效率的三种方案

    开发经常遇到分页查询的需求,但是当翻页过多的时候,就会产生深分页,导致查询效率急剧下降.有没有什么办法,能解决深分页的问题呢?本文总结了三种优化方案,查询效率直接提升10倍,一起学习一下. 开发经常遇到分页查询的需求,但是当翻页过多的时候,就会产生深分页,导致查询效率急剧下降. 有没有什么办法,能解决深分页的问题呢? 本文总结了三种优化方案,查询效率直接提升10倍,一起学习一下. 1. 准备数据 先创建一张用户表,只在create_time字段上加索引: CREATE TABLE `user`

  • MySql深分页问题解决

    目录 1. 问题描述 2. 问题分析 3. 验证测试 3.1 创建两个表 3.2 创建两个函数 3.3 编写存储过程 3.4 编写存储过程 3.5 创建索引 3.6 验证测试 4. 解决方案 4.1 使用索引覆盖+子查询优化 4.2 起始位置重定义 4.3 降级策略 5. 梳理总结 1. 问题描述 日常开发中经常会涉及到数据查询分页的问题,一般情况下都是根据前端传入页数与页码通过mysql的limit方式实现分页,对于数据量较小的情况下没有问题,但是如果数据量很大,深分页可能导致查询效率低下,接

  • jQuery+Ajax+PHP+Mysql实现分页显示数据实例讲解

    本文使用jQuery,结合PHP和Mysql,通过实例讲解如何实现Ajax数据加载效果. HTML <div id="list"> <ul></ul> </div> <div id="pagecount"></div> 页面中,#list用来展示数据列表,包括本例要展示的商品图片和标题,#pagecount用来展示分页条,即本例中的上一页.下一页. 当然,别忘了,在head中预先载入jquery

  • PHP和MYSQL实现分页导航思路详解

    预期效果 思路 通过SQL语句 SELECT * FROM table LIMIT start end 来从MySql数据库 步骤 传入页码p: 根据页码获取数据php->mysql 显示数据+分页条 源码 github 链接 注意点 table,input,button等控件的样式不会继承body,需要重新定义如下 input,label, select,option,textarea,button,fieldset,legend,table{ font-size:18px; FONT-FAM

  • mysql limit 分页的用法及注意要点

    mysql limit 分页的用法及注意事项: 在我们使用查询语句的时候,经常要返回前几条或者中间某几行数据,这个时候怎么办呢?不用担心,mysql已经为我们提供了这样一个功能. SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数.LIMIT 接受一个或两个数字参数.参数必须 是一个整数常量.如果给定两个参数,第一个参数指定第一个返回记录行的偏移量,第二个参

  • mysql limit分页优化详细介绍

    mysql limit分页优化 同样是取10条数据 select * from yanxue8_visit limit 10000,10 和 select * from yanxue8_visit limit 0,10 就不是一个数量级别的. 网上也很多关于limit的五条优化准则,都是翻译自MySQL手册,虽然正确但不实用.今天发现一篇文章写了些关于limit优化的,很不错. 文中不是直接使用limit,而是首先获取到offset的id然后直接使用limit size来获取数据.根据他的数据,

  • Mysql中分页查询的两个解决方法比较

    mysql中分页查询有两种方式, 一种是使用COUNT(*)的方式,具体代码如下 复制代码 代码如下: SELECT COUNT(*) FROM foo WHERE b = 1; SELECT a FROM foo WHERE b = 1 LIMIT 100,10; 另外一种是使用SQL_CALC_FOUND_ROWS 复制代码 代码如下: SELECT SQL_CALC_FOUND_ROWS a FROM foo WHERE b = 1 LIMIT 100, 10; SELECT FOUND_

  • Mysql Limit 分页查询优化详解

    select * from table LIMIT 5,10; #返回第6-15行数据 select * from table LIMIT 5; #返回前5行 select * from table LIMIT 0,5; #返回前5行 我们来写分页 物理分页 select * from table LIMIT (当前页-1)*每页显示条数,每页显示条数; MySQL之Limit简单优化.md 同样是取90000条后100条记录,传统方式还是改造方式? 传统方式是先取了前90001条记录,取其中最

随机推荐