文章详情

短信预约-IT技能 免费直播动态提醒

请输入下面的图形验证码

提交验证

短信预约提醒成功

MySQL查询优化之一

2024-12-03 03:57

关注

本文转载自微信公众号「小猪notebook」,作者钟Ger。转载本文请联系小猪notebook公众号。

上一期我们深入理解了MySQL的索引,有了MySQL索引可以一定程度上提高MySQL的查询速度。这一期我们来学习下MySQL查询性能优化的一般方法。

1 为什么查询速度会慢?

其实很简单,在一个应用程序中,我们要查询一些数据,通常是从客户端出发,请求经过网络传输到达服务端后,在服务端进行解析,然后把查询命令发送给MySQL,MySQL经过一系列解析、优化等,最终将结果查询出来,返回给客户端,最终给到我们用户。

在这一系列操作里,由于网络时延、CPU、内存、锁竞争、系统调用、上下文切换、存储引擎检索数据触发的一系列操作等,查询的速度或多或少会受到一定的影响,条件不利时,查询速度就会变慢。

大致地知道查询速度受到这些因素影响后,我们就可以找到优化查询速度的一些方向了。

2 慢查询基础:优化数据访问

查询性能低的最基本原因是访问的数据太多,我们可以通过以下两步来分析低效查询:

2.1 是否向数据库请求了不需要的数据

应用程序从MySQL查询请求了较多不需要数据时,这些多余数据其实会在应用程序的逻辑层中被丢弃掉,这种多余的操作会给MySQL服务器带来额外的负担,并增加网络开销,还会消耗应用服务器的CPU和内存资源。以下几种情况均是这种类型:

2.2 MySQL是否扫描了额外的记录

对于MySQL,衡量性能开销的三个指标是:响应时间、扫描行数、返回行数。

响应时间

响应时间的分类

扫描的行数与返回的行数

理想情况下,扫描的行与返回的行之间的比率通常要小,MySQL额外扫描的记录就少。

扫描的行数与访问类型

在评估查询开销时,需要考虑下从表中找到某一行数据的成本。MySQL有些访问方式可能要扫描很多行才能返回一行结果。

使用explain语句中的type列反应了访问类型。 访问类型有索引扫描、范围扫描、唯一索引查询、常数引用等。

一般MySQL能使用这下列三种方式应用where条件,从好到坏依次为:

如果发现查询需要扫描大量的数据但只返回少数的行,通常可以尝试下面的技巧去优化:

3 重构查询的方式

3.1 一个复杂查询or多个简单查询

MySQL其实在设计上是让连接和断开都很轻量级,在返回一个小的查询结果方面很高效。如果想用一个复杂的查询,而这个查询涉及了多个表的关联,那其实性能还远不如将这个查询分解成的多个简单查询。

因此,一般情况下,能用多个简单查询,就不要用一个复杂查询。

3.2 切分查询

对于一个大查询可以采用分而治之,将大查询切分成小查询,每个查询功能完全一样,只完成一小部分,每次只返回一小部分查询结果。

3.3 分解关联查询

将MySQL多表关联查询拆分成多个单表查询,然后将查询结果在应用程序逻辑层进行处理,可以提升性能。优势如下:

4 查询执行的基础

前面讲了这么多关于查询优化的内容,现在我们了解下MySQL执行一个查询的过程:

查询SQL执行路径

有一个大致的流程后,我们来具体看下每一步的细节。

4.1 MySQL客户端与服务端之间的通信协议

由于客户端与服务端之间传输的数据都必须是要完整可靠的,显然是使用TCP协议来建立连接。

MySQL客户端与服务端需要进行通信,在任意一个时刻,要么是服务端发送数据给客户端,要么是客户端发送数据给服务端,即半双工通信。

这种通信协议让MySQL客户端与服务端之间通信简单,但也限制了MySQL,例如一端必须完整地接受了另外一端发送来的数据,才能够给另外一端响应数据,就当我们使用像DataGrip、Navicat等客户端连接好MySQL服务端时,我们要select * from一张数据量很大的表,那么我们只能等服务端返回结果了。这一个查询请求占用了大量的资源,如果有很多个这样的查询请求,那MySQL服务端的压力肯定是很大的咯。所以,从数据库捞全表的数据而不使用limit加以限制,客户端和服务端都很难顶的。

书中讲到:当客户端从服务端获取数据时,看起来是一个拉数据的过程,实际上是服务端在向客户端推送数据的过程。客户端不断地接受从服务端推送来的数据,且没办法让服务端停下来,像从消防水管喝水一样。

所以通常,使用查询缓存可以减少服务器压力,让查询早点结束并释放相关资源。

查询状态

对于每一个MySQL连接,也可以说一个线程,任意时刻都有一个状态,该状态表示了MySQL当前正在做的事情。

4.2 查询缓存

在解析一个查询语句前,若MySQL的查询缓存功能开启,那么MySQL会优先检查该查询是否命中查询缓存中的数据。如果命中了查询缓存,则返回结果;若未命中,则继续后续流程。

4.3 查询优化处理

查询优化处理分为多个子阶段:解析SQL、预处理、优化SQL执行计划。

4.3.1 语法解析器和预处理

MySQL通过关键字将SQL语句进行解析,生成一棵对应的解析树,MySQL解析器将使用MySQL语法规则验证和解析查询。

预处理器根据MySQL规则进一步检查解析树是否合法,如将检查数据表和列属否存在、解析名字和别名、看看是否有歧义。接下来会进一步验证权限。

4.3.2 查询优化器

查询优化器的主要作用是找到执行一条SQL语句的最好执行计划。MySQL使用基于成本的优化器,它将尝试预测一个查询使用某种执行计划时的成本,并选择其中成本最小的一个。

优化器的评估成本时对要进行的随机IO次数的统计信息计算主要是受到每个表或索引页个数、索引基数、索引分布和数据行的长度、索引分布情况等影响。优化器在评估成本时并不会考虑任何层面的缓存,它假设读取任何数据都需要一次磁盘IO。

MySQL优化器可能选择错误的执行计划的情况:

在这里插入图片描述

MySQL能够处理的优化类型:

4.3.3 关联优化

MySQL在执行连接查询时,往往会先执行子查询,并将子查询的结果存放到一个临时表中,然后将临时表中的结果当作条件来执行父查询。MySQL的优化器会对select a.id, b.name from a inner join b on a.id = b.id之类的关联查询进行优化。优化器会对多个表关联时的顺序进行优化,它通过评估不同顺序时的成本来选择一个代价最小的关联顺序来执行查询。

4.3.4 排序优化

排序是一个成本很高的操作,故从性能上来讲,应该尽可能地避免排序或对大量数据进行排序。当MySQL不能使用索引进行排序时,它需要进行文件排序(数据量小在内存中进行,数据量大需要使用磁盘)。

如果需要排序的数据量小于“排序缓冲区”,MySQL使用内存进行快速排序。如果内存不够排序,MySQL会将数据分块,对每个独立的块使用快速排序,并将各个块的排序结果存放在磁盘上,然后将各个排序的块进行合并,最后返回排序结果。

MySQL 5.6以上,排序的算法是单次传输排序:先读取查询所需要的所有列,然后再根据给定列进行排序,最后直接返回排序结果。虽然这个排序算法只需要一次顺序IO读取所有的数据,但如果需要返回的列非常多、非常大,会额外占用大量的空间,而这些列对排序操作本身是没有什么用的,所以有利有弊吧。

值得一提的是,在关联查询的时候如果需要排序,MySQL会分情况来处理这样的文件排序

如果order by子句中的所有列都来自于关联的第一个表,那么MySQL在关联处理第一个表时就会进行文件排序

除此之外,MySQL会将关联的结果先存放到一个临时表中,然后再进行文件排序

4.4 查询执行引擎

在解析和优化阶段,MySQL将生成查询对应的执行计划,MySQL的查询执行引擎则根据这个执行计划来完成整个查询。查询执行引擎会根据执行计划给出的指令逐步执行。在执行过程中,有大量操作需要通过调用存储引擎实现的接口来完成,接口称为“handler API”。MySQL在优化阶段就为每个表创建了一个handler实例,优化器会根据这些实例的接口获取表的相关信息(列名、索引统计信息等)。

并非所有操作均有handler完成。例如,MySQL需要进行表锁时,handler可能会实现特定级别、更细粒度的锁,如InnoDB就实现了自己的行基本锁,但这并不能代替服务器层的表锁。

4.5 返回结果给客户端

查询执行的最后一个阶段是将结果返回给客户端,即使查询无需返回结果集,MySQL仍然会返回查询的一些信息,例如查询影响到的行数等。

若查询可以被缓存,MySQL返回结果给客户端前会将结果存储到查询缓存中。

MySQL将结果集返回给客户端是一个增量、逐步返回的过程。这样处理的好处是:服务端无需存储太多结果,也不会因为要返回太多结果而消耗太多内存;客户端也能够快速地获取到返回的结果。

结果集中的每一行都会以一个满足MySQL客户端/服务端通信协议的封包发送,然后通过TCP协议传输,在TCP传输过程中,可能对MySQL的封包进行缓存然后批量传输。

小结

本期主要对MySQL的查询过程进行了简要的梳理,理解了一条SQL执行的过程需要经过MySQL的各种组件,下一期,我们将重点探索下MySQL查询性能优化的方法。我是Zhongger,一个在互联网公司摸鱼写代码的打工人,你们的支持是我创作的最大动力,我们下期见~

 

来源:小猪notebook内容投诉

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题
  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     813人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     435人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机
位置:首页-资讯-后端开发
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯