聚簇索引与非聚簇索引（也叫二级索引）

艰深点讲

• 聚簇索引：将数据存储与索引放到了一块，找到索引也就找到了数据
• 非聚簇索引：将数据存储于索引离开构造，索引构造的叶子节点指向了数据的对应行，myisam 通过 key_buffer 把索引先缓存到内存中，当须要拜访数据时（通过索引拜访数据），在内存中间接搜寻索引，而后通过索引找到磁盘相应数据，这也就是为什么索引不在 key buffer 命中时，速度慢的起因

廓清一个概念：innodb 中，在聚簇索引之上创立的索引称之为辅助索引，辅助索引拜访数据总是须要二次查找，非聚簇索引都是辅助索引，像复合索引、前缀索引、惟一索引，辅助索引叶子节点存储的不再是行的物理地位，而是主键值

何时应用聚簇索引与非聚簇索引

聚簇索引具备唯一性

因为聚簇索引是将数据跟索引构造放到一块，因而一个表仅有一个聚簇索引

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

聚簇索引默认是主键 ，如果表中没有定义主键，InnoDB 会抉择一个 惟一的非空索引 代替。如果没有这样的索引，InnoDB 会 隐式定义一个主键 来作为聚簇索引。InnoDB 只汇集在同一个页面中的记录。蕴含相邻健值的页面可能相距甚远。如果你曾经设置了主键为聚簇索引，必须先删除主键，而后增加咱们想要的聚簇索引，最初复原设置主键即可。

此时其余索引只能被定义为非聚簇索引。这个是最大的误区。有的主键还是无意义的主动增量字段，那样的话 Clustered index 对效率的帮忙，齐全被节约了。

方才说到了，聚簇索引性能最好而且具备唯一性，所以十分宝贵，必须谨慎设置。个别要依据这个表最罕用的 SQL 查问形式来进行抉择，某个字段作为聚簇索引，或组合聚簇索引，这个要看理论状况。

记住咱们的 最终目标 就是 在雷同后果集状况下，尽可能减少逻辑 IO。

联合图再认真点看

image

image

1. InnoDB 应用的是聚簇索引，将 主键组织到一棵 B + 树 中，而 行数据就贮存在叶子节点 上，若应用 ”where id = 14″ 这样的条件查找主键，则 依照 B + 树的检索算法即可查找到对应的叶节点，之后取得行数据。
2. 若 对 Name 列进行条件搜寻，则须要两个步骤 ： 第一步在辅助索引 B + 树中检索 Name，达到其叶子节点获取对应的主键 。第二步 应用主键在主索引 B + 树种再执行一次 B + 树检索操作，最终达到叶子节点即可获取整行数据 。（ 重点在于通过其余键须要建设辅助索引）

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

聚簇索引的劣势

看上去聚簇索引的效率显著要低于非聚簇索引，因为 每次应用辅助索引检索都要通过两次 B + 树查找，这不是多此一举吗？聚簇索引的劣势在哪？

1. 因为 行数据和叶子节点存储在一起，同一页中会有多条行数据，拜访同一数据页不同行记录时，曾经把页加载到了 Buffer 中，再次拜访的时候，会在内存中实现拜访 ，不用拜访磁盘。这样 主键和行数据是一起被载入内存的，找到叶子节点就能够立即将行数据返回 了，如果依照主键 Id 来组织数据，取得数据更快。
2. 辅助索引应用主键作为 ” 指针 ” 而不是应用地址值作为指针的益处 是，缩小了当呈现行挪动或者数据页决裂时辅助索引的保护工作 ， 应用主键值当作指针会让辅助索引占用更多的空间，换来的益处是 InnoDB 在挪动行时毋庸更新辅助索引中的这个 ” 指针 ”。也就是说行的地位（实现中通过 16K 的 Page 来定位）会随着数据库里数据的批改而发生变化（后面的 B + 树节点决裂以及 Page 的决裂），应用聚簇索引就能够保障不论这个主键 B + 树的节点如何变动，辅助索引树都不受影响。
3. 聚簇索引适宜用在排序的场合，非聚簇索引不适宜
4. 取出肯定范畴数据的时候，应用用聚簇索引
5. 二级索引须要两次索引查找，而不是一次能力取到数据，因为存储引擎第一次须要通过二级索引找到索引的叶子节点，从而找到数据的主键，而后在聚簇索引中用主键再次查找索引，再找到数据
6. 能够把 相干数据保留在一起。例如实现电子邮箱时，能够依据用户 ID 来汇集数据，这样只须要从磁盘读取多数的数据页就能获取某个用户的全副邮件。如果没有应用聚簇索引，则每封邮件都可能导致一次磁盘 I/O。

聚簇索引的劣势

1. 保护索引很低廉，特地是插入新行或者主键被更新导至要分页 (page split) 的时候。倡议在大量插入新行后，选在负载较低的时间段，通过 OPTIMIZE TABLE 优化表，因为必须被挪动的行数据可能造成碎片。应用独享表空间能够弱化碎片
2. 表因为应用 UUId（随机 ID）作为主键，使数据存储稠密，这就会呈现聚簇索引有可能有比全表扫面更慢，

image

所以倡议应用 int 的 auto_increment 作为主键

image

主键的值是程序的，所以 InnoDB 把每一条记录都存储在上一条记录的前面。当达到页的最大填充因子时（InnoDB 默认的最大填充因子是页大小的 15/16，留出局部空间用于当前批改），下一条记录就会写入新的页中。一旦数据依照这种程序的形式加载，主键页就会近似于被程序的记录填满（二级索引页可能是不一样的）

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

为什么主键通常倡议应用自增 id

聚簇索引的数据的物理寄存程序与索引程序是统一的 ，即： 只有索引是相邻的，那么对应的数据肯定也是相邻地寄存在磁盘上的。如果主键不是自增 id，那么能够想 象，它会干些什么，一直地调整数据的物理地址、分页，当然也有其余一些措施来缩小这些操作，但却无奈彻底防止。但，如果是自增的，那就简略了，它只须要一 页一页地写，索引构造绝对紧凑，磁盘碎片少，效率也高。

因为 MyISAM 的主索引并非聚簇索引，那么他的数据的物理地址必然是凌乱的，拿到这些物理地址，依照适合的算法进行 I / O 读取，于是开始不停的寻道不停的旋转。 聚簇索引则只需一次 I /O。（强烈的比照）

不过，如果 波及到大数据量的排序、全表扫描、count 之类的操作的话，还是 MyISAM 占优势些，因为索引所占空间小，这些操作是须要在内存中实现的。

mysql 中聚簇索引的设定

聚簇索引 默认是主键 ，如果表中没有定义主键，InnoDB 会抉择一个 惟一的非空索引 代替。如果没有这样的索引，InnoDB 会 隐式定义一个主键 来作为聚簇索引。InnoDB 只汇集在同一个页面中的记录。蕴含相邻健值的页面可能相距甚远。