通过一条查询SQL语句看Mysql的运行机制

说明：本文环境是Mysql5.7+ 版本。弄清楚了SQL语句的执行流程也就弄明白了MYSQL的运行机制

目标：执行 select * from user where id=10; SQL的过程分析

mysql> select * from user where id = 1;
+----+------+
| id | name |
+----+------+
|  1 | jack |
+----+------+
1 row in set (0.00 sec)

在客户端里编写SQL语句，想要发送命令到MYSQL服务器，显然第一步应该是建立与MYSQL服务端连接

1.建立连接（Connectors & Connection Pool）

MYSQL客户端与服务端建立连接的通信方式是 “半双工”，并且对于每一个MYSQL的连接，时刻都有一个线程状态标识当前这个连接正在做什么

通信方式：

• 半双工：某一时刻，要么在发送数据，要么在接收数据，不能同时进行。
• 全双工：某一时刻，能同时发送和接收数据。
• 单工：只能发送数据，或者只能接收数据

查看一个连接的线程状态信息可以使用show processlist命令

mysql> show processlist;
+-----+------+-----------+-------+---------+------+----------+------------------+
| Id  | User | Host      | db    | Command | Time | State    | Info             |
+-----+------+-----------+-------+---------+------+----------+------------------+
| 224 | root | localhost | lagou | Query   |    0 | starting | show processlist |
+-----+------+-----------+-------+---------+------+----------+------------------+
1 row in set (0.00 sec)

客户端与MYSQL服务端建立好连接后，就可以向服务端发送命令了。这里可以类比一下mybatis中的查询过程，mybatis查询时首先会去查询一级缓存，如果命中就直接返回查询结果，没有命中就查询DB，并将查询结果缓存到一级缓存中。同样在mysql的查询时，也会先去查询缓存，如果命中就返回否则就会走后面一系列处理过程，包括语法语义解析、预处理、查询优化等等，所以建立好连接之后下一步就是到查询缓存中查找是否命中。

---

2.查询缓存（Cache & Buffer）

查询缓存功能需要开启，如果没有开启查询缓存或者在查询缓存中没有命中完全相同的SQL语句，那么就会交由解析器进行语法语义解析，生成解析树。

如何查询是否开启查询缓存？

mysql> show variables like '%query_cache%';
+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| have_query_cache             | YES     |
| query_cache_limit            | 1048576 |
| query_cache_min_res_unit     | 4096    |
| query_cache_size             | 1048576 |
| query_cache_type             | ON      |
| query_cache_wlock_invalidate | OFF     |
+------------------------------+---------+
6 rows in set (0.01 sec)

参数说明：

• have_query_cache：设置查询缓存是否可用
• query_cache_type：使用查询缓存的方式。有3个值可以设置（0代表关闭查询缓存OFF，1代表开启ON，2代表当sql语句中有SQL_CACHE关键词时才缓存）
• query_cache_size：分配多少内存大小给查询缓存使用
• query_cache_limit：查过这个值后查询结果就不会被缓存

需要注意有些情况下即使开启了查询缓存，以下情况SQL结果也不会缓存

1. 查询语句使用SQL_NO_CACHE (SELECT sql_no_cache id,name FROM tableName;)
2. 查询的结果大于query_cache_limit大小
3. 查询中使用到了一些不确定的函数，比如now()
4. query_cache_type=2时需要在SQL中明确指定SQL_CACHE才会缓存
5. 对一个表有过更新操作的，这个表上的所有查询缓存都被清空

那如何查看执行的SQL语句是否走了查询缓存呢？我们可以查看缓存的状态信息

在执行 SELECT id, name FROM USER; 这条SQL语句前缓存状态信息如下：

mysql> show status like 'Qcache%';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| Qcache_free_blocks      | 1       |
| Qcache_free_memory      | 1031832 |
| Qcache_hits             | 0       |
| Qcache_inserts          | 0       |
| Qcache_lowmem_prunes    | 0       |
| Qcache_not_cached       | 3       |
| Qcache_queries_in_cache | 0       |
| Qcache_total_blocks     | 1       |
+-------------------------+---------+
8 rows in set (0.00 sec)

当执行了上述查询语句之后再次查看缓存状态信息如下：

mysql> select id, name from user;
+----+------+
| id | name |
+----+------+
|  1 | jack |
|  2 | tom  |
+----+------+
2 rows in set (0.00 sec)

mysql> show status like 'Qcache%';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| Qcache_free_blocks      | 1       |
| Qcache_free_memory      | 1030296 |
| Qcache_hits             | 0       |
| Qcache_inserts          | 1       |   // 插入了一条缓存记录
| Qcache_lowmem_prunes    | 0       |
| Qcache_not_cached       | 3       |
| Qcache_queries_in_cache | 1       |
| Qcache_total_blocks     | 4       |	// 缓存块大小发生变化
+-------------------------+---------+
8 rows in set (0.00 sec)

观察缓存状态信息说明有SQL执行结果放到缓存中。接着再次重复执行上述查询SQL后观察缓存状态信息

mysql> select id, name from user;
+----+------+
| id | name |
+----+------+
|  1 | jack |
|  2 | tom  |
+----+------+
2 rows in set (0.00 sec)

mysql> show status like 'Qcache%';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| Qcache_free_blocks      | 1       |
| Qcache_free_memory      | 1030296 |
| Qcache_hits             | 1       |	// 缓存命中次数+1
| Qcache_inserts          | 1       |
| Qcache_lowmem_prunes    | 0       |
| Qcache_not_cached       | 3       |
| Qcache_queries_in_cache | 1       |
| Qcache_total_blocks     | 4       |
+-------------------------+---------+
8 rows in set (0.00 sec)

通过观察查询前后缓存状态信息变化，可以确认执行重复查询SQL时确实是走了查询缓存。那么问题来了，我们知道如果查询缓存没有命中情况下，SQL的执行引擎要与底层的存储引擎或物理文件交互，得到查询结果并返回给客户端。若开启了查询缓存，会先将SQL语句和查询结果完整的保存在查询缓存中，之后如有相同的SQL语句执行会直接返回结果。

那问题是：查询缓存如果没有命中，就会直接与存储引擎或物理文件进行交互拿到查询结果吗？

不是这样的！mysql的运行机制是在缓存没有命中后，会先交由解析器进行语法语义解析判断和查询优化器进行优化，然后才会交由查询执行引擎调用底层API接口查询结果并返回客户端。

---

3.解析器（Parser）

解析器会将客户端发送过来的SQL语句进行语法解析，解析成 “解析树”。

词法解析：我们输入的一条SQL语句是由多个字符和空格组成，mysql需要知道我们要做什么，因此词法分析会识别出SQL语句里面的字符串分别代表什么。

比如 select id, name from user; 经由词法分析后被识别成四个部分：

关键字	非关键字	关键字	非关键字
select	id, name	from	user

语法解析：关键字select被识别出来表示这是一个查询语句，语法解析就是判断输入的这个SQL语句是否满足MYSQL的SELECT语法，满足就会生成下面这样的一颗语法树（解析树），不满足就会报错。

生成解析树后，预处理器会根据MYSQL的规则进一步的检查“解析树”是否合法。例如会检查数据列与数据表是否存在，名字与别名是否有歧义等，最终生成新的“解析树”。

当“解析树”被认为是合法的之后，查询优化器会根据解析树生成最优的执行计划。

---

4.查询优化器（Optimizer）

查询优化器的任务是发现执行SQL查询的最佳方案即最优的执行计划。

MYSQL使用很多的查询优化策略生成最优的执行计划。比如查找最适合的索引、SQL条件等价变化、基于联合索引调换位置、一些内置函数的优化、提前终止查询limit、in语句优化等等。

使用explain语句查看解释执行计划

mysql> explain select id, name from user where id = 1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | user  | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

---

5.查询执行引擎

查询执行引擎负责执行 SQL 语句，此时查询执行引擎会根据 SQL 语句中表的存储引擎类型，以及对应的API接口与底层存储引擎缓存或者物理文件的交互，得到查询结果并返回给客户端。若开启用查询缓存，这时会将SQL 语句和结果完整地保存到查询缓存(Cache&Buffer)中，以后若有 相同 的 SQL 语句执行则直接返回结果。

6.返回结果给客户端

查询最后一个阶段就是将查询结果返回给客户端。如果查询结果可以被缓存，这时会将SQL 语句和结果完整地保存到查询缓存中。

如果查询结果过多，采用增量模式返回。 增量模式好处：服务端不需要存储太多的结果而消耗太多的内存。

---

7.查询流程图

8.MYSQL读与写过程异同

请看我的另一篇博文 《通过一条更新SQL语句看MYSQL的运行机制》