MySQL如何通过索引找到一条真实的数据

MySQL如何通过索引找到一条真实的数据

Mylsan和 InnoDB常见区别

事务方面

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

外键方面

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

索引层面

innoDB是聚集(聚簇)索引, MyISAM是非聚集(非聚簇)索引。后面会重点讲解这两种索引的区别
MyISAM支持 FULLTEXT类型的全文索引,
innoDB不支持 FULLTEXT类型的全文索引,但是 innoDB可以使用 sphinx插件支持全文索引,并且效果更好。

锁粒度方面

innoDB最小的锁粒度是行锁, MyISAM最小的锁粒度是表锁。
一个更新语句会锁住整张表,导致其他查询和更新都会被阻塞,因此并发访问受限

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

硬盘存储结构

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

• frm文件存储表的定义
• 数据文件的扩展名为.MND( MYData)
• 素引文件的扩展名是,MYr( MYIndex)

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

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

聚簇索引 和非聚簇索引

聚簇索引( InnoDB)

将数据存储与素引放到了一块,索引结构的叶子节点保存了数据（B+ tree）
表数据按照索引的顺序来存储的,也就是说索引项的顺序与表中记录的物理顺序一致。
InnoDB中,在聚簇索引之上创建的索引称之为辅助索引,像复合索引、前缀索引、唯一索引等等。

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

非聚簇索引( MyISAM)

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

MyISAM索引查询数据过程

该图的左边是索引文件，右边是数据文件。

索引文件和数据文件是分开存储的。

之前提到过，索引是B+ tree存储的，左图的非叶子节点就是主键id，叶子结点存储的除了id外，还有每一行数据的物理存储地址，通过B+ tree找到id对应的地址，再通过右边的地址映射找到数据。

InnoDB索引结构

对于InnoDB来说，它的索引和数据是存储到同一个文件里面去的，

• 左图叫聚簇索引：InnoDB中以主键id为索引key
• 右图是辅助索引：别的字段为索引

InnoDB同样是B+ tree，而这里和MyISAM引擎不同的是，叶子节点储存的不再是物理地址，而是真实的数据！

比如找id为15的数据，通过左图的B+ tree找到它对应的叶子结点，直接就把它整行的数据都拿出来了，不用再去找物理地址了！【和MyISAM区分开】

右图：辅助索引的叶子节点存储的是辅助索引key和它对应的id主键，而不是整行的数据！所以如果要如果user_name这个辅助索引找整行的数据，比如找user_name = Eric的整行数据，先要通过右图辅助索引找到它对应的id，然后再通过左图的聚簇索引找到它对应的叶子节点，从而才能拿到整行的数据！

这样就解释了，为什么 select的时候，尽量使用覆盖索引，也就是和后面检索条件中的索引尽可能一致或者是查它id，这样一次查到，而不用再去左图聚簇索引中查了！

对比MyISAM和InnoDB

• MyISAM：无论是主键索引还是普通索引，查找数据的流程都是一样的，都会先去B+ tree找到对应叶子节点，再去物理地址取数据。【先找地址，再通过地址找数据】

• InnDB，查找数据的流程分为两种类型

  • 聚簇索引：通过主键id能够直接取出全部数据（叶子节点中存放真实数据）
  • 辅助索引：通过索引找到对应id，再通过聚簇索引的B+ tree找到真实数据，要找两次！

• InnoDB在聚簇索引的查询过程中比MyISAM快一些；InnoDB辅助索引的查询过程在一定程度上比MyISAM慢一些