mysql 聚簇索引

聚簇索引概念

• 聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。具体的细节依赖于其实现方式，但 InnoDB 的聚簇索引实际上在同一个结构中保存了 B-Tree 索引和数据行。
• 聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。具体的细节依赖于其实现方式，但 InnoDB 的聚簇索引实际上在同一个结构中保存了 B-Tree 索引和数据行。当表有聚簇索引时，它的数据行实际上存放在索引的叶子页 (leaf page) 中。术语“聚簇”表示数据行和相邻的键值紧凑地存储在一起。因为无法同时把数据行存放在两个不同的地方,所以一个表只能有一个聚簇索引。
• 如果没有定义主键，InnoDB 会选择一个唯一的非空索引代替。如果没有这样的索引 InnoDB 会隐式定义一个主键来作为聚簇索引。InnoDB 只聚集在同一个页面中的记录，包含相邻键值的页面可能会相距其远。
• 聚簇主键可能对性能有帮助，但也可能导致严重的性能问题。所以需要仔细地考虑聚簇索引，尤其是将表的存储引擎从 InnoDB 改成其他引警的时候。

聚簇索引的优点

1. 可以把相关数据保存在一起。例如实现电子邮箱时，可以根据用户ID 来聚集数据这样只需要从磁盘读取少数的数据页就能获取某个用户的全部邮件。如果没有使用聚簇索引，则每封邮仕都可能导致一次磁盘 I/O。
2. 数据访问更快。聚簇索引将索引和数据保存在同一 B-Tree 中，因此从聚簇索引中获取数据通常比在非聚簇索引中查找要快。
3. 使用覆盖索引扫描的查询可以直接使用页节点中的主键值。

聚簇索引的劣势

1. 聚簇数据最大限度地提高了 I/O 密集型应用的性能，但如果数据全部都放在内存中则访问的顺序就没那么重要了，聚簇索引也就没什么优势了。
2. 插入速度严重依赖于插人顺序。按照主键的顺序插入是加载数据到 InnoDB 表中速度最快的方式。但如果不是按照主键顺序加载数据，那么在加载完成后最好使用 OPTIMIZE TABLE 命令重新组织一下表更新聚簇索引列的代价很高，因为会强制 InnoDB 将每个被更新的行移动到新的位置。
3. 基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临页分裂(page split)的问题。当行的主键值要求必须将这一行插入到某个已满的页中时，存储引擎会将该页分裂成两个页面来容纳该行，这就是一次页分裂操作页分裂会导致表占用更多的磁盘空间。
4. 聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候。
5. 二级索引(非聚簇索引)可能比想象的要更大，因为在二级索引的叶子节点包含了引用行的主键列。
6. 二级索引访问需要两次索引查找，而不是一次。

为什么不能用 UUID 作为聚簇索引

使用 UUID 作为聚簇索引，新插入行的主键值不一定比之前插人的大，所以InnoDB 无法简单地总是把新行插人到索引的最后，而是需要为新的行寻找合适的位置一一通常是已有数据的中间位置一一并且分配空间。这会增加很多的额外工作，并导致数据分布不够优化。下面是总结的一些缺点:

1. 写入的目标页可能已经刷到磁盘上并从缓存中移除，或者是还没有被加载到缓存中 InnoDB 在插人之前不得不先找到并从磁盘读取目标页到内存中。这将导致大量的随机 I/O。
2. 因为写入是乱序的，InnoDB 不得不频繁地做页分裂操作，以便为新的行分配空间页分裂会导致移动大量数据，一次插入最少需要修改三个页而不是一个页。
3. 由于频繁的页分裂，页会变得稀疏并被不规则地填充，所以最终数据会有碎片。

在把这些随机值载人到聚簇索引以后，也许需要做一次 OPTIMIZE TABLE 来重建表并优化页的填充。

顺序的主键什么时候会造成更坏的结果?

对于高并发工作负载，在InnoDB 中按主键顺序插入可能会造成明显的争用。主键的上界会成为“热点”。因为所有的插入都发生在这里，所以并发插入可能导致间隙锁竞争。另一个热点可能是 AUTO_INCREMENT 锁机制；如果遇到这个问题，则可能需要考虑重新设计表或者应用，或者更改 innodb_autoinc_lock_mode 配置。如果你的服务器版本还不支持 innodb_autoinc_lock_mode 参数，可以升级到新版本的InnoDB，可能对这种场景会工作得更好。