一 为什么查询速度会慢

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

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

在每一个消耗大量时间的查询案例中，我们都能看到一些不必要的额外操作、某些操作被额外地重复很多次、某些操作执行得太慢。优化查询得目的就是减少和消除这些操作所花费的时间。

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

对于低效的查询，通过下面两个步骤来分析：

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

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

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

典型案例：

  • 查询不需要的记录
  • 多表关联时返回全部列
  • 总是取出全部列
  • 重复查询相同的数据

2.2 MySQL是否在扫描额外的记录

衡量查询开销的三个指标：

  • 相应时间
  • 扫描的行数
  • 返回的行数

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

  • 使用索引覆盖扫描，把所有需要用的列都放在索引中，这样存储引擎无须回表获取对应行就可以返回结果了。
  • 改变库表结构，例如使用单独的汇总表。
  • 重写这个复杂的查询，让MySQL优化器能够以更优的方式执行这个查询。

三 重构查询的方式

在优化优问题的查询时，目的应该是找到一个更优的方法获得实际需要的结构——而不一定总是需要从MySQL获取一模一样的结果集。

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

目前网络速度比之前要快很多，可以根据需要将一个大的查询分解为多个小查询，从而减少MySQL扫描的行数。

3.2 切分查询

对于大查询可以“分而治之”，将大查询切分成小查询，每个查询功能一样，只完成一部分，每次只返回一小部分查询结果。

删除旧的数据就是一个好的例子。定期地清除大量数据时，如果用一个大的语句一次性完成的话，则可能需要一次锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但重要的查询。将一个大的DELETE语句切分成多个较小的查询可以尽可能小地影响MySQL性能，同时还可以减少MySQL复制的延迟。

3.3 分解关联查询

可以对一个表进行一次单表查询，然后将结果在应用程序中进行关联，这样做有如下优势：

  • 让缓存效率更高。许多应用可以方便地缓存单表查询对应的结果对象。
  • 将查询分解后，执行单个查询可以减少锁的竞争。
  • 在应用层做关联，可以更容易对数据库进行拆分，更容易做到高性能和可扩展。
  • 查询本身效率也有可能会有所提升。
  • 可以减少冗余记录的查询。在应用层做关联查询，意味着对于某条记录应用只需要查询一次，而在数据库中做关联查询，则可能需要重复地访问一部分数据。
  • 更进一步，这样做相当于在应用中实现了哈希关联，而不是使用MySQL的嵌套循环关联。

四 查询执行的基础

MySQL执行查询的过程如下图所示，执行过程大致为：

MySQL查询性能优化_客户端

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

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

两者之间通信协议时“半双工”的，这意味着，在任何一个时刻，要么是由服务器向客户端发送数据，要么是由客户端向服务器发送数据，这两个动作不能同时发生。

对于一个MySQL连接，或者说一个线程，任何时刻都有一个状态，该状态标识了MySQL当前正在做什么。可以使用SHOW FULL PROCESSLIST命令查看。状态主要有：

  • Sleep：线程正在等待客户端发送新的请求。
  • Query：线程正在执行查询或者正在将结果发送给客户端。
  • Locked：正在等待表锁。
  • Analyzing and statistics：线程正在收集存储引擎的统计信息，并生成查询的执行计划。
  • Copying to tmp table [on disk]：线程正在执行查询，并且将其结果集都复制到一个临时表中，这种状态一般要么是在做GROUP BY操作，要么是文件排序操作，或者是UNION操作。
  • Sorting result：线程正在对结果集进行排序。
  • Sending data：这表示多种情况：线程可能在多个状态之间发送数据，或者在生成结果集，或者在向客户端返回数据。

4.2 查询缓存（Query Cache）

通过检查一个大小写民反的哈希值来检查某个查询是否命中缓存。

4.3 查询优化处理

查询的生命周期的下一步是将一个SQL转换成一个执行计划，MySQL再依照这个执行计划和存储引擎进行交互。这包括了多个子阶段：解析SQL、预处理、优化SQL执行计划，这个过程中任何错误都可能终止查询。

语法解析器和预处理

MySQL通过关键字将SQL语句进行解析，并生成一棵对应的“解析树”。MySQL解析器将使用MySQL语法规则验证和解析查询。预处理器根据规则做进一步检查，图表和数据列是否存在、验证权限等。

查询优化器

一条语句有多种执行方式，最后都返回相同的结果，优化器的作用就是找到这其中最好的执行计划。

MySQL使用基于成本的优化器，它将尝试预测一个查询使用某种执行计划时的成本，并选择其中成本最小的一个。成本通过一系列的统计数据计算得来：每个表或者索引的页面个数、索引的基数（索引中不同值的数量）、索引和数据行的长度、索引分布情况。优化器再评估成本的时候并不考虑任何层面的缓存，它假设读取任何数据都需要一次磁盘I/O。

有很多中原因会导致MySQL优化器选择错误的执行计划，如下：

  • 统计信息不准确。
  • 执行计划中的成本估算不等同于实际执行的成本。
  • MySQL不考虑其他并发执行的查询。
  • MySQL不考虑不受其控制的操作的成本。

MySQL如何执行关联查询

MySQL对任何关联都执行嵌套循环关联操作，即MySQL先再一个表中循环取出单条数据，然后再嵌套循环到下一个表中寻找匹配的行，依次下去，直到找到所有表中匹配的行为止。然后根据各个表匹配的行，返回查询中需要的各个列。

排序优化

无论如何，排序都是一个成本很高的操作，所以从性能角度考虑，应尽可能避免排序或者尽可能避免对大量数据进行排序。

新版本使用的是单次传输排序算法，即先读取查询所需要的列，然后再根据给定列进行排序，最后直接返回排序结果。优点是，只需要一次顺序I/O读取所有的数据，而无须任何的随机I/O，缺点是，如果需要返回的列非常多、非常大，会额外占用大量的空间。

五 总结

优化的一些基本原则：

  • 尽量少做事，可以的话就不要做任何事情。除非不得已，否则不要使用轮询，因为这样会曾负载，还会带来很多低产出的工作。
  • 尽可能快地完成需要做的事情。尽量使用UPDATE代替先SELECT FOR UPDATE再UPDATE的写法，因为事务的速度越快，持有的锁时间就越短，可以大大减少竞争和加速串行执行效率。将已经处理完成和未处理的数据分开，保证数据集足够小。
  • 某些查询是无法优化的，可以考虑使用不同的查询或者不同的策略去实现相同的目的。