MySQL 中一条 sql 的执行过程

一条 SQL 的执行过程

前言

在开始学习 MySQL 中知识点的时候，首先来看下 SQL 在 MySQL 中的执行过程。

查询

查询语句是我们经常用到的，那么一个简单的查询 sql，在 MySQL 中的执行过程是怎么样的呢？

SELECT * FROM user WHERE id =1

栗如上面的这个简单的查询语句，来看下具体的查询逻辑。

MySQL 主要分为 Server 层和存储引擎层两部分。

Server 层包括连接器、查询缓存。分析器、执行器等。MySQL 中大多数的核心功能，所有的内置函数，所有跨存储引擎的功能都在这一层实现。栗如：存储过程，触发器，视图等。。。

存储引擎层负责数据的存储和提取。其架构是插件式的，支持 InnoDB、MyISAM、Memory 等多个存储引擎。MySQL 5.5.5 InnoDB 成为了默认的存储引擎。

连接器

大多数基于网络的客户端/服务器的工具或者服务都有类似的架构。

这里主要的工作就是管理和客户端的连接，同时进行连接的权限认证。

• 如果用户名密码不对，就会有一个 "Access denied for user" 的错误提示。

• 如果用户名密码认证通过，连接器中会在权限表中查询改账号拥有的权限，之后所有的权限判断逻辑，都依赖于此时读到的权限。

这就意味着，一旦一个用户建立连接后，即使对这个账号进行了权限的修改，对已经建立的连接也不会产生影响。只有新建连接，才能使用新的权限。

客户端如何太长时间没有动静，连接器会断开连接，这个时间由参数 wait_timeout 控制默认 8 小时。

数据库的连接分成两种类型短连接和短链接：

长连接：长连接在连接成功之后，后面客户端的请求，可以复用这个连接；

短连接：短连接每次执行完几次查询就断开连接，每次客户端的请求都会新建一个。

因为建立连接的过程是很复杂，并且是有一定开销的，应该尽量减少连接的建立，长连接更加推荐使用。

为了避免线程被频繁的创建和销毁，影响性能，MySQL5.5 版本引入了线程池，会缓存创建的线程，不需要为每一个新建的连接，创建或销毁线程。可以使用线程池中少量的线程服务大量的连接。

查询缓存

MySQL 查询缓存，为了提高相同 Query 语句的响应速度，会缓存特定 Query 的整个结果集信息，当后面有相同的查询语句，直接查询缓存，返回查询的结果。当命中的时候不需要执行后面复杂的操作，就可以直接返回结果，查询效率是很高的。

不过当一个表有更新的时候，和这个表有关的查询缓存都会被删除，造成查询缓存的失效。所以更新较频繁的数据库不建议使用查询缓存，命中率会非常的低。所以，长时间不更新的静态表，这种适合使用查询缓存。

在 MySQL 5.6 开始，就已经默认禁用查询缓存了。在 MySQL 8.0，就已经删除查询缓存功能了。

分析器

当 SQL 需要执行时候，首先分析器会做一个词法分析和语法分析，一条 SQL 语句由字符串和空格组成，MySQL 需要识别出里面的字符串分别是什么，代表什么。根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这个SQL语句是否满足MySQL语法。

优化器

优化执行：利用数据库的统计信息决定 SQL 语句的佳执行方式，选择合适的索引，找出优的查询方案，

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

执行器

开始执行的时候，要先判断一下你对这个表T有没有执行查询的权限，如果没有，就会返回没有权限的错误。

有权限，会根据优化后的 SQL，向存储引擎发起查询操作，并且返回查询的结果。

执行与优化

总体来说就是

MySQL 会解析查询，并创建内部的数据结构(解析树)，然后对其进行各种优化，包括重写查询，决定表的读取顺序，以及选择合适的索引。

数据更新

在了解数据的更新，需要先来了解下，MySQL 中几种常用的日志。

日志模块

先来看下 InnoDB 中的存储

InnoDB 存储引擎是基于磁盘存储的，并将其中的记录按照页的方式进行管理。因此可以将其视为基于磁盘的数据库系统，由于 CPU 速度与磁盘速度之间的鸿沟，基于
磁盘的数据库系统通常使用缓存池技术来提供数据库整体性能。

缓存池简单来说就是一块内存区域，通过内存速度来弥补磁盘速度较慢对数据库性能的影响。

• 数据库读取页的操作，会将从磁盘读出的页存放到缓存池中，下次读相同的页就可以在缓存池中查询了，查询不到还是会从磁盘中读取。

• 数据库中页的修改操作，首先修改缓存池中的页，然后一定频率刷新到新的磁盘上。页从缓存池中刷新到磁盘中的操作并不是在每次页发生更新时触发，而是通过一种称为 Checkpoint 的机制刷新回磁盘。

如果每一个页发生变化，就将新页的版本刷新到磁盘，那么这个开销是很大的，如果数据都集中在某几个页，那么数据库的性能将变的很差。

同时，如果在从缓存中将页的新版本刷新到磁盘时发生了宕机，那么数据就不能恢复了，为了避免发生数据丢失的问题，当前事务数据系统都采用了 Write Ahead Log 策略，当事务提交的时候，先写重做日志，再修改页。通过重做日志来处理宕机时候的数据丢失问题。

redo log (重做日志)

InnoDB 中的重做日志，由两部分组成，redo log 和 undo log。

• redo log 用来从保证事务的持久性；

• undo log 用来实现事务回滚以及 MVCC 的功能。

redo log 简单点讲就是 MySQL 异常宕机后，将没来得及提交的事物数据重做出来。

redo log 包括两部分：一个是内存中的日志缓冲( redo log buffer )，另一个是磁盘上的日志文件( redo log file )。

MySQL 每执行一条 DML 语句，先将记录写入 redo log buffer，后续某个时间点再一次性将多个操作记录写到 redo log file 。这种 先写日志，再写磁盘 的技术就是 MySQL 里经常说到的 WAL(Write-Ahead Logging) 技术。

MySQL 支持三种将 redo log buffer 写入 redo log file 的时机，可以通过 innodb_flush_log_at_trx_commit 参数配置，各参数值含义如下：