MySQL - MySQL索引实现原理:数据结构 + 算法原理
总结
必读!!!原文链接:http://blog.codinglabs.org/articles/theory-of-mysql-index.html
- 数据结构选择:MySQL普遍使用带“顺序访问指针”的B+Tree实现其索引结构。
- 数据结构选择B+树的原因:磁盘IO次数少 + 磁盘IO时间短
- 磁盘IO次数少:索引一般以文件形式存储在磁盘上,因此使用磁盘I/O次数就成为评价索引结构的优劣点。检索B+/-树中的某个值,最多需要访问h个节点(h是树的高度)。由于B+/-树的一个节点包含更多的值,h不会很大 (相比于二叉树,一个节点只有一个值。当“值”的总数一致时,二叉树的高度h会更大,需要更多次IO)。
- 磁盘IO时间短:磁盘IO时间 = 寻道时间(耗时) + 旋转时间(很快)。 MySQL设计者给B+/-树申请一个新节点时,直接申请一个页的空间(不必寻道,只有短暂的旋转时间),保证了每个节点只需要一次I/O就可以完全载入。注意:页是内存的最小存储单位,常见为4K;扇区是磁盘的最小存储单位,常见为512Byte。这里说的一个页的空间,应该是指4K空间。
- 特别的,B+比B-更适合。因为B+树内节点(非叶子节点)没有data域,所以有更多的空间存储指向下一级的指针,也就是说树的高度h会更低,时间更短。
- 非聚集索引 vs 聚集索引
- MyISAM 是非聚集索引:索引和数据分开,索引只保存了数据记录的地址。Primary Key(主键/主索引)和Secondary Key(辅助索引)在结构上没有任何区别,只是主索引要求key是唯一的,而辅助索引的key可以重复。
- InnoDB 是聚集索引: 索引和数据在一起,Primary Key(主键/主索引)的叶节点直接包含数据记录,Secondary Key(辅助索引)保存的是Primary Key的值。因此如果用Secondary Key(辅助索引)需要检索两遍索引:首先检索辅助索引获得主键,然后用主键到主索引中检索获得记录。
1. MySQL索引的数据结构
MySQL就普遍使用1.3 的结构 - 带“顺序访问指针”的B+Tree实现其索引结构
1.1 B-Tree
1.2 B+Tree
1.3 带有顺序访问指针的B+Tree
如图4所示,在B+Tree的每个叶子节点增加一个指向相邻叶子节点的指针,就形成了带有顺序访问指针的B+Tree。做这个优化的目的是为了提高区间访问的性能,例如图4中如果要查询key为从18到49的所有数据记录,当找到18后,只需顺着节点和指针顺序遍历就可以一次性访问到所有数据节点,极大提到了区间查询效率。
2. Primary Key和Secondary Key的区别
2.1 MyISAM 两者没有区别
2.2 InnoDB 两者有区别