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关键字，这个操作代价很高