聚集索引(转)和MVCC

　　1.聚集

　　聚集索引的区别

　　　　聚集索引:物理存储按照索引排序

　　　　非聚集索引:物理存储不按照索引排序

　　优势与缺点

　　　　聚集索引：插入数据时速度要慢（时间花费在“物理存储的排序”上，也就是首先要找到位置然后插入）,查询数据比非聚集数据的速度快

　　索引是通过二叉树的数据结构来描述的，我们可以这么理解聚簇索引：索引的叶节点就是数据节点。而非聚簇索引的叶节点仍然是索引节点，只不过有一个指针指向对应的数据块。如下图：

非聚集索引

聚集索引

　　索引可以提高检索效率，因为它的二叉树结构以及占用空间小，所以访问速度块。

　　但是有些时候用索引还不如不用索引快。比如说我们要检索上述表中的所有记录。

　　假设有一8000条记录的表，表中每条记录在磁盘上占用1000字节，如果在一个10字节长的字段上建立非聚簇索引主键，需要二叉树节点16000个（这16000个节点中有8000个叶节点，每个页节点都指向一个数据记录），这样数据将占用8000条×1000字节 /8K字节=1000个页面；索引将占用16000个节点×10字节/8K字节=20个页面，共计1020个页面。

　　同样一张表，如果我们在对应字段上建立聚簇索引主键，由于聚簇索引的页节点就是数据节点，所以索引节点仅有8000个，占用10个页面，数据仍然占有1000个页面。

　　执行插入操作时，非聚簇索引的主键为什么比聚簇索引主键要快。主键约束要求主键不能出现重复，那么SQL 是怎么知道不出现重复的呢？唯一的方法就是检索(这里应该是要检索所有)。对于非聚簇索引，只需要检索20个页面中的16000个节点就知道是否有重复，因为所有主键键值在这16000个索引节点中都包含了。但对于聚簇索引，索引节点仅仅包含了8000个中间节点，至于会不会出现重复必须检索另外8000个页数据节点才知道，那么相当于检索10+1000=1010个页面才知道是否有重复。所以聚簇索引主键的插入速度要比非聚簇索引主键的插入速度慢很多。

　　看看数据检索的效率，如果对上述两表进行检索，在使用索引的情况下（有些时候SQL执行计划会选择不使用索引，不过我们这里姑且假设一定使用索引），对于聚簇索引检索，我们可能会访问10个索引页面外加1000个数据页面得到结果（实际情况要比这个好），而对于非聚簇索引，系统会从20个页面中找到符合条件的节点，再映射到1000个数据页面上（这也是最糟糕的情况），比较一下，一个访问了1010个页面而另一个访问了1020个页面，可见检索效率差异并不是很大。所以不管非聚簇索引也好还是聚簇索引也好，都适合排序，聚簇索引仅仅比非聚簇索引快一点。

　　非聚簇对于更新肯定是有优势的，而它在检索的性能损失也不会太大，所以能不用聚簇当然是最好的了。
　　但是如果使用\order by(搜索范围值)的话，聚簇的优势也应该是很明显的。

 

　　2. Innodb的事务相关概念

 

　　为了支持事务，Innbodb引入了下面几个概念：

 

• redo log
  redo log就是保存执行的SQL语句到一个指定的Log文件，当Mysql执行recovery时重新执行redo log记录的SQL操作即可。当客户端执行每条SQL（更新语句）时，redo log会被首先写入log buffer；当客户端执行COMMIT命令时，log buffer中的内容会被视情况刷新到磁盘。redo log在磁盘上作为一个独立的文件存在，即Innodb的log文件。
• undo log
  与redo log相反，undo log是为回滚而用，具体内容就是copy事务前的数据库内容（行）到undo buffer，在适合的时间把undo buffer中的内容刷新到磁盘。undo buffer与redo buffer一样，也是环形缓冲，但当缓冲满的时候，undo buffer中的内容会也会被刷新到磁盘；与redo log不同的是，磁盘上不存在单独的undo log文件，所有的undo log均存放在主ibd数据文件中（表空间），即使客户端设置了每表一个数据文件也是如此。
• rollback segment
  回滚段这个概念来自Oracle的事物模型，在Innodb中，undo log被划分为多个段，具体某行的undo log就保存在某个段中，称为回滚段。可以认为undo log和回滚段是同一意思。
• 锁
  Innodb提供了基于行的锁，如果行的数量非常大，则在高并发下锁的数量也可能会比较大，据Innodb文档说，Innodb对锁进行了空间有效优化，即使并发量高也不会导致内存耗尽。
  对行的锁有分两种：排他锁、共享锁。共享锁针对对，排他锁针对写，完全等同读写锁的概念。如果某个事务在更新某行（排他锁），则其他事物无论是读还是写本行都必须等待；如果某个事物读某行（共享锁），则其他读的事物无需等待，而写事物则需等待。通过共享锁，保证了多读之间的无等待性，但是锁的应用又依赖Mysql的事务隔离级别。 (弦外：更新锁，为解决死锁，引入更新锁。更新锁的意思是：“我现在只想读，你们别人也可以读，但我将来可能会做更新操作，我已经获取了从共享锁（用来读）到排他锁（用来更新）的资格”。一个事物只能有一个更新锁获此资格。)
• 隔离级别
  隔离级别用来限制事务直接的交互程度，目前有几个工业标准：
  - READ_UNCOMMITTED：脏读，如果一个事务A对数据进行了更改，但是还没有提交，而另一个事务B就可以读到事务A尚未提交的更新结果。这样，当事务A进行回滚时，那么事务B开始读到的数据就是一笔脏数据。
  - READ_COMMITTED：读提交，一个事务的更新操作只有在提交了之后，才会被另一个事务读取到同一笔数据更新后的结果
  - REPEATABLE_READ：重复读，同一个事务在事务过程中，对同一个数据进行读取操作，读取到的结果不同。例如，事务B在事务A的更新操作前读到的数据，跟在事务A提交此更新操作后读到的数据，可能不同，在重复读中，必须要求前后一致！
  - SERIALIZABLE：串行化，所有的事务操作都必须串行操作。
  Innodb对四种类型都支持，脏读和串行化应用场景不多，读提交、重复读用的比较广泛。

　　3. 行的更新过程

　　下面演示下事务对某行记录的更新过程：

1. 初始数据行

 

　　F1～F6是某行列的名字，1～6是其对应的数据。后面三个隐含字段分别对应该行的事务号和回滚指针，假如这条数据是刚INSERT的，可以认为ID为1，其他两个字段为空。

 

2.事务1更改该行的各字段的值

 

 

　　当事务1更改该行的值时，会进行如下操作：

 

• 用排他锁锁定该行
• 记录redo log
• 把该行修改前的值Copy到undo log，即上图中下面的行
• 修改当前行的值，填写事务编号，使回滚指针指向undo log中的修改前的行

 

3.事务2修改该行的值

 

 

与事务1相同，此时undo log，中有有两行记录，并且通过回滚指针连在一起。

 

因此，如果undo log一直不删除，则会通过当前记录的回滚指针回溯到该行创建时的初始内容，所幸的时在Innodb中存在purge线程，它会查询那些比现在最老的活动事务还早的undo log，并删除它们，从而保证undo log文件不至于无限增长。

 

4. 事务提交

 

　　当事务正常提交时Innbod只需要更改事务状态为COMMIT即可，不需做其他额外的工作，而Rollback则稍微复杂点，需要根据当前回滚指针从undo log中找出事务修改前的版本，并恢复。如果事务影响的行非常多，回滚则可能会变的效率不高，根据经验值没事务行数在1000～10000之间，Innodb效率还是非常高的。很显然，Innodb是一个COMMIT效率比Rollback高的存储引擎。据说，Postgress的实现恰好与此相反。

 

5. Insert Undo log

 

　　上述过程确切地说是描述了UPDATE的事务过程，其实undo log分insert和update undo log，因为insert时，原始的数据并不存在，所以回滚时把insert undo log丢弃即可，而update undo log则必须遵守上述过程。

 

　　4.MVCC

　　Multi-Version Concurrency Control 多版本并发控制。

　　人们一般把基于锁的并发控制机称成为悲观机制，而把MVCC等机制称为乐观机制。

　　这是因为锁机制是一种预防性的，读会阻塞写，写也会阻塞读，当锁定粒度较大，时间较长是并发性能就不会太好；而MVCC是一种后验性的，读不阻塞写，写也不阻塞读，等到提交的时候才检验是否有冲突，由于没有锁，所以读写不会相互阻塞，从而大大提升了并发性能。

　　你可将MVCC看成行级别锁的一种妥协，它在许多情况下避免了使用锁，同时可以提供更小的开销。根据实现的不同，它可以允许非阻塞式读，在写操作进行时只锁定必要的记录。所以读不会阻塞写，而写也从不阻塞读。

　　MVCC会保存某个时间点上的数据快照。这意味着事务可以看到一个一致的数据视图，不管他们需要跑多久。这同时也意味着不同的事务在同一个时间点看到的同一个表的数据可能是不同的。

　　InnoDB：通过为每一行记录添加两个额外的隐藏的值来实现MVCC，这两个值一个记录这行数据何时被创建，另外一个记录这行数据何时过期（或者被删除）。但是InnoDB并不存储这些事件发生时的实际时间，相反它只存储这些事件发生时的系统版本号。这是一个随着事务的创建而不断增长的数字。每个事务在事务开始时会记录它自己的系统版本号。每个查询必须去检查每行数据的版本号与事务的版本号是否相同。让我们来看看当隔离级别是REPEATABLE READ时这种策略是如何应用到特定的操作的：

　　SELECT InnoDB必须每行数据来保证它符合两个条件：

　　1、InnoDB必须找到一个行的版本，它至少要和事务的版本一样老(也即它的版本号不大于事务的版本号)。这保证了不管是事务开始之前，或者事务创建时，或者修改了这行数据的时候，这行数据是存在的。

　　2、这行数据的删除版本必须是未定义的或者比事务版本要大。这可以保证在事务开始之前这行数据没有被删除。

　　符合这两个条件的行可能会被当作查询结果而返回。

　　INSERT：InnoDB为这个新行记录当前的系统版本号。

　　DELETE：InnoDB将当前的系统版本号设置为这一行的删除ID。

　　UPDATE：InnoDB会写一个这行数据的新拷贝，这个拷贝的版本为当前的系统版本号。它同时也会将这个版本号写到旧行的删除版本里。

　　这种额外的记录所带来的结果就是对于大多数查询来说根本就不需要获得一个锁。他们只是简单地以最快的速度来读取数据，确保只选择符合条件的行。这个方案的缺点在于存储引擎必须为每一行存储更多的数据，做更多的检查工作，处理更多的善后操作。

　　MVCC只工作在REPEATABLE READ和READ COMMITED隔离级别下。READ UNCOMMITED不是MVCC兼容的，因为查询不能找到适合他们事务版本的行版本；它们每次都只能读到最新的版本。SERIABLABLE也不与MVCC兼容，因为读操作会锁定他们返回的每一行数据。

 　　

 　　

参考(转)：http://kb.cnblogs.com/page/44125/

http://blog.csdn.net/chen77716/article/details/6742128

百科