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慢一些