从并发和索引说说innodb和myisam的区别

浏览数：25 / 时间：2015年06月12日

一.引言

这个问题看似是个很俗气的问题，但是在面试中也是个经常被问到的问题。我想很多人都能说出几条，比如事物，外键，等等。但是我想这样想起一点说一句这么回答的话，知识其实都是分散的，这样就显得不是很高大上。

我认为回答这个问题，可以从两个方面来阐述，一个是并发控制方面，还有一个就是索引实现方面。

二.并发控制

1.myisam是表级锁

表锁的意思就是，对整张表加锁，当用户进行写操作（UPDATE INSEART DELETE）时，用户获得一个写锁，写锁会禁止任何用户的读/写操作。

举个通俗点的例子来所，比如当一个程序在修改id=1的记录时，这时候任何试图读/写任何记录都会被阻塞。有些人肯定觉得这样很不科学，只是更新id=1的记录，凭什么我想读id=2的记录或者修改id=3的记录等等全都要等待啊。

很明显表锁的并发很低，但是表锁的管理开销是最低的，也是最简单的。就好像不是有了快排，其他排序就全没用武之地了。

2.innodb是支持MVCC(多版本并发控制)的行级锁

这个MVCC比较复杂，估计面试时候别人也不会深究，我觉得只需要知道行级锁解决了上面表级锁的那种情况，提高了并发，随之锁的系统开销也变大了。

三.索引实现

1.myisam和innodb的索引实现都是基于B+TREE

这两种存储引擎都是通过primary key(id)或者index(col1,col2...)的方式来添加索引的，在内部实现上这两种索引分别叫做主键索引和辅助索引，而这4种（2种引擎*2种索引）索引都是基于B+TREE的。B+TREE简单的来说就是二叉查找树的升级版，只不过他是有N个分裂，并且平衡的，所有数据都在叶子节点上，并且叶子节点都指向兄弟的指针。所以无论是那种引擎，当我们对比如col1建索引的时候，一定都是只有左匹配才能用到索引，因为B+TREE是按照col1的开始排序的，这样就解释了为什么like ‘%col1‘无法用到索引。当我们队比如col1,col2建索引的时候，如果where col2=xx and col1=oo是用不到索引的，因为B+TREE是按照先对col1排序，再对col2排序存储索引的。

2.myisam是非聚集索引

非聚集索引指的是，在B+TREE中叶子节点数据除了有索引数据，剩下的是存储物理行的地址。对于myisam而言，主键索引和辅助索引实现上几乎没什么区别。因为索引到的数据是指向物理行的地址，所以想取到数据的话，必须再根据物理行地址去读一次数据文件。

3.innodb是主键索引是聚集索引

所谓的聚集索引指的是，在B+TREE中的叶子节点数据包含了记录中的全部数据。辅助索引中和myisam不同的是，辅助索引除了索引值包含的是该行的主键值，也有人把myisam存储的叫物理地址，而innodb中存储的叫逻辑地址。这样设计以后，对于按照主键检索，可以只读索引文件，就能取到任何数据，减少了一次IO，但是对于辅助索引的使用，除了通过B+TREE检索到数据还要再次通过主键索引的B+TREE定位到具体的数据，相当于做了两次检索。