位置: 编程技术 - 正文

探究MySQL优化器对索引和JOIN顺序的选择(mysql优化总结)

编辑:rootadmin

推荐整理分享探究MySQL优化器对索引和JOIN顺序的选择(mysql优化总结),希望有所帮助,仅作参考,欢迎阅读内容。

文章相关热门搜索词:mysql优化实战,mysql实战优化高手,mysql优化器处理哪三个任务,mysql常用优化方案,mysql优化参数详解,mysql常用优化方案,mysql常用优化方案,mysql常用优化方案,内容如对您有帮助,希望把文章链接给更多的朋友!

本文通过一个案例来看看MySQL优化器如何选择索引和JOIN顺序。表结构和数据准备参考本文最后部分"测试环境"。这里主要介绍MySQL优化器的主要执行流程,而不是介绍一个优化器的各个组件(这是另一个话题)。

我们知道,MySQL优化器只有两个自由度:顺序选择;单表访问方式;这里将详细剖析下面的SQL,看看MySQL优化器如何做出每一步的选择。

1. 可能的选择

这里看到JOIN的顺序可以是A|B或者B|A,单表访问方式也有多种,对于A表可以选择:全表扫描和索引`IND_L_D`(A.LastName = 'zhou')或者`IND_DID`(B.DepartmentID = A.DepartmentID)。对于B也有三个选择:全表扫描、索引IND_D、IND_DN。2. MySQL优化器如何做2.1 概述

MySQL优化器主要工作包括以下几部分:Query Rewrite(包括Outer Join转换等)、const table detection、range analysis、JOIN optimization(顺序和访问方式选择)、plan refinement。这个案例从range analysis开始。2.2 range analysis

这部分包括所有Range和index merge成本评估(参考1 参考2)。这里,等值表达式也是一个range,所以这里会评估其成本,计算出found records(表示对应的等值表达式,大概会选择出多少条记录)。

本案例中,range analysis会针对A表的条件A.LastName = 'zhou'和B表的B.DepartmentName = 'TBX'分别做分析。其中:

表A A.LastName = 'zhou' found records: 表B B.DepartmentName = 'TBX' found records: 1

这两个条件都不是range,但是这里计算的值仍然会存储,在后面的ref访问方式评估的时候使用。这里的值是根据records_in_range接口返回,而对于InnoDB每次调用这个函数都会进行一次索引页的采样,这是一个很消耗性能的操作,对于很多其他的关系数据库是使用"直方图"的统计数据来避免这次操作(相信MariaDB后续版本也将实现直方图统计信息)。2.3 顺序和访问方式的选择:穷举

MySQL通过枚举所有的left-deep树(也可以说所有的left-deep树就是整个MySQL优化器的搜索空间),来找到最优的执行顺序和访问方式。2.3.1 排序

优化器先根据found records对所有表进行一个排序,记录少的放前面。所以,这里顺序是B、A。2.3.2 greedy search

当表的数量较少(少于search_depth,默认是)的时候,这里直接蜕化为一个穷举搜索,优化器将穷举所有的left-deep树找到最优的执行计划。另外,优化器为了减少因为搜索空间庞大带来巨大的穷举消耗,所以使用了一个"偷懒"的参数prune_level(默认打开),具体如何"偷懒",可以参考JOIN顺序选择的复杂度。不过至少需要有三个表以上的关联才会有"偷懒",所以本案例不适用。2.3.3 穷举

探究MySQL优化器对索引和JOIN顺序的选择(mysql优化总结)

JOIN的第一个表可以是:A或者B;如果第一个表选择了A,第二个表可以选择B;如果第一个表选择了B,第二个表可以选择A;

因为前面的排序,B表的found records更少,所以JOIN顺序穷举时的第一个表先选择B(这个是有讲究的)。

(*) 选择第一个JOIN的表为B (**) 确定B表的访问方式 因为B表为第一个表,所以无法使用索引IND_D(B.DepartmentID = A.DepartmentID),而只能使用IND_DN(B.DepartmentName = 'TBX') 使用IND_DN索引的成本计算:1.2;其中IO成本为1。 是否使用全表扫描:这里会比较使用索引的IO成本和全表扫描的IO成本,前者为1,后者为2;所以忽略全表扫描 所以,B表的访问方式ref,使用索引IND_D

(**) 从剩余的表中穷举选出第二个JOIN的表,这里剩余的表为:A (**) 将A表加入JOIN,并确定其访问方式 可以使用的索引为:`IND_L_D`(A.LastName = 'zhou')或者`IND_DID`(B.DepartmentID = A.DepartmentID) 依次计算使用索引IND_L_D、IND_DID的成本: (***) IND_L_D A.LastName = 'zhou' 在range analysis阶段给出了A.LastName = 'zhou'对应的记录约为:。 所以,计算IO成本为:;ref做IO成本计算时会做一次修正,将其修正为worst_seek(参考) 修正后IO成本为:,总成本为:.2 (***) IND_DID B.DepartmentID = A.DepartmentID 这是一个需要知道前面表的结果,才能计算的成本。所以range analysis是无法分析的 这里,我们看到前面表为B,found_record是1,所以A.DepartmentID只需要对应一条记录就可以了 因为具体取值不知道,也没有直方图,所以只能简单依据索引统计信息来计算: 索引IND_DID的列A.DepartmentID的Cardinality为,全表记录数为 所以,每一个值对应一条记录,而前面表B只有一条记录,所以这里的found_record计算为1*1 = 1 所以IO成本为:1,总成本为1.2 (***) IND_L_D成本为.2;IND_DID成本为1.2,所以选择后者为当前表的访问方式 (**) 确定A使用索引IND_DID,访问方式为ref (**) JOIN顺序B|A,总成本为:1.2+1.2 = 2.4

(*) 选择第一个JOIN的表为A (**) 确定A表的访问方式 因为A表是第一个表,所以无法使用索引`IND_DID`(B.DepartmentID = A.DepartmentID) 那么只能使用索引`IND_L_D`(A.LastName = 'zhou') 使用IND_L_D索引的成本计算,总成本为.2;参考前面计算; (**) 这里访问A表的成本已经是.2,比之前的最优成本2.4要大,忽略该顺序 所以,这次穷举搜索到此结束

把上面的过程简化如下:

(*) 选择第一个JOIN的表为B (**) 确定B表的访问方式 (**) 从剩余的表中穷举选出第二个JOIN的表,这里剩余的表为:A (**) 将A表加入JOIN,并确定其访问方式 (***) IND_L_D A.LastName = 'zhou' (***) IND_DID B.DepartmentID = A.DepartmentID (***) IND_L_D成本为.2;IND_DID成本为1.2,所以选择后者为当前表的访问方式 (**) 确定A使用索引IND_DID,访问方式为ref (**) JOIN顺序B|A,总成本为:1.2+1.2 = 2.4

(*) 选择第一个JOIN的表为A (**) 确定A表的访问方式 (**) 这里访问A表的成本已经是.2,比之前的最优成本2.4要大,忽略该顺序

至此,MySQL优化器就确定了所有表的最佳JOIN顺序和访问方式。3. 测试环境

4. 构造一个Bad case

因为关联条件中MySQL使用索引统计信息做成本预估,所以数据分布不均匀的时候,就容易做出错误的判断。简单的我们构造下面的案例:

表和索引结构不变,按照下面的方式构造数据:

可以看到这里,MySQL执行计划对表department使用了索引IND_D,那么A表命中一条记录为(zhou,);根据B.DepartmentID=将返回条记录,然后根据条件DepartmentName = 'TBX'进行过滤。

这里可以看到如果B表选择索引IND_DN,效果要更好,因为DepartmentName = 'TBX'仅仅返回条记录,再根据条件A.DepartmentID=B.DepartmentID过滤之。

使用Python的Django框架中的压缩组件Django Compressor 为了加快网站的加载速度,我们通常要多js和css进行压缩处理。这些js和css的压缩工作如果都手动处理,费时费力。DjangoCompressor可以实现js/css的自动压缩

查找MySQL线程中死锁的ID的方法 如果遇到死锁了,怎么解决呢?找到原始的锁ID,然后KILL掉一直持有的那个线程就可以了,但是众多线程,可怎么找到引起死锁的线程ID呢?MySQL发展到

用Autoconf检测MySQL软件包的教程 在你的程序(或者工程)中,如果编译阶段需要检测当前环境中是否存在MySQL客户端相关的库文件时,你可以使用Autoconf来帮你完成这个工作,轻盈、优雅

标签: mysql优化总结

本文链接地址:https://www.jiuchutong.com/biancheng/347695.html 转载请保留说明!

上一篇:在MySQL中使用STRAIGHT_JOIN的教程

下一篇:使用Python的Django框架中的压缩组件Django Compressor(python中的django)

  • 债务承担规定是什么意思
  • 安全生产费实际发生必须是付款吗还是挂帐也可以
  • 固定资产盘盈影响所有者权益吗
  • 全额工资是到手工资吗
  • 付款人常驻国家(地区)代码及名称RU
  • 流动负债占总负债比率较高说明什么
  • 维保税率和维修税率是多少
  • 外商投资企业与内资合资是国企吗
  • 外商投资企业土地使用税什么时候交
  • 股息交个人所得税怎么交
  • 直系亲属之间转账要交税吗
  • 怎么调整应收账款账龄
  • 企业支付劳务费到底需要发票吗
  • 税收优惠政策没有任何法律风险
  • 办公室零食知乎
  • 购买标书的费用计入什么科目
  • 增值税的调增调减
  • 收到专票有误,已跨月未认证,销售方不红冲咋办
  • 城建税上月少计提本月怎么做账
  • 生产领用产成品验证会计分录
  • 期末留抵税额可以冲减欠税吗
  • ’sass_binary_site‘ is not a valid npm option问题的产生原因及解决办法
  • 利息股息红利所得属于综合所得吗
  • 代垫运费会计分录怎么写
  • 预提费用是啥
  • 高薪技术企业研发项目合同范本最新
  • 缅因州达马里斯科塔地区的佩马基德灯塔 (© Tom Whitney/Adobe Stock)
  • 索尼体积最小的微单
  • chat function
  • 苏黎世湖天鹅
  • laravel php
  • thinkPHP模板不存在抛出异常
  • php cli 多线程
  • 用php开发app
  • ts入门教程
  • MySQL数据库存储引擎
  • mysql的使用实验总结与分析
  • 帝国cms插件编写教程
  • 没有发票怎么做收入
  • 未抵扣的进项发票,开出红字信息表,需要做进项税转出吗
  • 一般纳税人的账务处理分录
  • 增值税发票是记账联还是抵扣联
  • 个税出现负数是什么意思
  • 小企业会计准则主要按照什么计量
  • sql查询服务器硬件信息
  • 分页存储过程是什么
  • mysql数据数据库
  • 在建工程增多
  • 固定资产折旧提头不提尾
  • 没有收入是纳税人吗
  • 物业公司代收电费可以差额征税吗
  • 已认证发票退回的流程
  • 应收帐款收不回来怎么做会计分录
  • 酒店营业成本率怎么算
  • 应收账款和应付账款属于什么科目
  • 明细分类账的格式与登记方法
  • win8系统开机界面
  • windows中的帐户类别administrator为
  • linux怎么调整屏幕大小
  • winproj.exe - winproj进程是什么意思
  • win7系统的磁盘管理在哪里,怎么打开
  • linux系统怎么共享
  • linux shell 数字转字符串
  • 使用Apache&花生壳架设Web服务器
  • cocos2dx4.0教程
  • TestOpenGL
  • android 自定义dialog
  • shell程序中定义的函数能不能有参数
  • linux中命令行
  • 服务器总是自动关机
  • 浅谈python装饰器探究与参数的领取
  • node.js做服务器
  • macos如何使用
  • jQuery解析XML 详解及方法总结
  • 原生js实现ajax步骤
  • mvp 框架
  • 北京孩子社保网上怎么缴费
  • 如何查询税务是否签订三方
  • 某地区土拍的楼盘有哪些
  • 教育用地性质可以更改么
  • 免责声明:网站部分图片文字素材来源于网络,如有侵权,请及时告知,我们会第一时间删除,谢谢! 邮箱:opceo@qq.com

    鄂ICP备2023003026号

    网站地图: 企业信息 工商信息 财税知识 网络常识 编程技术

    友情链接: 武汉网站建设