MySQL体系结构和InnoDB存储引擎

最新推荐文章于 2024-03-28 08:42:48 发布

南方以南~

最新推荐文章于 2024-03-28 08:42:48 发布

阅读量281

点赞数

分类专栏： MySQL学习笔记

本文链接：https://blog.csdn.net/weixin_41680909/article/details/118633423

版权

MySQL学习笔记专栏收录该内容

2 篇文章 0 订阅

订阅专栏

MySQL体系结构简介

一、定义数据库和实例

数据库领域这两个词十分容易混淆，需要对其概念有比较清楚的理解。

1、数据库：指物理操作系统文件或其他形式文件类型的集合。

2、实例：数据库实例是程序，是位于用户与操作系统之间的一层数据管理软件，应用程序只有通过数据库实例才能和数据库打交道。

在MySQL数据库中，实例与数据库的关系通常是一一对应的，即一个实例对应一个数据库。但是在集群情况下，可能存在一个数据库被多个数据实例使用的情况。MySQL被设计为一个单进程多线程架构的数据库，由此，MySQL数据库实例在系统上的表现就是一个进程。

二、MySQL体系结构

图 2-1

MySQL由以下部分组成，见图2-1：

1.连接池组件
2.管理服务和工具组件
3.Sql接口组件
4.查询分析器组件
5.优化器组件
6.缓冲组件
7.插件式存储引擎（与其他数据库的主要区别）
8.物理文件

MySQL的体系结构可以分为两层,MySQL Server层和存储引擎层,在MySQL Server层中又包括连接层和SQL层;

1、连接层

应用程序通过接口(如ODBC、JDBC)来连接MySQL,最先连接处理的是连接层,连接层包括通信协议、线程处理、用户名密码认证三个部分.通信协议负责检测客户端版本是否与服务端兼容,线程处理是指每一个连接请求都会分配一个对应独立的线程,用户名密码认证验证创建的账号和密码,以及host主机授权是否可以连接到MySQL服务器;

2、SQL层

SQL层包含权限判断、查询缓存、解析器、预处理、查询优化器、缓存和执行计划;
1) 权限判断可以审核用户有没有访问某个库、某个表,或者表里某行的权限.
2) 查询缓存通过Query Cache进行操作,如果数据在Query Cache中,则直接返回结果给客户端;
3) 查询解析器针对SQL语句进行解析,判断语法是否正确,并生成解析树;
4) 预处理器解决解析器无法解析的语义;
5) 优化器对SQL语句进行改写和相应的优化(例如对连接表重排序、对外连接专内连接、代数等价法则、计算和减少常量表达式、自查询优化、早期终结、相等传递等),并生成最优的执行计划;
然后就可以调用程序的API接口,通过存储引擎层访问数据;

3、存储引擎层

MySQL数据库的核心就在于存储引擎;

MySQL数据库是开源的,所以用户可以根据MySQL预定义的存储引擎接口编写自己的存储引擎,如果对现有存储引擎的性能或功能不满意,可以通过修改源码来得到想要的特性;插件式的存储引擎是MySQL与其他数据库区别最明显的地方。

三、MySQL存储引擎

存储引擎可以分为MySQL官方存储引擎和第三方存储引擎,InnoDB存储引擎早期就是第三方存储引擎,后来被Oracle收购,同时也是MySQL数据量OLTP在线事务处理应用最广泛的存储引擎; 下面介绍一下常见的几种MySQL存储引擎：

1、InnoDB存储引擎
InnoDB存储引擎支持事务、其设计目标主要面向在线事务处理(OLTP)的应用,其特点是行锁设计、支持外键,并支持类似于Oracle的非锁定读,即默认读取操作不会产生锁,从MySQL数据库5.58版本开始,InnoDB存储引擎是默认的存储引擎;
InnoDB存储引擎将数据存放在一个逻辑的表空间中,这个表空间就像黑盒一样由InnoDB存储引擎自身进行管理,从MySQL4.1(包括4.1)版本开始,它可以将每个InnoDB存储引擎的表单独存放到一个独立的ibd文件中,此外,InnoDB存储引擎支持用裸设备(row disk)用来建立其表空间;
InnoDB存储引擎通过使用多版本并发控制(MVCC)来获得高并发性,并且实现了SQL标准的4种隔离级别,默认为REPEATABLE级别,同时,使用一种被称为next-key locking的策略来避免幻读(phantom)现象的产生,除此之外,InnoDB存储引擎还提供了插入缓冲(insert buffer)、二次写(double write)、自适应哈希索引(adaptive hash index)、预读(read ahead)等高性能和高可用的功能;
对于表中的数据存储,InnoDB存取引擎采用来聚集(clustered)的方式,因此每张表的存储都是按主键的顺序进行存放,如果没有显示地在表定义时指定主键,InnoDB存储引擎会为每一行生成一个6字节的ROWID,并以此作为主键;

2、MyISAM存储引擎
MyISAM存储引擎不支持事务、表锁设计,支持全文索引,主要面向一些OLAP数据应用,在MySQL5.58版本之前MyISAM存储引擎是默认的存储引擎(除Windows版本外),数据库系统与文件系统很大的一个不同之处在于对事务的支持,然而MyISAM存储引擎是不支持事务的,这个也不是很难理解,试想一下用户是否在所有的应用中都需要事务呢?在数据仓库中,如果没有ETL这些操作,只是简单的报表查询是否还需要事务的支持呢?此外,MyISAM存储引擎的另一个与众不同的地方是它的缓冲池只缓存(cache)索引文件,而不是缓冲数据文件,这一点和大多数的数据库都非常不同;
MyISAM存储引擎表由MYD和MYI组成,MYD用来存放数据文件,MYI用来存放索引文件,可以通过使用myisampack工具来进一步压缩数据文件,因为myisampack工具使用赫夫曼(Huffman)编码静态算法来压缩数据,因此使用myisampack工具压缩后的表是只读的,也可以通过myisampack来解压数据文件;
在MySQL5.0版本之前,MyISAM默认支持的表大小为4GB,如果需要支持大于4GB的MyISAM表时,则需要指定MAX_WORS和AVG_ROW_LENGTH属性,从MySQL5.0版本开始,MyISAM默认支持256TB的但表数据,这足够满足一般应用需求;
注意:对于MyISAM存储引擎表,MySQL数据只缓存其索引文件,数据文件的缓存交由操作系统本身来完成,这与其他使用LRU算法缓存数据的大部分数据库大不相同,此外,在MySQL5.1.23版本之前,无论是在32位还是64位操作系统环境中,缓存索引的缓冲区最大只能设置为4GB,在此之后的版本中,64位系统可以支持大于4GB的索引缓冲区了;

3、Memory存储引擎
Memory存储引擎(之前称HEAP存储引擎)将表中的数据存放在内存中,如果数据库重启或者发生崩溃,表中的数据都将消失,它非常适合用于存储临时数据的临时表,以及数据仓库中的纬度表,Memory存储引擎默认私用哈希索引,而不是B+数索引;
虽然Memory存储引擎速度非常快,但是在使用上还是有一定的限制,比如,只支持表锁,并发性能较差,并且不支持TEXT和BLOB列类型,最重要的是,存储变长字段(varchar)时是按照定长地段(char)的方式进行的,因此会浪费内存;
还有一点,MySQL数据库使用Memory存储引擎作为临时表来存放查询的中间结果集(intermediate result),如果中间结果集大于Memory存储引擎表的容量设置,又或者中间结果含有TEXT或BLOB列类型字段,则MySQL数据库会把其转换到MyISAM存储引擎表而存放到磁盘中;

4、Archive存储引擎
Archive存储引擎只支持INSERT和SELECT操作,从MySQL5.1开始支持索引,Archive存储引擎使用zlib算法将数据行(row)进行压缩存储,压缩比一般可达1:10,正如其名字所示,Archive存储引擎非常适合存储归档数据,如日志信息,Archive存储引擎使用行锁来实现高并发的插入操作,但是其本身并不是事务安全的存储引擎,其设计目标主要是提供高速的插入和压缩功能;

5、Meria存储引擎
Maria存储引擎是新开发的引擎,设计目标主要是用来取代原有的MyISAM存储引擎,从而成为MySQL的默认存储引擎,Mria存取引擎的开发者是MySQL的创始人之一的Michael Widenius,因此,它可以看做是MyISAM的后续版本,Maria存储引擎的特点是:支持缓存数据和索引文件,应用了行锁设计,提供MVCC功能,支持事务和非事务安全的选项,以及更好的BLOB字符类型的处理性能;

InnoDB和MyISAM是最主流的两个存储引擎，现在数据库版本默认的存储引擎是InnoDB。可以使用show engines语句查看当前那使用的MySQL数据库所支持的存储引擎，也可以通过查找information_schema架构下的ENGINES表，如下图所示：

我本机默认存储引擎是InnoDB，支持MyISAM、Memory等存储引擎。

InnoDB存储引擎

上一节中已经简单介绍了InnoDB存储引擎，这里再进入InnoDB原理层面详细介绍。InnoDB存储引擎是第一个完整支持ACID事务的MySQL存储引擎，其特点是行锁设计、支持MVCC、支持外键、提供一致性非锁定读，同时被设计用来最有效的利用以及使用内存和CPU。

一、MySQL体系架构

InnoDB的体系架构如下图所示：

InnoDB体系架构主要包含三部分：后台线程，缓冲池，文件。

（一）后台线程

InnoDB使用多线程模型，后台线程主要分为四个线程：

Master Thread： 最核心的一个线程，用于异步刷新缓冲页到磁盘，保证数据一致性；
IO Thread： InnoDB中使用了大量AIO来处理IO请求，这样可以极大提高数据库的性能，该线程主要负责这些IO请求的回调；共有四个IO Thread，分别是write、read、insert buffer、log IO Thread。
Purge Thread： 该线程用来回收无用的undo页，InnoDB支持多个Purge线程回收undo页；
Page Cleaner Thread： 负责脏页刷新到磁盘的操作，异步刷新，在InnoDB 1.2x版本中引入。

可以用show engine innodb status;命令来观察InnoDB中IO Thread, 如下图：

（二）InnoDB内存

1、缓冲池

缓冲池，简称BP。BP以Page页为单位，默认大小16K，BP的底层采用链表数据结构管理Page。在InnoDB访问表记录和索引时会在Page页中缓存，以后使用可以减少磁盘IO操作，提升效率。InnoDB存储引擎是基于磁盘存储的，并将其中的记录按照页的方式进行管理，可以将其视为基于磁盘的数据库系统。

图2-1

如图2-1所示，缓冲池中主要存放：

数据页
索引页
undo页
Change Buffer页
自适应哈希索引
锁信息
数据字典信息
redo日志

可以使用show engine innodb status命令观察缓冲池实例对象的运行状态，如下图：

Page管理机制 Page根据状态可以分为三种类型:

free page : 空闲page，未被使用
clean page:被使用page，数据没有被修改过
dirty page:脏页，被使用page，数据被修改过，页中数据和磁盘的数据产生了不一致

针对上述三种page类型，InnoDB通过三种链表结构来维护和管理

free list :表示空闲缓冲区，管理free page
lru list:表示正在使用的缓冲区，管理clean page和dirty page，缓冲区以 midpoint为基点，前面链表称为new列表区，存放经常访问的数据，占63%;后面的链表称为old列表区，存放使用较少数据，占37%。（可以通过 innodb_old_blocks_pct修改，默认值为 37）
flush list:表示需要刷新到磁盘的缓冲区，管理dirty page，内部page按修改时间排序。脏页即存在于flush链表，也在LRU链表中，但是两种互不影响，LRU链表负责管理page的可用性和释放，而flush链表负责管理脏页的刷盘操作。

改进型LRU算法维护

普通LRU:末尾淘汰法，新数据从链表头部加入，释放空间时从末尾淘汰
改进LRU:链表分为new和old两个部分，加入元素时并不是从表头插入，而是从中间 midpoint位置插入，如果数据很快被访问，那么page就会向new列表头部移动，如果数据没有被访问，会逐步向old尾部移动，等待淘汰。
每当有新的page数据读取到buffer pool时，InnoDb引擎会判断是否有空闲页，是否足够，如果有就将free page从free list列表删除，放入到LRU列表中。没有空闲页，就会根据LRU算法淘汰LRU链表默认的页，将内存空间释放分配给新的页。

改进LRU的目的主要是防止全表扫描等操作影响热缓存。

Buffer Pool配置参数

show variables like ‘%innodb_page_size%’; //查看page页大小
show variables like ‘%innodb_old%’; //查看lru list中old列表参数
show variables like ‘%innodb_buffer%’; //查看buffer pool参数

2、重做日志缓冲

innodb内存区域除了缓冲池外，还有重做日志缓冲。innodb存储引擎会先将重做日志信息写入缓冲区，然后按照一定频率刷新到重做日志文件。其可由参数innodb_log_buffer_size进行控制。

通常情况下，8M的重做日志缓冲能够满足大多数应用。因为在以下条件会刷新到重做日志文件

master thread每一秒将重做日志缓冲刷新到重做日志文件
每个事务提交时会刷新到重做日志文件
当重做日志缓冲池剩余空间小于1/2时，重做日志缓冲会刷新到重做日志文件

3、额外的内存池

在对一些数据结构本身的内存进行分配时,需要从额外的内存池中进行申请,当该区域的内存不够的时候,会从缓冲池进行申请。因此在申请了很大的缓冲池时，也应考虑相应增加这个值。

（三）InnoDB关键特性

InnoDB存储引擎的三个关键特性：插入缓冲（insert buffer）、二次写（double write）、自适应哈希索引（adaptive hash index）。

1.Insert Buffer

对于非聚集索引的插入和更新，不是每一次直接插入索引页中，而是首先判断插入的非聚集索引页是否在缓冲池中，如果在，则直接插入，否则，先放入一个插入缓冲区中。好似欺骗数据库这个非聚集的索引已经插入到叶子节点了，然后再以一定的频率执行插入缓冲和非聚集索引页子节点的合并操作，这时通常能将多个插入合并到一个操作中，这就大大提高了对非聚集索引执行插入和修改操作的性能。

　　插入缓冲使用的条件：

　　1、索引是辅助索引；

　　2、索引不是唯一的；

2、double write

innodb的数据页一般大小是16KB，MySQL存取数据的最小单位也是页，而操作系统并不能保障一个数据页的原子性，也就是说当写入数据时，有可能在一个页中写入一半时（比如8K）数据库宕机，这种情况称为部分写失效（partial page write），从而导致数据丢失。

这里有一个问题，不可以根据redo log进行数据恢复吗？答案是肯定的也是否定的，要分为两种情况：

1、数据库宕机，物理文件完好无损，是可以通过redo log进行崩溃恢复。

2、数据库宕机，正在刷新到磁盘的页发生partial page write，而正好在磁盘上的这个数据页由于宕机发生损坏，这时就无法通过redo log进行数据恢复了，这里要清楚的认识到，redo log里记录的是对页的物理操作，比如一条redo记录"page number xx，偏移量 800 写记录 “this is abc”"，那当页损坏时，这条redo记录就没有意义了，在这种特殊情况下，doublewrite就必不可少了。

两次写给innodb带来的是可靠性，主要用来解决部分写失败(partial page write)。doublewrite有两部分组成，一部分是内存中的doublewrite buffer，大小为2M，另外一部分就是物理磁盘上的共享表空间中连续的128个页，即两个区，大小同样为2M。当缓冲池的胀业刷新时，并不直接写硬盘，而是通过memcpy函数将脏页先拷贝到内存中的doublewrite buffer，之后通过doublewrite buffer再分两次写，每次写入1M到共享表空间的物理磁盘上，然后马上调用fsync函数，同步磁盘，如下图所示：

3、adaptive hash index

由于innodb不支持hash索引，但是在某些情况下hash索引的效率很高，于是出现了 adaptive hash index功能，innodb存储引擎会监控对表上索引的查找，如果观察到建立hash索引可以提高性能的时候，则自动建立hash索引。可以通过 show engine innodb status 来查看自适应哈西索引的使用情况。可以使用innodb_adaptive_hash_index来禁用和启用hash索引，默认开启。

4、异步IO

为了提高磁盘操作性能，当前的数据库系统都采用异步IO的方式来处理磁盘操作。InnoDB也是如此。

与AIO对应的是Sync IO，即每进行一次IO操作，需要等待此次操作结束才能继续接下来的操作。但是如果用户发出的是一条索引扫描的查询，那么这条SQL语句可能需要扫描多个索引页，也就是需要进行多次IO操作。在每扫描一个页并等待其完成再进行下一次扫描，这是没有必要的。用户可以在发出一个IO请求后立即再发出另外一个IO请求，当全部IO请求发送完毕后，等待所有IO操作完成，这就是AIO。

AIO的另外一个优势是进行IO Merge操作，也就是将多个IO合并为一个IO操作，这样可以提高IOPS的性能。

在InnoDB 1.1.x之前，AIO的实现是通过InnoDB存储引擎中的代码来模拟的。但是从这之后，提供了内核级别的AIO的支持，称为Native AIO。Native AIO需要操作系统提供支持。Windows和Linux都支持，而Mac则未提供。在选择MySQL数据库服务器的操作系统时，需要考虑这方面的因素。

MySQL可以通过参数innodb_use_native_aio来决定是否启用Native AIO。在InnoDB存储引擎中，read ahead方式的读取都是通过AIO完成，脏页的刷新，也是通过AIO完成。