mysql聚簇索引详解

聚簇索引

不是一种单独的索引类型，而是一种数据存储方式。innodb的聚簇索引实际上在同一个结构中保存了B-tree索引和数据行。

当表有聚簇索引时，数据行实际上是存储在索引的叶子页中。

聚簇：表示数据行和相邻的键值紧凑地存储在一起。一个表只能有一个聚簇索引

聚簇索引如何存放记录如图：

聚簇索引的优点：

    可以把相关数据保存在一起

    数据访问更快(聚集索引将索引和数据保存在同一个b-tree中)

    使用覆盖索引扫描的查询可以直接使用页节点中的主键值

聚簇索引的缺点：

    聚簇数据提高了IO性能，如果数据全部放在内存中，则访问的顺序就没那么重要了

    插入速度严重依赖插入顺序。按主键的顺序插入是速度最快的。但如果不是按照主键顺序加载数据，则需在加载完成后最好使用optimize table重新组织一下表

    更新聚簇索引列的代价很高。因为会强制innod将每个被更新的行移动到新的位置

    基于聚簇索引的表在插入新行，或主键被更新导致需要移动行的时候，可能面临页分裂的问题。页分裂会导致表占用更多的磁盘空间。

    聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或由于页分裂导致数据存储不连续的时

    非聚集索引比想象的更大，因为二级索引的叶子节点包含了引用行的主键列

    非聚集索引访问需要两次索引查找(非聚集索引中叶子节点保存的行指针指向的是行的主键值)，对于innodb自适应哈希索引可以减少这样的重复工作

innodb和myisam的数据分布对比

myisam的数据分布按照数据插入顺序存储在磁盘上，如图：

主键分布，如图:

col2列索引分布，如图：

总结：myisam主键索引和其他索引在结构上没有不同。主键索引就是一个名为primary的非空唯一索引

innodb的数据分布。存储数据方式，如图：

说明：聚簇索引的每个叶子节点都包含了键值、事务ID、用于事务和MVCC的回滚指针以及所有的剩余列。如果主键是一个列前缀索引，innodb也会包含完整的主键列和剩下的其他列

使用主键值当作指针会让非聚集索引占用更多的空间，带来的好处是:innodb在移动时无须更新非聚集索引中的这个‘指针’。

innodb非叶子节点包含了索引列和一个指向下级节点的指针(下一级可以是非叶子节点，也可以是叶子节点)

col2存储数据方式，如图：

innodb和myisam保存数据和索引的抽象图：

在innodb表中按主键顺序插入行

如果使用innodb表没有什么数据需要聚集，可以定义一个代理键作为主键，使用auto_increment自增列。这样可以保证数据行是按顺序写入，对于主键做关联操作的性能也会更好

向聚簇索引插入顺序的索引值，如图：

说明：innodb把每一条记录都存储在上一条记录的后面。当达到页的最大填充因子(默认是页大小的15/16，留下部分空间用于修改)，下一条记录会被写入新的页中

使用UUID插入聚簇索引的表，如图：

说明：因为新行的主键值不一定比之前插入的大，索引无法简单的总是把新行插入到索引的最后，而是需要为新的行寻找合适的位置——通常是已有数据的中间位置并分配空间。（会增加额外的工作，和数据分布不够优化）

缺点：

    1.写入的目标页可能已经刷到磁盘上并从缓冲中删除，或还没被加载到缓存中，innodb在插入之前不得不先找到然后从磁盘中读取目标页到内存中。会导致大量的随机IO

    2.因为写入是乱序的，innodb不得不频繁的做页分裂操作，以便为新的行分配空间。页分裂会导致大量移动数据，一次插入需要修改多个页(最少三页)而不是一个页

    3.由于频繁的页分裂，页会变得稀疏并不被规则填充，所以数据会有碎片

顺序主键在什么时候会有更坏的结果：对于高并发工作负载，在innodb中按主键顺序插入可能造成明显的争用。可能导致间隙锁竞争和auto_increment锁机制。

译者介绍：家华，从事mysqlDBA的工作，记录自己对mysql的一些总结