mysq优化（6）：了解mysql执行的基础

如果我们希望mysql能以更高的性能运行查询，我们就需要了解mysql是如何优化和执行查询的。如此我们在实际的查询优化工作上就能遵循一些原则让优化器能够按照预想的合理的方式运行。

下面我们看看mysql执行一个查询的真实过程：

1. 客户端发送查询语句给服务器

2. 服务器先检查缓存，如果缓存命中，则返回缓存结果，否则进入下一阶段

3. 服务器进行sql解析、预处理，再由优化器生成对应的执行计划

4. mysql根据优化器生成的执行计划，调用存储引擎的API来执行查询

5. 将结果返回给客户端

1、mysql客户端/服务器通信协议

mysql发送查询语句给服务器、服务器返回查询结果给客户端，要了解这两部操作的过程，需要知道mysql客户端和服务器的通信协议。

mysql客户端和服务器端的通信协议是半双工的，即要么是服务器向客户端发送数据，要么是客户端向服务器发送数据，两个动作不能同时进行。客户端用一个数据包想服务器端发送请求，如果我们请求的SQL语句很长，如批量插入数据时，这会使客户端发送请求占用的时间很长，而且如果语句超出max_allowed_packet  mysql  会抛出错误信息，为此一般我们要把它拆分成多个小的sql语句处理，然后等待操作结果；而服务器响应客户端的数据，一般会响应比较多的数据，这些数据由多个数据包组成，且客户端必须完整的接收整个返回结果，然后才能让服务器端停止发送数据，

这要注意mysql库函数的应用，多数链接mysql的库函数都可以获得全部结果集并缓存到内存中。一般情况下，缓存到内存中的数据有利于mysql性能提升，但是如果返回的结果集过大，库函数就需要花很多的时间和内存来存储结果集。

mysql查询状态：对于一个mysql链接，或一个线程，任何一个时刻都有一个状态，标识mysql当前的动作。可以使用   show full processing 命令查看mysql状态。

下面是mysql查询中的集中状态：

1. SLEEP:线程正在等待客户端发送新的请求
2. QUERY: 线程正在查询或者正在将结果发送给客户端
3. LOCKED: 在mysql服务器层，改线程正在等待表锁---存储引擎级别的锁，如InnoDB的行锁不会提现在线程状态中，而MyISAM则经常出现该状态。
4. Analyzing and Statistics：线程正在收集存储引擎的统计信息，并生成查询的执行计划
5. Copying to tmp table [ on disk ]：线程正在执行查询，并将结果集复制到临时表中，这种状态一般是在做group by 操作、文件排序或union。如果有on disk 标记，标识mysql正在将内存临时表放到磁盘上。
6. Sorting result: 线程正在对结果集进行排序。
7. Sending data：线程可能在多个状态之间传送数据、生成结果集或者想客户端发送数据。

2、查询缓存

在解析一个查询语句之前，如果查询缓存是打开的，mysql会优先检查这个查询是否命中缓存中的数据。且这个查询是同通过一个对大小写敏感的哈希查找实现的，查询和缓冲中的查询即使有一个字节不同，也不会匹配缓存结果。

如果查询命中缓存，则不会再去解析sql语句，也不会后后续的3、4步的操作，而是直接返回结果，大大提高查询效率。

3、查询优化处理

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

语法解析器：mysql先通过关键字将SQL语句进行解析，并生成对应的解析树，并对起进行校验。

预处理：根据Mysql规则进一步检查解析树的合法性。

查询优化器：优化器将解析树转化成执行计划。而一个查询可以有多种执行方式，优化器的作用就是从中找到最优执行计划。查询优化器是mysql中非常重要且复杂的部件，这里简单介绍一下优化器的一些特点。

查询优化器：

Mysql查询优化器是基于成本的优化器，它会预测一个查询使用某种计划的成本，并选择成本最小的一个。

下面这些原因可能会导致Mysql选择错误的执行计划：

• 统计信息错误：Mysql依赖存储引擎提供的统计信息来评估成本，但存储引擎的统计信息有可能出现偏差。
• 执行计划的估算成本与实际成本不符：有时候某个执行计划需要读取更多的页面，但实际成本却更小，如这些页面都是顺序读取或已经被缓存到内存中。
• Mysql的最优与我们想象的最优不同
• Mysql不考虑并发执行的查询，这可能会影响查询的速度。
• Mysql有时会基于一些固定原则而不是基于成本优化，如存在全本搜索的Match() 子句，则会存在全文索引时使用全文索引
• Mysql不会考虑不受控制的操作成本，如执行存储过程、用户自定义函数的成本
• 优化器有时不会估算所有可能的执行计划，它可能会错过实际最优的执行计划

mysql优化器优化策略分为静态优化和动态优化：静态优化可以直接对解析树进行分析并完成优化，不依赖于特别的数值，依次完成后就一直有效，如通过简单的代数变换将where条件转换成另一种等价形式； 动态优化和查询的上下文有关，也可能和多种其他因素有关，如where条件的取值、索引中条目对应数据行数等，它需要每次在查询的时候进行评估，可以理解为 “运行时优化”。

mysql处理的优化类型有：

• 重新定义关联表顺序
• 将外连接转化成内连接
• 使用等价变换规则
• 优化count() 、min()、max()
• 预估并转化为常数表达式
• 覆盖扫描索引
• 子查询优化
• 提前终止查询
• 等值传播
• 列表IN() 的比较

 

4、查询执行引擎

这一阶段是真正执行查询操作，执行引擎用来开始真正执行查询操作的工具。它根据之前生成的执行计划，按照执行计划逐步执行。

5、返回结果给客户端

查询执行的最后一个阶段是将结果返回给客户端。及时查询不需要返回结果给客户端，MySQL仍然会返回这个查询的信息，如该查询影响到的行数。

在这个阶段中，如果查询是可以被缓存的，那么MySQL在这个阶段也将会被存放到查询缓存中。

MySQL将结果集返回给客户端是一个增量地、逐步返回的过程。这样做有两个好处：服务器端无需存储太多的结果，也就不会因为要返回太多结果而消耗太多内存。这样的处理也会让MySQL客户端第一时间获得返回的结果。

结果集中的每一行都会以一个满足MySQL客户端/服务器通信协议的封包发送，再通过TCP协议进行传输，在TCP传输中，可能会对MySQL的封包进行缓存然后批量传输