当前位置: 首页 » 综合知识 » it知识 » 正文

mysql千万数据表Limit分页查询如何优化

发布时间:2023-07-20 以下文章来源于网友投稿,内容仅供参考!

前言

在各类系统的表格类信息展示的功能中,经常会用到“翻页”这个操作,在页面上每次只展示有限的数据,需要看其他数据的时候则像翻书一样翻到后面的“页”。在 MySQL 支持的 SQL 语法中对此有特殊的支持,开发人员在实现这类功能的时候很方便:

  • select*fromxxx limit M,N

  • select*fromxxx limit N offset M

这两类语法代表的意思是一样的:返回从第 M 开始(不包括这一行)之后的 N 行数据。虽然使用起来很方便,但是这类语句存在查询性能上的陷阱,需要特别注意一下。 

原理简介

在解释原理之前,先看一下实际的效果,看看这个“性能的陷阱”是什么。


两个语句的内容都非常简单,差别只在 limit 的部分,第一个语句跳过的行数很少,第二个语句跳过的行数很多,结果是两个语句的执行时间差了至少 200 倍。PS:limit 配合 order by 使用是一个好习惯,确保结果数据是稳定的

可以看到跳过的行数大幅度增长时,SQL 语句的执行时间也会快速增长,原因其实比较简单:在处理 limit M,N 的时候,MySQL 会先拿到 M+N 行结果数据,然后再丢弃 M 行数据,展示之后剩下的 N 行数据。所以上图的第二个语句实际上扫描了 800 多万行数据,然后丢弃了 800 万行数据,只展示之后的 1 行结果。

利用[慢查询分析三部曲][Link 1]的方法尝试排查一下,explain 和 optimizer_trace 都看不出来差别,但是 profile 里面能看出来两者的差距:

    

虽然都只输出一行结果,但是在 Sending data 阶段花费的时间差别很大,其实就是花在扫描 800 万行数据上去了。 

优化策略

针对这个问题,其实有一个比较通用的优化思路:利用 join,先根据主键搜索到需要的数据,再通过主键关联到原来的表输出结果。SQL 可以改写一下:

SQL 改写的效果

可以看到查询时间降到了 1.5s 左右,提升了约 37%,看起来还可以,那么还有其他的办法么?

显然还是有的,不过这会要求表有自增主键。在分页查询的时候,记录上一次查询结果中的主键,然后在 where 条件中添加主键的范围约束。以上面的查询为例,上次分页查询时的主键是 8000001,那么下次分页的时候,where 条件中添加一个主键约束:id>8000001,再来看看查询效果:

添加条件之后的效果

可以发现利用主键来筛选掉上一次分页前的所有数据后再用 limit,查询基本是马上返回结果的。不过要特别注意,这种方法是根据主键的顺序先做了一次筛选,不一定会适用于所有的业务场景,理论上 UUID 类的主键也可以用,但是改造 SQL 前务必确保查询结果是符合预期的

  • • Linux Ecdsa密钥长度选择有何依据

    在Linux

  • • Linux Khook在内核监控中的应用如何

    Linux

  • • Linux Gsoap是否支持异步通信

    GSOAP是

  • • Linux Coremail如何提升用户体验

    提升Linu

  • • Linux Ecdsa算法有哪些局限性

    ECDSA

  • 哎呀音乐钢琴键盘学习《 钢琴主人训练营》 西瓜学琴
    郭蝈 陪练钢琴 30节课时 考级刚需 让孩子每一次练琴都是高质量的
    30天轻松学会五线谱 流行钢琴自学初级教程 牙牙学琴
    流行爵士钢琴实战技巧VIP课 - 继伟 哎呀音乐
    【海上钢琴师】原版 MT1990钢琴谱
    百首经典流行钢琴实战曲集 - 继伟
    雷费尔德电钢琴重锤88键专业考级儿童初学者数码电子钢琴家用
    小练咖 真人钢琴陪练 1v1服务 2999随时退 1课时50分钟 考级刚需
    雅马哈电钢琴88键重锤CLP735智能数码电子钢琴家用专业初学者考级
    【原装进口】Yamaha/雅马哈钢琴 b121 SC2原声静音钢琴
  • 珠海专业调钢琴
  • 天津宝坻区调钢琴
  • 天津静海区钢琴调音
  • 成都简阳市钢琴调律
  • 大连瓦房店市钢琴调音
  • 眉山调钢琴联系方式
  • 惠州大亚湾钢琴调琴师
  • 长治调琴师
  • 厦门湖里区钢琴调音师
  • 上海普陀区钢琴调音师