MyISAM存储引擎中的索引方案

MyISAM存储引擎中的索引

我们知道 InnoDB存储引擎中索引即数据，也就是聚集索引的那棵B+树的叶子节点中已经把所有完整的用户记录都包含了，而MyISAM引擎也使用B+Tree作为索引结构，但是却 将索引和数据分开存储，其特点如下：

• MyISAM存储引擎将记录按照插入顺序单独存储在一个.MYD文件中，称之为「数据文件」。这个文件并不划分为若干个数据页，有多少记录就往这个文件中塞多少记录就成了。就不像InnoDB存储引擎在插入数据的时候就将数据放在聚簇索引树中，按照主键大小排序。
• MyISAM存储引擎会把索引信息另外存储到一个.MYI文件中，称之为「索引文件」。MyISAM会单独为表的主键创建一个索引，只不过在索引的叶子节点中存储的不是完整的用户记录，而是「主键值 ＋ 数据记录地址」的组合。

这里设表一共有三列，假设我们以Col1为主键，上图是一个MyISAM表的主索引 (Primary key）示意。可以看出MyISAM的索引文件叶子结点仅仅保存数据记录的地址偏移量。在MyISAM中，主键索号和二级素引(Secondary key）在结构上没有任何区别，只是主键素引要求key是唯一的，而二级素引的key可以重复。如果我们在Col2上建立一个二级索引，则此索引的结构如下图所示：

同样也是一棵B+Tree,根据二级索引的key有序组织，叶子结点的data域保存数据记录的地址偏移量。因此，MyISAM中索引检索的算法为：首先按照B+Tree搜索算法搜索索引，如果指定的key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。

MyISAM 与 InnoDB对比

MyISAM的索引方式都是“非聚簇”的（索引都不包含数据，只包含数据地址），与InnoDB包含1个聚簇索引是不同的。小结两种引擎中索引的区别：

1. 在InnoDB存储引擎中，我们只需要根据主键值对聚簇索引进行一次查找就能命中记录，而在 MyISAM 中都需要进行一次回表操作，意味着MyISAM中建立的索引相当于全部都是二级索引。

2. InnoDB的数据文件.ibd本身就是索引文件，而MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。

3. InnoDB的二级索引叶子节点data域存储相应记录「主键的值」 ，而MyISAM索引记录的是「数据地址」。

4. MyISAM的「回表操作是十分快速」的，因为是拿着地址偏移量直接到文件中取数据的，反观InnoDB是通过获取主键之后再去聚簇索引里找记录，虽然说也不慢，但还是比不上直接用地址去访问。

5. InnoDB要求表必须有主键 （ MyISAM可以没有 ）。如果没有显式指定，则MySQL系统会自动选择一个非空且唯一标识数据记录的列作为主键。如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整型。

此外，除了上面的数据存储方式，MyISAM不支持事务；MyISAM只支持表锁，InnoDB最小支持到行锁；MyISAM不支持外键，而InnoDB支持。

小结：

了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助。比如：

举例1：知道了InnoDB的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键，因为所有二级索引都引用主键索引，过长的主键索引会令二级索引占用的空间过大。

举例2：用非单调的字段作为主键在InnoDB中不是个好主意，因为InnoDB数据文件本身是一棵B+Tree，非单调的主键会造成在插入新记录时，数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。