《MySQL技术内幕：InnoDB存储引擎 第二版》读书笔记

《MySQL技术内幕：InnoDB存储引擎 第二版》读书笔记

第一章 MySQL体系结构和存储引擎

MySQL被设计为一个可移植的数据库，几乎能够在所有系统上运行，如Linux 、windows等等，虽然各平台在底层（如线程）的实现方面各有不同，但是MySQL基本能保证在各平台上的物理体系结构的一致性。

1.定义数据库和实例

1.数据库：物理操作系统文件或其他形式文件的集合，在MySQL数据库中，数据库文件可以是frm 、MYD 、MYI 、ibd结尾的文件。当使用NDB引擎时，数据库中的文件可能不是操作系统上的文件，而是存放于内存中的文件，但定义仍不变。
2.实例：由后台线程及一个共享内存区组成，共享内存区可以被后台运行的线程共享，需要牢记 ，数据库实例才是真正操作数据库文件的。
注意：

1. MySQL数据库中实例和数据库通常是一一对应的（即一个实例对应一个数据库），但是在集群的情况下，可能存在一个数据库被多个实例使用的情况。
2. MySQL数据库是单进程多线程架构的，与Oracle的多进程架构不同（windows下，Oracle也是单进程多线程架构）。也就是说，MySQL数据库实例在系统上的表现就是一个进程。
3. 看例子，课本P14 - P16。

2.MySQL体系结构

(1).进一步讲解数据库与实例

1.数据库是文件集合。
2.数据库实例是是程序，包括数据库定义 、数据查询 、数据维护 、数据库运行控制等都是在数据库实例下进行的应用程序只有通过数据库实例才能和数据库打交道。
3.数据库是由一个个文件组成的，要对这些文件执行诸如SELECT 、INSERT 、DELETE 和UPDATE 等数据库操作不能通过简单地操作（修改）文件来更改数据库的内容，需要通过数据库实例来完成对数据库的操作。
4.所以用户把Oracle 、MySQL简单理解为数据库其实是有误的，但实际开发中并没有强调这些区别。

(2).MySQL体系结构图

注：这里参考了别人的文章MySQL体系结构详解

1.Connectors：连接者，不同语言的代码程序与MySQL的交互（SQL交互）。
2.连接池组件：管理 、缓冲用户的连接。
3.管理服务和工具组件：系统管理和控制工具，比如备份恢复 、MySQL复制 、集群等等。
4.SQL接口组件：接收用户的SQL命令，并返回用户的操作（比如 查询操作）的结果。
5.查询解析器组件：SQL语句传递到解析器的时候会被解析器验证和解析（比如 解析权限 、语法结构等等）。
6.优化器组件：SQL语句在查询之前会使用查询优化器进行优化，比如
SELECT id, name FROM user WHERE age = 40;

1. 这个select 查询先根据where 语句进行选取，而不是先将表全部查询出来以后再进行age过滤 ；
2. 这个select查询先根据id和name进行属性投影，而不是将属性全部取出以后再进行过滤；
3. 将这两个查询条件联接起来生成最终查询结果。

7.缓冲组件（缓存组件）：如果查询到缓存中存在想要查询的结果，那么可以直接去缓存中取出数据。
8.插件式存储引擎（最重要的成员，MySQL独有的概念）：存储引擎说白了就是管理操作数据的（比如说存储数据 、查询 、更新等等）一种方式，因为关系数据库中数据是以表的形式存储的，所以存储引擎也可以称作为表类型（即存储和操作此表的类型），3-7这几个组件的底层物理结构都是有存储引擎来实现的，存储引擎是基于表的，而不是基于数据库的。
9.物理文件：日志文件，数据文件，配置文件，pid文件,socket文件等。

3.MySQL存储引擎

在Oracle 和 SQL Server 等数据库中，所有数据存储管理机制都是一样的。而MySQL数据库提供了多种存储引擎。
用户可以根据不同的需求为数据库表选择不同的存储引擎，用户也可以根据自己的需要编写自己的存储引擎。
甚至一个库中不同的表使用不同的存储引擎也是允许的。

(1).InnoDB存储引擎

1.InnoDB存储引擎支持事务，其设计目标主要面向在线事务处理（OLTP）的应用。其特点就是行锁设计 、支持外键，并支持类似于Oracle的 非锁定读，即默认读取操作不会加锁。从MySQL 5.5.8版本开始，InnoDB存储引擎成为默认的存储引擎。
2.InnoDB存储引擎将数据存放在一个逻辑的表空间里，这个表空间就像黑盒一样由InnoDB存储引擎自身进行管理。
3.InnoDB通过多版本并发控制（MVCC）来获得高并发性，并且实现了SQL的四个隔离级别，默认级别是 REPEATABLE 级别。同时使用 next-key locking的策略来避免幻读。此外，InnoDB还提供了插入缓冲（insert buffer） 、二次写 （double write）、自适应哈希索引 、预读（read ahead）等高性能高可用的功能。
4.InnoDB采用聚集的方式来存储表中的数据，所以每张表中的每行数据都会按照主键的顺序进行存放，如果没有在表中定义主键，InnoDB存储引擎会自动为每一行生成一个6字节的ROWID，并把它作为当前行的主键值。
5.InnoDB是MySQL数据库最常用的存储引擎，它具有高性能 、高可用性和高可扩展性。

(2).MyISAM存储引擎

1.MyISAM存储引擎不支持事务 、表锁设计 、外键等等，支持全文索引，因此访问速度快。主要面向一些在线访问处理（OLAP）的数据库应用.
2.MyISAM存储引擎的缓冲池只缓存索引文件，所以不缓存数据文件。
3.MyISAM存储引擎由MYD和MYI组成，MYD用来存放数据文件，MYI用来存放索引文件。
4.对事务完整性没有要求并以访问为主的应用适合使用该存储引擎。

(3).NDB存储引擎

1.它是一个集群存储引擎，类似于Oracle的RAC集群，不过与RAC share everything架构不同的是，其结构是 share nothing的集群架构，因此具有更高的可用性。
2.NDB的特点是他将数据存储在内存中（可以将非索引数据放在磁盘），因此主键查找速度特别快；但是要注意的是，NDB存储引擎的连接操作是在数据库层完成的，而非在存储引擎层，复杂的连接操作需要巨大的网络开销，所以查询速度很慢。

(4).Memory存储引擎

1.Memory存储引擎将数据存放在内存中，如果数据库重启或者崩溃，数据将丢失，所以不安全，适合用于存储临时数据的临时表.
2.存储引擎默认使用哈希索引而不是 B+树 索引，查找速度非常快。
3.使用上有一定限制，比如，只支持表锁，并发性能差，不支持TEXT 、BLOD 列数据类型，变长字段VARCHAR 使用效果和定长字段 CHAR 相同。
4.MySQL数据库使用Memory存储引擎作为临时表存放查询的中间结果集，如果中间结果集大于Memory存储引擎表的容量设置，又或者中间结果集中含有TEXT 或BLOD 列类型字段，则MySQL数据库会把其转换到MyISAM存储引擎表进而存放到磁盘中。但是MyISAM不缓存数据文件，因此这是产生的临时表的性能对于查询会有损失。

(5).Archive存储引擎

1.Archive存储引擎只支持 INSERT 、SELECT 操作，MySQL5.1版本起支持索引。
2.使用zlib算法将数据行进行压缩后存储，压缩比一般可达 1 : 10。
3.适合用来存储归档数据，如日志信息
4.使用行锁来实现高并发的插入操作，但本身并不是事务安全的存储引擎，其设计目的主要是提供快速的插入和压缩功能。

(6).Federated存储引擎

1.不是存放数据用的，而是只是指向一台远程MySQL数据库服务器上的表。
2.类似SQL Server的链接服务器和Oracle的透明网关，不同的是，Federated存储引擎只支持lMySQL数据库表，不支持异构数据库表。

(7).Maria存储引擎

1.是MyISAM存储引擎的后续版本，开发者是MySQL创始人之一，设计目的是为了替换MyISAM存储引擎，从而成为MySQL的默认存储引擎。
2.支持缓存数据和索引文件，应用行锁设计，提供MVCC功能，支持事务和非事务的安全选项，更好的BLOD字符类型处理性能。

(8).BlackHole存储引擎

黑洞存储引擎，写入的任何数据都会消失，可以应用于主备复制中的分发主库。

(9).其他存储引擎

还有Merge 、CSV 、Sphinx 、Infobright等存储引擎，他们都有各自的应用场景。

4.各存储引擎之间的比较

1.存储引擎是MySQL体系结构的核心。
2.数据库与文件系统的最大区别在于，数据库支持事务

3.看课本例子，P24 - P26。
4.虽然MySQL有许多的存储引擎，但是它们之间不存在优劣性的差异，用户应该根据不同的应用选择合适自己的存储引擎。

第二章 InnoDB存储引擎

InnoDB存储引擎是事务安全的MySQL存储引擎，是OLTP应用中核心表的首选存储引擎。

1.InnoDB存储引擎概述

该引擎是第一个完全支持ACID 事务的MySQL存储引擎，其特点就是支持行锁设计 、支持外键 、支持MVCC 、提供一致性非锁定读同时被设计来最有效地利用和使用内存和CPU。

2.InnoDB存储引擎的版本

多滚回段：可以提高支持的并发事务数量。

3.InnoDB体系架构

InnoDB存储引擎有多个内存块，可以认为这些内存块组成了一个大的内存池，内存池负责：
1.维护所有进程和线程需要访问的多个内部数据结构。
2.缓存磁盘上的数据，方便快速地读取，同时在磁盘文件的数据修改之前在内存池缓存。
3.重做日志缓冲等等。
4.后台线程主要是为了刷新内存池中的数据，保证缓冲池的内存缓存是最近的数据。此外还将修改的数据文件刷新到磁盘文件，保证在数据库发生异常的情况下InnoDB能恢复正常的运行状态。

(1).后台线程

InnoDB存储引擎是一个多线程的模型，其后台运行着多个后台线程，负责着不同的任务：
1.Master Thread：InnoDB存储引擎的核心线程，负责将缓冲池中的数据刷新到磁盘，保证数据的一致性，包括脏页的刷新 、合并插入缓冲池 、UNDO页的回收等等。
2.IO Thread：InnoDB存储引擎使用大量的AIO 来处理写IO请求，极大地提高了数据库的性能，该线程主要负责这些IO请求的回调处理。（即该线程负责调用AIO）

3.Purge Thread：事务提交后，其所使用的的undolog可能不再需要，这时就需要该线程来回收已经使用并分配的undo 页。
4.Page Cleaner Thead：将脏页的刷新操作放入到一个单独的线程中完成；目的是为了减轻Master Thread的工作及对于用户查询线程的阻塞，进一步提高InnoDB存储引擎额性能。

(2).内存

1.缓冲池：
(1). InnoDB存储引擎是基于磁盘存储的，并将其中的记录按照页的方式进行管理，因此将其视为基于磁盘的数据库系统。
(2). 缓冲池简单来说就是一块内存区域，通过内存的速度来弥补因为磁盘速度较慢对于数据库性能的影响。在数据库中进行读取页的操作时，首先从磁盘中读取到页然后存放到缓冲池中，这个过程称为将页 FIX 到缓冲池中，下一次再读相同的页时，首先先判断该页是否在缓冲池中，若存在，则该页在缓冲池中被命中，直接读取该页，否则读取磁盘上的页。对于页的修改操作，首先修改在缓冲池中的页，然后再以一定的频率刷新到磁盘上，需要注意的是，页从缓冲池中刷新回磁盘并不是每一次页的更新都会执行，而是通过一种 Checkpoint 的机制刷新回磁盘。
(3). 缓冲池的数据页类型有：索引页 、数据页 、undo页 、插入缓冲(insert buffer) 、自适应哈希索引 、InnoDB存储的锁信息 、数据字典信息等等。而索引页 和 数据页占了很大一部分。InnoDB内存中除了缓冲池还有其他缓冲，但缓冲池占很大一部分。
(4). InnoDB从1.0.x版本开始，允许有多个缓冲实例，每个页根据哈希值分配到不同的缓冲池实例中，这样做的好处就是减少数据库内部的竞争，增加数据库的并发处理能力。
(5). 看例子 P37 - P38。

(2).LRU List 、Free List 和 Flush List

InnoDB利用他们来管理内存池
1.LRU List：
(1).数据库缓冲池是通过LRU（最少最近使用）算法进行管理的，即最频繁使用的页在LRU列表的最前端，最少使用的页在列表的最后端，当缓冲池不能存放读取到的最新的页时，就会释放掉列表尾端的页。
(2). InnoDB存储引擎中，缓冲池中页的大小默认为16KB，同样使用LRU算法来管理，但不同于传统LRU算法（对LRU算法做了一些优化），InnoDB存储引擎的LRU算法还加入了 midpoint 位置，新读取到的页不是放入列表前端，而是插入到 midpoint 位置 (一般该位置位于距离前端 5/8的位置，midpoint之后的列表叫 old 列表，之前的叫 new 列表，new 列表中的页是最为活跃的热点数据)，这个位置可以用 innodb_old_blocks_pct 来控制（如：innodb_old_blocks_pct = 20 表示插入到距离末端 20% 的位置），这种算法叫做 midpoint insertion strategy。
(3). 之所以不采用传统的LRU算法直接把读取到的页放在LRU列表首部，是因为若直接将读取到的页放到LRU首部，那么某些SQL操作可能会使缓冲池中的页被刷新出(这里指可能会导致缓冲池中那些频繁使用的页被释放掉)，从而影响缓冲池的效率。这些SQL操作常见的有索引或数据的扫描操作，这些操作一般需要访问表中的多个页甚至是所有页，但这些页只是在这次访问中所需要的，并不是常用的活跃数据。所以如果这些页插入列表首部，很大可能会刷新掉活跃的数据页，导致下次访问这些活跃数据页时，InnoDB存储引擎不得不再去访问磁盘。
(4). 参数innodb_old_blocks_time 用于表示 页读取到mid位置后需要等待多久才会被加入到LRU列表的热端。

2.Free List
(1). LRU列表用来管理已经读取的页，但当数据库刚启动时，LRU列表是空的，既没有任何页（页是一种双向链表结构，用来存放数据），这时页都存放在Free列表中。当缓冲池分页时，首先从Free列表中查找是否有空闲的页，有则将该页从Free列表中删除，放入到LRU列表中，否则根据LRU算法，删除LRU列表末端的页，将该内存空间分配给新的页。
(2). 当页从LRU列表的old部分加入到new部分时，此操作称为 page made young，如果因为 innodb_old_blocks_time 的设置而导致页没有从old部分移动到new部分的操作称为 page not made young。
(3). 看例子 P40 - P41.
(4). 缓冲池中的页还可能分配给了自适应哈希索引，Lock信息，Insertion Buffer等页，而这些页是不需要LRU算法来维护的，所以要知道，缓冲池中页的数量是大于等于Free列表中页的数量与LRU列表中页的数量之和的。
(5). Buffer pool hit rate 表示缓冲的命中率，通过该值应该不小于 95%，若发生命中率小于 95%的情况，用户需要观察是否是由于全表扫描引起的LRU列表污染问题。
(6). 通过information_schema数据库的表INNODB_BUFFER_POOL_STATS 来观察缓冲池的运行状态，例子 P41。
(7). 通过表INNODB_BUFFER_PAGE_LRU来观察每个LRU列表中每一个页的具体信息。
(8). InnoDB存储引擎还支持压缩页的功能，即将原来16KB的页压缩为1KB 、2KB 、4KB 、8KB。对于非16KB的页，有 unzip_LRU列表来管理。
(9). unzip_LRU列表从缓冲池中分配内存的步骤：首先在 unzip_LRU列表中对不同压缩页大小的页进行分别管理；其次通过伙伴算法进行内存分配。

3.Flush List
(1). 在LRU列表中的页被修改后，称该页为脏页（dirty page），即缓冲池中的页和磁盘上的页的数据产生了不一致，这时需要通过CHECKPOINT机制将脏页刷新回磁盘。Flush列表中的页就是脏页。脏页及存在于LRU列表，也存在于Flush列表。但LRU列表是用来管理缓冲池中页的可用性，而Flush列表是将脏页刷新回磁盘，两者互不影响。
(2). 因为脏页也属于LRU列表，所以可以通过INNODB_BUFFER_PAGE_LRU来查看，但是需要添加一个OLDEST_MODIFICATION大于0的SQL查询条件，TABLE_NAME为NULL的表示该页属于系统表空间（表空间：是数据库的逻辑划分，主要用来存放表）
(3). 看例子 P43

(3).重做日志缓冲

1.InnoDB内存除了缓冲池，还有重做日志缓冲。InnoDB存储引擎首先将重做日志信息放入到该缓冲区，然后按一定的频率刷新到重做日志文件。
2.重做日志缓冲一般不需要设置很大，用户只要保证每秒产生的日志量在缓冲大小之内即可。缓冲大小可以通过参数 innodb_log_buffer_size来控制，一般设置为8MB。

三种会将重做日志缓冲中的内容刷新到外部磁盘重做日志文件中的情况：
(1). Master Thread 每一秒会将重做日志缓冲刷新到重做日志文件中；
(2). 每个事务提交时，会将重做日志缓冲刷新到重做日志文件中；
(3). 当重做日志缓冲池的剩余空间小于 1/2时，会将重做日志缓冲刷新到重做日志文件中。

(4).额外的内存池

1.InnoDB存储引擎通过内存堆(heap)进行内存管理。
2.在对一些数据结构本身的内存进行分配时，需要从额外的内存池中进行申请，当该区域(即额外的内存池)的内存不够时，会从缓冲池进行申请。所以当申请了很大的InnoDB缓冲池时，应考虑相应地增加该区域的值。

4.Checkpoint 技术