linuxsir首页 LinuxSir.Org | Linux、BSD、Solaris、Unix | 开源传万世,因有我参与欢迎您!
网站首页 | 设为首页 | 加入收藏
您所在的位置:主页 > Linux数据库 >

MySQL查询执行的基础——查询优化处理

时间:2019-01-26  来源:未知  作者:admin666

查询的生命周期的下一步是将一个SQL转换成一个可执行计划,MySQL再按照这个计划和存储引擎进行交互

语法解析器和预处理

首先,MySQL通过关键词将SQL语句进行解析,并生成一颗对应的 解析树 。MySQL解析器将使用MySQL语法规则验证和解析查询。比如是否使用了错误的关键字,关键字的顺序是否正确,或者引号是否能够正确的前后匹配。

预处理器则会根据一些MySQL规则进一步检查解析树是否合法。它会检查数据表和数据列是否存在,还会解析名字和别名,看看它们是否存在歧义。

查询优化器

当语法树被认为是合法的时候,将转由优化器去转化成执行计划。一条查询可以有很多种执行方式,最后都返回相同的结果。优化器的作用就是找到这其中最好的执行计划。

MySQL使用的是基于成本的优化器,它将会尝试预测一个查询使用某种执行计划时的成本,并选择其中成本最小的那一个。可以通过查询当前会话的 Last_query_cost 的值来的值MySQL计算的当前查询的成本。

优化器估算查询成本

这个结果表示MySQL的优化器认为大概需要做1040个数据页的随机查找才能够完成上面的查询。这是根据一系列的统计信息计算的来的。优化器在评估成本时并不会考虑任何层面的缓存,它假设读取任何数据都需要一次磁盘I/O。

有很多种情况会导致MySQL优化器选择错误的执行计划,如下所示:

统计信息不准确。 MySQL依赖存储引擎提供的统计信息来评估成本,但是有的存储引擎提供的信息偏差非常大。比如InnoDB因为使用了MVCC架构,并不能维护一个数据表的行数的精确统计信息。 执行计划中的成本估算不等同于实际执行的成本。 所以及时统计信息精准,优化器给出的执行计划也可能不是最优的。比如某个执行计划虽然需要读取很多的页面,但是如果这些页面都是顺序读取获取已经在内存中了的话,那么访问它的成本将非常小。 MySQL的最优可能和我们想的最优是不同的。 我们理解的最优是执行时间尽可能短,但是MySQL只是基于其成本模型选择最优的执行计划,而有些时候并不是最快的执行方式。 MySQL从不考虑其他正在并发执行的查询,这可能会影响当前查询的速度。 MySQL并不是任何时候都基于成本的优化。 有时候它也会基于一些固定的规则,比如存在全文搜索的MATCH()子句时,MySQL会选择使用全文索引而不是使用其他更快的索引或者WHERE条件。 MySQL不会考虑不受其控制的操作的成本,比如执行存储过程或者用户自定义函数的成本。 优化器有时候无法去估算所有可能的执行计划,所以可能会错过实际上最优的执行计划。

MySQL的查询优化器是一个非常复杂的不见,它使用了很多优化策略来生成一个最优的执行计划。优化策略可以简单的分为两种,一种是静态优化,一种是动态优化。

静态优化可以直接对解析树进行分析,并完成优化。例如通过一些简单的袋鼠变换将WHERE条件转换成另一种等价形式,可以认为是一种 编译时优化 。

动态优化则是和查询的上下文相关,也可能和很多其他因素有关,这些需要在每次查询时重新评估,可以认为是 运行时优化

在执行语句和存储过程的时候,动态优化和静态优化的区别很重要。MySQL对查询的静态优化只需要做一次,但是对查询的动态优化则在每次执行时都需要重新评估。有时候甚至在查询的执行过程中也会重新优化。

下面是一些MySQL可以处理的优化类型:

重新定义关联表的顺序 将外连接转化为内连接 使用等价变换规则。 它可以合并和减少一些比较,还可以移除一些恒成立和一些恒不成立的判断。 优化COUNT()、MIN()和MAX()。 索引和列是否为空通常可以帮助MySQL优化这类表达式。比如需要找到某一列的最小值,只需要查询对应B-Tree索引的最左端记录即可。 预估并转化为常量表达式。 当MySQL检测到一个表达式可以转化为常数时,就会一直把该表达式作为常数进行优化处理。 覆盖索引扫描。 当索引中的列包含所有查询中需要使用的列的时候,MySQL就可以使用索引返回需要的数据,而无需查询对应的数据行。 子查询优化 提前终止查询。 当发现已经满足查询需求的时候,MySQL总是能够立刻终止查询。一个典型的例子就是当使用了LIMIT 子句的时候。 等值传播。 如果两个列的值通过等式关联,那么MySQL能够把其中一个列的WHERE条件传递到另一列上。 列表IN()的比较。 在很多的数据库系统中,IN()完全等同于多个OR条件的子句,因为这两者是完全等价的。但是在MySQL中,它将IN()列表中的数据先进行排序,然后通过二分查找的方式来确定列表中的值是否满足条件,这是一个O(log n)复杂度的操作,等价转换为OR查询的复杂度为O(n)。对于IN()列表中有大量取值的时候,MySQL的处理速度会更快。

上面列举的并不是MySQL优化器的全部,MySQL还会做其他大量的优化,因此我们完全没有必要尝试 自己会比优化器更加聪明 ,这样不仅会让查询更加复杂而难以维护,并且最终收益可能为0.让优化器按照自己的方式正常工作即可。

但是如果能够确认优化器给出的并不是最佳选择,并且清除背后的原理那么也可以尝试帮助优化器作进一步的优化。比如在查询中添加hint提示,也可以重写查询或者重新设计库表结构。

数据和索引的统计信息

MySQL在服务器层有查询优化器,但是没有保存数据和索引的统计信息。统计信息由存储引擎实现,不同的存储引擎可能会存储不同的统计信息或者按照不同的格式存储统计信息。

MySQL如何执行关联操作

MySQL中的 关联(join) 比一般意义上理解的更加广泛。总的来说,MySQL认为任何一个查询都是一次 关联 并不仅仅是一个查询需要用到两张表的匹配才叫关联。

以UNION查询为例MySQL先将一系列的单个查询结果放到一个临时表中,然后再重新读出临时表数据来完成UNION查询。

当前MySQL关联执行的策略如下:MySQL对任何关联都执行嵌套循环关联操作,即MySQL现在一个表中循环取出单条数据,然后再嵌套循环到下一个表中寻找匹配的行,依次下去,直到找到所有表中匹配的行为止。然后根据各个表匹配的行,返回查询中需要的各个列。MySQL会尝试在最后一个关联表中查找到所有匹配的行,如果最后一个关联表无法找到更多的行以后,MySQL就会返回上一层次关联表,看是否能够找到更多的匹配记录,以此类推迭代执行。

和很多其他关系型数据库不同,MySQL并不会生成查询字节码来执行查询。MySQL生成查询的一棵指令书,然后通过存储引擎执行完成这棵指令书并返回结果。最终的执行计划包含了重构查询的全部信息

关联查询优化器

MySQL优化器最重要的一部分就是关联查询优化,它决定了多个表关联时的顺序。通常多表关联时,可以有多种不同的关联顺序来获得相同的结果。

关联查询优化器则通过评估不同顺序时的成本来选择一个代价最小的关联顺序。它会遍历每一个表然后逐个做嵌套循环计算每一棵可能的执行计划树的成本,最后返回一个最优的执行计划。

然而,如果有超过n个表的关联,那么需要检查n的阶乘种关联顺序。这被称为可能的执行计划的 搜索空间 ,搜索空间的增长速度非常快,如果我们需要关联10个表,那么共有3628800种不同的关联顺序。

当搜索空间非常大时,优化器会选择使用 贪婪 搜索的方式查找 最优 的关联顺序。有时候,各个查询的顺序是不能随意安排的,比如左连接等,这时候关联优化器就可以根据这些规则大大减少搜索空间。

无论如何排序都是一个成本很高的操作,所以从性能上看,应该尽可能避免排序或者尽可能避免对大量数据进行排序。

当不能使用索引生成排序结果的时候,MySQL需要自己进行排序。如果数据量小则在内存中进行,如果数据量大则需要使用磁盘,不过MySQL将这个过程统一称为文件排序(filesort),即使完全是内存排序不需要任何磁盘文件时也是如此。

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

Linux公社的RSS地址:https://www.linuxidc.com/rssFeed.aspx

友情链接
  • Mozilla发布Firefox 67.0.4,修复沙箱逃逸漏洞
  • 蚂蚁金服正式成为CNCF云原生计算基金会黄金会员
  • Firefox 68将采用Microsoft BITS安装更新
  • OpenSSH增加对存储在RAM中的私钥的保护
  • 谷歌想实现自己的curl,为什么?
  • Raspberry Pi 4发布:更快的CPU、更大的内存
  • Firefox的UA将移除CPU架构信息
  • Ubuntu放弃支持32位应用程序实属乌龙,Steam会否重回Ubuntu怀抱
  • Qt 5.13稳定版发布:引入glTF 2.0、改进Wayland以及支持Lottie动
  • 红帽企业Linux 7现已内置Redis 5最新版
  • Slack进入微软内部禁用服务清单,GitHub也在其列?
  • 安全的全新编程语言V发布首个可用版本
  • Windows Terminal已上架,快尝鲜
  • 阿里巴巴微服务开源生态报告No.1
  • 面世两年,Google地球将支持所有基于Chromium的浏览器
  • 推进企业容器化持续创新,Rancher ECIC千人盛典完美收官
  • CentOS 8.0最新构建状态公布,或于数周后发布
  • Debian移植RISC
  • 微软拆分操作系统的计划初现雏形
  • Oracle发布基于VS Code的开发者工具,轻松使用Oracle数据库
  • Ubuntu 19.10停止支持32位的x86架构
  • 微软为Windows Terminal推出全新logo
  • 联想ThinkPad P系列笔记本预装Ubuntu系统
  • 微软发布适用于Win7/8的Microsoft Edge预览版
  • 启智平台发布联邦学习开源数据协作项目OpenI纵横
  • 经过六个多月的延迟,微软终于推出Hyper
  • ZFS On Linux 0.8.1 发布,Python可移植性工作
  • DragonFly BSD 5.6.0 发布,HAMMER2状态良好
  • Linux Kernel 5.2
  • CentOS 8.0 看起来还需要几周的时间
  • 百度网盘Linux版正式发布
  • PCIe 6.0宣布:带宽翻倍 狂飙至256GB/s
  • PHP 7.4 Alpha 发布,FFI扩展,预加载Opcache以获得更好的性能
  • Canonical将在未来的Ubuntu版本中放弃对32位架构的支持
  • Scala 2.13 发布,改进的编译器性能
  • 微软的GitHub收购了Pull Panda,并且使所有订阅完全免费
  • Windows Subsystem for Linux 2 (WSL 2)现在适用于Windows 10用
  • Debian 10 “Buster”的RISC
  • MariaDB宣布发布MariaDB Enterprise Server 10.4
  • DXVK 1.2.2 发布,带来微小的CPU开销优化
  • DragonFlyBSD 5.6 RC1 发布,VM优化,默认为HAMMER2
  • PrimeNG 8.0.0 发布,支持Angular 8,FocusTrap等
  • GIMP 2.10.12 发布,一些有用的改进
  • 清华大学Anaconda 镜像服务即将恢复
  • Debian GNU/Linux 10 “Buster” 操作系统将于2019年7月6日发布
  • 时时彩论坛
  • 五星体育斯诺克
  • 北单比分直播
  • 河北11选5走势图
  • 福建体彩36选7开奖结果
  • 九龙图库下载