面试官：知道数据库是如何通过索引定位数据

前言

我们之前讲了MySQL索引的底层数据结构，以及索引的失效原理等知识点。那么索引在硬盘上到底是怎么存储的呢？怎么通过索引定位查询出一条真实的数据呢？

MySQL两大引擎MyIsam和InnoDB他们的索引又有哪些不同点呢？

今天我们就来给大家揭晓一下

MyIsam 和 InnoDB常见区别

事务方面

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

外键方面

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

索引层面

InnoDB 是聚集（聚簇）索引，MyISAM 是非聚集（非聚簇）索引。后面会重点讲解这两种索引的区别。

MyISAM支持 FULLTEXT类型的全文索引， InnoDB不支持FULLTEXT类型的全文索引，但是InnoDB可以使用sphinx插件支持全文索引，并且效果更好。

锁粒度方面

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

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

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

硬盘存储结构

MyISAM在磁盘上存储成三个文件。第一个文件的名字以表的名字开始，扩展名指出文件类型。

• .frm文件存储表的定义。

• 数据文件的扩 展名为.MYD (MYData)。

• 索引文件的扩 展名是.MYI (MYIndex)。

Innodb存储引擎存储数据库数据，一共有两个文件(没有专门保存数据的文件)：

• Frm文件：表的定义文件。

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

聚簇索引 和 非聚簇索引

聚簇索引（InnoDB）

将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据。

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

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

• 聚簇索引默认是主键，

• 如果表中没有定义主键，InnoDB 会选择一个唯一的非空索引代替。

• 如果没有这样的索引，InnoDB 会在内部生成一个名为 GEN_CLUST_INDEX 的隐式的聚簇索引。

非聚簇索引（MyISAM）

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

MyISAM索引查询数据过程

非聚簇索引存储结构：

源于网络

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

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

• 根据id值在B+树上找到相应的叶子节点

• 取出叶子节点上的数据存储地址

• 根据数据存储地址，去找到相应的真实数据

InnoDB索引查询数据过程

聚簇索引存储结构：

源于网络

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

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

聚簇索引（主键索引）：

• 根据id值在B+树上找到相应的叶子节点

• 取出叶子节点上的行数据，返回即可

辅助索引（聚簇索引以外的）：

• 在相应索引的B+树上找到相应的叶子节点

• 取出叶子节点上的数据，该数据是主键id

• 拿到主键id后，去主键索引的B+树上找到相应的叶子节点

• 取出叶子节点上的行数据，返回

总结

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

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

但是InnoDB普通索引查询会比MyIsam慢些，因为InnoDB要进行2次B+树的查找。

还比如说在数据重构的时候，MyIsam记录的是数据地址，那么重构数据的时候地址就要重新生成一遍，这也是有问题的。

InnoDB重构数据的时候就不会这样，因为他记录的是主键id，地址会变化，主键id是不会变的。

还有为什么少用select *，为什么尽量使用覆盖索引，大家能根据他们的索引结构推测出来吗，给大家留道思考题？

每个技术都是存在优缺点的，我们还是那句话，对于技术我们要扬长避短，根据我们的业务场景，发挥出技术的长处。

PS：如果觉得我的分享不错，欢迎大家随手点赞、转发。