前面的章节我们介绍了如何设计最优的库表结构、如何建立最好的索引，这些对于高性能来说是必不可少的。但这些还不够 —— 还需要合理的设计查询。如果查询写得很糟糕，即使库表结构再合理、索引再合适，也无法实现高性能。

查询优化、索引优化、库表结构优化需要齐头并进，一个不落。

6.1 为什么查询速度会慢

通常来说，查询的生命周期大致可以按照顺序来看：从客户端，到服务器，然后在服务器上进行解析，生成执行计划，执行，并返回结果给客户端。其中“执行”包括了大量为了检索数据到存储引擎的调用以及调用后的数据处理，包括排序、分组等。

查询需要在不同的地方花费时间，包括网络，CPU计算，生成统计信息和执行计划、锁等待（互斥等待）等操作，尤其向底层存储引擎检索数据的调用操作，这些调用需要在内存操作、CPU操作和内存不足时导致的I/O操作上消耗时间。根据存储引擎不同，可能还会产生大量的上下文切换以及系统调用。

在每一个消耗大量时间的查询案例中，我们都能看到一些不必要的额外操作、某些操作被额外地重复了很多次、某些操作执行得太慢等。

6.2 慢查询基础：优化数据访问

查询性能低下最基本的原因是访问的数据太多。大部分性能低下的查询都可以通过减少访问的数据量的方式进行优化。对于低效的查询，我们发现通过下面两个步骤来分析总是很有效的：

  1. 确认应用程序是否在检索大量超过需要的数据。
  2. 确认MySQL服务层是否在分析大量超过需要的数据行。

是否向数据库请求了不需要的数据

有些查询会请求超过实际需要的数据，然后这些多余的数据会被应用程序丢弃。这会给MySQL服务器带来额外的负担，并增加网络开销，另外也会消耗应用服务器的CPU和内存资源。例如：

  1. 查询不需要的记录，应该用 Limit 杜绝
  2. 多表关联时返回全部列，应该取需要的列
  3. 总是取出全部列，应该取需要的列
  4. 重复查询相同的数据，应该放入缓存

MySQL 是否在扫描额外的记录

对于 MySQL，最简单的衡量查询开销的三个指标如下：

  • 响应时间
    • 服务时间 + 排队时间
  • 扫描的行数
    • 理想状态下，扫描的行数 = 返回的行数
  • 返回的行数

这三个指标都会记录到 MySQL 的慢日志中，所以检查慢日志记录是找出扫描行数过多的查询的好办法。

—— 21.7.15

访问类型

在 EXPLAIN 语句中的 type 列反应了访问类型。从全表扫描到索引扫描、范围扫描、唯一索引扫描、常数引用等。
如果查询没有办法找到合适的访问类型，那么解决的最好办法就是增加一个索引。索引让 MySQL 以最高效、扫描行数最少的方式找到需要的记录。

WHERE条件

  1. 在索引中使用 WHERE 条件来过滤不匹配的记录，这是在存储引擎完成的，也是效率最好的；
  2. 使用索引覆盖扫描（在 Extra 列中出现了 Using index）来返回记录；
  3. 从数据表中返回数据，然后过滤不满足条件的记录（在 Extra 中出现 Using where），这是在 MySQL 服务器层完成的，需要先从数据表读出记录然后过滤，性能最差；

扫描大量数据返回少数的行

如果遇上查询需要扫描大量的数据但只返回少数的行，那么可以尝试以下的技巧去优化它：

  1. 使用索引覆盖扫描；
  2. 改变库表的结构，例如使用单独的汇总表；
  3. 优化 SQL；

6.3 重构查询的方式

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

有时候，将一个大查询分解为多个小查询是很有必要的。

切分查询

分而治之，例如删除旧数据，可以分批执行，减少锁住的数据。

分解关联查询

对每一个表进行一次单表查询，然后将结果在应用程序中进行关联。例如进销存报表。

6.4 查询执行的基础

image.png

  1. 客户端发送一条查询给服务器。
  2. 服务器先检查缓存，如果命中了缓存，则立刻返回结果，否则进入下一个阶段。
  3. 服务器进行 SQL 解析、预处理，再由优化器生成对应的执行计划。
  4. MySQL 根据执行计划调用存储引擎的 API 来执行查询。
  5. 将结果返回给客户端。

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

MySQL 客户端和服务器之间的通信协议是“半双工”的，意思是在任何一个时刻，要么是服务器向客户端发送数据，要么是客户端向服务器发送数据，这两个动作不能同时发生。并且一旦一端开始发送消息，另一端要接收完整的消息才能响应它。

客户端用一个单独的数据包将查询传给服务器，可以通过参数 max_allowed_packet 配置最大长度。一旦客户端发送了请求，它能做的事情就只有等待结果了。

相反的，服务器响应给客户端的数据由多个数据包组成。MySQL 通常需要等所有的数据都已经发送给客户端才能释放这条查询所占用的资源，所以接收全部结果并缓存通常可以减少服务器压力。只有一种情况除外：如果需要返回一个很大的结果集时，存储结果至内存需要花费很多时间和内存，还不如直接让客户端接收了。

查询状态

MySQL 连接，或者说一个线程的状态，通过 show full processlist 查询。

  • Sleep
    • 线程正在等待客户端发送新的请求。
  • Query
    • 线程正在执行查询或正在将结果发给客户端。
  • Locked
    • 在MySQL服务器层，该线程正在等待表锁。InnoDB 的行锁不会体现在线程状态中。
  • Analyzing and statistics
    • 线程正在收集存储引擎的统计信息，并生成查询的执行计划。
  • Copying to tmp table [on disk]
    • 线程正在执行查询，并将结果集都复制到一个临时表中，这种状态一般要么是在做 group by，要么是文件排序，要么是 union。如果有 on disk 标记，表示正在将一个内存临时表放到磁盘上。
  • Sorting result
    • 线程正在对结果集进行排序。
  • Sending data
    • 线程可能在多个状态之间传送数据，或生成结果集，或向客户端返回数据。

查询缓存

在解析一个查询语句之前，MySQL 会优先检查缓存，这个检查是通过一个对大小写敏感的哈希查找来实现的。查询和缓存中的查询即使只有一个字节不同，那也不会匹配缓存结果。

查询优化器

查询优化器是一个非常复杂的部件，它使用了很多优化策略来生成一个最优的执行计划。

优化策略可以简单的分为两种：静态优化和动态优化。静态优化在第一次完成后就一直有效，即使使用不同的参数重复执行查询也不会发生变化，可以认为是编译时优化。动态优化则和查询的上下文有关，例如 where 条件中的取值，索引条目对应的数据行数等，这需要在每次查询时都重新评估，也叫运行时优化。优化类型如下：

  • 重新定义关联表的顺序
  • 将外连接转化为内连接
  • 使用等价变换规则
  • 优化 COUNT()、MIN() 和 MAX()
  • 预估并转化为常数表达式
  • 覆盖索引扫描
  • 子查询优化
    • 减少多个查询
  • 提前终止查询
    • 满足条件时立刻终止，例如 limit
  • 等值传播
  • 列表 IN() 的比较