四、查询性能优化

作者: 小炼君 | 来源:发表于2017-10-02 15:30 被阅读218次

国庆第二天，今天嗨翻模式的开启就等室友的同学来了再说吧，在这之前，先来一波笔记

加油.jpg

查询真正重要的是响应时间，查询包含一系列子任务

通过减少执行的子任务执行次数
消除子任务的方式来优化查询
提高子任务执行的效率
通过上面三种方式来提高查询效率
查询的生命周期分为：从客户端，到服务器，然后在服务器上进行解析，生成执行计划，执行，并将结果返回给客户端
在每一个消耗大量时间的查询案列中，都可以看到一些不必要的额外操作，某些操作被重复的执行了很多次，某些操作执行的太慢，优化查询的目的就是为了减少和消除这些操作所花费的时间

优化数据访问

查询性能低下最基本的原因是访问的数据太多，可以通过下面两种方式来分析：

确认应用是否在检索大量超过需要的数据，这通常意味着访问了太多的行，但有时候也可能是访问了太多的列
mysql服务器层是否在分析大量超过需要的数据行
访问过多的数据包括
查询不需要的行
误以为mysql只会返回需要的行，实际上mysql却先返回全部结果集再进行计算，最简单有效的解决方法就是在查询后面添加limit语句
多表关联返回全部列
总是返回全部列
查询重复的数据
分析过多的数据
查询性能需要关注的三个方面：响应时间、扫描行数、返回行数
响应时间=等待时间+服务时间
服务时间：指执行某条具体sql查询时所消耗的时间
等待时间：指服务器因为等待某些资源而没有真正执行查询的时间
理想情况下扫描的行数与返回的行数应该是相同的，但是实际中这种情况并不多，比如关联查询，需要扫描多表的多条记录才能产生一条结果记录
在explain语句中的type列反应了访问类型，访问类型有很多种，从全表扫描到索引扫描、范围扫描、唯一索引查询、常数引用等，速度由慢到块，数据由多到少
在mysql中，能够使用过下面三种方式的where条件，由好到坏依次为：
索引中使用where条件来过滤不需要的记录，这是在存储引擎层实现的
索引覆盖扫描来返回记录，直接在索引中过滤掉不需要的记录并返回命中结果，这是在mysql服务器层实现的
从数据表中返回数据，然后过滤不满足的记录

重构查询的方式

一个复杂查询还是多个简单查询

强调数据库层完成尽可能多的工作，是考虑到以前总是认为网络通信，查询解析和优化是一件代价很高的事情，但是这对mysql来说已经不是大问题了
mysql每秒能扫描内存中上百万行数据，但是mysql响应数据给客户端就慢多了，所以到底需不需要拆分复杂查询，这得根据具体情况而定，不能偏左更不能偏右

切分查询

有时候对于一个大查询，我们需要分而治之，这主要是针对查询返回的数据结果太多，导致影响其他业务逻辑的情况。将大查询切分成小查询每个查询的功能都一样，只完成一小部分，比如删除旧数据这个案例，定期清理大量数据时，如果用一个大查询将会锁住很多表，耗尽系统资源，阻塞很多小的重要的查询，但我们拆分查询后，每次删除1万条记录这样的情况就可以得到缓解

分解关联查询

很多高性能应用都会对关联查询进行分解，也就是对单表进行查询，然后在应用中进行关联，这样做的好处在于：

让缓存效率更高
将查询分解后，执行单个查询可以减少锁的竞争
在应用层做关联，可以更容易对数据库进行拆分
可以减少冗余记录的查询，在应用层进行关联查询时，往往对于某条记录应用只需要查询一次，但是在数据库层做关联可能需要重复地访问一部分数据

查询执行的基础

在客户端发送一条sql请求时，mysql到底做了些什么，下面这张图完美的阐释了整个流程：

sql查询过程.jpg

客户端发送查询请求给服务器
服务器先检查查询缓存是否命中，命中则立刻返回给客户端，否则进入下一阶段
服务器端进行sql解析，预处理，再由优化器生成查询执行计划
mysql根据优化器生成的查询执行计划，调用存储引擎API来执行查询
将结果返回给客户端，同时将结果保存到服务器缓存中

mysql客户端/服务器通信协议

mysql客户端与服务器之间的通信协议是半双工的，在任何一个时刻，只能由客户端或者服务器发送数据给彼此，而两者不能同时发生
当客户端发送sql查询语句给服务器后，它剩下的工作就只有等待了
一般服务器响应给用户的数据特别多，由多个数据包组成，当服务器开始响应客户端请求时，客户端必须完整的接受完整个返回结果后才能给予响应，以致于客户端可能会接收到不需要的数据并丢弃，为了解决这种情况，可以采用limit限制返回的行数

库函数缓存

当使用多数连接mysql的库函数对mysql获取数据时，其结果看起来都像是从mysql服务器获取数据，但实际上是从这个库函数的缓存中获取数据，当库函数查询的结果记录小还没什么，但是如果是很大的结果集，那么效率就会有影响
这种情况下，可以设置不使用缓存来记录结果，而是直接处理，具体语言处理起来不一样，比如php在获取结果时：
$result = mysql_query(‘select * from huge_table’, $link);
通过$result = mysql_unbuffered_query(‘select * from huge_table’, $link)代替

查询缓存

在解析一个查询语句之前，如果查询缓存是打开的，那么mysql会优先检查这个查询是否查询命中缓存中的数据，它通过一个大小写敏感的hash查找实现，如果命中了查询缓存，这时还需要检查用户的权限，这些权限也一并保存在缓存中的，所以不需要查表，如果权限通过，那么直接就从缓存中取数据

查询优化处理

查询的生命周期经过缓存后，下一步就是需要将sql语句转化成一个执行计划，mysql再按照这个执行计划和存储引擎进行交互，这个过程包含多个子阶段：
解析sql，预处理，优化sql执行计划
mysql通过语法解析器解析sql，通过关键字将sql语句进行解析，并生成一颗解析树，并使用mysql语法规则验证和解析查询，这里可以说是对sql语句字面上进行验证
预处理阶段，则根据mysql规则进一步检查解析树是否合法，这里将会更深层次对sql做验证，比如验证表名，字段名是否合法等
现在语法树被认为是合法的了，并且由优化器将其转化成了执行计划，一条sql有很多条执行计划，mysql优化器使用基于成本的优化器，将从这多条执行计划中选择成本最低的一条
执行一次where条件比较的成本，可以通过last_query_cost变量值来查看当前查询成本
show status like ‘last_query_cost’
该值得到的结果是通过一系列统计信息计算得来的，所以这里有很多种情况都会导致mysql优化器选择错误的执行计划：

统计信息不准确
执行计划中的成本估算不等同于实际执行的成本，即使统计信息精准，优化器给出的执行计划也不一定是最优的，比如某个执行计划虽然要读取更多的页面，但是它的成本更小，因为这些页面都是顺序读取或者这些页面都在内存中，那么它的访问成本将更小
mysql优化器的最优可能跟你想的最优不一样，你可能需要的是执行时间尽可能短，但mysql基于成本模型选择最优，所以有些时候不一定执行的时间是最短的
mysql从不考虑并发执行的查询，这可能会影响到当前执行的查询
mysql也不是在任何时候都是基于成本的优化，也会基于一些固定规则，比如存在全文搜索的match子句，则在全文索引的时候使用全文索引
mysql并不考虑不受其控制的操作的成本，例如执行存储过程或用户自定义函数
优化器有时候无法估算所有可能的执行计划，所以它可能错过实际上最优的执行计划
mysql的查询优化分两种：静态优化和动态优化
静态优化，在编译时就完成，只优化一次，比如where查询条件中的常量值，将where条件通过简单的代数运算转化为另一种等价的方式
动态优化，又称为编译时优化，跟查询的上下文有关，比如where条件中的取值，索引条目，对应的数据行数等
mysql能够处理的优化类型：

重新定义关联表顺序
将外链接转化为内连接
使用等价变换原则，(5=5 and a>5) ==> a>5, (a<b and b=c) and a=5 ==> b>5 a<b b=c。优化count(),min(),max()
min()取b-tree最左端的记录
max()取b-tree最右端的记录
预估并转化为常数
当mysql检测到一个表达式可以转化为常数的时候，就会一直把该表达式当做常数进行优化处理，例如用户自定义的变量，在查询中没有发生变化时就可以转化为一个常数，或者在索引列上执行min(),甚至在主键或者唯一索引查找语句也可以转化为常数表达式
索引覆盖扫描
子查询优化
提前终止查询，not exist
提前终止查询有两种情况可以达到，一种使用Limit，还有一种是发现了不成立的条件
等值传播
如果两个列的值通过等值关联，那么mysql能够把其中一个列的where条件传递到另一个列上

inner join film_actor 
using(film_id)
where film_id > 500

film_id > 500将会被使用到film表以及film_actor表

列表in的比较
mysql中的In()列表中的数据会先进行排序，然后通过二分查找的方式来确定列表中的值是否满足条件

数据与索引的统计信息

mysql架构有多个层次组成，在服务器层有查询优化器，却没有保存数据和索引的统计信息，统计信息由存储引擎实现，所以查询优化器在优化查询时，需要向存储引擎获取相应的统计信息，包括每个表或者索引有多少页面，每个表的每个索引基数是多少，数据行和索引长度、索引的分布等

mysql中的关联查询

mysql中的关联一词包含的意义比一般意义上理解的更要广泛，总的来说，mysql认为每一次查询都是一次关联，并不局限于一个查询需要用到大于两个表才叫关联
对于union查询，mysql先将一系列的单个查询结果放到一个临时表中，然后再重新读出临时表数据来完成union查询，在mysql的概念中，每个查询都是一次关联，所以读取临时表也是一次关联
mysql关联执行的策略是对任何关联都执行嵌套循环关联操作：先从一个表中查出一条记录，然后再嵌套循环到下一个表中寻找匹配的行，依次下去，直到找到所有表中匹配的行为止，然后根据各个表匹配的行，返回查询中需要的列，mysql会尝试在最后一个表中找到所有匹配的行，如果最后一个表无法找到，就返回上一层关联表继续查找

mysql关联查询表.jpg

关联查询优化器

mysql优化器最重要的一部分就是关联查询优化，它决定了多个表关联时的顺序，mysql会根据扫描的行数选择合理的关联顺序，目的是为了让查询进行更少的嵌套循环和回溯操作，当然如果优化器给定的关联顺序并不是最优的，那么可以通过添加STRAIGHT_JOIN关键字重写查询，让优化器按照给定的表顺序进行关联，但是如果关联表多了，比如超过了N个表进行关联，那么需要检查n的阶乘中关联顺序，当搜索空间非常大的时候，优化器不可能给逐一评估每一种关联顺序，优化器选择使用贪婪搜索的方式查找最优

排序优化

无论如何排序都是一个成本很高的操作，所以从性能角度考虑，应尽可能避免排序或者尽可能避免对大数据量数据进行排序，如果排序数据量小，则可以在内存中排序，如果数据量大，则需要使用磁盘，不过这个过程mysql统一称为文件排序
如果排序的数据量小于排序缓冲区，mysql使用内存进行快速排序操作，如果数据量大于排序缓冲区，那么mysql会先将数据切块，对独立的块进行排序并将排序结果存放在磁盘，然后将各个排好序的块进行合并，最后返回排序结果
mysql排序有两种方式：

读取行指针与排序列，根据排序列排好序后再通过行指针查询最后的结果，这需要两次数据传输（旧版本使用）
先读取查询所需要的所有列，然后根据排序列进行排序，最后直接返回结果（新版本使用）
对于order by操作，如果排序列全在第一个表，那么在关联第一个表的时候就已经进行文件排序，除此之外的所有情况，mysql会先将关联的结果存放到一个临时表中，然后再进行文件排序

返回结果给客户端

查询执行的最后一个阶段是将查询结果返回给客户端，mysql将查询结果返回给客户端是一个增量、逐步返回的过程，一旦服务器处理完最后一个关联表，开始生成第一条记录时，mysql就可以开始向客户端逐步返回结果集了
这样做的好处在于：

服务器无需存储大量的结果，也就不会因此消耗太多内存
这样的处理也让mysql客户端第一时间获得返回的结果数据

mysql查询优化器的局限性

关联子查询

mysql的子查询实现的非常糟糕，最糟糕的一类查询就是where条件中包含in()的子查询
对于关联子查询，通过explain进行分析时，select_type为dependent subquery
可以用两种方法来解决关联子查询的问题：

将关联子查询转化为内连接
通过group_concat将需要的列用,拼接而不是直接使用查询
我们在查询时，一旦使用了distinct或者groupby，那么在查询的执行过程中，通常需要产生临时中间表

特定类型查询优化

count

count有两方面的意思，一个是统计列有效值的个数，一个是统计行数，count中如果传入的是一个列，就是统计这个列不为NULL的值的个数，使用count()则是在统计结果集的行数，并不是把所有列进行统计，在使用*时，他会忽略所有的列，而直接统计行数
统计行数，最好使用count()
统计列有效值，则使count(colname)

优化关联查询

确保on,using子句中的列上有索引，在创建索引的时候需要考虑到关联的顺序，比如A,B表用C列进行关联，关联顺序为BA,那么就不需要再B表上建立索引
确保group by ,order by 上的表达式只涉及一列

优化子查询

尽量使用关联查询代替子查询，但是，mysql5.6之后就可以忽略这个建议了

优化关联子查询

对于where 条件中使用in(sub query)，mysql会将外层查询结果压到子查询中

    select film_id from film_actor where actor_id = 1;
)

对于上面这种查询，mysql会优化成下面的查询语句

select * from film_actor where actor_id = 1
and film.id = film_actor.film_id);

解决方法：
1.使用连接代替子查询
select film.* from film inner join film_actor using(film_id) where actor_id = 1;
2.使用group_concat()在in()中构造逗号分隔的列表
select film.* from film where

优化union的限制

如果希望各个子句先排好序在合并结果集，就需要分别在各个子句中排序并使用limit
例如，将两个子查询结合起来，并取前20条记录

(select first_name,last_name from actor order by last_name limit 20)
union all 
(select first_name, last_name from customer order by last_name limit 20)
order by last_name
limit 20;

这样中间表中就只有40条数据,需要注意的是通过两个查询合并的记录顺序并不是一定的，需要对全局进行order by 和limit操作
mysql对union[all]查询总是以创建并填充临时表的方式来执行union查询,没有特别说明要去重，就一定要加上all，因为union会给临时表加上distinct，这回导致整个数据临时表的唯一性检查

在同一个表查询与更新

mysql不允许对同一张表同时进行查询和更新操作，可以通过多表关联更新来解决

update tbl inner join (
    select type, count(*) as cnt from tbl group by type
) as der using(type) 
set tbl.cnt = der.cnt

优化groupby distinct

使用索引进行优化，这是最有效的方法，当无法使用索引的时候，mysql采用两种(临时表和文件排序)来做分组，这里可以通过hint:SQL_SMALL_RESULT,SQL_BIG_RESULT来让优化器按照你的方式运行
在使用groupby按照表中某一列进行分组的时候，通常采用标识列(也就是外键表的标识列)来进行分组效率会比其他列分组效率高

image.png

在分组查询的select语句中直接使用非分组列，通常都不是什么好主意
select actor.first_name, actor.last_name, count(*) from film_actor inner join actor using (film_id) group by film_actor.actor_id
可能替换成下面的语句更加满足关系理论，但是使用子查询需要创建和填充临时表，而子查询中创建的临时表时没有任何索引的
select actor.first_name, actor.last_name, c.cnt from actor inner join ( select actor_id, count(*) as cnt from film_actor group by actor_id ) as c using(actor_id)
如果没有显示的使用orderby子句指定排序列，当查询使用group by 的时候，默认会使用排序列进行排序，如果不关心排序，而这种排序又导致了文件排序，那么可以通过group by null来禁用排序，也可以使用DESC,ASC让查询结果按照需要的方向进行排序

优化limit分页

limit语法
limit [offset，]rows | rows offset
如果只设置一个值，limit 20, 它等价于 limit 0, 20 也就是查询开头20条记录
limit 中的offset将会导致记录从第一行开始扫描，如果根据这样分页limit 1000, 20,那么将会扫描1020条记录，然后抛弃前面1000条记录
在进行limit分页的时候，我们通常会使用limit加上偏移量的办法实现，同时加上合适的order by子句，如果有对应的索引，效率通常会不错，但是如果limit偏移量很大，比如，limit 10000, 20 ，mysql会搜索10020条记录并取最后20条记录，抛弃掉前面10000条记录，可以采取在分页中限制分页的数量或者优化大偏移量的性能
优化此类分页查询的一个最简单的办法就是尽可能的使用索引覆盖扫描，而不是查询所有的列，然后根据需要再做一次关联查询再返回操作所需要的列
select film.id, film.description from film order by title limit 50, 5
如果上面的film表非常大，那么上面的查询最好是改写成下面的语句：
select film.id, film.description from film inner join ( select film.id from film order by film.title limit 50, 5) as tmp ) using(film.id)
这里其实使用的是延迟关联的技术，他让mysql扫描尽可能少的页面，获取需要访问的记录后再根据关联列回原表查询需要的列，该技术也可以用来优化关联查询中的limit语句
也可以使用范围查询，前提是已经知道需要查询的范围，比如上面的查询语句可以改写为
select film.id, film.description from film where id between 50 and 54;
分页查询limit与offset,其实是offset的问题，他会让数据库扫描大量不需要的记录行然后再抛弃掉，可以使用书签方式记录上一次访问的数据位置，下次直接从书签位置进行访问，那么就可以避免使用offset，但使用这种方式的前提是表主键单调递增，没有删除
使用下面的查询获取第一组数据
select * from rental order by rental_id desc limit 20;
假设上面返回的是16049到16030的记录，那么下一页就可以直接从16030访问
select * from rental where rental_id < 16030 order by rental_id desc limit 20;

优化union查询

mysql总是通过创建并填充临时表的方式来执行union查询，除非确实需要服务器消除重复的行，否则就一定要使用union all，这一点很重要，如果没有加all，mysql会给临时表加上distinct选项，这导致整个临时表的数据需要做唯一性验证，即使加上了all,mysql仍然会用临时表存储结果，事实上，mysql总是将结果放入临时表然后在读出，在返回给客户端

用户自定义变量

采用自定义变量来实现排名

set @cur_cnt:=0,@prev_cnt:=0, @rank:=0;
select actor_id,
@cur_cnt := count(*) as cnt,@rank:=if(@prev_cnt<>@cur_cnt, @rank+1, @rank) as rank,@prev_cnt := @cur_cnt
from film_actor
group by actor_id, order by cnt desc
limit 10;