聚簇索引及 InnoDB 与 MyISAM 数据分布对比

1. 概述

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。 不同的存储引擎有着不同的实现方式，对于 InnoDB，聚簇索引意味着将 B-Tree 索引与数据行存储在同一个结构中。 “聚簇”指的就是数据行和相邻的键值紧凑的存储在一起。 因为每一个行都只能存储在唯一的地方，所以一个表只能有一个聚簇索引。

2. 实现

并不是所有的存储引擎都支持聚簇索引。 对于 InnoDB，他将主键建立为聚簇索引，叶子页包含了全部数据，而节点页则只包含了索引列。 InnoDB 只保证让同一页面中的数据聚集在一起，而相邻页面的数据可能相距甚远，因此聚簇索引也可能产生严重的性能问题。

3. 优点

1. 由于相关数据保存在了一起，所以只需要从磁盘读取少数的数据页就能获取主键对应的全部数据，如果没有使用聚簇索引，则可能会消耗多次磁盘操作 2. 由于索引和数据保存在同一个 B-Tree 中，查找索引的同时也就找到了对应的数据，因此从聚簇索引中获取数据通常比在非聚簇索引中查找数据要快 3. 使用覆盖索引扫描的查询可以直接使用页节点中的主键值

4. 缺点

当然，聚簇索引也有一些缺点。 1. 对于 IO 密集型应用，聚簇索引的性能提升是很明显的，但是如果数据全部都在内存中，聚簇索引也就没什么优势了 2. 更新聚簇索引的代价很高，因为会强制 InnoDB 将每个被更新的行移动到新的位置，同时，也可能面临“页分裂”的问题，即插入或更新的行所在的页面已满，则需要分裂为两个新页来容纳该行，导致更多的磁盘空间占用 3. 聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏或由于页分裂导致数据存储不连续的时候 4. 二级索引（非聚簇索引）可能比想象要更大，因为二级索引的叶子节点包含了引用行的主键 5. 由于二级索引的叶子及诶单保存的是“行指针” — 主键值，二级索引也因此需要两次索引查找而不是一次，InnoDB 的自适应哈希索引能够减少这样的重复工作

5. InnoDB 与 MyISAM 数据分布对比

由于 InnoDB 使用聚簇索引，所以 InnoDB 和 MyISAM 在数据分布上的区别主要是聚簇索引与非聚簇索引的区别，主键索引和二级索引的数据分布也因此有区别。

5.1. MyISAM 数据分布

MyISAM 数据分布非常简单，数据按插入顺序存储在磁盘上，由于每行数据都是定长的，所以 MyISAM 可以根据行号迅速跳过相应的字节数定位需要查找的数据。 这种分布方式很容易创建索引，在索引 B+ 树的叶子节点上，每个节点都是一个值-行号的键值对，通过查找到值，同时也就找到了相应的行号，通过上述跳过相应字节数就可以快速定位数据了。 这样的索引与数据存储方式和索引是否是主键索引无关。

5.2. InnoDB 数据分布

由于 InnoDB 的主键索引采用聚簇索引，所以数据存储方式与 MyISAM 非常不同。 对于主键索引，每个叶子节点上存储了一行的全部数据，还要包含事务 ID以及用于事务和 MVCC 的回滚指针，这样通过主键查询就得到了全部的真实数据。 对于二级索引，与 MyISAM 不同，叶子节点键值对的值并不是行号而是主键，通过索引找到主键后，再通过主键聚簇索引找到相应的数据。

这样的设计虽然有着上面所说的聚簇索引的那些优势，但是缺点也是很明显的，如果聚簇索引自增，那么新的数据总会被插入到主键索引树的最后，这样无论是插入还是查询效率都是很高的，但是如果聚簇索引的索引值随机插入，那么，因为频繁的更新索引结构，导致页分裂，从而让索引占用的磁盘空间和查询效率都明显降低。 同时，如果是用 AUTO_INCREMENT 作为主键，当大量并发的时候，那么必须用互斥量加锁，以免两次 insert 操作使用相同的主键值，这样会严重的影响并发性能。 mysql 5.1.22 之后，mysql 通过预判的方式生成 AUTO_INCREMENT 主键，也就是说，如果某次 insert 会将 AUTO_INCREMENT 主键增加到 3，则在 insert 执行前，先将 AUTO_INCREMENT 值预分配到4，则并发的下次插入并不会因为上一次 insert 操作没有完成而导致冲突。

可以通过 innodb_autoinc_lock_mode 进行配置。

• innodb_autoinc_lock_mode = 0 (“traditional” lock mode：全部使用表锁)
• innodb_autoinc_lock_mode = 1 (默认)(“consecutive” lock mode：可预判行数时使用新方式，不可时使用表锁)
• innodb_autoinc_lock_mode = 2 (“interleaved” lock mode：全部使用新方式，不安全，不适合replication)