位置: 编程技术 - 正文

探究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)

  • 增值税抵扣了还能作废吗
  • 个人所得税专项扣除子女教育标准
  • 票据承兑和贴现市场上最主要的交易对象是( )
  • 年度纳税总额的时间怎么计算
  • 记账软件收费吗
  • 会计凭证装订放哪些报表
  • 企业电子支付
  • 30人以上的企业有哪些
  • 公司回购房产契税谁交
  • 其他综合收益转入留存收益还是投资收益
  • 现金日记账定金和实收怎么记
  • 我的初级备考经验--跟老师节奏走
  • 顾客退货补差价怎么做账?
  • 车间领用低值易耗品记账凭证
  • 调财务报表怎样调整
  • 非公益性捐赠支出是永久性差异吗
  • 履约保证金需纳什么税
  • 成本票最多开多少
  • 个人能去税务局开普票吗
  • 印花税滞纳金计入什么科目
  • 如何正确理解新制度经济学与新古典经济学的关系
  • 特许权使用费怎么入账
  • 未到期汇票要怎么进行税务处理?
  • 应收账款坏账准备计算表
  • 记账凭证填制的内容
  • 找私人贴现怎么做账
  • 小微企业所得税减免政策2023
  • 企业收到银行利息收入2000元会计分录
  • 跨期费用会计处理
  • 出口退税服务
  • 待认证进项税额借方余额表示什么
  • 三大期间费用属于什么类
  • 工厂院子里的地图怎么画
  • 保险赔款确认函
  • 个人独资企业公账转私账
  • 以前年度损益调整借贷方向
  • 股份支付如何缴纳个人所得税?
  • 员工提前预支工资借条
  • 固定资产可以直接入账吗
  • 图像超分辨率重建数据集
  • python单子
  • flip函数
  • php解析xml文件
  • 3d沙盒游戏推荐
  • 逾期未退押金是否确认收入
  • 刷题笔贴吧
  • 选择简易计税方法按照5%的征收率计税公式
  • 其他应收款个人挂账很大该怎么处理
  • BootStrap--selectpicker的使用
  • 事业单位以固定工资为准
  • 个税返还款增值税税率
  • mysql重置
  • 材料的采购成本构成
  • 提前预支工资怎么打条子
  • 河道维护中心
  • 销售产品收取的价款
  • 车子租赁费开什么发票?多少税
  • mysql在查询结果中查询
  • macos越狱教程
  • linux 清理
  • linux/tmp
  • repair.exe是什么软件
  • 如何创作图文并茂的图片
  • qqexternal.exe是什么进程如何删除(CPU的使用率在90%)
  • win10无线网络不见了只显示飞行模式
  • 自建ss
  • linux wordpress gzip压缩开启方法
  • linux 如何查看
  • win7系统怎么查看内存
  • 关于node.js中的模块化规范
  • css div模糊
  • 重写SimpleAdapter的getView以实现按钮点击响应
  • javascript判断浏览器
  • Javascript & DHTML 实例编程(教程)(四)初级实例篇2—动画
  • jquery去除class
  • 深圳税务网上申报客户端更正申报流程
  • 个体工商户票种核定金额应该填多少
  • 新疆伊犁水费怎么交
  • 四川增值税发票图片
  • 贵州税务申报如何网上申报
  • 免责声明:网站部分图片文字素材来源于网络,如有侵权,请及时告知,我们会第一时间删除,谢谢! 邮箱:opceo@qq.com

    鄂ICP备2023003026号

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

    友情链接: 武汉网站建设