Mysql数据库结构、事务、隔离级别

最新推荐文章于 2024-08-27 16:25:50 发布

zzpueye

最新推荐文章于 2024-08-27 16:25:50 发布

阅读量342

点赞数 1

分类专栏： java基础

本文链接：https://blog.csdn.net/zzpueye/article/details/90047674

版权

java基础专栏收录该内容

13 篇文章 0 订阅

订阅专栏

知识整理

数据库索引的存储结构一般是B+树，为什么不适用红黑树等普通的二叉树

知识整理

B树
1. 特点：平衡、层少、升序
2. 结构：父节点、子节点指针数组、key信息数组（查找值或折半）
B+树
1. 结构区别：非叶节点保存下一节点的索引、叶子节点保存key数组、有序链表
2. 性能区别：IO少、平衡、范围查找
B+与普通二叉树比较 IO少：层数少、层间局部性原理、顺序读写
索引
1. 优点：查找排序快、主键索引保证唯一
2. 缺点：存储空间、维护时间
3. 使用场景：查询多、更新少、空值少的数据；
隔离级别 4种隔离级别、3种错误【脏读、不可重复读、幻读】
1. 读未提交
2. 读已提交 read commited：事务被提交后才能读
3. 可重复读 Repeatable read：同个事务的两次查询，结果一致；行锁 innodb的版本实现
4. 串行化 Serializable：范围读取后不能insert；读写互斥；页锁
InnoDB
1. 组成
2. 事务、检查点
3. 自适应索引
4. 插入缓冲

数据库索引的存储结构一般是B+树，为什么不适用红黑树等普通的二叉树

局部性原理：程序运行期间所需要的数据通常比较集中；当一个数据被用到时，其附近的数据也通常会马上被使用；
1. 磁盘会顺序预读页的整数倍的数据到内存中，减少磁盘的IO次数
2. 磁盘顺序读取的效率很高
B树的节点大小设置为一页（4k~16k），减少层间索引次数，效率较高；
1. 一次检索磁盘操作最多需要h-1次I/O，渐进复杂度为O(h)=O(logdN)
2. 实际应用中，出度d是非常大的数字，通常超过100，因此h非常小
红黑树等高度较高，索引次数较多，即磁盘IO次数较多，性能较慢

　　1. 数据库文件是放在硬盘上，磁盘寻道开销是非常大，应该尽量减少磁盘I/O次数。

　　2. B+树所有的关键字都出现在叶子节点，内存中可以存入更多的关键字，减少磁盘I/O次数

3.叶子节点通过链表相连，链表中的关键字是有序的，适合范围查找。非叶子节点只起索引作用

层间索引是磁盘

层内是顺序读

B树是一种平衡的多路查找树

索引高度h：从根块到达叶子块时所遍历的数据块的个数，大多数的高度都是2到3，层数越高，性能越差；
关键字集合分布在整棵树中、每个节点存储着关键字信息
平衡：所有叶子节点位于同一层上，到达任意叶子节点的搜索代价相同
分支节点和叶子节点默认升序排序

typedef struct BTNode

{

int keynum; //节点信息的数量，不包含key[0]节点

struct BTNode *parent; //父节点

int key[M+1]; //节点信息数组，第一个节点没用。

struct BTNode *ptr[M+1];//子节点信息

}BTNode;

B树的查找

（1）从树的根节点T开始，遍历key数组，或折半查找给定的值，如果找到，则返回节点指针和在节点中的位置；如果没有，则到（2）

（2）与节点中的Key进行比较，找到给定值左右Key中间的指针，去其子树中查找

（3）重复执行1,2两步，直到找到。如果直到叶子节点，仍未找到，则返回0，并返回最后搜索的叶子节点。（此节点是给定值需要插入的位置）

关于分支节点（包括根节点块）

1、索引有序（缺省是升序排列，也可以在创建索引时指定为降序排列）。

2、索引两个字段：所链接的索引块中包含的最小（大）键值；第二个字段：索引块的地址，指向下一个索引块

（0 B1）、（500 B2）、（1000 B3）指向三个分支节点块。其中的0、500和1000分别表示链接的键值的最小值。B1、B2和B3：索引块的地址。

关于叶子节点

平衡：B树索引的所有叶子块一定位于同一层上。根块到达任何一个叶子块的遍历代价相同，保证了B树索引的平衡，提高索引性能。防止数据插入、删除操作造成B树索引变得不平衡，影响索引性能。
有序：叶子节点所包含的索引条目与分支节点一样，有序（缺省是升序排列，也可以在创建索引时指定为降序排列）

2字段：每个索引条目（也可以叫做每条记录）也具有两个字段。第一个字段表示索引的键值，对于单列索引来说是一个值；而对于多列索引来说则是多个值组合在一起的。第二个字段：键值对应的记录行的ROWID，该ROWID是记录行在表里的物理地址。

除根结点之外的结点（包括叶子结点）的关键字的个数n必须满足：（ceil(m / 2)-1）<= n <=m-1。m表示最多含有m个孩子，n表示关键字数

在B树中插入关键码key的思路：

对高度为h的m阶B树，新结点一般是插在第h层。通过检索可以确定关键码应插入的结点位置。然后分两种情况讨论：

1、若该结点中关键码个数小于m-1，则直接插入即可。

2、若该结点中关键码个数等于m-1，则将引起结点的分裂。以中间关键码为界将结点一分为二，产生一个新结点，并把中间关键码插入到父结点(h-1层)中

重复上述工作，最坏情况一直分裂到根结点，建立一个新的根结点，整个B树增加一层。

删除(delete)操作

判断是否存在：首先查找B树中需删除的元素,如果该元素在B树中存在，则将该元素在其结点中进行删除，
判断是否有左右孩子：如果有，则上移孩子结点中最相近元素(“左孩子最右边的节点”或“右孩子最左边的节点”)到父节点中；如果没有，直接删除

删除元素，移动相应元素丰满：结点中元素个数>=ceil (m/2)-1，关键字数小了<（ceil(m/2)-1）就合并，大了>（m-1）就分裂

自身丰满，ok
若自身不丰满：元素数目（即关键字数）小于ceil(m/2)-1，则需要看其某相邻兄弟结点是否丰满
如果相邻兄弟结点丰满，则向父节点借一个元素来满足条件,兄弟结点上移到父结点；
如果相邻兄弟刚脱贫（==ceil (m/2)-1），借了之后结点数目小于ceil(m/2)-1，则该结+相邻兄弟结点+父结点=“合并”成一个结点，根节点时，树减少一层

减少一层，变为：

B+树 n叉排序树

聚集索引，叶结点data域保存了完整的数据记录，数据文件本身要按主键聚集

辅助索引data域存储相应记录主键的值，而不是地址

非叶子节点

不保存关键字指针，只进行节点索引，内存中能保存更多关键字指针；
所有数据地址必须要到叶子节点才能获取到，所以每次数据查询的次数都一样；

叶子节点特有

所有关键字都会在叶子结点出现；（与非叶会重复，闭区间）
有序
所有叶子结点指针有序的连接在一起；，可以按照关键字遍历，范围查找效率高

子树的个数最多可以与关键字一样多

B+缺点：B+树最大的性能问题是会产生大量的随机IO，随着新数据的插入，叶子节点会慢慢分裂，逻辑上连续的叶子节点在物理上往往不连续，甚至分离的很远，但做范围查询时，会产生大量读随机IO。解决B+树这一问题可采用LSM树，即Log-Structured Merge-Trees。其中Hbase中就利用LSM，感兴趣可以多查查LSM树相关的知识，这里就不再赘述。

性能上优点存入更多key，减少IO；效率稳定；范围查找效率高

B+树的磁盘读写代价更低：B+树的内部结点不包含关键字，其内部结点比B树小，内存能容纳的结点中关键字数量更多
B+树的查询效率更加稳定。B树搜索有可能会在非叶子结点结束，越靠近根节点的记录查找时间越短，只要找到关键字即可确定记录的存在，其性能等价于在关键字全集内做一次二分查找。而在B+树中，顺序检索比较明显，随机检索时，任何关键字的查找都必须走一条从根节点到叶节点的路，所有关键字的查找路径长度相同，每一个关键字的查询效率相当。
B+树的叶子节点使用指针链接在一起，只要遍历叶子节点就可以实现整棵树的遍历，基于范围的查询效率高

B树优点：

由于B树的每一个节点都包含key和value，因此经常访问的元素可能离根节点更近，因此访问也更迅速

索引

现在看下如何定位一个Record：

　　1 通过根节点开始遍历一个索引的B+树，通过各层非叶子节点最终到达一个数据页Page，这个Page里存放的都是叶子节点。

　　2 在Page内从"Infimum"节点开始遍历单链表（这种遍历往往会被优化），如果找到该键则成功返回。如果记录到达了"supremum"，说明当前Page里没有合适的键，这时要借助Page的Next Page指针，跳转到下一个Page继续从"Infimum"开始逐个查找。

对数据库表中列值进行排序的结构，作用：协助快速查询

索引在创建或者删除时，MySQL会先创建一个新的临时表，然后把数据导入临时表，删除原表，再把临时表更名为原表名称

索引缺点：存储空间、维护时间

增加了数据库的存储空间
创建索引和维护索引要耗费时间
1. 在插入和修改数据时要花费较多的时间(因为索引也要随之变动)

优点：

唯一性索引，可以保证行数据的唯一性
加快数据检索速度
减少查询中分组和排序的时间

场景优化不适用

很少查询的列不用创建索引
很少取值的无需单独创建，text, image、性别等
1. 性别只有男女，索引查询只能得到表50%的数据，没有意义
修改性能大于检索性能时，不应该创建索引；修改性能和检索性能是互相矛盾

数据库创建三种索引：唯一索引、主键索引和聚集索引

唯一索引可以保证行数据的唯一性，不允许其中任何两行具有相同索引值的索引，允许null；UNIQUE
主键索引要求主键值唯一，为表定义主键将自动创建主键索引，不允许null PRIMARY
普通索引：无限制；如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。
聚集索引提供更快的数据访问速度，表中行的物理顺序与键值索引顺序相同。一个表只能包含一个聚集索引，

普通索引的唯一任务是加快对数据的访问速度，因此，应该只为那些最经常出现在查询条件或者排序条件中的数据列创建索引

唯一索引用关键字UNIQUE把它定义为一个，Mysql会在有新纪录插入数据表时，自动检查新纪录的这个字段的值是否已经在某个记录的这个字段里出现过了。如果是，mysql将拒绝插入那条新纪录。唯一索引可以保证数据记录的唯一性

对比：

1.值空值：主键不允许空值，唯一索引允许空值

2.字段数量：主键字段只允许一个，唯一索引的字段允许多个

两者都要求数据值唯一

主键产生唯一的聚集索引，唯一索引产生唯一的辅助索引

注：聚集索引确定表中数据的物理顺序，所以是主键是唯一的（聚集就是整理数据的意思）

InnoDB的主键选择与插入优化

与业务无关的自增字段作为主键，每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页，形成一个紧凑的索引结构，近似顺序填满。由于每次插入时也不需要移动已有数据

聚集索引、辅助索引、非聚集索引

聚集（clustered）索引，也叫聚簇索引

定义：聚集索引是按每张表的主键构造的一颗B+树，并且叶节点中存放着整张表的行记录数据
1. 索引的叶子节点就是对应的数据节点，可以直接获取到对应的全部字段的数据
数据行的物理顺序与主键的逻辑顺序相同，一个表中只能拥有一个聚集索引
由于定义了数据的逻辑顺序，聚集索引能够特别快地访问针对范围值的查询
非叶结点只存放仅仅是键值及数据页的偏移量

优点：聚族索引将索引和数据保存在同一个B+树中，索引的叶子节点就是对应的数据节点，可以直接获取到对应的全部列的数据，而非聚集索引在索引没有覆盖到对应的列的时候需要进行二次查询，后面会详细讲。因此在查询方面，聚集索引的速度往往会更占优势

辅助索引

辅助索引data域存储相应记录主键的值而不是地址，再通过主键找到整行数据

辅助索引页级别不包含行的全部数据，叶节点除了包含键值以外，每个叶级别中的索引行中还包含了一个书签bookmark，该书签用来告诉InnoDB哪里可以找到与索引相对应的行数据

需要通过主键进行二次查询，因此在查询方面，聚集索引的速度往往会更占优势

非聚集索引：数据存储在一个地方，索引存储在另一个地方，索引带有指针指向数据的存储位置。

例子查字典：聚集索引：按拼音直接在正文查找；非聚集索引：按偏旁在索引目录查，再去翻正文

非聚簇索引

就是指B+Tree的叶子节点上的data并不是数据本身，而是数据存放的地址，主要用在MyISAM存储引擎。主索引和辅助索引没啥区别，只是主索引中的key一定是唯一的。

MyISAM使用的是非聚簇索引，非聚簇索引的两棵B+树看上去没什么不同，节点的结构完全一致只是存储的内容不同而已，主键索引B+树的节点存储了主键，辅助键索引B+树存储了辅助键。表数据存储在独立的地方，这两颗B+树的叶子节点都使用一个地址指向真正的表数据，对于表数据来说，这两个键没有任何差别。由于索引树是独立的，通过辅助键检索无需访问主键的索引树。

我们重点关注聚簇索引，看上去聚簇索引的效率明显要低于非聚簇索引，因为每次使用辅助索引检索都要经过两次B+树查找，这不是多此一举吗？聚簇索引的优势在哪？

　　1 由于行数据和叶子节点存储在一起，这样主键和行数据是一起被载入内存的，找到叶子节点就可以立刻将行数据返回了，如果按照主键Id来组织数据，获得数据更快

　　2 辅助索引使用主键作为"指针" 而不是使用地址值作为指针的好处是，减少了当出现行移动或者数据页分裂时辅助索引的维护工作，使用主键值当作指针会让辅助索引读入更多数据到内存，换来的好处是InnoDB在移动行时无须更新辅助索引中的这个"指针"。也就是说行的位置（实现中通过16K的Page来定位，后面会涉及）会随着数据库里数据的修改而发生变化（前面的B+树节点分裂以及Page的分裂），使用聚簇索引就可以保证不管这个主键B+树的节点如何变化，辅助索引树都不受影响

索引为什么那么快

1.二分查找、顺序读，效率高

索引就是通过事先排好序，从而在查找时可以应用二分查找等高效率的算法

一般的顺序查找，复杂度为O(n)，而二分查找复杂度为O(log2n)。当n很大时，二者的效率相差及其悬殊。

1.1 先读缓存不管数据表有无索引，首先在数据缓存池Buffer Pool中查找所需要的数据，

如果缓冲区中没有需要的数据时，服务器进程才去读磁盘【层间I/O】

无索引，直接去读表数据存放的磁盘块，读到数据缓冲区中，再查找需要的数据
有索引，先读入索引表，通过索引表直接找到所需数据的物理地址page，并把数据读入数据缓冲区中。再查找

2.B+树结构

联合索引=复合索引=组合索引

概念: 单一索引是指索引列为一列的情况,即新建索引的语句只实施在一列上;

用户可以在多个列上建立索引,这种索引叫做复合索引(组合索引);

联合索引的键值数量大于等于2，且叶节点按左位优先排序【类似于二次排序、n次排序】

因此，如果不从最左位开始查，只查询第二个，结果是无序的，索引不起作用

第二个键值进行了排序处理，如用户、购买时间

联合索引在数据库操作期间所需的开销更小,可以代替多个单一索引;

同时有两个概念叫做窄索引和宽索引，窄索引是指索引列为1-2列的索引，宽索引也就是索引列超过2列的索引;

设计索引的一个重要原则就是能用窄索引不用宽索引,因为窄索引往往比组合索引更有效;

使用:创建索引

create index idx1 on table1(col1,col2,col3)

select * from table1 where col1= A and col2= B and col3 = C

这时候查询优化器,不在扫描表了,而是直接的从索引中拿数据,因为索引中有这些数据,这叫覆盖式查询,这样的查询速度非常快;

注意事项:

1、对于复合索引，在查询使用时，最好将条件顺序按照索引的顺序，这样效率最高;

select * from table1 where col1=A AND col2=B AND col3=D

如果使用 where col2=B AND col1=A 或者 where col2=B 将不会使用索引

2、何时复合索引根据where条件建索引是极其重要的一个原则;

注意不要过多用索引,否则对表更新的效率有很大的影响,因为在操作表的时候要化大量时间花在创建索引中

索引应该建在选择性高的字段上（键值唯一的记录数/总记录条数），选择性越高索引的效果越好、价值越大，唯一索引的选择性最高；
组合索引中字段的顺序，选择性越高的字段排在最前面；
where条件中包含两个选择性高的字段时，可以考虑分别创建索引，引擎会同时使用两个索引（在OR条件下，应该说必须分开建索引）；
用窄索引

3、复合索引会替代单一索引么

不能。复合索引的使用原则是第一个条件应该是复合索引的第一列；正常情况下复合索引不能替代多个单一索引

如果索引满足窄索引的情况下可以建立复合索引,这样可以节约空间和时间

备注: 对一张表来说,如果有一个复合索引 on(col1,col2),就没有必要同时建立一个单索引 on col1;

如果查询条件需要,可以在已有单索引 on col1的情况下,添加复合索引on (col1,col2),对于效率有一定的提高

4.复合索引何时起作用：从第一个索引位置开始【左位优先匹配】，查询任意个复合字段；否则不起作用

如果不从最左位开始查，只查询第二个，结果是无序的，索引不起作用

覆盖索引

从辅助索引中即可查询到的记录，则不去聚集索引中进行二次查询，如count

利用辅助索引只存储主键，读入内存的数据更多，且不需要二次查询

InnoDB中的hash索引

目的：从内存【innodb_buffer_pool】中O(1)速度，得到某个被缓存的页；

除法散列：h(k) = k mod m，m为大于2倍缓冲池页数的最小质数，k为=[表空间id]space_id<<20+space_id+offset[页偏移量]

innodb会对表上的索引页的查询进行监控，如果发现建立hash索引能够带来性能提升，就自动创建hash索引。

hash索引的创建是有条件的,首先是必定能够带来性能提升。其次数据库以特定模式的连续访问超过了100次，

自适应hash根据B+树中的索引构造而来，只需为这个表的热点页构造hash索引而不是为整张表都构建。

全文检索、倒排索引

在辅助表中存储单词与单词所在文档位置之间的映射，利用关联数组实现

两种存储格式

inverted file index：{单词，单词所在文档ID}

full inverted index：{单词，{单词所在文档ID：文档中具体位置}} InnoDB默认

额外保存：第一次出现文档的ID，最后一次文档ID、在多少个文档中存在

删除操作会将文档ID插入DELETED表中，彻底删除使用optimize table命令

具体查询 match{clo1,col2} aganist (查询模式[自然语言NL{单词存在哪个文档}、Boolean{包含限定条件，包含啥，不包含啥}])

数据库各种隔离级别、事务的4种隔离性、会发生的3种问题：首先 set session autocommit=0，关闭sql自动提交

低到高分别为Read uncommitted 、Read committed 、Repeatable read 、Serializable

1.Read uncommitted 读未提交脏读--在账户中看到了未提交的数据

（在账户中看到了未提交的数据）

2.Read committed 读已提交针对脏读--在账户中看到了未提交的数据 oracle默认

只有在事务提交后，其更新结果才会被其他事务看见。解决脏读问题

问题：读取后UPDATE delete，数据变了，重复读时不一致，存在不可重复读；因为其他事务进行了修改

（A付费时，检测到账户钱有3w，此时B把钱转走了，A发现账户没钱了，即A的转账事务内，再查询（重复读）时数据不一样了）

输出的是最新提交的结果，RC级别的快照读遵循以下规则：

优先读取当前事务修改的数据，自己修改的，当然可以读到了；
其次读取最新已提交数据

会出现前后读取结果不一样的情况，但读取的是最新数据。

3.Repeatable read 可重复读针对单条数据读取后UPDATE delete，数据变了，重复读时不一致，对数据加行锁 mysql默认

解决方法：读取数据加行锁，其它事务无法修改这些数据，在一个事务中，保证对于同一份数据（相同字段）的读取结果总是相同的。

可以解决脏读、不可重复读（A付费时，锁定账户，不允许B用户拿该账户转账，即事务开启时，不允许其他事务的UPDATE修改操作）

问题：范围查询时，重复查又多了一条；

4.Serializable (串行化) 针对读取后insert ，发现多了一条；解决，行锁+间隙锁=next-key lock

幻读和不可重复读都是读取了另一条已经提交的事务,不可重复读重点在于update和delete，而幻读的重点在于insert

所以当事务A先前读取了数据，或修改了全部数据，事务B还是可以insert数据提交，这时事务A就会发现莫名其妙多了一条之前没有的数据

需要Serializable隔离级别：事务串行化顺序执行，读用读锁，写用写锁，读锁和写锁互斥，这么做可以有效的避免幻读、不可重复读、脏读等问题，但会极大的降低数据库的并发能力

innodb解决幻读：

实现了一致性不锁定读(Consistent Nonlocking Reads)，从而避免了幻读

在默认隔离级别REPEATABLE READ下，同一事务的所有一致性读只会读取第一次查询时创建的快照

一致性读是通过 MVCC 为查询提供了一个基于时间的点的快照。这个查询只能看到在自己之前提交的数据，而在查询开始之后提交的数据是不可以看到的

把该行修改前的值Copy到undo log（Copy on write）；
修改当前行的值，填写事务编号，使回滚指针指向undo log中的修改前的行。

在READ COMMITTED事务隔离级别下，对于快照数据，非一致性读总是读取被锁定行的最新一份快照数据

而在REPEATABLE READ事务隔离级别下，对于快照数据，非一致性读总是读取事务开始时的数据版本

SQL语句：

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

LOCK TABLES tablename WRITE;

LOCK TABLES tablename READ;

锁的粒度主要有以下几种类型：都是悲观锁

l 行锁：索引项加锁，粒度最小，并发性最高 for updata

l 页锁：一次锁定一页。25个行锁可升级为一个页锁。

l 表锁：粒度大，并发性低

l 数据库锁：控制整个数据库操作

间隙锁

乐观锁：人工控制：或CAS

认为数据一般情况下不会造成冲突，在数据进行提交更新时，才冲突检测；
如果冲突，则返回错误的信息给用户，让用户决定如何去做；
一般的实现乐观锁的方式就是记录数据版本、时间戳

如果数据库表当前版本号与更新前取出来的version值相等，则予以更新，否则认为是过期数据。用下面的一张图来说明：

2.乐观锁定的第二种实现方式和第一种差不多，在table中增加一个字段，字段类型使用时间戳（timestamp）

在更新提交的时候检查当前数据库中数据的时间戳，和自己更新前取到的时间戳进行对比，如果一致则OK，否则就是版本冲突。

数据库设置隔离级别

悲观锁：

每次去拿数据都认为可能存在并发修改，都会上锁，其他线程想拿数据就会阻塞，直到它拿到锁。
关系型数据库，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁

要使用悲观锁，我们必须关闭mysql数据库的自动提交属性，因为MySQL默认使用autocommit模式，也就是说，当你执行一个更新操作后，MySQL会立刻将结果进行提交。

begin;

//1.查询出商品信息

select status from t_goods where id=1 for update; //id=1的数据被行锁

//2.根据商品信息生成订单

insert into t_orders (id,goods_id) values (null,1);

//3.修改商品status为2

update t_goods set status=2;

//4.提交事务

commit;/commit work;

只有「明确」地指定主键，MySQL 才会执行Row lock (只锁住被选取的数据) ；如果是 name=..，执行表锁

否则MySQL 将会执行Table Lock (将整个数据表单给锁住)。

明确指定索引，并且有此数据，row lock；给name创建索引，再依据name=..查，行锁

Mysql 分页查询分页语句

select * from table limit (start-1)*limit,limit

取前5条数据

select * from table_name limit 0,5

或者

select * from table_name limit 5

查询第11到第15条数据

select * from table_name limit 10,5

大数据量下的优化

1.在查询下一页时把上一页的行id，作为参数传递给客户端程序；

select * from table where id>3000000 limit 10;

2.SELECT * FROM table WHERE id BETWEEN 1000000 AND 1000010;【双闭合】

Mysql有哪些存储引擎？有什么区别？

MyISAM：

不支持事务，但是每次查询都是原子的；
支持表级锁，即每次操作是对整个表加锁；
存储表的总行数；
一个MYISAM表有三个文件：索引文件、表结构文件、数据文件；
采用非聚集索引，索引文件的数据域存储指向数据文件的指针
MYISAM索引和数据是分开的，而且其索引是压缩的，可以更好地利用内存。所以它的查询性能明显优于INNODB

InnoDb：

支持ACID的事务，支持事务的四种隔离级别；
支持行级锁及外键约束：因此可以支持并发写；
不存储总行数；
一个InnoDb引擎存储在一个文件空间（共享表空间，表大小不受操作系统控制，一个表可能分布在多个文件里），也有可能为多个（设置为独立表空，表大小受操作系统文件大小限制，一般为2G），受操作系统文件大小的限制；
主键索引采用聚集索引（索引的数据域存储数据文件本身），
辅索引的数据域存储主键的值；因此从辅索引查找数据，需要先通过辅索引找到主键值，再访问辅索引；最好使用自增主键，防止插入数据时，为维持B+树结构，文件的大调整

innodb

INNODB会支持一些关系数据库的高级功能，如事务功能和行级锁，MYISAM不支持。MYISAM的性能更优，占用的存储空间少

你的应用程序一定要使用事务，毫无疑问你要选择INNODB引擎
如果你的应用程序对查询性能要求较高，就要使用MYISAM了。MYISAM索引和数据是分开的，而且其索引是压缩的，可以更好地利用内存。所以它的查询性能明显优于INNODB

InnoDB也使用B+Tree作为索引结构，InnoDB的数据文件本身就是索引文件【MyISAM索引文件和数据文件是分离】
叶节点包含了完整的数据记录，这种索引叫做聚集索引。这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有）；
如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形

InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域。例如，下图为定义在Col3上的一个辅助索引

innodb架构

innodb的整个体系架构就是由多个内存块组成的缓冲池、多个后台线程构成
缓冲池缓存磁盘数据（解决cpu速度和磁盘速度的严重不匹配问题），
后台进程保证缓存池和磁盘数据的一致性（读取、刷新），并保证数据异常宕机时能恢复到正常状态

缓冲池主要分为三个部分：redo log buffer、innodb_buffer_pool、innodb_additional_mem_pool

innodb_buffer_pool由包含：数据、索引、插入缓冲 ,自适应hash索引,锁信息及数据字典

数据的读写需要经过缓存（缓存在buffer pool 即在内存中）
数据以整页（16K）位单位读取到缓存中
缓存中的数据以LRU策略换出（最少使用策略）
IO效率高，性能好

redo log buffer 用来缓存重做日志。
additional memory pool:用来缓存LRU链表、等待、锁等数据结构
double write：存储脏页的拷贝，每次1M刷新到磁盘

后台进程分为：master thread，IO thread，purge thread，page cleaner thread。

master thread负责刷新缓存数据到磁盘，并协调调度其它后台进程。
IO thread 分为 insert buffer、log、read、write进程。分别用来处理插入缓冲insert buffer、重做日志、读写请求的IO回调。
purge thread用来回收undo 页
page cleaner thread用来刷新脏页

master thread根据服务器的压力分为了每一秒及每十秒的操作。

每一秒的操作包括：刷新重做日志、根据过去一秒的磁盘吞吐量来判断是否需要merge insert buffer、根据脏页在缓冲池中占比是否超过最大脏页占比及是否开启自适应刷新来刷新脏页。

每十秒的操作包括：根据过去10秒的磁盘吞吐量来刷新脏页，刷新重做日志，回收undo 页，再根据脏页占比是否超过70%刷新定量脏页。

特点：

根据主键寻址速度很快
主键值递增的insert插入效率较好
主键值随机insert插入操作效率差
因此，innodb表必须指定主键，建议使用自增数字；

如果不使用主键，系统会自动加上一个6字符字符串的主键；

数据的读写需要经过缓存（缓存在buffer pool 即在内存中）
数据以整页（16K）位单位读取到缓存中
缓存中的数据以LRU策略换出（最少使用策略）
IO效率高，性能好

事务机制

Mysql会最大程度的使用缓存机制来提高数据库的访问效率
事务日志包括：重做日志redo和回滚日志undo
1. Redo记录的是已经全部完成的事务，就是执行了commit的事务，记录文件是ib_logfile0， ib_logfile1
2. Undo记录的是已部分完成并且写入硬盘的，未完成的事务

引入checkpoint机制

随着redo的量增加，每次从redo的第一条开始恢复就会浪费长的时间，所以引入了checkpoint机制

Dirty page：脏页

一般业务运行过程中，当业务需要对某张的某行数据进行修改的时候，innodb会先将该数据从磁盘读取到缓存中去，然后在缓存中对这条数据进行修改，这样缓存中的数据就和磁盘的数据不一致了，这个时候缓存中的数据就称为dirty page，只有当脏页统一刷新到磁盘中才会是clean page

Checkpoint：如果在某个时间点，脏页的数据被刷新到了磁盘，系统就把这个刷新的时间点记录到redo log的结尾位置，在进行恢复数据的时候，checkpoint时间点之前的数据就不需要进行恢复了，可以缩短时间

double write：

double write的实现分两个部分，缓冲池，2M的内存块大小，共享表空间中连续的128个页
脏页从flush list刷新时，并不是直接刷新到磁盘而是先调用函数（memcpy），将脏页拷贝到double write buffer中，
然后再分两次，每次1M将double write buffer 刷新到磁盘double write 区，之后再调用fsync操作，同步到磁盘

因为脏页刷新到磁盘的写入单元小于单个页的大小，如果在写入过程中数据库突然宕机，可能会使数据页的写入不完成，造成数据页的损坏。

redo log中记录的是对页的物理操作，如果数据页损坏了，通过redo log也无法进行恢复。所以为了保证数据页的写入安全，引入了double write

double write的实现分两个部分，缓冲池，2M的内存块大小，共享表空间中连续的128个页

脏页从flush list刷新时，并不是直接刷新到磁盘而是先调用函数（memcpy），将脏页拷贝到double write buffer中，

然后再分两次，每次1M将double write buffer 刷新到磁盘double write 区，之后再调用fsync操作，同步到磁盘。

如果应用在业务高峰期，innodb_dblwr_pages_written:innodb_dblwr_writes远小于64:1，则说明，系统写入压力不大。

虽然，double write buffer刷新到磁盘的时候是顺序写，但还是是有性能损耗的。如果系统本身支持页的安全性保障（部分写失效防范机制），如ZFS，那么就可以禁用掉该特性（skip_innodb_doublewrite）。

adaptive hash index：

innodb会对表上的索引页的查询进行监控，如果发现建立hash索引能够带来性能提升，就自动创建hash索引。

hash索引的创建是有条件的,首先是必定能够带来性能提升。其次数据库以特定模式的连续访问超过了100次，

自适应hash根据B+树中的索引构造而来，只需为这个表的热点页构造hash索引而不是为整张表都构建。

insert buffer：

专门为维护非唯一辅助索引的更新设计的

聚集索引对应的辅助索引的更新则是离散的，为了避免大量离散读写

先判断插入的非聚集索引叶子是否在缓冲池中，若在，则直接插入；

若不在，则先将插入的记录放到insert buffer中，然后根据一些算法将insert buffer 缓存的记录通过后台线程慢慢的合并（merge）回辅助索引页中。

这样做的好处是：（1）减少磁盘的离散读取；（2）将多次插入合并为一次操作。

checkpoint:

Dirty page：脏页

异步IO：

mysql 5.5之前并不支持异步IO，而是通过innodb代码模拟实现。5.5之后开始提供AIO支持。数据库可以连续发出IO请求，然后再等待IO请求的处理结果。异步IO带来的好处就是可以进行IO合并操作，减少磁盘压力。要想mysql支持异步IO还需要操作系统支持，首先操作系统必须支持异步IO，像windows，linux都是支持的，但是 mac osx却不支持。同时在编译和运行时还需要有libaio依赖包。可以通过设置innodb_use_native_aio来控制是否启用这个特性，一般开启这个特性可以使数据恢复带来75%的性能提升。

多隔离级别：

innodb支持四种隔离级别RU\RC\RR\serializable。RU不使用MVCC，读取的时候也不加锁。RC利用MVCC都是读取记录最新的版本，RR利用MVCC总是读取记录最旧的版本，并通过next-key locking来避免幻读，serializable不使用MVCC，读取记录的时候加共享锁，堵塞了其它事务对该记录的更新，实现可串行化。隔离级别越高，维护成本越高，并发越低。RC隔离级别下要求二进制日志格式必须是row格式的，因为RC隔离级别下，不会加gap锁，不能禁止一个事务在执行的过程中另一个事务对它的间隙进行操作的情况。这种情况下，对于事务开始的和提交的顺序是先更改后提交，后更改先提交的情况，statement格式的binlog只会是按照事务提交的顺序进行记录。这可能会导致复制环境的slave数据和master数据不一致。通过设置innodb_locks_unsafe_for_binlog=1也可以使用statement格式，但是主从数据的一致性没法保证。

MVCC 多版本并发控制机制

机制可以控制并发操作,但是其系统开销较大,而MVCC可以在大多数情况下代替行级锁,使用MVCC,能降低其系统开销.

MVCC是通过保存数据在某个时间点的快照来实现的