如何有效提升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查询技巧给你实际工作中帮到你。

在这里插入图片描述


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


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

如若内容造成侵权/违法违规/事实不符,请联系长河编程网进行投诉反馈email:809451989@qq.com,一经查实,立即删除!

相关文章

DGUS屏使用方法

1、DGUS工程下载 迪文DGUS屏的所有硬件参数和资料下载,都是通过屏上的SD/SDHC接口来完成的,文件必须使用FAT32文件格式。第一次使用SD卡前,推荐先格式化一次,流程如下: 1、 右键单击SD卡,在弹出来的菜单中选…

设计产品宣传册没头绪?推荐一个超多产品宣传册、画册的案例网站

在当今市场竞争激烈的背景下,产品宣传册和画册是企业宣传的重要手段之一。一本独具匠心的宣传册,不仅能够准确传达产品特点,还能吸引潜在客户,提升品牌形象。然而,设计一本优秀的宣传册并非易事,许多设计师…

接口测试(八)jmeter——参数化(CSV Data Set Config)

一、CSV Data Set Config 需求:批量注册5个用户,从CSV文件导入用户数据 1. 【线程组】–>【添加】–>【配置元件】–>【CSV Data Set Config】 2. 【CSV数据文件设置】设置如下 3. 设置线程数为5 4. 运行后查看响应结果

【网页布局技术】项目五 使用CSS设置导航栏

《CSSDIV网页样式与布局案例教程》 徐琴 目录 任务一 制作简单纵向导航栏支撑知识点1.合理利用display:block属性2.利用margin-bottom设置间隔效果3.利用border设置特殊边框 任务二 制作简单横向导航栏任务三 制作带图片效果的横向导航栏任务…

基于LangChain构建安全Agent应用实践(含代码)

概述:本文基于langchain和Cyber Security Breaches数据集构建Agent,并基于该Agent实现了数据分析、趋势图输出、预测攻击态势三个功能,最后给出Agent在安全领域应用的三点启示。 前提: 1、拥有openai API KEY;&#…

机器学习-决策树

登录后复制 import numpy as np import matplotlib.pyplot as plt from sklearn import datasetsiris datasets.load_iris() X iris.data[:,2:] y iris.target plt.scatter(X[y0,0], X[y0,1]) plt.scatter(X[y1,0], X[y1,1]) plt.scatter(X[y2,0], X[y2,1]) plt.show() 1.2.…

为什么大模型都是Decoder-only结构?

扫一扫下方,获取更多面试真题的集合 在探讨当前大型语言模型(LLM)普遍采用Decoder-only架构的现象时,我们可以从以下几个学术角度进行分析: 注意力机制的满秩特性:Decoder-only架构采用的因果注意力机制&am…

Linux系统块存储子系统分析记录

1 Linux存储栈 通过网址Linux Storage Stack Diagram - Thomas-Krenn-Wiki-en,可以获取多个linux内核版本下的存储栈概略图,下面是kernel-4.0的存储栈概略图: 2 存储接口、传输速度 和 协议 2.1 硬盘 《深入浅出SSD:固态存储核心…

北京迅为iTOP-LS2K0500开发板快速使用编译环境虚拟机Ubuntu基础操作及设置

迅为iTOP-LS2K0500开发板 迅为iTOP-LS2K0500开发板采用龙芯LS2K0500处理器,基于龙芯自主指令系统(LoongArch)架构,片内集成64位LA264处理器核、32位DDR3控制器、2D GPU、DVO显示接口、两路PClE2.0、两路SATA2.0、四路USB2.0、一路…

电子电气架构 --- 车载芯片现状

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

MySQL分层结构由哪些组成?

1、MySQL分层结构由哪些组成? MySQL按照功能模块可以分为3层:连接层、服务层和存储引擎层。 连接层位于Server服务层的最外层,负责与客户端的直接交互,从功能上单独划分一层更合适。 不同的存储引擎在存储层有不同的实现&#x…

Vue3入门--[vue/compiler-sfc] Unexpected token, expected “,“ (18:0)

新手小白学习Vue–入门就踩坑系列 问题描述 创建了一个Person.vue,保存后直接报错: [plugin:vite:vue] [vue/compiler-sfc] Unexpected token, expected "," (18:0) 在网上搜了半天也没找到原因,最后还得靠自己,现将解…

【宠粉赠书】大模型项目实战:多领域智能应用开发

在当今的人工智能与自然语言处理领域,大型语言模型(LLM)凭借其强大的生成与理解能力,正在广泛应用于多个实际场景中。《大模型项目实战:多领域智能应用开发》为大家提供了全面的应用技巧和案例,帮助开发者深…

java:入门基础(1)

练习一:文字版格斗游戏 需求: ​ 格斗游戏,每个游戏角色的姓名,血量,都不相同,在选定人物的时候(new对象的时候),这些信息就应该被确定下来。 举例: ​ 程序运行之后…

Apache Paimon介绍

目录 背景 诞生 应用场景 实时数据分析与查询 流批一体处理 低成本高效存储 具体业务场景示例 总结 系统架构 存储层 元数据管理 计算层 数据摄入和输出 查询优化 扩展性和可靠性 生态系统集成 总结 核心概念 表(Table) 模式&#xf…

书生实战营第四期-第三关 Git+InternStudio

一、任务1: 破冰活动:自我介绍 1.fork项目到自己的账号下 2. 配置git并克隆项目到InternStudio本地 3.创建分支 4.创建自己的介绍文件 5.提交更改分支 6.推送分支到远程仓库 这里推送时会报错 问题解决:将密码换成access token 7.检查提交内容 分支…

【商汤科技-注册/登录安全分析报告】

前言 由于网站注册入口容易被黑客攻击,存在如下安全问题: 暴力破解密码,造成用户信息泄露短信盗刷的安全问题,影响业务及导致用户投诉带来经济损失,尤其是后付费客户,风险巨大,造成亏损无底洞…

2-134 基于matlab的图像边缘检测

基于matlab的图像边缘检测,采用六种算子(分别是gabor、拉普拉斯、priwitt、robert、sobel、wallis微分算子),对图象进行边缘检测比较,输出边缘检测结果。可对比效果优劣。程序已调通,可直接运行。 下载源程序请点链接…

【计网】网络协议栈学习总结 --- 浏览器上输入网址域名后点击回车,到底发生了什么?

未来的路不会比过去更笔直,更平坦, 但是我并不恐惧, 我眼前还闪动着道路前方野百合和野蔷薇的影子。 --- 季羡林 《八十抒怀》--- 浏览器上输入URL后回车,到底发生了什么? 1 前言2 解析URL形成http请求3 DNS域名解…

ReactNative Fabric渲染器和组件(5)

ReactNative Fabric渲染器和组件 简述 Fabric是ReactNative中新架构最核心的模块,本章我们会来了解一下自定义一个Fabric组件,然后在JS文件中声明之后如何,是怎么映射到原生构建一个View的。 关于Fabric架构理念官网已经有说明了&#xff0…