MyISAM 与 InnoDB 索引的区别有哪些?

MyISAM索引实现

1. MyISAM使用非聚集索引（Unclustered Index）方式，将索引和行记录分开存储。
2. 主键索引和普通索引在本质上没有太大差异。
3. 行记录单独存储，对于主键索引和普通索引，它们都是独立的索引B+树，索引树的叶子节点存储索引列的值以及对应行记录的指针。

当使用索引列进行查找时，首先会定位到B+树的叶子节点，然后通过指针找到对应的行记录。

需要注意的是，MyISAM表可以没有主键，但是如果定义了主键，则会有一个主键索引。

举例来说，假设有一个MyISAM表t，它包含以下结构：

t(id PK, name KEY, age)

在这个表中，有四条记录：

在这个表中，id是主键，并有一个主键索引。同时，name列也有一个普通索引。根据表结构，可以构建针对id和name的独立索引B+树，其中叶子节点分别指向对应的行记录。

当执行以下查询语句时：

select * from t where name=“A”;

首先，通过索引name定位到B+树的叶子节点，获取0x01地址，然后通过地址定位到对应的行记录。

InnoDB索引实现

1. InnoDB使用聚集索引（Clustered Index）方式，将主键索引和行记录存储在一起。
2. 聚集索引的叶子节点存储主键的值以及对应的行记录，而不是存储指向行记录的指针。
3. 普通索引的叶子节点存储主键的值。

由于聚集索引的存储方式，InnoDB表必须具有聚集索引，具体如下：

1. 如果表定义了主键（PK），那么主键就是聚集索引。
2. 如果表没有定义主键，但存在第一个非空的唯一列，那么该列就成为聚集索引。
3. 如果表既没有定义主键，也没有唯一列，则InnoDB会创建一个隐藏的row-id作为聚集索引。

与聚集索引不同，InnoDB的普通索引可以有多个。普通索引的叶子节点存储主键值，而不是指针。

举例来说，假设有一个InnoDB表t，它包含以下结构：

t(id PK, name KEY, age)

在这个表中，有四条记录：

对应的B+树索引构造如图所示，id作为主键索引，行记录与id索引存储在一起，name作为普通索引，叶子节点存储id。

当执行以下查询语句时：

select * from t where name=“A”;

首先，通过辅助索引name定位到B+树的叶子节点，获取id=1，然后通过聚集索引定位到对应的行记录。

这意味着实际上进行了两次索引树的扫描。

区别总结：

• MyISAM使用非聚集索引，将索引和行记录分开存储；InnoDB使用聚集索引，将主键索引和行记录存储在一起。
• MyISAM的索引树的叶子节点存储指向行记录的指针；InnoDB的聚集索引叶子节点存储主键的值和行记录。
• MyISAM的表可以没有主键，而InnoDB的表必须具有聚集索引。
• MyISAM的普通索引叶子节点存储指向行记录的指针；InnoDB的普通索引叶子节点存储主键的值。