天天看点

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

MyISAM索引实现

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

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

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

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

t(id PK, name KEY, age)

在这个表中,有四条记录:

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

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

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

当执行以下查询语句时:

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)

在这个表中,有四条记录:

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

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

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

当执行以下查询语句时:

select * from t where name=“A”;

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

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

区别总结:

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