• 128980

    文章

  • 810

    评论

  • 12

    友链

  • 最近新加了换肤功能,大家多来逛逛吧~~~~
  • 喜欢这个网站的朋友可以加一下QQ群,我们一起交流技术。

【拉勾速推指南-歪里谐说】MySQL查询优化

服了这份高薪指南,涨多少你说了算>>

文章内容输出来源:拉勾教育Java高薪训练营

前言

影响MySQL性能的因素多种多样,和查询优化相关的内容包括以下:

可以类比成导致人身体生病的病因,医生按照病因给病人调理身体,也需要对症下药。

  • 网络(身体经络)
  • CPU(大脑)
  • IO(呼吸道)
  • 上下文切换(消化道)
  • 生成统计信息(生殖:计划生育)
  • 锁等待时间(泌尿:尿频尿急尿等待...)
  • 系统调用(神经系统)

优化方案

1. 优化数据访问

  • 查询性能低下的主要原因是访问的数据太多,某些查询不可避免的需要筛选大量的数据,我们可以通过减少访问数据量的方式进行优化(吃得太多,消化系统负担太大,通过减少进食量优化)

  • 是否向数据库请求了不需要的数据(是否摄入了太多不需要的营养和热量)

    • 查询不需要的记录
    • 多表关联时返回全部列
    • 总是取出全部列
    • 重复查询相同的数据

2. 执行过程的优化

  • 查询缓存:在解析一个查询语句之前,如果查询缓存是打开的,那么mysql会优先检查这个查询是否命中查询缓存中的数据,如果查询恰好命中了查询缓存,那么会在返回结果之前会检查用户权限,如果权限没有问题,那么mysql会跳过所有的阶段,就直接从缓存中拿到结果并返回给客户端

  • 查询优化处理:mysql查询完缓存之后会经过以下几个步骤:解析SQL、预处理、优化SQL执行计划,这几个步骤出现任何的错误,都可能会终止查询

    • 语法解析器和预处理:mysql通过关键字将SQL语句进行解析,并生成一颗解析树,mysql解析器将使用mysql语法规则验证和解析查询,例如验证使用使用了错误的关键字或者顺序是否正确等等,预处理器会进一步检查解析树是否合法,例如表名和列名是否存在,是否有歧义,还会验证权限等等
    • 查询优化器:当语法树没有问题之后,相应的要由优化器将其转成执行计划,一条查询语句可以使用非常多的执行方式,最后都可以得到对应的结果,但是不同的执行方式带来的效率是不同的,优化器的最主要目的就是要选择最有效的执行计划
      • select count(*) from film_actor; show status like 'last_query_cost'; 可以看到这条查询语句大概需要做1104个数据页才能找到对应的数据,这是经过一系列的统计信息计算来的

        • 每个表或者索引的页面个数
        • 索引的基数
        • 索引和数据行的长度
        • 索引的分布情况
      • 在很多情况下mysql会选择错误的执行计划,原因如下:

        • 统计信息不准确:InnoDB因为其mvcc的架构,并不能维护一个数据表的行数的精确统计信息
        • 执行计划的成本估算不等同于实际执行的成本:有时候某个执行计划虽然需要读取更多的页面,但是他的成本却更小,因为如果这些页面都是顺序读或者这些页面都已经在内存中的话,那么它的访问成本将很小,mysql层面并不知道哪些页面在内存中,哪些在磁盘,所以查询之际执行过程中到底需要多少次IO是无法得知的
        • mysql不考虑其他并发执行的查询
        • mysql不会考虑不受其控制的操作成本:执行存储过程或者用户自定义函数的成本
      • 优化器的优化策略

        • 静态优化:直接对解析树进行分析,并完成优化
        • 动态优化: 动态优化与查询的上下文有关,也可能跟取值、索引对应的行数有关
        • mysql对查询的静态优化只需要一次,但对动态优化在每次执行时都需要重新评估
      • 优化器的优化类型

        • 重新定义关联表的顺序:数据表的关联并不总是按照在查询中指定的顺序进行,决定关联顺序时优化器很重要的功能
        • 将外连接转化成内连接,内连接的效率要高于外连接
        • 使用等价变换规则,mysql可以使用一些等价变化来简化并规划表达式
        • 优化count(),min(),max():索引和列是否可以为空通常可以帮助mysql优化这类表达式:例如,要找到某一列的最小值,只需要查询索引的最左端的记录即可,不需要全文扫描比较
        • 预估并转化为常数表达式,当mysql检测到一个表达式可以转化为常数的时候,就会一直把该表达式作为常数进行处理
        • 索引覆盖扫描,当索引中的列包含所有查询中需要使用的列的时候,可以使用覆盖索引
        • 子查询优化:mysql在某些情况下可以将子查询转换一种效率更高的形式,从而减少多个查询多次对数据进行访问,例如将经常查询的数据放入到缓存中
        • 等值传播:如果两个列的值通过等式关联,那么mysql能够把其中一个列的where条件传递到另一个上: 使用film_id字段进行等值关联,film_id这个列不仅适用于film表而且适用于film_actor表
      • 排序优化

        • 排序的算法
          • 两次传输排序:第一次数据读取是将需要排序的字段读取出来,然后进行排序,第二次是将排好序的结果按照需要去读取数据行。

这种方式效率比较低,原因是第二次读取数据的时候因为已经排好序,需要去读取所有记录而此时更多的是随机IO,读取数据成本会比较高

两次传输的优势,在排序的时候存储尽可能少的数据,让排序缓冲区可以尽可能多的容纳行数来进行排序操作

    * 单次传输排序:先读取查询所需要的所有列,然后再根据给定列进行排序,最后直接返回排序结果,此方式只需要一次顺序IO读取所有的数据,而无须任何的随机IO,问题在于查询的列特别多的时候,会占用大量的存储空间,无法存储大量的数据
    * 当需要排序的列的总大小超过max_length_for_sort_data定义的字节,mysql会选择双次排序,反之使用单次排序,当然,用户可以设置此参数的值来选择排序的方式

mysql使用的是基于成本的优化器,在优化的时候会尝试预测一个查询使用某种查询计划时候的成本,并选择其中成本最小的一个

结语


695856371Web网页设计师②群 | 喜欢本站的朋友可以收藏本站,或者加入我们大家一起来交流技术!

0条评论

Loading...


发表评论

电子邮件地址不会被公开。 必填项已用*标注

自定义皮肤 主体内容背景
打开支付宝扫码付款购买视频教程
遇到问题联系客服QQ:419400980
注册梁钟霖个人博客