MVCC多版本并发控制
 
 
如果觉得对你有帮助，能否点个赞或关个注，以示鼓励笔者呢？！博客目录 | 先点这里
 
！首先声明，MySQL 的测试环境是 5.7
 
前提概要 
  
什么是 MVCC
什么是当前读和快照读？
当前读，快照读和 MVCC 的关系
 
MVCC 实现原理 
  
隐式字段
undo日志
Read View
整体流程
 
MVCC 相关问题 
  
RR 是如何在 RC 级的基础上解决不可重复读的？
RC, RR 级别下的 InnoDB 快照读有什么不同？
 
 

 
 前提概要
 
 
 什么是 MVCC ?
 
 
 
MVCC
 MVCC，全称 Multi-Version Concurrency Control ，即多版本并发控制。MVCC 是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问，在编程语言中实现事务内存。
 mvcc - @百度百科
 
 
MVCC 在 MySQL InnoDB 中的实现主要是为了提高数据库并发性能，用更好的方式去处理读-写冲突，做到即使有读写冲突时，也能做到不加锁，非阻塞并发读
 
 
什么是当前读和快照读？
 
在学习 MVCC 多版本并发控制之前，我们必须先了解一下，什么是 MySQL InnoDB 下的当前读和快照读?
 
 
当前读
 像 select lock in share mode (共享锁), select for update; update; insert; delete (排他锁)这些操作都是一种当前读，为什么叫当前读？就是它读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁
 
 
快照读
 像不加锁的 select 操作就是快照读，即不加锁的非阻塞读；快照读的前提是隔离级别不是串行级别，串行级别下的快照读会退化成当前读；之所以出现快照读的情况，是基于提高并发性能的考虑，快照读的实现是基于多版本并发控制，即 MVCC ,可以认为 MVCC 是行锁的一个变种，但它在很多情况下，避免了加锁操作，降低了开销；既然是基于多版本，即快照读可能读到的并不一定是数据的最新版本，而有可能是之前的历史版本
 
 
说白了 MVCC 就是为了实现读-写冲突不加锁，而这个读指的就是快照读, 而非当前读，当前读实际上是一种加锁的操作，是悲观锁的实现
 
 
当前读，快照读和MVCC的关系
 
MVCC 多版本并发控制是 「维持一个数据的多个版本，使得读写操作没有冲突」 的概念，只是一个抽象概念，并非实现
因为 MVCC 只是一个抽象概念，要实现这么一个概念，MySQL 就需要提供具体的功能去实现它，「快照读就是 MySQL 实现 MVCC 理想模型的其中一个非阻塞读功能」。而相对而言，当前读就是悲观锁的具体功能实现
要说的再细致一些，快照读本身也是一个抽象概念，再深入研究。MVCC 模型在 MySQL 中的具体实现则是由 3 个隐式字段，undo 日志 ，Read View 等去完成的，具体可以看下面的 MVCC 实现原理
 
 
MVCC 能解决什么问题，好处是？
 
数据库并发场景有三种，分别为：
 
读-读：不存在任何问题，也不需要并发控制
读-写：有线程安全问题，可能会造成事务隔离性问题，可能遇到脏读，幻读，不可重复读
写-写：有线程安全问题，可能会存在更新丢失问题，比如第一类更新丢失，第二类更新丢失
 
MVCC 带来的好处是？
 多版本并发控制（MVCC）是一种用来解决读-写冲突的无锁并发控制，也就是为事务分配单向增长的时间戳，为每个修改保存一个版本，版本与事务时间戳关联，读操作只读该事务开始前的数据库的快照。 所以 MVCC 可以为数据库解决以下问题
 
在并发读写数据库时，可以做到在读操作时不用阻塞写操作，写操作也不用阻塞读操作，提高了数据库并发读写的性能
同时还可以解决脏读，幻读，不可重复读等事务隔离问题，但不能解决更新丢失问题
 
小结一下咯
 简而言之，MVCC 就是因为大佬们，不满意只让数据库采用悲观锁这样性能不佳的形式去解决读-写冲突问题，而提出的解决方案，所以在数据库中，因为有了 MVCC，所以我们可以形成两个组合：
 
MVCC + 悲观锁
 MVCC解决读写冲突，悲观锁解决写写冲突
MVCC + 乐观锁
 MVCC 解决读写冲突，乐观锁解决写写冲突
 
这种组合的方式就可以最大程度的提高数据库并发性能，并解决读写冲突，和写写冲突导致的问题
 

 
 MVCC 的实现原理 
 
 
MVCC 的目的就是多版本并发控制，在数据库中的实现，就是为了解决读写冲突，它的实现原理主要是依赖记录中的 3个隐式字段，undo日志 ，Read View 来实现的。所以我们先来看看这个三个 point 的概念
 
 隐式字段
 
每行记录除了我们自定义的字段外，还有数据库隐式定义的 DB_TRX_ID, DB_ROLL_PTR, DB_ROW_ID 等字段
 
DB_TRX_ID
 6 byte，最近修改(修改/插入)事务 ID：记录创建这条记录/最后一次修改该记录的事务 ID
DB_ROLL_PTR
 7 byte，回滚指针，指向这条记录的上一个版本（存储于 rollback segment 里）
DB_ROW_ID
 6 byte，隐含的自增 ID（隐藏主键），如果数据表没有主键，InnoDB 会自动以DB_ROW_ID产生一个聚簇索引
实际还有一个删除 flag 隐藏字段, 既记录被更新或删除并不代表真的删除，而是删除 flag 变了
 

 如上图，DB_ROW_ID 是数据库默认为该行记录生成的唯一隐式主键，DB_TRX_ID 是当前操作该记录的事务 ID ,而 DB_ROLL_PTR 是一个回滚指针，用于配合 undo日志，指向上一个旧版本
 
 
undo日志
 
undo log 主要分为两种：
 
insert undo log
 代表事务在 insert 新记录时产生的 undo log, 只在事务回滚时需要，并且在事务提交后可以被立即丢弃
update undo log
 事务在进行 update 或 delete 时产生的 undo log ; 不仅在事务回滚时需要，在快照读时也需要；所以不能随便删除，只有在快速读或事务回滚不涉及该日志时，对应的日志才会被 purge 线程统一清除
 
 
 
purge
 
 
 
从前面的分析可以看出，为了实现 InnoDB 的 MVCC 机制，更新或者删除操作都只是设置一下老记录的 deleted_bit ，并不真正将过时的记录删除。
 
 
为了节省磁盘空间，InnoDB 有专门的 purge 线程来清理 deleted_bit 为 true 的记录。为了不影响 MVCC 的正常工作，purge 线程自己也维护了一个read view（这个 read view 相当于系统中最老活跃事务的 read view ）;如果某个记录的 deleted_bit 为 true ，并且 DB_TRX_ID 相对于 purge 线程的 read view 可见，那么这条记录一定是可以被安全清除的。
 
 
 
对 MVCC 有帮助的实质是 update undo log ，undo log 实际上就是存在 rollback segment 中旧记录链，它的执行流程如下：
 
一、 比如一个有个事务插入 persion 表插入了一条新记录，记录如下，name 为 Jerry , age 为 24 岁，隐式主键是 1，事务 ID和回滚指针，我们假设为 NULL
 
 
  
 
二、 现在来了一个事务 1对该记录的 name 做出了修改，改为 Tom
 
在事务 1修改该行(记录)数据时，数据库会先对该行加排他锁
然后把该行数据拷贝到 undo log 中，作为旧记录，既在 undo log 中有当前行的拷贝副本
拷贝完毕后，修改该行name为Tom，并且修改隐藏字段的事务 ID 为当前事务 1的 ID, 我们默认从 1 开始，之后递增，回滚指针指向拷贝到 undo log 的副本记录，既表示我的上一个版本就是它
事务提交后，释放锁
 
 
  
 
三、 又来了个事务 2修改person 表的同一个记录，将age修改为 30 岁
 
在事务2修改该行数据时，数据库也先为该行加锁
然后把该行数据拷贝到 undo log 中，作为旧记录，发现该行记录已经有 undo log 了，那么最新的旧数据作为链表的表头，插在该行记录的 undo log 最前面
修改该行 age 为 30 岁，并且修改隐藏字段的事务 ID 为当前事务 2的 ID, 那就是 2 ，回滚指针指向刚刚拷贝到 undo log 的副本记录
事务提交，释放锁
 
 
  
 
从上面，我们就可以看出，不同事务或者相同事务的对同一记录的修改，会导致该记录的undo log成为一条记录版本线性表，既链表，undo log 的链首就是最新的旧记录，链尾就是最早的旧记录（当然就像之前说的该 undo log 的节点可能是会 purge 线程清除掉，向图中的第一条 insert undo log，其实在事务提交之后可能就被删除丢失了，不过这里为了演示，所以还放在这里）
 
 
 Read View 读视图
 
什么是 Read View?
 
什么是 Read View，说白了 Read View 就是事务进行快照读操作的时候生产的读视图 (Read View)，在该事务执行的快照读的那一刻，会生成数据库系统当前的一个快照，记录并维护系统当前活跃事务的 ID (当每个事务开启时，都会被分配一个 ID , 这个 ID 是递增的，所以最新的事务，ID 值越大)
 
所以我们知道 Read View 主要是用来做可见性判断的, 即当我们某个事务执行快照读的时候，对该记录创建一个 Read View 读视图，把它比作条件用来判断当前事务能够看到哪个版本的数据，既可能是当前最新的数据，也有可能是该行记录的undo log里面的某个版本的数据。
 
Read View遵循一个可见性算法，主要是将要被修改的数据的最新记录中的 DB_TRX_ID（即当前事务 ID ）取出来，与系统当前其他活跃事务的 ID 去对比（由 Read View 维护），如果 DB_TRX_ID 跟 Read View 的属性做了某些比较，不符合可见性，那就通过 DB_ROLL_PTR 回滚指针去取出 Undo Log 中的 DB_TRX_ID 再比较，即遍历链表的 DB_TRX_ID（从链首到链尾，即从最近的一次修改查起），直到找到满足特定条件的 DB_TRX_ID , 那么这个 DB_TRX_ID 所在的旧记录就是当前事务能看见的最新老版本
 
那么这个判断条件是什么呢？
 
 我们这里盗窃@呵呵一笑百媚生一张源码图，如上，它是一段 MySQL 判断可见性的一段源码，即 changes_visible 方法（不完全哈，但能看出大致逻辑），该方法展示了我们拿 DB_TRX_ID 去跟 Read View 某些属性进行怎么样的比较
 
在展示之前，我先简化一下 Read View，我们可以把 Read View 简单的理解成有三个全局属性
 
 
 
trx_list（名称我随意取的） 
   
一个数值列表
用于维护 Read View 生成时刻系统 正活跃的事务 ID 列表
 
up_limit_id 
   
lower water remark
是 trx_list 列表中事务 ID 最小的 ID
 
low_limit_id 
   
hight water mark
ReadView 生成时刻系统尚未分配的下一个事务 ID ，也就是 目前已出现过的事务 ID 的最大值 + 1
为什么是 low_limit ? 因为它也是系统此刻可分配的事务 ID 的最小值
 
 
 
首先比较 DB_TRX_ID < up_limit_id , 如果小于，则当前事务能看到 DB_TRX_ID 所在的记录，如果大于等于进入下一个判断
接下来判断 DB_TRX_ID >= low_limit_id , 如果大于等于则代表 DB_TRX_ID 所在的记录在 Read View 生成后才出现的，那对当前事务肯定不可见，如果小于则进入下一个判断
判断 DB_TRX_ID 是否在活跃事务之中，trx_list.contains (DB_TRX_ID)，如果在，则代表我 Read View 生成时刻，你这个事务还在活跃，还没有 Commit，你修改的数据，我当前事务也是看不见的；如果不在，则说明，你这个事务在 Read View 生成之前就已经 Commit 了，你修改的结果，我当前事务是能看见的
 
 
整体流程
 
我们在了解了 隐式字段，undo log， 以及 Read View 的概念之后，就可以来看看 MVCC 实现的整体流程是怎么样了
 
整体的流程是怎么样的呢？我们可以模拟一下
 
当事务 2对某行数据执行了快照读，数据库为该行数据生成一个Read View读视图，假设当前事务 ID 为 2，此时还有事务1和事务3在活跃中，事务 4在事务 2快照读前一刻提交更新了，所以 Read View 记录了系统当前活跃事务 1，3 的 ID，维护在一个列表上，假设我们称为trx_list
 
事务 1
事务 2
事务 3
事务 4
事务开始
事务开始
事务开始
事务开始
…
…
…
修改且已提交
进行中
快照读
进行中
…
…
…

Read View 不仅仅会通过一个列表 trx_list 来维护事务 2执行快照读那刻系统正活跃的事务 ID 列表，还会有两个属性 up_limit_id（ trx_list 列表中事务 ID 最小的 ID ），low_limit_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 相关问题
 
 
RR 是如何在 RC 级的基础上解决不可重复读的？
 
当前读和快照读在 RR 级别下的区别：
 
表1:
 
事务A
事务B
开启事务
开启事务
快照读(无影响)查询金额为500
快照读查询金额为500
更新金额为400
提交事务
select 快照读金额为500
select lock in share mode当前读金额为400
在上表的顺序下，事务 B 的在事务 A 提交修改后的快照读是旧版本数据，而当前读是实时新数据 400
 
表2:
 
事务A
事务B
开启事务
开启事务
快照读（无影响）查询金额为500
更新金额为400
提交事务
select 快照读金额为400
select lock in share mode当前读金额为400
而在表 2这里的顺序中，事务 B 在事务 A 提交后的快照读和当前读都是实时的新数据 400，这是为什么呢？
 
这里与上表的唯一区别仅仅是表 1的事务 B 在事务 A 修改金额前快照读过一次金额数据，而表 2的事务B在事务A修改金额前没有进行过快照读。
 
所以我们知道事务中快照读的结果是非常依赖该事务首次出现快照读的地方，即某个事务中首次出现快照读的地方非常关键，它有决定该事务后续快照读结果的能力
 
我们这里测试的是更新，同时删除和更新也是一样的，如果事务B的快照读是在事务A操作之后进行的，事务B的快照读也是能读取到最新的数据的
 
 
RC , RR 级别下的 InnoDB 快照读有什么不同？
 
正是 Read View 生成时机的不同，从而造成 RC , RR 级别下快照读的结果的不同
 
在 RR 级别下的某个事务的对某条记录的第一次快照读会创建一个快照及 Read View, 将当前系统活跃的其他事务记录起来，此后在调用快照读的时候，还是使用的是同一个 Read View，所以只要当前事务在其他事务提交更新之前使用过快照读，那么之后的快照读使用的都是同一个 Read View，所以对之后的修改不可见；
即 RR 级别下，快照读生成 Read View 时，Read View 会记录此时所有其他活动事务的快照，这些事务的修改对于当前事务都是不可见的。而早于Read View创建的事务所做的修改均是可见
而在 RC 级别下的，事务中，每次快照读都会新生成一个快照和 Read View , 这就是我们在 RC 级别下的事务中可以看到别的事务提交的更新的原因
 
总之在 RC 隔离级别下，是每个快照读都会生成并获取最新的 Read View；而在 RR 隔离级别下，则是同一个事务中的第一个快照读才会创建 Read View, 之后的快照读获取的都是同一个 Read View。
 

 
 MySQL系列
 
 
【MySQL笔记】正确的理解MySQL的乐观锁与悲观锁,MVCC
【MySQL笔记】正确的理解MySQL的MVCC及实现原理
【MySQL笔记】正确的理解MySQL的事务和隔离级别
 
 
参考资料 
 
 
InnoDB多版本(MVCC)实现简要分析 - @作者：何登成
MySQL InnoDB MVCC深度分析 - @作者：stevenczp
InnoDB存储引擎MVCC的工作原理 - @作者：秋风醉了 
MySQL 在 RC 隔离级别下是如何实现读不阻塞的？ - @作者：知乎
MVCC read view的问题 - @作者：PHP中文网
MySQL数据库事务各隔离级别加锁情况–read committed && MVCC - @作者：mark_fork
乐观锁与CAS，MVCC - @作者：shuff1e
悲观锁，乐观锁以及MVCC - @作者：wezheng
【数据库】悲观锁与乐观锁与MySQL的MVCC实现简述 - @作者：Nick Huang
如果觉得对你有帮助，能否点个赞或关个注，以示鼓励笔者呢？！

       目前大部分数据库系统及文件系统都采用B-Tree(B树)或其变种B+Tree(B+树)作为索引结构。B+Tree是数据库系统实现索引的首选数据结构。在MySQL中,索引属于存储引擎级别的概念,不同存储引擎对索引的实现方式是不同的,本文主要讨论MyISAM和InnoDB两个存储引擎的索引实现方式。MyISAM索引实现MyISAM引擎使用B+Tree作为索引结构,叶节点的data域存放的是数据记录的地址。下图是MyISAM索引的原理图:image.png这里设表一共有三列,假设我  
 
        在 MySQL 中,索引属于存储引擎级别的概念,不同存储引擎对索引的实现方式是不同的,本文主要讨论 MyISAM 和 InnoDB 两个存储引擎的索引实现方式。
 
MyISAM 索引实现 
 
MyISAM 引擎使用 B+Tree 作为索引结构,叶节点的 data 域存放的是数据记录的地址。下图是 MyISAM 索引的原理图:
 
这里设表一共有三列,假设我们以 Col1 为主键,则图 8 是一个 MyISAM 表的主索引(Primary key)示意。可以看出 MyISAM 的索引文件仅仅保存数据记录的地址。
 
辅助索引 
 
在 MyISAM 中,主索引和辅助索引(Secondary key)在结构上没有任何区别,只是主索引要求 key 是唯一的,而辅助索引的 key 可以重复。如果我们在 Col2 上建立一个辅助索引,则此索引的结构如下图所示
 

 
同样也是一颗 B+Tree,data 域保存数据记录的地址。因此,MyISAM 中索引检索的算法为首先按照 B+Tree 搜索算法搜索索引,如果指定的 Key 存在,则取出其data 域的值,然后以 data 域的值为地址,读取相应数据记录。
 
MyISAM 的索引方式也叫做“非聚集索引”,之所以这么称呼是为了与 InnoDB的聚集索引区分。
 

 InnoDB 索引实现 
 
虽然 InnoDB 也使用 B+Tree 作为索引结构,但具体实现方式却与 MyISAM 截然不同。
 
1.第一个重大区别是 InnoDB 的数据文件本身就是索引文件。从上文知道,MyISAM 索引文件和数据文件是分离的,索引文件仅保存数据记录的地址。
 
而在InnoDB 中,表数据文件本身就是按 B+Tree 组织的一个索引结构,这棵树的叶点data 域保存了完整的数据记录。这个索引的 key 是数据表的主键,因此 InnoDB 表数据文件本身就是主索引。
 

 
上图是 InnoDB 主索引(同时也是数据文件)的示意图,可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为 InnoDB 的数据文件本身要按主键聚集,
 
 1 .InnoDB 要求表必须有主键(MyISAM 可以没有),如果没有显式指定,则 MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键,如果不存在这种列,则MySQL 自动为 InnoDB 表生成一个隐含字段作为主键,类型为长整形。
 
 同时,请尽量在 InnoDB 上采用自增字段做表的主键。因为 InnoDB 数据文件本身是一棵B+Tree,非单调的主键会造成在插入新记录时数据文件为了维持 B+Tree 的特性而频繁的分裂调整,十分低效,而使用自增字段作为主键则是一个很好的选择。如果表使用自增主键,那么每次插入新的记录,记录就会顺序添加到当前索引节点的后续位置,当一页写满,就会自动开辟一个新的页。如下图所示:
 

 
这样就会形成一个紧凑的索引结构,近似顺序填满。由于每次插入时也不需要移动已有数据,因此效率很高,也不会增加很多开销在维护索引上。
 
 2.第二个与 MyISAM 索引的不同是 InnoDB 的辅助索引 data 域存储相应记录主键的值而不是地址。换句话说,InnoDB 的所有辅助索引都引用主键作为 data 域。
 例如,图 11 为定义在 Col3 上的一个辅助索引:
 


  
 
聚集索引这种实现方式使得按主键的搜索十分高效,但是辅助索引搜索需要检索两遍索引（回表）:首先检索辅助索引获得主键,然后用主键到主索引中检索获得记录。
 
 引申:为什么不建议使用过长的字段作为主键?
 
 因为所有辅助索引都引用主索引,过长的主索引会令辅助索引变得过大。
 

聚簇索引与非聚簇索引 
 
InnoDB 使用的是聚簇索引, 将主键组织到一棵 B+树中, 而行数据就储存在叶子节点上, 若使用"where id = 14"这样的条件查找主键, 则按照 B+树的检索算法即可查找到对应的叶节点, 之后获得行数据。 若对 Name 列进行条件搜索, 则需要两个步骤:
 第一步在辅助索引 B+树中检索 Name, 到达其叶子节点获取对应的主键。
 第二步使用主键在主索引 B+树种再执行一次 B+树检索操作, 最终到达叶子节点即可获取整行数据。
 
MyISM 使用的是非聚簇索引, 非聚簇索引的两棵 B+树看上去没什么不同, 节点
 的结构完全一致只是存储的内容不同而已, 主键索引 B+树的节点存储了主键, 辅助键索引B+树存储了辅助键。 表数据存储在独立的地方, 这两颗 B+树的叶子节点都使用一个地址指向真正的表数据, 对于表数据来说, 这两个键没有任何差别。 由于索引树是独立的, 通过辅助键检索无需访问主键的索引树。
 
为了更形象说明这两种索引的区别, 我们假想一个表如下图存储了 4 行数据。 其中Id 作为主索引, Name 作为辅助索引。 图示清晰的显示了聚簇索引和非聚簇索引的差异
 

 
 
 
联合索引及最左原则
 
联合索引存储数据结构图：
 
 
最左原则：
 
例如联合索引有三个索引字段（A,B,C）
 
查询条件：
 
（A，，）---会使用索引
 
（A，B，）---会使用索引
 
（A，B，C）---会使用索引
 
（，B，C）---不会使用索引
 
（，，C）---不会使用索引
 
 
 
*最后来一个问题：mysql假设一行数据大小为1k，则一颗层高为3的b+树可以存放多少条数据？
 
mysql页默认大小16k，如果数据行大小1k，叶子节点存放的完整数据，则叶子节点一页可以放16条数据；非叶子节点页面存放的是主键和指针，所以主要看主键是啥类型，假设是integer，则长度8字节，指针大小在innodb是6字节，一共14字节，所以非叶子节点每页可以存16384/14=1170个主键数据（1170个分叉），则三层b+树数据可以存1170*1170*16=21902400条数据。（千万级别）

MySql事务实现原理--参考极客时间专栏《MySQL实战45讲》，博客
 
 
 
1.事务的基本要素（ACID）
 
原子性（Atomicity）：事务内的操作要嘛全部完成，要嘛全部回滚。
 
一致性(Consistency)：事务执行的前后都是合法的数据状态，不会违反任何的数据完整性。
 
隔离性（Isolation）:主要是事务之间的相互的影响，根据隔离有不同的影响效果。
 
持久性（Durability）：事务一旦提交，就会体现在数据库上，不能回滚。
 
 
 
2.事务并发的问题
 
脏读：比如事务A执行的过程中，读到了事务B未提交的内容。
 
不可重复度：指一个事务在前后两次查询的结果不一致。
 
幻读：幻读是指前后两次相同条件下的查询，后一次查询读到了前一次查询没有的行数据。
 
特别注意的是：在不可重复读的隔离级别下，普通查询是不会出现幻读的，因为都是快照读，只有在当前读，也就是select语句后面加for update查询的时候出现，幻读的解决办法，后面会有。
 
 
 
3.事务的隔离级别
 
（1）读未提交
 
存在问题：脏读，不可重复读，幻读。
 
（2）读已提交
 
存在问题：不可重复读，幻读.
 
实现依赖于一致性视图（MVCC实现）：查询只承认在语句启动前就已经提交完成的数据。
 
（3）可重复读
 
存在问题：幻读，使用当前读解决读已提交隔离级别的不可重复读。
 
实现依赖于一致性视图（MVCC实现）：查询只承认在事务启动前就已经提交完成的数据；
 
幻读的问题：
 
1.破坏语义
 
select * from test_table where col_1 = 1 for update；
 
这条sql语句的本意是要把test_table的col_1 为1的行锁住，不允许别人事务对这些行进行操作。
 
假设test_table中id为1的行，col_1的值不为1
 
有以下两个语句：
 
update test_table set col_1 = 1 where id = 1
 
update test_table set col_2 = 12 where id = 1
 
 对test_table的col_1 为1的行进行修改，违背了将这些行锁住的语句。
 
2.数据一致性问题
 
主要体现在各个事务提交的时间点不确定，各个事务之间的语句会有影响，从而写到binlog的逻辑语句与表达的语义不一致。
 
即使对所有的行加入了锁，也会出现不一致，因为可能要插入心的行。
 
 
 
幻读解决办法：
 
引入间隙锁，会锁住区间，跟间隙锁存在冲突关系的，是“往这个间隙中插入一个记录”这个操作。间隙锁和行锁合称 next-key lock。
 
间隙锁的困扰：有可能引发死锁。主要体现在两个事务互相等待对方的间隙锁。
 
解决办法：隔离级别设为读已提交，同时为了解决数据不一致问题，binlog的格式设置为row。
 
选择哪一个事务隔离级别，具体问题具体分析。
 
 
 
（4）串行化
 
存在问题：没有幻读，脏读，不可重复读的问题，但是由于是串行，性能非常差。

说到索引，很多人都知道“索引是一个排序的列表，在这个列表中存储着索引的值和包含这个值的数据所在行的物理地址，在数据十分庞大的时候，索引可以大大加快查询的速度，这是因为使用索引后可以不用扫描全表来定位某行的数据，而是先通过索引表找到该行数据对应的物理地址然后访问相应的数据。”
 
但是索引是怎么实现的呢？因为索引并不是关系模型的组成部分，因此不同的DBMS有不同的实现，我们针对MySQL数据库的实现进行说明。本文内容涉及MySQL中索引的语法、索引的优缺点、索引的分类、索引的实现原理、索引的使用策略、索引的优化几部分。
 
目录
 
一、MySQL中索引的语法
 
二、索引的优缺点
 
三、索引的分类
 
四、索引的实现原理
 
1、哈希索引：
 
2、全文索引：
 
3、BTree索引和B+Tree索引
 
*    聚簇索引和非聚簇索引
 
五、索引的使用策略
 
六、索引的优化
 

一、MySQL中索引的语法
 
创建索引
 
在创建表的时候添加索引
 
CREATE TABLE mytable(  
    ID INT NOT NULL,   
    username VARCHAR(16) NOT NULL,  
    INDEX [indexName] (username(length))  
); 
 
在创建表以后添加索引
 
ALTER TABLE my_table ADD [UNIQUE] INDEX index_name(column_name);
或者
CREATE INDEX index_name ON my_table(column_name);
 
注意：
 
1、索引需要占用磁盘空间，因此在创建索引时要考虑到磁盘空间是否足够
 
2、创建索引时需要对表加锁，因此实际操作中需要在业务空闲期间进行
 
根据索引查询
 
具体查询：
SELECT * FROM table_name WHERE column_1=column_2;(为column_1建立了索引)

或者模糊查询
SELECT * FROM table_name WHERE column_1 LIKE '%三'
SELECT * FROM table_name WHERE column_1 LIKE '三%'
SELECT * FROM table_name WHERE column_1 LIKE '%三%'

SELECT * FROM table_name WHERE column_1 LIKE '_好_'

如果要表示在字符串中既有A又有B，那么查询语句为：
SELECT * FROM table_name WHERE column_1 LIKE '%A%' AND column_1 LIKE '%B%';

SELECT * FROM table_name WHERE column_1 LIKE '[张李王]三';  //表示column_1中有匹配张三、李三、王三的都可以
SELECT * FROM table_name WHERE column_1 LIKE '[^张李王]三';  //表示column_1中有匹配除了张三、李三、王三的其他三都可以

//在模糊查询中，%表示任意0个或多个字符；_表示任意单个字符（有且仅有），通常用来限制字符串长度;[]表示其中的某一个字符；[^]表示除了其中的字符的所有字符

或者在全文索引中模糊查询
SELECT * FROM table_name WHERE MATCH(content) AGAINST('word1','word2',...);
 
删除索引
 
DROP INDEX my_index ON tablename；
或者
ALTER TABLE table_name DROP INDEX index_name;
 
查看表中的索引
 
SHOW INDEX FROM tablename
 
查看查询语句使用索引的情况
 
//explain 加查询语句
explain SELECT * FROM table_name WHERE column_1='123';
 
二、索引的优缺点
 
优势：可以快速检索，减少I/O次数，加快检索速度；根据索引分组和排序，可以加快分组和排序；
 
劣势：索引本身也是表，因此会占用存储空间，一般来说，索引表占用的空间的数据表的1.5倍；索引表的维护和创建需要时间成本，这个成本随着数据量增大而增大；构建索引会降低数据表的修改操作（删除，添加，修改）的效率，因为在修改数据表的同时还需要修改索引表；
 
三、索引的分类
 
常见的索引类型有：主键索引、唯一索引、普通索引、全文索引、组合索引
 
1、主键索引：即主索引，根据主键pk_clolum（length）建立索引，不允许重复，不允许空值；
 
ALTER TABLE 'table_name' ADD PRIMARY KEY pk_index('col')；
 
2、唯一索引：用来建立索引的列的值必须是唯一的，允许空值
 
ALTER TABLE 'table_name' ADD UNIQUE index_name('col')；
 
3、普通索引：用表中的普通列构建的索引，没有任何限制
 
ALTER TABLE 'table_name' ADD INDEX index_name('col')；
 
4、全文索引：用大文本对象的列构建的索引（下一部分会讲解）
 
ALTER TABLE 'table_name' ADD FULLTEXT INDEX ft_index('col')；
 
5、组合索引：用多个列组合构建的索引，这多个列中的值不允许有空值
 
ALTER TABLE 'table_name' ADD INDEX index_name('col1','col2','col3')；
 
*遵循“最左前缀”原则，把最常用作为检索或排序的列放在最左，依次递减，组合索引相当于建立了col1,col1col2,col1col2col3三个索引，而col2或者col3是不能使用索引的。
 
*在使用组合索引的时候可能因为列名长度过长而导致索引的key太大，导致效率降低，在允许的情况下，可以只取col1和col2的前几个字符作为索引
 
ALTER TABLE 'table_name' ADD INDEX index_name(col1(4),col2（3))；
 
表示使用col1的前4个字符和col2的前3个字符作为索引
 
四、索引的实现原理
 
MySQL支持诸多存储引擎，而各种存储引擎对索引的支持也各不相同，因此MySQL数据库支持多种索引类型，如BTree索引，B+Tree索引，哈希索引，全文索引等等，
 
1、哈希索引：
 
只有memory（内存）存储引擎支持哈希索引，哈希索引用索引列的值计算该值的hashCode，然后在hashCode相应的位置存执该值所在行数据的物理位置，因为使用散列算法，因此访问速度非常快，但是一个值只能对应一个hashCode，而且是散列的分布方式，因此哈希索引不支持范围查找和排序的功能。
 
2、全文索引：
 
FULLTEXT（全文）索引，仅可用于MyISAM和InnoDB，针对较大的数据，生成全文索引非常的消耗时间和空间。对于文本的大对象，或者较大的CHAR类型的数据，如果使用普通索引，那么匹配文本前几个字符还是可行的，但是想要匹配文本中间的几个单词，那么就要使用LIKE %word%来匹配，这样需要很长的时间来处理，响应时间会大大增加，这种情况，就可使用时FULLTEXT索引了，在生成FULLTEXT索引时，会为文本生成一份单词的清单，在索引时及根据这个单词的清单来索引。FULLTEXT可以在创建表的时候创建，也可以在需要的时候用ALTER或者CREATE INDEX来添加：
 
//创建表的时候添加FULLTEXT索引
CTREATE TABLE my_table(
    id INT(10) PRIMARY KEY,
    name VARCHAR(10) NOT NULL,
    my_text TEXT,
    FULLTEXT(my_text)
)ENGINE=MyISAM DEFAULT CHARSET=utf8;
 
//创建表以后，在需要的时候添加FULLTEXT索引
ALTER TABLE my_table ADD FULLTEXT INDEX ft_index(column_name);
 
全文索引的查询也有自己特殊的语法，而不能使用LIKE %查询字符串%的模糊查询语法
 
SELECT * FROM table_name MATCH(ft_index) AGAINST('查询字符串');
 
注意：
 
*对于较大的数据集，把数据添加到一个没有FULLTEXT索引的表，然后添加FULLTEXT索引的速度比把数据添加到一个已经有FULLTEXT索引的表快。
 
*5.6版本前的MySQL自带的全文索引只能用于MyISAM存储引擎，如果是其它数据引擎，那么全文索引不会生效。5.6版本之后InnoDB存储引擎开始支持全文索引
 
*在MySQL中，全文索引支队英文有用，目前对中文还不支持。5.7版本之后通过使用ngram插件开始支持中文。
 
*在MySQL中，如果检索的字符串太短则无法检索得到预期的结果，检索的字符串长度至少为4字节，此外，如果检索的字符包括停止词，那么停止词会被忽略。
 
* 更深入的理解参考文章：全文索引的深入理解
 
 
 
3、BTree索引和B+Tree索引
 
 
 
BTree索引
BTree是平衡搜索多叉树，设树的度为2d（d>1），高度为h，那么BTree要满足以一下条件：
 
每个叶子结点的高度一样，等于h；
每个非叶子结点由n-1个key和n个指针point组成，其中d<=n<=2d,key和point相互间隔，结点两端一定是key；
叶子结点指针都为null；
非叶子结点的key都是[key,data]二元组，其中key表示作为索引的键，data为键值所在行的数据；
BTree的结构如下：
 
 
在BTree的机构下，就可以使用二分查找的查找方式，查找复杂度为h*log(n)，一般来说树的高度是很小的，一般为3左右，因此BTree是一个非常高效的查找结构。
 
BTree的查询、插入、删除过程可以参考：https://blog.csdn.net/endlu/article/details/51720299
 
B+Tree索引
B+Tree是BTree的一个变种，设d为树的度数，h为树的高度，B+Tree和BTree的不同主要在于：
 
B+Tree中的非叶子结点不存储数据，只存储键值；
B+Tree的叶子结点没有指针，所有键值都会出现在叶子结点上，且key存储的键值对应data数据的物理地址；
B+Tree的每个非叶子节点由n个键值key和n个指针point组成；
B+Tree的结构如下：
 
 
B+Tree对比BTree的优点：
 
1、磁盘读写代价更低
 
一般来说B+Tree比BTree更适合实现外存的索引结构，因为存储引擎的设计专家巧妙的利用了外存（磁盘）的存储结构，即磁盘的最小存储单位是扇区（sector），而操作系统的块（block）通常是整数倍的sector，操作系统以页（page）为单位管理内存，一页（page）通常默认为4K，数据库的页通常设置为操作系统页的整数倍，因此索引结构的节点被设计为一个页的大小，然后利用外存的“预读取”原则，每次读取的时候，把整个节点的数据读取到内存中，然后在内存中查找，已知内存的读取速度是外存读取I/O速度的几百倍，那么提升查找速度的关键就在于尽可能少的磁盘I/O，那么可以知道，每个节点中的key个数越多，那么树的高度越小，需要I/O的次数越少，因此一般来说B+Tree比BTree更快，因为B+Tree的非叶节点中不存储data，就可以存储更多的key。
 
2、查询速度更稳定
 
由于B+Tree非叶子节点不存储数据（data），因此所有的数据都要查询至叶子节点，而叶子节点的高度都是相同的，因此所有数据的查询速度都是一样的。
 
更多操作系统内容参考：
 
硬盘结构
 
扇区、块、簇、页的区别
 
操作系统层优化（进阶，初学不用看）
 
带顺序索引的B+TREE
很多存储引擎在B+Tree的基础上进行了优化，添加了指向相邻叶节点的指针，形成了带有顺序访问指针的B+Tree，这样做是为了提高区间查找的效率，只要找到第一个值那么就可以顺序的查找后面的值。
 
B+Tree的结构如下：
 
 
 
 
聚簇索引和非聚簇索引
 
分析了MySQL的索引结构的实现原理，然后我们来看看具体的存储引擎怎么实现索引结构的，MySQL中最常见的两种存储引擎分别是MyISAM和InnoDB，分别实现了非聚簇索引和聚簇索引。
 
聚簇索引的解释是:聚簇索引的顺序就是数据的物理存储顺序
 
非聚簇索引的解释是:索引顺序与数据物理排列顺序无关
 
（这样说起来并不好理解，让人摸不着头脑，清继续看下文，并在插图下方对上述两句话有解释）
 
首先要介绍几个概念，在索引的分类中，我们可以按照索引的键是否为主键来分为“主索引”和“辅助索引”，使用主键键值建立的索引称为“主索引”，其它的称为“辅助索引”。因此主索引只能有一个，辅助索引可以有很多个。
 
MyISAM——非聚簇索引
 
MyISAM存储引擎采用的是非聚簇索引，非聚簇索引的主索引和辅助索引几乎是一样的，只是主索引不允许重复，不允许空值，他们的叶子结点的key都存储指向键值对应的数据的物理地址。
非聚簇索引的数据表和索引表是分开存储的。
非聚簇索引中的数据是根据数据的插入顺序保存。因此非聚簇索引更适合单个数据的查询。插入顺序不受键值影响。
只有在MyISAM中才能使用FULLTEXT索引。(mysql5.6以后innoDB也支持全文索引)
*最开始我一直不懂既然非聚簇索引的主索引和辅助索引指向相同的内容，为什么还要辅助索引这个东西呢，后来才明白索引不就是用来查询的吗，用在那些地方呢，不就是WHERE和ORDER BY 语句后面吗，那么如果查询的条件不是主键怎么办呢，这个时候就需要辅助索引了。
 
InnoDB——聚簇索引
 
聚簇索引的主索引的叶子结点存储的是键值对应的数据本身，辅助索引的叶子结点存储的是键值对应的数据的主键键值。因此主键的值长度越小越好，类型越简单越好。
聚簇索引的数据和主键索引存储在一起。
聚簇索引的数据是根据主键的顺序保存。因此适合按主键索引的区间查找，可以有更少的磁盘I/O，加快查询速度。但是也是因为这个原因，聚簇索引的插入顺序最好按照主键单调的顺序插入，否则会频繁的引起页分裂，严重影响性能。
在InnoDB中，如果只需要查找索引的列，就尽量不要加入其它的列，这样会提高查询效率。
 
 
*使用主索引的时候，更适合使用聚簇索引，因为聚簇索引只需要查找一次，而非聚簇索引在查到数据的地址后，还要进行一次I/O查找数据。
 
*因为聚簇辅助索引存储的是主键的键值，因此可以在数据行移动或者页分裂的时候降低成本，因为这时不用维护辅助索引。但是由于主索引存储的是数据本身，因此聚簇索引会占用更多的空间。
 
*聚簇索引在插入新数据的时候比非聚簇索引慢很多，因为插入新数据时需要检测主键是否重复，这需要遍历主索引的所有叶节点，而非聚簇索引的叶节点保存的是数据地址，占用空间少，因此分布集中，查询的时候I/O更少，但聚簇索引的主索引中存储的是数据本身，数据占用空间大，分布范围更大，可能占用好多的扇区，因此需要更多次I/O才能遍历完毕。
 
下图可以形象的说明聚簇索引和非聚簇索引的区别
 
 
从上图中可以看到聚簇索引的辅助索引的叶子节点的data存储的是主键的值，主索引的叶子节点的data存储的是数据本身，也就是说数据和索引存储在一起，并且索引查询到的地方就是数据（data）本身，那么索引的顺序和数据本身的顺序就是相同的；
 
而非聚簇索引的主索引和辅助索引的叶子节点的data都是存储的数据的物理地址，也就是说索引和数据并不是存储在一起的，数据的顺序和索引的顺序并没有任何关系，也就是索引顺序与数据物理排列顺序无关。
 
 
 
此外MyISAM和innoDB的区别总结如下：
 

  MyISAM和innoDB引擎对比
 
 
MyISAM
innoDB
索引类型
非聚簇
聚簇
支持事务
是
否
支持表锁
是
是
支持行锁
否
是（默认）
支持外键
否
是
支持全文索引
是
是（5.6以后支持）
适用操作类型
大量select下使用
大量insert、delete和update下使用
总结如下：
 
InnoDB 支持事务，支持行级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
MyISAM 不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
此外，Memory 不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引；
 
更多MyISAM和innoDB的区别具体内容参考：MyISAMheinnoDB的区别，包括行级锁死锁的具体分析
 
 
 
五、索引的使用策略
 
 
 
什么时候要使用索引？
 
主键自动建立唯一索引；
经常作为查询条件在WHERE或者ORDER BY 语句中出现的列要建立索引；
作为排序的列要建立索引；
查询中与其他表关联的字段，外键关系建立索引
高并发条件下倾向组合索引；
用于聚合函数的列可以建立索引，例如使用了max(column_1)或者count(column_1)时的column_1就需要建立索引
什么时候不要使用索引？
 
经常增删改的列不要建立索引；
有大量重复的列不建立索引；
表记录太少不要建立索引。只有当数据库里已经有了足够多的测试数据时，它的性能测试结果才有实际参考价值。如果在测试数据库里只有几百条数据记录，它们往往在执行完第一条查询命令之后就被全部加载到内存里，这将使后续的查询命令都执行得非常快--不管有没有使用索引。只有当数据库里的记录超过了1000条、数据总量也超过了MySQL服务器上的内存总量时，数据库的性能测试结果才有意义。
索引失效的情况：
 
在组合索引中不能有列的值为NULL，如果有，那么这一列对组合索引就是无效的。
在一个SELECT语句中，索引只能使用一次，如果在WHERE中使用了，那么在ORDER BY中就不要用了。
LIKE操作中，'%aaa%'不会使用索引，也就是索引会失效，但是‘aaa%’可以使用索引。
在索引的列上使用表达式或者函数会使索引失效，例如：select * from users where YEAR(adddate)<2007，将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成：select * from users where adddate<’2007-01-01′。其它通配符同样，也就是说，在查询条件中使用正则表达式时，只有在搜索模板的第一个字符不是通配符的情况下才能使用索引。
在查询条件中使用不等于，包括<符号、>符号和！=会导致索引失效。特别的是如果对主键索引使用！=则不会使索引失效，如果对主键索引或者整数类型的索引使用<符号或者>符号不会使索引失效。（经erwkjrfhjwkdb同学提醒，不等于，包括&lt;符号、>符号和！，如果占总记录的比例很小的话，也不会失效）
在查询条件中使用IS NULL或者IS NOT NULL会导致索引失效。
字符串不加单引号会导致索引失效。更准确的说是类型不一致会导致失效，比如字段email是字符串类型的，使用WHERE email=99999 则会导致失败，应该改为WHERE email='99999'。
在查询条件中使用OR连接多个条件会导致索引失效，除非OR链接的每个条件都加上索引，这时应该改为两次查询，然后用UNION ALL连接起来。
如果排序的字段使用了索引，那么select的字段也要是索引字段，否则索引失效。特别的是如果排序的是主键索引则select * 也不会导致索引失效。
尽量不要包括多列排序，如果一定要，最好为这队列构建组合索引；
 
 
六、索引的优化
 
 
 
1、最左前缀
 
索引的最左前缀和和B+Tree中的“最左前缀原理”有关，举例来说就是如果设置了组合索引<col1,col2,col3>那么以下3中情况可以使用索引：col1，<col1,col2>，<col1,col2,col3>，其它的列，比如<col2,col3>，<col1,col3>，col2，col3等等都是不能使用索引的。
 
根据最左前缀原则，我们一般把排序分组频率最高的列放在最左边，以此类推。
 
2、带索引的模糊查询优化
 
在上面已经提到，使用LIKE进行模糊查询的时候，'%aaa%'不会使用索引，也就是索引会失效。如果是这种情况，只能使用全文索引来进行优化（上文有讲到）。
 
3、为检索的条件构建全文索引，然后使用
 
SELECT * FROM tablename MATCH(index_colum) ANGAINST(‘word’);
 
4、使用短索引
 
对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的 列，如果在前10 个或20 个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。