MySQL 深度分页

这篇具有很好参考价值的文章主要介绍了MySQL 深度分页。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。


一、深度分页

MySQL 深度分页是指在分页查询数据量比较大的表时,需要访问表中的某一段数据,而这段数据的位置非常靠后,需要通过较大的 offset 来获取目标数据。


二、测试数据

-- 测试表
drop table if exists wide_table;
create table wide_table
(
    id          bigint auto_increment primary key,
    a           varchar(255),
    b           varchar(255),
    c           varchar(255),
    d           varchar(255),
    e           varchar(255),
    f           varchar(255),
    g           varchar(255),
    h           varchar(255),
    i           varchar(255),
    create_time datetime default current_timestamp
);
-- 插入十万条记录
delimiter //

drop procedure if exists insert_data;
create procedure insert_data()
begin
    declare i int default 1;
    while i <= 100000
        do
            insert into wide_table (a, b, c, d, e, f, g, h, i)
            values (i, i, i, i, i, i, i, i, i);
            set i = i + 1;
            if i % 10000 = 0 then
                select i;
            end if;
        end while;
end //

delimiter ;

call insert_data();

三、分页策略

3.1 默认分页

默认分页即通过 limit #{offset}, #{pageSize}limit #{pageSize} offset #{offset} 来进行分页。二者本质上都是全表扫描,MySQL 会依次取出 pageSize 条记录,然后判断其是否在 offset 后,如果不在则舍弃,继续过滤,所以效率低下。

test> select *
      from wide_table
      limit 80000, 100
[2024-01-18 14:55:42] 在 335 ms (execution: 298 ms, fetching: 37 ms) 内检索到从 1 开始的 100 行
test> select *
      from wide_table
      limit 100 offset 80000
[2024-01-18 14:55:43] 在 282 ms (execution: 233 ms, fetching: 49 ms) 内检索到从 1 开始的 100 行
explain
select *
from wide_table
limit 80000, 100;
# +--+-----------+----------+----------+----+-------------+----+-------+----+-----+--------+-----+
# |id|select_type|table     |partitions|type|possible_keys|key |key_len|ref |rows |filtered|Extra|
# +--+-----------+----------+----------+----+-------------+----+-------+----+-----+--------+-----+
# |1 |SIMPLE     |wide_table|null      |ALL |null         |null|null   |null|99551|100     |null |
# +--+-----------+----------+----------+----+-------------+----+-------+----+-----+--------+-----+

explain
select *
from wide_table
limit 100 offset 80000;
# +--+-----------+----------+----------+----+-------------+----+-------+----+-----+--------+-----+
# |id|select_type|table     |partitions|type|possible_keys|key |key_len|ref |rows |filtered|Extra|
# +--+-----------+----------+----------+----+-------------+----+-------+----+-----+--------+-----+
# |1 |SIMPLE     |wide_table|null      |ALL |null         |null|null   |null|99551|100     |null |
# +--+-----------+----------+----------+----+-------------+----+-------+----+-----+--------+-----+

3.2 索引分页

索引分页即通过索引字段(一般选取主键)的范围查询以及比较操作进行分页,通过应用索引能够大幅缩短查询时间。

test> select *
      from wide_table
      where id between 80000 and 80100
[2024-01-18 15:02:27] 在 224 ms (execution: 184 ms, fetching: 40 ms) 内检索到从 1 开始的 101 行
test> select *
      from wide_table
      where id > 80000
      limit 100
[2024-01-18 14:58:34] 在 218 ms (execution: 185 ms, fetching: 33 ms) 内检索到从 1 开始的 100 行
explain
select *
from wide_table
where id between 80000 and 80100;
# +--+-----------+----------+----------+-----+-------------+-------+-------+----+----+--------+-----------+
# |id|select_type|table     |partitions|type |possible_keys|key    |key_len|ref |rows|filtered|Extra      |
# +--+-----------+----------+----------+-----+-------------+-------+-------+----+----+--------+-----------+
# |1 |SIMPLE     |wide_table|null      |range|PRIMARY      |PRIMARY|8      |null|101 |100     |Using where|
# +--+-----------+----------+----------+-----+-------------+-------+-------+----+----+--------+-----------+

explain
select *
from wide_table
where id > 80000
limit 100;
# +--+-----------+----------+----------+-----+-------------+-------+-------+----+-----+--------+-----------+
# |id|select_type|table     |partitions|type |possible_keys|key    |key_len|ref |rows |filtered|Extra      |
# +--+-----------+----------+----------+-----+-------------+-------+-------+----+-----+--------+-----------+
# |1 |SIMPLE     |wide_table|null      |range|PRIMARY      |PRIMARY|8      |null|39420|100     |Using where|
# +--+-----------+----------+----------+-----+-------------+-------+-------+----+-----+--------+-----------+

3.3 子查询

索引分页的局限性在于待查询的表必须有一个自增长的主键,如果主键是 UUID 就无法进行索引分页了,此外在编码时还需要考虑索引断裂导致的重复查询。

我们也可以通过子查询来优化分页,即先通过默认分页操作找到限定的主键,这个操作能够利用主键索引而且只考虑主键这一个字段,因此效率比较高。然后在根据这个主键进行条件查询,从而得到分页数据。

test> select *
      from wide_table
      where id >= (select id from wide_table limit 80000, 1)
      limit 100
[2024-01-18 15:03:34]236 ms (execution: 202 ms, fetching: 34 ms) 内检索到从 1 开始的 100
explain
select *
from wide_table
where id >= (select id from wide_table limit 80000, 1)
limit 100;
# +--+-----------+----------+----------+-----+-------------+-------+-------+----+-----+--------+-----------+
# |id|select_type|table     |partitions|type |possible_keys|key    |key_len|ref |rows |filtered|Extra      |
# +--+-----------+----------+----------+-----+-------------+-------+-------+----+-----+--------+-----------+
# |1 |PRIMARY    |wide_table|null      |range|PRIMARY      |PRIMARY|8      |null|39420|100     |Using where|
# |2 |SUBQUERY   |wide_table|null      |index|null         |PRIMARY|8      |null|99551|100     |Using index|
# +--+-----------+----------+----------+-----+-------------+-------+-------+----+-----+--------+-----------+

3.4 延迟关联

延迟关联主要通过减少回表次数来提高查询效率。

分析下面的执行计划:

  • 第三行执行计划 id 最大最先执行,这是一个对 wide_table 表的索引扫描,只遍历索引而无需回表(可以理解为索引覆盖)。最终查询到了从第 80001 条记录开始的 100 条记录的主键,并将这些主键保存在临时表 wt 中。
  • 第一行执行计划对 wt 进行了全表扫描,获取了主键的查询结果集。
  • 第二行执行计划在 wt 中这 100 条主键记录的基础上,通过等值连接(延迟关联)回表获取到了最终的分页结果。

整个查询过程中只有最终的 100 条记录发生了回表,其余记录都只查询了 id 并被过滤掉了,提高了查询的效率。文章来源地址https://www.toymoban.com/news/detail-807462.html

test> select *
      from wide_table
      inner join (select id from wide_table limit 80000, 100) as wt
      on wide_table.id = wt.id
[2024-01-18 15:03:36]241 ms (execution: 207 ms, fetching: 34 ms) 内检索到从 1 开始的 100
explain
select *
from wide_table
inner join (select id from wide_table limit 80000, 100) as wt
on wide_table.id = wt.id;
# +--+-----------+----------+----------+------+-------------+-------+-------+-----+-----+--------+-----------+
# |id|select_type|table     |partitions|type  |possible_keys|key    |key_len|ref  |rows |filtered|Extra      |
# +--+-----------+----------+----------+------+-------------+-------+-------+-----+-----+--------+-----------+
# |1 |PRIMARY    |<derived2>|null      |ALL   |null         |null   |null   |null |80100|100     |null       |
# |1 |PRIMARY    |wide_table|null      |eq_ref|PRIMARY      |PRIMARY|8      |wt.id|1    |100     |null       |
# |2 |DERIVED    |wide_table|null      |index |null         |PRIMARY|8      |null |99551|100     |Using index|
# +--+-----------+----------+----------+------+-------------+-------+-------+-----+-----+--------+-----------+

到了这里,关于MySQL 深度分页的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • 毕业设计:基于python人脸识别考勤系统 签到系统 深度学习 Flask框架 Dlib库 MySQL数据库 大数据(源码+论文)✅

    毕业设计:2023-2024年计算机专业毕业设计选题汇总(建议收藏) 毕业设计:2023-2024年最新最全计算机专业毕设选题推荐汇总 🍅 感兴趣的可以先收藏起来,点赞、关注不迷路,大家在毕设选题,项目以及论文编写等相关问题都可以给我留言咨询,希望帮助同学们顺利毕业 。

    2024年02月20日
    浏览(76)
  • MySQL百万数据深度分页优化思路分析

    一般在项目开发中会有很多的统计数据需要进行上报分析,一般在分析过后会在后台展示出来给运营和产品进行 分页查看 , 最常见的一种就是根据日期进行筛选 。这种统计数据随着时间的推移数据量会慢慢的变大,达到百万、千万条数据只是时间问题。 创建了一张user表,

    2024年02月03日
    浏览(55)
  • 【性能优化】MySQL百万数据深度分页优化思路分析

            一般在项目开发中会有很多的统计数据需要进行上报分析,一般在分析过后会在后台展示出来给运营和产品进行 分页查看 , 最常见的一种就是根据日期进行筛选 。这种统计数据随着时间的推移数据量会慢慢的变大,达到百万、千万条数据只是时间问题。 一、数

    2024年02月15日
    浏览(51)
  • 【MySQL 】MySQL 创建数据库, MySQL 删除数据库,MySQL 选择数据库

    作者简介: 辭七七,目前大一,正在学习C/C++,Java,Python等 作者主页: 七七的个人主页 文章收录专栏: 七七的闲谈 欢迎大家点赞 👍 收藏 ⭐ 加关注哦!💖💖 我们可以在登陆 MySQL 服务后,使用 create 命令创建数据库,语法如下: 以下命令简单的演示了创建数据库的过程,

    2024年02月13日
    浏览(92)
  • 【MySQL数据库】初识MySQL数据库、安装MySQL

    在今天的数字化世界中,数据是企业和个人的重要资产。管理和存储数据变得至关重要,而 MySQL 数据库是一种备受欢迎的开源关系型数据库管理系统,它提供了稳定、可靠、高性能的数据存储解决方案。本文将介绍 MySQL 数据库的基本概念和安装过程,以便初学者能够轻松上

    2024年02月08日
    浏览(64)
  • 【MySQL数据库】MySQL数据库管理

    Structure Query Language(结构化查询语言)简称SQL,它被美国国家标准局(ANSI)确定为关系型数据库语言的美国标准,后被国际化标准组织(ISO)采纳为关系数据库语言的国际标准。数据库管理系统可以通过SQL管理数据库;定义和操作数据,维护数据的完整性和安全性。 数据:(data)

    2024年02月08日
    浏览(51)
  • 初识MySQL数据库——“MySQL数据库”

    各位CSDN的uu们你们好呀,小雅兰好久没有更文啦,确实是心有余而力不足,最近学习的内容太难了,这篇博客又是小雅兰的新专栏啦,主要介绍的是一些MySQL数据库的知识点,下面,让我们进入初识MySQL数据库的世界吧 为什么要使用数据库 数据库与数据库管理系统 MySQL介绍

    2024年02月06日
    浏览(52)
  • MySQL数据库:数据库管理系统与安装MySQL数据库

    目录 一、理论 1.数据库管理系统 2.关系型数据库 3.数据库 4.MySQL数据库 5.MySQL部署 二、实验 1.yum安装MySQL 2.编译安装MySQL 3.配置MySQL数据库的Tab补全  三、问题 1.数据库登录报错 2.数据库密码复杂度报错 3.数据库连接报错 四、总结 (1)概念 数据库管理系统(Database Management

    2024年02月12日
    浏览(65)
  • 初识mysql数据库之mysql数据库安装(centos)

    目录 一、卸载不需要的环境 二、安装mysql yum源 三、安装mysql 四、登录mysql 1. 直接登录 2. 设置免密码登录 五、配置my.cnf 六、mysql登录时的一些选项介绍 要注意,在安装mysql数据库时,最好将用户切换为root,避免一些不必要的问题。当数据库安装好后,普通用户也可以使用的

    2024年02月03日
    浏览(52)
  • MySQL笔记——MySQL数据库介绍以及在Linux里面安装MySQL数据库,对MySQL数据库的简单操作,MySQL的外接应用程序使用说明

    MySQL笔记——MySQL数据库介绍以及在Linux里面安装MySQL数据库,对MySQL数据库的简单操作,MySQL的外接应用程序使用说明 MySQL笔记——表的分组查询、表的分页查询、表的约束、数据库设计 MySQL案例——多表查询以及嵌套查询 MySQL笔记——数据库当中的事务以及Java实现对数据库进

    2024年01月16日
    浏览(75)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包