MySQL中MyISAM和InnoDB的索引方式以及区别与选择

一、索引

1、什么是索引

      索引是一种帮助mysql高效的获取数据的数据结构，这些数据结构以某种方式引用数据，这种结构就是索引。可简单理解为排好序的快速查找数据结构。如果要查“mysql”这个单词，我们肯定需要定位到m字母，然后从下往下找到y字母，再找到剩下的sql。

具体的参考：

http://www.2cto.com/database/201501/368126.html

      作为索引中最为常见的一种类型，B-Tree索引大都采用的是 B+Tree数据结构来存储数据（NDB集群存储引擎内部实际上采用 T-Tree结构存储这种索引）。B-Tree通常也意味着所有的值都是按顺序存储的。

2、B-Tree数据结构

这点从磁盘读写上给出解释，磁盘顺序读写时才能达到其宣传的数值(fio可以进行简单的读写测试)，因为随机读写，机械磁盘需要旋转及寻道时间，哪怕是ssd，随机读写也需要寻址时间；那么如果将索引tree构建的层数越低，使得key相近的数据都存在一起，伴随磁盘预读特性，能更进一步提高性能。

     那么使用B+Tree的关键就是Tree层数低(3层)，有序的数据存储位置接近，结合磁盘顺序读写、OS预读写特性，使得能很快定位到数据；而使用RB-Tree时key值相近的数据会存储的较远，导致效率低下。

二、MyISAM索引的实现

      MyISAM索引文件和数据文件是分离的，索引文件仅保存记录所在页的指针（物理位置），通过这些地址来读取页，进而读取被索引的行。下图是MyISAM的索引原理图：（为了简化，一个页内只存放了两条记录。）

      上图所提供的示例表字段有Col1（ID）、Col2(age)、Col3（name）三个，其中Col1为Primary Key（主键），上图很好地说明了树中叶子保存的是对应行的物理位置。通过该值，存储引擎能顺利地进行回表查询，得到一行完整记录。同时，每个叶子页也保存了指向下一个叶子页的指针。从而方便叶子节点的范围遍历。

      而对于二级索引，在 MyISAM存储引擎中以与上图同样的方式实现，可以看出MyISAM的索引文件仅仅保存数据记录的地址。

三、InnoDB索引的实现

1、聚集索引

      与 MyISAM相同的一点是，InnoDB 也采用 B+Tree这种数据结构来实现 B-Tree索引。而很大的区别在于，InnoDB 存储引擎采用“聚集索引”的数据存储方式实现B-Tree索引，所谓“聚集”，就是指数据行和相邻的键值紧凑地存储在一起，注意 InnoDB 只能聚集一个叶子页（16K）的记录（即聚集索引满足一定的范围的记录），因此包含相邻键值的记录可能会相距甚远。

注意: innodb来说,

1: 主键索引 既存储索引值,又在叶子中存储行的数据

2: 如果没有主键, 则会Unique key做主键

3: 如果没有unique,则系统生成一个内部的rowid做主键.

4: 像innodb中,主键的索引结构中,既存储了主键值,又存储了行数据,这种结构称为”聚簇索引”

      下图说明了 InnoDB聚集索引的实现方式，同时也体现了一张 innoDB表的结构，可以看到，InnoDB 中，主键索引和数据是一体的，没有分开。：

这种实现方式，给予了 InnoDB 按主键检索的超高性能。可以有目的性地选择聚集索引，比如一个邮件表，可以选择用户ID来聚集数据，这样只需要从磁盘读取较少并且连续的数据页就能获得某个id的用户全部的邮件，避免了读取分散页时所耗费的随机I/O。

2、辅助索引

      而对于辅助索引，InnoDB采用的方式是在叶子页中保存主键值，通过这个主键值来回表（上图）查询到一条完整记录，因此按辅助索引检索实际上进行了二次查询，效率肯定是没有按照主键检索高的。下图是辅助索引的实现方式：

      由于每个辅助索引都包含主键索引，因此，为了减小辅助索引所占空间，我们通常希望 InnoDB 表中的主键索引尽量定义得小一些（值得一提的是，MySIAM会使用前缀压缩技术使得索引变小，而InnoDB按照原数据格式进行存储。），并且希望InnoDB的主键是自增长的，因为如果主键并非自增长，插入时，由于写入时乱序的，会使得插入效率变低。

四、总结

1、关于innoDB中索引的使用

      了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助，例如知道了InnoDB的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。再例如，用非单调的字段作为主键在InnoDB中不是个好主意，因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。

2、什么时候选用myisam

      myisam的主键索引的叶子节点只存放数据在物理磁盘上的指针，其他次索引也是一样的；

innodb的主键索引的叶子节点下面直接存放数据，其他次索引的叶子节点指向主键id；

由此可以挖掘出一个问题，就是如果Innodb有大数据列，比如 varchar(300)，这种比较多的话，那么排序的时候用主键id排序会比较慢，因为id主键下面放着所有数据列，而Myisam就不需要扫描数据列，要解决这个问题的话可以再建一个和主键id一起的联合索引；

      MyISAM表索引在处理文本索引时更具优势，而INNODB表索引在其它类型上更具效率优势。比如全文索引一般在CHAR、VARCHAR或TEXT列上创建，MyISAM表支持而INNODB表不支持，常见主要针对文本进行索引。同时MySQL高并发需要事务场景时，只能使用INNODB表。

3、该如何选用两个存储引擎呢

此处参考链接：MySQL中MyISAM与InnoDB区别及选择

      因为MyISAM相对简单所以在效率上要优于InnoDB.如果系统读多，写少。对原子性要求低。那么MyISAM最好的选择。且MyISAM恢复速度快。可直接用备份覆盖恢复。

如果系统读少，写多的时候，尤其是并发写入高的时候。InnoDB就是首选了。

两种类型都有自己优缺点，选择那个完全要看自己的实际类弄。

五、参考资料

1、由浅入深理解InnoDB的索引实现(1):

2、由浅入深理解InnoDB的索引实现(2):

3、关于MyISAM与InnoDB的讲解：

4、关于什么是索引：

5、mysql优化——-Myisam与innodb引擎,索引文件的区别