Mysql存储引擎

一、数据库引擎

数据库存储引擎是数据库底层软件组织，是用于存储、处理和保护数据的核心服务。利用数据库引擎可控制访问权限并快速处理数据，使用数据库引擎可以创建用于存储数据的表和用于查看、管理和保护数据安全的数据库对象（如表，索引，视图，图表，缺省值，规则，触发器，用户，函数等）。可以使用DBMS管理数据库对象。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎，还可以获得特定的功能。

从发展的历史看，数据库是数据管理的高级阶段，它是由文件管理系统发展起的，由数据库系统根据自己的数据库引擎将存在磁盘上的文件读出来变成各种各样的数据和结构。数据库在计算机中是以文件的形式存在的，下面简单的说下文件存储的格式：

  • db.opt

　　MySQL的每个数据库目录中有一个文件db.opt文件，create database时会自动生成一个文件db.opt。该文件主要用来存储当前数据库的默认字符集和字符校验规则。

  • *.ibd
　　MySQL 使用 InnoDB 存储表时，会将表的定义和数据索引等信息分开存储，其中前者存储在.frm文件中，后者存储在.ibd文件中。.ibd 文件就是每一个表独有的表空间，文件存储了当前表的数据和相关的索引数据。
  •  *.frm

　　无论在 MySQL 中选择了哪个存储引擎，所有的 MySQL 表都会在硬盘上创建一个.frm文件用来描述表的格式或者说定义；.frm文件的格式在不同的平台上都是相同的。 

二、MySql数据引擎分类

MYSQL支持多个数据库引擎：ISAM、MYISAM、HEAP、INNODB和BERKLEY（BDB），要添加一个新的引擎，就必须重新编译MYSQL。本来是对每一个数据引擎都详细的介绍下，但是网上找了很久，都没有详细的文档。这篇文章主要学习INNODB数据引擎，其他的数据引擎根据百科简单的说下自己的理解。

1、ISAM

ISAM（发音是“阿塞姆”）搜索引擎的算法可参考浅谈基础算法之ISAM。ISAM数据库引擎采用的算法是索引顺序存取算法（ISAM）,ISAM结构相当于多叉平衡树，树矮，能够减少硬盘I/O次数，另外树的节点记录多，一次性读取更多的数据，因此ISAM执行读取操作的速度很快，而且不占用大量的内存和存储资源。 ISAM是一个定义明确且历经时间考验的数据表格管理方法，它在设计之时就考虑到数据库被查询的次数要远大于更新的次数，所以常用于读取频繁的应用中。ISAM的两个主要不足之处在于，它不支持事务处理，也不能够容错：如果你的硬盘崩溃了，那么数据文件就无法恢复了。如果你正在把ISAM用在关键任务应用程序里，那就必须经常备份你所有的实时数据，通过其复制特性，MYSQL能够支持这样的备份应用程序。 

2、MYISAM

MYISAM（发音为 "my-阿塞姆"）是MYSQL的ISAM扩展格式。MYSQL-5.5版本之前默认引擎是MyISAM，之后是innoDB。MYISAM和ISAM一样，读取速度很快，除此之外还提供了索引、字段管理、表格锁定等功能，对多个并发的读写操作进行了优化。所以大多数虚拟主机提供商和INTERNET平台提供商只允许使用MYISAM格式。但是它仍然没有提供对数据库事务的支持，也不支持行级锁和外键，因此当INSERT或UPDATE数据时需要锁定整个表，效率便会低一些。

3、HEAP(MEMORY)

HEAP（发音“黑普”）也叫Memory，因为它的数据是放在内存中的，访问速度非常得快，比ISAM和MYISAM都快。HEAP表格在你需要使用SELECT表达式来选择和操控数据的时候非常有用。Memory的表支持HASH索引和BTree索引，默认HASH索引。但是一旦服务关闭，表中的数据就会丢失掉，但是表的结构会保存下来，所以它所管理的数据是不稳定的。另外在数据行被删除的时候，HEAP也不会浪费大量的空间。

4、BERKLEY

Berkeley DB （发音bɚkli）（BDB）是一个高性能的嵌入式数据库引擎，它可以用来保存任意类型的键／值对，而且可以为一个键保存多个数据。Berkeley DB可以支持数千的并发线程同时操作数据库，支持最大256TB的数据。具体可以参考官网文档。

5、INNODB

从Mysql5.5.5版本开始，InnoDB是默认的表存储引擎。InnoDB是事务安全的MySQL存储引擎，支持ACID事务。其设计目标主要面向在线事务处理(OLTP)的应用。其特点是行锁设计、支持外键，并支持非锁定读，即默认读操作不会产生锁。InnoDB存储数据是基于磁盘存储的,且其记录是按照页的方式进行管理。

三、INNODB体系结构

如下图所示，InnoDB存储引擎由缓冲池和一些后台线程组成，InnoDB存储引擎有多个内存块，这些内存块组成了一个大的缓冲池，该缓冲池可以被运行的后台线程所共享。后台线程主要负责刷新缓冲池中的数据、将已修改的数据刷新到磁盘等等。为什么要是用缓冲池？还要后台线程进行刷新到磁盘的操作？InnoDB 存储引擎是基于磁盘存储的，即数据都是存储在磁盘上的，由于 CPU 速度和磁盘速度之间的鸿沟，InnoDB 引擎使用缓冲池技术来提高数据库的整体性能，即通过内存的速度来弥补磁盘速度慢对数据库性能造成的影响。因此缓冲池主要工作

  • 维护所有进程/线程需要访问的多个内部数据结构

  • 缓存磁盘上的数据，方便快速读取，同时在对磁盘文件修改之前进行缓存

  • 缓存重做日志（redo log）

后台线程主要工作

  • 刷新缓冲池中的数据，保证缓冲池中缓存的数据最新

  • 将已修改数据文件刷新到磁盘文件

  • 保证数据库异常时InnoDB能恢复到正常运行状态

可以通过以下命令查看innodb引擎的详细信息，下面是对该信息的解析：红色标记的是注释 
mysql> show engine innodb status;
+--------+------+-----------------------------------------------+
| Type   | Name | Status                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
+--------+------+-----------------------------------------------+
| InnoDB |      |
#第一段是头部信息，它仅仅声明了输出的开始，其内容包括当前的日期和时间，以及自上次输出以来经过的时长。
=====================================
2020-12-04 15:24:15 0x90e8 INNODB MONITOR OUTPUT #当前日期和时间
=====================================
#显示的是计算出这一平均值的时间间隔，即自上次输出以来的时间，
或者是距上次内部复位的时长#从innodb1.0.x开始，可以使用命令show engine innodb status;
来查看master thread的状态信息
Per second averages calculated from the last 49 seconds 
-----------------
BACKGROUND THREAD #后台进程
-----------------
#主线程每秒loop循环的次数(9 激活的次数  0 停止的次数  358242 等待的次数)
srv_master_thread loops: 9 srv_active, 0 srv_shutdown, 358242 srv_idle 
srv_master_thread log flush and writes: 0
----------
SEMAPHORES #锁信息
----------
#操作系统等待数组的信息，它是一个插槽数组，innodb在数组里为信号量保留了一些插槽，
操作系统用这些信号量给线程发送信号，使线程可以继续运行。reservation count显示了innodb分配插槽的频度
OS WAIT ARRAY INFO: reservation count 2 
#操作系统等待线程，signal count表示线程通过数组得到信号的频度，数值越大，
表示有很多I/0等待或者是存在InnoDB争用问题
OS WAIT ARRAY INFO: signal count 2 

RW-shared spins 0, rounds 0, OS waits 0 #RW-shared 共享锁，spins表示共享读锁期间读写锁等待的个数,
rounds表示循环迭带的个数,OS waits系统调用的等待个数
RW-excl spins 0, rounds 0, OS waits 0 #RW-excl 排他锁，spins表示排它写锁期间读写锁等待的个数,
rounds表示循环迭带的个数,OS waits系统调用的等待个数
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 0.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
------------
TRANSACTIONS #事务信息
------------
Trx id counter 37797 #下一个事务号
#所有编号小于37724的事务都已经从历史记录列表中清除了
Purge done for trx's n:o < 37724 undo n:o < 0 state: running but idle 
History list length 77 #历史列表的长度
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 283697766612480, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 283697766611608, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 283697766610736, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
--------
FILE I/O #文件IO
--------
I/O thread 0 state: wait Windows aio (insert buffer thread) #插入缓冲线程
I/O thread 1 state: wait Windows aio (log thread) #日志线程
I/O thread 2 state: wait Windows aio (read thread) #4个异步读线程
I/O thread 3 state: wait Windows aio (read thread)
I/O thread 4 state: wait Windows aio (read thread)
I/O thread 5 state: wait Windows aio (read thread)
I/O thread 6 state: wait Windows aio (write thread) #4个异步写线程
I/O thread 7 state: wait Windows aio (write thread)
I/O thread 8 state: wait Windows aio (write thread)
I/O thread 9 state: wait Windows aio (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] , #每个读写线程状态（4个读4个写）
 ibuf aio reads:, log i/o's:, sync i/o's: #插入缓冲线程的状态,每秒日志IO状态,IO同步的状态
Pending flushes (fsync) log: 0; buffer pool: 0 #文件同步的日志状态,缓冲池的个数
1062 OS file reads, 582 OS file writes, 167 OS fsyncs #读了多少个文件,系统写了多少个文件,系统同步了多少个文件
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s #秒读的信息,平均每秒读的字节数,每秒写入的信息,每秒同步的信息
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX #插入缓冲和自适应哈稀索引
-------------------------------------
#size表示缓冲索引树的当前大小,free表示空闲列表的长度,seg size文件段中已分配段的个数,merges合并页的个数
Ibuf: size 1, free list len 0, seg size 2, 0 merges 
merged operations:
 insert 0, delete mark 0, delete 0 #insert插入缓冲的次数,delete mark标记为已删除的次数,delete表示purge的次数(删除)
discarded operations:
 insert 0, delete mark 0, delete 0 #有多少个insert buffer被丢弃,有多少个insert buffer被标记为已删除,purge多少个insert buffer等
Hash table size 34679, node heap has 0 buffer(s) #自适应哈稀索引单元格的数量与预留缓冲结构的数量
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 3 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s #使用哈稀索引的数量与不能使用哈稀索引时向下搜索B树索引的次数
---
LOG #重做日志信息
---
Log sequence number          32459336 #当前重做日志序列号
Log buffer assigned up to    32459336 
Log buffer completed up to   32459336
Log written up to            32459336 
Log flushed up to            32459336 #刷新到重做日志文件的序列号
Added dirty pages up to      32459336
Pages flushed up to          32459336 #刷新到磁盘的日志序列号
Last checkpoint at           32459336 #下一个日志序列号
174 log i/o's done, 0.00 log i/o's/second #innodb启动后的io个数,最近一次显示之后的每秒io操作个数
----------------------
BUFFER POOL AND MEMORY #缓存池和内存
---------------------- 
Total large memory allocated 137363456 #分配的最大内存总数
Dictionary memory allocated 446405 #数据字典占用的字节数
Buffer pool size   8192 #缓冲池的个数
Free buffers       6994 #剩余缓冲区的个数
Database pages     1193 #LRU中数据页的个数
Old database pages 460 #LRU中旧数据页的个数
Modified db pages  0 #LRU中已修改的数据页个数
Pending reads      0 #挂起读操作的个数
#通过使用LRU算法等待刷新的页数,在BUF_FLUSH_LIST列表等待刷新的页数,在BUF_FLUSH_SINGLE_PAGE等待刷新的页数
Pending writes: LRU 0, flush list 0, single page 0 
Pages made young 1, not young 0 #第一次访问变成新页面的次数,0没有变成新页面的次数
0.00 youngs/s, 0.00 non-youngs/s #LRU中每秒变成新页面的速率,没有变成新页面的速率
Pages read 1039, created 154, written 363 #1039读操作的页面个数,154在缓冲池中创建了没有读取的页面个数,363写操作的页面个数
0.00 reads/s, 0.00 creates/s, 0.00 writes/s #LRU中每秒读取数据的页速率,每秒创建数据的页速率,每秒写入数据的页速率
#读取页面数与获得缓冲池页面的比例,变为新页面的页面数与获得缓冲池页面的比例,没有变为新页面的页面数与获得缓冲池页面的比例
No buffer pool page gets since the last printout 
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s #预读的速率,不通过访问剔除的预读页面的个数
LRU len: 1193, unzip_LRU len: 0 #LRU列表长度,unzip_LRU列表长度
I/O sum[0]:cur[0], unzip sum[0]:cur[0] #LRU在LRU列表中I/O操作的次数,unzip在LRU列表中I/O操作的次数
--------------
ROW OPERATIONS #行操作
--------------
0 queries inside InnoDB, 0 queries in queue #有多少个正在查询操作个数
0 read views open inside InnoDB #显示只读视图的数量
Process ID=7264, Main thread ID=00000000000028AC , state=sleeping #显示主线程的ID及其状态  
Number of rows inserted 14, updated 395, deleted 0, read 6717 #从innodb存储引擎启动后插入,更新,删除,查询的行数
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s #最近一次显示增,删,改,查的速率
----------------------------
END OF INNODB MONITOR OUTPUT
============================
 |
+--------+------+---------------------------------------------------------------------------------+
1 row in set
 

1、后台线程

MySQL是一个单进程多线程架构的数据库，也就是说MySQL数据库实例在系统中表现形式就是一个进程。InnoDB是多线程的模型，后台有多个不同的线程，用来负责不同的任务。主要有如下： 

（1）Master Thread

　　这是最核心的一个线程,主要负责将缓冲池中的数据异步刷新到磁盘，保证数据的一致性，包括赃页的刷新、合并插入缓冲、UNDO 页的回收等。Master thread在主循环中，分为两类操作，每秒钟的操作和每10秒钟的操作：

  • 每秒一次的操作

    • 日志缓冲刷新到磁盘: 即使这个事务还没有提交（总是），这点解释了为什么再大的事务commit时都很快；

    • 合并插入缓冲（可能）: 合并插入并不是每秒都发生，InnoDB会判断当前一秒内发生的IO次数是否小于5，如果是，则系统认为当前的IO压力很小，可以执行合并插入缓冲的操作。

    • 至多刷新100个InnoDB的缓冲池的脏页到磁盘（可能) : 这个刷新100个脏页也不是每秒都在做，InnoDB引擎通过判断当前缓冲池中脏页的比例(buf_get_modified_ratio_pct)是否超过了配置文件中innodb_max_drity_pages_pct参数(默认是90，即90%)，如果超过了这个阈值，InnoDB引擎认为需要做磁盘同步操作，将100个脏页写入磁盘。 

  • 每10秒一次的操作

    • 刷新100个脏页到磁盘（可能）: InnoDB引擎先判断过去10秒内磁盘的IO操作是否小于200次，如果是，认为当前磁盘有足够的IO操作能力，即将100个脏页刷新到磁盘。

    • 合并至多5个插入缓冲（总是）: 此次的合并插入缓冲操作总会执行，不同于每秒操作时可能发生的合并操作。

    • 将日志缓冲刷新到磁盘（总是）: InnoDB引擎会再次执行日志缓冲刷新到磁盘的操作，与每秒发生的操作一样。

    • 删除无用的undo页（总是）: 当对表执行update，delete操作时，原先的行会被标记为删除，但是为了一致性读的关系，需保留这些行版本的信息，在进行10S一次的删除操作时，InnoDB引擎会判断当前事务系统中已被删除的行是否可以删除，如果可以，InnoDB会立即将其删除。InnoDB每次最多删除20个Undo页。

    • 产生一个检查点（checkpoing）； 

（2）IO Thread 

 　　在 InnoDB 存储引擎中大量使用了异步 IO 来处理写 IO 请求，IO Thread 的工作主要是负责这些 IO 请求的回调处理。通过以下命令查询IO线程：

mysql> show variables like 'innodb_%io_threads';
 　　从截图中可以看出innodb引擎中io读线程和写线程的数量。5.6以后的版本可以通过innodb_write_io_threads和innodb_read_io_threads来限制读写线程，而在5.6版本以前，只有一个参数innodb_file_io_threads来控制读写总线程数。 

（3）Purge Thread  

　　事务被提交之后，undo log 可能不再需要，因此需要 Purge来回收已经使用并分配的 undo页，purge操作默认是由master thread中完成的，为了减轻master thread的工作，提高cpu使用率以及提升存储引擎的性能，用户可以在参数文件中添加如下命令来启动独立的purge thread

innodb_purge_threads=1
　　从innodb1.2版本开始，可以指定多个innodb_purge_threads来进一步加快和提高undo回收速度。同时 Purge Thread 需要离散地读取 undo 页，这样能更进一步利用磁盘的随机读取性能。可以通过以下命令查询Purge Thread的情况： 
show variables like 'innodb_purge%'

  　　其中innodb_purge_threads 为开启的Purge Thread数量，innodb_purge_batch_size为设置每次purge清理的undo页数，如果值太大则会导致CPU和磁盘IO过于集中。 

（4）Page Cleaner Thread 

　　Page Cleaner Thread 是在InnoDB 1.2.x版本新引入的，其作用是将之前版本中脏页的刷新操作都放入单独的线程中来完成，这样减轻了 Master Thread 的工作及对于用户查询线程的阻塞。

 

2、缓冲池及其组成

InnoDB 存储引擎是基于磁盘存储的，即数据都是存储在磁盘上的，由于 CPU 速度和磁盘速度之间的鸿沟，InnoDB 引擎使用缓冲池技术来提高数据库的整体性能，即通过内存的速度来弥补磁盘速度慢对数据库性能造成的影响。其工作方式总是将数据库文件按页（每页16K）读取到缓冲池，然后按最近最少使用（LRU）的算法来保留在缓冲池中的缓存数据。在数据库中进行读操作时，首先将从磁盘读到的页存放在缓冲池中，下一次读取相同的页时，首先判定是否存在缓冲池中，如果有就是被命中直接读取，没有的话就从磁盘中读取。在数据库进行改操作时，首先修改缓冲池中的页（修改后，该页即为脏页），然后在以一定的频率刷新到磁盘上。这里的刷新机制不是每页在发生变更时触发。而是通过一种checkpoint机制刷新到磁盘的。所以缓冲池的大小直接影响着数据库的整体性能，可以通过配置参数innodb_buffer_pool_size来设置。从架构图中可以看出缓冲池中缓存的数据页类型有索引页、数据页、 undo 页、插入缓存、自适应哈希索引、 InnoDB 的锁信息、数据字典信息等。索引页和数据页占缓冲池的很大一部分。

  • 数据页和索引页: Page是Innodb存储的最基本结构，也是Innodb磁盘管理的最小单位，与数据库相关的所有内容都存储在Page结构里。Page分为几种类型，数据页和索引页就是其中最为重要的两种类型。

  • 插入缓存: 在InnoDB引擎上进行插入操作时，一般需要按照主键顺序进行插入，这样才能获得较高的插入性能。当一张表中存在非聚簇的且不唯一的索引时，在插入时，数据页的存放还是按照主键进行顺序存放，但是对于非聚簇索引叶节点的插入不再是顺序的了，这时就需要离散的访问非聚簇索引页，由于随机读取的存在导致插入操作性能下降。InnoDB为此设计了Insert Buffer来进行插入优化。对于非聚簇索引的插入或者更新操作，不是每一次都直接插入到索引页中，而是先判断插入的非聚集索引是否在缓冲池中，若在，则直接插入；若不在，则先放入到一个Insert Buffer中。看似数据库这个非聚集的索引已经查到叶节点，而实际没有，这时存放在另外一个位置。然后再以一定的频率和情况进行Insert Buffer和非聚簇索引页子节点的合并操作。这时通常能够将多个插入合并到一个操作中，这样就大大提高了对于非聚簇索引的插入性能。

  • 自适应哈希索引: InnoDB会根据访问的频率和模式，为热点页建立哈希索引，来提高查询效率。InnoDB存储引擎会监控对表上各个索引页的查询，如果观察到建立哈希索引可以带来速度上的提升，则建立哈希索引，所以叫做自适应哈希索引。自适应哈希索引是通过缓冲池的B+树页构建而来，因此建立速度很快，而且不需要对整张数据表建立哈希索引。其有一个要求，即对这个页的连续访问模式必须是一样的，也就是说其查询的条件(WHERE)必须完全一样，而且必须是连续的。

  • 锁信息 : InnoDB存储引擎会在行级别上对表数据进行上锁。不过InnoDB也会在数据库内部其他很多地方使用锁，从而允许对多种不同资源提供并发访问。数据库系统使用锁是为了支持对共享资源进行并发访问，提供数据的完整性和一致性。

  • 数据字典信息 : InnoDB有自己的表缓存，可以称为表定义缓存或者数据字典。当InnoDB打开一张表，就增加一个对应的对象到数据字典。数据字典是对数据库中的数据、库对象、表对象等的元信息的集合。在MySQL中，数据字典信息内容就包括表结构、数据库名或表名、字段的数据类型、视图、索引、表字段信息、存储过程、触发器等内容。MySQL INFORMATION_SCHEMA库提供了对数据局元数据、统计信息、以及有关MySQL server的访问信息（例如：数据库名或表名，字段的数据类型和访问权限等）。该库中保存的信息也可以称为MySQL的数据字典。 
  • 重做日志缓冲：InnoDB有buffer pool（简称bp）。bp是数据库页面的缓存，对InnoDB的任何修改操作都会首先在bp的page上进行，然后这样的页面将被标记为dirty并被放到专门的flush list上，后续将由master thread或专门的刷脏线程阶段性的将这些页面写入磁盘。这样的好处是避免每次写操作都操作磁盘导致大量的随机IO，阶段性的刷脏可以将多次对页面的修改merge成一次IO操作，同时异步写入也降低了访问的时延。然而，如果在dirty page还未刷入磁盘时，server非正常关闭，这些修改操作将会丢失，如果写入操作正在进行，甚至会由于损坏数据文件导致数据库不可用。为了避免上述问题的发生，Innodb将所有对页面的修改操作写入一个专门的文件，并在数据库启动时从此文件进行恢复操作，这个文件就是redo log file。这样的技术推迟了bp页面的刷新，从而提升了数据库的吞吐，有效的降低了访问时延。带来的问题是额外的写redo log操作的开销（顺序IO，当然很快），以及数据库启动时恢复操作所需的时间。redo日志由两部分构成：redo log buffer、redo log file。innodb是支持事务的存储引擎，在事务提交时，必须先将该事务的所有日志写入到redo日志文件中，待事务的commit操作完成才算整个事务操作完成。在每次将redo log buffer写入redo log file后，都需要调用一次fsync操作，因为重做日志缓冲只是把内容先写入操作系统的缓冲系统中，并没有确保直接写入到磁盘上，所以必须进行一次fsync操作。因此，磁盘的性能在一定程度上也决定了事务提交的性能。InnoDB 存储引擎先将重做日志信息放入这个缓冲区,然后以一定频率将其刷新到重做日志文件。重做日志文件一般不需要设置得很大,因为在下列三种情况下重做日志缓冲中的内容会刷新到磁盘的重做日志文件中。额外的缓冲池
    • 　　Master Thread 每一秒将重做日志缓冲刷新到重做日志文件
    • 　　每个事物提交时会将重做日志缓冲刷新到重做日志文件
    • 　　当重做日志缓冲剩余空间小于1/2时,重做日志缓冲刷新到重做日志文件
  • 额外的缓冲池：在 InnoDB 存储引擎中，对一些数据结构本身的内存进行分配时，需要从额外的内存池中进行申请。例如: 分配了缓冲池,但是每个缓冲池中的帧缓冲还有对应的缓冲控制对象，这些对象记录以一些诸如 LRU, 锁,等待等信息,而这个对象的内存需要从额外的内存池中申请。

3、缓冲池的原理

上边已经介绍了缓冲池的概念以及缓冲池中的组成，这里介绍下缓冲池工作原理。

（1）怎么将磁盘上的页缓存到内存中的Buffer Pool中呢？

Buffer Pool其实是一片连续的内存空间，那么怎么将磁盘上的页缓存到内存中的Buffer Pool中呢？直接把需要缓存的页向Buffer Pool里一个一个往里怼么？不不不，为了更好的管理这些被缓存的页，InnoDB为每一个缓存页都创建了一些所谓的控制信息，这些控制信息包括该页所属的表空间编号、页号、页在Buffer Pool中的地址、一些锁信息以及LSN信息，当然还有一些别的控制信息。

每个缓存页对应的控制信息占用的内存大小是相同的，我们就把每个页对应的控制信息占用的一块内存称为一个控制块，控制块和缓存页是一一对应的，它们都被存放到 Buffer Pool 中，其中控制块被存放到 Buffer Pool 的前边，缓存页被存放到 Buffer Pool 后边，所以整个Buffer Pool对应的内存空间看起来就是这样的：

 
控制块和缓存页之间的那个碎片是个什么呢？每一个控制块都对应一个缓存页，那在分配足够多的控制块和缓存页后，可能剩余的那点儿空间不够一对控制块和缓存页的大小，自然就用不到喽，这个用不到的那点儿内存空间就被称为碎片了。当然，如果你把Buffer Pool的大小设置的刚刚好的话，也可能不会产生碎片。

（2）InnoDB存储引擎是怎么对缓冲池进行管理的？

空闲链表：

当我们最初启动MySQL服务器的时候，需要完成对Buffer Pool的初始化过程，就是分配Buffer Pool的内存空间，把它划分成若干对控制块和缓存页。但是此时并没有真实的磁盘页被缓存到Buffer Pool中（因为还没有用到），之后随着程序的运行，会不断的有磁盘上的页被缓存到Buffer Pool中，那么问题来了，从磁盘上读取一个页到Buffer Pool中的时候该放到哪个缓存页的位置呢？或者说怎么区分Buffer Pool中哪些缓存页是空闲的，哪些已经被使用了呢？我们最好在某个地方记录一下哪些页是可用的，我们可以把所有空闲的页包装成一个节点组成一个链表，这个链表也可以被称作Free链表（或者说空闲链表）。因为刚刚完成初始化的Buffer Pool中所有的缓存页都是空闲的，所以每一个缓存页都会被加入到Free链表中，假设该Buffer Pool中可容纳的缓存页数量为n，那增加了Free链表的效果图就是这样的：

 
 

从图中可以看出，我们为了管理好这个Free链表，特意为这个链表定义了一个控制信息，里边儿包含着链表的头节点地址，尾节点地址，以及当前链表中节点的数量等信息。我们在每个Free链表的节点中都记录了某个缓存页控制块的地址，而每个缓存页控制块都记录着对应的缓存页地址，所以相当于每个Free链表节点都对应一个空闲的缓存页。有了这个Free链表事儿就好办了，每当需要从磁盘中加载一个页到Buffer Pool中时，就从Free链表中取一个空闲的缓存页，并且把该缓存页对应的控制块的信息填上，然后把该缓存页对应的Free链表节点从链表中移除，表示该缓存页已经被使用了～

上面介绍了缓存池中的空闲链表，free list用来维护buffer pool的空闲缓存页的数据结构。

内存空间不足引起的问题？

简单地回顾Buffer Pool的工作机制。Buffer Pool两个最主要的功能：

  • 一个是加速读，加速读呢？ 就是当需要访问一个数据页面的时候，如果这个页面已经在缓存池中，那么就不再需要访问磁盘，直接从缓冲池中就能获取这个页面的内容。
  • 一个是加速写，加速写呢？就是当需要修改一个页面的时候，先将这个页面在缓冲池中进行修改，记下相关的重做日志，这个页面的修改就算已经完成了。至于这个被修改的页面什么时候真正刷新到磁盘，这个是后台刷新线程来完成的。

在实现上面两个功能的同时，需要考虑客观条件的限制，因为机器的内存大小是有限的，所以MySQL的InnoDB Buffer Pool的大小同样是有限的，如果需要缓存的页占用的内存大小超过了Buffer Pool大小，也就是Free链表中已经没有多余的空闲缓存页的时候岂不是很尴尬，发生了这样的事儿该咋办？当然是把某些旧的缓存页从Buffer Pool中移除，然后再把新的页放进来喽～ 那么问题来了，移除哪些缓存页呢？

LRU list

为了回答上边的问题，我们还需要回到我们设立Buffer Pool的初衷，我们就是想减少和磁盘的I/O交互，最好每次在访问某个页的时候它都已经被缓存到Buffer Pool中了。假设我们一共访问了n次页，那么被访问的页已经在缓存中的次数除以n就是所谓的缓存命中率，我们的期望就是让缓存命中率越高越好～

怎么提高缓存命中率呢？InnoDB Buffer Pool采用经典的LRU算法来进行页面淘汰，以提高缓存命中率。当Buffer Pool中不再有空闲的缓存页时，就需要淘汰掉部分最近很少使用的缓存页。不过，我们怎么知道哪些缓存页最近频繁使用，哪些最近很少使用呢？我们可以再创建一个链表，由于这个链表是为了按照最近最少使用的原则去淘汰缓存页的，所以这个链表可以被称为LRU链表（Least Recently Used）。当我们需要访问某个页时，可以这样处理LRU链表：

  • 如果该页不在Buffer Pool中，在把该页从磁盘加载到Buffer Pool中的缓存页时，就把该缓存页包装成节点塞到链表的头部。

  • 如果该页在Buffer Pool中，则直接把该页对应的LRU链表节点移动到链表的头部。

但是这样做会有一些性能上的问题，比如你的一次全表扫描或一次逻辑备份就把热数据给冲完了，就会导致缓冲池污染问题！Buffer Pool中的所有数据页都被换了一次血，其他查询语句在执行时又得执行一次从磁盘加载到Buffer Pool的操作，而这种全表扫描的语句执行的频率也不高，每次执行都要把Buffer Pool中的缓存页换一次血，这严重的影响到其他查询对 Buffer Pool 的使用，严重的降低了缓存命中率 ！

所以InnoDB存储引擎对传统的LRU算法做了一些优化，在InnoDB中加入了midpoint。新读到的页，虽然是最新访问的页，但并不是直接插入到LRU列表的首部，而是插入LRU列表的midpoint位置。这个算法称之为midpoint insertion stategy。默认配置插入到列表长度的5/8处。midpoint由参数innodb_old_blocks_pct控制。midpoint之前的列表称之为new列表，之后的列表称之为old列表。可以简单的将new列表中的页理解为最为活跃的热点数据。同时InnoDB存储引擎还引入了innodb_old_blocks_time来表示页读取到mid位置之后需要等待多久才会被加入到LRU列表的热端。可以通过设置该参数保证热点数据不轻易被刷出。

FLUSH链表

前面我们讲到页面更新是在缓存池中先进行的，那它就和磁盘上的页不一致了，这样的缓存页也被称为脏页（英文名：dirty page）。所以需要考虑这些被修改的页面什么时候刷新到磁盘？以什么样的顺序刷新到磁盘？当然，最简单的做法就是每发生一次修改就立即同步到磁盘上对应的页上，但是频繁的往磁盘中写数据会严重的影响程序的性能（毕竟磁盘慢的像乌龟一样）。所以每次修改缓存页后，我们并不着急立即把修改同步到磁盘上，而是在未来的某个时间点进行同步，由后台刷新线程依次刷新到磁盘，实现修改落地到磁盘。

但是如果不立即同步到磁盘的话，那之后再同步的时候我们怎么知道Buffer Pool中哪些页是脏页，哪些页从来没被修改过呢？总不能把所有的缓存页都同步到磁盘上吧，假如Buffer Pool被设置的很大，比方说300G，那一次性同步这么多数据岂不是要慢死！所以，我们不得不再创建一个存储脏页的链表，凡是在LRU链表中被修改过的页都需要加入这个链表中，因为这个链表中的页都是需要被刷新到磁盘上的，所以也叫FLUSH链表，有时候也会被简写为FLU链表。链表的构造和Free链表差不多，这就不赘述了。这里的脏页修改指的此页被加载进Buffer Pool后第一次被修改，只有第一次被修改时才需要加入FLUSH链表（代码中是根据Page头部的oldest_modification == 0来判断是否是第一次修改），如果这个页被再次修改就不会再放到FLUSH链表了，因为已经存在。需要注意的是，脏页数据实际还在LRU链表中，而FLUSH链表中的脏页记录只是通过指针指向LRU链表中的脏页。并且在FLUSH链表中的脏页是根据oldest_lsn（这个值表示这个页第一次被更改时的lsn号，对应值oldest_modification，每个页头部记录）进行排序刷新到磁盘的，值越小表示要最先被刷新，避免数据不一致。

注意：脏页既存在于LRU列表中，也存在与Flush列表中。LRU列表用来管理缓冲池中页的可用性，Flush列表用来管理将页刷新回磁盘，二者互不影响。

这三个重要列表（LRU list， free list，flush list）的关系可以用下图表示：


 
 

Free链表跟LRU链表的关系是相互流通的，页在这两个链表间来回置换。而FLUSH链表记录了脏页数据，是通过指针指向了LRU链表，所以图中FLUSH链表被LRU链表包裹。

4、INNODB重要特性

（1） 插入缓冲

一般情况下，主键是行唯一的标识符，通常应用程序中行记录的插入顺序是按照主键递增的顺序进行插入的，因此，插入聚集索引一般是顺序的，不需要磁盘的随机读取。因为，对于此类情况下的插入，速度还是非常快的。（如果主键类是UUID这样的类，那么插入和辅助索引一样，也是随机的。）

如果索引是非聚集的且不唯一，在进行插入操作时，数据的存放对于非聚集索引叶子节点的插入不是顺序的，这时需要离散地访问非聚集索引页，由于随机读取的存在而导致了插入操作性能下降。这是因为B+树的特性决定了非聚集索引插入的离散性。

Insert Buffer的设计，对于非聚集索引的插入和更新操作，不是每一次直接插入到索引页中，而是先判断插入非聚集索引页是否在缓冲池中，若存在，则直接插入，不存在，则先放入一个Insert Buffer对象中。数据库这个非聚集的索引已经插到叶子节点，而实际并没有，只是存放在另一个位置。然后再以一定的频率和情况进行Insert Buffer和辅助索引页子节点的merge（合并）操作，这时通常能将多个插入合并到一个操作中（因为在一个索引页中），这就大大提高了对于非聚集索引插入的性能。

需要满足的两个条件：

  • 索引是辅助索引；
  • 索引不是唯一的。

辅助索引不能是唯一的，因为在插入缓冲时，数据库并不去查找索引页来判断插入的记录的唯一性。如果去查找肯定又会有离散读取的情况发生，从而导致Insert Buffer失去了意义。

（2）两次写

如果说插入缓冲是为了提高写性能的话，那么两次写是为了提高可靠性。介绍两次写之前，说一下部分写失效：

场景：当数据库正在从内存向磁盘写一个数据页时，数据库宕机，从而导致这个页只写了部分数据，这就是部分写失效，它会导致数据丢失。这时是无法通过重做日志恢复的，因为重做日志记录的是对页的物理修改，如果页本身已经损坏，重做日志也无能为力。

从上面分析我们知道，在部分写失效的情况下，我们在应用重做日志之前，需要原始页的一个副本，两次写就是为了解决这个问题，下面是它的原理图：

 
两次写需要额外添加两个部分：
  • 内存中的两次写缓冲（doublewrite buffer），大小为2MB
  • 磁盘上共享表空间中连续的128页，大小也为2MB

其原理是这样的：
1）当刷新缓冲池脏页时，并不直接写到数据文件中，而是先拷贝至内存中的两次写缓冲区。
2）接着从两次写缓冲区分两次写入磁盘共享表空间中，每次写入1MB
3）待第2步完成后，再将两次写缓冲区写入数据文件

这样就可以解决上文提到的部分写失效的问题，因为在磁盘共享表空间中已有数据页副本拷贝，如果数据库在页写入数据文件的过程中宕机，在实例恢复时，可以从共享表空间中找到该页副本，将其拷贝覆盖原有的数据页，再应用重做日志即可。其中第2步是额外的性能开销，但由于磁盘共享表空间是连续的，因此开销不是很大。可以通过参数skip_innodb_doublewrite禁用两次写功能，默认是开启的，强烈建议开启该功能。

（3） 自适应哈希索引

哈希是一种非常快的查找方法，在一般情况时间复杂度为O(1)。而B+树的查找次数，取决于B+树的高度，在生成环境中，B+树的高度一般为3-4层，不需要查询3-4次。InnoDB存储引擎会监控对表上各索引页的查询。如果观察到建立哈希索引可以提升速度，就会建立哈希索引，称之为自适应哈希索引(Adaptive Hash Index, AHI)。AHI是通过缓冲池的B+树页构造而来的。因此建立的速度非常快，且不要对整张表构建哈希索引。InnoDB存储引擎会自动根据访问的频率和模式来自动的为某些热点页建立哈希索引。AHI有一个要求，对这个页的连续访问模式(查询条件)必须一样的。例如联合索引(a,b)其访问模式可以有以下情况:

  • WHERE a=XXX;

  • WHERE a=xxx AND b=xxx。

若交替进行上述两次查询，InnoDB存储引擎不会对该页构造AHI。根据官方文档显示，启用AHI后，读取和写入的速度可以提高2倍，负责索引的链接操作性能可以提高5倍。其设计思想是数据库自由化的，无需DBA对数据库进行人为调整。

（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需要操作系统提供支持。MySQL可以通过参数innodb_use_native_aio来决定是否启用Native AIO。在InnoDB存储引擎中，read ahead方式的读取都是通过AIO完成，脏页的刷新，也是通过AIO完成。

（5）刷新领接页

当刷新一个脏页时，innodb会检测该页所在区（extent）的所有页，如果是脏页，那么一起进行刷新。这样做，通过AIO将多个IO写入操作合并为一个IO操作。该工作机制在传统机械磁盘下有显著优势。但是需要考虑下面两个问题：

  • 是不是将不怎么脏的页进行写入，而该页之后又会很快变成脏页？
  • 固态硬盘有很高IOPS，是否还需要这个特性？

为此InnoDB存储引擎1.2.x版本开始提供参数innodb_flush_neighbors来决定是否启用。对于传统机械硬盘建议使用，而对于固态硬盘可以关闭。

5、CheckPoint技术

CheckPoint技术是用来解决如下几个问题：

  • 缩短数据库恢复时间：缩短数据库恢复时间，重做日志中记录了的checkpoint的位置，这个点之前的页已经刷新回磁盘，只需要对checkpoint之后的重做日志进行恢复。这样就大大缩短了恢复时间

  • 缓冲池不够用时，将脏页刷新到磁盘：缓冲池不够用时，根据LRU算法，溢出最近最少使用的页，如果页为脏页，强制执行checkpoint，将脏页刷新回磁盘。

  • 重做日志不可用时，刷新脏页：重做日志不可用，是指重做日志的这部分不可以被覆盖，因为由于重做日志的设计是循环使用的，这部分对应的数据还未刷新到磁盘上。数据库恢复时，如果不需要这部分日志即可被覆盖；如果需要必须强制执行checkpoint将缓冲池中的页至少刷新到当前重做日志的位置。 

InnoDB存储引擎内部，两种checkpoint，分别为: 

  • Sharp Checkpoint

  • Fuzzy Checkpoint

什么时间触发checkpoint？ 

（1）Sharp Checkpoint发生在数据库关闭时，将所有的脏页都刷新回磁盘，这是默认的工作方式，即参数：innodb_fast_shutdown=1。不适用于数据库运行时的刷新。

（2）在数据库运行时，InnoDB存储引擎内部采用Fuzzy Checkpoint，只刷新一部分脏页。 

几种发生Fuzzy Checkpoint的情况？

（1）MasterThread Checkpoint
异步刷新，每秒或每10秒从缓冲池脏页列表刷新一定比例的页回磁盘。

（2）FLUSH_LRU_LIST Checkpoint
InnoDB存储引擎需要保证LRU列表中差不多有100个空闲页可供使用。在InnoDB 1.2.x版本之前，用户查询线程会检查LRU列表是否有足够的空间操作。如果没有，根据LRU算法，溢出LRU列表尾端的页，如果这些页有脏页，需要进行checkpoint。因此叫：flush_lru_list checkpoint。InnoDB 1.2.x开始，这个检查放在了单独的进程（Page Cleaner）中进行。

设置参数：innodb_lru_scan_dept：控制LRU列表中可用页的数量，该值默认1024

（3）Async/Sync Flush Checkpoint
 指重做日志不可用的情况，需要强制刷新页回磁盘。

（4）Dirty Page too much Checkpoint

即脏页太多，强制checkpoint.保证缓冲池有足够可用的页。参数设置：innodb_max_dirty_pages_pct = 75 表示：当缓冲池中脏页的数量占75%时，强制checkpoint。

四、MyISAM 和 InnoBD如何选择

  • MyISAM不支持事务，读取速度快，支持全文搜索，如果应用中需要执行大量的SELECT查询，那么MyISAM是更好的选择。
  • InnoDB支持ACID事务，如果有频繁的插入、更新操作，并发量大，则应该使用InnoDB
【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/qtiger/p/14081545.html