面试详解 -- 一条SQL查询语句是如何执行的

一、MySQL基本架构

MySQL可以分为Server和存储引擎两部分，从图中可以看出不同的存储引擎共用同一个Server层

1、Server层

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

2、存储引擎层

负责数据的存储和提取，其架构模式是插件式的，支持Memory、MyISAM、InnoDB等多个引擎，默认InnoDB

二、语句执行过程

测试语句为select * from t where ID = 10

1、连接器

首先应该连接到该数据库上，此时为连接器负责。
连接器负责跟客户端建立连接、获取权限、维持和管理连接，命令为

mysql -h $ip -P $port -u $user -p
Enter password: ***

连接命令中的mysql是客户端（mysqld才是服务端），用来与服务端建立连接。

• 完成TCP握手
• 验证用户名和密码
• 到权限表中查询该用户权限，之后，在这个连接中的权限判断逻辑，都将依赖于此时读到的权限。也就是说，建立连接后再修改权限必须重新建立连接后才能生效

查询连接状态的命令为

mysql> show processlist;

若建立连接后没有后续操作，则连接处于空闲状态，Command列表示为Sleep，断开某个连接的命令为

mysql> kill $thread_id;

客户端如果太久没有操作，一段时间后会自动断开连接，这个时间由wait_timeout控制，一般默认为8小时，针对的是空闲连接，如果在这时间内此连接进行操作，那么该时间需要重新计算；
如果连接被断开时发送请求，会收到错误提醒：Lost connection to MySQL server during query

wait_timeout可用如下命令查看，

// 此命令是查看会话变量，与这句命令的意义相同：show session variables like 'wait_timeout';
mysql> show variables like 'wait_timeout';
// 查看全局变量为
mysql> show global variables like 'wait_timeout'

set variables like 'wait_timeout'该命令设置的wait_timeout为会话变量，重新建立连接则恢复默认值；
show global variables like 'wait_timeout'该命令设置的wait_timeout为全局变量，重新启动服务则恢复默认；
永久设置应去配置文件中进行修改。

长连接：指连接成功后，如果客户端持续有请求，则一直使用该连接
短连接：指很少的几次查询后，就断开连接
长/短连接不是由参数设置的，这个名词指的是一种“行为”，你执行完少数几次查询后就断开就是短连接，所以想使用长连接就把wait_timeout时间设长一点就可以了

建立连接的过程是比较复杂的，所以在使用的时候一般都使用长连接，但使用长连接后，Mysql占用内存会长的非常快。因为MySQL在执行过程中临时使用的内存是管理在连接对象里面的。这些资源会在连接断开的时候才释放。所以如果长连接累积的话就会导致内存占用过大，被系统强行杀掉（OOM），从现象看就是MySQL异常重启了。
解决方法：

1. 定期断开长连接。使用一段时间或者程序里面判断执行过一个占用内存的大查询后，断开连接，之后要查询再重连。
2. MySQL5.7即以上版本，可以在执行一次大的更新后，通过执行mysql_reset_connection()来初始化连接资源。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态。mysql_reset_connection()是C的api，不是命令，官方文档为：https://dev.mysql.com/doc/c-api/8.0/en/mysql-reset-connection.html

2、查询缓存

连接建立完成后，进入第二步：查询缓存
当mysql收到一条命令时，首先去判断查询缓存功能有没有开启，如果开启，会判断sql语句是select还是其他的
对于select语句尝试在查询缓存中查看该命令有没有在之前被执行过，执行过的语句及其结果可能会以key-value对的形式存在，被直接缓存到内存中。key是查询的语句，value是查询的结果。
如果在缓存中可以找到这个key，那么这个value就会直接返回给客户端。如果语句不在查询缓存中，则会继续执行后续操作。执行完毕后将结果存入查询缓存之中。
但这样往往弊大于利，只要对一个表进行更新，那么这个表上的查询缓存就会被清空。可能很费劲地存入了结果，还没用就被清空了。对于一个更新频繁的表，查询缓存的命中率非常低，对不常更新的静态表，比如系统配置表用查询缓存才是合适的。
MySQL中查询缓存的开放与否是可以自己设置的，由参数query_cache_type控制

query_cache_type = 0/OFF:关闭查询缓存
query_cache_type = 1/ON:开启查询缓存
query_cache_type = 2/DEMAND:当使用SELECT SQL_CACHE会开启缓存（推荐）

需要注意的是，MySQL8中取消了查询缓存的功能。

3、分析器

真正执行语句的第一步是分析器。首先，MySQL需要知道你要做什么，因此需要对SOL语句进行分析，在分析阶段会判断语句是否正确，表是否存在、列是否存在等

• 先“词法分析”，MySQL需要识别出你输入的一条SQL语句的字符串分别是什么、代表什么，比如select * from t where ID=1，MySQL通过词法分析可以知道select表示查询语句、t识别为“表名t”，ID识别为“列ID”
• 再“语法分析”。根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这个SQL语句是否满足MySQL语法
• 得到解析树，然后预处理器对解析树进一步分析，验证数据表、字段是否存在

4、优化器

通过分析器，明白你要干啥后，数据库会针对你的需求想一个最优的解决方案，也就是执行计划，这个最优方案选择的操作，就是优化器要做的事情。

5、执行器

MySQL通过分析器知道你要做什么，通过优化器知道要怎么做，就到了执行器中进行执行了。

1. 先判断用户对这张表有无查询权限

注意：关于权限认证的几个要点

1. 在工程上，如果命中缓存，则要在缓存返回结果时检测用户有无权限
2. 查询也会在优化器前调用precheck验证权限
3. 但因为像触发器、存储过程这种涉及到其他表的操作，分析不出来，得在执行器阶段才能确定，所以在执行器阶段也需要再做一次优化

2. 如果有权限，则打开表继续执行，打开表的时候，执行器会根据这个表的引擎定义，去调引擎的API接口来操作数据库

1. select * from t where ID=1查找的时候如果没有索引，则进行的都是全表扫描，由第2点可以知道扫描全表这个操作并不是引擎层在做，而是执行器调用引擎接口在做
2. 因为innodb是以page为一个单位的，所以每次引擎层（默认innodb）读取1个page的数据，而不是1行，server层是每次调用innodb接口读取一行数据，然后where判断是否符合条件，如果符合就存入结果集中，接着去扫描下一行，最后全表扫描（无索引）后将最终结果集返回给客户端
3. 对于有索引的表，执行的逻辑差不多，第一次调用的是“取满足条件的第一行”这个接口，之后循环取“满足条件的下一行”这个接口，这些接口在引擎层都已经定义好

3. 如果缓存开启状态，mysql会更新缓存