MySQL执行查询的过程
- 客户端发送一条查询给服务器。
- 服务器先检查查询缓存，如果命中了缓存，则立刻返回存储在缓存中的结果。否则进入下一阶段。
- 服务器端进行SQL解析、预处理，再由优化器生成对应的执行计划。
- MySQL根据优化器生成的执行计划，调用存储引擎的API来执行查询。
- 将结果返回给客户端。
为什么查询会慢？
:::warning 如果把查询看作是一个任务，那么它由一系列子任务组成，每个子任务都会消耗一定的时间。如果要优化查询，实际上要优化其子任务，要么消除其中一些子任务，要么减少子任务的执行次数，要么让子任务运行得更快。 :::
MySQL在执行查询的时候有哪些子任务，哪些子任务运行的速度很慢？这里很难给出完整的列表。
通常来说，查询的生命周期大致可以按照顺序来看：从客户端，到服务器，然后在服务器上进行解析，生成执行计划，执行，并返回结果给客户端。其中“执行”可以认为是整个生命周期中最重要的阶段，这其中包括了大量为了检索数据，对存储引擎的调用以及调用后的数据处理，包括排序、分组等。
在完成这些任务的时候，查询需要在不同的地方花费时间，包括网络，CPU计算，生成统计信息和执行计划、锁等待（互斥等待）等操作，尤其是向底层存储引擎检索数据的调用操作，这些调用需要在内存操作、CPU操作和内存不足时导致的I／O操作上消耗时间。根据存储引擎不同，可能还会产生大量的上下文切换以及系统调用。
在每一个消耗大量时间的查询案例中，我们都能看到一些不必要的额外操作、某些操作被额外地重复了很多次、某些操作执行得太慢等。优化查询的目的就是减少和消除这些操作所花费的时间。
再次申明一点，对于一个查询的全部生命周期，上面列的并不完整。这里我们只是想说明：了解查询的生命周期、清楚查询的时间消耗情况对于优化查询有很大的意义。有了这些概念，我们再一起来看看如何优化查询。
慢查询基础：优化数据访问
查询性能低下最基本的原因是访问的数据太多。某些查询可能不可避免地需要筛选大量数据，但这并不常见。大部分性能低下的查询都可以通过减少访问的数据量的方式进行优化。对于低效的查询，我们发现通过下面两个步骤来分析总是很有效：
- 确认应用程序是否在检索大量超过需要的数据。这通常意味着访问了太多的行，但有时候也可能是访问了太多的列。
- 确认MySQL服务器层是否在分析大量超过需要的数据行。
6.2.1 是否向数据库请求了不需要的数据
有些查询会请求超过实际需要的数据，然后这些多余的数据会被应用程序丢弃。这会给MySQL服务器带来额外的负担，并增加网络开销，另外也会消耗服务器的内存和CPU资源。
反面案例：
- 查询不需要的记录。在查询后总是加上limit；
- 多表关联时返回全部列。
- 总是取出全部列。只取需要的列，不要用select *;
- 重复查询相同的数据。
6.2.2 MySQL是否在扫描额外的记录
在确定查询只返回需要的数据以后，接下来应该看看查询为了返回结果是否扫描了过多的数据。对于MySQL，最简单的衡量查询开销的三个指标如下：
- 响应时间
- 扫描的行数
- 返回的行数
没有哪个指标能够完美地衡量查询的开销，但它们大致反映了MySQL在内部执行査询时需要访问多少数据，并可以大概推算出查询运行的时间。这三个指标都会记录到MySQL的慢日志中，所以检查慢日志记录是找出扫描行数过多的查询的好办法。
响应时间
要记住，响应时间只是一个表面上的值。这样说可能看起来和前面关于响应时间的说法有矛盾？其实并不矛盾，响应时间仍然是最重要的指标，这有一点复杂，后面细细道来。
响应时间是两个部分之和：服务时间和排队时间。服务时间是指数据库处理这个查询真正花了多长时间。排队时间是指服务器因为等待某些资源而没有真正执行查询的时间-可能是等I／O操作完成，也可能是等待行锁，等等。我们无法把响应时间细分到上面这些部分，除非有什么办法能够逐个测量上面这些消耗，不过很难做到。一般最常见和重要的等待是I／O和锁等待，但是实际情况更加复杂。
所以在不同类型的应用压力下，响应时间并没有什么一致的规律或者公式。诸如存储引擎的锁（表锁、行锁）、高并发资源竞争、硬件响应等诸多因素都会影响响应时间。所以，响应时间既可能是一个问题的结果也可能是一个问题的原因，不同案例情况不同。
当你看到一个查询的响应时间的时候，首先需要问问自己，这个响应时间是否是一个合理的值。实际上可以使用“快速上限估计”法来估算查询的响应时间，概括地说，了解这个查询需要哪些索引以及它的执行计划是什么，然后计算大概需要多少个顺序和随机I/O，再用其乘以在具体硬件条件下一次I/O的消耗时间。最后把这些消耗都加起来，就可以获得一个大概参考值来判断当前响应时间是不是一个合理的值。
扫描的行数和返回的行数
分析查询时，查看该查询扫描的行数是非常有帮助的。这在一定程度上能够说明该查询找到需要的数据的效率高不高。
对于找出那些“糟糕”的查询，这个指标可能还不够完美，因为并不是所有的行的访问代价都是相同的。较短的行的访问速度更快，内存中的行也比磁盘中的行的访问速度要快得多。
理想情况下扫描的行数和返回的行数应该是相同的。但实际情况中这种“美事”并不多。例如在做一个关联查询时，服务器必须要扫描多行才能生成结果集中的一行。扫描的行数对返回的行数的比率通常很小，一般在1：1和10：1之间，不过有时候这个值也可能非常非常大。
扫描的行数和访问类型
在评估查询开销的时候，需要考虑一下从表中找到某一行数据的成本。MySQL有好几种访问方式可以查找并返回一行结果。有些访问方式可能需要扫描很多行才能返回一行结果，也有些访问方式可能无须扫描就能返回结果。
在EXPLAIN语句中的type列反应了访问类型。访问类型有很多种，从全表扫描到索引扫描、范围扫描、唯一索引查询、常数引用等。
如果查询没有办法找到合适的访问类型，那么解决的最好办法通常就是增加一个合适的索引，索引让MySQL以最高效、扫描行数最少的方式找到需要的记录。
例如，我们看看示例数据库Sakila中的一个查询案例：
mysql> SELECT * FROM sakila.film_actor WHERE film_id=1;
这个查询将返回10行数据，从EXPLAIN的结果可以看到，MySQL在索引 idx_fk_film_id上使用了ref访问类型来执行查询。
一般MySQL 能够使用如下三种方式应用WHERE条件，从好到坏依次为：
- 在索引中使用WHERE条件来过滤不匹配的记录。这是在存储引擎层完成的。
- 使用索引覆盖扫描（在Extra列中出现了Using index）来返回记录，直接从索引中过滤不需要的记录并返回命中的结果。这是在MySQL服务器层完成的，但无须再回表查询记录。
- 从数据表中返回数据，然后过滤不满足条件的记录（在Extra列中出现Using Where）。这在MySQL服务器层完成，MySQL需要先从数据表读出记录然后过滤。
好的索引可以让查询使用合适的访问类型，尽可能地只扫描需要的数据行。但也不是说增加索引就能让扫描的行数等于返回的行数。
如果发现查询需要扫描大量的行，但是只返回少量的行，那么通常可以尝试下面的技巧去优化它：
- 使用覆盖索引扫描，把所有需要用到的列都放到索引中，这样存储引擎无需回表获取对应行就可以返回结果了。
- 改变库表结构，例如使用单独的汇总表。
- 重写这个复杂的查询，让MySQL优化器能够以更优的方式执行这个查询。
若有收获，就点个赞吧
0 人点赞
