MySQL的架构及查询sql的执行流程（一）

MySQL 的整体架构

Server 层

大多数 mysql 核心服务都位于这一层，主要包括连接器、查询缓存、分析器、优化器、执行器等，所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图，函数（如日期、时间、数学和加密函数等）等，还有一个通用的日志模块 binglog 日志模块。不同的存储引擎共用一个 Server 层

存储引擎层

主要负责数据的存储和提取。其架构模式是插件式的，包括 InnoDB、MyISAM、Memory 等多个可选的存储引擎，InnoDB 在 5.5.5 后成为默认存储引擎。存储引擎通过 API 与上层进行通信，这些 API 屏蔽了不同存储引擎之间的差异，使得这些差异对上层查询过程透明。mysql 的存储引擎架构将查询处理以及其他任务系统和数据的存储处理分离开来，这样做的好处在于可以根据需要灵活选择存储引擎，使得 mysql 的架构具备很大的灵活性

Server 层

连接器

连接器主要用于连接处理、授权认证、安全相等相关功能

如果采用连接命令，则如下：mysql -h$ip -P$port -u$user -p，当客户端通过 TCP 三次握手连接到 mysql 服务器时，服务器对首先其进行身份认证，可以通过用户名和密码的方式进行认证，也可以通过 SSL 证书进行认证。登录认证通过后，服务器还会查询出当前用户拥有的权限，之后，这个连接里面的权限判断逻辑，都将依赖于此时读到的权限。这就意味着，一个用户成功建立连接后，即使你用管理员账号对这个用户的权限做了修改，也不会影响已经存在连接的权限。修改完成后，只有再新建的连接才会使用新的权限设置

每个客户端的连接都对应着服务器上的一个线程。服务器的连接器上维护了一个线程池，避免为每个连接都创建销毁一个线程

如果连接完成后，未来的一段时间里没做任何操作，这个连接就处于空闲的状态，通过 show processlist 命令中看到空闲连接列表，如下所示，Sleep 标志就表示一个空闲连接

查询缓存

查询缓存：主要用来缓存我们所执行的 SELECT 语句以及该语句的结果集

连接建立后，执行查询语句的时候，会先查询缓存，mysql 会先校验这个 sql 是否执行过，之前执行过的 sql 语句会以 Key-Value 的形式缓存在 mysql 内存中，Key 是查询 sql 语句，Value 是结果集。如果缓存 key 被命中，就会直接返回给客户端，如果没有命中，就会执行后续的操作，完成后也会把结果缓存起来，方便下一次调用。当然在真正执行缓存查询的时候还是会校验用户的权限，是否有该表的查询条件

MySQL 查询不建议使用缓存，有两个原因

• 查询缓存的操作是由一个单一的全局锁来控制，这时候大量的查询将被阻塞，直至锁释放。所以不要简单的认为设置缓存必定会带来性能的提升
• 缓存失效的问题：mysql 查询缓存的失效很容易，只要对表有任何的更新，这个表的所有查询缓存就会全部被清空。这就很有可能出现缓存还没被使用过就被清空的情况，或者说一个更新将所有缓存都清除的情况。专业来说就是：缓存命中率低，一般只有那种只查询不更新的表适用缓存，但是这样的表往往很少存在，一般都是什么配置表之类的

自 MySQL 5.6（2013）以来，默认情况下已禁用查询缓存，而在 MySQL 8.0 之后，查询缓存的整块功能已被移除，移除的原因：https://mysqlserverteam.com/mysql-8-0-retiring-support-for-the-query-cache/

分析器

如果存在查询缓存且缓存没有命中，或者没有查询缓存，那么接下来会真正的开始 sql 执行，首先会走到分析器。分析器主要是用来分析 sql 语句是来干嘛的，解析 sql 语句，即词法分析和语法分析

• 词法分析：sql 语句有很多单词、空格，mysql 就需要识别每个字符串所代表的是什么，是关键字，还是表名，还是列名等等
• **语法分析：根据词法分析的结果，语法分析会判断你 sql 的语法对错，错了会提醒you have an error in your SQL syntax 错误，并且会提示你哪里错了，一般来说语法错误会提示第一个出现错误的位置，所以你要关注的是紧接 use near 的内容

优化器

经过了分析器后，即使 sql 语句语法没问题，在真正执行 sql 之前，还需要经过优化器的优化处理。优化器会对你的 sql 选择他认为最优的执行方案去执行，但优化器的选择也并不一定是最优的

通常优化器的作用

• 在 sql 中使用多个索引的时，决定使用哪个最优索引
• 在一个语句有多表关联 join 的时候，重新定义各个表的连接顺序。也就是说数据表的关联并不总是按照在查询中指定的顺序决定
• 重写查询，消除冗余的操作，选择子任务最优的策略。比如在 WHERE 之后跟一个 1 = 1 的条件，显然，查询条件中的 1 = 1 是完全多余的。在经过优化器优化之后的 sql 中这个条件将被去除。但有时候优化器的选择并不是最好的
• 将外连接转换为内连接。并非所有的 outer join 都必须以外连接的方式执行，优化器能够识别等价条件并重写查询
• 优化 count()、min()、max()，比如 MyISAM 就维护了一个变量来记录总数，比如 mix()、 max() 的字段如果有索引，则直接取索引树的最左边和最右边的值即可
• 提前终止查询。查询已经满足条件时，mysql 总是能够立刻终止查询，比如使用了limit 字句的时候
• 索引覆盖扫描。如果索引中包含需要返回的所有数据，则无需回表查询对应的数据行
• 负责生成执行计划，也就是常说的通过 EXPLAIN 看到的内容

执行器

经过了优化器之后可以说这个语句具体该如何执行就已经定下来，接下来就进入执行器阶段，开始执行 sql

• 首先会对当前用户进行执行权限校验，如果没有权限，就会返回没有权限的错误：ERROR 1142 (42000): SELECT command denied to user
• 如果有权限，就会去调用引擎的接口，返回接口执行的结果。执行的时候，从第一行开始一行一行的去判断是否满足条件，读取一行的接口都是执行引擎定义好的，直接调用即可，直到取到表的最后一行，有索引的执行起来可能就好点，执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端

一条查询 sql 的执行流程

• 首先通过连接器连接到数据库，然后校验是否有执行当前查询的权限，如果没有权限，则直接返回错误信息，如果有权限，则执行下一步
• 在 mysql 5.6 之前还会查询缓存，以这条 sql 语句为 key 在内存中查询是否有结果，如果有直接缓存，如果没有找到缓存，则执行下一步。mysql 5.6 及其之后默认都不会查询缓存，直接执行下一步
• 通过分析器进行词法分析，识别出这条 sql 语句字符串里面的字符串分别是什么，代表什么意思，比如到底是操作的哪个表，哪个字段等等，随后进行语法分析，校验 sql 语法比如关键词的使用是否有误等。如果分析器检查没问题就执行下一步
• 通过优化器对 sql 进行各种优化，比如在 sql 中使用多个索引的时候，决定使用哪个最优索引，或者在一个语句有多表关联 join 的时候，决定各个表的连接顺序，或者改写查询，去除没必要的条件，比如 where 1 = 1。优化结束执行下一步
• 经过了优化器之后可以说这个语句具体该如何执行就已经定下来，接下来就进入执行器，首先会进行权限校验，如果没有权限就会返回错误信息，如果有权限就会调用数据库引擎接口，最后返回引擎的执行结果（如果有查询缓存则还会将结果存入缓存中），sql 执行结束