在上一篇文章 MySQL(五)|千万级大数据查询优化第二篇: 查询性能优化 (1) 中讲到一条 SQL 的查询执行路径如下图 5-1 所示:
图 5-1
步骤如下:
客户端发送一条查询给服务器.
服务器先检查查询缓存, 如果命中了缓存, 则立刻返回存储在缓存中的结果. 否则进行下一阶段.
服务器端进行 SQL 解析, 预处理, 再由优化器生成对应的执行计划.
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书籍的第六章. 因为我这边没有使用到, 就不做过多的讲解啦.
来源: http://www.jianshu.com/p/607542bc7b4f