深入理解MySql——查询性能优化 sql

查询性能优化 1.为什么查询会慢
2.慢查询基础：优化数据访问
3.重构查询方式
4.查询执行的基础
5.mysql查询优化器的局限性
6.优化特定类型的查询
1.为什么查询会慢
如果把一条sql查询看做是一个完整的任务，那么它就会由一系列子任务组成，每个子任务都需要消耗一定的时间，我们对于sql的优化，其实就是对于它子任务的优化，要么消除其中一些子任务，要么减少子任务的执行次数，要么让子任务运行得更快，有了这些概念，我们就来看看如何优化查询。
2.慢查询基础：优化数据访问
通常查询慢的原因，我们可以简单地分为以下两类：
1.确认程序是否需要检索大量超过需要的数据，这通常意味着访问了太多的行，也可能是访问了太多的列。
2.确认mysql服务器是否分析大量超过需要的数据行。
2.1 是否向数据库请求了不需要的数据
有些查询会返回应用程序不需要的数据，可能是多余的行，也可能是多余的列，这些多余的数据会被应用程序抛弃，同时也会给mysql服务器带来额外的负担，增加网络开销，另外也会消耗服务器的CPU和内存资源。
查询不需要的数据：
有时候我们只需要查询十条信息，但是mysql却返回了全部的信息，我们只取前面的十条，客户端的应用程序会接收全部的结果集数据，然后抛弃其中大部分的数据，这时候为了避免资源浪费，最简单的方法就是加上limit。
多表关联的时候返回全部列
假设有下列查询
select * from actor join film_actor on actor.id = film_actor.actor_id where actor.id = 1;
我们要查询出这个演员演的所有电影，此时会返回两个表全部的数据列，其实我们只需要返回需要的列就行了。
select film_actor.film_id from .....
总是取出全部的列
当我们每次看到select * 的时候，都应该用怀疑的眼光审视，因为很多列可能都是不必要的，如果去除全部列，会让优化器无法完成索引覆盖扫描这类优化，但是，如果这种浪费可以简化开发，提高复用性吗，如果清楚这种做法的性能影响，那这种方法还是可取的。
总是重复查询相同的数据
如果你不太小心，就会出现这种问题——不断地重复执行相同的查询，然后每次都返回相同的数据（比如商城主页，比如重复查询用户的头像url），那么这种情况，我们可以把这种热点数据缓存起来，需要的时候从缓存中取出，这样性能显然会更好。
2.2 mysql是否在扫描额外的记录
在完成了确定查询只返回最基本的字段后，接下来应该看看查询为了返回结果是否需要扫描了过多的数据。
对于mysql，最简单的衡量查询开销的指标如下：
扫描的行数和返回的行数
理想情况下扫描的行数和返回的行数是相同的，但是实际情况这种美事并不多。
扫描的行数和访问类型
在评估查询开销的时候，要考虑一下从表中找到某一行数据的成本，mysql有好几种访问方式可以查找并返回一行结果，有些访问方式可能需要扫描很多行才能获取结果，有些访问方式可能无须扫描就能返回结果。
explain语句的type列反应了访问类型，访问类型有全表，范围，唯一索引等

文章图片

如图，就使用了全表扫描，同时这里的Using Where 标识mysql通过where条件来筛选存储引擎返回的记录。
一般mysql能够使用如下三种方式应用where条件，从好到坏依次为：
1.在索引中使用where条件来过滤不匹配的记录，这是在存储引擎层完成的。
2.使用索引覆盖扫描来返回记录，直接从索引中过滤不需要的记录并返回命中的结果，这是在mysql服务层完成的。
3.从数据表中返回数据，然后过滤不满足条件的记录。
3.重构查询方式
在优化有问题的查询时，目标应该是找到一个更优秀的方法获得一个实际需要结果——而不是一定总是要从mysql获取一个一模一样的结果，有时候可以换一种写法获得一模一样的结果，但是性能更好。
3.1一个简单查询还是多个复杂查询
我们在设计一个复杂查询的时候，对于是否需要将一个复杂的查询拆分成多个简单的查询，这点值得商榷。在传统的实现中，总是强调要数据库层完成尽可能多的操作，这样的逻辑是基于以前网络通信是一件代价很高的事情。
但是这样的想法对于mysql并不适用，现代带宽已经越来越大，而且mysql在设计上从连接到断开都很轻量级，所以运行多个小查询已经不是什么大问题了。
3.2切分查询
有时候我们对于一个比较大的查询这个时候我们需要分而治之，将大查询切分成小查询，每个查询功能完全一样，只完成一小部分。
举个例子，删除旧数据，如果我们要更新大量用户的数据，每次都用一个大的语句一次性完成的话，则可能就需要一次性锁住很多数据，占满整个事务日志，耗尽系统资源，阻塞很多很小的但是很重要的查询。但是如果我们每次从数据库中取出100个用户的数据，然后进行滚动更新，不仅减少了内存占用，也减少了数据库的开销。
3.3分解关联查询
有时候，我们会对很多join的表进行拆解查询，然后得出的结果再在应用程序中进行关联。
比如说，下面这个查询：
select * from tag

join tag_post on tag_post.tag_id = tag.id join post on tag_post.post_id = post.id where tag.tag = 'mysql'

可以分解成下面三个查询：
select * from tag where tag = 'mysql';
select * from tag_post where tag_id = 1234;
select * from post where post.id in (123,456);
其实将一条sql拆解成三条sql，有以下好处：

让缓存更加高效，许多应用程序可以获取mysql缓存中对应的结果，录入：tag='mysql' 已经被别的查询缓存过了，这个时候我们直接去获取就可以了。
将查询分解后，执行单个查询可以减少锁的竞争。
在应用层做关联，可以更容易对数据库进行拆分，更容易做到高性能和可扩展
查询本身也有可能会提升。用in()代替关联查询，可以让mysql按照ID顺序进行查询，这可能比随机的关联更高效。
可以减少冗余记录的查询，在应用层做关联查询，意味着对于某条记录应用只需要查询一次，而在数据库中佐关联查询，则可能需要重复地访问一部分数据。

4.查询执行的基础
我们先看看当我们向mysql发送一个请求的时候，mysql到底做了什么：

文章图片

1.客户端发送一条查询给服务器。
2.服务器先检查缓存，如果命中了缓存，则理科返回存储在缓存中的结果，否则进入下一阶段。
3.服务器端进行sql解析，预处理，再由优化器生成对应的执行计划
4.mysql根据优化器生成的执行计划，调用存储引擎的API来执行查询。
5.将结果返回给客户端
4.1查询缓存：
在解析一个查询语句之前，如果查询缓存是打开的，那么mysql就会优先检查这个查询是否命中缓存中的数据。这个检查是通过一个对大小写敏感的哈希查找来实现的。查询语句即使只有一个字节不同，那也不会匹配缓存结果。
4.2语法解析和预处理：
mysql通过关键字对查询语句进行解析，并生成一颗对应的"解析树"。
mysql解析器将使用mysql语法规则校验和解析查询。例如：是否使用错误的关键字，或者关键字的顺序是否正确等等。
预处理器则根据一些mysql规则进行进一步检查解析树是否合法。
4.3查询优化器：
现在查询已经被认为是合法的了，但是一条查询可以有多种方式进行查询，最后都会返回相同的结果，优化器的作用就是找到这其中最好的执行计划。
mysql使用基于成本的优化器，他会预测一个查询的成本，然后选择成本最小的一个。成本最小的单位是随机读取一个4K数据页的成本，我们可以使用：
show status like 'Last_query_cost' 来计算最后一个查询的成本。

文章图片

如图，查询一个这个结果需要从87241个数据页来完成以上查询。
有很多种原因会导致mysql优化器选择错误的执行计划，如下所示：

执行中的成本估算不算不等于实际的成本。所以即使统计信息精确，优化器给出的执行计划也可能不是最优的。例如有时候某个执行计划需要读取更多的页，但是它的成本却更小，因为如果这些页面都是顺序或者这些页都已经在内存中的话，mysql并不知道哪些页在内存或者在磁盘中。
mysql的最优是基于成本模型，可能和我们所知的缩短查询时间的成本不太相同。
mysql从不考虑其它并发执行的查询，这可能会影响到当前查询的速度。

下列是mysql能够处理的优化类型：

重新定义关联表的顺序：比如我们a join b表，我们会以一张表为基准，对每一行数据进行对另外一张表的扫描，这时候优化器就会选择表数据比较小的那张，当做基础表，可以减少大量匹配。
使用等价变换原则，mysql会自动简化一些表达式，比如会自动去掉1=1，类似的，比如有 a < b and a = 5，mysql就会优化为b > 5
对于count() ,max(),min()这些，索引可以帮我们优化查询，例如要找某一列的最小或者最大值，只需要查询索引B-TREE最左边或者最右边的记录，类似的，如果没有where条件的count() MyISAM维护了一个变量来存放数据表的行数。
覆盖索引扫描：当索引中的字段完全包括了要查询的字段，就可以从索引中返回所有数据，不用回表查询了。
提前终止：当我们满足查询需求的时候，mysql会立刻停止查询，一个典型的例子就是limit关键字，或者mysql发现了一个不成立的条件，比如非空的id等于null，也会提前终止查询。
列表in()的比较：mysql 将 in()里面的数据先进行排序，然后通过二分查找的方式来确定列表中的值是否满足，这是一个O（log n）复杂度的操作。

4.4 mysql如何执行关联查询：
mysql执行关联的策略很简单，mysql对于任何表的关联都执行嵌套循环操作，即mysql先在一个表中循环取出一条数据，然后再嵌套循环到下一个表中寻找匹配的行，依次下去，直到找到所有表中的列为止，如果最后一个表也无法找到更多的行，mysql就返回上一层的关联表，是否能找到更多的匹配记录，再嵌套查询下一个表。

文章图片

4.5 排序优化：
无论如何排序都是一个消耗很高的操作，所以从性能的角度来看，应尽可能地避免排序或者对大量数据进行排序。
两次传输排序（旧版使用）
读取行直针和需要排序的字段，对齐进行排序，然后再根据排序结果读取所需要的的数据行。
这需要对数据进行两次传输读取，第二次读取的时候，因为是读取排序列进行排序后的所有记录，这回产生大量的随机IO，所以两次数据传输的成本非常高。
单次传输排序（新版本使用）
先读取查询所需要的的所有列，然后再给定列进行排序，最后直接返回排序结果，这就不需要对表进行两次读取，对于IO密集型的应用，这样做的效率高了很多。缺点是，如果需要返回的列非常多，非常大，会额外占用大量的空间，而这些列对排序操作本身来说是没有任何帮助的，因为单挑排序记录很大，所以有更多的排序快进行合并。
5.mysql查询优化器的局限性
5.1 关联子查询
mysql的子查询实现地非常糟糕，最糟糕的一类是条件中包含IN()的子查询语句，例如，我们希望找到演员id为1的人演过的所有影片，可能会这么写：
select film_name from film where film_id in (select film_id from film_actor where actor_id = 1)
我们眼里认为，sql会先执行in里面的语句，然后再执行外面的语句：
select film_name from film where film_id in (1,2,3)
其实不会，mysql会将相关的外层查询压倒子表中，他认为这样会更快：
select film_name from film
where exists (select film_id from film_actor where actor_id = 1 and film_actor.film_id = film.film_id)
mysql会先会对film表进行全表扫描，然后根据返回的film_id逐个进行子查询。如果这是一个非常大的表，这样的查询将会非常糟糕。当然我们可以用join来重写这个语句：
select film_name from film join film_actor on film_actor.film_id = film.film_id ;
5.2 在同一个表上进行查询和更新
mysql不允许在同一个表上进行查询和更新：
update tb1 set cnt = (select count (*) from tb1 where outter.tb1.type=inner.tb1.type);
6.优化特定类型的查询
6.1优化count()的查询
关于MyISAM，因为这种存储引擎会保存所有行数，但是是在没有where条件下的，有时候我们可以利用这一特性，来加速一些cont()的查询，比如：
select count(*) from city where id > 5;
假设我们这时候要扫描5000行数据，如果将条件反转一下，先查询<=5的行数，然后再用总行数减去它，也能获得相同的结果，不过速度会快很多。
select ( select count() from city ) -count() from city where id < = 5;
【深入理解MySql——查询性能优化】6.2优化关联查询