[大数据]MySQL总结

MySQL总结

三大范式

三大范式: 原子性( 不可再分 ) / 主键唯一 (全部数据依赖主键) / 每列字段必须和主键有直接关系而不是间接关系

第一范式（1NF）

要求数据库表的每一列都是不可分割的原子数据项。

第二范式（2NF）

在1NF的基础上，非码属性必须完全依赖于候选码（在1NF基础上消除非主属性对主码的部分函数依赖）

第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。

第三范式（3NF）

在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）

第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。

上表中，所有属性都完全依赖于学号，所以满足第二范式，但是“班主任性别”和“班主任年龄”直接依赖的是“班主任姓名”，

而不是主键“学号”，所以需做如下调整：

MySQL常用数据库引擎

• 查看MySQL数据库使用的引擎

  	SHOW ENGINES;
  

• 查看数据库默认使用哪个引擎

  SHOW VARIABLES LIKE 'storage_engine';
  

InnoDB

InnoDB是MySQL默认的存储引擎, InnoDB也是事务型数据库的首选引擎，支持事务安全表（ACID），支持行锁定和外键，上图也看到了，InnoDB是默认的MySQL引擎。
InnoDB主要特性有：

• 支持事务
• 支持外键
• 支持聚簇索引
• 最小锁粒度是行锁
• 本地存储数据文件为.frm 和 .data

MyISAM

MyISAM基于ISAM存储引擎，并对其进行扩展。它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。MyISAM拥有较高的插入、查询速度，但不支持事物，不支持外键;

frm、MYD、MYI是myisam引擎表的结构文件，数据文件，索引文件

MEMORY

MEMORY存储引擎将表中的数据存储到内存中，为查询和引用其他表数据提供快速访问。由于是在内存上操作数据，所以会导致数据丢失。

Mysql中的中间表就是用Memory引擎的, 所以数据库优化中有一条是尽量和用联合索引代替中间表

存储引擎的选择

• 如果要提供提交、回滚、崩溃恢复能力的事物安全（ACID兼容）能力，并要求实现并发控制，InnoDB是一个好的选择
• 如果数据表主要用来插入和查询记录，则MyISAM引擎能提供较高的处理效率
• 如果只是临时存放数据，数据量不大，并且不需要较高的数据安全性，可以选择将数据保存在内存中的Memory引擎，MySQL中使用该引擎作为临时表，存放查询的中间结果
• 如果只有INSERT和SELECT操作，可以选择Archive，Archive支持高并发的插入操作，但是本身不是事务安全的。Archive非常适合存储归档数据，如记录日志信息可以使用Archive

数据库索引

索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。如果想按特定职员的姓来查找他或她，则与在表中搜索所有的行相比，索引有助于更快地获取信息。

索引类型

单列索引

一次索引只包含一个列, 一个表里可以有多个单列索引

1. 主键索引
2. 唯一索引

CREAT UNIQUE INDEX 索引名 ON 表名(字段名)

3.普通索引

CREAT INDEX 索引名 ON 表名(字段名)

组合索引

包含两个或两个以上的列

CREAT INDEX 索引名 ON 表名(字段A,字段B,字段C)

注: 查询时遵循mysql组合查询的 “最左前缀”,

删除语句:

DORP INDEX indexName ON TableName

聚集索引（也叫 聚簇索引、主键索引）

一个表只能拥有一个聚集索引，而且是建立在主键上面的，主键一般采用自增ID和GUID等方式。自增ID由于是有序的，而且占用字节较少。所以在性能和空间上都比较有优势。

加入索引的好处和坏处

优点

• 可以通过建立唯一索引或主键索引, 保证每行数据的唯一性
• 建立索引可以大大提高检索的速度, 减少表的检索行数
• 可以加速表与表的相连速度
• 可以减少 分组( group by )/排序( order by )所消耗的时间
• 通过索引对数据进行排序，降低数据排序的成本，降低了CPU的消耗

缺点

• 创建索引和维护索引需要耗费时间和消耗内存
• 索引文件会占用物理空间
• 当表内进行 insert / update / delete操作时, 索引也需要动态的维护, 所以会降低维护的效率

加入索引要注意的地方

需要考虑哪些列上需要, 哪些列上不需要

• 在常常被搜索到的列上, 加索引可以提高查询速度
• 主键列上可以确保列的唯一性
• 表和表连接的条件上加索引, 可以加快连接查询的速度
• 经常需要排序 order by / 分组 group by / 去重 distinct 加索引可以加快排序查询的时间
• 索引不包含null值

什么情况下最好加(或不加)索引?

• 主键自动建立唯一索引
• 频繁作为查询条件的字段应该创建索引
• 查询中与其他表关联的字段，外键关系建立索引
• 频繁更新的字段不适合建立索引，因为每次更新不单单更新了记录还会更新索引
• WHERE条件里用不到的字段不创建索引
• 单键和组合索引的选择问题，（高并发情况下适合创建组合索引）
• 查询中排序的字段，排序的字段若通过索引去访问将大大提高排序速度
• 查询中统计或者分组字段

不适合使用索引的场合 :

• 对经常更新的表就避免对其进行过多的索引
• 数据量小的表最好不要使用索引，由于数据较少，可能查询全部数据时间比遍历索引的时间还要短，索引就可能不会产生优化效果
• 在不同值少的列上不要建立索引

索引失效的场景和原因

1. 列上有计算的时候
2. 非最左前缀查询
3. 范围查询( >右边的会失效 )
4. like位于%的右面时
5. 跳过某个数据时 ( < > 时 )
6. 列类型是字符串，查询条件未加引号
7. 未使用该列(被索引的列)作为查询条件
8. 在查询条件中使用OR ( 要想是索引生效，需要将or中的每个列都加上索引 ).

原因

此处使用一个联合索引失效的具体场景做例子

需要先了解的知识点 :

最左前缀法则:

联合索引的排序 先排序第一个字段去排序, 如果第一个字段相同, 再按着第二个字段去排序…以此类推

如上图可知 :

2. 最左前缀失效: 二分查找法查找的时候 前面的元素没找到 ,所以索引失败 , 参考上面知识点 [ 只有第一个元素相等的情况下 , 第二个元素才是有序的 / 反之无序 ]
3. " > "右边的会索引失效:

由图可知 , 根据条件 a>1得到 a=2和a=3条件, 则b元素为1,4,1,2 是无序的所以不能索引

4. like关键字中 %xx 和 %xx% 索引失效: 同上 违反了最左前缀法则

索引的数据结构

此处主要讲解Mysql底层的索引数据结构: 也就是B+Tree
mysql中也有其他的数据结构, 比如hash / B树等 , 这些数据结构有个共同的问题, 是解决不掉(或者是效率极低)地进行范围查询( 要么就是多次回旋查找 )

数据库索引主要有Hash表、二叉树、红黑树、B树、B+树，我们MySQL使用的是B+树！

Hash索引

Hash索引(hash index)基于哈希表实现，对于每一行数据，存储引擎都会对所有的索引列计算一个hash码(hash code)，哈希码是一个较小的值，哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。如果hash码一样则会采用链表的形式存储，类似于HashMap，Hash索引适用于精准查询。

我们有一条SELECT id,name,age FROM t_user WHERE name=‘石小添’; 这样的一条SQL可以直接对石小添 按哈希算法算出来一个Hash值，通过该值找到对应的记录指针，通过记录指针找到表中的哪一行数据，最后比较name是否为石小添，以保证就是要查找的行。

但是如果我们有 SELECT id,name,age FROM t_user WHERE name>‘石小添’; 这样的一条SQL则无能为力，因为**Hash表支持快速的精确查询，但是不支持范围查询。**哈希索引只包含哈希值和行指针，而不存储字段值，所以不

• 能使用索引中的值来避免读取行
• 哈希索引数据并不是按照索引值顺序存储的，所以也就无法用于排序
• 哈希索引不支持部分索引列匹配查找，因为哈希索引始终是使用索引列的全部内容来计算哈希值
• 哈希索引只支持等值比较查询，不支持任何范围查询
• 访问哈希索引的数据非常快，除非有很多哈希冲突（不同的索引列值却有相同的哈希值）。当出现哈希冲突的时候，存储引擎必须遍历链表中所有的行指针，逐行进行比较，直到找到所有符合条件的行
• 如果哈希冲突很多的话，一些索引维护操作的代价也会很高。例如，如果在某个选择性很低（哈希冲突很多）的列上建立哈希索引，那么当从表中删除一行时，存储引擎需要遍历对应哈希值的链表中的每一行，找到并删除对应行的引用，冲突越多，代价越大

二叉树排序树

二叉树（Binary Tree）是每个结点最多有两个子树的树结构。通常子树被称作“左子树”（left subtree）和“右子树”（right subtree）。二叉树常被用于实现二叉查找树和二叉堆。

如果我们的数据是单边增长的最终二叉树可能成为一个链表，我们查询一个数据如下图

如果有一条SQLSELECT id,name,age FROM tb_user WHERE id=7 ，对该字段创建索引并且使用的是二叉树维护它会查找6次，速度和没有创建索引是一样的！
二叉树索引在索引字段是连续时或其他场景下性能很低，而且这棵树是不平衡的严重倾斜，我们就此引出红黑树

红黑树

红黑树（Red Black Tree）是一种含有红黑结点并能自平衡的二叉查找树，是一种平衡二叉树。红黑树的每个节点上都有存储表示节点的颜色，可以是红(Red)或黑(Black)

相同的，id列添加索引使用红黑树进行存储，如下图，我们会发现会做一个调整，使这棵树相对平衡，较小的值放上级节点左边，较大的值放上级节点右边

很显然我们使用红黑树之后相对于二叉树来说，这棵树更加平衡，搜索数据也更快，MySQL仍然不用该数据结构来维护索引数据是为什么？下边来分析一下，彻底搞定

红黑树弊端

• 目前表中有6条数据，所以需要将这六列存储起来使用红黑树维护，那么这棵树的高度h=4，分别为2、4、6、7四个节点，这里没有问题吧
• 我们实际项目中数据不可能只有几条，都是百万条，千万条数据，如果红黑树要维护百万条，千万条数据，那么这可红黑树的高度h=？，很好计算，如果我们要想表中存储100W条数据，也就是有100W个红黑节点，每个节点有2个分支，将整棵树撑满2^n=1000000，n就是h深度，自己算一下吧
• 通过上边的分析，我们发现使用红黑树维护索引数据，这棵树的深度太深，太深就~~~
• 如果你要查找的数据在叶子节点，那么查询的次数也是蛮多的

通过上边的红黑树我们可以发现数据越多数高度就越高，树越高查询数据需要的次数就越多，我们控制住树的高度，就可以控制查询的次数，这就是我们的B树要来完成的伟业，所以大家不妨喝杯茶，思考一下在红黑树的基础上将树的高度控制在3-5层，进而存储千万条数据，若是你，将如何？

B树

二叉树和红黑树都是一个节点上存储一个数据，而B数是在红黑树的基础上一个节点上存储多个数据，所谓的B-Tree，BTree，B树说的都是同一个东西，全称Balance-tree译为平衡多路查找树，平衡为左边和右边分布均匀。·多路为相对于二叉树而言，二叉树就是二路查找树，查找时只有两条路，而B-tree有多条路，即父节点有多个子节点

• 上边18、25、60是一个节点，20、23是一个节点，同一个节点上存储多个数据
• data是在该节点上存储的数据，如果是mysql中使用B树存储的就是数据的磁盘地址，就是我们要查找的那行数据在磁盘上的位置
• 到索引中找到对应的节点，在节点中找到对应的数据，再获取磁盘地址就可以找到这行数据

在看看B树取数据，我们第一次取4根节点直接找到数据，第二次取7，找两次确定所在节点

• B-Tree可以显著减少定位记录时所经历的中间过程，从而加快存取速度。这个数据结构一般用于数据库的索引，综合效率较高
• 关键字集合分布在整颗树中，任何一个关键字出现且只出现在一个结点中
• 节点中的数据从左到右依次排序
• 搜索有可能在非叶子结点结束，叶子结点就是出度为0的结点就是没有子结点的结点
• B-树的搜索，从根结点开始，对结点内的关键字（有序）序列进行二分查找，如果命中则结束，否则进入查询关键字所属范围的儿子结点；重复，直到所对应的儿子指针为空，或已经是叶子结点

B+Tree

B+Tree是B-Tree的变种，MySQL就是使用B+Tree作为索引数据结构，上图

• 非叶子节点并不存储数据
• 节点冗余，叶子结点包含所有的非叶子节点
• 数据在叶子结点存储，而且叶子结点之间有箭头指向

B+Tree为什么将数据存储到叶子结点进行冗余

data和节点都需要空间存储，如果将data移除，可以存出更多节点，MySQL中使用的B+Tree每一节点可以存储最多16KB数据可以通过SHOW GLOBAL STATUS LIKE ‘InnoDb_page_size’;这条SQL查询，在16KB的情况下MySQL使用B+Tree可以存储更多的索引元素，若表中id使用bigint当做索引占8Byte，同时使用6Byte记录该节点子节点位置那么一个索引字段占8+6=14Byte，16KB/14Byte=1170，每一个节点可以存储1170个元素

B+Tree叶子结点箭头是干嘛的

B+树中的非叶子节点会冗余一份在叶子节点中，并且叶子节点之间用指针相连，最开始的Hash不支持范围查询，二叉树树高很高，只有B树跟B+有的一比，B树一个节点可以存储多个元素，相对于红黑树整体的树高降低了，磁盘IO效率提高了。而B+树是B树的升级版，只是把非叶子节点冗余一下，这么做的好处是为了提高范围查找的效率。提高了的原因也无非是会有指针指向下一个节点的叶子节点

• 所有关键字都出现在叶子节点的链表中（稠密索引），且链表中的关键字恰好有序
• 在B-树基础上，为叶子节点增加链表指针，所有关键字都在叶子节点中出现，非叶子节点作为叶子节点的索引；
• B+树总是到叶子节点才命中数据不可能在非叶子节点命中
• 更适合文件索引系统
• 单一节点存储的元素更多，使得查询的IO次数更少，所以也就使得它更适合做为数据库MySQL的底层数据结构
• 解决了回旋查找问题 在范围查找的速度是非常高的

到这我们介绍了Hash、二叉树、红黑树、B-Tree、B+Tree每种数据结构，并且得出结论Mysql使用B+Tree作为维护索引的数据结构，可以提高查询索引时的磁盘IO效率，并且可以提高范围查询的效率，并且B+树里的元素也是有序的，接下来我们就说说Mysql中常见的两种存储引擎具体如何使用索引

Myisam存储引擎索引实现

CREATE TABLE `tb_myisam` (
  `id` int(11) NOT NULL,
  `col1` varchar(255) DEFAULT NULL,
  `col2` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

一个myisam表创建之后在磁盘上会有三个文件frm，MYD，MYI维护

• frm：存储表结构
• MYD：存储表数据
• MYI：存储表中索引

Myisam索引维护

InnoDB存储引擎索引实现

CREATE TABLE `tb_innodb` (
  `id` int(11) NOT NULL,
  `col1` varchar(255) DEFAULT NULL,
  `col2` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

一个InnoDB表创建之后在磁盘上会有两个文件frm，ibd维护

• frm：存储表结构
• ibd：存储表索引和数据

mysql主键自动创建索引，innodb搜索引擎按照主键排序

InnoDB存储引擎，表数据文件也就是ibd文件本身就是按照B+Tree组织的索引结构文件，叶节点包含了完整的数据记录

如果我们InnoDB没有索引怎么办，数据就无法存储了吗？

大家应该通过一句话InnoDB表必须有主键，并且这个主键建议使用整型自动递增的，如果你表中有主键那么会在主键上添加索引来维护，如果你创建表时没有指定主键，数据库会在你的表中找到唯一数据那列去维护，如果找不到这样的列，数据库默认会自己增加一列来维护。
??推荐使用整型原因首先整型存储所占空间较小，而且比较排序时较快，可能有些公司在使用UUID当做主键等，UUID是一个随机的字符串，在比较时需要先转换再比较而且占用空间大，所以不推荐使用
??推荐自动递增是因为我们叶子结点数据从左到右依次递增排序，在做范围查询时比较方便，如果你的数值是随机的就有可能修改树原有的结构，导致分裂，裂开造成性能影响

联合索引长什么样？

我们在开发项目时一般不创建单列索引，而是多个键创建联合索引，现在只要你把联合索引底层原理整明白，网上看的那些MySQL索引优化原则你就可以在底层原理上去理解

假设我们联合索引为(col1,col2,col3)，分别为上图绿色方格中的三行数据，分别根据col1，col2，col3三列排序，紫色为其他非索引字段，这里要明白的是联合索引是按照什么排序的，最左前缀法则，为什么索引会失效

mysql搜索引擎和方式

1. 通过主键索引搜索到对应索引的物理地址
2. 然后根据地址去查找索引的信息

InnoDB

1. 主索引树(实际是主键索引): 可以通过主键找到整行数据的全部信息
2. 辅助索引 : 辅助索引中 假设用user_name为索引的话, 只能在最底层找到名字对应的id, 在使用id通过主键索引获取该行数据的全部信息, 即可完成查询.

总结: InnoDB中如果只一次主索引就能查到的话 ,效率一般是大于MyISAM的 ; 如果需要辅助索引查询的话, 也就是查询两次, 可能效率会低于MyISAM.

两者的区别

事务方面 : InnoDB支持事务 MyISAM不支持 [ 这是mysql将默认存储引擎从MyISAM变成InnoDB的重要原因之一 ]

外键方面 : InnoDB支持 MyISAM不支持, 对一个包含外键的InnoDB表转为MyISAM表会失败

索引方面 : InnoDB是聚簇索引, 不支持全文索引, 但是InnoDB可以使用 sphinx插件 支持全文索引, 且效果非常好 ; MyISAM是非聚簇索引, 支持全文索引 .

锁粒度方面 : InnoDB最小粒度的是行锁 ; MyISAM最小粒度是表锁. [ 这也是mysql将默认存储引擎从MyISAM变成InnoDB的重要原因之一 ]

复合索引什么情况下失效(有实例)
MySql中的多列索引:

• 联合索引又叫复合索引。对于复合索引 : Mysql从左到右的使用索引中的字段，一个查询可以只使用索引中的一部份，但只能是最左侧部分。

• 例如索引是 key index (a,b,c) 可以支持 a | a,b| a,b,c3种组合进行查找，但不支持 b,c进行查找 , 当最左侧字段是常量引用时，索引就十分有效。

• 多列建索引比对每个列分别建索引更有优势，因为索引建立得越多就越占磁盘空间，在更新数据的时候速度会更慢。另外建立多列索引时，顺序也是需要注意的，应该将严格的索引放在前面，这样筛选的力度会更大，效率更高。

• 组合索引的生效原则是 : 从前往后依次使用生效，如果中间某个索引没有使用，那么断点前面的索引部分起作用，断点后面的索引没有起作用;

  如 where a=3 and b=45 and c=5. 这种三个索引顺序使用中间没有断点全部发挥作用;

  where a=3 and c=5 这种情况下b就是断点，a发挥了效果，c没有效果

  where b=3 and c=4 这种情况下a就是断点，在a后面的索引都没有发挥作用，这种写法联合索引没有发挥任何效果;

  where b=45 and a=3 and c=5 这个跟第一个一样，全部发挥作用，abc只要用上了就行，跟写的顺序无关

还需注意， (a,b,c)多列索引和 (a,c,b)是不一样的

例子:

• select * from mytable where a=3 and b=5 and c=4; abc三个索引都在where条件里面用到了，而且都发挥了作用
• select * from mytable where c=4 and b=6 and a=3; 这条语句列出来只想说明 mysql没有那么笨，where里面的条件顺序在查询之前会被mysql的查询优化器自动优化，效果跟上一句一样
• select * from mytable where a=3 and c=7; a用到索引，b没有用，所以c是没有用到索引效果的
• select * from mytable where a=3 and b>7 and c=3; a用到了，b也用到了，c没有用到，这个地方b是范围值，也算断点，只不过自身用到了索引
• select * from mytable where b=3 and c=4; 因为a索引没有使用，所以这里 bc都没有用上索引效果
• select * from mytable where a>4 and b=7 and c=9; a用到了 b没有使用，c没有使用
• select * from mytable where a=3 order by b; a用到了索引，b在结果排序中也用到了索引的效果，前面说了，a下面任意一段的b是排好序的
• select * from mytable where a=3 order by c; a用到了索引，但是这个地方c没有发挥排序效果，因为中间断点了，使用 explain 可以看到 filesort
• select * from mytable where b=3 order by a; b没有用到索引，排序中a也没有发挥索引效果

那如果我们分别在a和b上创建两个列索引，mysql的处理方式就不一样了，它会选择一个最严格的索引来进行检索，可以理解为检索能力最强的那个索引来检索，另外一个利用不上了，这样效果就不如多列索引了。

关于 in 是否能触发索引?

答 : 可能会用到索引

1. IN 的条件过多，会导致索引失效，走索引扫描
2. IN 的条件过多，返回的数据会很多，可能会导致应用堆内内存溢出。

事务

事务（Transaction），一般是指要做的或所做的事情。在计算机术语中是指访问并可能更新数据库中各种数据项的一个程序执行单元(unit)。事务通常由高级数据库操纵语言或编程语言（如SQL，C++或Java）书写的用户程序的执行所引起，并用形如begin transaction和end transaction语句（或函数调用）来界定。事务由事务开始(begin transaction)和事务结束(end transaction)之间执行的全体操作组成。

事务的特点: 要么全部完成， 要么全部失败！

事务的四大特性

ACID 记忆方法 A 代表原子 C一致 I 分割线 所以是隔离性 D 持久性

一致性

事务执行后，数据库状态与其它业务规则保持一致。如转账业务，无论事务执行成功与否，参与转账的两个账号余额之和应该是不变的，可以理解为事务前后数据库内的逻辑永远是对的；

其他三特性都是为了一致性

持久性

一旦事务提交成功，事务中所有的数据操作都必须被持久化到数据库中，即使提交事务后，数据库马上崩溃，在数据库重启时，也必须能保证通过某种机制恢复数据。

redo log 保证了持久性

原子性

事务是不可再分割的原子单位。事务中所有操作要么全部执行成功，要么全部执行失败。

undo log 保证了原子性

隔离性

隔离性是指在并发操作中，不同事务之间应该隔离开来，使每个并发中的事务不会相互干扰。

mvcc和next-lock保证了隔离性

隔离事务

如果不隔离会出现的问题?

• 脏读: A事务读到B事物未提交的数据
• 不可重复读: 在一个事务范围内多次查询的结果不同, 数据被另一个事务修改并提交了
• 幻读: ( 幻读是多个事务执行时可能发生的现象 ) 假设T1事务将表里所有数据由1改成2 , 同时T2事务插入了一条新数据, T1执行完毕后一刷新发现多了一条没有被更改的, 以为自己出现了幻觉;这就是幻读

1. 幻读 和 不可重复读 都是读取了其他事务已提交的事务
2. 解决 不可重复读----> 锁行
3. 解决 幻读 -------> 锁表

四种隔离级别

• 读未提交( Read Uncommitted ) : select 语句不加锁 可能读到不一样的数据 并发最高 一致性最差

• 读已提交( Read Committed ) : 可避免脏读

  ( 高并发的情况下 几乎不会使用 如上两种隔离级别 )

• 可重复读 ( Repeatable read ): mysql 默认的隔离级别 可避免脏读 不可重复读

• 串行化( Serializable 隔离级别最高 ): 使用 锁表 的方式, 可避免脏读 不可重复读 幻读 ///但是 并发性最差 一致性最好

存储过程

存储过程了解即可

分表和分库

分库分表

为什么要分库和分表?

当我们遇到海量数据存储在数据库中的某张表，此时单个表承受的压力是巨大的。
这不但会导致查询变慢，而且由于表的锁机制导致更新修改删除等操作也会受到严重影响（即使是行级锁也会有影响），因此便出现了数据库性能瓶颈。

分表

分表是将一个大表按照一定的规则分解成多张具有独立存储空间的实体表，每个表都对应三个文件: MYD 数据文件，.MYI 索引文件，.frm 表结构文件。这些表可以分布在同一块磁盘上，也可以在不同的机器上。在应用访问数据库读写的时候根据事先定义好的规则得到对应的表名，然后去操作它。

将单个数据库表进行拆分，拆分成多个数据表，然后用户访问的时候，根据一定的算法(如用 hash 的方式，也可以用求余(取模)的方式)，让用户访问不同的表，这样数据分散到多个数据表中，减少了单个数据表的访问压力。单表的并发能力提高了，磁盘I/O性能也提高了，提升了数据库访问性能。分表的目的就在于此，减小数据库的负担，缩短查询时间。

水平分表

通常情况下，我们使用 hash、取模等方式来进行表的拆分。

与水平分库的思路类似，不过这次操作的目标是表，商品信息及商品描述被分成了两套表。如果商品ID为双数，将此操作映射至商品信息1表；如果商品ID为单数，将操作映射至商品信息2表。此操作要访问表名称的表达式为商品信息[商品ID%2 + 1] 。

水平分表是在同一个数据库内，把同一个表的数据按一定规则拆到多个表中。

垂直分表

垂直拆分更多时候就应该在数据表设计之初就执行的步骤，垂直切分是指数据表列的拆分，把一张列比较多的表拆分为多张表。
通常，我们按以下原则进行垂直拆分:

• 把不常用的字段单独放在一张表;
• 把 text，blob(binary large object，二进制大对象)等大字段拆分出来放在附表中;
• 经常组合查询的列放在一张表中;

undo & redo 日志

详解undo和redo

Undo Log

undo日志用于存放数据修改被修改前的值

假设修改 tba 表中 id=2的行数据，把Name=’B’ 修改为Name = ‘B2’ ，那么undo日志就会用来存放Name=’B’的记录，如果这个修改出现异常，可以使用undo日志来实现回滚操作，保证事务的一致性。

undo 默认内存 : 1G , truncate后的大小默认为10M

Undo Log的空间管理

innodb存储引擎对undo的管理采用段的方式。rollback segment称为回滚段，每个回滚段中有1024个undo log segment。

在以前老版本，只支持1个rollback segment，这样就只能记录1024个undo log segment。后来MySQL5.5可以支持128个rollback segment，即支持128*1024个undo操作，还可以通过变量 innodb_undo_logs (5.6版本以前该变量是 innodb_rollback_segments )自定义多少个rollback segment，默认值为128。

回滚段（rollback segment）分配如下：

• slot 0 ，预留给系统表空间；
• slot 1- 32，预留给临时表空间，每次数据库重启的时候，都会重建临时表空间；
• slot33-127，如果有独立表空间，则预留给UNDO独立表空间；如果没有，则预留给系统表空间；

Redo Log

当数据库对数据做修改的时候，需要把数据页从磁盘读到buffer pool中，然后在buffer pool中进行修改，那么这个时候buffer pool中的数据页就与磁盘上的数据页内容不一致，称buffer pool的数据页为dirty page 脏数据，如果这个时候发生非正常的DB服务重启，那么这些数据还没在内存，并没有同步到磁盘文件中（注意，同步到磁盘文件是个随机IO），也就是会发生数据丢失，如果这个时候，能够在有一个文件，当buffer pool 中的data page变更结束后，把相应修改记录记录到这个文件（注意，记录日志是顺序IO），那么当DB服务发生crash的情况，恢复DB的时候，也可以根据这个文件的记录内容，重新应用到磁盘文件，数据保持一致。

这个文件就是 Redo Log ，用于记录 数据修改后的记录，顺序记录。

它可以带来这些好处：

• 当buffer pool 中的 dirty page 还没有刷新到磁盘的时候，发生crash，启动服务后，可通过redo log 找到需要重新刷新到磁盘文件的记录；
• buffer pool 中的数据直接 flush 到 disk file，是一个随机IO，效率较差，而把buffer pool中的数据记录到redo log，是一个顺序IO，可以提高事务提交的速度；

假设修改 tba 表中 id=2的行数据，把Name=’B’ 修改为Name = ‘B2’ ，那么redo日志就会用来存放Name=’B2’的记录，如果这个修改在flush 到磁盘文件时出现异常，可以使用redo log实现重做操作，保证事务的持久性。

Undo + Redo事务的简化过程

假设有A、B两个数据，值分别为1,2，开始一个事务
事务的操作内容为：把1修改为3，2修改为4，那么实际的记录如下（简化）：

A.事务开始.
B.记录A=1到undo log.
C.修改A=3.
D.记录A=3到redo log.
E.记录B=2到undo log.
F.修改B=4.
G.记录B=4到redo log.
H.将redo log写入磁盘。
I.事务提交

事务恢复

未提交的事务和回滚了的事务也会记录在Redo Log中，因此在进行恢复时,这些事务要进行特殊的的处理。有2种不同的恢复策略：

1. 进行恢复时，只重做已经提交了的事务。
2. 进行恢复时，重做所有事务包括未提交的事务和回滚了的事务。然后通过Undo Log回滚那些
   未提交的事务。

MySQL数据库InnoDB存储引擎使用了B策略, InnoDB存储引擎中的恢复机制有几个特点：

• 在重做Redo Log时，并不关心事务性。 恢复时，没有BEGIN，也没有COMMIT,ROLLBACK的行为。也不关心每个日志是哪个事务的。尽管事务ID等事务相关的内容会记入Redo Log，这些内容只是被当作要操作的数据的一部分。
• 使用2策略就必须要将Undo Log持久化，而且必须要在写Redo Log之前将对应的Undo Log写入磁盘。Undo和Redo Log的这种关联，使得持久化变得复杂起来。为了降低复杂度，InnoDB将Undo Log看作数据，因此记录Undo Log的操作也会记录到redo log中。这样undo log就可以象数据一样缓存起来，而不用在redo log之前写入磁盘了。
  包含Undo Log操作的Redo Log，看起来是这样的：

 记录1: <trx1, Undo log insert <undo_insert …>>
 记录2: <trx1, insert …>
 记录3: <trx2, Undo log insert <undo_update …>>
 记录4: <trx2, update …>
 记录5: <trx3, Undo log insert <undo_delete …>>
 记录6: <trx3, delete …>

到这里，还有一个问题没有弄清楚。既然Redo没有事务性，那岂不是会重新执行被回滚了的事务？
确实是这样。同时Innodb也会将事务回滚时的操作也记录到redo log中。回滚操作本质上也是
对数据进行修改，因此回滚时对数据的操作也会记录到Redo Log中。
一个回滚了的事务的Redo Log，看起来是这样的：

	 记录1: <trx1, Undo log insert <undo_insert …>>
     记录2: <trx1, insert A…>
     记录3: <trx1, Undo log insert <undo_update …>>
     记录4: <trx1, update B…>
     记录5: <trx1, Undo log insert <undo_delete …>>
     记录6: <trx1, delete C…>
     记录7: <trx1, insert C> --------
     记录8: <trx1, update B to old value>
     记录9: <trx1, delete A>

锁相关

锁的类型

首先对mysql锁进行划分：

• 按照锁的粒度划分：行锁、表锁、页锁

• 按照锁的使用方式划分：共 享锁、排它锁（悲观锁的一种实现）

• 还有两种思想上的锁：悲观锁、乐观锁。

• InnoDB中有几种行级锁类型：Record Lock、Gap Lock、Next-key Lock

  ? Record Lock：在索引记录上加锁
  ? Gap Lock：间隙锁
  ? Next-key Lock：Record Lock+Gap Lock

行锁

行级锁是Mysql中锁定粒度最细的一种锁, 同时也只存在于InnoDB中，表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，但加锁的开销也最大。有可能会出现死锁的情况。 行级锁按照使用方式分为共享锁和排他锁。

行锁又分为共享锁和排它锁

共享锁用法（S锁 读锁）：

若事务T对数据对象A加上S锁，则事务T可以读A但不能修改A，其他事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁。这保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改。

select ... lock in share mode;

共享锁就是允许多个线程同时获取一个锁，一个锁可以同时被多个线程拥有。

排它锁用法（X 锁 写锁）：

若事务T对数据对象A加上X锁，事务T可以读A也可以修改A，其他事务不能再对A加任何锁，直到T释放A上的锁。这保证了其他事务在T释放A上的锁之前不能再读取和修改A。

排它锁，也称作独占锁，一个锁在某一时刻只能被一个线程占有，其它线程必须等待锁被释放之后才可能获取到锁。

• 对于普通 SELECT 语句，InnoDB 不会加任何锁；

• 对于 UPDATE、 DELETE 和 INSERT 语句， InnoDB会自动给涉及数据集加排他锁（X)；

• 事务可以通过以下语句显式给记录集加共享锁或排他锁：

  共享锁（S）：SELECT * FROM table_name WHERE …LOCK IN SHARE MODE。 其他 session 仍然可以查询记录，并也可以对该记录加share mode 的共享锁。但是如果当前事务需要对该记录进行更新操作，则很有可能造成死锁。

  排他锁（X)：SELECT * FROM table_name WHERE … FOR UPDATE。其他 session 可以查询该记录，但是不能对该记录加共享锁或排他锁，而是等待获得锁

意向锁

为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB 还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁：

• 意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的 IS 锁。
• 意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的 IX 锁。

考虑这个例子：

事务A锁住了表中的一行，让这一行只能读，不能写。

之后，事务B申请整个表的写锁。

如果事务B申请成功，那么理论上它就能修改表中的任意一行，这与A持有的行锁是冲突的。

数据库需要避免这种冲突，就是说要让B的申请被阻塞，直到A释放了行锁。

数据库要怎么判断这个冲突呢？

step1：判断表是否已被其他事务用表锁锁表

step2：判断表中的每一行是否已被行锁锁住。

注意step2，这样的判断方法效率实在不高，因为需要遍历整个表。

于是就有了意向锁。

在意向锁存在的情况下，事务A必须先申请表的意向共享锁，成功后再申请一行的行锁。

在意向锁存在的情况下，上面的判断可以改成

step1：不变

step2：发现表上有意向共享锁，说明表中有些行被共享行锁锁住了，因此，事务B申请表的写锁会被阻塞。

注意：申请意向锁的动作是数据库完成的，就是说，事务A申请一行的行锁的时候，数据库会自动先开始申请表的意向锁，不需要我们程序员使用代码来申请。

• 隐式锁定：

InnoDB在事务执行过程中，使用两阶段锁协议：

1. 随时都可以执行锁定，InnoDB会根据隔离级别在需要的时候自动加锁；
2. 锁只有在执行commit或者rollback的时候才会释放，并且所有的锁都是在同一时刻被释放。

• 显式锁定 ：

select ... lock in share mode //共享锁 
select ... for update //排他锁 

select lock in share mode : in share mode 子句的作用就是将查找到的数据加上一个 share 锁，这个就是表示其他的事务只能对这些数据进行简单的select 操作，并不能够进行 DML 操作。select *** lock in share mode使用场景：为了确保自己查到的数据没有被其他的事务正在修改，也就是说确保查到的数据是最新的数据，并且不允许其他人来修改数据。但是自己不一定能够修改数据，因为有可能其他的事务也对这些数据 使用了 in share mode 的方式上了 S 锁。

select *** for update 的使用场景：为了让自己查到的数据确保是最新数据，并且查到后的数据只允许自己来修改的时候，需要用到 for update 子句。

for update 和 lock in share mode 的区别：

• 前一个上的是排他锁（X 锁），一旦一个事务获取了这个锁，其他的事务是没法在这些数据上执行 for update ；
• 后一个是共享锁，多个事务可以同时的对相同数据执行 lock in share mode。

InnoDB 行锁实现方式

• InnoDB 行锁是通过给索引上的索引项加锁来实现的，这一点 MySQL 与 Oracle 不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB 这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB 才使用行级锁，否则，InnoDB 将使用表锁！
• 不论是使用主键索引、唯一索引或普通索引，InnoDB 都会使用行锁来对数据加锁。
• 只有执行计划真正使用了索引，才能使用行锁：即便在条件中使用了索引字段，但是否使用索引来检索数据是由 MySQL 通过判断不同执行计划的代价来决定的，如果 MySQL 认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下 InnoDB 将使用表锁，而不是行锁。因此，在分析锁冲突时，别忘了检查 SQL 的执行计划（可以通过 explain 检查 SQL 的执行计划），以确认是否真正使用了索引。
• 由于 MySQL 的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然多个session是访问不同行的记录， 但是如果是使用相同的索引键， 是会出现锁冲突的（后使用这些索引的session需要等待先使用索引的session释放锁后，才能获取锁）。 应用设计的时候要注意这一点。

表锁

表级锁是mysql锁中粒度最大的一种锁，表示当前的操作对整张表加锁，资源开销比行锁少，不会出现死锁的情况，但是发生锁冲突的概率很大。被大部分的mysql引擎支持，MyISAM和InnoDB都支持表级锁，但是InnoDB默认的是行级锁。

共享锁用法：

LOCK TABLE table_name [ AS alias_name ] READ

排它锁用法：

LOCK TABLE table_name [AS alias_name][ LOW_PRIORITY ] WRITE

解锁用法：

unlock tables;

页锁

页级锁是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录。BDB支持页级锁

乐观锁/悲观锁

乐观锁

乐观锁（ Optimistic Locking ） 相对悲观锁而言，乐观锁假设认为数据一般情况下不会造成冲突，所以在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则让返回用户错误的信息，让用户决定如何去做。

相对于悲观锁，在对数据库进行处理的时候，乐观锁并不会使用数据库提供的锁机制。一般的实现乐观锁的方式就是记录数据版本。

数据版本 : 为数据增加的一个版本标识。当读取数据时，将版本标识的值一同读出，数据每更新一次，同时对版本标识进行更新。当我们提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的版本标识进行比对，如果数据库表当前版本号与第一次取出来的版本标识值相等，则予以更新，否则认为是过期数据。

乐观锁在MySQL中的实现是mvcc

悲观锁

在关系数据库管理系统里，悲观并发控制（又名“悲观锁”，Pessimistic Concurrency Control，缩写“PCC”）是一种并发控制的方法。它可以阻止一个事务以影响其他用户的方式来修改数据。如果一个事务执行的操作对某行数据应用了锁，那只有当这个事务把锁释放，其他事务才能够执行与该锁冲突的操作。悲观并发控制主要用于数据争用激烈的环境，以及发生并发冲突时使用锁保护数据的成本要低于回滚事务的成本的环境中。

悲观锁在MySQL的主要实现是 锁机制

排他锁原理

在对任意记录进行修改前，先尝试为该记录加上排他锁（exclusive locking）

• 如果加锁失败，说明该记录正在被修改，那么当前查询可能要等待或者抛出异常。 具体响应方式由开发者根据实际需要决定。

• 如果成功加锁，那么就可以对记录做修改，事务完成后就会解锁了。

  其间如果有其他对该记录做修改或加排他锁的操作，都会等待我们解锁或直接抛出异常。

mysql中的autocommit默认开启，mysql 默认使用自动提交模式，也就是说当我们进行一个sql操作的时候，mysql会将这个操作当做一个事务并且自动提交这个操作。

1.开始事务
begin;/begin work;/start transaction; (三者选一就可以)
2.查询出商品信息
select ... for update;
3.提交事务
commit;/commit work;

通过下面的例子来说明：

窗口1：
mysql> set autocommit=0; #关闭自动提交
Query OK, 0 rows affected
我这里锁的是表
mysql> select * from user for update; #sql执行完成但是不会提交 那么锁也就不会释放
+----+-------+
| id | price |
+----+-------+
|  1 |   500 |
|  2 |   800 |
+----+-------+
2 rows in set

窗口2：
mysql> update user set price=price-100 where id=1;
执行上面操作的时候，会显示等待状态，一直到窗口1执行commit提交事务才会出现下面的显示结果
Database changed
Rows matched: 1  Changed: 1  Warnings: 0

窗口1：
mysql> commit; # 需要手动提交以后 释放锁 
Query OK, 0 rows affected
mysql> select * from user;
+----+-------+
| id | price |
+----+-------+
|  1 |   400 |
|  2 |   800 |
+----+-------+
2 rows in set

Record Lock、Gap Lock、Next-key Lock锁

Record Lock

单条索引上加锁，record lock 永远锁的是索引，而非数据本身，如果innodb表中没有索引，那么会自动创建一个隐藏的聚集索引，锁住的就是这个聚集索引。所以说当一条sql没有走任何索引时，那么将会在每一条聚集索引后面加X锁，这个类似于表锁，但原理上和表锁应该是完全不同的。

Gap Lock

间隙锁，是在索引的间隙之间加上锁，这是为什么Repeatable Read隔离级别下能防止幻读的主要原因。

InnoDB的间隙锁：

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）。

很显然，在使用范围条件检索并锁定记录时，InnoDB这种加锁机制会阻塞符合条件范围内键值的并发插入，这往往会造成严重的锁等待。因此，在实际应用开发中，尤其是并发插入比较多的应用，我们要尽量优化业务逻辑，尽量使用相等条件来访问更新数据，避免使用范围条件。

mysql> select * from product_copy;
+----+--------+-------+-----+
| id | name   | price | num |
+----+--------+-------+-----+
|  1 | 伊利   |    68 |   1 |
|  2 | 蒙牛   |    88 |   1 |
|  6 | tom    |  2788 |   3 |
| 10 | 优衣库 |   488 |   4 |
+----+--------+-------+-----+
其中id为主键 num为普通索引
窗口A：
mysql> select * from product_copy where num=3 for update;
+----+------+-------+-----+
| id | name | price | num |
+----+------+-------+-----+
|  6 | tom  |  2788 |   3 |
+----+------+-------+-----+
1 row in set

窗口B：
mysql> insert into product_copy values(5,'kris',1888,2);
这里会等待  直到窗口A commit才会显示下面结果
Query OK, 1 row affected

但是下面是不需要等待的
mysql> update product_copy set price=price+100 where num=1;
Query OK, 2 rows affected
Rows matched: 2  Changed: 2  Warnings: 0
mysql> insert into product_copy values(5,'kris',1888,5);
Query OK, 1 row affected

通过上面的例子可以看出Gap 锁的作用是在1,3的间隙之间加上了锁。而且并不是锁住了表，我更新num=1，5的数据是可以的.可以看出锁住的范围是（1,3]U[3,4)。

为什么说gap锁是RR隔离级别下防止幻读的主要原因?

解决幻读的方式很简单，就是需要当事务进行当前读的时候，保证其他事务不可以在满足当前读条件的范围内进行数据操作。

根据索引的有序性，我们可以从上面的例子推断出满足where条件的数据，只能插入在num=（1,3]U[3,4)两个区间里面，只要我们将这两个区间锁住，那么就不会发生幻读。

主键索引/唯一索引+当前读会加上Gap锁吗？

窗口A：
mysql> select * from product_copy where id=6 for update;
+----+------+-------+-----+
| id | name | price | num |
+----+------+-------+-----+
|  6 | tom  |  2788 |   3 |
+----+------+-------+-----+

窗口B：并不会发生等待
mysql> insert into product_copy values(5,'kris',1888,3);
Query OK, 1 row affected

例子说明的其实就是行锁的原因，我只将id=6的行数据锁住了，用Gap锁的原理来解释的话：因为主键索引和唯一索引的值只有一个，所以满足检索条件的只有一行，故并不会出现幻读，所以并不会加上Gap锁。

通过范围查询是否会加上Gap锁

窗口A：
mysql> select * from product_copy where num>3 for update;
+----+--------+-------+-----+
| id | name   | price | num |
+----+--------+-------+-----+
| 10 | 优衣库 |   488 |   4 |
+----+--------+-------+-----+

窗口B：会等待
mysql> insert into product_copy values(11,'kris',1888,5);
Query OK, 1 row affected
不会等待
mysql> insert into product_copy values(3,'kris',1888,2);
Query OK, 1 row affected

其实原因都是一样，只要满足检索条件的都会加上Gap锁

检索条件并不存在的当前读会加上Gap吗？

窗口A：
mysql> select * from product_copy where num=5 for update;
Empty set

窗口B：6 和 4都会等待
mysql> insert into product_copy values(11,'kris',1888,6);
Query OK, 1 row affected

mysql> insert into product_copy values(11,'kris',1888,4);
Query OK, 1 row affected

原因一样会锁住（4,5]U[5,n）的区间

范围查询不存在的数据

这里就会有点不一样

窗口A：
mysql> select * from product_copy where num>6 for update;
Empty set
窗口B：8 和 4 都会锁住
mysql> insert into product_copy values(11,'kris',1888,4);
Query OK, 1 row affected

mysql> insert into product_copy values(11,'kris',1888,8);
Query OK, 1 row affected

上面的2例子看出当你查询并不存在的数据的时候，mysql会将有可能出现区间全部锁住。

Next-Key Lock

这个锁机制其实就是前面两个锁相结合的机制，既锁住记录本身还锁住索引之间的间隙。

MVCC

MVCC 是什么？

MVCC 的英文全称是 Multiversion Concurrency Control ，中文意思是多版本并发控制技术。原理是，通过数据行的多个版本管理来实现数据库的并发控制，简单来说就是保存数据的历史版本。可以通过比较版本号决定数据是否显示出来。读取数据的时候不需要加锁可以保证事务的隔离效果。

MVCC的好处

MySQL InnoDB存储引擎，实现的是基于mvcc的

MVCC最大的好处：读不加锁，读写不冲突。在读多写少的OLTP应用中，读写不冲突是非常重要的，极大的增加了系统的并发性能，这也是为什么现阶段，几乎所有的RDBMS，都支持了MVCC。

MySQL 中 InnoDB 引擎支持 MVCC；

应对高并发事务, MVCC 比单纯的加行锁更有效, 开销更小；

MVCC 只在读已提交(Read Committed)和可重复读(Repeatable Read)隔离级别下起作用，其他两个隔离级别和MVCC不兼容；(对于 Serializable 隔离级别，是通过加锁互斥来访问数据，因此不需要 MVCC )

MVCC 既可以基于乐观锁又可以基于悲观锁来实现。

MVCC 用来解决什么问题？

一般解决不可重复读和幻读问题，是采用锁机制实现，有没有一种乐观锁的问题去处理？

可以采用 MVCC 机制的设计，可以用来解决这个问题。取代行锁，降低系统开销。

• 读写之间阻塞的问题，通过 MVCC 可以让读写互相不阻塞，读不相互阻塞，写不阻塞读，这样可以提升数据并发处理能力。
• 降低了死锁的概率，这个是因为 MVCC 采用了乐观锁的方式，读取数据时，不需要加锁，写操作，只需要锁定必要的行。
• 解决了一致性读的问题，当我们朝向某个数据库在时间点的快照是，只能看到这个时间点之前事务提交更新的结果，不能看到时间点之后事务提交的更新结果。

什么是快照读？

快照读，读取的是快照数据，不加锁的简单 Select 都属于快照读. 读到的可能是历史数据

SELECT * FROM player WHERE ...

什么是当前读？

当前读就是读的是最新数据,而不是历史的数据，加锁的 SELECT，或者对数据进行增删改都会进行当前读。

SELECT * FROM player LOCK IN SHARE MODE;
SELECT FROM player FOR UPDATE;
INSERT INTO player values ...
DELETE FROM player WHERE ...
UPDATE player SET ...

InnoDB 的 MVCC 是如何实现的？

InnoDB 是如何存储记录多个版本的？这些数据是 事务版本号 和 **行记录中的隐藏列 **和 Undo Log。

InnoDB 实现多版本控制 （MVCC）是通过 ReadView+ UndoLog 实现的，UndoLog 保存了历史快照，ReadView 规则帮助判断当前版本的数据是否可见。

事务版本号

每开启一个事务，都会从数据库中获得一个事务ID（也称为事务版本号），这个事务 ID 是自增的，通过 ID 大小，可以判断事务的时间顺序。

行记录的隐藏列

1. row_id :隐藏的行 ID ,用来生成默认的聚集索引。如果创建数据表时没指定聚集索引，这时 InnoDB 就会用这个隐藏 ID 来创建聚集索引。采用聚集索引的方式可以提升数据的查找效率。
2. trx_id: 操作这个数据事务 ID ，也就是最后一个对数据插入或者更新的事务 ID 。
3. roll_ptr:回滚指针，指向这个记录的 Undo Log 信息。

Undo Log

InnoDB 将行记录快照保存在 Undo Log 里。

数据行通过快照记录都通过链表的结构的串联了起来，每个快照都保存了 trx_id 事务ID，如果要找到历史快照，就可以通过遍历回滚指针的方式进行查找。

Read View 是啥？

如果一个事务要查询行记录，需要读取哪个版本的行记录呢？ Read View 就是来解决这个问题的。Read View 可以帮助我们解决可见性问题。 Read View 保存了当前事务开启时所有活跃的事务列表。

Read View就是事务进行快照读操作的时候生产的读视图(Read View)，在该事务执行的快照读的那一刻，会生成数据库系统当前的一个快照，记录并维护系统当前活跃事务的ID(当每个事务开启时，都会被分配一个ID, 这个ID是递增的，所以最新的事务，ID值越大)

1. trx_ids 系统当前正在活跃的事务ID集合。
2. low_limit_id ,活跃事务的最大的事务 ID。
3. up_limit_id 活跃的事务中最小的事务 ID。
4. creator_trx_id，创建这个 ReadView 的事务ID。

如果当前事务的 creator_trx_id 想要读取某个行记录，这个行记录ID 的trx_id ，这样会有以下的情况：

• 如果 trx_id < 活跃的最小事务ID（up_limit_id）,也就是说这个行记录在这些活跃的事务创建前就已经提交了，那么这个行记录对当前事务是可见的。
• 如果trx_id > 活跃的最大事务ID（low_limit_id），这个说明行记录在这些活跃的事务之后才创建，说明这个行记录对当前事务是不可见的。
• 如果 up_limit_id < trx_id <low_limit_id,说明该记录需要在 trx_ids 集合中，可能还处于活跃状态，因此我们需要在 trx_ids 集合中遍历 ，如果trx_id 存在于 trx_ids 集合中，证明这个事务 trx_id 还处于活跃状态，不可见，否则 ，trx_id 不存在于 trx_ids 集合中，说明事务trx_id 已经提交了，这行记录是可见的。

怎么实现不可重复读的

如果事务隔离级别是 ReadCommit ，一个事务的每一次 Select 都会去查一次ReadView ，每次查询的Read View 不同，就可能会造成不可重复读或者幻读的情况。

如果事务的隔离级别是可重读，为了避免不可重读读，一个事务只在第一次 Select 的时候会获取一次Read View ，然后后面索引的Select 会复用这个 ReadView.

幻读的解决方式

MVCC的整体流程

我们在了解了隐式字段，undo log， 以及Read View的概念之后，就可以来看看MVCC实现的整体流程是怎么样了

整体的流程是怎么样的呢？我们可以模拟一下

• 当事务2对某行数据执行了快照读，数据库为该行数据生成一个Read View读视图，假设当前事务ID为2，此时还有事务1和事务3在活跃中，事务4在事务2快照读前一刻提交更新了，所以Read View记录了系统当前活跃事务1，3的ID，维护在一个列表上，假设我们称为trx_list

• Read View不仅仅会通过一个列表trx_list来维护事务2执行快照读那刻系统正活跃的事务ID，还会有两个属性up_limit_id（记录trx_list列表中事务ID最小的ID），low_limit_id(记录trx_list列表中事务ID最大的ID，也有人说快照读那刻系统尚未分配的下一个事务ID也就是目前已出现过的事务ID的最大值+1）；所以在这里例子中up_limit_id就是1，low_limit_id就是4 + 1 = 5，trx_list集合的值是1,3，Read View如下图

• 我们的例子中，只有事务4修改过该行记录，并在事务2执行快照读前，就提交了事务，所以当前该行当前数据的undo log如下图所示；我们的事务2在快照读该行记录的时候，就会拿该行记录的DB_TRX_ID去跟up_limit_id,low_limit_id和活跃事务ID列表(trx_list)进行比较，判断当前事务2能看到该记录的版本是哪个。

• 所以先拿该记录DB_TRX_ID字段记录的事务ID 4去跟Read View的的up_limit_id比较，看4是否小于up_limit_id(1)，所以不符合条件，继续判断 4 是否大于等于 low_limit_id(5)，也不符合条件，最后判断4是否处于trx_list中的活跃事务, 最后发现事务ID为4的事务不在当前活跃事务列表中, 符合可见性条件，所以事务4修改后提交的最新结果对事务2快照读时是可见的，所以事务2能读到的最新数据记录是事务4所提交的版本，而事务4提交的版本也是全局角度上最新的版本

• 也正是Read View生成时机的不同，从而造成RC,RR级别下快照读的结果的不同

MVCC详解

Mysql默认隔离级别为什么是可重复读？

我们先来思考一个问题，在Oracle，SqlServer，postgresql中都是选择读已提交作为默认的隔离级别，为什么Mysql不选择Read Commited作为默认隔离级别，而选择Repeatable Read作为默认的隔离级别呢？

这个是有历史原因的，当然要从我们的主从复制开始讲起了！
主从复制，是基于什么复制的？
是基于binlog复制的！这里不想去搬binlog的概念了，就简单理解为binlog是一个记录数据库更改的文件吧～
binlog有几种格式？
OK，三种，分别是

• statement:记录的是修改SQL语句
• row：记录的是每行实际数据的变更
• mixed：statement和row模式的混合

那Mysql在5.0这个版本以前，binlog只支持STATEMENT这种格式！而这种格式在读已提交(Read Commited)这个隔离级别下主从复制是有bug的，因此Mysql将可重复读(Repeatable Read)作为默认的隔离级别！
接下来，就要说说当binlog为STATEMENT格式，且隔离级别为读已提交(Read Commited)时，有什么bug呢？如下图所示，在主(master)上执行如下事务

此时在主(master)上执行下列语句

select * from test；

输出如下

+---+
| b |
+---+
| 3 |
+---+
1 row in set

但是，你在此时在从(slave)上执行该语句，得出输出如下

Empty set

这样，你就出现了主从不一致性的问题！原因其实很简单，就是在master上执行的顺序为先删后插！而此时binlog为STATEMENT格式，它记录的顺序为先插后删！从(slave)同步的是binglog，因此从机执行的顺序和主机不一致！就会出现主从不一致！
如何解决？
解决方案有两种！
(1)隔离级别设为可重复读(Repeatable Read),在该隔离级别下引入间隙锁。当Session 1执行delete语句时，会锁住间隙。那么，Ssession 2执行插入语句就会阻塞住！
(2)将binglog的格式修改为row格式，此时是基于行的复制，自然就不会出现sql执行顺序不一样的问题！奈何这个格式在mysql5.1版本开始才引入。因此由于历史原因，mysql将默认的隔离级别设为可重复读(Repeatable Read)，保证主从复制不出问题！

在RR隔离级别下，存在间隙锁，导致出现死锁的几率比RC大的多！

在RR隔离级别下，条件列未命中索引会锁表！而在RC隔离级别下，只锁行

在RC隔离级别下，引入半一致性读(semi-consistent)特性增加了update操作的性能！

• 在RC级别下，不可重复读问题需要解决么？
  不用解决，这个问题是可以接受的！毕竟你数据都已经提交了，读出来本身就没有太大问题！Oracle和Postgressql的默认隔离级别就是RC，你们改过他们的默认隔离级别么？
• 在RC级别下，主从复制用什么binlog格式？
  OK,在该隔离级别下，用的binlog为row格式，是基于行的复制！Innodb的创始人也是建议binlog使用该格式！

互联网项目请用：读已提交(Read Commited)这个隔离级别！