事务
事务就是一组原子性的SQL查询，或者说一个独立的工作单元。如果数据库引擎能够成功地对数据库应用该组查询的全部语句，那么就执行该组查询。如果其中有任何一条语句因为崩溃或其他原因无法执行，那么所有的语句都不会执行。也就是说，事务内的语句，要么全部执行成功，要么全部执行失败。
ACID
用START TRANSACTION语句开始一个事务，然后要么使用COMIT提交事务将修改的 数据持久保留，要么使用ROLLBACK撤销所有的修改。事务SQL的样本如下:
1 START TRANSACTION;
2 SELECT balance FROM checking WHERE customer_id = 10233276;
3 UPDATE checking SET balance = balance - 200.00 WHERE customer_id = 10233276;
4 UPDATE savings SET balance = balance + 200.00 WHERE customer_id = 10233276;
5 COMMIT;
ACID表示原子性(atomicity)、一致性 (consistency)、 隔离性(isolation) 和持久性(durability)。 一个运行良好的事务处理系统，必须具备这些标准特征。
-
原子性(atomicity)
一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全 部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分 操作，这就是事务的原子性。 -
一致性(consistency)
数据库总是从一个一致性的状态转换到另外一个一致性的状态。在前面的例子中， 一致性确保了 ，即使在执行第三、四条语句之间时系统崩溃，支票账户中也不会损 失200美元，因为事务最终没有提交，所以事务中所做的修改也不会保存到数据库中。 -
隔离性(isolation)
通常来说，一个事务所做的修改在最终提交以前，对其他事务是不可见的。在前面 的例子中，当执行完第三条语句、第四条语句还未开始时，此时有另外一个账户汇 总程序开始运行，则其看到的支票账户的余额并没有被减去200美元。后面我们讨 论隔离级别(Isolationlevel)的时候,会发现为什么我们要说“通常来说”是不可见的。 -
持久性(durability)
一旦事务提交，则其所做的修改就会永久保存到数据库中。此时即使系统崩溃，修 改的数据也不会丢失。持久性是个有点模糊的概念，因为实际上持久性也分很多 不同的级别。有些持久性策略能够提供非常强的安全保障，而有些则未必。而且 不可能有能做到100%的持久性保证的策略(如果数据库本身就能做到真正的持久 性，那么备份又怎么能增加持久性呢? )。
隔离级别
在SQL标准中定义了四种隔离级别，每一种级别都规定了 一个事务中所做的修改，哪些在事务内和事务间是可见的，哪些是不可见的。较低级别 的隔离通常可以执行更高的并发，系统的开销也更低。
-
READ UNCOMMITTED (未提交读)
在READ UNCOMMITTED级别，事务中的修改，即使没有提交,对其他事务也都是可见 的。事务可以读取未提交的数据，这也被称为脏读(Dirty Read)。这个级别会导致 很多问题，从性能上来说，READUNCOMMITTED不会比其他的级别好太多，但却缺乏 其他级别的很多好处，除非真的有非常必要的理由，在实际应用中一般很少使用。 -
READ COMMITTED (提交读 RC)
大多数数据库系统的默认隔离级别都是READ COMMITTED (但MySQL不是)该级别解决了脏读（脏读指的是读到了其他事务未提交的数据）的问题。 READ COMMITTED满足前面提到的隔离性的简单定义:一个事务开始时，只能“看见”已 经提交的事务所做的修改。换句话说，一个事务从开始直到提交之前，所做的任何 修改对其他事务都是不可见的。这个级别有时候也叫做不可重复读(nonrepeatable read)，因为一个事务内两次执行同样的查询，可能会得到不一样的结果。 -
REPEATABLE READ (可重复读 RR)
REPEATABLE READ。该级别保证了在同一个事务中多次读取同样记录的结果是一致的。 但是理论上，可重复读隔离级别还是无法解决另外一个幻读(Phantom Read)的问题。所谓幻读，指的是当某个事务在读取某个范围内的记录时， 另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录时，会产生幻行(Phantom Row)。InnoDB和XtraDB存储弓|擎通过多版本并发控制(MVCC, Multiversion Concurrency Control) 解决了幻读的问题。可重复读是MySQL的默认事务隔离级别。 -
SERIALIZABLE (可串行化)
SERIALIZABLE是最高的隔离级别。它通过强制事务串行执行，避免了前面说的幻读 的问题。简单来说，SERIALIZABLE 会在读取的每一行数据上都加锁，所以可能导致 大量的超时和锁争用的问题。实际应用中也很少用到这个隔离级别，只有在非常需 要确保数据的一致性而且可以接受没有并发的情况下，才考虑采用该级别。
事务日志
事务日志可以帮助提高事务的效率。使用事务日志，存储引擎在修改表的数据时只需要
修改其内存拷贝，再把该修改行为记录到持久在硬盘上的事务日志中，而不用每次都将
修改的数据本身持久到磁盘。事务日志采用的是追加的方式，因此写日志的操作是磁盘
上一小块区域内的顺序I/O，而不像随机I/O需要在磁盘的多个地方移动磁头，所以采用
事务日志的方式相对来说要快得多。事务日志持久以后，内存中被修改的数据在后台可
以慢慢地刷回到磁盘。目前大多数存储引擎都是这样实现的，我们通常称之为预写式日
志(Write-Ahead Logging)，修改数据需要写两次磁盘。
如果数据的修改已经记录到事务日志并持久化，但数据本身还没有写回磁盘，此时系统
崩溃，存储引擎在重启时能够自动恢复这部分修改的数据。具体的恢复方式则视存储引擎而定。
主备复制
-
第一步在主库上记录二进制日志。在每次准备提交事务完成数据更新前，主库将数据更新的事件记录到二进制日志中。MySQL会按事务提交的顺序 而非每条语句的执行顺序来记录一进制日志。在记录二进制日志后，主库会告诉存储引擎可以提交事务了。
-
第二步备库将主库的二进制日志复制到其本地的中继日志中。首先，备库会启动一个工作线程，称为I/O线程，I/O 线程跟主库建立一个普通的客户端连接，然后在主库上启 动一个特殊的二进制转储(binlog dump)线程(该线程没有对应的SQL命令)，这个二进制转储线程会读取主库上二进制日志中的事件。它不会对事件进行轮询。如果该线程 追赶上了主库，它将进入睡眠状态，直到主库发送信号量通知其有新的事件产生时才会被唤醒，备库I/O线程会将接收到的事件记录到中继日志中。
-
第三步，备库的SQL线程从中继日志中读取事件并在备库执行，从而实现备库数据的更新。当SQL线程追赶上I/O线程时，中继日志通常已经在系统缓存中，所 以中继日志的开销很低。SQL线程执行的事件也可以通过配置选项来决定是否写入其自己的二进制日志中，这在多备份的场景下非常有用。
-
这种复制架构实现了获取事件和重放事件的解耦，允许这两个过程异步进行。也就是说I/O线程能够独立于SQL线程之外工作。但这种架构也限制了复制的过程，其中最重要 的一点是在主库上并发运行的查询在备库只能串行化执行，因为只有一个SQL线程来重放中继日志中的事件。这是很多工作负载的性能瓶颈所在。
InnoDB逻辑存储结构
从InnoDB存储引擎的逻辑存储结构看，所有数据都被逻辑地存放在一个空间中，称之为表空间(tablespace)。 表空间又由段(segment)、 区(extent)、 页(page) 组成。页在 一些文档中有时也称为块( block)，InnoDB 存储引擎的逻辑存储结构大致如图所示：
-
表
表空间可以看做是InnoDB存储引擎逻辑结构的最高层，所有的数据都存放在表空间中。在默认情况下InnoDB存储引擎有一个共享表空间ibdatal, 即所有数据都存放在这个表空间内。如果用户启用了参数innodb_file_per_table, 则每张表内的数据可以单独放到一个表空间内。 如果启用了innodb_file_per_able 的参数，需要注意的是每张表的表空间内存放的只是数据、索引和插人缓冲Bitmap页，其他类的数据，如回滚(undo) 信息，插入缓冲 索引页、系统事务信息，二次写缓冲(Double write buffer)等还是存放在原来的共享表空间内。 -
段
上图显示了表空间是由各个段组成的，常见的段有数据段、索引段、回滚段等。 数据段即为B+树的叶子节点(Leaf node segment)，存放数据。 索引段即为B+树的非索引节点(Non-leaf node segment)，存放索引信息。 回滚段用于存储undo日志，用于事务失败后数据回滚以及在事务未提交之前通过undo日志获取之前版本的数据。 -
区
区是由连续页组成的空间，在任何情况下每个区的大小都为1MB。为了保证区中页的连续性，InnoDB 存储引擎一次从磁盘申请4 ~ 5个区。在默认情况下，InnoDB存储 引擎页的大小为16KB,即一个区中共有64个连续的页。 - 页
页是InnoDB磁盘管理的最小单位。在InnoDB存储引擎中，默认每个页的大小为16KB。而从InnoDB1.2.x版本开始，可以通过参数innodb_page_size将页的大小设置为4K、8K、 16K。若设置完成，则所有表中页的大小都为innodb_page_size, 不可以对其再次进行修改。除非通过mysqldump导人和导出操作来产生新的库。在InnoDB存储引擎中，常见的页类型有:- 数据页(B-tree Node)
- undo页( undo Log Page)
- 系统页(System Page)
- 事务数据页(Transaction system Page)
- 插人缓冲位图页(Insert Buffer Bitmap)
- 插人缓冲空闲列表页(Insert Buffer Free List)
- 未压缩的二进制大对象页(Uncompressed BLOB Page)
- 压缩的二进制大对象页(compressed BLOB Page)
- 行
InnoDB存储引擎是面向列的(row-oriented)，也就说数据是按行进行存放的。每个页存放的行记录也是有硬性定义的，最多允许存放16KB/2 - 200行的记录，即7992行记录。
B+树索引
-
聚集索引
聚集索引( clustered index)就是按照每张表的主键构造一棵B+树，同时叶子节点中存放的即为整张表的行记录数据，也将聚集索引的叶子节点称为数据页。聚集索引 的这个特性决定了索引组织表中数据也是索引的一部分。同B+树数据结构一样，每个数据页都通过一个双向链表来进行链接。 由于实际的数据页只能按照一棵 B+ 树进行排序，因此每张表只能拥有一个聚集索引。在多数情况下，查询优化器倾向于采用聚集索引。因为聚集索引能够在B+树索引 的叶子节点上直接找到数据。此外，由于定义了数据的逻辑顺序，聚集索引能够特别快地访问针对范围值的查询。查询优化器能够快速发现某一段范围的数据页需要扫描。 -
辅助索引
辅助索引(Secondary Index，也称非聚集索引)，叶子节点并不包含行记录的全部数据。叶子节点除了包含键值以外，每个叶子节点中的索引行中还包含了一个书签(bookmark)。 该书签用来告诉InnoDB存储引擎哪里可以找到与索引相对应的行数据。由于InnoDB存储引擎表是索引组织表，因此InnoDB存储引擎的辅助索引的书签就是相应行数据的聚集索引键。
辅助索引的存在并不影响数据在聚集索引中的组织，因此每张表上可以有多个辅助索引。当通过辅助索引来寻找数据时，InnoDB 存储引擎会遍历辅助索引并通过叶级别的指针 获得指向主键索引的主键，然后再通过主键索引来找到一个完整的行记录。举例来说，如果在一棵高度为3的辅助索引树中查找数据，那需要对这棵辅助索引树遍历3次找到指定主 键，如果聚集索引树的高度同样为3，那么还需要对聚集索引树进行3次查找，最终找到一个完整的行数据所在的页，因此一共需要6次逻辑IO访问以得到最终的一个数据页。 -
联合索引
联合索引是指对表上的多个列进行索引。联合索引的创建方法与单个索引创建的方法一样， 不同之处仅在于有多个索引列。 联合索引也是一棵B+树，不同的是联合索引的键值的数量不是1，而是大于等于2。接着来讨论两个整型列组成的联合索引，假定两个键值的名称分别为a、b,如图5-22所示。
从图5-22可以观察到多个键值的B+树情况。其实和之前讨论的单个键值的B+树并没有什么不同，键值都是排序的， 通过叶子节点可以逻辑上顺序地读出所有数据，就上面的例子来说，即(1, 1)、 (1, 2)、 (2, 1)、 (2, 4)、 (3, 1)、(3, 2)。数据按(a,b)的顺序进行了存放。
联合索引的组织结构类似于英语字典，首先按照单词首字母排序，然后按照第二个字母排序，依次类推。 联合索引就是首先按照第一个索引排序，然后按照第二个索引排序，依次类推。所以联合索引中有一个最左匹配原则， 试想一下在查找字典时你不可能先查找一个单词中的第二个字母，然后再查询第一个字母。所以联合索引中必须先查找第一个索引然后再查找第二个索引。 如果直接查找第二个索引，则此查询过程就是遍历整个数据表，而不是利用索引来查询。
因此，对于查询SELECT * FROM TABLE WHERE a =xxx and b=xxx，显然是可以使用(a, b)这个联合索引的。 对于单个的a列查询SELECT * FROM TABLE WHERE a=xxx，也可以使用这个(a,b)索引。 但对于b列的查询SELECT*FROMTABLE WHERE b=xxx，则不可以使用这棵B+树索引。 可以发现叶子节点上的b值为1、2、1、4、1、2，显然不是排序的，因此对于b列的查询使用不到(a, b)的索引。 -
倒排索引
全文检索通常使用倒排索引(inverted index)来实现。倒排索引同B+树索引一样，也是一种索引结构。它在辅助表(auxiliary table)中存储了单词与单词自身在一个或多 个文档中所在位置之间的映射。这通常利用关联数组实现，其拥有两种表现形式:- inverted file index,其表现形式为{单词，单词所在文档的ID}
- fill inverted index,其表现形式为{单词，(单词所在文档的ID,在具体文档中的位置)}
例如，对于下面这个例子，表t存储的内容如表5-6所示。
DocumentId 表示进行全文检索文档的Id，Text表示存储的内容，用户需要对存储的这些文档内容进行全文检索。
例如，查找出现过Some单词的文档Id,又或者查找单个文档中出现过两个Some单词的文档Id，等等。
对于inverted fle index的关联数组，其存储的内容如表5-7所示。
可以看到单词code存在于文档1和4中，单词days存在与文档3和6中。之后再要进行全文查询就简单了，可以直接根据Documents得到包含查询关键字的文档。
对于invertedfileindex，其仅存取文档Id，而fullinvertedindex存储的是对(pair)，即(DocumentId, Position)。
锁
读写锁
InnoDB存储引擎实现了如下两种标准的行级锁:
- 共享锁(S Lock),允许事务读一行数据。
- 排他锁(X Lock),允许事务删除或更新一行数据。
如果一个事务T1已经获得了行r的共享锁，那么另外的事务T2可以立即获得行r的共享锁，因为读取并没有改变行r的数据，称这种情况为锁兼容(Lock Compatible)。
但若有其他的事务T3想获得行r的排他锁，则其必须等待事务T1、T2释放行r上的共享锁这种情况称为锁不兼容。又叫做读写锁。
意向锁
InnoDB 存储引擎支持多粒度( granular)锁定，这种锁定允许事务在行级上的锁和表级上的锁同时存在。为了支持在不同粒度上进行加锁操作，InnoDB 存储引擎支持一种额外的锁方式，称之为意向锁(Intention Lock)。 意向锁是将锁定的对象分为多个层次，意向锁意味着事务希望在更细粒度(fine granularity)上进行加锁，若将上锁的对象看成一棵树，那么对最下层的对象上锁，也就是对最细粒度的对象进行上锁， 那么首先需要对粗粒度的对象上锁。如果需要对页上的记录r进行上X锁，那么分别需要对数据库A、表、页上意向锁IX，最后对记录r上X锁。若其中任何一个部分导致等待，那么该操作需要等待粗粒度锁的完成。
InnoDB存储引擎支持意向锁设计比较简练，其意向锁即为表级别的锁。设计目的主要是为了在一个事务中揭示下一行将被请求的锁类型。其支持两种意向锁:
- 意向共享锁(IS Lock)， 事务想要获得一张表中某几行的共享锁
- 意向排他锁(IX Lock)，事务想要获得一张表中某几行的排他锁
由于InnoDB存储引擎支持的是行级别的锁，因此意向锁其实不会阻塞除全表扫以外的任何请求。故表级意向锁与行级锁的兼容性如表6-4所示。
间隙锁
间隙锁是一种加在两个索引之间的锁，或者加在第一个索引之前，或最后一个索引之后的间隙。
使用间隙锁锁住的是一个区间，而不仅仅是这个区间中的每一条数据。
间隙锁只阻止其他事务插入到间隙中，他们不阻止其他事务在同一个间隙上获得间隙锁，所以 gap x lock 和 gap s lock 有相同的作用。
一致性非锁定读（MVCC）
一致性的非锁定读(consistent nonlocking read) 是指InnoDB存储弓|擎通过行多版本控制(multi versioning)的方式来读取当前执行时间数据库中行的数据。 如果读取的行正在执行DELETE或UPDATE操作，这时读取操作不会因此去等待行上锁的释放。相反地，InnoDB存储引擎会去读取行的一个快照数据。 如图6-4所示。图6-4直观地展现了InnoDB存储引擎一致性的非锁定读。之所以称其为非锁定读，因为不需要等待访问的行上X锁的释放。
快照数据是指该行的之前版本的数据，该实现是通过undo段来完成。而undo用来在事务中回滚数据，因此快照数据本身是没有额外的开销。 此外，读取快照数据是不需要上锁的，因为没有事务需要对历史的数据进行修改操作。可以看到，非锁定读机制极大地提高了数据库的并发性。 在InnoDB存储引擎的默认设置下，这是默认的读取方式，即读取不会占用和等待表上的锁。 但是在不同事务隔离级别下，读取的方式不同，并不是在每个事务隔离级别下都是采用非锁定的一致性读。 此外，即使都是使用非锁定的一致性读，但是对于快照数据的定义也各不相同。 通过图6-4可以知道，快照数据其实就是当前行数据之前的历史版本，每行记录可能有多个版本。 就图6-4所显示的，一个行记录可能有不止一个快照数据，一般称这种技术为行多版本技术。由此带来的并发控制，称之为多版本并发控制(Multi VersionConcurrency Control, MVCC)。
在事务隔离级别READCOMMITTED和REPEATABLEREAD(InnoDB存储引擎的默认事务隔离级别)下，InnoDB 存储引擎使用非锁定的一致性读。
然而，对于快照数据的定义却不相同。在READ COMMITTED事务隔离级别下，对于快照数据，非一致性读总是读取被锁定行的最新一份快照数据（所以会出现不可重复读问题）。
而在REPEATABLEREAD事务隔离级别下，对于快照数据，非一致性读总是读取事务开始时的行数据版本。
总结
任何隔离级别，数据操作都有两种：快照读（普通select）和当前读（select for update, update 等），当前读总是读当前版本的数据，也就是多版本中最新一次提交的那版。快照读利用MVCC，在RC隔离级别下快照读和当前读一样都是读取最新版本的数据，所以如果另外一个事务更改了数据并且已提交，则当前事务再次读同样的数据会不一样，这就是不可重复读问题。 而在RR隔离级别下快照读用的是此事务开始时的行数据版本，所以其他事务对数据修改在此事务下不可见，也就是可重复读。
当前读利用锁，RC隔离级别下写加排它锁，事务结束释放，读加共享锁，读完立刻释放，这样解决了脏读问题，但存在不可重复读问题； RR隔离级别下写加排它锁，事务结束释放，读加共享锁，事务结束释放，这样解决了不可重复读问题，但存在幻读问题。
InnoDB在RR隔离级别下通过间隙锁解决了幻读问题。
参考
本文是《高性能MySQL》和《MySQL技术内幕：InnoDB存储引擎》读书笔记。