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

查询后面加上LIMIT子句
严格禁止SELECT*的写法
将重复查询相同的数据缓存起来，需要的时候从缓存中取出
用如下三种方式应用WHERE条件，从好到坏依次
- 在索引中使用WHERE条件来过滤不匹配的记录。这是在存储引擎层完成的。
- 使用索引覆盖扫描（在Extra列中出现了Using index）来返回记录，直接从索引中过滤不需要的记录并返回命中的结果。这是在MySQL服务器层完成的，但无须再回表查询记录。
- 从数据表中返回数据，然后过滤不满足条件的记录（在Extra列中出现Using where）。这在MySQL服务器层完成，MySQL需要先从数据表中读出记录然后过滤。
使用索引覆盖扫描，把所有需要用的列都放到索引中，这样存储引擎无须回表获取对应行就可以返回结果
改变库表结构

重构查询的方式

切分查询

将大查询切分成小查询，每个查询的功能完全一样，只完成一小部分，每次只返回一小部分查询结果。

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

DELETE FROM messages
WHERE created < DATE_SUB(NOW(), INTERVAL 3 MONTH);

一次删除一万行数据一般来说是一个比较高效而且对服务器影响最小的做法（如果是事务型引擎，很多时候小事务能够更高效）。同时，需要注意的是，如果每次删除数据后，都暂停一会儿再做下一次删除，也可以将服务器上原本一次性的压力分散到一个很长的时间段中，可以大大降低对服务器的影响，还可以大大减少删除时锁的持有时间。

rows_affected = 0
do {
    rows_affected = do_query(
        "DELETE FROM messages WHERE created < DATE_SUB(NOW(), INTERVAL 3 MONTH) LIMIT 10000"
    )
} while rows_affected > 0

分解联接查询

对每一个表进行一次单表查询，然后将结果在应用程序中进行联接。

用分解联接查询的方式重构查询有如下优势：

让缓存的效率更高。
将查询分解后，执行单个查询可以减少锁的竞争。
在应用层做联接，更容易对数据库进行拆分，更容易做到高性能和可扩展。
查询本身的效率也可能会有所提升。
可以减少对冗余记录的访问。在应用层做联接查询，意味着对于某条记录应用只需要查询一次，而在数据库中做联接查询，则可能需要重复地访问一部分数据。从这点看，这样的重构还可能会减少网络和内存的消耗。

在有些场景下，在应用程序中执行联接操作会更加有效。比如，当可以缓存和重用之前查询结果中的数据时、当在多台服务器上分发数据时、当能够使用IN()列表替代联接查询大型表时、当一次联接查询中多次引用同一张表时。

查询执行的基础

MySQL执行查询的过程：

客户端给服务器发送一条SQL查询语句。
服务器端进行SQL语句解析、预处理，再由优化器生成对应的执行计划。
MySQL根据优化器生成的执行计划，调用存储引擎的API来执行查询。
将结果返回给客户端。

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

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

客户端用一个单独的数据包将查询传给服务器，一旦客户端发送了请求，它能做的事情就只是等待结果了。

然而，一般的服务器响应给用户的数据通常很多，由多个数据包组成。当服务器开始响应客户端请求时，客户端必须完整地接收整个返回结果，而不能简单地只取前面几条结果，然后让服务器停止发送数据。

多数连接MySQL的库函数都可以获得全部结果集并将结果缓存到内存里，还可以逐行获取需要的数据。默认一般是获得全部结果集并将它们缓存到内存中。MySQL通常需要等所有的数据都已经发送给客户端才能释放这条查询所占用的资源，所以接收全部结果并缓存通常可以减少服务器的压力，让查询能够早点结束、早点释放相应的资源。

当使用多数连接MySQL的库函数从MySQL获取数据时，实际上都是从这个库函数的缓存获取数据。在返回一个很大的结果集时，库函数会花很多时间和内存来存储所有的结果集。如果能够尽早开始处理这些结果集，就能大大减少内存的消耗，在这种情况下可以不使用缓存来记录结果而是直接处理。这样做的缺点是，对于服务器来说，需要查询完成后才能释放资源，所以在和客户端交互的整个过程中，服务器的资源都是被这个查询所占用的。

查询状态

SHOW FULL PROCESSLIST命令（该命令返回结果中的Command列，其就表示当前的状态）。状态列如下：

Sleep: 线程正在等待客户端发送新的请求。
Query：线程正在执行查询或者正在将结果发送给客户端。
Locked：在MySQL服务器层，该线程正在等待表锁。在存储引擎级别实现的锁，例如InnoDB的行锁，并不会体现在线程状态中。
Analyzing and statistics：线程正在检查存储引擎的统计信息，并优化查询。
Copying to tmp table [on disk]：线程正在执行查询，并且将其结果集复制到一个临时表中，一般要么是在做GROUP BY操作，要么是在进行文件排序操作，或者是在进行UNION操作。如果这个状态后面还有“on disk”标记，那表示MySQL正在将一个内存临时表放到磁盘上。
Sorting result:线程正在对结果集进行排序。

查询优化处理

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

语法解析器和预处理

通过关键字将SQL语句进行解析，并生成一棵对应的“解析树”。解析器将使用MySQL语法规则验证和解析查询。例如，它将验证是否使用了错误的关键字，使用关键字的顺序是否正确，或者它还会验证引号是否能前后正确匹配。

然后，预处理器检查生成的解析树，以查找解析器无法解析的其他语义，例如，这里将检查数据表和数据列是否存在，还会解析名字和别名，看看它们是否有歧义。

查询优化器

解析树被认为是合法的了，由优化器将其转化成查询执行计划。一条查询可以有很多种执行方式，最后都返回相同的结果。优化器的作用就是找到这其中最好的执行计划。

MySQL使用基于成本的优化器，它将尝试预测一个查询使用某种执行计划时的成本，并选择其中成本最小的一个。

MySQL的查询优化器是一个非常复杂的软件，它使用了很多优化策略来生成一个最优的执行计划。优化策略简单地分为两种，静态优化和动态优化。

静态优化可以直接对解析树进行分析，并完成优化。例如，优化器可以通过一些简单的代数变换将WHERE条件转换成另一种等价形式。静态优化不依赖于特别的数值，如WHERE条件中带入的一些常数等。静态优化在第一次完成后就一直有效，即使使用不同的参数重复执行查询也不会发生变化，可以认为这是一种“编译时优化”。

然而，动态优化则和查询的上下文有关，也可能和很多其他因素有关，例如WHERE条件中的取值、索引中条目对应的数据行数等。这需要在每次查询的时候都重新评估，可以认为这是“运行时优化”。

优化类型

重新定义联接表的顺序
将外联接转化成内联接
使用代数等价变换规则
优化COUNT()、MIN()和MAX()
预估并转化为常数表达式
覆盖索引扫描：当索引中的列包含所有查询中需要使用的列的时候，MySQL就可以使用索引返回需要的数据，而无须查询对应的数据行。
子查询优化
提前终止查询
等值传播
列表IN()的比较：将IN()列表中的数据先进行排序，然后通过二分查找的方式来确定列表中的值是否满足条件，这是一个O（logn）复杂度的操作，等价地转换成OR查询的复杂度为O（n），对于IN()列表中有大量取值的时候，MySQL的处理速度将会更快。
表和索引的统计信息

联接查询

对于UNION查询，MySQL先将一系列的单个查询结果放到一个临时表中，然后再重新读出临时表中的数据来完成UNION查询。在MySQL的概念中，每个查询都是一次联接，所以读取临时表的结果也是一次联接。

MySQL对任何联接都执行嵌套循环联接操作，即先在一个表中循环取出单条数据，然后再嵌套循环到下一个表中寻找匹配的行，依次下去，直到找到所有表中匹配的行为止。最后根据各个表匹配的行，返回查询中需要的各列。MySQL会尝试在最后一个联接表中找到所有匹配的行，如果最后一个联接表无法找到更多的行，MySQL返回到上一层次的联接表，看是否能够找到更多的匹配记录，依此类推，迭代执行。

在MySQL 8.0.20版本之后，已经不再使用基于块的嵌套循环联接操作，取而代之的是哈希联接。这让联接操作性能变得更好，特别是当数据集可以全部存储在内存时。

排序优化

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

查询性能优化