一条mysql 语句的背后

Mysql 的基本架构图

MYSQL 的整个架构可以分为 server 层 和存储引擎层2个部分，其中

Server 层 包括：

• 连接器
• 查询缓存
• 分析器
• 优化器
• 执行器

server 层涵盖了 Mysql 的大多少核心服务功能以及内置函数

存储引擎层 负责数据的存储和提取其架构是插件式的，支持 InnoDB、MyISAM、Memory 等存储引擎，目前主流的是InnoDB，从 mysql 5.5.5 开始默认使用InnoDB 引擎。

连接器

当我们连接数mysql时 ，通常我们会-h 指定服务器IP -u 指定用户名 -p 指定密码，mysql 默认的端口是 3306，然后输入密码

$ mysql -h 192.168.10.1 -u root  -p
Enter password:

这个时候连接器就开始工作了，连接器负责跟客户端建立连接、获取权限、维持和管理连接。

在完成 TCP 握手后，连接器会认证你的身份，这个输入需要你输入用户名和密码，如果密码不对你会收到一条Access denied for user 的错误，如下所示：

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

如果认证通过，连机器或到权限表里面查出当前用户所拥有的权限，之后，这个连接里面的权限判断逻辑，都会依赖此时所读取到的权限。

一旦用户建立连接成功后，即使你用管理员账号对用户的权限做了修改，也不会影响已经连接的权限。

连接完成后，如果后续没有动作，这个连接就处于空闲状态，可以通过show processlist; 查看，其中 sleep就表示现在系统里面有一个空闲状态。

mysql> show processlist;
+----+------+-----------+------+---------+------+----------+------------------+
| Id | User | Host      | db   | Command | Time | State    | Info             |
+----+------+-----------+------+---------+------+----------+------------------+
|  5 | root | localhost | NULL | Query   |    0 | starting | show processlist |
|  7 | root | localhost | NULL | Sleep   |    6 |          | NULL             |
+----+------+-----------+------+---------+------+----------+------------------+
2 rows in set (0.00 sec)

客户端如果太长时间没有动静，连接器就会自动断开，这个时间是由参数 wait_timeout 控制的，默认是 8小时，如果在连接被断开之后，客户端再次发送请求，就会收到如下错误：

ERROR 2013 (HY000): Lost connection to MySQL server during query

在5.7以前，这个超时时间的相关参数可以在 information_schema 库下的 SESSION_VARIABLES 表中查询，而5.7 以后，直接查询该表会提示

ERROR 3167 (HY000): The 'INFORMATION_SCHEMA.SESSION_VARIABLES' feature is disabled; see the documentation for 'show_compatibility_56'

如果希望沿用information_schema中进行查询的习惯，5.7提供了show_compatibility_56参数，设置为ON可以兼容5.7之前的用法。

5.7 以后System and status 变量需要从performance_schema中进行获取，要获取系统超时时间，我们可以执行如下语句查看系统超时时间的相关设置：

mysql> select variable_name,variable_value from performance_schema.session_variables where variable_name in ('interactive_timeout','wait_timeout');
+---------------------+----------------+
| variable_name       | variable_value |
+---------------------+----------------+
| interactive_timeout | 28800          |
| wait_timeout        | 28800          |
+---------------------+----------------+
2 rows in set (0.00 sec)

interactive_timeout 参数是针对交互式连接，例如通过mysql客户端连接数据库是交互式连接，

wait_timeout针对非交互式连接，例如通过jdbc连接数据库是非交互式连接。

如果需要修改 WAIT_TIMEOUT ，可以执行语句：

mysql> set session WAIT_TIMEOUT=10;
Query OK, 0 rows affected (0.00 sec)

如果要修改INTERACTIVE_TIMEOUT 则执行语句:

mysql> set session INTERACTIVE_TIMEOUT=10;
Query OK, 0 rows affected (0.00 sec)

在连接启动的时候，根据连接的类型，来确认会话变量wait_timeout的值是继承于全局变量wait_timeout，还是interactive_timeout。

mysql> select variable_name,variable_value from global_variables where variable_name in('interactive_timeout','wait_timeout')
    -> ;
+---------------------+----------------+
| variable_name       | variable_value |
+---------------------+----------------+
| interactive_timeout | 28800          |
| wait_timeout        | 28800          |
+---------------------+----------------+
2 rows in set (0.00 sec)

设置全局 wait_timeout

mysql> set global wait_timeout=20;
Query OK, 0 rows affected (0.00 sec)

mysql> select variable_name,variable_value from global_variables where variable_name in('interactive_timeout','wait_timeout')
    -> ;
+---------------------+----------------+
| variable_name       | variable_value |
+---------------------+----------------+
| interactive_timeout | 28800          |
| wait_timeout        | 20             |
+---------------------+----------------+
2 rows in set (0.01 sec)

控制连接最大空闲时长的wait_timeout参数。

对于非交互式连接，类似于jdbc连接，wait_timeout的值继承自服务器端全局变量wait_timeout。

对于交互式连接，类似于mysql客户单连接，wait_timeout的值继承自服务器端全局变量interactive_timeout。

判断一个连接的空闲时间，可通过show processlist输出中Sleep状态的时间

由于建立连接比较复杂，需要经过TCP 握手、权限认证等等，因此建议尽量使用长连接。但是全部使用长连接，会导致 MYSQL 内存占用过高，因为MYSQL 在执行过程中临时使用的内存是管理在连接对象里面的，这些资源需要连接断开才会释放。如果长时间积累会导致内存 OOM从而导致MYSQL 异常重启。

解决办法：

1.定期断开连接。

2.在5.7以后的版本可以在执行比较大的操作后，通过 mysql_reset_connection 来重新初始化连接资源，该过程无需重新做权限验证。

查询缓存

连接建立后，可以执行查询语句，那么执行逻辑就会来到第二步，查询缓存。

mysql 拿到一个请求后会先到缓存查询，看看之前是不是执行过这条语句。之前执行过的语句以及其结果会以key-value 的形式被直接缓存在内存中，key是查询语句，value是查询结果。

如果语句不在查询缓存中，就会继续后面的执行阶段。执行完成后，执行结果会被缓存在查询缓存中。

但是大多数情况下不建议使用查询缓存，因为查询缓存弊大于利。因为查询缓存失效频繁，只要对一个表做更新，这个表的所有查询缓存都会被清空。除非你是一张静态表，很久才更新。

与查询缓存相关的参数：

mysql> SHOW GLOBAL VARIABLES LIKE 'query_cache%';
+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| query_cache_limit            | 1048576 |
| query_cache_min_res_unit     | 4096    |
| query_cache_size             | 1048576 |
| query_cache_type             | OFF     |
| query_cache_wlock_invalidate | OFF     |
+------------------------------+---------+
5 rows in set (0.00 sec)

使用 query_cache_type 变量来开启查询缓存，开启方式有三种：

• ON : 正常缓存。表示在使用 SELECT 语句查询时，若没指定 SQL_NO_CACHE 或其他非确定性函数，则一般都会将查询结果缓存下来。

• DEMAND ：指定SQL_CACHE才缓存。表示在使用 SELECT 语句查询时，必须在该 SELECT 语句中指定 SQL_CACHE 才会将该SELECT语句的查询结果缓存下来。

• OFF： 关闭查询缓存。

开启查询缓存，修改my.cnf 中，query_cache_type = OFF ，为 ON

注意：当 my.cnf 中，query_cache_type = OFF 启动mysql服务后，在mysql命令行中使用上面语句开启查询缓存，会报错

mysql> set global query_cache_type=1;
ERROR 1651 (HY000): Query cache is disabled; restart the server with query_cache_type=1 to enable it

分析器

如果没有命中查询缓存，就开始执行真正的SQL 语句了，首先分析器会做”词法分析”，例如你输入一条语句：

select * from user where Host='localhost'；

则 MYSQL 会从你输入的 select 这个关键词开始识别，这是一个查询语句，然后把 user 识别为一个表名，把Host 识别为一个列Host。

接下里会进行”语法分析”，语法分析器会根据语法规则，判断你的SQL 语法是否满足 MYSQL语法。如果语法不对，则出现如下错误：

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'q' at line 4

优化器

在sql 语句经过了分析器，MYSQL 就指定你要做什么了，，在开始执行之前，还需要经过优化器的处理。

优化器是在表里有多个索引的时候，决定使用哪个索引，或者在一个语句有关联的时候决定各个表的连接顺序。

优化器阶段完成后，就会进入执行阶段。

执行器

通过分析器知道了你要做什么，通过优化器知道了该怎么做，接下来就进入执行阶段了。

开始执行语句的时候，要判断一下你对这个表有没有执行查询的权限，如果没有就会返回权限错误，如果有就打开表继续执行，打开表的时候，执行器会根据表的引擎定义去使用这个引擎的接口，比如我们使用的引擎是 InnoDB 引擎，那么上面的语句:

select * from user where Host='localhost'；

的执行流程如下：

1.调用引擎接口区这个表的第一行,判断Host 是否是 localhost，如果不是，则跳过，如果是则将这行存在结果集中

2.调用引擎接口取下一行，继续判断，直到最后一行。

3.执行器将上述遍历过程中满足条件的行组成的记录集返回客户端。

至此，执行语句就完成了。

对于有索引的表，执行逻辑类似。

你会在数据库的慢查询日志中看到一个Rows_examined 的字段，这个值就是执行器每次调用引擎获取数据行的时候累加的。

# Time: 2019-03-20T05:11:59.731673Z
# User@Host: root[root] @ localhost []  Id:    22
# Query_time: 10.000289  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
use mysql;
SET timestamp=1553058719;
select sleep(10);

在有些场景下，执行器调用一次，在引擎内部则扫描了多行，因此引擎扫描行数并非完全相同。

本篇是一篇学习笔记，大部分内容来源于 极客时间 《Mysql 实战 45 讲》