谈谈你对聚簇索引的理解

通俗的讲

聚簇索引：

• 将数据存储和索引放到了一块，找到了索引也就找到了数据
• 一般情况下主键会默认创建聚簇索引，且一张表只允许存在一个聚簇索引。

非聚簇索引：

• 将数据存储于索引分开结构，索引结构的叶子节点指向了数据的对应行。
• MyISAM通过key_buffer把索引先缓存到了内存中，当需要访问数据时(通过索引访问数据)，在内存中直接查找索引，然后通过索引找到磁盘相应数据。这也就是为什么索引不在key buffer命中时，速度慢的原因。

聚集索引⼀个表只能有⼀个，⽽⾮聚集索引⼀个表可以存在多个。聚集索引存储记录是物理上连续存在，⽽⾮聚集索引是逻辑上的连续，物理存储并不连续。

InnoDB中，在聚集索引上创建的索引叫做辅助索引

辅助索引访问数据总是需要二次查找，非聚簇索引都是辅助索引，像复合索引、前缀索引、唯一索引、辅助引擎叶子节点存储的不再是行的物理位置，而是主键值。

定义

聚簇索引：

• 聚簇索引就是按照每张表的主键构造一颗B+树，同时叶子节点中存放的就是整张表的行记录数据，也将聚簇索引的叶子节点称为数据页。这个特性决定了索引组织表中数据也是索引的一部分，每张表只能拥有一个聚簇索引
• InnoDB通过主键聚集数据，如果没有定义主键，InnoDB会选择非空的唯一索引代替。如果没有这样的索引，InnoDB会隐式定义一个主键来作为聚簇索引

辅助索引（非聚簇索引）

• 在聚集索引之上创建的索引叫做辅助索引
• 辅助索引访问数据总是需要二次查找，第一次找到主键值，第二次根据主键值找到行数据：
  • 辅助索引叶子节点存储的不再是行的物理位置，而是主键值。
  • 通过辅助索引首先找到的是主键值，再通过主键值找到数据行的数据页，再通过数据页中的Page Directory找到数据行。
• 辅助索引的存储不影响数据在聚簇索引中的组织，所以一张表可以由多个辅助索引。在innodb中有时也称辅助索引为二级索引

聚簇索引具有唯一性

由于聚簇索引是将数据跟索引结构放在一块，所以一个表仅有一个聚簇索引

一个误区：把主键设为聚簇索引

• 聚簇索引默认是主键。
• 如果表中没有定义主键，InnoDB会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB会隐式定义一个主键作为聚簇索引。
• InnoDB只聚集在同一页面中的记录，包含相邻键值的页面可能相聚很远
• 如果你已经设置了主键为聚簇索引，必须先删除主键，然后添加我们想要的聚簇索引，之后恢复设置主键即可

此时其他索引只能被定义为非聚簇索引。这里是最大的误区，有的主键还是无意义的自动增量字段，那样的话clusteed index对效率的帮助，完全被浪费了

刚才说了，聚簇索引性能最大而且具有唯一性，所以非常珍贵，必须慎重设置。一般要根据这个表最常用的SQL查询方式来就那些选择，某个字段作为聚簇索引，或者组合聚簇索引，这个要看实际情况。

记住我们最终目的就是在相同结果集的情况下，尽可能减少IO逻辑

• InnoDB使用的是聚簇索引，将主键组织到一颗B+树中，而行数据就存储在叶子节点上，如使用"where id = 14"这样的条件查找主键，则按照B+树的检索算法就可以查找到对应的叶子节点，之后获得行数据

• 如果对Name类进行条件搜索，则需要两个步骤：第一步在辅助索引B+树中检索Name，到达其叶子节点获取对应的主键；第二步使用主键在主索引B+t树中再执行一次B+树的检索操作，最终到达叶子节点即可获取整行数据（重点在于通过其他键需要建立辅助索引）

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

聚簇索引的优势

数据访问更快：

• 数据访问更快：
  • 由于行数据和叶子节点存储在一起，同一页中会有多条数据，访问同一数据的不同行记录时，已经把也加载到了Buffer中，再次访问的时候，会在内存中完成访问，不必访问磁盘
  • 这样由于主键和行数据时一起被载入磁盘的，找到叶子节点就可以立即将行数据返回了，如果按照主键ID来组织数据，获得数据更快
• 聚簇索引对主键的排序查找和范围查找速度非常快
  • 聚簇索引适合用在排序的场合，非聚簇索引不适合
  • 取出一定范围数据的时候，使用用聚簇索引

聚簇索引的缺点

插入速度严重依赖于插入顺序

• 按照主键的顺序插入是最快的方式，否则将出现页分裂，严重影响性能。因此，对于InnoDB表，我们

  一般都会定义一个自增的ID作为主键

  • 主键的值是顺序的，所以InnoDB会将每一条记录都存储在上一天记录的后面。
  • 当达到页的最大填充因子（InnoDB 默认的最大填充因子是页大小的 15/16，留出部分空间用于以后修改）下一条记录就会被写入到新的页中。
  • 一旦数据按照这种顺序的方式被加载，主键也就会近乎与被顺序的记录填满（二级索引页可能是不一样的）

• 维护索引很昂贵，特别是插入新行或者主键被更新导至要分页(page split)的时候。因此，对于InnoDB表，我们一般定义主键不可更新；另外，建议在大量插入新行后，选在负载较低的时间段，通过OPTIMIZE TABLE优化表，因为必须被移动的行数据可能造成碎片。使用独享表空间可以弱化碎片

• 如果主键比较大的时候，那辅助索引将会变得更大，因为辅助索引的叶子存储的是主键值；过长的主键值，会导致非叶子节点占用更多的物理空间