2019-09-19 15:52 已编辑首都师范大学 Java

关注

Mysql笔记系列一MySQL体系结构和InnoDB存储引擎

名词解释：

“数据库”和”数据库实例”

数据库：更侧重于指的是存储在物理磁盘上的文件的集合，例如frm,myd,myi,ibd结尾的文件。

数据库实例：运行在内存中，正在执行的一个进程，用来操作数据库文件。

可以通过理解进程和程序之间的关系来类比的理解，进程就是动态的运行在内存中的程序，它的实体就是存储在磁盘上的文件，而程序则指的是静态的，即使用特定的语言编写的可编译的文件，侧重的是文件。实例就是数据库进程，可以对数据库进行操作的集合，数据库就是在实例上操作在磁盘上的映射，最终的改变通过文件的方式存储在磁盘上。对应关系：在非集群情况下，一个数据库对应一个数据库实例，在集群情况下，一个数据库可被多个实例使用。

启动数据库实例

MYSQL数据库会去读取配置文件，根据配置文件的属性值来启动数据库实例，MYSQL数据库实例启动时，照/etc/my.cnf->/etc/mysql/my.cnf->/usr/local/mysql/etc/my.cnf->~/.my.cnf的顺序读取配置文件，如果属性相同，则按照最后一个配置文件的属性值为准，如果没有配置文件，则MYSQL会按照编译时的默认参数设置启动实例。Oracle中，如果没有参数文件，则启动时会提示找不到该参数文件，启动失败。

Window平台，配置文件的后缀以.cnf，.ini.Linux环境下，配置文件一般放在/etc/my.cnf下。配置文件中有一个datadir参数，该参数制定了数据库所在的路径，Linux下，datadir默认为/usr/local/mysql/data。

存储引擎

数据库的核心是存储引擎，存储引擎是一个抽象的概念，可以理解成一个程序，用来定义如何进行存储的程序，数据库有多种存储引擎，每种存储引擎都针对不同的情况，各有千秋。Mysql是开源的，可以根据Mysql预定义的存储引擎接口编写自己的存储引擎，也可以修改源码，实现更多的特性。存储引擎可分为Mysql官方存储引擎和第三方存储引擎。

⑴InnoDB存储引擎

面向在线事务处理（OLTP），特点是行锁设计，支持事务，外键，非锁定读（即默认情况下读取操作不会产生锁）。MVCC（多版本并发控制）获取高并发性，还有：二次写，插入缓冲，自适应哈希索引，预读等高性能和高可用的功能。对于表的存储，InnoDB存储引擎采用聚集的方式，每张表的存储按照主键的顺序存放，如果没有显式的在表定义时指定主键，InnoDB存储引擎会为每一行生成一个6字节的ROWID，并以此作为主键。

⑵MyISAM存储引擎

表锁，全局索引，不支持事务，MyISAM存储引擎由MYD和MYI组成，MYD用来存放数据文件，MYI用来存放索引文件。

⑶NDB存储引擎

集群存储引擎，数据全放在内存中，主键查找速度极快。

⑷Memory存储引擎

将表中的数据存放在内存中，如果数据库重启，崩溃，则表中数据消失，默认使用哈希索引。

等等等，可通过在mysql中使用 show engines；查询mysql支持的存储引擎。

数据库的连接方式

数据库的连接就是连接进程和MySQL数据库实例（即mysql server)进行通信，即进程通信，进程通信的方式有以下几个：

⑴TCP/IP

TCP/IP协议是一个网络连接协议，用来使网络上的两个主机建立安全可靠的连接，因此，可以在TCP/IP连接上建立一个基于网络的连接请求，一般情况下，客户端和服务端不在一个主机上，但是因为他是基于网络的，所以即使在一个主机上，也可以。

C:\Users\liumin>mysql -u root -p

⑵Unix套接字

Unix套接字不是一个协议，所以只能在Mysql客户端和数据库实例在同一台服务器的情况下使用，可以在配置文件中指定套接字文件的路径，eg:-socket=/tmp/mysql.sock。当数据库实例启动后，可以通过mysql -u username -S pathofsocket来进行连接。

C:\Users\liumin>mysql -u root -S /tmp/mysql.sock

还有命名管道和共享内存，但是现在常用的连接方式是建立在TCP/IP协议上的数据库连接。

InnoDB存储引擎

InnoDB有Innobase Oy公司开发，是一个支持事务，行锁，外键，MVCC(多版本并发控制）提供非锁定读（即在读取操作中不适用锁），还有插入缓存，二次写，自适应性哈希等特性。

InnoDB的体系架构

首先存储引擎是一个程序，程序自然有很多分工和与之对应的线程，因此在内存中，系统会为存储引擎分配一个内存区域（内存池）。内存池中有多个后台线程，后台线程的主要任务是负责刷新内存池中的数据，保证缓冲池中的内存缓冲的是最近的数据。

后台线程：

可以通过 show engine innodb status\G;命令来查看innodb存储引擎的状态：(内容有所删减，我只保留我们目前需要关注的内容）

mysql> show engine innodb status\G;
*************************** 1. row ***************************
  Type: InnoDB
  Name:
Status:
...
--------
FILE I/O
--------
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)
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)
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] ,
 ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
1139 OS file reads, 447714 OS file writes, 21607 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
...

默认情况，InnoDB存储引擎的后台线程有7个，4个I/O thrads（insert buffer thread,log thread,read thread,write thread),1个master thread,1个lock thread,1个错误监控 thread.

内存划分：

InnoDB的内存由以下几个部分组成：缓冲池，日志缓冲池，额外的内存池。可以通过配置文件中的参数innodb_buffer_pool_size和innodb_log_buffer_size的大小配置。

通过 show engine innodb status\G;可以看到缓冲池的状态。

BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 137363456
Dictionary memory allocated 436363
Buffer pool size   8192
Free buffers       4012
Database pages     4165
Old database pages 1517
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 3, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1114, created 3051, written 5858
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
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: 4165, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]

buffer pool size 表示缓冲帧的数量，每个帧16K,即换算下来为：128M，Free buffers 为当前空闲的缓冲帧，Database pages表示已经使用的缓冲帧，Modified db page表示脏页的数量。

也可以通过 show variables like 'innodb_buffer_pool_size'\G;来查看特定区域的配置情况，其他的同理。

mysql> show variables like 'innodb_buffer_pool_size'\G;
*************************** 1. row ***************************
Variable_name: innodb_buffer_pool_size
        Value: 134217728
1 row in set, 1 warning (0.00 sec)

⑴缓冲池

缓冲池是占用内存最大的一部分，用来存放各种数据的缓存。InnoDB的存储引擎的工作方式是将数据库文件按照页（每页16k)读取到缓冲池，然后按最近最少使用的算法来保留住缓冲池中的缓冲数据，数据库的修改也是最先在缓冲池中的页，然后按照一定的频率将缓冲池中的脏页刷新到文件。缓冲池的数据类型有：索引页，数据页，undo页，插入缓冲，自适应哈希索引，InnoDB存储的锁信息，数据字典等。

⑵日志缓冲

日志缓冲即先将日志信息放入这个区域，然后按照一定的频率刷新到重做日志文件。频率不需要设置太大，因为一般情况下每一秒中就会重做日志缓冲刷新。

⑶额外内存池

对于一些数据结构的内存分配问题，需要从额外的内存池中申请，当该区域的内存不足时，会从缓冲池中申请。

关键特性：

⑴插入缓冲

插入缓冲，和数据页一样，是物理页的一个组成部分。应用程序中行记录的插入顺序是按照主键递增的顺序进行插入的，则插入聚集索引（数据库表行中数据的物理顺序与键值的逻辑顺序相同。一个表只能有一个聚集索引）一般是顺序的，不需要磁盘的随机读取，即当执行插入操作时，主键列会自动增长，页中的行记录按照主键行顺序存放，一般情况下，不需要随机读取另一个页执行记录的存放，因此，插入操作能迅速完成。但是普遍情况下，一张表有多个非聚集的辅助索引，这个时候进行插入操作时，数据的存放还是按照主键的执行顺序存放，但是对于非聚集索引，叶子节点的插入不再是顺序的。

InnoDB为了解决这个问题，设计了插入缓存，对于非聚集索引的插入或更新操作，不是每一次直接插入索引页中，而是先判断插入的非聚集索引页是否在缓冲池中，如果在直接插入，如果不在，先放入一个插入缓冲区中，然后按照一定的频率执行插入缓存和非聚集索引页子节点的合并操作，这时通常能够将多个插入合并到一个操作中，大大提高非聚集索引的插入性能。但是插入缓冲的使用需要两个条件：1.索引是辅助索引2.索引不是唯一。

⑵两次写

当数据库发生宕机时，可能发生数据库正在写一个页面，但这个页面有只写了一部分，我们称为部分写失效，又由于这个时候页本身已经损坏，所以进行重做日志记录是不行的，因为重做日志记录是对页的物理操作。因此设计了两次写，即在应用重做日志前，我们需要一个页的副本，当写入失效的时，先通过页的副本来还原该页，在进行重做。

doublewrite由两部分组成：

一是内存中的doublewrite buffer，大小为2MB，另一部分是物理磁盘上共享表空间中连续的128个页，即两个区，大小同样是2MB,当缓冲池的脏页刷新时，即进行修改操作时，并不直接写到磁盘，而是通过memcpy函数将脏页先拷贝到内存中的doublewrite buffer，之后通过doublewrite bufffer分两次，每次写入1MB到共享表空间的物理磁盘上，然后调用fsync函数，同步到磁盘，因为doublewrite页是连续的，所以这个过程也是顺序写的，并不会造成很多资源的使用，在完成doublewrite页的写入后，再将doublewrite buffer的页写入各个表中间文件中，此时的写入则是离散的。

如果系统在将页写入磁盘的过程中崩溃了，则在恢复过程中，InnoDB存储引擎可以从共享表空间中的doublewrite中找到该页的一个副本，然后将其拷贝到表空间文件，在重用重做日志。

⑶自适应哈希

哈希的查找时间复杂度为O(1)，InnoDB存储引擎会监控对表上索引的查找，如果观察到建立哈希索引可以带来速度的提升，则建立哈希索引，称为自适应，自适应哈希通过B+树构造，因此建立的速度很快，而且不需要将整个表都建哈希索引，InnoDB存储引擎会根据访问的频率和模式来为某些页建立哈希索引。

启动，关闭和恢复

InnoDB存储引擎的启动，关闭指的是在MYSQL实例的启动过程中对InnoDB表存储的处理过程，在关闭的时候，参数innodb_fast_shutdonw影响着存储引擎为InnoDB的行为，参数取值为0，1，2。0代表Mysql关闭时，需要完成所有的full purge和merge insert buffer操作。1是默认值，表示不需要上述操作，但是在缓冲池中的数据脏页还是要刷新到磁盘上。2表示不完成full purge 和merge into buffer操作，也不将缓冲池的数据脏页写回到磁盘，而是将日志都写入日志文件，这样不会又任何事务的丢失，但Mysql数据库下次启动时，会执行恢复操作。

参考《MySQL技术内幕 InnoDB存储引擎》姜承尧著