MySQL存储引擎MyISAM和InnoDB底层索引结构_mysql的innodb和myisam 底层数据结构

一、存储引擎作用于什么对象

存储引擎是作用在表上的，而不是数据库。

二、MyISAM和InnoDB对索引和数据的存储在磁盘上是如何体现的

创建的两张表信息，role表使用的存储引擎是MyISAM，而user使用的是InnoDB：

再来看下两张表在磁盘中的索引文件和数据文件：

1. role表有三个文件，对应如下：

role.frm：表结构文件
role.MYD：数据文件（MyISAM Data）
role.MYI：索引文件（MyISAM Index）

2. user表有两个文件，对应如下：

user.frm：表结构文件
user.ibd：索引和数据文件（InnoDB Data）

也由于两种引擎对索引和数据的存储方式的不同，我们也称MyISAM的索引为非聚集索引，InnoDB的索引为聚集索引。

三、MyISAM主键索引与辅助索引的结构

我们先列举一部分数据出来分析，如下：

上面已经说明了MyISAM引擎的索引文件和数据文件是分离的，我们接着看一下下面两种索引结构异同。

1. 主键索引：

     上一篇文章已经介绍过数据库索引是采用B+Tree存储，并且只在叶子节点存储数据，在MyISAM引擎中叶子结点存储的数据其实是索引和数据的文件指针两类。

如下图中我们以Col1列作为主键建立索引，对应的叶子结点储存形式可以看一下表格。

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

              

       通过索引查找数据的流程：

1. 先从索引文件中查找到索引节点，从中拿到数据的文件指针，可以看出MyISAM的索引文件仅仅保存数据记录的地址。
2. 再到数据文件中通过文件指针定位了具体的数据。这一步是非常耗费资源的。

四、InnoDB主键索引与辅助索引的结构

1. 主键索引：
      我们已经知道InnoDB索引是聚集索引，它的索引和数据是存入同一个.idb文件中的，因此它的索引结构是在同一个树节点中同时存放索引和数据，如下图中最底层的叶子节点有三行数据，对应于数据表中的Col1、Col2、Col3数据项。

虽然InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同。

       第一个重大区别是InnoDB的数据文件本身就是索引文件。从上文知道，MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

      上图是InnoDB主索引（同时也是数据文件）的示意图，可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

2. 辅助（非主键）索引：

这次我们以数据表中的Col3列的字符串数据建立辅助索引，它的索引结构跟主键索引的结构有很大差别，我们来看下面的图：

在最底层的叶子结点有两行数据，第一行的字符串是辅助索引，按照ASCII码进行排序，第二行的整数是主键的值。

五、InnoDB索引结构需要注意的点

1. 数据文件本身就是索引文件

2. 表数据文件本身就是按B+Tree组织的一个索引结构文件

3. 聚集索引中叶节点包含了完整的数据记录

4. InnoDB表必须要有主键，并且推荐使用整型自增主键

     正如我们上面介绍InnoDB存储结构，索引与数据是共同存储的，不管是主键索引还是辅助索引，在查找时都是通过先查找到索引节点才能拿到相对应的数据，如果我们在设计表结构时没有显式指定索引列的话，MySQL会从表中选择数据不重复的列建立索引，如果没有符合的列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，并且这个字段长度为6个字节，类型为整型。