《高性能Mysql》--聚簇索引

矫情吗；* 2021-09-16 05:48 311阅读 0赞

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。比如，InnoDB的聚簇索引使用B+Tree的数据结构存储索引和数据。

当表有聚簇索引时，它的数据行实际上存放在索引的叶子页(leaf page)中。因为无法同时把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引（不过，覆盖索引可以模拟多个聚簇索引的情况）。

*  术语“聚簇”表示数据行和相邻的键值紧凑地存储在一起。
 *  聚簇索引的二级索引：叶子节点不会保存引用的行的物理位置，而是保存行的主键值。

对于聚簇索引的存储引擎，数据的物理存放顺序与索引顺序是一致的，即：只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的，如果主键不是自增id，可以想象，它会干些什么，不断地调整数据的物理地址、分页，当然也有其他一些措施来减少这些操作，但却无法彻底避免。但，如果是自增的，那就简单了，它只需要一页一页地写，索引结构相对紧凑，磁盘碎片少，效率也高。

对于非聚簇索引的存储引擎，**表数据存储顺序与索引顺序无关**，叶结点包含索引字段值及指向数据页数据行的逻辑指针，其行数量与数据表行数据量一致。

下图1展示了聚簇索引的记录是如何存放的。注意到，节点页只包含了索引列，叶子页包含行的全部数据，这是B+Tree的数据结构。在这个案例中，索引列包含的是整数值。

![Image 1][]

图1 聚簇索引的数据分布

InnoDB将通过主键聚集数据，图1中的“被索引的列”就是主键列。如果没有定义主键，InnoDB会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB会隐式定义一个主键来作为聚簇索引。InnoDB只聚集在同一个页面中的记录，包含相邻键值的页面可能会相距甚远。

聚簇主键可能对性能有帮助，但也可能导致严重的性能问题。所以需要仔细地考虑聚簇索引，尤其是将表的存储引擎从InnoDB改成其他引擎的时候（反过来也一样）。

聚簇的数据有一些重要的优点：

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

如果设计表和查询时能充分利用上面的优点，就能极大地提升性能。但是，聚簇索引也有一些缺点：

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

最后一点可能让人有些疑惑，为什么二级索引需要两次索引查找？答案在于二级索引中保存的”行指针“的实质。要记住，二级索引叶子节点保存的不是指向行的物理位置的指针，而是行的主键值。

这意味着通过二级索引查找行，存储引擎需要找到二级索引的叶子节点获得对应的主键值，然后根据这个值去聚簇索引中查找到对应的行。这里做了重复的工作：两次B-Tree查找而不是一次。对于 InnoDB，自适应哈希索引能够减少这样的重复工作。

InnoDB和MyISAM的数据分布对比

聚簇索引和非聚簇索引的数据分布有区别，以及对应的主要索引和二级索引的数据分布也有区别，通常会让人感到困扰和意外。来看看InnoDB和MyISAM是如何存储下面这个表的：

create table layout_test(
        col1 int not null,
        col2 int not null,
        primary key(col1),
        key(col2)
    );

假设该表的主键取值为1~10000，按照随机顺序播放并使用optimize table命令做了优化。换句话说，数据在磁盘上的存储方式已经最优，但行的顺序是随机的。列col2的值是从1~100之间随机赋值，所以有很多重复的值。

MyISAM的数据布局

MyISAM的B+Tree的叶子节点上的data，并不是数据本身，而是数据存放的地址。MyISAM按照数据插入的顺序存储在磁盘上，如下图2所示，左边为行号(row number)，从0开始。因为元组的大小固定，所以MyISAM很容易的从表的开始位置找到某一字节的位置。

![Image 1][]

图2 MyISAM表layout\_test的数据分布

MyISAM建立的primary key的索引结构大致如图3和图4所示。MyISAM不支持聚簇索引，**索引中每一个叶子节点仅仅包含行号(row number)，且叶子节点按照col1的顺序存储**。MyISAM是按列值与行号来组织索引的。

![Image 1][]

图3 MyISAM表layout\_test的主键分布

在图4中，表一共有三列，假设以Col1为主键，可以看出，MyISAM的叶子节点中保存的实际上是指向存放数据的物理块的指针。**从MYISAM存储的物理文件看出，MyISAM引擎的索引文件（.MYI）和数据文件(.MYD)是相互独立的**，索引文件仅仅保存数据记录的地址。

![Image 1][]

图4 MyISAM主键索引的分布

下图5显示col2 的索引结构，与图3的primary key对比，**索引中每一个叶子节点仅仅包含行号(row number)，且叶子节点按照col2的顺序存储**。在图6中，在Col2建立一个辅助索引，与图4对比，MyISAM的叶子节点也是保存指向存放数据的物理块的指针。

所以，结论是MyISAM的primary key和辅助索引没有任何区别。只是Primary key要求key唯一非空，而辅助索引的key可以重复。

![Image 1][]

图5 MyISAM表layout\_test的col2列索引的分布

![Image 1][]

图6 MyISAM辅助索引的分布

因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。

InnoDB的数据布局

MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

图7和与图3 MyISAM对比看出，InnoDB索引的每一个叶子节点都包含了主键值、事务ID、用于事务和MVCC的回流指针以及所有的剩余列（在这个例子中是col2)。如果主键是一个列前缀索引，InnoDB也会包含完整的主键列和剩下的其他列。这种索引叫做**聚簇索引**。

图8可以看到叶节点包含了完整的数据记录。

因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

![Image 1][]

图7 InnoDB表layout\_test的主键分布

![Image 1][]

图8 InnoDB主键索引的分布

还有一点和MyISAM的不同是，InnoDB的二级索引和聚簇索引很不相同。InnoDB二级索引的叶子节点中存储的不是”行指针“，而是主键值，并以此作为指向行的“指针”。这样的策略减少了当出现行移动或者数据页分裂时二级索引的维护工作。使用主键值当作指针会让二级索引占用更多的空间，换来的好处是，InnoDB在移动行时无须更新二级索引中的这个“指针”。

下图9展示了示例表的二级索引col2索引。每一个叶子节点都包含了索引列（这里是col2)，紧接着是主键值(col1)。图10展示了InnoDB的所有辅助索引都引用主键作为data域。

![Image 1][]

图9 InnoDB表layout\_test的col2列索引的分布

![Image 1][]

图10 InnoDB辅助索引的分布

InnoDB 表是基于聚簇索引建立的。因此InnoDB 的索引能提供一种非常快速的主键查找性能。不过，它的辅助索引（Secondary Index， 也就是非主键索引）也会包含主键列，所以，如果主键定义的比较大，其他索引也将很大。如果想在表上定义 、很多索引，则争取尽量把主键定义得小一些。InnoDB 不会压缩索引。

InnoDB与MyIASM索引和数据布局对比

图7描述InnoDB和MyISAM如何存放表的抽象图。对比InnoDB和MyISAM的主键索引与二级索引。

InnoDB的的二级索引的叶子节点存放的是KEY字段加主键值。因此，通过二级索引查询首先查到是主键值，然后InnoDB再根据查到的主键值通过主键索引找到相应的数据块。而MyISAM的二级索引叶子节点存放的还是列值与行号的组合，叶子节点中保存的是数据的物理地址。所以可以看出MYISAM的主键索引和二级索引没有任何区别，主键索引仅仅只是一个叫做PRIMARY的唯一、非空的索引，且MYISAM引擎中可以不设主键。

![Image 1][]

图7 聚簇和非聚簇表对比图

为了更形象说明这两种索引的区别，我们假想一个表如下图8存储了4行数据。其中id作为主索引，name作为辅助索引。图示清晰的显示了聚簇索引和非聚簇索引的差异。

对于聚簇索引存储来说，行数据和主键B+树存储在一起，辅助键B+树只存储辅助键和主键，主键和非主键B+树几乎是两种类型的树。对于非聚簇索引存储来说，主键B+树在叶子节点存储指向真正数据行的指针，而非主键。

InnoDB使用的是聚簇索引，将主键组织到一棵B+树中，而行数据就储存在叶子节点上，若使用"where id = 14"这样的条件查找主键，则按照B+树的检索算法即可查找到对应的叶节点，之后获得行数据。若对Name列进行条件搜索，则需要两个步骤：第一步在辅助索引B+树中检索Name，到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树种再执行一次B+树检索操作，最终到达叶子节点即可获取整行数据。

MyISM使用的是非聚簇索引，非聚簇索引的两棵B+树看上去没什么不同，节点的结构完全一致只是存储的内容不同而已，主键索引B+树的节点存储了主键，辅助键索引B+树存储了辅助键。表数据存储在独立的地方，这两颗B+树的叶子节点都使用一个地址指向真正的表数据，对于表数据来说，这两个键没有任何差别。由于索引树是独立的，通过辅助键检索无需访问主键的索引树。

![Image 1][]

图8 聚簇和非聚簇表形象对比图

我们重点关注聚簇索引，看上去聚簇索引的效率明显要低于非聚簇索引，因为每次使用辅助索引检索都要经过两次B+树查找，这不是多此一举吗？聚簇索引的优势在哪？  
1 由于行数据和叶子节点存储在一起，这样主键和行数据是一起被载入内存的，找到叶子节点就可以立刻将行数据返回了，如果按照主键Id来组织数据，获得数据更快。  
2 辅助索引使用主键作为"指针" 而不是使用地址值作为指针的好处是，减少了当出现行移动或者数据页分裂时辅助索引的维护工作，使用主键值当作指针会让辅助索引占用更多的空间，换来的好处是InnoDB在移动行时无须更新辅助索引中的这个"指针"。也就是说行的位置（实现中通过16K的Page来定位，后面会涉及）会随着数据库里数据的修改而发生变化（前面的B+树节点分裂以及Page的分裂），使用聚簇索引就可以保证不管这个主键B+树的节点如何变化，辅助索引树都不受影响。

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

如果正在使用InnoDB表并且没有什么数据需要聚集，那么可以定义一个代理键作为主键，这种主键的数据应该和应用无关，最简单的方法是使用auto\_increment自增列。这样可以保证数据行是按照顺序写入，对于根据主键做关联操作的性能也会更好。

最好避免随机的聚簇索引，特别对于I/O密集型的应用。例如，从性能的角度考虑，使用UUID作为聚簇索引会很糟糕：它使得聚簇索引的插入变得完全随机，这是最坏的情况，使得数据没有任何聚集特性。

为了演示这一点，我们做如下两个基准测试。第一个使用整数ID插入shopinfo表，整数ID自增且为主键：

CREATE TABLE `shopinfo` (
      `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '记录ID',
      `shop_id` int(11) NOT NULL COMMENT '商店ID',
      `goods_id` int(11) NOT NULL COMMENT '物品ID',
      `pay_type` int(11) NOT NULL COMMENT '支付方式',
      `price` decimal(10,2) NOT NULL COMMENT '物品价格',
      `comment` varchar(4000) DEFAULT NULL,
      PRIMARY KEY (`id`),
      UNIQUE KEY `shop_id` (`shop_id`,`goods_id`),
      KEY `price` (`price`),
      KEY `pay_type` (`pay_type`),
      KEY `idx_comment` (`comment`(255))
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='商店物品表';

第二个例子是shopinfo\_uuid表，除了主键改为UUID，其余和前面的shopinfo表完全相同。

CREATE TABLE `shopinfo_uuid` (
      `uuid` varchar(36) NOT NULL,
      `shop_id` int(11) NOT NULL COMMENT '商店ID',
      `goods_id` int(11) NOT NULL COMMENT '物品ID',
      `pay_type` int(11) NOT NULL COMMENT '支付方式',
      `price` decimal(10,2) NOT NULL COMMENT '物品价格',
      `comment` varchar(4000) DEFAULT NULL,
      PRIMARY KEY (`uuid`),
      UNIQUE KEY `shop_id` (`shop_id`,`goods_id`),
      KEY `price` (`price`),
      KEY `pay_type` (`pay_type`),
      KEY `idx_comment` (`comment`(255))
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='商店物品表';

我们先向这两个表各插入1万条记录。然后再向这两个表继续插入9万条记录，观察这两个表的插入耗时和表索引大小，下表对测试结果进行比较。其中，查看指定库的指定表shopinfo的索引大小SQL语句：  
`SELECT CONCAT(ROUND(SUM(index_length)/(1024*1024), 2), ' MB') AS 'Total Index Size' FROM TABLES WHERE table_schema = 'study' and table_name = 'shopinfo';`

<table> 
 <thead> 
  <tr> 
   <th>表名</th> 
   <th>行数</th> 
   <th>时间</th> 
   <th>索引大小（MB）</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>shopinfo</td> 
   <td>10000</td> 
   <td>0.755s</td> 
   <td>4.08</td> 
  </tr> 
  <tr> 
   <td>shopinfo_uuid</td> 
   <td>10000</td> 
   <td>1.699s</td> 
   <td>8.16</td> 
  </tr> 
  <tr> 
   <td>shopinfo</td> 
   <td>90000</td> 
   <td>8.014s</td> 
   <td>29.47</td> 
  </tr> 
  <tr> 
   <td>shopinfo_uuid</td> 
   <td>90000</td> 
   <td>46.111s</td> 
   <td>60.58</td> 
  </tr> 
 </tbody> 
</table>

通过测试，插入同样的行数和内容（除主键内容），向UUID主键插入行不仅花费的时间更长，而且索引占用的空间也更大。这一方面是由于主键字段更长，另一方面毫无疑问是由于页分裂和碎片导致的。

如图9所示，由于主键的值是顺序的，InnoDB把每一条记录都存储在上一条记录的后面。当达到页的最大填充因子时（InnoDB默认的最大填充因子是页大小的15/16，留出的部分空间用于以后修改），下一条记录就会写入新的页中。一旦数据按照这样顺序的方式加载，主键页就会近似于被顺序的记录填满，这也是所期望的结果。

![Image 1][]

图9 向聚簇索引插入顺序的索引值

而当采用UUID的聚簇索引的表往插入数据，如图10所示，因为新行的主键值不一定比之前的插入值大，所以InnoDB无法简单的总是把新行插入到索引的最后，而是需要为新的行寻找合适的位置----通常是已有数据的中间位置----并且分配空间。这会增加很多额外的工作，并导致数据分布不够优化。

![Image 1][]

图10 向聚簇索引插入无序的值

下面总结使用UUID作为主键的一些缺点：

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

把这些随机值载入到聚簇索引以后，需要做一次optimize table来重建表并优化页的填充。

注意，顺序主键也有缺点：对于高并发工作负载，在InnoDB中按主键顺序插入可能会造成明显的争用。主键的上界会成为“热点”。因为所有的插入都发生在这里，所以并发插入可能导致间隙锁竞争。另一个热点可能是auto\_increment锁机制；如果遇到这个问题，则可能需要考虑重新设计表或者应用，比如应用层面生成单调递增的主键ID，插表不使用auto\_increment机制，或者更改innodb\_autonc\_lock\_mode配置。

作者：大头8086  
链接：https://www.jianshu.com/p/54c6d5db4fe6  
來源：简书  
简书著作权归作者所有，任何形式的转载都请联系作者获得授权并注明出处。

[Image 1]: