高性能的MySQL（6）查询执行机制（上）

谁借莪１个温暖的怀抱￠ 2022-04-08 15:13 149阅读 0赞

希望优化查询性能的时候，最好的办法是弄清楚MySQL是如何优化和执行查询的。了解了内部机制，才能更好的实施设计。

当MySQL执行一个查询时，到底做了什么，先看一副图吧：

[![183417787.png][]][183417787.png]

1、客户端发送一条查询给服务器。

2、服务器先检查查询缓存，如果命中了缓存，则立刻返回缓存中的结果，否则进入下一步。

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

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

5、将结果返回给客户端

上面的每一步都很复杂，特别是查询优化器这部分，接下来介绍每一个步骤的具体内容。

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

MySQL客户端和服务端之间的通信协议是“半双工”的，意思是在任何一个时刻，要么是由服务端向客户端发送数据，要么是客户端向服务器发送数据，这2个动作不能同时发生。一旦一端开始发生消息，另一端要接收完整个消息才能响应对方。

客户端用一个单独的数据包传给服务器，这也是为什么当查询的语句很长的时候，参数max\_allowed\_packet就特别重要了，服务器会拒绝太大的数据抛出相应错误。

相反的，服务器给客户端是数据一般比较多，由多个数据包组成，这也是为什么查询时必须加上LIMIT限制的原因。

多数连接MySQL的库函数都可以获得全部结果集并缓存到内存中，默认一般是获得全部结果集并缓存在内存中。但是如果需要返回一个很大的结果集，这样做并不好，因为库函数会花费很多时间来存储所有结果集，但是这样也会在客户端交互的整个过程一直占用服务器资源。

我们举例PHP来说明一下：

<?php
    $link = mysql_connect('localhost','root','123456');
    $result = mysql_query('select * from emp',$link);
    while($row = mysql_fetch_array($result)){
    //do something
    }

不是需要的时候，才去服务器端取数据，而是mysql\_query的时候，php就已经将整个结果缓存到内存中，然后从内存中取出数据，如果不想缓存可以使用mysql\_unbuffered\_query()代替mysql\_query()

<?php
    $link = mysql_connect('localhost','root','123456');
    $result = mysql_unbuffered_query('select * from emp',$link);
    while($row = mysql_fetch_array($result)){
    //do something
    }

对于一个MySQL连接，或者说一个线程，任何时候都有一个状态，可以使用showfullprocesslist命令来查看，结果中Command字段显示了这一类的状态

[![211035528.png][]][211035528.png]

这里对状态做一下简单解释

Sleep：等待客户端发送请求。

Query：线程正在执行查询或者正在将结果发送给客户端。

Locked：线程正在等待表锁，对于MyISAM是一个比较典型的状态，InnoDB的行锁不会体现出来

Analyzingandstatistics：线程正在收集存储引擎的索引统计信息，并生成执行计划。

Copyingtotmptable\[ondisk\]：正在执行查询，并将结果复制到一个临时表，一般是groupby,文件排序，union操作，如果出现ondisk，表示正在将一个内存临时表放到磁盘临时表上。

Sortingresult：线程正在对结果进行排序。

Sendingdata：这个包括多个状态间，或者生成结果集，或者给客户端返回数据多个情况。

2、查询缓存

在解析一个语句之前，如果查询是打开的，MySQL会先检查这个查询是否会命中缓存中的数据，如果命中，直接从缓存中拿结果给客户端，这种情况查询不会被解析，不会生成执行计划，也不会执行。

3、查询优化器

这个过程是非常复杂的，包括解析SQL，预处理，优化SQL执行计划。

a、语法解析和预处理

语法解析，主要是检测语法规则，比方说错误的关键字，关键字顺序等。

预处理会检测表，列是否存在，这个过程通常很快。

b、语法合格的SQL会由优化器转化成执行计划，一个查询可以有很多执行方式，优化器的作用就是找到最好的执行计划。

MySQL使用基于成本的优化器，选择一个成本最小的。可以通过查询当前会话的last\_query\_cost的值来看查询成本。

[![213719982.png][]][213719982.png]

这是根据一系列的统计信息计算得来的，但是很多原因会导致mysql优化器选择错误的执行计划。

比方说统计信息不准确，而且预算的成本不一定等于实际的成本，也不定是最优的执行计划。

下面一些是MySQL能够优化的类型：

》重新定义关联表的顺序

》将外连接转换成内连接

》使用等价变换规则，例如（5=5anda>5）将被改写为a>5

》优化count()，min()，max()，比如查找一个最大的值，只需读取B-Tree索引的最后一条，如果看到EXPLAIN下的Extra中看到Selecttablesoptimizeaway。

有点必须说明：没有任何条件的count(\*)会使用引擎提供的一些优化，比方说MyISAM维护了一个变量来存放总行数，所以速度非常快，看下图

[![220517465.png][]][220517465.png]

》预估并转换常数表达式，表达式会被转化为常数，特别是数学表达式，甚至一个查询也能够转化为一个常数。比方说：

explain select film.film_id,film_actor.actor_id
    from film
    inner jion film_actor using(film_id)
    where film.film_id=1;

》覆盖索引扫描

》子查询优化

》提前终止查询，当发现以满足查询条件的时候，或者条件不成立的时候。

》等值传播

select film.film_id
    from film
    inner join film_actor using(film_id)
    where film.film_id > 500;

》列表IN()的比较，用二分查找代替转化成多个等值.

等等优化器所能做的远不止这些，有兴趣的朋友可以深入研究下。

[183417787.png]: /images/20220404/8fb608139bcb4e9f93652897fd5353fa.png
[211035528.png]: /images/20220404/a605c33d30b0478aa9815e2c509b21b9.png
[213719982.png]: /images/20220404/d79c671cbbd14c269ad86c088fc293f0.png
[220517465.png]: /images/20220404/f3d5bce29fbc46e39dd4200b03901b2e.png