MYSQL一条sql语句是如何执行的

1.一条sql是如何执行的，先上图

首先，我们的数据是存储在MySQL服务端的。应用程序或者工具都是客户端。客户端要读写数据库，第一步要做什么事情？客户端要跟数据库建立连接

1.1 连接

客户端连接服务端的方式是多种多样的，可以是同步的也可以是异步的，可以是长连接也可以是短连接，可以是TCP也可以是UnixSocket（Windows还有命名管道和内存共享）。

可以用showstatus命令，模糊匹配Thread : show global status like ‘Thread%’;
字段含义:
Threads_cached缓存中的线程连接数。
Threads_connected当前打开的连接数。
Threads_created为处理连接创建的线程数。
Threads_running非睡眠状态的连接数，通常指并发连接数。

MySQL会把那些长时间不活动的连接自动断开
有两个参数：
showglobalvariableslike’wait_timeout’;–非交互式超时时间，如JDBC程序showglobalvariableslike’interactive_timeout’;–交互式超时时间，如数据库工具
默认都是28800秒，8小时

既然连接消耗资源，MySQL服务允许的最大连接数（也就是并发数）默认是多少呢？在5.7版本中默认是 151个，最大可以设置成100000。showvariableslike’max_connections’;

参数级别说明：MySQL中的参数分为session和global级别，分别是在当前会话中生效和全局生效，但是并不是每个参数都有两个级别，比如max_connections就只有全局级别。
当没有带参数的时候，默认是session级别，包括查询和修改。

1.2 查询缓存

MySQL内部自带了一个缓存模块，但是5.7的版本里面默认是关闭的。
默认关闭的意思就是不推荐使用，为什么MySQL不推荐使用它自带的缓存呢？主要是因为MySQL自带的缓存的应用场景有限，第一个是它要求SQL语句必须一模一样，例如中间多一个空格、字母大小写不同都被认为是不同的的SQL。

缓存这一块，我们一般交给ORM框架（比如MyBatis默认开启了一级缓存），或者独立的缓存服务，比如Redis来处理更合适。在MySQL8.0中，查询缓存已经被移除了

1.3 语法解析和预处理

为什么我的一条SQL语句能够被识别呢？它怎么知道我执行的DDL还是DML还是DQL？
假如我们随意执行一个字符串：如test，服务器就会报一个1064的错误

它是怎么知道我输入的内容是错误的？这个就是MySQL的Parser解析器和Preprocessor预处理模块。这一步主要做的事情是对SQL语句进行词法和语法分析和语义的解析

1.3.1.词法解析
词法分析就是把一个完整的SQL语句打碎成一个个的单词。比如一个简单的SQL语句：

select name from user whereid=1;

它会打碎成8个符号，每个符号是什么类型，从哪里开始到哪里结束

1.3.2.语法解析
第二步就是语法分析，语法分析会对SQL做一些语法检查，比如单引号有没有闭合，
然后根据MySQL定义的语法规则，根据SQL语句生成一个数据结构。这个数据结构我们把它叫做解析树（select_lex）。

1.3.3.预处理器

问题：如果我写了一个词法和语法都正确的SQL，比如：

select * from testTable;

但是表名或者字段不存在，会在哪里报错？是解析的时候报错还是执行的时候报错？

解析器可以分析语法，但是它怎么知道数据库里面有什么表，表里面有什么字段呢？那它必须把表结构存起来才行。实际上还是在解析的时候报错，解析SQL的环节里面有个预处理器。它会检查生成的解析树，解决解析器无法解析的语义。比如，它会检查表和列名是否存在，检查名字和别名，保证没有歧义。
预处理之后得到一个新的解析树

1.4 查询优化（QueryOptimizer）与查询执行计划

1.4.1什么是优化器
解析树是一个可以被执行器认识的数据结构。得到解析树之后，是不是可以执行，操作数据了呢？
这里我们有一个问题，一条SQL语句是不是只有一种执行方式？或者说数据库最终执行的SQL是不是就是我们发送的SQL？
这个答案是否定的。一条SQL语句是可以有很多种执行方式的，最终返回相同的结果，他们是等价的。但是如果有这么多种执行方式，这些执行方式怎么得到的？最终选择哪一种去执行？根据什么判断标准去选择？这个就是MySQL的查询优化器的模块（Optimizer）。
1.4.2.优化器可以做什么？
查询优化器的主要作用就是根据解析树生成不同的执行计划（ExecutionPlan），然后选择一种最优的执行计划。
注意：MySQL里面使用的是基于成本（cost）的优化器，那种执行计划成本最小，就用哪种

经过优化器处理之后，就会得到一条执行路径，我们把它叫做执行计划（execution_plans），执行计划也是一个数据结构。
当然，这个执行计划也不一定是最优的执行计划，因为MySQL也有可能覆盖不到所有的执行计划
1.4.3.优化器得到的结果
MySQL提供了一个执行计划的工具。我们在SQL语句前面加上EXPLAIN，就可以看到执行计划的信息。

EXPLAIN select name from user where id=1;

如果要得到详细的信息，还可以用FORMAT=JSON，或者开启optimizertrace。

EXPLAIN FORMAT=JSON select name from user where id=1;

1.5.存储引擎

得到执行计划这个数据结构以后，执行器是不是终于可以执行了？问题又来了：从逻辑的角度来说，或者说从数据库用户看到的表面，我们的数据是放在哪里的，或者说放在一个什么结构里面

任何一个存储引擎都有一个frm文件，这个是表结构定义文件
不同的存储引擎存放数据的方式不一样，产生的文件也不一样，innodb是1个，memory没有，myisam是两个
有几个问题：
1、表类型是怎么选择的？可以修改吗？
2、MySQL为什么支持这么多存储引擎呢？一种还不够用吗？
3、这些不同的存储引擎，到底有什么区别

1.5.1.存储引擎选择
一张表的存储引擎，是在创建表的时候指定的，使用ENGINE关键字（建表之后可以修改）

没有指定的时候，数据库就会使用默认的存储引擎，5.5.5之前，默认的存储引擎是MyISAM，5.5.5之后，默认的存储引擎是InnoDB。这么多的存储引擎，区别到底在哪里

试想一下：
如果我有一张表，需要很高的访问速度，而不需要考虑持久化的问题，是不是要把数据放在内存？
如果一张表，是用来做历史数据存档的，不需要修改，也不需要索引，那它是不是要支持数据的压缩？如果一张表用在读写并发很多的业务中，是不是要支持读写不干扰，而且要保证比较高的数据一致性呢？
为什么要支持这么多的存储引擎，就是因为我们有不同的业务需求

存储引擎比较

1.5.2.存储引擎选择
MyISAM（3个文件）
MySQL自带的存储引擎，由ISAM升级而来。mostlyworkloadsinWebanddatawarehousingconfigurations.应用范围比较小。表级锁定限制了读/写的性能，因此在Web和数据仓库配置中，它通常用于只读或以读为主的工作。特点：支持表级别的锁（插入和更新会锁表）。不支持事务。拥有较高的插入（insert）和查询（select）速度。存储了表的行数（count速度更快）。（怎么快速向数据库插入100万条数据？我们有一种先用MyISAM插入数据，然后修改存储引擎为InnoDB的操作。）适合：只读之类的数据分析的项目。

InnoDB（2个文件）
最开始是第三方公司为MySQL开发的。mysql5.7中的默认存储引擎。InnoDB是一个事务安全（与ACID兼容）的MySQL存储引擎，它具有提交、回滚和崩溃恢复功能来保护用户数据。InnoDB行级锁（不升级为更粗粒度的锁）和Oracle风格的一致非锁读提高了多用户并发性和性能。InnoDB将用户数据存储在聚集索引中，以减少基于主键的常见查询的I/O。为了保持数据完整性，InnoDB还支持外键引用完整性约束。特点：支持事务，支持外键，因此数据的完整性、一致性更高。支持行级别的锁和表级别的锁。支持读写并发，写不阻塞读（MVCC）。特殊的索引存放方式，可以减少IO，提升查询效率。适合：经常更新的表，存在并发读写或者有事务处理的业务系统。
Memory（1个文件）
将所有数据存储在RAM中，以便在需要快速查找非关键数据的环境中快速访问。这个引擎以前被称为堆引擎。其使用案例正在减少；InnoDB及其缓冲池内存区域提供了一种通用、持久的方法来将大部分或所有数据保存在内存中，而ndbcluster为大型分布式数据集提供了快速的键值查找。特点：把数据放在内存里面，读写的速度很快，但是数据库重启或者崩溃，数据会全部消失。只适合做临时表。将表中的数据存储到内存中。默认使用哈希索引。
CSV（3个文件）
它的表实际上是带有逗号分隔值的文本文件。csv表允许以csv格式导入或转储数据，以便与读写相同格式的脚本和应用程序交换数据。因为csv表没有索引，所以通常在正常操作期间将数据保存在innodb表中，并且只在导入或导出阶段使用csv表。特点：不允许空行，不支持索引。格式通用，可以直接编辑，适合在不同数据库之间导入导出。
Archive（2个文件）
这些紧凑的未索引的表用于存储和检索大量很少引用的历史、存档或安全审计信息。特点：不支持索引，不支持updatedelete。这是MySQL里面常见的一些存储引擎，我们看到了，不同的存储引擎提供的特性都不一样，它们有不同的存储机制、索引方式、锁定水平等功能

1.4.4.如何选择存储引擎？
如果对数据一致性要求比较高，需要事务支持，可以选择InnoDB。
如果数据查询多更新少，对查询性能要求比较高，可以选择MyISAM。
如果需要一个用于查询的临时表，可以选择Memory。
如果所有的存储引擎都不能满足你的需求，并且技术能力足够，可以根据官网内部手册用C语言开发一个存储引擎：
官网定制存储引擎规范链接
按照这个开发规范，实现相应的接口，给执行器操作。也就是说，为什么能支持这么多存储引擎，还能自定义存储引擎，表的存储引擎改了对Server访问没有任何影响，就是因为大家都遵循了一定了规范，提供了相同的操作接口

1.6.执行引擎（ExecutionEngine）
返回结果是谁使用执行计划去操作存储引擎呢？
这就是我们的执行器，或者叫执行引擎，它利用存储引擎提供的相应的API来完成操作。最后把数据返回给客户端，即使没有结果也要返回

2.MySQL体系结构

总结基于上面分析的流程，我们一起来梳理一下MySQL的内部模块和架构。
2.1.架构分层
总体上，我们可以把MySQL分成两层，执行操作的服务层（Server层），和存储
管理数据的存储引擎层。Server层跟存储引擎做的事情是完全不一样的。存储引擎主要负责数据的存取。对于数据的操作，过滤，计算这些都是在Server层。存储引擎再往下就是文件系统、硬件

3.一条更新SQL是如何执行的？

在数据库里面，我们说的update操作其实包括了更新、插入和删除。
更新流程和查询流程有什么不同呢？
基本流程也是一致的，也就是说，它也要经过解析器、优化器的处理，最后交给执行器。区别就在于拿到符合条件的数据之后的操作。
3.1.缓冲池BufferPool
首先，对于InnoDB存储引擎来说，数据都是放在磁盘上的，存储引擎要操作数据，必须先把磁盘里面的数据加载到内存里面才可以操作。
这里就有个问题，是不是我们需要的数据多大，我们就一次从磁盘加载多少数据到内存呢？
比如我要读10个字节。磁盘I/O的读写相对于内存的操作来说是很慢的。如果我们需要的数据分散在磁盘的不同的地方，那就意味着会产生很多次的I/O操作。
所以，无论是操作系统也好，还是存储引擎也好，都有一个预读取的概念。
也就是说，当磁盘上的一块数据被读取的时候，很有可能它附近的位置也会马上被读取到，这个就叫做局部性原理。那么这样，我们干脆每次多读取一点，而不是用多少读多少。
InnoDB设定了一个存储引擎从磁盘读取数据到内存的最小的单位，叫做页。操作系统也有页的概念。操作系统的页大小一般是4K，而在InnoDB里面，这个最小的单位默认是16KB（16384bytes）大小，它是一个逻辑单位。如果要修改这个值的大小，必须修改源码重新编译安装。
https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_page_size
举个例子，你去烧烤店跟老板说，老板，来一个生蚝。他根本不卖，懒得给你烤。这个“打”dozen，就是一个逻辑单位，实际上它的物理存放方式可能不是12个12个地放到一起的，但是老板给卖给你生蚝，就是一打一打地卖。
我们要操作的数据就在这样的页里面，数据所在的页叫数据页。这里有一个问题，操作数据的时候，每次都要从磁盘读取到内存（再返回给Server），有没有什么办法可以提高效率？还是缓存的思想。把读取过的数据页缓存起来。InnoDB设计了一个内存的缓冲区。读取数据的时候，先判断是不是在这个内存区域里面，如果是，就直接读取，然后操作，不用再次从磁盘加载。如果不是，读取后就写到这个内存的缓冲区。这个内存区域有个专属的名字，叫BufferPool。修改数据的时候，也是先写入到bufferpool，而不是直接写到磁盘。内存的数据页和磁盘数据不一致的时候，我们把它叫做脏页。
那脏页什么时候才同步到磁盘呢？
InnoDB里面有专门的后台线程把BufferPool的数据写入到磁盘，每隔一段时间就一次性地把多个修改写入磁盘，这个动作就叫做刷脏。
总结一下**：BufferPool的作用是为了提高读写的效率。BufferPool默认大小是128M（134217728字节），可以调整。**
查看参数：

SHOW VARIABLES like'%innodb_buffer_pool%'

3.2.（redo）LogBuffer
因为刷脏不是实时的，如果BufferPool里面的脏页还没有刷入磁盘时，数据库宕机或者重启，这些数据就会丢失。所以内存的数据必须要有一个持久化的措施。为了避免这个问题，InnoDB把所有对页面的修改操作专门写入一个日志文件。如果有未同步到磁盘的数据，数据库在启动的时候，会从这个日志文件进行恢复操作（实现crash-safe）。我们说的事务的ACID里面D（持久性），就是用它来实现的

这个日志文件就是磁盘的redolog（叫做重做日志）。
问题：同样是写磁盘，为什么不直接写到dbfile里面去？为什么先写日志再写磁盘？写日志文件和和写到数据文件有什么区别？
我们先来了解一下随机I/O和顺序I/O的概念。如果我们操作的数据是随机分散在磁盘上不同页的不同扇区中，那么找到相应的数据需要等到磁臂旋转到指定的页，然后盘片寻找到对应的扇区，才能找到我们所需要的一块数据，一次进行此过程直到找完所有数据，这个就是随机IO，读取数据速度较慢。假设我们已经找到了第一块数据，并且其他所需的数据就在这一块数据后边，那么就不需要重新寻址，可以依次拿到我们所需的数据，这个就叫顺序IO。刷盘是随机I/O，而记录日志是顺序I/O（连续写的），顺序I/O效率更高。因此先把修改写入日志文件，在保证了内存数据的安全性的情况下，可以延迟刷盘时机，进而提升系统吞吐。redolog位于/var/lib/mysql/目录下的ib_logfile0和ib_logfile1，默认2个文件，
每个48M。

show variables like 'innodb_log%';

redolog有什么特点？
1、redolog是InnoDB存储引擎实现的，并不是所有存储引擎都有。支持崩溃恢复是InnoDB的一个特性。2、redolog是物理日志，记录的是“在某个数据页上做了什么修改”。
3、redolog的大小是固定的，前面的内容会被覆盖，一旦写满，就会触发bufferpool到磁盘的同步，以便腾出空间记录后面的修改

除了redolog之外，还有一个跟修改有关的日志，叫做undolog。
redolog和undolog与事务密切相关，统称为事务日志。
undo log tablespace
undo log（撤销日志或回滚日志）
记录了事务发生之前的数据状态（不包括select）。如果修改数据时出现异常，可以用undolog来实现回滚操作（保持原子性）。在执行undo的时候，仅仅是将数据从逻辑上恢复至事务之前的状态，而不是从物理页面上操作实现的，属于逻辑格式的日志。undolog的数据默认在系统表空间ibdata1文件中，因为共享表空间不会自动收缩，也可以单独创建一个undo表空间。

show global variables like '%undo%'

有了这些日志之后，我们来总结一下一个更新操作的流程，这是一个简化的过程。
name原值是zhangsan。

update user set name ='lisi' where id = 1;

1、事务开始，从内存（bufferpool）或磁盘(datafile)取到包含这条数据的数据页，返回给Server的执行器；
2、Server的执行器修改数据页的这一行数据的值为lisi；
3、记录name=zhangsan到undolog；
4、记录name=lisi到redolog；
5、调用存储引擎接口，记录数据页到BufferPool（修改name=lisi）；
6、事务提交。内存和磁盘之间，工作着很多后台线程

3.3.Binlogbinlog
以事件的形式记录了所有的DDL和DML语句，
比如“给ID=1这一行的count字段加1”,因为它记录的是操作而不是数据值，属于逻辑日志）。
binlog有两个非常重要的作用：1、主从复制，2、数据恢复
主从复制的原理就是从服务器读取主服务器的binlog，然后执行一遍

在开启了binlog功能的情况下，我们可以把binlog导出成SQL语句，把所有的操作重放一遍，来实现数据的恢复。举例：每天凌晨1点做数据库的全量备份，上午9点，数据库被删除跑路了。
首先，恢复到凌晨1点的数据。然后利用1点到9点之间的binlog，提出drop，恢复数据。
有了这两个日志之后，我们来看一下一条更新语句是怎么执行的（这里省略了undo）

例如一条语句：
updateteachersetname='盆鱼宴’whereid=1;
1、先从内存或者磁盘拿到这条数据。
2、把name改成盆鱼宴，然后调用存储引擎的API接口，写入这一行数据到内存，同时记录redolog。这时redolog进入prepare状态，然后告诉执行器，执行完成了，可以随时提交。
3、执行器收到通知后记录binlog
4、然后调用存储引擎接口，设置redolog为commit状态。更新完成。
流程没必要背下来，总结一下这张图片的重点：
1、先记录到内存（bufferpool），再写日志文件。
2、记录redolog分为两个阶段（prepare和commit）。
3、存储引擎和server分别记录不同的日志。
4、先记录redo，再记录binlog