MySQL查询原理与优化

文章目录

  • 前言
  • 执行查询的过程逻辑
    • 连接器
    • 查询缓存
    • 解析器
    • 优化器
    • 执行器
  • 衡量查询开销的三个指标
    • 响应时间
    • 扫描行数
    • 返回的行数
  • 重构查询的几种选择
    • 一个复杂的查询还是多个简单的查询
    • 切分查询
    • 关联查询
    • 解决关联查询的原则
  • 总结

前言

上一篇文章中(MySQL索引全解:从理论到实践,打造高效查询的指南),详细介绍了索引相关的内容,理解这部分知识对于实现高性能的查询来说必不可少,但这并不是全部,你还需要了解MySQL的查询流程,以及一些其他相关的查询机制,这样才能确保你不会写出一些性能糟糕的查询语句。

所以,本篇文章就针对MySQL查询相关的内容进行介绍,包含:执行查询的过程、查询消耗的关键指标、查询的优化等,通过这些内容希望能够帮助你明白一些执行较慢的查询产生的原因,以及如何才能避开它们。

执行查询的过程逻辑

下图展示了MySQL执行查询的大致过程逻辑。
image.png

了解这张图,将有助于你理解MySQL服务器的,总体来说,MySQL服务器可以分为两层:服务层和存储引擎层。

服务层包含了连接器、查询缓存、解析器、优化器、执行器以及所有内置函数、存储过程、触发器、视图等。

而存储引擎层主要就是负责数据的存储与查询,并且支持以插件形式进行替换,服务器通过存储引擎API进行交互,这些API屏蔽了不同存储引擎之间的差异,存储引擎通常只需包含少量的底层函数,然后通过一些组合即可满足各种查询需求,比如获取第一行数据,获取下一行数据,通过这两个API即可实现查询所有数据的功能,目前最常用的存储引擎就是InnoDB

连接器

连接器主要负责服务端与客户端建立连接、管理连接等,一旦连接建立完成,在服务端就会产生一个空闲连接,如果用show processlist查看,就会显示为sleep

如果超过wait_timeout时间都没有请求的话,连接器就会断开这个连接。

查询缓存

查询缓存用处不大,现在基本上客户端都会自己进行缓存,所以MySQL8.0版本中也已经删除了此功能。

解析器

解析器主要用来检查查询语句,对语法进行分析,检查表、字段是否存在等情况。

优化器

到了优化器就负责对执行计划的生成了,优化器会决定使用哪个索引,采用哪种join方式以及其他非常多的优化技巧。

执行器

最后,执行器就会根据优化器生成的执行计划,调用存储引擎的接口,大致流程就是取一行,然后判断是否符合条件,符合就存下来,不符合就跳过,然后继续取下一行,直到取到最后一行为止,然后将结果集返回给客户端。

衡量查询开销的三个指标

知道查询的消耗有哪些,将更有助于我们理解查询优化的方式以及设计,一般来说衡量查询开销的指标主要有如下三个:响应时间、扫描行数、返回的行数。

响应时间

响应时间包含又服务时间和排队时间,服务时间指的是执行查询真正花费的时间,而排队时间指的是服务器因为等待I/O资源、锁等资源时消耗的时间,两部分时间加起来则是一次请求所需要的全部时间,而这个时间通常是需要我们能大致判断出来的。

扫描行数

扫描行数是判断一个查询效率是否足够高的一个重要指标,从一定程度上来讲扫描行数越多查询也就越慢。

扫描的数据页多少也能反映出扫描行数的多少
image.png

对比主键查询
image.png

返回的行数

最完美的情况当然是,扫描的行数等于返回的行数,也就是要什么就查什么,但很遗憾,大多数情况下扫描行数与返回的行数的比例大约在1:110:1之间。

重构查询的几种选择

结合查询消耗的关键指标来分析,重构查询的目的就在于采取一些手段来尽量减少这些指标的消耗。

一个复杂的查询还是多个简单的查询

考虑到现在网络连接的速度以及连接池等手段,发起多次小而快的查询已经不是什么严重的问题了,言外之意就是如果能将一个复杂的查询拆分为几个简单的小查询,那也是不错的选择,这样拆分出来的逻辑,常常还可以通过其他方式进一步优化,比如join查询被拆分后,单张表的结果就可以被缓存起来,因此,你应该好好评估一下是否可以这样做。

切分查询

切分查询的目的是为了避免一次性产生过多的资源消耗,比如一次性要删除非常多的数据,这将会造成锁数据的范围非常大、事务日志的写入非常多,这些都会带来大量的线程排队等待的消耗。通常我们的建议是将一次大的操作,拆分为多次小的操作,分批完成。

关联查询

关联查询在日常满足业务需求中是非常常见的查询方式,同时也经常是造成慢查询的主要原因,MySQL其实也针对关联查询做了很多优化,尽量的提高其查询效率,但有些情况MySQL也并不能直接来进行查询重构,还是需要使用者有意识的做出一些调整才能命中到事先设计好的一些优化方式中,所以,接下来,让我们了解一下关联查询的一些查询设计吧!

如何完成一次关联查询?

首先,先了解一下关联查询是如何实现的,比如如下的一条查询语句:select b.* from a inner join b on a.id = b.id where a.col in (1, 2);

要实现关联查询,在代码逻辑上来说,本质就是嵌套循环的查询,下面是一段伪代码逻辑,大致表现出了处理的逻辑:

for each row in t1 matching range {
  for each row in t2 matching reference key {
    for each row in t3 {
      if row satisfies join conditions, send to client
    }
  }
}

既然是嵌套循环,那就可以考虑是不是尽力让小循环嵌套大循环更好一点,毕竟内层循环如果是利用索引查找,时间复杂度是较低的。

下面这个案例可以很好的体现这一点,t_user_1表的数据比t_user_2表的数据要多。
image.png

加上straight join关键字后,可以让MySQL按照指定的顺序做表关联查询。
image.png

所以在做关联查询时,找到合适的关联顺序是非常关键的,但如果关联的表比较多时,MySQL也不可能枚举每一种关联的方式(3张表有6种组合,4张表有24种组合),再决定采取哪一种更合适,当关联表的数量超过optimizer_serach_depth时,就会采用一些贪心的计算策略。

解决关联查询的原则

MySQL针对一些关联查询会采取额外的优化方式,比如我们在执行计划能看到:Index Nested-Loop Join、Block Nested Loop、Index Condition Pushdown等,这些优化的核心思想都是尽量减少磁盘I/O的消耗,尤其是随机磁盘I/O的问题,而解决的方式无非就是把数据尽量一次性加载到内存中来进行过滤,过滤前还可以先将数据排序好,必减少随机访问的问题。所以,本质上任何优化方式都还是在围绕三个关键指标来进行选择。

总结

从上一篇文章MySQL索引全解:从理论到实践,打造高效查询的指南,到本篇的MySQL查询原理与优化,我们已经大致将查询优化从理论支撑到实践方式完整的介绍了一遍,在有了这些知识铺垫之后,下一篇文章实践性将会更强,计划将会整理一些日常开发中常见的实际优化案例进行分享,完成从理论到实践的最后一步。

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

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

相关文章

TikTok数字艺术:短视频背后的视觉盛宴

在当今数字时代,社交媒体平台已经成为创意表达和文化传播的重要场所之一。其中,以短视频为代表的形式在TikTok这一平台上崭露头角,为创作者和观众提供了一个数字艺术的舞台。 本文将深入探讨TikTok数字艺术的独特之处,剖析短视频…

idea报错java: 程序包com.alibaba.fastjson不存在,明明存在!

经常从线上拉下来代码后编译运行时会报这个错误。刷新maven也没用,重新导入项目也没用 发现解决方法如下: 找到当前报错文件的路径。找到iml文件 删除它!然后刷新maven 就好了!!! 记录一下我的解决方法&…

酷柚易汛ERP- 备份与恢复操作指南

1、应用场景 该界面只有管理员才会显示,对已有数据的账套进行备份与恢复。 2、操作指南 2.1 开始备份 对当前系统内的所有数据进行备份,备份成功后当前数据则保存至当前服务器上,同时也会在列表内新增一条当前操作的备份文件记录 2.2 上传…

Linux 使用随记

Linux 使用随记 shell 命令行模式登录后所取得的程序被成为shell,这是因为这个程序负责最外层的跟用户(我们)通信工作,所以才被戏称为shell。 命令 1、命令格式 command [-options] parameter1 parameter2 … 1、一行命令中第…

c++分割路径的字符串,得到 目录 文件名 扩展名

简单的做一个c小代码片的记录 c分割了图片的 路径字符串&#xff0c;得到 目录 文件名 扩展名 #include <iostream> using namespace std;int main() {std::string path "E:\\set1_seg\\32.jpg";//index:"\\"在字符串中的位置int index path.find…

如何搭建高性能地图(访问速度快、层级高、免费)

1、下载地图数据源 可以从很多市面上的地图供应商下载地图&#xff0c;如水经注、bigemap等&#xff0c;大部分层级能够达到19级&#xff0c;也即地图的空间分辨率能够达到0.6米&#xff0c;比如下面广州市的数据。如果数据量少&#xff0c;只需要一个城市或者几个城市的数据&…

时间序列预测实战(十三)定制化数据集FNet模型实现滚动长期预测并可视化结果

论文地址->官方论文代码地址 官方代码地址->官方下载地址Github 个人修改代码地址-> 个人修改版本呢的下载地址CSDN 一、本文介绍 本博客将介绍一种新的时间序列预测模型——FNet&#xff0c;它通过使用傅里叶变换代替自注意力机制&#xff0c;旨在解决传统Transf…

利用uni-app 开发的iOS app 发布到App Store全流程

1.0.3 20200927 更新官方对应用审核流程的状态。 注&#xff1a;最新审核后续将同步社区另一篇记录 AppStore 审核被拒原因记录及解决措施 &#xff1a;苹果开发上架常见问题 | appuploader使用教程 1.0.2 20200925 新增首次驳回拒绝邮件解决措施。 1.0.1 20200922 首次…

活动回顾丨“益”起净化江滩,争做环保卫士

“孤帆远影碧空尽&#xff0c;唯见长江天际流。”长江是中国最长、最重要的河流之一&#xff0c;拥有水生生物4300多种&#xff0c;其中鱼类400多种(含亚种)&#xff0c;170多种为长江特有&#xff0c;是世界上水生生物多样性最为丰富的河流之一。为推动长江生态大保护融入公众…

后台系统更改主题【el-color-picker】

Element el-color-picker 颜色选择器的使用 设置的页面 <script setup lang"ts"> import { getThemeColor } from "../../utils/colorChange"; import { useUserStore } from "/store/index"; import { reactive } from "vue"…

Java之“数字困境”:资产管理项目中的Bug追踪与启示

目录 1 前言2 问题的发现3 调试的开始4 深入调试5 调试心得与反思6 结语 1 前言 在程序员的日常工作中&#xff0c;我们时常面对各种令人头疼的问题&#xff0c;其中最令人崩溃的瞬间之一&#xff0c;就是当我们花费大量时间追踪一个看似复杂的bug&#xff0c;最终发现问题的根…

Node-RED系列教程-28修改UI默认背景图片

主要使用到如下节点: 实现过程如下: 步骤1:编写背景图片请求服务. http in节点配置: 读文件节点配置: http out节点配置: 到此,背景图片请求服务搭建完成。

vue中cli组件如何自定义定义

目录 创建自定义组件 注册并使用自定义组件 注册组件&#xff1a; 在需要使用该组件的页面或父组件中注册并引入自定义组件。 使用 Props 传递数据 总结步骤&#xff1a; 前言 在Vue CLI中使用自定义组件是构建交互式和模块化Web应用的重要一环。Vue CLI为开发者提供了使用…

[sd_scripts]之gen_img

https://github.com/kohya-ss/sd-scripts/blob/main/docs/gen_img_README-ja.mdhttps://github.com/kohya-ss/sd-scripts/blob/main/docs/gen_img_README-ja.md1.图像生成 ckpt "/home/image_team/image_team_docker_home/lgd/e_commerce_sd/stable-diffusion-webui/mod…

轻量封装WebGPU渲染系统示例<23>- 可渲染对象添加到多个渲染Pass节点(源码)

渲染和计算混合系统&#xff0c; 可以看做基于算力驱动设计理念的一种实现。 此系统中&#xff0c;可渲染(rendering)/计算(computing)实体可以任意添加到一个渲染器pass节点。若干个这样的节点相关联&#xff0c;就能构成对应的pass node graph&#xff0c;也就实现了整个3D渲…

LDC1000配Pico - 通过不同金属导电性自制金属分类(其实线圈很重要)

话不多说&#xff0c;上接线和代码&#xff0c;用的arduino IDE。 LDC1000 - PicoW5V ------- VSYS GND ------ GND ECK默认不接&#xff0c;板载8M有源晶振时基时钟,如需使用其他外部时钟&#xff0c;请去掉R3&#xff1b; INTB默认不接&#xff1b; SCK ------ GP2 CSB ----…

选择护眼台灯的标准?儿童护眼台灯挑选指南

自从护眼台灯成为学生书桌上最常见的照明灯具后&#xff0c;就经常能看到关于护眼台灯伤眼的信息。从根源上说&#xff0c;是各种不专业产品混杂在市场中&#xff0c;这种产品技术较差&#xff0c;而且为减少成本选择品质差的材料&#xff0c;但由于低廉的价格仍然吸引了众多消…

YOLO V1中关于bounding boxs的部分要点

YOLO的核心原理预览 YOLO将输入的图片resize成448 x 448&#xff0c;并且为 S x S&#xff08;S 7&#xff09;个grid&#xff0c;如果物体的中心落入该grid中&#xff0c;那么该grid就需要负责检测该物体。一次性输出所检测到的目标信息&#xff0c;包括类别和位置。 对于每一…

【4】Gradle-快速入门使用【Gradle多模块项目详解】

目录 【4】Gradle-快速入门使用【Gradle多模块项目详解】创建多项目构建添加子项目命名建议 项目依赖项项目路径不同模块的build.gradle配置 子项目之间共享构建逻辑公约插件跨项目配置buildSrc开发公约插件 调整多模块项目配置修改项目树的元素 了解Gralde配置时间和执行时间并…

SparkSQL之Catelog体系

按照SQL标准的解释&#xff0c;在SQL环境下Catalog和Schema都属于抽象概念。在关系数据库中&#xff0c;Catalog是一个宽泛的概念&#xff0c;通常可以理解为一个容器或数据库对象命名空间中的一个层次&#xff0c;主要用来解决命名冲突等问题。 在Spark SQL系统中&#xff0c;…