如何有效提升MySQL大表分页查询效率(本文以一张900万条数据体量的表为例进行详细解读)

文章目录

    • 1、提出问题
      • 1.1 问题测试
    • 2、解决问题(三种方案)
      • 2.1、方案一:查询的时候,只返回主键 ID
      • 2.2、方案二:查询的时候,通过主键 ID 过滤
      • 2.3、方案三:采用 elasticSearch 作为搜索引擎
    • 3、总结

1、提出问题

在某车辆监控项目的软件系统开发过程中,记录每台车的行驶轨迹表数据会随着时间的推移,单表的数据量会越来越大(每台车辆10秒记录一条)。

每台车的数据体量:

车辆记录方式天/按8个小时计算
每台车辆10秒一条记录2880864001036800

可见,车辆轨迹数据的查询不会像最初那样简单快速,如果查询关键字段没有走索引,会直接影响到用户体验,甚至会影响到服务是否能正常运行!

下面就某月车辆行驶轨迹表为例,数据库是 Mysql,数据体量在 900 万以上,详细介绍分页查询下,不同阶段的查询效率情况。

在这里插入图片描述

1.1 问题测试

下面我们一起来测试一下,每次查询车辆行驶轨迹表时最多返回 100 条数据,不同的起始下,数据库查询性能的差异。

查询sql语句起点位置查询数量耗时(秒)
SELECT * FROM t_location_log ORDER BY id LIMIT 0,10001000.830s
SELECT * FROM t_location_log ORDER BY id LIMIT 10000,10001000.876s
SELECT * FROM t_location_log ORDER BY id LIMIT 1000000,10001001.038s
SELECT * FROM t_location_log ORDER BY id LIMIT 2000000,10001001.248s
SELECT * FROM t_location_log ORDER BY id LIMIT 3000000,10001001.326s
SELECT * FROM t_location_log ORDER BY id LIMIT 4000000,10001001.526s
SELECT * FROM t_location_log ORDER BY id LIMIT 5000000,10001001.906s

在这里插入图片描述

可以看出,随着起点位置越大,分页查询效率下降明显,一般查询耗时超过 1 秒的 SQL 都被称为慢 SQL,事实上,这还只是数据库层面的耗时,还没有算后端服务的处理链路时间,以及返回给前端的数据渲染时间,以百万级的单表查询为例,如果数据库查询耗时 1 秒,再经过后端的数据封装处理,前端的数据渲染处理,以及网络传输时间,没有异常的情况下,差不多在 3~4 秒之间,必须在限定的时间内尽快优化,不然可能会影响服务的正常运行和用户体验。

对于千万级的单表数据查询,我也测试了一下,查询耗时结果:43 秒!
在这里插入图片描述

据互联网软件用户体验报告:

  • b当平均请求耗时在1秒之内,用户体验是最佳的,此时的软件也是用户留存度最高的;
  • 2 秒之内,还勉强过的去,用户能接受;
  • 当超过 3 秒,体验会稍差;超过 5 秒,基本上会卸载当前软件。

2、解决问题(三种方案)

2.1、方案一:查询的时候,只返回主键 ID

我们继续回到上文给大家介绍的客户表查询,将select *改成select id,简化返回的字段,我们再来观察一下查询耗时。

查询sql语句起点位置查询数量耗时(秒)
SELECT id FROM t_location_log ORDER BY id LIMIT 0,10001000.649s
SELECT id FROM t_location_log ORDER BY id LIMIT 10000,10001000.713s
SELECT id FROM t_location_log ORDER BY id LIMIT 1000000,10001000.883s
SELECT id FROM t_location_log ORDER BY id LIMIT 2000000,10001001.107s
SELECT id FROM t_location_log ORDER BY id LIMIT 3000000,10001001.272s
SELECT id FROM t_location_log ORDER BY id LIMIT 4000000,10001001.452s
SELECT id FROM t_location_log ORDER BY id LIMIT 5000000,10001001.753s

通过对比发现,通过简化返回的字段,可以提升查询效率。

实际的操作思路就是先通过分页查询满足条件的主键 ID,然后通过主键 ID 查询部分数据,可以显著提升查询效果。

-- 先分页查询满足条件的主键ID
select id from  t_location_log order by id limit 100000,10;

-- 再通过分页查询返回的ID,批量查询数据
select * from  t_location_log where id in (1,2,3,4,.....);

2.2、方案二:查询的时候,通过主键 ID 过滤

这种方案有一个要求就是主键ID,必须是数字类型,实践的思路就是取上一次查询结果的 ID 最大值,作为过滤条件,而且排序字段必须是主键 ID,不然分页排序顺序会错乱。

查询sql语句耗时(秒)
SELECT id FROM t_location_log WHERE id > 100000 ORDER BY id LIMIT 1000.636s
SELECT id FROM t_location_log WHERE id > 500000 ORDER BY id LIMIT 1000.669s
SELECT id FROM t_location_log WHERE id > 1000000 ORDER BY id LIMIT 1000.738s

带上主键 ID 作为过滤条件,查询性能非常的稳定,基本上在0.69 s内可以返回。

这种方案还是非常可行的,如果当前业务对排序要求不多,可以采用这种方案,性能也非常杠!但是如果当前业务对排序有要求,比如通过客户最后修改时间、客户最后下单时间、客户最后下单金额等字段来排序,那么上面介绍的【方案一】,比【方案二】查询效率更高!

2.3、方案三:采用 elasticSearch 作为搜索引擎

当数据量越来越大的时候,尤其是出现分库分表的数据库,以上通过主键 ID 进行过滤查询,效果可能会不尽人意,还有另一种比较好的解决办法就是将数据存储到 elasticSearch 中,通过 elasticSearch 实现快速分页和搜索,效果提升也是非常明显。

3、总结

上文中介绍的表主键 ID 都是数值类型的,之所以采用数字类型作为主键,是因为数字类型的字段能很好的进行排序。但如果当前表的主键 ID 是字符串类型,比如 uuid 这种,就没办法实现这种排序特性,而且搜索性能也非常差,因此不建议大家采用 uuid 作为主键ID,具体的数值类型主键 ID 的生成方案有很多种,比如自增、雪花算法等等,都能很好的满足我们的需求。

希望本文中的一些sql查询技巧给你实际工作中帮到你。

在这里插入图片描述


人生从来没有真正的绝境。只要一个人的心中还怀着一粒信念的种子,那么总有一天,他就能走出困境,让生命重新开花结果。


本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:/a/906116.html

如若内容造成侵权/违法违规/事实不符,请联系我们进行投诉反馈qq邮箱809451989@qq.com,一经查实,立即删除!

相关文章

整车功能架构 --- 智能座舱

我是穿拖鞋的汉子,魔都中坚持长期主义的汽车电子工程师。 老规矩,分享一段喜欢的文字,避免自己成为高知识低文化的工程师: 所有人的看法和评价都是暂时的,只有自己的经历是伴随一生的,几乎所有的担忧和畏惧…

「C/C++」C/C++ 之 动态内存分配

✨博客主页何曾参静谧的博客📌文章专栏「C/C」C/C程序设计📚全部专栏「VS」Visual Studio「C/C」C/C程序设计「UG/NX」BlockUI集合「Win」Windows程序设计「DSA」数据结构与算法「UG/NX」NX二次开发「QT」QT5程序设计「File」数据文件格式「PK」Parasoli…

WSGI、uwsgi、uWSGI与Nginx

一、写在前 WSGI、uwsgi与uWSGI WSGI (web server gateway interface 网络服务网关接口) 只能用于python,是一个接口标准协议,django、flask自带有uwsgi 是WSGI的通信协议uWSGI是实现了uwsgi协议和WSGI服务的web服务器 他们是为了将web服务器与web框架…

【element ui系列】分享几种实现el-table表格单选的方法

在实际的开发中,经常会用到从表格中选择一条记录的情况,虽然官方给出的例子,但是给人感觉看起来不明显,于是,在此基础上做了改进。接下来,介绍两种常见的实现方法: 1、采用复选框(checkbox)实现…

nodejs批量修改word文档目录样式

工作中遇到一个需求:写个nodejs脚本,对word文档(1000+个)的目录页面进行美化。实现过程遇到不少麻烦,在此分享下。 整体思路 众所周知,Docx格式的Word文档其实是个以xml文件为主的zip压缩包,所以,页面美化整体思路是:先将文档后缀名改为zip并解压到本地,然后将关键的…

信息安全数学基础(40)同态

一、定义 设R和S是两个环(或群等其他代数结构),如果存在一个映射σ:R→S,使得对于R中的任意元素a和b,都满足σ(ab)σ(a)σ(b)和σ(ab)σ(a)σ(b)(在群的情况下,则满足σ(a*b)σ(a)σ…

【笔面试常见题:三门问题】用条件概率、全概率和贝叶斯推导

1. 问题介绍 三门问题,又叫蒙提霍尔问题(Monty Hall problem),以下是蒙提霍尔问题的一个著名的叙述,来自Craig F. Whitaker于1990年寄给《展示杂志》(Parade Magazine)玛丽莲沃斯莎凡特&#x…

反向代购企业如何精准出击高需求国家——以俄罗斯市场为例

在全球化贸易的浪潮中,中国凭借其强大的工业生产能力、优越的地缘优势以及与俄罗斯日益紧密的双边关系,已成为俄罗斯不可或缺的贸易伙伴。中国制造的商品,从衣食住行到各类消费品,已深深渗透到俄罗斯民众的日常生活中。 据数据统计…

Muse-Ant-Desgin-Vue 改造成 Vite+Vue3

后台地址:https://www.creative-tim.com/product/muse-vue-ant-design-dashboard?refantdv-official 一、配置 ViteAntDesginVue 配置ViteAntDesginVue ViteAntDesginVue配置:https://blog.csdn.net/qq_17523181/article/details/143241626 安装vue-ro…

Proteus中单片机IO口外接LED输出低电平时,引脚却一直保持高电平的问题(已解决)

文章目录 前言解决方法后记 前言 一个排阻接八个 LED,方便又省事,但出现了P1端口输出低电平后,仿真引脚却一直显示红色保持高电平不变,用电压表测量显示 2V 左右。 这是仿真的问题,在用开发板时是不会遇到的&#xff…

DEVOPS: 认证与调度

概述 不知道大家有没有意识到一个现实,就是大部分时候,我们已经不像以前一样通过命令行,或者可视窗口来使用一个系统了现在我们上微博、或者网购,操作的其实不是眼前这台设备,而是一个又一个集群 通常,这样…

Altium Designer使用技巧(一)

一、清除绿色报错。 按TM快捷键。即可消除报错。 二、模块化框选集中。 在原理图中框选原理图,然后按 T S,会跳转到指定pcb,并选择上相应封装,此时按 T O L,再次画一个矩形,刚才选中的封装就会汇集到一起。 三、隐藏飞线 在PCB界面…

笔记本双系统win10+Ubuntu 20.04 无法调节亮度亲测解决

sudo add-apt-repository ppa:apandada1/brightness-controller sudo apt-get update sudo apt-get install brightness-controller-simple 安装好后找到一个太阳的图标,就是这个软件,打开后调整brightness,就可以调整亮度,可…

Unity XR Interaction Toolkit 开发教程(2):导入 SDK【3.0 以上版本】

文章目录 📕课程总结📕安装 Unity 编辑器与打包模块📕导入 OpenXR📕导入 XR Interaction Toolkit📕打包发布 获取完整课程以及答疑,工程文件下载: https://www.spatialxr.tech/ 视频试看链接&a…

Python中的字符串“不可改变。/可以改变?”

Python中,规定字符串是“不可变”类型,字符串方法可以“重写”字符串。Python最终让您明白,“字符串不可改变”。😎 (笔记模板由python脚本于2024年11月01日 17:55:57创建,本篇笔记适合熟悉python础数据类型str的coder…

5天学习RAG路线图,你信吗?

RAG是"Retrieval Augmented Generation"的缩写,让我们来拆解这个术语,了解RAG的本质: R -> Retrieval(检索) A -> Augmented(增强) G -> Generation(生成&…

tkinter 走进现代化【一】 - 登录页

import customtkinter as ctk from PIL import Image, ImageTk from tkinter import messagebox import timeclass LoginApp(ctk.CTk):def __init__(self):super().__init__()self.title("登录页面")self.geometry("600x400")self.resizable(False, False)…

实时特征框架的生产实践|得物技术

一、业务背景 使用场景 推荐系统在当今的互联网应用中扮演着至关重要的角色,它极大地丰富了用户体验,帮助用户在海量信息中发现和探索他们可能感兴趣的内容。然而,随着数据量的激增和用户需求的日益多样化,传统的离线推荐系统已…

vue3+vant实现视频播放(含首次禁止进度条拖拽,视频看完后恢复,保存播放视频进度,刷新及下次进入继续播放,判断视频有无全部看完等)

1、效果图 2、 <div><videocontrolsclass"video_player"ref"videoPlayer":src"videoSrc"timeupdate"handleTimeUpdate"play"onPlay"pause"onPause"ended"onVideoEnded"></video><…

【设计模式系列】迭代器模式(七)

一、什么是迭代器模式 迭代器模式&#xff08;Iterator Pattern&#xff09;是一种行为型设计模式&#xff0c;它提供一种方法来顺序访问一个聚合对象中的各个元素&#xff0c;而不暴露其内部的表示。迭代器模式将集合的遍历过程封装在一个独立的迭代器对象中&#xff0c;这样…