一条mysql 语句的背后

Mysql 的基本架构图

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

Server 层 包括:

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

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

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

连接器

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

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

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

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

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

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

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

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

1
2
3
4
5
6
7
8
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小时,如果在连接被断开之后,客户端再次发送请求,就会收到如下错误:

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

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

1
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中进行获取,要获取系统超时时间,我们可以执行如下语句查看系统超时时间的相关设置:

1
2
3
4
5
6
7
8
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 ,可以执行语句:

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

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

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

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

1
2
3
4
5
6
7
8
9
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

1
2
3
4
5
6
7
8
9
10
11
12
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是查询结果。

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

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

与查询缓存相关的参数:

1
2
3
4
5
6
7
8
9
10
11
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命令行中使用上面语句开启查询缓存,会报错

1
2
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 语句了,首先分析器会做”词法分析”,例如你输入一条语句:

1
select * from user where Host='localhost';

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

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

1
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 引擎,那么上面的语句:

1
select * from user where Host='localhost';

的执行流程如下:

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

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

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

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

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

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

1
2
3
4
5
6
# 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 讲》