MySQL（六）｜《千万级大数据查询优化》第二篇：查询性能优化（2）

在上一篇文章MySQL（五）｜《千万级大数据查询优化》第二篇：查询性能优化（1）中讲到一条SQL的查询执行路径如下图5-1所示：

图5-1

步骤如下：

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

这一章节详细讲解下这一过程。

1、通信协议

MySQL客户端和服务端之间的通信协议是“半双工”的，也就是说在任何一个时刻，那么是由服务端向客户端发送数据，要么是由客户端向服务端发送数据，这两个动作不能同时发生。并且，一旦一端开始发送消息，另一端要接收完整个消息才能响应它。这就像来回抛球的游戏：在任何时刻，只有一个人能控制球，并且只有控制球的人才能将球抛回去（发送消息）。

当服务端开始响应客户端请求时，客户端必须完整地接收整个返回结果，而不是简单地只取前面几条结果，然后让服务器停止发送数据。所以在前一章节的时候我们强烈在分页时一定要使用LIMIT限制MySQL只返回需要的数据的原因。

2、查询状态

每一个MySQL查询，任何时间都会有一个状态，该状态表示了MySQL当前正在做什么。我一般使用SHOW FULL PROCESSLIST命令查看当前SQL执行，包括执行状态、是否锁表等信息，如下图5-2所示。

图5-2

该命令返回结果中的Command列表示当前的状态。在一个查询额生命周期中，状态会变化很多次。MySQL官方手册中对这些状态值的含义有最权威的解释。

• Sleep 线程正在等待客户端发送新的请求。
• Query 线程正在执行查询或者正在将结果发送给客户端。
• Locked 在MySQL服务器层，该线程正在等待表锁。在存储引擎级别实现的锁，例如InnoDB的行锁，并不会体现在线程状态中。对于MyISAM来说这是一个比较典型的状态，但是其他没有行锁的引擎中也经常会出现。
• Analyzing and statistics 线程正在收集存储引擎的统计信息，并生成查询的执行计划。
• Copying to tmp table on disk 线程正在执行查询，并且将结果集都复制到一个临时表中，这种状态一般要么是在做 GROUP BY 操作，要么是文件排序操作，或者是UNION操作。如果这个状态后面还有“on disk”标记，那表示MySQL正在将一个内存临时表放到磁盘上。
• Sorting result 线程正在对结果集进行排序。
• Sending data 这表示多种情况：线程可能在多个状态之间传送数据，或者在生成结果集，或者在客户端发送请求。

了解这些状态的基本含义非常有用，这可以让我们很快了解当前“谁正在持球”。

3、查询缓存

当缓存中有需要查询的数据时，会直接从缓存中拿到结果并返回给客户端，不会生成执行计划，SQL也不会被执行。关于缓存更多的内容以后再讲。

4、查询优化处理

任何缓存中没有需要的数据，下一步就是服务器端进行SQL解析、预处理，再由优化器生成对应的执行计划。

SQL解析和预处理主要验证是否使用了错误的关键字、语法是否有误、数据表和数据列是否存在，名称和别名是否有歧义等问题。

当语法验证都是合法的之后，优化器会将SQL转化为执行计划。一条SQL查询有很多种执行方式，最后都返回相同的结果。优化器的作用就是找到这其中最好的执行计划。

5、查询执行引擎

调用存储引擎的API来执行查询，这里没有太多可以优化的地方。

6、返回结果给客户端

查询执行的最后一个阶段是将结果返回给客户端。

关于查询性能优化话题更多的内容，建议读者自己阅读相关的内容，比如《高性能MySQL》书籍的第六章。因为我这边没有使用到，就不做过多的讲解啦。