0


MySql性能优化(七)查询优化

文章目录

优化数据访问

思路

1.查询性能低下的主要原因是因为访问数据太多,某些查询不可避免的需要筛选大量的数据,我们可以通过减少访问数据的方式进行数据优化

1.确认应用程序是否在检索大量超过需要的数据
2.确认mysql服务器层是否在分析大量超过需要的数据行

2 . 是否数据库请求了不需要的数据

1.查询不需要的记录

    mysql实际上总会返回全部结果再进行计算,优化方式就是在查询后面加上limit
    比如 明知道只有一条的数据,在结尾加上 limit 1
    
2.多表关联时返回全部列
    
3.总是取出全部列

4.重复查询相同的数据
    
    如果不断的重复执行相同的查询,每次都返回相同的数据
    优化方式:可以将这部分数据缓存起来提高查询效率

执行过程优化


1.查询缓存

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


2.查询优化处理

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


2.1 词法解析器预处理

mysql通过关键字将sql语句进行解析,并且生成一颗解析树,mysql解析器将使用mysql语法规则进行验证和解析查询,例如验证是否使用了错误的关键字,顺序是否正确等等,预处理器会进一步检查解析树是否合法,例如表名和列名是否存在,是否有歧义,还会验证权限等等


2.2 查询优化器

当语法树没有问题之后,由优化器将其转化为执行计划,一条查询语句可以使用非常多的执行方式,最后都可以得到对应的结果,但是不同的执行方式带来的效率是不同的,优化器的主要目的就是选择最有效的执行计划。
mysql使用的是基于成本的优化器,在优化的时候会尝试预测一个查询使用某种查询计划时候的成本,并选择其中成本最小的一个。

CBO 基于成本的优化,RBO基于规则的优化

Question: 在很多情况下mysql会选择错误的执行计划,原因如下:

1.统计信息不准确

innoDB因为其mvcc的架构,并不能维护一个数据表的行数的精确统计信息

2.执行计划的成本估算不等同于实际的执行成本

有时候某个执行计划虽然需要读取更多的页面,但是成本确更小,因为如果这些页面都是顺序读或者已经在内存当中的话,它的访问将很小。mysql层面并不知道哪些页面在内存当中哪些页面在磁盘当中,所以查询当中需要耗费多少IO是无法得知的。

3.MySql不考虑其他并发执行的查询

MySql的优化是基于成本模型的优化,但是可能不是最快的优化

4.MySql不会考虑不受其控制的操作成本

执行存储过程或者用户自定义函数的成本


2.3 优化器的优化策略

静态优化

直接对解析树进行分析,并完成优化

动态优化

动态优化与查询的上下文有关,也可能跟取值,索引对应的行数有关

TIPS

MySql对静态优化只需要一次,但动态优化在每次执行时都需要重新评估


2.4 优化器的优化类型

  1. 重新定义关联表的顺序 数据表的关联不总是按照查询中指定的顺序进行,决定关联顺序是优化器的一个重要功能。
  2. 将外连接转换成内连接,内连效率高于外连
  3. 使用等价交换的规则,mysql可以使用一些等价变化来简化并规划表达式
  4. 优化count(),min(),max() 索引和列是否为空可以帮助mysql优化这些类型的表达式, 比如要找到某一列的最小值,只需要查询索引最左端的数据即可,不必全表扫描
  5. 预估并转化常数表达式,当mysql检查到一个表达式可以转化为常数的时候,就会一直把该表达式当作常数处理
  6. 索引覆盖扫描,当索引值的列包含所有查询中所需要使用的列的时候,可以使用覆盖索引
  7. 子查询优化 mysql在某些情况下可以将子查询转换成一种效率更高的形式,从而减少多个查询多次对数据进行访问,例如将经常查询的数据放入缓存当中
  8. 等值传播 比如两张表使用id进行关联,where指定的时候只指定了一个id的范围(对另外一张表也生效)

2.5 关联查询

官方文档-Nested-Loop Join Algorthims
1.Simple Nested-Loop Join
简单嵌套循环连接实际上就是简单粗暴的嵌套循环,如果table1有1万条数据,table2有1万条数据,那么数据比较的次数=1万 * 1万 =1亿次,这种查询效率会非常慢。

2.Index Nested-Loop Join(索引嵌套循环连接)
索引嵌套循环是使用索引减少扫描的次数来提高效率的,所以要求非驱动表上必须有索引才行。
在查询的时候,驱动表会根据关联字段的索引进行查询,当索引上找到符合的值,才会进行回表查询。如果非驱动表的关联字段是主键的话,查询效率会非常高(主键索引结构的叶子结点包含了完整的行数据(InnoDB)),如果不是主键,每次匹配到索引后都需要进行一次回表查询(根据二级索引(非主键索引)的主键ID进行回表查询),性能肯定弱于主键的查询。

3.Block Nested-Loop Join(缓存块嵌套循环连接)
如果存在索引,那么会使用index的方式进行join,如果join的列没有索引,被驱动表要扫描的次数太多了,每次访问被驱动表,其表中的记录都会被加载到内存中,然后再从驱动表中取一条与其匹配,匹配结束后清除内存,然后再从驱动表中加载一条记录 然后把被驱动表的记录在加载到内存匹配,这样周而复始,大大增加了IO的次数。为了减少被驱动表的IO次数,就出现了Block Nested-Loop Join的方式。
不再是逐条获取驱动表的数据,而是一块一块的获取,引入了join buffer缓冲区,将驱动表join相关的部分数据列(大小是join buffer的限制)缓存到join buffer中,然后全表扫描被驱动表,被驱动表的每一条记录一次性和join buffer中的所有驱动表记录进行匹配(内存中操作),将简单嵌套循环中的多次比较合并成一次,降低了非驱动表的访问频率。
3.1 TIPS

  1. join buffer 会缓存所有参与查询的列而不是只有join的列
  2. 可以调整join_buffer_size的大小
  3. join_buffer_sizc的默i认值是256K,join_buffer_siz的最大值在MySQL5.1.22版本前是4G-1,而之后的版本才在64位操作系统下申请大于4G的们oin Buffers空间。
  4. 使用Block Nested-Loop Join!算法需要开启优化器管理配置的optimizer switch的设置block_nested_loop为on,默认为开启
  5. show variables like ‘%optimizer_switch%’

2.6 排序优化

1.两次传输排序

第一次数据读取是将需要排序的字段读取出来,然后进行排序

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

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

2.单次传输排序

先读取查询所需要的所有列,然后再根据给定列进行排序,最后直接返回排序结果,此方式只需要一次顺序IO读取所有的放据,而无须任何的随机IO。

问题在于查可的列特别多的时候,会占闲大量的存储空间,无法存储大量的数据

3.TIPS

当需要排序的列的总大小加上order_by的列大小超过max_length_for_sort_data定义的字节,mysql会选择两次传输排序,反之使用单次传输排序。当然用户可以设置此参数的值来设置排序的方式


优化特定类型的查询

1. 优化count() 查询

1. myinsm 的count函数只有没有任何where条件的时候,count(*)才比较快
2. 一般情况下,count()需要扫描大量的行才能获取到准确的数据,其实不太容易优化,
       在实际操作的过程当中可以考虑使用索引覆盖扫描,或者增加汇总表,或者增加外部缓存系统

2. 优化关联查询

 1.确保on或者using子句当中的列上有索引,在创建索引时就需要考虑到关联的顺序
 2.确保任何的groupby和orderby中表达式只涉及到一个表中的列,这样mysql才能用索引来优化这个过程 

3. 优化子查询

 尽量使用关联查询代替子查询(子查询筛掉的值多的话还是子查询,不然临时表io不如直接join了)

4. 优化limit分页

 尽量使用覆盖索引,而不是查询所有的列

4. 优化union查询

除非确实需要服务器消除重复的行,否则一定要使用union all,因为没有all关键字的时候,mysql会在查询的时候给临时表加上distinct关键字,这个操作代价很高

本文转载自: https://blog.csdn.net/qq_38895905/article/details/128886703
版权归原作者 我本半山人 所有, 如有侵权,请联系我们删除。

“MySql性能优化(七)查询优化”的评论:

还没有评论