SELECT语句是如何执行的

在学习和使用MySQL时，select语句几乎可以说是使用的最多的一类语句，但是在执行期间MySQL到底做了哪些操作呢，今天就来学习一下。

连接器

使用MySQL第一步就是连接MySQL服务，连接命令如下：

mysql -h#{ip} -u#{user} -p

MySQL建立连接时需要经过TCP三次握手，在完成三次握手后，MySQL服务会校验账号密码，如果一切正常就会查询该用户所对应的权限，并且将该权限保存起来，**之后所有的SQL执行都以此次保存的权限为准，即便中途权限修改了。**可参照下图验证结论，最开始只有select权限，中途修改为select update权限，最终依旧无法执行update语句。

查看MySQL被多少客户端连接

show processlist;

比如这里id为10的用户就处于睡眠状态，且持续时间为16966s，意思就是该用户连接之后就什么事不干了，处于空闲状态，并且空闲时长是16966s。

MySQL空闲连接会一直存活吗，如何清除空闲连接

MySQL定义了空闲连接最大连接时长，由wait_timeout参数控制，默认是8小时，超过这个时长会自动断开。不过我们也可以手动断开空闲的连接，命令是kill connection +id命令。

mysql> show variables like 'wait_timeout';
+---------------+---------+
| Variable_name | Value   |
+---------------+---------+
| wait_timeout  | 2880000 |
+---------------+---------+
1 row in set, 1 warning (0.05 sec)

mysql> kill connection +159;
Query OK, 0 rows affected (0.00 sec)

此外，杀死一个空闲的连接，此时该客户端并不会立马知道，需要等到下一次请求时才会收到报错，从下图可以看到，使用kill命令杀死连接后，permission用户看起来一切正常，随便执行一条SQL后才会收到报错信息。

mysql> show databases;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    160
Current database: *** NONE ***

+--------------------+
| Database           |
+--------------------+
| information_schema |
| learn              |
+--------------------+
2 rows in set (0.12 sec)

MySQL的连接数限制、长连接内存占用问题

MySQL最大连接数由max_connections参数控制，默认是151。

mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+
1 row in set, 1 warning (0.00 sec)

MySQL的连接与HTTP类似，分为短连接和长连接。短连接在每次执行请求都需要经历一次TCP三次握手和TCP四次挥手；而长连接则是在整个周期中只需要经历一次TCP三次握手和一次TCP四次挥手。

所以一般来说都是推荐使用长连接，但是长连接会导致内存占用过多，因为MySQL在执行查询过程中会临时使用内存管理连接对象，只有连接断开才会释放。如果长连接过多就会导致MySQL服务内存占用过高，最终会导致MySQL服务异常重启问题。

一般有两种方式解决内存占用过高问题：

• 定期断开长连接
• 客户端主动重置连接，可以通过mysql_reset_connection();函数重置连接。

到这里先小结一下MySQL连接器的作用：

• 通过TCP与客户端建立连接
• 校验账号密码
• 保存账号权限，并且此后所有的权限校验都以此次保存的为准，即使中途权限发生改变也不会受到影响。

查询缓存

在与客户端完成连接建立后，之后MySQL服务就会收到客户端发送来的SQL语句，如果SQL语句是select语句则会去查询缓存，这个缓存是key-value格式的，以SQL语句为key，查询结果为value，如果命中则直接返回，没有则继续往下面执行，等到执行完毕之后会将查询结果缓存起来。

不过对于一些频繁更新数据的表来说，缓存的命中率会很低，因为只要有一个表数据更新了会导致这个表所有的查询缓存都被清除。所以MySQL在8.0版本就删掉了这个查询缓存。

解析SQL

在执行SQL语句之前都会对SQL语句进行解析。这一步解析器主要完成两个事情：词法解析和语法分析。

以 select name from user;语句来说，词法解析会识别出来两个关键字：select和from，两个非关键字name和user，此时MySQL就知道了这是一个查询语句，要查询的表是user表。

语法分析则是根据词法解析的结果，判断SQL语句是否存在语法错误，如果没有语法问题就会构建出SQL语法树。

select id, name from user where age > 20 and city = '深圳';

生成语法树方便后面的执行流程获取查询条件，SQL语句类型，不过这里有一点需要注意：表不存在或者字段不存在并不是在这一阶段判断的，而是在后面的预处理阶段处理的。

SQL执行

经过解析后，接下来就是执行SQL，SQL执行被分成三个阶段：

• 预处理阶段，即prepare
• 优化阶段，即optimize
• 执行阶段，即execute

预处理阶段

在预处理阶段，主要是完成两件事：将select *替换成对应的字段；检查SQL中查询的字段名和表名是否存在。

这里贴个图证明上面的结论。

从图中的堆栈信息可以看到是在预处理阶段判断查询表或者字段是否存在。

优化阶段

接下来MySQL会给SQL语句生成一个执行计划，这一阶段就是确定SQL语句的执行方案，比如利用什么索引来执行查询，MySQL最终会根据查询成本来确定最终的执行方案。查询SQL语句的执行计划使用explain即可，比如下面例子：

MySQL查询成本是如何计算的

MySQL计算成本的公式为 查询成本 = I/O成本 + CPU成本。在Innodb、MyISAM等存储引擎中，数据都存储在磁盘上，当查询表记录时需要先将数据或者索引加载到内存中然后再执行查询，从磁盘加载到内存的过程就是I/O成本。而CPU成本则是读取以及检测记录是否满足查询条件、对结果集进行排序等这些操作所耗费的时间。可以通过以下命令查看成本常数：

# 查看server层对应的成本常数
mysql> select * from mysql.server_cost;
+------------------------------+------------+---------------------+---------+---------------+
| cost_name                    | cost_value | last_update         | comment | default_value |
+------------------------------+------------+---------------------+---------+---------------+
| disk_temptable_create_cost   |       NULL | 2023-11-20 17:48:15 | NULL    |            20 |
| disk_temptable_row_cost      |       NULL | 2023-11-20 17:48:15 | NULL    |           0.5 |
| key_compare_cost             |       NULL | 2023-11-20 17:48:15 | NULL    |          0.05 |
| memory_temptable_create_cost |       NULL | 2023-11-20 17:48:15 | NULL    |             1 |
| memory_temptable_row_cost    |       NULL | 2023-11-20 17:48:15 | NULL    |           0.1 |
| row_evaluate_cost            |       NULL | 2023-11-20 17:48:15 | NULL    |           0.1 |
+------------------------------+------------+---------------------+---------+---------------+
6 rows in set (0.02 sec)

# 查看存储引擎成本常数
mysql> select * from mysql.engine_cost;

查询一条select语句的查询成本可以使用explain+json，比如下面SQL：

explain format=json select * from user where age > 20;

查询结果如下所示：

1. 第一个 cost_info
   • query_cost：代表整个查询的成本估算值，这是整个查询执行的成本估算。
2. 第二个 cost_info
   • read_cost：表示为了执行这个步骤而读取数据的成本。
   • eval_cost：表示评估表达式的成本，即执行 WHERE 条件等表达式的成本。
   • prefix_cost：前缀成本，指的是到目前为止执行查询的总成本。
   • data_read_per_join：每次关联读取的数据量的估计值。

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "2.51"
    },
    "table": {
      "table_name": "user",
      "access_type": "range",
      "possible_keys": [
        "idx_age"
      ],
      "key": "idx_age",
      "used_key_parts": [
        "age"
      ],
      "key_length": "5",
      "rows_examined_per_scan": 5,
      "rows_produced_per_join": 5,
      "filtered": "100.00",
      "index_condition": "(`learn`.`user`.`age` > 20)",
      "cost_info": {
        "read_cost": "2.01",
        "eval_cost": "0.50",
        "prefix_cost": "2.51",
        "data_read_per_join": "10K"
      },
      "used_columns": [
        "id",
        "name",
        "age",
        "city"
      ]
    }
  }
}

执行阶段

在经过优化阶段后，MySQL已经确定了执行方案，接下来就是和存储引擎层进行交互查出最后的数据了，Server层和存储引擎层以记录为单位交互。以下面sql为例子简单说一下是交互的过程：

select * from user where id = 1;

因为是主键等值查询，所以最终是使用const查询，大致的过程如下：

• 因为是主键等值，所以会将id = 1传给存储引擎，存储引擎会返回符合条件的第一条记录
• 存储引擎通过聚簇索引B+树定位到id = 1的第一条记录，记录存在会返回记录给Server层，不存在则向上报错。

总结

总结一下select语句执行过程：

• 连接器：建立连接 管理连接 校验身份 权限控制
• 查询缓存：查询语句命中缓存则直接返回记录，否则往下执行，在MySQL8中已被删除
• 解析SQL：对SQL语句解析词法解析和语法分析，构建语法树，方便后续流程获取查询条件等信息
• 执行阶段
  • 预处理阶段：检查表和字段是否存在，将*替换为所有列
  • 优化阶段：根据查询成本确定最终SQL执行方案
  • 执行阶段：根据执行计划执行SQL，与存储引擎层交互，将查询结果返回给客户端

最后放两张图方便理解和记忆，完结，撒花。

参考资料：

SQL解析在美团的应用 - 美团技术团队 (meituan.com)

[11 索引出错：请理解 CBO 的工作原理 (lianglianglee.com)](https://learn.lianglianglee.com/专栏/MySQL实战宝典/11 索引出错：请理解 CBO 的工作原理.md)