读书笔记之MySQL技术内幕

前言

本文内容基本摘抄自《MySQL技术内幕 InnoDB存储引擎》，以供复习之用，没有多少参考价值。想要更详细了解请参考原书。

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

数据库是物理操作系统文件或其他形式文件类型的集合。MySQL数据库实例由后台线程以及一个共享内存区组成。MySQL是一个单进程多线程的架构的数据库

MySQL数据库区别于其他数据库的特点是插件式的表存储引擎：

InnoDB存储引擎：支持事务，主要面向在线事务处理的应用。特点是行锁设计、支持外键，并支持类似于Oracle的非锁定读，即默认读取操作不会产生锁。InnoDB通过使用多版本并发控制（MVCC）来获得高并发性，并且实现SQL标准的4种隔离级别，默认REPEATABLE。同时，使用next-key locking策略来避免幻读的产生。除此之外，InnoDB还提供了插入缓冲、二次写、自适应哈希索引、预读等功能。

MyISAM存储引擎：不支持事务、表锁设计，支持全文索引，主要面向在线分析处理的应用。MyISAM存储引擎的另一个与众不同的地方是它的缓冲池只缓存索引文件，而不缓冲数据文件，本身不支持外键，对于外键的定义只是起到注释的作用。

连接MySQL的几种方式：TCP/IP、命名管道和共享内存、UNIX域套接字。

第二章.InnoDB存储引擎

InnoDB体系架构

后台线程：1.Master Thread主要负责将缓冲池中的数据异步刷新到磁盘，保证数据的一致性，包括脏页的刷新、合并插入缓冲、UNDO页的回收等；2.InnoDB大量使用AIO来处理写IO请求，IO Thread的工作复制这些IO请求的回调处理；3.事务被提交后，所使用的undolog可能不再需要，因此需要Purge Thread来回收已经使用并分配的undo页；4.Page Cleaner Thread作用是将之前版本中脏页的刷新操作都放入到单独的线程中来完成，目的是为了减轻原Master Thread的工作及对于用户查询线程的阻塞。

内存：1.缓冲池简单来说就是一块内存区域，通过内存的速度来弥补磁盘速度较慢对数据库性能的影响（页从缓冲池刷新回磁盘的操作并不是在每次页发生更新时触发，而是采用Checkpoint机制），从1.0版本开始允许多个缓冲池实例；2.InnoDB存储引擎首先将重做日志信息放入重做日志缓冲这个缓冲区，然后按一定频率将其刷新到重做日志文件；3.在对一些数据结构本身的内存进行分配时，需要从额外的内存池中进行申请，当该区域内存不够时，会从缓冲池中进行申请。

通常来说，数据库中的缓冲池是通过LRU算法进行管理。最频繁使用的页在前端，最少使用的页在尾端。InnoDB对传统的LRU算法做了一些优化，LRU列表中添加了midpoint位置。新读取到的页，虽然是最新访问的页，但不是直接放入到LRU列表的首部，而是放入到LRU列表的midpoint位置。可以设定页读取到mid位置后需要等待多久才会加入到LRU列表的热端。这样做是避免类似索引或数据的扫描操作会使缓冲池中的页被刷新出，从而影响缓冲池的效率。

Checkpoint技术

为避免数据丢失问题，当前事务数据库系统普遍采用了Write Ahead Log策略，即当事务提交时，先写重做日志，再修改页。Checkpoint解决下面问题：缩短数据库恢复时间、缓冲池不够用时，将脏页刷新到磁盘；重做日志不可用时，刷新脏页。

当数据库发生宕机时，数据库不需要重做所有的日志，因为Checkpoint之前的页都已经刷新回磁盘；当缓冲池不够用，根据LRU算法会溢出最近最少使用的页，若此页为脏页，需要强制执行Checkpoint，将脏页刷回磁盘；重做日志是循环使用而不是无限增大的，当被重用的部分仍需要使用时，必须强制产生Checkpoint，将缓冲池中的页至少刷新到当前重做日志的位置。

Master Thread

Master Thread具有最高的线程优先级别。内部由多个循环组成：主循环、后台循环、刷新循环、暂停循环。

Loop分为每秒操作和每10秒操作，每秒操作包括：日志缓冲刷新到磁盘，即使这个事务还没有提交（总是）；合并插入操作（可能）；至多刷新100个InnoDB的缓冲池中的脏页到磁盘（可能）；如果当前没有用户活动，则切换到background loop（可能）。每10秒操作包括：刷新100个脏页到磁盘（可能的情况下）；合并至多5个插入缓冲（总是）；将日志缓冲刷新到磁盘（总是）；删除无用的Undo页（总是）；刷新100个或者10个脏页到磁盘。

若当前没有用户活动或者数据库关闭，就会切换到这个循环。background loop会执行：删除无用的undo页（总是）；合并20个插入缓冲（总是）；跳回到主循环（总是）；不断刷新100个页直到符合条件（可能，跳转到flush loop中完成）。

若flush loop无事可做，会切换到suspend loop，将Master Thread挂起，等待事件的发生。

InnoDB关键特性

Insert Buffer：

聚集索引是指该索引中键值的逻辑顺序决定了表中相应行的物理顺序；而非聚集索引索引的逻辑顺序与行的物理存储顺序无关。主键是一种特殊的具有唯一约束的索引，可以是聚集索引或是非聚集索引。

InnoDB设计了Insert Buffer，对于非聚集索引的插入或更新操作，不是每一次直接插入到索引页中，而是先判断插入的非聚集索引页是否在缓冲池中存在，是的话直接插入，否则先放到一个Insert Buffer对象中，然后再以一定的频率和情况进行Insert Buffer和辅助索引页子节点的merge操作，这时通常能将多个插入合并到一个操作中。

Insert Buffer的使用需要同时满足两个条件：索引是辅助索引和索引非唯一。插入聚集索引一般是顺序的，不需要磁盘的随机读取，而非唯一是因为如果索引唯一，每次插入时还要去离散查找页来确定唯一性，从而导致Insert Buffer失去意义。

Change Buffer时Insert Buffer的升级，InnoDB可以对DML都进行缓冲。

Insert Buffer的数据结构时一棵B+树。非叶节点存放查询的search key。当一个辅助索引要插入到页（space，offset）时，如果这个页不在缓冲池中，那么InnoDB存储引擎首先根据上述规则构造一个search key，接下来查询Insert Buffer这棵B+树，然后再将这条记录插入到Insert Buffer B+树的叶子节点。同非叶节点一样，叶子节点也需要先构造，再插入。

Merge Insert Buffer的操作发生在以下情况：辅助索引页被读取到缓冲池时；Insert Buffer Bitmap页追踪到该辅助索引页无可用空间；Master Thread。

两次写：

Insert Buffer带来性能上的提升，double write带来数据页的可靠性。double write分两部分，一部分是内存中的doublewrite buffer，另一部分是物理磁盘上共享表空间的doublewrite。在对缓冲池的脏页进行刷新时，并不直接写磁盘，而是会通过memcpy函数将脏页先复制到内存中的doublewrite buffer，之后通过doublewrite buffer两次顺序写入到doublewrite，然后调用fsync函数同步磁盘。如果操作系统在将页写入到磁盘时发生了崩溃，恢复时先从共享表空间找到副本复制到表空间文件再应用重做日志。

自适应哈希索引：

InnoDB存储引擎会监控对表上各索引页的查询。如果观察到建立哈希索引可以带来速度提升，则建立哈希索引。
AHI通过缓冲池的B+树索引页构造，建立速度快，且无需对整张表建立哈希索引。

异步IO：

异步IO在发送完IO请求后可以继续发出新的IO请求，等待通知即可，不用等待请求结果；且AIO可以进行IO Merge。

刷新邻接页：

当刷新一个脏页时，InnoDB会检测该页所在区的所有页，如果是脏页，那么一起进行刷新。这样可以利用AIO合并多个IO操作。

第三章.文件

参数文件（my.cnf）：当MySQL实例启动时，数据库会先去读一个配置参数文件，用来寻找数据库的各种文件所在位置以及指定某些初始化参数，这些参数通常定义了某种内存结构有多大。
日志文件：错误日志、慢查询日志、查询日志、二进制日志。二进制日志记录了对MySQL数据库执行更改的所有操作，不包括select、show这类。二进制日志有几种作用：某些数据的恢复需要二进制日志；复制；通过二进制日志信息进行审计，判断是否有对数据库进行注入的攻击。
套接字文件：在UNIX系统下本地连接MySQL可以采用UNIX域套接字方式，需要一个套接字文件。
pid文件：MySQL实例启动时，会将自己的进程ID写入pid文件。
表结构定义文件：MySQL数据的存储是根据表进行的，每个表都有与之对应的文件，不论采用何种存储引擎，都有一个后缀frm的文件记录了该表的表结构定义。
InnoDB存储引擎文件：默认配置下，表的数据存放在默认表空间文件ibdata1中。可以设置为每个表设计独立表空间存放数据，但仍有相关信息需要存放在共享表空间。每个InnoDB存储引擎至少有一个重做日志文件组，每个组至少有两个重做日志文件，ib_logfile0和ib_logfile1,以循环写入方式运行。

第四章.表

索引组织表：InnoDB存储引擎中，表都是根据主键顺序组织存放的，称为索引组织表。如果创建表时没有显式定义主键，InnoDB会首先判断表中是否有非空唯一索引，存在则以第一个定义的非空唯一索引为主键，不存在则InnoDB自动创建6字节大小的指针rowid。

表空间：从InnoDB存储引擎的逻辑存储结构来看，所有数据都被逻辑地存放在一个空间中，称为表空间。表空间又由段（segment）、区（extent）、页（page）组成。段分为数据段、索引段、回滚段等。数据段即为B+树的叶子节点、索引段即为B+树的非索引节点。区是由连续页组成的空间，每个区大小1MB。页是InnoDB磁盘管理的最小单位，有数据页、undo页、系统页、事务数据页等。InnoDB存储引擎是面向列的，也就是说数据是按行进行存放的。

InnoDB行记录格式：

• Compact行记录格式
• Redundant行记录格式

InnoDB存储引擎可以将一条记录中的某些数据存储在真正的数据页面之外。如果可以在一个页中至少放入两行数据，那varchar（TEXT或BLOB）类型的行数据就不会存放到BLOB页中去。

InnoDB数据页格式：数据页由文件头、页头、Infimun和Supremum Records、User Records、Free Space、Page Directory、File Trailer组成。

约束：数据库提供约束机制来保证数据库中数据的完整性：实体完整性、域完整性、参照完整性。InnoDB提供如下约束：Primary Key、Unique Key、Foreign Key、Default、Not Null。约束是一个逻辑的概念，用来保证数据的完整性，而索引是一个数据结构，既有逻辑上的概念，还代表着物理存储的方式。

Mysql不支持传统Check约束，但是通过Enum和Set类型可以解决部分需求，对于连续值的范围约束或更复杂的约束，则需要通过触发器。触发器的作用是在执行Insert、Delete、Update命令before或after自动调用SQL命令或存储过程。InnoDB在外键建立时会自动地对该列添加一个索引Key。

视图：Mysql中视图是一个命名的虚表，由一个SQL查询来定义，可以当作表使用。与持久表不同，视图中的数据没有实际的物理存储。

分区表：分区的过程是将一个表或索引分解为多个更小、更可管理的部分。MySQL数据库支持水平分区，并不支持垂直分区。Mysql数据库的分区是局部分区索引，一个分区中既存放了数据又存放了索引。而全局分区是指，数据存放在各个分区中，但是所有数据的索引存放在一个对象中。目前Mysql支持：RANGE分区、List分区、Hash分区、key分区、Columns分区。子分区允许在分区的基础上再进行分区，也称符合分区。Mysql允许在RANGE和LIST的分区上再进行Hash或Key的子分区。

对于OLAP的应用，分区的确可以很好提高查询性能，因为OLAP应用大多数查询需要频繁扫描一张大表。OLTP则不一定，若根据主键分区，那么进行主键查询或许有帮助，但对于别的列索引查询反而会降低性能。

第五章.索引和算法

InnoDB存储引擎支持以下几种常见索引：B+树索引、全文索引、哈希索引。自适应哈希索引会根据表的使用情况自动生成，不能人为干预。B+树索引找到被查找数据行所在的页，然后数据库把页读入内存，再在内存中进行查找，得到查找的数据。
B+树索引通过高扇出性保证高度一般在2~4层。B+树索引分为聚集索引和辅助索引，聚集索引和辅助索引不同的是，叶子节点存放的是否是一整行的信息。

聚集索引就是按照每张表的主键构造一棵B+树，同时叶子节点存放的即为整张表的行记录数据，聚集索引的叶子节点称为数据页。每个数据页通过一个双向链表来进行连接。通过查看表空间文件发现非数据页节点存放的仅仅是键值及指向数据页的偏移量，而不是一个完整的行记录，而数据页上存放的是完整的每行的记录。聚集索引的存储其实并不是物理上连续的，而仅仅逻辑上连续。页之间通过双向链表进行维护，每个页中的记录之间也通过双向链表维护。聚集索引还有一个优点是它对于主键的排序查找和范围查找速度非常快。

辅助索引也是B+树，叶子节点并不包含行记录的全部数据。叶子节点除了包含键值外，每个叶子节点中的索引行中还包含了一个书签，由于InnoDB是索引组织表，因此InnoDB存储引擎的辅助索引的书签就是相应行数据的聚集索引键。

索引的创建和删除有两种方法，一种是alter table add/drop key，另一种是create/drop index。

并不是所有的查询条件中出现的列都需要添加索引。对于什么时候添加B+树索引，经验是具有高选择性的列才有必要添加索引，通过show index from tablename 可以查看cardinality值来查看列是否具有高选择性。

联合索引是指对表上的多个列进行索引。联合索引也是一棵B+树，不同的是联合索引的键值数量大于等于2.联合索引已经对后面的键值进行了排序处理。

InnoDB存储引擎支持索引覆盖，即从辅助索引中就可以得到查询的记录，而不需要查询聚集索引中的记录。使用覆盖索引的一个好处是辅助索引不包含整行记录的所有信息，可以减少IO操作。

InnoDB存储引擎使用哈希算法来对字典进行查找，其冲突机制采用链表方式哈希函数采用除法散列方式。自适应哈希索引基于此实现，对于字典类型的查找非常迅速，但是对于范围查找无能为力。

全文检索通过使用倒排索引来实现。倒排索引在辅助表中存储了单词与单词自身在一个或多个文档中所在位置之间的映射,通常使用关联数组实现。

第六章.锁

InnoDB存储引擎提供一致性的非锁定读、行级锁支持。行级锁没有相关额外的开销，并可以同时的到并发性和一致性。

数据库中有两种锁：lock和latch。latch锁定时间短，目的是保证并发线程操作临界资源的正确性，没有死锁检测机制，分为互斥量和读写锁。lock的对象是事务，用来锁定数据库中的对象，如表、页、行。并且一般lock的对象仅在事务commit或rollback后进行释放，lock有死锁机制。

InnoDB存储引擎实现了如下两种标准的行级锁，允许事务读一行数据的共享锁（S锁）和允许事务删除或更新一行数据的排他锁（X锁）。InnoDB支持意向锁为表级别的锁，意向共享锁和意向排他锁。

一致性的非锁定读是指InnoDB存储引擎通过行多版本控制的方法来读取当前执行时间数据库中行的数据。如果读取的行正在执行delete或update操作，这时读取操作不会因此去等待行上锁的释放。相反，InnoDB会去读取行的快照数据。快照数据是指该行的之前版本的数据，这是通过undo段来完成。undo段是用来在事务中回滚数据，因此快照数据本身没有开销。且快照数据无需上锁。不同事务隔离级别不都是采用非锁定的一致性读，即使采用非锁定一致读，对于快照数据的定义也各不相同。

某些情况下，需要显示地对数据库读取操作进行加锁以保证数据逻辑的一致性。InnoDB对于select提供两种一致性锁定读操作：

• select...for update（对读取行加一个X锁）
• select...lock in share mode（对读取行记录加上一个S锁）

InnoDB存储引擎有3种行锁的算法：

• Record Lock：单个行记录上的锁
• Gap Lock：间隙锁，锁定一个范围，但不包含记录本身
• Next-Key Lock:Gap+Record,锁定一个范围，并且锁定记录本身

InnoDB存储引擎默认事务隔离级别是Repeatable read，该隔离级别下，其采用next-key locking加锁。而在Read committed下，其仅采用Record Lock。当查询的索引含有唯一属性时，InnoDB存储引擎会对Next-Key
Lock进行优化，将其降级为Record Lock。Next-key Lock用来解决幻读问题，组织多个事务将记录插入到同一个范围。

脏读是指一个事务可以读到另外一个事务中未提交的数据；不可重复读是指在一个事物内多次读取到同一个数据集合，但由于另一事务的修改，第一个事务两次读到的数据不一样。丢失更新是指一个事务的更新操作会被另一个事务的更新操作所覆盖，从而导致数据不一致，避免的话需要让事务串行化，类似整个事务阶段都加锁。

死锁是指两个或以上的事务在执行过程中，因争夺锁资源而造成的一种互相等待的现象。最简单的办法是超时机制，但超时机制根据FIFO的顺序选择回滚对象，若超时事务所占权重较大，效率就会低。所以也会通过等待图来进行死锁检测，存在死锁时回滚undo量最小的事务。

第七章.事务

InnoDB中的事务完全符合ACID的特性，即原子性、一致性、隔离性、持久性。从事务理论角度，可以分为以下几类：扁平事务、带有保存点的扁平事务、链事务、嵌套事务、分布式事务。

事务隔离性由由锁实现，。原子性、一致性、持久性通过数据库的redo log和undo log来完成，redo log称为重做日志，用来保证事务的原子性和持久性。undo log用来保证事务的一致性，用来帮助事务回滚及MVCC的功能。

重做日志都是以512字节进行存储的。这意味着重做日志缓存、重做日志文件都是以块的方式进行保存，称为重做日志块。由于重做日志块的大小和磁盘扇区的大小一样，都是512字节，因此重做日志的写入可以保证原子性，不需要doublewrite技术。

重做日志记录了事务的行为，可以很好地通过其对页进行“重做”操作。但是事务有时还需要进行回滚操作，这时就需要undo。redo存放在重做日志文件中，undo存放在数据库内部的一个特殊段中，称为undo段。undo段位于共享表空间中。undo只是逻辑地将数据库恢复，比如用insert恢复delete，而不是把页回滚为事务开始的样子，因为可能别的事务对同一个页进行修改。undo的另一个作用是MVCC。undo的产生会伴随着redo log的产生，因为undo log也需要持久性的保护。

purge用于最终完成delete和update操作。这样设计是因为InnoDB存储引擎支持MVCC，所以记录不能在事务提交时立即进行处理，这时其他事务可能正在引用这行，故InnoDB存储引擎需要保存记录之前的版本。而是否可以删除该条记录通过purge来判断。若该行记录不被任何其他事务引用，那么可以进行真正的delete操作。

InnoDB存储引擎提供了对XA事务的支持，并通过XA事务来支持不同数据库之间分布式事务的实现。使用分布式事务隔离级别必须设置为Serializable，全局事务要求在其中的所有参与的事务要么都提交，要么都回滚。XA事务由一个或多个资源管理器、一个事务管理器以及一个应用程序组成。