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

区别

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

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

这里要特别注意页的概念，一个页可以理解为一块具有一定大小的连续的存储区域。相同页内的数据行在物理上是相邻的，因此逻辑上键值相邻的页在物理上可能相隔很远。

在中间的某个节点页中，主键<11的叶子页和11 <主键> <21的叶子页分别被两个指针所指向，且主键<11的叶子页也有一个指针指向了11 <主键> <21的叶子页，其余页之间的关系也是一样。

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

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

聚簇索引具有唯一性。由于聚簇索引是将数据跟索引结构放到一块，因此一个表仅有一个聚簇索引

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

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

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

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

实例

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

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

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

聚簇索引的优势

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

1、聚簇索引将索引和数据行保存在同一个B-Tree中，查询通过聚簇索引可以直接获取数据，相比非聚簇索引需要第二次查询（非覆盖索引的情况下）效率要高。

2、聚簇索引适合用在排序的场合，非聚簇索引不适合

3、聚簇索引对于范围查询的效率很高，因为其数据是按照大小排列的

4、二级索引需要两次索引查找，而不是一次才能取到数据，因为存储引擎第一次需要通过二级索引找到索引的叶子节点，从而找到数据的主键，然后在聚簇索引中用主键再次查找索引，再找到数据

5、可以把相关数据保存在一起。例如实现电子邮箱时，可以根据用户 ID 来聚集数据，这样只需要从磁盘读取少数的数据页就能获取某个用户的全部邮件。如果没有使用聚簇索引，则每封邮件都可能导致一次磁盘 I/O。

聚簇索引的劣势

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

2、表因为使用UUId（随机ID）作为主键，使数据存储稀疏，这就会出现聚簇索引有可能有比全表扫面更慢，所以建议使用int的auto_increment作为主键

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

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

为什么主键通常建议使用自增id

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

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

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

mysql中聚簇索引的设定

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

参考：1、聚簇索引与非聚簇索引：https://www.jianshu.com/p/fa81928531842、MySQL中Innodb的聚簇索引和非聚簇索引：https://blog.csdn.net/u013132035/article/details/82193763