innodb表结构（一条SQL在Innodb中的执行过程）

逗爷 2023-05-29 23:30:40 65

innodb表结构（一条SQL在Innodb中的执行过程）首先分析SQL命令语法的合法性，并尝试将SQL命令分解成数据结构，若分解失败，则提示SQL语句不合理。查询分析器组件(Parser)负责监听对客户端向MySQL Server端的各种请求，接收请求，转发请求到目标模块。每个成功连接MySQL Server的客户请求都会被创建或分配一个线程，该线程负责客户端与MySQL Server端的通信，接收客户端发送的命令，传递服务端的结果信息等。MYSQL架构最上层体系有时也称之为连接层，往往容易混淆，各位读者自己多多留心SQL接口组件(SQL Interface)接收用户SQL命令，如DML DDL和存储过程等，并将最终结果返回给用户。

Mysql体系架构
Innodb内存结构和磁盘结构详解

1 Mysql体系架构

了解Mysql架构体系的同学，应该都很清楚Mysql架构体系自上而下由四层结构构成，如下图所示：

innodb表结构（一条SQL在Innodb中的执行过程）(1)

连接层（Connector组件）：MySQL 可以运行于多个系统上，并且支持多种语言。这些编程语言包括 C、C 、Python、Java、Perl、PHP、Eiffel、Ruby 和 Tcl 等。所以连接层是为了给上层系统封装一个连接接口，实现各个平台于MySQL服务器的交互操作，具体实现由各个平台自己实现。
服务层（Management Service & Utilities）：MySQL服务管理组件和工具组件，提供对MySQL的集成管理，如备份(Backup) 恢复(Recovery) 安全管理(Security)等，包含了如下具体组件：

组件	作用
连接池组件(Connection Pool)	负责监听对客户端向MySQL Server端的各种请求，接收请求，转发请求到目标模块。每个成功连接MySQL Server的客户请求都会被创建或分配一个线程，该线程负责客户端与MySQL Server端的通信，接收客户端发送的命令，传递服务端的结果信息等。MYSQL架构最上层体系有时也称之为连接层，往往容易混淆，各位读者自己多多留心
SQL接口组件(SQL Interface)	接收用户SQL命令，如DML DDL和存储过程等，并将最终结果返回给用户。
查询分析器组件(Parser)	首先分析SQL命令语法的合法性，并尝试将SQL命令分解成数据结构，若分解失败，则提示SQL语句不合理。
优化器组件（Optimizer）	每个编写SQL程序的人都会认为MySQL底层就会按照自己所写的自己所想的方式执行自己的SQL，但是往往并非如此，就是应为优化器组件的存在，对SQL命令按照标准流程进行优化分析。SQL真正执行的过程可以通过Explain查询SQL的执行计划进行分析
缓存主件（Caches & Buffers）	缓存和缓冲组件

引擎层：MySQL存储引擎在MySQL中扮演重要角色，其作比较重要作用，大致归结为如下：表的创建，数据的存储，检索，更新等。存储引擎即要执行各种SQL命令，又要将具体数据保存到磁盘当中，所以存储引擎包含了两部分结构：内存结构和磁盘结构。
数据层：实际存储MySQL 数据库文件和一些日志文件等的系统，如Linux，Unix Windows等。

2 Innodb的内存结构和磁盘结构

innodb表结构（一条SQL在Innodb中的执行过程）(2)

Innodb内存结果和磁盘结构

Innodb内存结构

内存结构：内存结构主要包含4部分：Buffer Pool Change Buffer Log Buffer和Adaptive Hash Index.

Buffer Pool:Buffer Pool缓存的是数据页和索引页（为什么以页为单位：根据操作系统的局部性原理，操作系统每次访问数据的时候相邻的数据很有可能也会被访问，所以操作系统每次都都是读取一块区域的数据，该数据称为页，一页大小为8kb），Buffer Pool使用三个链表维护其内存的使用，如下：

1》free链表：维护空闲空间，当存储新的数据页或者索引页时，通过该链表查询空闲空间 2》flush链表：当数据页中的某条数据被修改时，数据页并不会立刻刷新到磁盘，而是保存在Buffer Pool 中，该页称为脏页，脏页由flush链表维护，并由后台线程定时刷新到磁盘中，提高数据库的更新性能 3》lru链表：分为两部分，热数据区和冷数据区（5：3）.可以理解为两条链表，一条维护热数据，一条维护冷数据，当一条数据加入时插入链表尾部

Changer Buffer:在更新数据的时候，如果这个数据页不是唯一索引（索引的值不允许重复），也就不需要从磁盘加载索引页判断数据是不是重复（唯一性检查）。这种情况下可以先把修改记录在内存的缓冲池中，从而提升更新语句（Insert、Delete、Update）的执行速度。这一块区域就是 Change Buffer。5.5 之前叫 Insert Buffer 插入缓冲，现在也能支持 delete 和 update，最后把 Change Buffer 记录到数据页的操作叫做 merge.在访问这个数据页的时候，或者通过后台线程、或者数据库 shut down、redo log 写满时触发.如果数据库大部分索引都是非唯一索引，并且业务是写多读少，不会在写数据后立刻读取，就可以使用 Change Buffer（写缓冲）。写多读少的业务，调大这个值

Log Buffer:主要缓存的redo log日志文件。当Buffer Pool中的数据还没有刷新到磁盘，或正在向磁盘刷新的时候，数据库宕机或者重启，那么Buffer Pool中的数据就会丢失，如果向磁盘刷新数据的时候宕机甚至会破坏数据库中的数据结构，导致整个数据库无法使用。为了避免这个问题，InnoDB 把所有的修改操作专门写入一个日志文件，并且在数据库启动时从这个文件进行恢复操作（实现 crash-safe）——用它来实现事务的持久性。这个文件就是磁盘的 redo log（叫做重做日志），对应于/var/lib/mysql/目录下的ib_logfile0 和 ib_logfile1，每个 48M这种日志和磁盘配合的整个过程，其实就是 MySQL 里的 WAL 技术（Write-Ahead Logging），它的关键点就是先写日志，再写磁盘。

当然redo log主要是用于异常回复，数据来源还是从 buffer pool 里面通过刷盘策略刷新到DB File里面 redolog大小是固定的，两个48M的文件，满了之后会覆盖老的数据 undo log ：事务撤销回滚日志，记录事务发生之前的状态 undo log 是逻辑日志，redo log是物理日志（两个日志都属于存储引擎层）

Adaptive Hash Index：索引应该是放在磁盘的，为什么要专门把一种哈希的索引放到内存？

2. Innodb磁盘结构

系统表空间 system tablespace：默认情况下 InnoDB 存储引擎有一个共享表空间（对应文件/var/lib/mysql/ibdata1），也叫系统表空间。InnoDB 系统表空间包含 InnoDB 数据字典和双写缓冲区，Change Buffer 和 UndoLogs），如果没有指定 file-per-table，也包含用户创建的表和索引数据

1、undo 有独立的表空间。 2、数据字典：由内部系统表组成，存储表和索引的元数据（定义信息）。 3、双写缓冲（InnoDB 的一大特性）：因为InnoDB 的页和操作系统的页大小不一致，InnoDB 页大小为 16K，操作系统页大小为 4K， InnoDB 的页写入到磁盘时，一个页需要分 4 次写。如果存储引擎正在写入页的数据到磁盘时发生了宕机，可能出现页只写了一部分的情况，比如只写了 4K，就宕机了，这种情况叫做部分写失效（partial page write），可能会导致数据丢失。我们不是有 redo log 吗？但是有个问题，如果这个页本身已经损坏了，怎么办?所以在对于应用 redo log 之前，需要一个页的副本。如果出现了写入失效，就用页的副本来还原这个页，然后再应用 redo log。这个页的副本就是 double write，InnoDB 的双写技术。通过它实现了数据页的可靠性。因为 double write 是顺序写入的，不会带来很大的开销。在默认情况下，所有的表共享一个系统表空间，这个文件会越来越大，而且它的空间不会收缩

独占表空间 file-per-table tablespaces：我们可以让每张表独占一个表空间。这个开关通过 innodb_file_per_table 设置，默认开启。开启后每张表会开辟一个表空间，存放在数据目录下的 ibd 文件（例如/var/lib/mysql/gupao/user_innodb.ibd），存放表的索引和数据，但是其他类的数据，如回滚（undo）信息，插入缓冲索引页、系统事务信息，二次写缓冲（Double write buffer）等还是存放在原来的共享表空间内。

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

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

Redo log：磁盘结构里面的 redo log，redo log是用于异常恢复的，redolog大小是固定的，两个48M的文件，满了之后会覆盖老的数据

undo Log：undo Log 的数据默认在系统表空间 ibdata1 文件中，因为共享表空间不会自动收缩，也可以单独创建一个 undo 表空间。

undo log ：事务撤销回滚日志，记录事务发生之前的状态 undo log 是逻辑日志，redo log是物理日志（两个日志都属于存储引擎层）3 一条修改SQL的执行过程

1、事务开始，从内存或磁盘取到这条数据(修改数据前要先取得数据)，返回给 Server 的执行器 2、执行器修改这一行数据的值为 penyuyan； 3、记录 name=qingshan 到 undo log； 4、记录 name=penyuyan 到 redo log； 5、调用存储引擎接口，在内存（Buffer Pool）中修改 name=penyuyan； 6、事务提交4 Mysql中三种日志文件额作用

redo log:数据修改时，并不会立马同步到磁盘（修改一条数据要将整个页的数据同步到磁盘，影响性能），而是以脏页的形式保存在Buffer Pool中，以链表的形式维护，由后台线程进行同步操作，但是为了避免Buffer Pool中的数据刷新到磁盘之前丢失，所以事务提交之前将修改的操作记录到redo log日志文件当中

undo log:记录数据修改之前的数据，用于数据回滚和数据撤销时，数据的恢复。

bin log:以二进制的形式保存数据库的操作过程，可用于任何数据库存储应勤，主要用于不同数据库之间的数据同步，如主从复制。在开启了 binlog 功能的情况下，我们可以把 binlog 导出成 SQL 语句，把所有的操作重放一遍，来实现数据的恢复。binlog 的另一个功能就是用来实现主从复制，它的原理就是从服务器读取主服务器的 binlog，然后执行一遍

5 一条查询SQL的执行过程

innodb表结构（一条SQL在Innodb中的执行过程）(3)