MySQL索引定位数据过程

MySQL主要有MyIsam和InnoDB引擎，它们通过索引定位数据的过程是不一样的。

首先说下MyIsam和InnoDB的常见区别

1、事务

InnoDB 支持事务，MyISAM 不支持事务。这是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一。

2、外键

InnoDB 支持外键，而 MyISAM 不支持。对一个包含外键的 InnoDB 表转为 MYISAM 会失败。

3、索引

InnoDB 是聚集（聚簇）索引，MyISAM 是非聚集（非聚簇）索引。

4、锁粒度方面

InnoDB最小的锁粒度是行锁，MyISAM最小的锁粒度是表锁

一个更新语句会锁住整张表，导致其他查询和更新都会被阻塞，因此并发访问受限。

这也是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一。

5、硬盘存储结构

InnoDB存储引擎存储数据库数据，一共有两个文件（没有专门保存数据的文件）：

Frm文件：表的定义文件

Ibd：数据和索引存储文件。数据以主键进行聚集存储，把真正的数据保存在叶子节点中。

MyISAM的话，在磁盘上存储成三个文件。（不同的扩展名）

.frm文件存储 表的定义。

.MYD 数据文件（MYData）

.MYI 索引文件 （MYIndex）

聚簇索引 和 非聚簇索引

1.聚簇索引（InnoDB）
将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据。

表数据按照索引的顺序来存储的，也就是说索引项的顺序与表中记录的物理顺序一致。

InnoDB中，在聚簇索引之上创建的索引称之为辅助索引，像复合索引、前缀索引、唯一索引等等。

• 聚簇索引默认是主键，
• 如果表中没有定义主键，InnoDB 会选择一个唯一的非空索引代替。
• 如果没有这样的索引，InnoDB 会在内部生成一个名为 GEN_CLUST_INDEX 的隐式的聚簇索引。

2.非聚簇索引（MyISAM）

将数据与索引分开存储，表数据存储顺序与索引顺序无关。

MyISAM索引查询数据过程

非聚簇索引存储结构：

 MyISAM的B+树的叶子节点上，记录的是真实数据的存储地址。

比如通过主键id查询，MyISAM查询流程如下：

1. 根据id值在B+树上找到相应的叶子节点。
2. 取出叶子节点上的数据存储地址。
3. 根据数据存储地址，去找到相应的真实数据。

InnoDB索引查询数据过程

聚簇索引存储结构：

 InnoDB的B+树的叶子节点上，记录的是真实行数据。

比如通过主键id查询，InnoDB查询流程如下：

聚簇索引（主键索引）：

1. 根据id值在B+树上找到相应的叶子节点。
2. 取出叶子节点上的行数据，返回即可。

辅助索引（在聚簇索引之上创建的索引，复合索引、前缀索引、唯一索引）

1. 在相应索引的B+树上找到相应的叶子节点
2. 取出叶子节点上的数据，该数据是主键id
3. 拿到主键id后，去主键索引的B+树上找到相应的叶子节点
4. 取出叶子节点上的行数据返回。

总结

以上就是两个引擎索引查询流程，MyIsam 和 InnoDB各有优缺点。

比如通过主键id查询的时候，InnoDB比MyIsam快一些，因为InnoDB只需要一次B+树查找就能取出数据。MyIsam通过B+树查找到地址后，还需要根据地址去查询真正的数据。但是InnoDB普通索引查询会比MyIsam慢些，因为InnoDB要进行2次B+树的查找。

还比如说在数据重构的时候，MyIsam记录的是数据地址，那么重构数据的时候地址就要重新生成一遍，这也是有问题的。InnoDB重构数据的时候就不会这样，因为他记录的是主键id，地址会变化，主键id是不会变的。

来源：IT老哥的微信公众号面试官：知道数据库是如何通过索引定位数据