InnoDB存储引擎抱佛脚

外键

InnoDB关键特性

插入缓冲
两次写
自适应哈希索引
异步IO

索引与算法

InnoDB存储引擎支持的常见索引：

B+树索引

目前关系型数据库系统中最常用的最为有效的索引。B+树索引并不能找到一个给定键值的具体行。能找到的是被查找数据行所在的页，然后数据库通过把页读入到内存中，再在内存中进行查找，最后得到想要查找的数据。
2.全文索引

哈希索引

InnoDB存储引擎会根据表的使用情况自动为表生成哈希索引，不能人为干预是否在一张表中生成哈希索引

B+树

B+树是为磁盘或其他直接存取辅助设备设计的一种平衡查找树。在B+树中，所有记录节点都是按键值的大小顺序存放在同一层的叶子节点上，各叶子节点指针进行连接。

B+树索引

B+树索引在数据库中有一个特点是高扇出性。因此在数据库中，B+树的高度一般都在2-4层，

聚集索引

聚集索引按照每张表的主键构造一颗b+树，同时叶子节点中存放的即为整张表的行记录数据，也将聚集索引的叶子节点称为数据页。聚集索引的这个特性决定了索引组织表中数据也是索引的一部分。同B+树数据结构一样，每个数据页都通过一个双向链表来进行链接。

每张表只能有一个聚集索引，在多数情况下，查询优化器倾向于采用聚集索引。

数据页上存放的是完整的每行的记录，而在非数据页的索引页中，存放的仅仅是键值及指向数据页的偏移量，而不是完整的行记录。聚集索引的存储并不是物理上连续的，而是逻辑上连续的。这其中有两点：1.也通过双向链表链接，页按照主键的顺序排序；2.每个页中的记录也是通过双向链表维护的，物理存储上可以同样不按照主键存储。

聚集索引对于主键的排序查找和范围查找非常快。叶子节点的数据就是用户要查询的数据，如果要查找主键某一范围内的数据，通过叶子节点的上层中间节点就可以得到页的范围，之后直接读取数据页即可。

辅助索引

叶子节点除了包含键值以外，每个叶子节点中的索引行还包含了一个书签，该书签用来告诉InnoDB存储引擎哪里可以找到与索引对应的行数据。InnoDB存储引擎的辅助索引的书签就是相应行数据的聚集索引键。

每张表上可以有多个辅助索引。当通过辅助索引来寻找数据时，InnoDB存储引擎会遍历辅助索引并通过叶级别的指针获得指向主键索引的主键，然后再通过主键索引来找到一个完整的行记录。

B+树索引的分裂

InnoDB存储引擎的Page Header中有以下几个部分用来保存插入的顺序信息：
PAGE_LAST_INSERT
PAGE_DIRECTION
PAGE_N_DIRECTION
通过这些信息，InnoDB存储引擎觉得是向左还是向右进行分类，同时决定将分裂点记录为哪一个。

B+树索引的管理

show index命令可以查看表中索引的信息。
cardinality值非常关键，优化器会根据这个值来判断是否使用这个索引。
对于辅助索引的创建，InnoDB存储引擎会对创建索引的表加上一个S锁。在创建的过程中，不需要重建表。
删除辅助索引操作，InnoDB存储引擎只需更新内部视图，并将辅助作品的空间标记为可用。

B+树索引的使用

联合索引

联合索引是指对表上的多个列进行索引。本质上联合索引也是一颗B+树，不用的是联合索引的键值的数量不是1，而是大于等于2。

覆盖索引

InnoDB存储引擎支持覆盖索引，即从辅助索引中就可以得到查询的记录，而不需要查询聚集索引中的记录。
好处：1.辅助索引不包含整行记录的所有信息，远小于聚集索引，可以减少大量的IO操作。
2.对某些统计问题而言，InnoDB存储引擎并不会选择通过查询聚集索引来进行统计

优化器选择不使用索引的情况

这种情况多发生于范围查找、JOIN链接操作等情况

InnoDB存储引擎中的哈希算法

InnoDB存储引擎使用哈希算法来对字典进行查找，其冲突机制采用链表方式，哈希函数采用除法散列方式。

自适应哈希索引

自适应哈希索引仅是数据库自身创建并使用的。DBA本身并不能对其进行干预。对于字典类型的查找非常快速，例如 select * from table where index_col=‘xxx’。但是对于范围类型的查找就无能为力了

全文检索

全文检索是将存储与数据库中的整本书或整篇文章中的任意内容信息查找处理的技术。它可以根据需要获得全文中有关章、节、段、句、词等信息

倒排索引

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

锁的类型

数据库系统使用锁是为了支持对共享资源进行并发访问，提供数据的完整性和一致性。InnoDB存储引擎会在行级别上对表数据上锁。

lock和latch

latch，可通过命令SHOW ENGINE INNODB MUTEX看到有关latch的信息，称为闩锁，要求锁定的时间非常短。若持续时间长，则应用的性能会非常差。
InnoDB索引中，latch又分为mutex（互斥量）和rwlock（读写锁）。用来保证并发线程操作临街资源的正确性，并且通常没有死锁检测的机制。

lock的对象是事务，用来锁定数据库的对象，如表、页、行，一般lock的对象仅在事务commit或rollback后进行示范。lock有死锁机制。

InnoDB存储引擎中的锁

锁的类型

实现了如下两种标准的行级锁：
1.共享锁：允许事务读一行数据
2.排它锁：允许事务删除或更新一行数据

为了支持在不同粒度上进行加锁操作，InnoDB存储索引支持意向锁。意向锁是将锁定的对象分为多个层次，意向锁意为这书屋希望更细粒度上进行加锁。

InnoDB存储引擎支持意向锁设计比较简练，其意向锁即为表级别的锁。设计目的主要是为了在一个事务中揭示下一行将被请求的锁类型。支持两种意向锁：
1.意向共享锁（IS lock），事务想要获得一张表中某几行的共享锁
2.意向排他锁（IX lock），事务想要获得一张表中某几行的排他锁
意向锁不会阻塞除了全表扫以为的任何请求。

一致性非锁定读

指InnoDB存储引擎通过行多版本控制的方式来读取当前执行时间数据库中行的数据。如果读取的行正在执行delete或update操作，这时读取操作不会因此去等等行上锁的释放，会去读取行的一个快照数据。

一个行记录可能有不止一个快照数据，一般称这种技术为多版本技术，有次带来的并发控制，称之为多版本并发控制（MVCC）

在事务隔离级别READ COMMITTED和PRPEATABLE READ（可重复读，InnoDB存储引擎的默认事务隔离级别）下，InnoDB使用非锁定的一致性读。READ COMMITTED对于快照数据，非一致性读总是读取被锁定行的最新一份快照数据。而PRPEATABLE READ对于快照数据，总是读取事务开始时的行数据版本。

一致性锁定读

InnoDB存储引擎对于select语句支持两种一致性的锁定读操作：
SELECT…FOR UPDATE 对读取的行记录加一个X锁，其他事务不能对已锁定的行加上任何锁。
SELECT…LOCK IN SHARE MODE 对读取的行记录加了一个S锁，其他事务可以对读取的行记录加一个S锁，加X锁会被阻塞。
SELECT…FOR UPDATE 和 SELECT…LOCK IN SHARE MODE必须在一个事务中，当事务提交了，锁就释放了。因此只需上述两句select锁定语句时，务必加上BEGIN,START TRANSACTION或者SET AUTOCOMMIT=0;

自增长与锁

InnoDB存储引擎中自增长的实现和MyISAM不同，MyISAM是表锁设计，自增长不用考虑并发插入的问题，在InnoDB存储引擎中，自增长的列必须是索引，同时必须是索引的第一个列。

外键和锁

外键主要用于引用完整性的约束检查。对于一个外键列，如果没有显式地对这个列加索引，InnoDB存储引擎自动对其加一个索引，这样可以避免表锁

锁的算法

行锁的3种算法

Record Lock：单个行记录上的锁，总会去锁住索引记录，如果InnoDB存储引擎表在建立的时候没有设置任何一个索引，那么InnoDB存储引擎会使用隐式的主键来进行锁定。
Gap Lock：间隙锁，锁定一个范围，但不包含记录本身
Next-Key Lock:Gap Lock + Record Lock锁定一个范围，并且包含锁定记录本身。InnoDB对于行的查询都是采用这种锁定算法。

幻象问题
是指在同一事务下，连续执行两次同一的SQL语句可能导致不同的结果，第二次的SQL语句可能返回之前不存在的行。
例如：表t由1、2、5三个值组成，若事务T1执行如下的SQL语句：
SELECT * FROM t WHERE a > 2 FOR UPDATE;
事务没有提交操作，应该返回5。与此同时，另一个事务T2插入了4这个值，并且数据库允许改操作者，那么事务T1再次执行上述SQL语句会得到结果4和5。得到的结果与第一次不同，违反了事务的隔离性，即当前事务能够看到其他事务的结果。

InnoDB存储引擎默认的事务隔离级别是REPEATABLE READ, 这个隔离级别下，采用next-key locking的方式来加锁。用户可以通过InnoDB存储引擎的NEXT-KEY LOCKING机制在应用层层面实现唯一性的检测。

脏读

脏页是指是在缓冲池中已经被修改的也，但是还没有刷新到磁盘中，即数据库实例内存中的页和磁盘汇总的页的数据是不一致的，在刷新到磁盘之前，日志都已经被写入到了重做日志文件中。脏页是因为数据库实例内存和磁盘的异步造成的，这并不影响数据的一致性。并且因为脏页的刷新是异步的，不影响数据库的可用性，因为可以带来性能的提高。
脏数据是指事务对缓冲池中行记录的修改，并且还没有被提交。
脏读指的就是在不同的事务下，当前事务可以读到另一个事务未提交的数据。

不可重复读

不可重复读是指在一个事务内多次读取同一数据集合。在这个事务还没有结束时，另一个事务也访问该同一数据集合，并做了一些DML操作。这样就发生了一个事务内两次读到的数据是不一样的情况，这种情况称为不可重复读。

不可重复读和脏读的区别：
脏读是读到未提交的数据，而不可重复读读到的是已经提交的数据，但是其违反了数据库事务一致性的要求。
next-key lock算法，避免了不可重复读的现象。

丢失更新

丢失更新是另一个锁导致的问题，简单的说就是：一个事务的更新操作被另一个事务的更新操作所覆盖，从而导致数据不一致。
要避免丢失更新发生，需要将事务在着这种情况下的操作变成串行化，而不是并行的操作。

死锁

死锁概念
死锁是指两个或两个以上的事务在执行过程汇总，因争夺资源而造成的一种户型等待的现象。
解决死锁的最简单的方式是不要有等待，将任何的等待都转换为回滚，并且事务重新开始。但是会导致并发性能的下降，甚至任何一个事务都不能进行。这种带来的问题更严重。
解决死锁问题最简单的一种方式是超时，即当两个事务互相等待时，当一个等待时间超过设置的某个阈值，其中一个事务进行回归，另一个等待的事务就能继续执行。
当前数据库还都普遍采用wait-for graph（等待图）的方式来进行死锁检测。InnoDB也采用这种方式。wait-for graph要求数据库保存以下两种信息：
1.锁的信息链表
2.事务等待链表
上述链表构造成一张图，如果图中有回路，就代表存在死锁。

事务的隔离级别

READ UNCOMMITED
READ COMMITTED
REPEATABLE READ
SERIALIZABLE
InnoDB存储引擎会对每个select语句后自动加上lock in share mode，即为每个读取操作加一个共享锁。

分布式事务

在使用分布式事务时，InnoDB存储引擎的事务隔离级别必须设置为SERIALIZABLE
XA事务由一个或多个资源管理器，一个事务管理器以及一个应用程序组成。
资源管理器：提供访问事务资源的方法。通常一个数据库就是一个资源管理器。
事务管理器（连接Mysql服务器的客户端）：协调参与全局事务中的各个事务。需要和参与全局事务的所有资源管理器进行通信。
应用程序：定义事务的边界，指定全局事务的操作。
分布式事务使用两段式提交的方式。第一阶段，所有参与全局事务的节点都开始准备，告诉事务管理器它们准备好提交了。第二阶段，事务管理器告诉资源管理器执行ROLLBACK还是COMMIT。如果任何一个节点显示不能提交，则所有的节点都被告知需要回滚。