Innodb 引擎和 Myisam 引擎对索引的实现

一、写在前面——索引类型

在MySQL数据库，将索引的具体类型主要分为以下几类：主键索引、唯一索引、常规索引、全文索引。

主键索引

含义：针对于表中主键创建的索引
特点：默认自动创建, 只能有一个，非空
关键字：PRIMARY

唯一索引

含义：避免同一个表中某数据列中的值重复
特点：可以有多个，可以为空
关键字：UNIQUE

常规索引

含义：快速定位特定数据
特点：可以有多个
关键字：无

全文索引

含义：全文索引查找的是文本中的关键词，而不是比较索引中的值
特点：可以有多个
关键字：FULLTEXT

二、Innodb 引擎和 Myisam 引擎对索引的实现

Myisam 虽然数据查找性能极佳，但是不支持事务处理。Innodb 最大的特色就是支持了 ACID 兼容的事务功能，而且他支持行级锁。Mysql 建立表的时候就可以指定引擎。B+树作为 Mysql 的索引的数据结构非常合适，那么两种引擎是怎么实现的呢？

在执行建表语句并指定引擎后，Innodb 生成的文件有：
frm:创建表的语句
idb:表里面的数据+索引文件

Myisam 生成的文件有:
frm:创建表的语句
MYD:表里面的数据文件（myisam data）
MYI:表里面的索引文件（myisam index）

从生成的文件看来，这两个引擎底层数据和索引的组织方式并不一样，MyISAM 引擎把数据和索引分开了，一人一个文件，这叫做非聚集索引方式；Innodb 引擎把数据和索引放在同一个文件里了，这叫做聚集索引方式。

三、底层分析

MyISAM 引擎的底层实现（非聚集索引方式）

MyISAM 用的是非聚集索引方式，即数据和索引落在不同的两个文件上。MyISAM 在建表时以主键作为 KEY 来建立主索引 B+树，树的叶子节点存的是索引所在行对应数据的物理地址。通过这个物理地址后，就可以到 MyISAM 数据文件中直接定位到具体的数据记录了。

Innodb 引擎的底层实现（聚集索引方式、非聚集索引）

而在在InnoDB存储引擎中，根据索引的存储形式，又可以分为以下两种：

• 主索引（聚集索引）

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

InnoDB 的主键索引是聚集索引方式，数据和索引都存储在同一个文件里。首先 InnoDB 会根据主键 ID 作为 KEY 建立索引 B+树，而 B+树的叶子节点存储的是主键 ID 对应的数据。在根据主键ID查询时，会查询这颗主键ID的索引树，找到对应叶子结点的数据。

建表的时候，InnoDB就会建好主键ID的索引树，这也是为什么 Mysql 在建表时要求必须指定主键的原因。

聚集索引选取规则：
如果存在主键，主键索引就是聚集索引。 如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。
如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。

• 辅助索引（非聚集索引）

将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键
用于加速数据的查找，一张表上有多个辅助索引提升数据库的性能，所以可以有多个

在为其他字段建立索引时，非叶子结点存储当前字段的key，叶子结点存储主键的key。得到主键key后，才会在主键索引树中找到当前字段所对应的数据。

四、为什么 InnoDB 只在主键索引树的叶子节点存储了具体数据，但是其他索引树却不存具体数据呢，而要多此一举先找到主键，再在主键索引树找到对应的数据呢？

因为 InnoDB 要节省存储空间。一个表里可能有很多个索引，如果给每个加了索引的字段生成索引树，都存储了具体数据，那么这个表的索引数据文件就变得非常巨大（数据极度冗余了）。从节约磁盘空间的角度来说，没有必要，通过这种看似“多此一举”的步骤，在牺牲较少查询的性能下节省了巨大的磁盘空间。

五、为什么InnoDB 和MyISAM 对比，MyISAM 查询性能更好？

从上面索引文件数据文件的设计来看也可以看出原因：

MyISAM 直接找到物理地址后就可以直接定位到数据记录。
InnoDB 查询到叶子节点后，还需要再查询一次主键索引树，才可以定位到具体数据。
等于 MyISAM 一步就查到了数据，但是 InnoDB 要两步，所以 MyISAM 查询性能更高。