mysql数据库之mysql架构与执行流程

一：Mysql基础架构

1：基础架构

mysql基础架构图

MySQL基架大致包括如下几大模块组件简介：

 （1）MySQL向外提供的交互接口（Connectors）
 
  		Connectors组件，是MySQL向外提供的交互组件，如java,.net,php等语言可以通过该组件来操作SQL语句，实现与SQL的交互。

 （2）管理服务组件和工具组件(Management Service & Utilities)
 
 		 提供对MySQL的集成管理，如备份(Backup),恢复(Recovery),安全管理(Security)等

 （3）连接池组件(Connection Pool)
 
 		负责监听对客户端向MySQL Server端的各种请求，接收请求，转发请求到目标模块。每个成功连接MySQL Server的客户请求都会被创建或分配一个线程，该线程负责客户端与MySQL Server端的通信，接收客户端发送的命令，传递服务端的结果信息等。

 （4）SQL接口组件(SQL Interface)
 
 		接收用户SQL命令，如DML,DDL和存储过程等，并将最终结果返回给用户。

 （5）查询分析器组件(Parser)
 
 		首先分析SQL命令语法的合法性，并尝试将SQL命令分解成数据结构，若分解失败，则提示SQL语句不合理

 （6）优化器组件（Optimizer）
 
 		对SQL命令按照标准流程进行优化分析。

 （7）缓存主件（Caches & Buffers）
 
 		缓存和缓冲组件

 （8）插件式存储引擎（Pluggable Storage Engines）
 
 		mysql存储引擎

 （9）物理文件（File System）
 
 		 实际存储MySQL 数据库文件和一些日志文件等的系统，如Linux，Unix,Windows等

2：逻辑存储

• mysql存储结构

级别	原文	备注
表空间	tablespace	对应ibd文件和frm文件
段	segment	一个索引2个段
镞	Extent	1MB,由64个连续的页构成
页	Page	InnoDB磁盘I/O的最小单位16KB:64个page=1个Extent
行	row
字段	Field

• 表空间

  • 表空间可以看做是 InnoDB 存储引擎逻辑结构的最高层，所有的数据都存放在表空间中。
  • 按类型分为：系统表空间、独占表空间、通用表空间、临时表空间。
  • InnoDB 存储引擎有一个默认的共享表空间 ibdata1。在这种情况下，所有的表共享一个表空间，这个文件会越来越大，而且它的空间不会收缩。
  • 如果我们需要为每张表开辟一个表空间，可以把 innodb_file_per_table=1（ON，默认）。但是其他类的数据，如回滚（undo）信息，插入缓冲索引页、系统事务信息，二次写缓冲（Double write buffer）等还是存放在原来的共享表空间内。 开启后，在数据目录下，每个表都会有一个目录，代表一个表空间文件，InnoDB 中是 ibd 文件。ibd文件存储的内容主要是 B+树（索引）。一个表可以有多个索引，都在一个文件中。

• 页

  • 每个页默认 16KB。页是 InnoDB 存储引擎磁盘管理的最小单位。
  • 为了高效管理物理空间，对簇进一步细分，就得到了页。
  • 簇是由连续的页（Page）组成的空间，一个簇中有 64 个连续的页。 （1MB/16KB=64这些页面在物理上和逻辑上都是连续的。
  • 注意，文件系统中，也有页的概念，它是计算机管理存储器的逻辑块，在很多操作系统中，页的大小通常是 4K（8 个扇区*512 字节）。
    
  • 页的分裂与合并
    • 往表中插入数据时，如果一个页面已经写完，则会产生一个新的叶页面。
    • 如果一个簇的所有的页面都被用完，会从当前页面所在段新分配一个簇。
    • 如果数据不是连续的，往已经写满的页中插入数据，会导致叶页面分裂：
  • 常见的页类型

  类型	描述
  数据页	B-tree Node
  Undo 页	Undo Log Page
  系统页	System Page
  事务数据页	Transaction system Page
  插入缓冲位图页	Insert Buffer Bitmap
  插入缓冲空闲列表页	Insert Buffer Free List
  未压缩的二进制大对象页	Uncompressed BLOB Page
  压缩的二进制大对象页	Compressed BLOB Page

二：mysql查询与更新语句执行流程

1：一条查询语句执行流程

• 查询语句执行流程示意图
  

• 通信协议

  • MySQL 支持多种通信协议，可以使用同步/异步的方式，支持长连接/短连接

  • 我们采用异步长连接的形式

    	大部分时候我们用的是同步。异步有什么特点？
      1、异步不能节省 SQL 执行的时间
      2、如果异步存在并发，每一个 SQL 的执行都要建立一个连接，避免数据混乱。但是这样会给服务端
      带来巨大的压力（一个连接就会创建一个线程，线程间切换会占用大量 CPU 资源）。另外异步通信还带来了编码的复杂度，所以一般不建议使用。
      如果要异步，必须使用连接池，排队从连接池获取连接而不是创建新连接。
    

    交互时间

      show global variables like 'wait_timeout'; （非交互式超时时间，如 JDBC 程序）
      show global variables like 'interactive_timeout'; （交互式超时时间，如数据库工具）
      默认都是 28800 秒，8 小时。
      netstat -an|grep 3306|wc -l  查看 MySQL 当前有多少个连接
      show variables like 'max connections'；，查看MySQL 默认的连接数是 151 个，最大是 16384（2^14）。
    

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

• 查询缓存
  在 MySQL 8.0 中，查询缓存已经被移除了

• 语法解析和预处理

  • 词法解析
    词法分析就是把一个完整的 SQL 语句打碎成一个个的单词。
    比如一个简单的 SQL 语句：
    select name from user where id = 1;
    它会打碎成 8 个符号，每个符号是什么类型，从哪里开始到哪里结束。

  • 语法解析

    • 语法分析会对 SQL 做一些语法检查，比如单引号有没有闭合，然后根据 MySQL定义的语法规则，根据 SQL 语句生成一个数据结构。这个数据结构我们把它叫做解析树
    • 在解析 SQL 的环节里面有个预处理器。它会检查生成的解析树，解决
      解析器无法解析的语义。比如，它会检查表和列名是否存在，检查名字和别名，保证没有歧义。

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

  EXPLAIN select name from user where id=1; 查看执行计划语句
  

• 存储引擎
  在 MySQL 里面，支持多种存储引擎，他们是可以替换的，所以叫做插件式的存储引

  • 常见存储引擎
    
    1、InnoDB：支持事务处理，支持外键，支持崩溃修复能力和并发控制。

    2、MyISAM：占用空间小，查询速度快。缺点是不支持事务的完整性和并发性。

    3、MEMORY：所有的数据都在内存中，处理速度快，但是安全性不高。对表的大小有要求，依赖内存，不能建立太大的表。

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

2：一条更新语句执行流程

更新语句执行流程图下图所示

三：缓冲池 Buffer Pool

• 首先，在 InnoDB 里面有个内存的缓冲池（Buffer Pool）。我们对数据的更新，不会每次都直接读写磁盘，因为 IO 的代价太大了，所以先写入到 Buffer Pool 里面，可以加速数据的访问。
• 内存的数据页和磁盘数据不一致的时候，我们把它叫做脏页。InnoDB 里面有专门的后台线程把 Buffer Pool 的数据写入到磁盘，每隔一段时间就一次性地把多个修改写入磁盘，这个就叫做刷脏。
• 但是这里面就会有一个问题，如果在脏页还没有写入磁盘的时候，服务器出问题了，内存里面的数据丢失了。或者是刷脏刷到一半，甚至会破坏数据文件。这个问题怎么解决呢？我们必须要有一个持久化的机制redolog。
  

四：日志

1：重做日志 redo log

• InnoDB 引入了一个日志文件，叫做 redo log 重做日志（数据目录下的 ib_logfile0 和 ib_logfile1，每个48M）。

• 我们把所有对内存数据的修改操作写入日志文件，如果服务器出问题了，我们就从这个日志文件里面读取数据，恢复数据——用它来实现事务的持久性。

• 这种日志和磁盘配合的整个过程，其实就是 MySQL 里的 WAL 技术（Write-Ahead Logging），它的关键点就是先写日志，再写磁盘。

• redo log 有什么特点？

    1、不是记录数据页更新之后的状态，而是记录这个页做了什么改动，属于物理日志。
    2、redo log 的大小是固定的，前面的内容会被覆盖。
    注意 redo log 是 InnoDB 存储引擎实现的，并不是所有存储引擎都有。
  

2：undo log 回滚日志

• undo log（撤销日志或回滚日志）记录了事务发生之前的数据状态（不包括 select），在执行 undo的时候，仅仅是将数据从逻辑上恢复至事务之前的状态，而不是从物理页面上操作实现的，属于逻辑格式的日志。可以用于回滚（保持原子性），同时可以提供多版本并发控制下的读（MVCC），也即非锁定读。读取一行记录时，若已被其他事务占据，则通过 undo 读取之前的版本。
• Redo Log 和 Undo Log 与事务密切相关，统称为事务日志
• Undo Log 的数据默认在系统表空间 ibdata1 文件中，因为共享表空间不会自动收缩，也可以单独创建一个 Undo 表空间。

3：biglog 二进制日志

• MySQL Server 层也有一个日志文件，叫做 binlog，它可以被所有的存储引擎使用。
• binlog 以事件的形式记录了所有的 DDL 和 DML 语句（因为它记录的是操作而不是数据值，属于逻辑日志），可以用来做主从复制和数据恢复。
• 文件内容是可以追加的，没有固定大小限制

五：InnoDB和磁盘存储结构

InnoDB 的整个体系架构由多个内存块组成的缓冲池以及多个后台线程构成：

1. 内存结构

缓冲池主要分为 4 个部分： Buffer Pool、（redo）log buffer、Change Buffer、Adaptive Hash Index。

• Buffer Pool
  Buffer Pool 由包含数据页(data page)、索引页(data page)等的缓冲数据。InnoDB 用 LRU 算法（Leastrecently Used）来管理缓冲池（不是传统的 LRU，分成了 young 和 old），经过淘汰的数据就是热点数据。

    show VARIABLES like '%InnoDB_buffer_pool%'; 
    Buffer Pool 默认大小是 128M。
  

• Change Buffer
  注意：旧版本中叫 Insert Buffer。

  • 当需要更新一个数据页时，如果数据页在 Buffer Pool 中存在，那么就直接更新好了。同时要记录 redolog。但是如果这个数据页不在 Buffer Pool 中，并且不是唯一索引（普通索引的情况），不需要从磁盘加载索引页判断数据是不是重复（唯一性检查），就会先记录在 Change Buffer 中，从而提升更新语句（Insert、Delete、Update）的执行速度。 最后把 Change Buffer 记录到数据页的操作叫做 merge，会在访问这个数据页，或者通过后台线程、或者数据库 shut down、redo log 写满时触发。如果数据库大部分索引都是非唯一索引，并且业务是写多读少，不会在写数据后立刻读取，就可以使用 Change Buffer（写缓冲）。
    innodb_change_buffer_max_size 配置 Change Buffer 占 Buffer Pool 的比例。

• Log Buffer
  Redo log 一样也不是直接写入磁盘文件的，而是有一个缓冲，Redo Log Buffer 用来优化 redo log 的性能。大小由下面这个参数决定：

    show variables like '%innodb_log_buffer_size%'; 默认 16M
  

• Adaptive Hash Index
  InnoDB 存储引擎会监控对表上索引的查找，如果观察到建立哈希索引可以带来速度的提升，则热点页建立哈希索引，来提高查询效率

2：磁盘结构

• 系统表空间 system tablespace
  InnoDB 系统表空间包含 InnoDB 数据字典和双写缓冲区、Change Buffer 和 Undo Logs），也包含用户创建的表和索引数据（没有指定 file-per-table 时）。属于共享表空间。对应于 ibdata1 文件。

• 独占表空间 file-per-table tablespaces
  包含单个 InnoDB 表的数据和索引，并存储在文件系统中自己的数据文件中（tbl name.ibd）

• 通用表空间 general tablespaces
  通用表空间也是一种共享的表空间，跟 ibdata1 类似。
  可以创建一个通用的表空间，用来存储不同数据库的表，数据路径和文件可以自定义。

   create tablespace ts2673 add datafile 'ts2673.ibd' file_block_size=16K engine=innodb; # 创建通用表空间
  

• 临时表空间 temporary tablespaces
  存储临时表的数据，对应数据目录下的 ibtmp1 文件。当数据服务器正常关闭时，该表空间被删除，下次重新产生