我要投搞

标签云

收藏小站

爱尚经典语录、名言、句子、散文、日志、唯美图片

当前位置:2019跑狗图高清彩图 > 执行开销 >

MySQL · 引擎特性 · Cost Model直方图及优化器开销优化

归档日期:08-15       文本归类:执行开销      文章编辑:爱尚语录

  MySQL当前已经发布到MySQL8.0版本,在新的版本中,可以看到MySQL之前被人诟病的优化器部分做了很多的改动,由于笔者之前的工作环境是5.6,最近切换到最新的8.0版本,本文涵盖了一些本人感兴趣的和优化器相关的部分,主要包括MySQL5.7的cost model以及MySQL8.0的直方图功能。

  为什么需要配置cost model常量 ? 我们知道MySQL已经发展了好几十年的历史,但是在优化器中依然使用了hardcode的权重值来衡量io, cpu等资源情况,而这些权重值实际上是基于多年前甚至十来年前的经验设定的。想想看,这么多年硬件的发展多么迅速。几十上百个核心的服务器不在少数甚至在某些大型公司大规模使用,ssd早就成为主流,NVME也在崛起。高速RDMA网络正在走入寻常百姓家。这一切甚至影响到数据库系统的实现和变革。显而易见,那些hardcode的权值已经过时了,我们需要提供给用户可定义的方式,甚至更进一步的,能够智能的根据硬件环境自动设定。

  MySQL5.7引入两个新的系统表, 通过这两个系统表暴露给用户来进行更新,如下:

  可以看到用法也非常简单,上面包含了两张表:server_cost及engine_cost,分别对server层和引擎层进行配置

  全局cache维护了一个当前的cost model信息, 用户线程在lex_start时会去判断其有没有初始化本地指针,如果没有的话就去该cache中将指针拷贝到本地

  由于用户可以动态的更新系统表,执行完flush optimizer_costs后,有可能老的版本还在被某些session使用,因此需要引用计数,老的版本ref counter被降为0后才能被释放

  统一的对象类型cost_estimate来存储计算的cost结果,包含四个维度:

  目前来看,除非根据工作负载,经过充分的测试才能得出合理的配置值,但如何配置,什么是合理的值,个人认为应该是可以自动调整配置的。关键是找出配置和硬件条件的对应关系。 这也是我们未来可以努力的一个方向。

  直方图也是MySQL一个万众期待的功能了,这个功能实际上在其他数据库产品中是很常见的,可以很好的指导优化器选择执行路径。利用直方图存储了指定列的数据分布。MariaDB从很早的10.0.2版本支持这个功能, 而MySQL在最新的8.0版本中也开始支持

  从输出的json可以看到,在执行了上述语句后产生的直方图,有4个bucket,数据类型为Int, 类型为equi-height,即等高直方图(另外一种是等宽直方图,即SINGLETON)。每个Bucket中,描述的信息包括:数值的上界和下界, 频率以及不同值的个数。通过这些信息可以获得比较精确的数据分布情况,从而优化器来根据这些统计信息决定更优的执行计划。

  如果列上存在大量的重复值,那么MySQL也可能选择等宽直方图,例如上例,我们将列k上的值更新为一半10一半为20, 那么出来的直方图数据如下:

  如上,对于SINGLETON类型,每个bucket只包含两个值:列值,及对应的累计频率(即百分之多少的数据比当前Bucket里的值要小或相等)

  注意这里的sampling-rate, 这里的值为1,表示读取了表上所有的数据来进行统计,但通常对于大表而言,我们可能不希望读太多的数据,因为可能产生过度的内存消耗,因此MySQL还提供了一个参数histogram_generation_max_mem_size来限制内存的使用上限。

  如果表上的DML不多,那直方图基本是稳定的,但频繁写入的话,那我们就可能需要去定期更新直方图,MySQL本身不会去主动更新。

  优化器通过histogram来计算列的过滤性,大多数的谓词都可以使用到。具体参阅官方文档

  通过直方图,我们可以根据列上的条件判断出列值的过滤性,来辅助选择更优的执行计划。在没有直方图之前我们需要通过在列上建立索引来获得相对精确的列值分布。但我们知道索引是有很大的维护开销的,而直方图则可以灵活的按需创建。

  MySQL通过rec_per_key 接口来估算记录的个数(暗示每个索引Key对应的记录个数),但在早前版本中这个数字是整数,对于小数会取整,不能表示准确的rec_per_key,从而影响到索引的选择,因此在5.7版本中,将其记录的值改成了float类型

  在之前的版本中,优化器是无法知道数据的状态,是否是cache在内存中,还是需要从磁盘读出来的,缺乏这部分信息,导致优化器统一认为数据属于磁盘的来计算开销。这可能导致低效的执行计划。

  server层新增api,用于获取表或索引上有百分之多少的数据是存储在cache中的

  为了减少性能的影响,计数器是通过lock-free hash的结构存储的,对应的结构为ut_lock_free_hash_t。 基本的实现思路是:hash是一个定长的数组,数组元素为(key, val), 根据Key计算一个hash值再模上array size, 找到对应的槽位, 如果槽位被占用了,则向右查找一个空闲的slot。 当数组满了的时候,会创建一个新的更大的数组,在数据还没Move到这个新hash之前,所有的search都需要查询两个数组。当所有的记录到迁移到新数组,并且没有线程访问老的数组时,就可以把老的hash删除掉了。

  在hash中存储的counter本身,也考虑到多核和numa架构,避免同时更新引起的cpu cache失效。在大量core的场景下这个问题可能很明显。Innodb封装计数操作到类ut_lock_free_cnt_t中,使用数组维护counter, 按照cpu no作为index更新,需要获取counter值时则累加数组中的值。

  这个Lock free hash并不是个通用场景的hash结构:例如处理冲突的时候,可能占用其他key的槽位,hash不够用时,需要迁移到新的array中。实际上mysql本身实现了一个lf_hash,在扩展Hash时无需迁移数据,有空单独开篇博客讲一下。

  当定义好接口,并且Innodb提供相应的统计数据后,优化器就可以利用这些信息来计算开销:

  成本模型,也有叫做代价模型,原文是CostModel,下面翻译都使用成本模型。 8.9.5优化器的成本模型SQL查询的方式多种多样,MySQL的优化器使用基于对查询成本进行预估的成本模型来生成执行方案...博文来自:lkforce

  MySQL5.6提供了对SQL的跟踪trace,通过trace文件能够进一步了解为什么优化器选择A执行计划而不是选择B执行计划,帮助我们更好地理解优化器行为。  使用方式:首先打开trace,设置格...博文来自:taojin12的博客

  小谈mysql存储引擎优化平时我们在写某个sql时,总会用Explain来查看一下执行计划,看看自己的sql是否和预期一样。执行计划分析也是我们常需要准备技能之一。无论哪种DBMS,决定查询执行计划的...博文来自:vipshop_fin_dev的博客

  摘要:MySQL当前已经发布到MySQL8.0版本,在新的版本中,可以看到MySQL之前被人诟病的优化器部分做了很多的改动,由于笔者之前的工作环境是5.6,最近切换到最新的8.0版本,本文涵盖了一些本...博文来自:weixin_34378969的博客

  1、优化器是oracle数据库中内置的一个核心子系统,也可以把它理解成是oracle数据库中的一个核心的模块或者一个核心功能组件,优化器的目的是为了得到目标sql的执行计划2、oracle数据库里的优...博文来自:天行健君子以自强不息

  查询优化器是指生成查询计划的子系统,该子系统通常完全处于服务器端,根据要参与连接(join)的表、数据读取方式(所殷读取或表扫描)和索引选择等因素制定查询计划。以基于开销的优化器为例,数据库查询优化器...博文来自:Wake_me_Up123的博客

  TensorFlow里面针对代价函数的训练优化器有多个下面针对各个优化器来分析比较他们各自的特点...博文来自:qxdoit的博客

  sql优化器会重写sql  sql在执行时,并不一定就会按照我们写的顺序执行,mysql优化器会重写sql,如何才能看到sql优化器重写后的sql呢?这就要用到explainextended和show...博文来自:wangjun5159的专栏

  文章目录1.分别传入两个模型的参数2.实现网络不同模块按不同lr进行学习的要求1.分别传入两个模型的参数举例说明:model_one只是一个提取特征的网络,得到features,model_two对f...博文来自:怡宝2号

  这是MaxCompute有关SQL优化器原理的系列文章之一。我们会陆续推出SQL优化器有关优化规则和框架的其他文章。添加钉钉群“关系代数优化技术”(群号11719083)可以获取最新文章发布动态。本文...博文来自:weixin_34148456的博客

  Mysql在执行时,并不一定就会按照我们写的顺序执行,同时也不需要我们人为的从左到右的安排where后面的条件,mysql优化器会重写sql,如何才能看到mysql优化器重写后的sql呢?此时需要参考...博文来自:臣本布衣,躬耕于南阳

  引用连接优化器分类传统关系型数据库里面的优化器分为CBO和RBO两种。RBO—Rule_Base...博文来自:天宝的博客

  本篇文章主要是讲下查询的局限性java高级教程–安全博文来自:shareing

  本文首发于个人的知识星球,参考了网络上各类文章,感谢这些朋友们留下的宝贵材料和分享,让每个热爱SQL的人学到了更加深入的知识。本篇足足有10945字左右,大概需要花费半小时,感谢你的阅读!以下是正文S...博文来自:wujiandao的专栏

  OptimizerImplementation(PartI)背景在讲述这个优化器的时候,就必须先了解查询过程。在本系列的数据库四:浅谈数据库查询过程(QueryProcessing)中大致地说明了一下...博文来自:Mr.Phoebe的专栏

  GradientDescent(BatchGradientDescent,BGD)梯度下降法是最原始,也是最基础的算法。它将所有的数据集都载入,计算它们所有的梯度,然后执行决策。(即沿着梯度相反的方向...博文来自:Aliz_

  线性回归,我们在初中就开始接触了,二维平面给出一些点,用一条直线拟合这些点。但实际情况下,这些点中都会有一些噪点、误差,那么怎么才能找出一条拟合度最好的直线的点,随机加入一些...

  caffe中solver不同优化器的一些使用方法(只记录一些常用的)下面是一些公用的参数测试时需要前向传播的次数,比如你有1000个数据,批处理大小为10,那么这个值就应该是100,这样才能够将所有的...

  14.1.8语义优化  我们在2.2.8节介绍了语义优化技术,在8.1.9节介绍了PostgreSQL支持的语义优化技术,本节介绍MySQL对于语义优化技术的支持情况。   我们在第13章曾经简单介绍...

  因为大多数机器学习任务就是最小化损失,在损失定义的情况下,后面的工作就交给了优化器。因为深度学习常见的是对于梯度的优化,也就是说,优化器最后其实就是各种对于梯度下降算法的优化。常用的optimizer...

  标准梯度下降法:汇总所有样本的总误差,然后根据总误差更新权值随机梯度下降:随机抽取一个样本误差,然后更新权值(每个样本都更新一次权值,可能造成的误差比较大)批量梯度下降法:相当于前两种的折中方案,抽取...

  一、前言这篇文章的诞生很偶然,优化器大家基本都听过,感觉就是只要自己不能理解的东西,都归于mysql优化器就完事了,哈哈。但是优...

  1.SGD在这里SGD和mini-batch是同一个意思,抽取m个小批量(独立同分布)样本,通过计算他们的平均梯度均值。缺点:(1)leraningrate选择太小,收敛速度会很慢,如果太大,则los...

  优化器总结机器学习中,有很多优化方法来试图寻找模型的最优解。比如神经网络中可以采取最基本的梯度下降法。梯度下降法(GradientDescent)梯度下降法是最基本的一类优化器,目前主要分为三种梯度下...

  github博客传送门博客园传送门计算出损失值后,这个损失值越小越好,梯度下降的算法就用在这里,帮助寻找最小的那个损失值,从而反推出w和b达到优化模型的效果常用的梯度下降法可以分为:批量梯度下降:遍历...

  tensorflow详解优化器的选择1.什么是优化器2.梯度下降算法1.什么是优化器要型对各种优化的选择游刃有余,得先理解什么是优化器。深度学习的目标是通过不断改变网络参数,使得参数能够对输入做各种非...

  OptimizerImplementation(PartII)在这儿解释一下,逻辑查询计划或者逻辑计划指的是关系代数层面的查询语句;物理查询计划或者物理计划指的是具体查询层面的查询算法,具体到每一个J...

  1、RMSprop算法是一种自适应学习率的优化算法,其核心思想是通过统计相似梯度的平均值的方式来自动地调整学习率。一般来讲,我们会在梯度算法中引入一个衰减系数,使每一次衰减都有一定的比例。在Tenso...

  摘自莫烦python中关于TensorFlow的讲解传统的参数更新方式如下,传统的参数W的更新是把原始的W累加上一个负的学习率(learningrate)乘以校正值(dx).这种方法可能会让学习过程曲...

  单纯以算法为论,深度学习从业者的算法能力可能并不需要太大,因为很多时候,只需要构建合理的框架,直接使用框架是不需要太理解其中的算法的。但是我们还是需要知道其中的很多原理,以便增加自身的知识强度,而优化...

  今天我们要讨论损失函数(lossfunction)与优化器(optimizer),上周我们说到了线性分类器,我们将图片当成一个矩阵放入计算机中,其次我们寻找一个权值矩阵W,这个W我们可以当做是每...

  1.优化器的选择 关于深度学习各种优化器的介绍和对比在网上有很多图文并茂的讲解,比如我上一篇博文转载的文章:深度学习——优化器算法Optimizer详解(BGD、SGD、MBGD、Momentum、N...

本文链接:http://guidoon.com/zhixingkaixiao/309.html