Mysql架构与SQL执行流程

1.一条查询sql是如何执行的

1.1 通信协议

mysql首先是一个服务，监听默认的3306端口。程序或者工具要操作数据库，首先要跟数据库建立连接。MySQL 支持多种通信协议，可以使用同步/异步的方式，支持长连接/短连接，可以从通信类型和连接方式来学习。

1.1.1 通信类型-同步/异步

• 同步通信依赖于被调用方，受限于被调用方的性能。也就是说，应用操作数据库，线程会阻塞，等待数据库的返回；一般只能做到一对一通信
• 异步通信可以避免应用阻塞，但不能节省sql执行时间。如果异步存在并发，每一个 SQL 的执行都要单独建立一个连接，避免数据混乱。
  但是这样会给服务端带来巨大的压力（一个连接就会创建一个线程，线程间切换会占用大量 CPU 资源）。另外异步通信还带来了编码的复杂度，所以一般不建议使用。如果要异步，必须使用连接池，排队从连接池获取连接而不是创建新连接。一般来说我们连接数据库都是同步连接。

1.1.2 连接方式-长连接/短连接

MySQL 既支持短连接，也支持长连接。
短连接就是操作完毕以后，连接马上断开。长连接可以保持打开，减少服务端创建和释放连接的消耗，后面的程序访问的时候还可
以使用这个连接。一般我们会在连接池中使用长连接；保持长连接会消耗内存，长时间不活动的连接，MySQL 服务器会断开。

show global variables like 'wait_timeout';  // 非交互式超时时间，如 JDBC 程序
show global variables like 'interactive_timeout'; //交互式超时时间，如数据库工具
show global status like 'Thread%'; // 查看当前有多少个连接
SHOW PROCESSLIST;   // 查看当前连接的状态
show variables like 'max_connections'; // msyql服务默认的最大连接数，5.7版本默认151个

Threads_cached：缓存中的线程连接数。
Threads_connected：当前打开的连接数。
Threads_created：为处理连接创建的线程数。
Threads_running：非睡眠状态的连接数，通常指并发连接数。
每产生一个连接或者一个会话，在服务端就会创建一个线程来处理，如果要杀死会话，就是 Kill 线程。

1.1.3 通信协议

MySQL 支持Unix Socket协议和TCP/IP协议。

比如我们在 Linux 服务器上，如果没有指定-h 参数，它就用 socket 方式登录（省略了-S /var/lib/mysql/mysql.sock），它不用通过网络协议，也可以连接到 MySQL 的服务器，它需要用到服务器上的一个物理文件（/var/lib/mysql/mysql.sock）；

如果指定-h 参数，就会用第二种方式，TCP/IP 协议： mysql -h192.168.8.211 -u root -p123456
编程语言的连接模块都是用 TCP 协议连接到 MySQL 服务器的，比如mysql-connector-java-x.x.xx.jar。

1.1.4 通信方式

通信方式有单工（如遥控器，只能单向通信），半双工（如对讲机，可以相互通信，但同一时刻只能有一方向另一方通信），全双工（如手机，同一时刻，双方都可以 向对方通信）

• MySQL 使用半双工的通信方式
  要么是客户端向服务端发送数据，要么是服务端向客户端发送数据，这两个动作不能同时发生。所以客户端发送 SQL 语句给服务端的时候，（在一次连接里面）数据是不能分成小块发送的，不管你的 SQL 语句有多大，都是一次性发送。

比如我们用 MyBatis 动态 SQL 生成了一个批量插入的语句，插入 10 万条数据，values后面跟了一长串的内容，或者 where 条件 in 里面的值太多，会出现问题。这个时候我们必须要调整 MySQL 服务器配置 max_allowed_packet 参数的值（默认是 4M），把它调大，否则就会报错。

另一方面，对于服务端来说，也是一次性发送所有的数据到客户端，不能因为你已经取到了想要的数据就中断操作，这个时候会对网络和内存产生大量消耗。所以我们一定要在程序里面避免不带 limit 的操作，比如一次把所有满足条件的数据全部查出来，一定要先 count 一下，如果数据量的话，可以分批查询。

1.2 查询缓存

MySQL 内部自带了一个缓存模块，但是它是默认关闭的，意思就是不推荐使用，为什么 MySQL 不推荐使用自带缓存呢？主要是因为 MySQL 自带的缓存太鸡肋了，它要求 SQL 语句必须一模一样，中间多一个空格，字母大小写不同都被认为是不同的的 SQL，并且当表里任何一条数据发生变化的时候，这张表所有缓存都会失效。在 MySQL 8.0 中，查询缓存已经被移除了。

1.3 分析器(Parser & Preprocessor）

包含语法解析和预处理

• 词法解析：把一个完整的 SQL 语句打碎成一个个的单词
• 语法解析：根据语法规则判断SQL语句是否满足MySQL语法语法分析（会对 SQL 做一些语法检查，比如单引号有没有闭合）
  并根据 MySQL 定义的语法规则，根据 SQL 语句生成一个解析树（select_lex）：
  
• 预处理器
  如果写了一个词法和语法都正确的 SQL，但是表名或者字段不存在，会在哪里报错？是在数据库的执行层还是解析器？比如：
  select * from XX;
  解析器可以分析语法，但是它怎么知道数据库里面有什么表，表里面有什么字段呢？实际上还是在解析的时候报错，解析 SQL 的环节里面有个预处理器。它会检查生成的解析树，解决解析器无法解析的语义。比如，它会检查表和列名是否存在，检查名字和别名，保证没有歧义。预处理之后得到一个新的解析树。

1.4 优化器（Query Optimizer）

查询优化器的目的是根据解析树生成不同的执行计划（Execution Plan），然后选择一种最优的执行计划，MySQL 里面使用的是基于开销（cost）的优化器，那种执行计划开销最小，就用哪种。

show status like 'Last_query_cost';   // 查看查询的开销

• 优化器如何得到执行计划
  https://dev.mysql.com/doc/internals/en/optimizer-tracing.html

1. 首先启用优化器的追踪（默认是关闭的）：

SHOW VARIABLES LIKE 'optimizer_trace';
set optimizer_trace='enabled=on';

2. 接着我们执行一条 SQL 语句，优化器会生成执行计划，比如：

select o.*,oi.state from order_detail o 
left join order_info oi on oi.order_id = o.order_id where oi.order_id =1

3. 优化器的结果在information_schema表中会有专门的记录,通过查询语句可以得到

select * from information_schema.`OPTIMIZER_TRACE`    

将结果中的trace的值拷贝出来，它是一个json数据：
主要分成三部分，准备阶段、优化阶段和执行阶段

expanded_query 是优化后的 SQL 语句。
considered_execution_plans 里面列出了所有的执行计划。
优化器在表里有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序；MySQL 提供了一个执行计划的工具。我们在 SQL 语句前面加上 EXPLAIN，就可以看到执行计划的信息。

explain select o.*,oi.state from order_detail o left join order_info oi on oi.order_id = o.order_id where oi.order_id =1

1.5 存储引擎

得到执行计划以后，SQL 语句又该如何执行？我们的数据放在一个什么结构里面？执行计划在哪里执行？是谁去执行？
关系型数据库的数据是放在一个表里面的，表在存储收卷机的同时，还要组织数据的存储结构，这个存储结构是由存储引擎决定的。mysql支持多种存储引擎：InnoDB,MyISAM,Memory,CSV,Archive.

• 数据库数据的存放路径

show variables like 'datadir';

默认情况下，每个数据库有一个自己文件夹；任何一个存储引擎都有一个 frm 文件，这个是表结构定义文件；不同的存储引擎存放数据的方式不一样，产生的文件也不一样，innodb存放数据的文件是后缀为ibd 的文件， myisam存放数据的文件时 MYD 和MYI后缀的文件。

• InnoDB存储引擎（mysql 5.7 的默认存储引擎）
  支持事务，支持外键，因此数据的完整性、一致性更高。
  支持行级别的锁和表级别的锁。
  支持读写并发，写不阻塞读（MVCC）。
  特殊的索引存放方式，可以减少 IO，提升查询效率。
  适合经常更新的表，存在并发读写或者有事务处理的业务系统。
• MyISAM 存储引擎
  支持表级别的锁（插入和更新会锁表）。不支持事务。
  拥有较高的插入（insert）和查询（select）速度。
  存储了表的行数（count 速度更快）。
  （怎么快速向数据库插入 100 万条数据？我们有一种先用 MyISAM 插入数据，然后
  修改存储引擎为 InnoDB 的操作。）
  适合只读之类的数据分析的项目。

1.6 执行引擎（Query Execution Engine）返回结果

执行引擎会使用执行计划去操作存储引擎，它利用存储引擎提供的相应的 API 来完成操作。
为什么我们修改了表的存储引擎，操作方式不需要做任何改变？因为不同功能的存储引擎实现的 API 是相同的。最后把数据返回给客户端，即使没有结果也要返回。

2. MySQL架构

2.1 mysql模块

• Connector：用来支持各种语言和 SQL 的交互，比如 PHP，Python，Java 的
  JDBC；
• Management Serveices & Utilities：系统管理和控制工具，包括备份恢复、MySQL 复制、集群等
• Connection Pool：连接池，管理需要缓冲的资源，包括用户密码权限线程等
• SQL Interface：用来接收用户的 SQL 命令，返回用户需要的查询结果
• Parser：用来解析 SQL 语句；
• Optimizer：查询优化器；
• Cache and Buffer：查询缓存，除了行记录的缓存之外，还有表缓存，Key 缓存，权限缓存等等；
• Pluggable Storage Engines：插件式存储引擎，它提供 API 给服务层使用，跟具体的文件打交道。

2.2 mysql架构分层

总体上，我们可以把 MySQL 分成三层，跟客户端对接的连接层，真正执行操作的服
务层，和跟硬件打交道的存储引擎层（参考 MyBatis：接口、核心、基础）

3 一条更新sql是如何执行的

数据库 的update 操作其实包括了更新、插入和删除。 MyBatis 的源码中Executor 里面也只有 doQuery()和 doUpdate()的方法，没有 doDelete()和 doInsert()。

• 更新流程和查询流程的差异

sql语句的更新流程与查询流程基一致，都要经过解析器、优化器的处理，最后交给执行器。区别在于拿到符合条件的数据之后的操作。

3.1InnoDB内存结构与磁盘结构

https://dev.mysql.com/doc/refman/5.7/en/innodb-architecture.html

3.1.1 内存结构

3.1.1.1 缓冲池BufferPool

InnnoDB 的数据都是放在磁盘上的，InnoDB 操作数据的最小逻辑单位叫做页（索引页和数据页）。我们对于数据的操作并不是每次都直接操作磁盘，因为磁盘的速度太慢了。InnoDB 使用了一种缓冲池的技术，也就是把磁盘读到的页放到一块内存区域里面。这个内存区域就叫 Buffer Pool；

下一次读取相同的页，先判断是不是在缓冲池里面，如果是，就直接读取，不用再次访问磁盘。修改数据的时候，先修改缓冲池里面的页, 内存的数据页和磁盘数据不一致的时候，我们把它叫做脏页 。InnoDB 里面有专门的后台线程把 Buffer Pool 的数据写入到磁盘，每隔一段时间就一次性地把多个修改写入磁盘，这个动作叫做刷脏。

Buffer Pool 主要分为 3 个部分： Buffer Pool、Change Buffer、Adaptive Hash Index以及（redo）log buffer。
BufferPool缓存了数据页，索引页的信息；查看服务器状态，里面有很多跟 Buffer Pool 相关的信息：

SHOW STATUS LIKE '%innodb_buffer_pool%';
SHOW VARIABLES like '%innodb_buffer_pool%';

3.1.1.2 Change Buffer 写缓冲

如果这个数据页不是唯一索引，不存在数据重复的情况下，不需要从磁盘加载索引页判断数据做唯一性检查，这种情况下可以先把修改记录在内存的缓冲池中，从而提升更新语句（Insert、Delete、Update）的执行速度。这一块区域叫做Change Buffer，最后把 Change Buffer 记录到数据页的操作叫做 merge。

• 什么时候 merge

有几种情况：在访问这个数据页的时候，或者通过后台线程、或者数据库 shut down、redo log 写满时触发。
如果数据库大部分索引都是非唯一索引，并且业务是写多读少，不会在写数据后立刻读取，就可以使用 Change Buffer（写缓冲）。写多读少的业务可以调大这个值。

3.1.1.3 自适应hash索引

索引应该是放在磁盘的，为什么要专门把一种哈希的索引放到内存呢？ 后续研究索引时再细说

3.1.1.4 （redo）Log Buffer

• 问题1：如果 Buffer Pool 里面的脏页还没有刷入磁盘时，数据库宕机或者重启，这些数据丢失，如何避免这种情况？为了解决这个问题，innodb引入了重做日志redolog。

InnoDB 把所有对页面的修改操作专门写入一个日志文件，并且在数据库启动时从这个文件进行恢复操作（崩溃恢复）——用它来实现事务的持久性。这个文件就是磁盘的 redo log（叫做重做日志），对应于/var/lib/mysql/目录下的ib_logfile0 和 ib_logfile1，每个 48M。这 种 日 志 和 磁 盘 配 合 的 整 个 过 程 ， 其 实 就 是 MySQL 的 WAL 技 术（Write-Ahead Logging），它的关键在于 先写日志，再写磁盘。

• 问题2：写日志和写磁盘本质都是记录数据到磁盘上，为什么不直接写到dbfile? 为什么要先写日志？
  这里涉及到随机IO和顺序IO的概念；

操作系统与内存交互的最小单位是页(Page)
操作系统读写磁盘的最小单位是块（Block）,而磁盘的最小组成单元叫扇区，通常是512字节。
如果需要操作的数据随机分散在不同页的不同扇区中，那么找到所需的数据，就需要等磁盘的磁臂旋转到指定的页的对应扇区，才能找到所需的一块数据，如此循环知道找完所有数据，这种数据读取速度比较慢；而如果我们已经找到了第一块数据，并且其他所需的数据就在这一块数据后边，那么就不需要重新寻址，可以依次拿到我们所需的数据，这个就叫顺序 IO。
写磁盘是随机 I/O，而记录日志是顺序 I/O，顺序 I/O 效率更高。因此先把修改写入日志，可以延迟刷盘时机，进而提升系统的吞吐量。

• log buffer
  redo log 也不是每一次都直接写入磁盘，在 Buffer Pool 里面有一块内存区域（Log Buffer）专门用来保存即将要写入文件的数据，默认 16M，它一样可以节省磁盘 IO。
• Log Buffer 什么时候写入 log file
  在我们写入数据到磁盘的时候，操作系统本身是有缓存的。flush 就是把操作系统缓
  冲区写入到磁盘。log buffer 写入磁盘的时机，由一个参数控制，默认是1（1代表实时写，实时刷，每次事务提交时 MySQL 都会把 log buffer 的数据写入 log file，并且刷到磁盘中）
• regolog特性

1. redolog只有innodb引擎实现，其他存储引擎不具备
2. RedoLog 记录的是物理日志，即记录的是这个页做了什么改动
3. redolog大小固定，从开头写到末尾又回开头循环写，check point 是当前要覆盖的位置。如果 write pos 跟 check point 重叠，说明 redolog 已经写满，这时候需要同步 redo log 到磁盘中。
   

3.2 磁盘结构

Innodb 所有的数据都存放在表空间中。InnoDB 的表空间分为 5 大类。

3.2.1 系统表空间

InnoDB 存储引擎默认有一个共享表空间（对应文件/var/lib/mysql/ibdata1），也叫系统表空间。InnoDB 系统表空间包含：

• InnoDB 数据字典
  由内部系统表组成，存储表和索引的元数据（定义信息）。
• 双写缓冲
  InnoDB 的页和操作系统的页大小不一致，InnoDB 页大小一般为 16K，操作系统页
  大小为 4K，InnoDB 的页写入到磁盘时，一个页需要分 4 次写。
  
  如果存储引擎正在写入页的数据到磁盘时发生了宕机，可能出现页只写了 4K就宕机了，这种情况叫做部分写失效（partial page write），会导致数据丢失。我们不是有 redo log 吗？但是有个问题，如果这个页本身已经损坏了，用它来做崩溃恢复是没有意义的。所以在对于应用 redo log 之前，需要一个页的副本。如果出现了写入失效，就用页的副本来还原这个页，然后再应用 redo log。这个页的副本就是 double write。通过它实现了数据页的可靠性。
• Change Buffer
• Undo log

3.2.2 独占表空间

每张表独占一个表空间来存放表的索引和数据（但是其他类的数据，如回滚（undo）信息，插入缓冲索引页、系统事务信息，双写缓冲（Double write buffer）等还是存放在系统表空间）。这个开关通过 innodb_file_per_table 设置，默认开启。

SHOW VARIABLES LIKE 'innodb_file_per_table';

3.2.3 通用表空间

可以创建一个通用的表空间，用来存储不同数据库的表，数据路径和文件可以自定
义。语法：

create tablespace ts2673 add datafile '/var/lib/mysql/ts2673.ibd' file_block_size=16K engine=innodb;

3.2.4 临时表空间

存储临时表的数据，包括用户创建的临时表，和磁盘的内部临时表。对应数据目录下的 ibtmp1 文件。当数据服务器正常关闭时，该表空间被删除，下次重新产生.

3.2.5 undo log表空间

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

3.2.6 redo log

见3.1.1.4

3.3 Binlog

• binlog 以事件的形式记录了所有的 DDL 和 DML 语句（因为它记录的是操作而不是数据值，属于逻辑日志）， 可以用来做主从复制和数据恢复。 跟 redo log 的不同在于它的文件内容是可以追加的，没有固定大小限制。
• 在开启了 binlog 功能的情况下，我们可以把 binlog 导出成 SQL 语句，把所有的操作重放一遍，来实现数据的恢复。
• binlog 看用来实现主从复制，它的原理就是从服务器读取主服务器的 binlog，然后执行一遍对应的语句。

综合redolog和undolog之后，一条更新语句的执行流程如下：

例如一条语句：update teacher set name=‘mic’ where id=1;
1、先查询到这条数据，如果有缓存，也会用到缓存。
2、把 name 改成mic，然后调用引擎的 API 接口，写入这一行数据到内存，同时记录 redo log。这时 redo log 进入 prepare 状态，然后告诉执行器，执行完成了，可以随时提交。
3、执行器收到通知后记录 binlog，然后调用存储引擎接口，设置 redo log为 commit状态。
4、更新完成。

重点：

1. 更新语句先记录到内存，再写redolog
2. 记录redolog 分为两个阶段
3. 存储引擎和server端记录不同的日志：存储引擎记录redolog 和undolog，server端记录binlog
4. 先记录redolog, 再记录binolog