前言

所以，本篇文章就针对MySQL查询相关的内容进行介绍，包含：执行查询的过程、查询消耗的关键指标、查询的优化等，通过这些内容希望能够帮助你明白一些执行较慢的查询产生的原因，以及如何才能避开它们。

执行查询的过程逻辑

下图展示了MySQL执行查询的大致过程逻辑。

了解这张图，将有助于你理解MySQL服务器的，总体来说，MySQL服务器可以分为两层：服务层和存储引擎层。

服务层包含了连接器、查询缓存、解析器、优化器、执行器以及所有内置函数、存储过程、触发器、视图等。

而存储引擎层主要就是负责数据的存储与查询，并且支持以插件形式进行替换，服务器通过存储引擎API进行交互，这些API屏蔽了不同存储引擎之间的差异，存储引擎通常只需包含少量的底层函数，然后通过一些组合即可满足各种查询需求，比如获取第一行数据，获取下一行数据，通过这两个API即可实现查询所有数据的功能，目前最常用的存储引擎就是InnoDB。

连接器

连接器主要负责服务端与客户端建立连接、管理连接等，一旦连接建立完成，在服务端就会产生一个空闲连接，如果用show processlist查看，就会显示为sleep。

如果超过wait_timeout时间都没有请求的话，连接器就会断开这个连接。

查询缓存

查询缓存用处不大，现在基本上客户端都会自己进行缓存，所以MySQL在8.0版本中也已经删除了此功能。

解析器

解析器主要用来检查查询语句，对语法进行分析，检查表、字段是否存在等情况。

优化器

到了优化器就负责对执行计划的生成了，优化器会决定使用哪个索引，采用哪种join方式以及其他非常多的优化技巧。

执行器

最后，执行器就会根据优化器生成的执行计划，调用存储引擎的接口，大致流程就是取一行，然后判断是否符合条件，符合就存下来，不符合就跳过，然后继续取下一行，直到取到最后一行为止，然后将结果集返回给客户端。

衡量查询开销的三个指标

知道查询的消耗有哪些，将更有助于我们理解查询优化的方式以及设计，一般来说衡量查询开销的指标主要有如下三个：响应时间、扫描行数、返回的行数。

响应时间

响应时间包含又服务时间和排队时间，服务时间指的是执行查询真正花费的时间，而排队时间指的是服务器因为等待I/O资源、锁等资源时消耗的时间，两部分时间加起来则是一次请求所需要的全部时间，而这个时间通常是需要我们能大致判断出来的。

扫描行数

扫描行数是判断一个查询效率是否足够高的一个重要指标，从一定程度上来讲扫描行数越多查询也就越慢。

扫描的数据页多少也能反映出扫描行数的多少

对比主键查询

返回的行数

最完美的情况当然是，扫描的行数等于返回的行数，也就是要什么就查什么，但很遗憾，大多数情况下扫描行数与返回的行数的比例大约在1:1到10:1之间。

重构查询的几种选择

结合查询消耗的关键指标来分析，重构查询的目的就在于采取一些手段来尽量减少这些指标的消耗。

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

考虑到现在网络连接的速度以及连接池等手段，发起多次小而快的查询已经不是什么严重的问题了，言外之意就是如果能将一个复杂的查询拆分为几个简单的小查询，那也是不错的选择，这样拆分出来的逻辑，常常还可以通过其他方式进一步优化，比如join查询被拆分后，单张表的结果就可以被缓存起来，因此，你应该好好评估一下是否可以这样做。

切分查询

切分查询的目的是为了避免一次性产生过多的资源消耗，比如一次性要删除非常多的数据，这将会造成锁数据的范围非常大、事务日志的写入非常多，这些都会带来大量的线程排队等待的消耗。通常我们的建议是将一次大的操作，拆分为多次小的操作，分批完成。

关联查询

关联查询在日常满足业务需求中是非常常见的查询方式，同时也经常是造成慢查询的主要原因，MySQL其实也针对关联查询做了很多优化，尽量的提高其查询效率，但有些情况MySQL也并不能直接来进行查询重构，还是需要使用者有意识的做出一些调整才能命中到事先设计好的一些优化方式中，所以，接下来，让我们了解一下关联查询的一些查询设计吧！

如何完成一次关联查询？

首先，先了解一下关联查询是如何实现的，比如如下的一条查询语句：select b.* from a inner join b on a.id = b.id where a.col in (1, 2);

要实现关联查询，在代码逻辑上来说，本质就是嵌套循环的查询，下面是一段伪代码逻辑，大致表现出了处理的逻辑：

for each row in t1 matching range {
  for each row in t2 matching reference key {
    for each row in t3 {
      if row satisfies join conditions, send to client
    }
  }
}

既然是嵌套循环，那就可以考虑是不是尽力让小循环嵌套大循环更好一点，毕竟内层循环如果是利用索引查找，时间复杂度是较低的。

下面这个案例可以很好的体现这一点，t_user_1表的数据比t_user_2表的数据要多。

加上straight join关键字后，可以让MySQL按照指定的顺序做表关联查询。

所以在做关联查询时，找到合适的关联顺序是非常关键的，但如果关联的表比较多时，MySQL也不可能枚举每一种关联的方式（3张表有6种组合，4张表有24种组合），再决定采取哪一种更合适，当关联表的数量超过optimizer_serach_depth时，就会采用一些贪心的计算策略。

解决关联查询的原则

MySQL针对一些关联查询会采取额外的优化方式，比如我们在执行计划能看到：Index Nested-Loop Join、Block Nested Loop、Index Condition Pushdown等，这些优化的核心思想都是尽量减少磁盘I/O的消耗，尤其是随机磁盘I/O的问题，而解决的方式无非就是把数据尽量一次性加载到内存中来进行过滤，过滤前还可以先将数据排序好，必减少随机访问的问题。所以，本质上任何优化方式都还是在围绕三个关键指标来进行选择。

总结