vlambda博客
学习文章列表

我们先看一条SQL语句：

select * from user where id = 1;

通常情况下我们看到的只是输入一条SQL语句，然后执行返回一个结果，却不知道这条语句在MySQL内部是怎样的一个执行过程。

下面这张图是MySQL的基本架构示意图：

从图中可以看出，MySQL可以分为Server层和存储引擎层两部分。

Server层包括连接器，查询缓存，分析器，优化器和执行器等，涵盖MySQL的大多数核心服务功能以及所有的内置函数（如日期，时间，数学等），所有的的跨存储引擎的功能都在这一层实现（如存储过程，触发器，试图等）。

存储引擎负责存储存储数据和提取数据。它其实是一个插件形式的，支持InnoDB，MyISAM，Memory等存储引擎，现在最常用的存储引擎是InnoDB，从MySQL5.5.5开始也是默认的存储引擎了（创建表时可以使用engine=memory来指定我们想要使用的存储引擎）。

连接器

连接器负责跟客户端建立连接，获取权限，维持和管理连接。一般的连接命令如下：

mysql -hIP地址 -P端口号 -u用户名 -p

输入完上面的语句后会让你出去密码，当前密码也可以输入到-p后面，但是这样会导致密码泄露，所以不建议这样用。

输入完连接命令后，连接器就会用你刚刚输入的用户名和密码认证你的身份，这通常会有两种情况：

• 如果用户名或密码不对，就会提示“ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)”的错误

• 如果用户名密码认证通过，连接器会到权限表里查询你拥有的权限。以后这个连接里面的权限判断都会依赖此时读取到的权限。也就是说，一个用户建立连接成功，即使是管理员修改了这个用户的权限，也不会影响现有的连接权限，只有重新连接才能使用新的权限。

如果连接完成后没有后续的操作，那么这个连接就会处于空闲状态，可以使用 show processlist 命令查看空闲的连接，其中 Command 这列等于“Sleep”时表示这是一个空闲的连接，如下图：

如果客户端太长时间没有操作，连接器就会自动断开这个连接，这个时间是由 wait_timeout 控制的，默认是8小时。

在数据库里面，长连接是指连接成功后，如果客户端持续请求，则一直使用同一个连接，短连接则是指每次执行完很少的几次查询就断开连接，下次查询需要再重新创建一个新的连接。

创建新的连接的工程比较复杂，所以尽量使用长连接，但全部使用长连接有时候MySQL占用的内存会涨的特别快，这是因为MySQL在执行中使用的内存是管理在连接对象里面的，这些资源会在连接断开时释放。所以如果长连接累积下来，可能导致内存占用太大，被系统强行杀掉（OOM），从现象看就是 MySQL 异常重启了。

这个问题的解决方案是：

• 定期断开长连接。使用一段时间，或者程序里面判断执行过一个占用内存的大查询后，断开连接，之后要查询再重连。

• 如果你用的是 MySQL 5.7 或更新版本，可以在每次执行一个比较大的操作后，通过执行 mysql_reset_connection 来重新初始化连接资源。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态。

查询缓存

连接建立完成后，就可以执行select语句了。执行逻辑就会来到第二步：查询缓存。

MySQL拿到一个查询请求后，会先到查询缓存里看看之前是不是执行过这条语句，之前的查询结果使用key-value形式缓存在内存中的，key是查询的语句，value是查询的结果。如果我们的查询能够在缓存中直接找到，那这个value就会直接返回给客户端而不用执行后面的复杂的逻辑，这个效率会很高。

但是大多数情况下是不建议使用查询缓存的，因为查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。这样就可能出现一种情况是你费了很大劲才查出来的结果，放到缓存中，结果还没使用就因为另一个更新操作导致缓存全部清空了。除非我们的业务是一张静态表，比如系统配置信息表，很长时间才会更新一次。这样的表才适合查询缓存。

注意：MySQL8.0版本直接把查询缓存这块功能能删掉了，彻底没有这个功能了。

分析器

如果没有命中缓存，那么就要开始真正执行SQL语句了。首先经过的是分析器。

分析器先做“词法分析”，MySQL需要识别出SQL语句里面的字符串分别是什么，代表什么。

拿我们文章开头的那条SQL语句举例，MySQL从这条语句的 “select” 关键字识别出来这是一个查询语句，字符串 “user” 识别成 “表名user” ，把字符串 “id” 识别成 “列ID” 。

然后就是 “语法分析” 。分析器根据词法分析的结果判断你输入的SQL语句是否满足MySQL语法。如果语法不对就会报一个 “You have an error in your SQL syntax” 的错误提示。

注意：如果我们查了一个不存在的表或者不存在的列，MySQL也是在这一步做的校验。

优化器

经过了分析器，MySQL就知道我们要做什么了，然后就会经过优化器。

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

比如下面这条语句：

select * from t1 join t2 using(ID) where t1.c=10 and t2.d=20;

• 既可以先从表 t1 里面取出 c=10 的记录的 ID 值，再根据 ID 值关联到表 t2，再判断 t2 里面 d 的值是否等于 20。

• 也可以先从表 t2 里面取出 d=20 的记录的 ID 值，再根据 ID 值关联到 t1，再判断 t1 里面 c 的值是否等于 10。

这两种执行方法的逻辑产生的结果是一样的，但是执行效率会有不同，而优化器的作用就是决定选择哪一种方案。

执行器

执行器阶段就是开始执行我们的SQL语句了。

开始执行的时候，要先判断当前用户对这个表 user 有没有执行查询的权限，如果没有就返回没有权限的错误：

ERROR 1142 (42000): SELECT command denied to user 'b'@'localhost' for table 'user'

如果有权限，就打开表继续执行。打开表的时候，执行器会根据表的引擎选择不同的引擎提供的接口。还拿文章开头的SQL语句举例：

1. 调用 InnoDB 引擎接口取这个表的第一行，判断 id 值是不是 1，如果不是则跳过，如果是则将这行存在结果集中；

2. 调用引擎接口取“下一行”，重复相同的判断逻辑，直到取到这个表的最后一行。

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

到此，这个SQL语句就执行完了。

结束！