MySQL - MySQL索引实现原理：数据结构 + 算法原理

阅读目录(Content)

• 总结
• 1. MySQL索引的数据结构
• 1.1 B-Tree
• 1.2 B+Tree
• 1.3 带有顺序访问指针的B+Tree
• 2. Primary Key和Secondary Key的区别
• 2.1 MyISAM 两者没有区别
• 2.2 InnoDB 两者有区别

回到顶部(go to top)

总结

必读！！！原文链接：http://blog.codinglabs.org/articles/theory-of-mysql-index.html

1. 数据结构选择：MySQL普遍使用带“顺序访问指针”的B+Tree实现其索引结构。
2. 数据结构选择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会更低，时间更短。
3. 非聚集索引 vs 聚集索引
   • MyISAM 是非聚集索引：索引和数据分开，索引只保存了数据记录的地址。Primary Key(主键/主索引)和Secondary Key(辅助索引)在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。
   • InnoDB  是聚集索引：   索引和数据在一起，Primary Key(主键/主索引)的叶节点直接包含数据记录，Secondary Key(辅助索引)保存的是Primary Key的值。因此如果用Secondary Key(辅助索引)需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

 

回到顶部(go to top)

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后，只需顺着节点和指针顺序遍历就可以一次性访问到所有数据节点，极大提到了区间查询效率。

 

 

 

回到顶部(go to top)

2. Primary Key和Secondary Key的区别

2.1 MyISAM 两者没有区别

 

 

 

 

2.2 InnoDB 两者有区别