MySQL聚集索引与非聚集索引相关总结

目录

一、概述

话不多说，先来问几个问题：

二、聚集索引

三、非聚集索引

四、聚集索引和非聚集索引在MySQL的实现

五、聚集索引与非聚集索引的区别

六、适用场景

七、总结

一、概述

话不多说，先来问几个问题：

什么是聚集索引？什么是非聚集索引？聚集索引和非聚集索引有什么区别？为什么 InnoDB 只在主键索引树的叶子节点存储具体数据，但是其他索引树却不存具体数据，而是存储主键值？为什么索引会提高检索速度？为什么针对区分度低的字段，例如性别，不建议建立索引？为什么不建议使用mysql内置函数，而是要放在业务代码中去处理？为什么明明建了索引，但是SQL执行还是很慢？为什么MySQL建议用自增ID做主键索引，而不是UUID、雪花算法等？

接下来，我们带着上述几个问题开始我们今天的主题 -- MySQL聚集索引和非聚集索引的介绍。

二、聚集索引

(一)、概念

聚集（clustered）索引，也叫聚簇索引。它是指数据库表行中数据的物理顺序与键值的逻辑（索引）顺序相同。因为一个表的物理顺序只有一种情况，所以每个表的聚集索引只能有一个。

聚集索引类比我们的新华字典，按字母a-z排序，并且后面的所有字也是按照a-z的顺序排序，这就是聚集索引。聚集索引的叶子结点即存储了真实的数据行，所以通过聚集索引可以直接获取到数据库中的数据。

(二)、示意图

下图是聚集索引的存储示例图：

从上图可以看出，聚集索引的叶子节点就是对应的数据节点，可以直接获取到对应的全部列的数据，而后面我们即将介绍的非聚集索引，查询的时候可能需要二次查询，因此在查询方面，聚集索引的速度往往会比非聚集索引快。

(三)、如何生成聚集索引

以MySQL为例，聚集索引通常是表的主键，这也是为什么InnoDB要求表必须有主键，InnoDB会按照如下规则进行处理：

1，如果一个主键被定义了，那么这个主键就是作为聚集索引；

2，如果没有主键被定义，那么该表的第一个唯一非空索引被作为聚集索引；

3，如果没有主键也没有合适的唯一索引，那么innodb内部会生成一个隐藏的主键作为聚集索引，这个隐藏的主键是一个6个字节的列，该列的值会随着数据的插入自增；

(四)、注意点

尽量在创建表的时候添加聚集索引，由于聚集索引的物理顺序上的特殊性，因此如果再在上面创建索引的时候会根据索引列的排序移动全部数据行上面的顺序，会非常地耗费时间以及性能。

三、非聚集索引

(一)、概念

非聚集（unclustered）索引，即索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同，所以一个表中可以拥有多个非聚集索引。

与聚集索引不同的是，非聚集索引的叶子节点不存储具体的数据页， 叶子结点包含索引字段值及指向数据行的逻辑指针。所以非聚集索引不能直接获取到数据，需要通过逻辑指针进行二次查找来获取数据。

(二)、示意图

下面是非聚集索引的存储示意图：

从上图可以看到，非聚集索引的叶子结点并没有存储真正的数据，它只是存储了指向具体数据化的地址，第一次查找只能获取到数据行的地址，还需要经过第二次去数据文件中查找对应的数据【也就是回表】。

(三)、如何解决非聚集索引的二次查询问题

我们其实可以使用覆盖索引来解决非聚集索引的二次查询问题。建立两列以上的索引，即可查询复合索引里的列的数据而不需要进行回表二次查询，如表中建立了联合索引index(col1, col2)，如下SQL：

select col1, col2 from t1 where col1 = '213';

就可以避免二次查询，因为从索引列就已经可以查询到对应的数据返回了。

注意：使用复合索引需要满足最左侧索引的原则，也就是查询的时候如果where条件里面没有最左边的一到多列，索引就不会起作用。

四、聚集索引和非聚集索引在MySQL的实现

我们来看一下聚集索引和非聚集索引在MySQL两种储存引擎的底层实现。

(一)、Innodb 引擎

InnoDB 是聚集索引方式，数据和索引都存储在同一个文件里。

【主索引搜索过程】

首先 InnoDB 会根据主键 ID 作为 KEY 建立索引 B+树，如上图所示，而 B+树的叶子节点存储的是主键 ID 对应的数据，比如在执行 select * from user_info where id=15 这个语句时，InnoDB 就会查询这颗主键 ID 索引 B+树，找到对应的 user_name=‘Bob’。

【辅助索引搜索过程】

比如我们要给 user_name 这个字段加索引，那么 InnoDB 就会建立 user_name 索引 B+树，节点里存的是 user_name 这个 KEY，叶子节点存储的数据的是主键 KEY。

注意，叶子存储的是主键 KEY，拿到主键 KEY 后，InnoDB 才会去主键索引树里根据刚在user_name 索引树找到的主键 KEY 查找到对应的数据。【两次查询，回表操作】

为什么 InnoDB 只在主键索引树的叶子节点存储了具体数据，但是其他索引树却不存具体数据呢，而要先找到主键，再在主键索引树找到对应的数据呢?

因为 InnoDB 需要节省存储空间，一个表里可能有很多个索引，InnoDB 都会给每个加了索引的字段生成索引树，如果每个字段的索引树都存储了具体数据，那么这个表的索引数据文件就变得非常巨大（数据极度冗余）。

(二)、MyISAM 引擎

MyISAM 用的是非聚集索引方式，数据和索引落在不同的两个文件上。

如上图，MyISAM 在建表时以主键作为 KEY 来建立主索引 B+树，树的叶子节点存的是对应数据的物理地址。我们拿到这个物理地址后，就可以到 MyISAM 数据文件中直接定位到具体的数据记录了。

当我们为某个字段添加索引时，我们同样会生成对应字段的索引树，该字段的索引树的叶子节点同样是记录了对应数据的物理地址，然后也是拿着这个物理地址去数据文件里定位到具体的数据记录。

五、聚集索引与非聚集索引的区别

聚集索引一个表只能有一个，而非聚集索引一个表可以存在多个；聚集索引存储记录是物理上连续存在，而非聚集索引是逻辑上的连续，物理存储并不连续；通过聚集索引一次就可以查到需要查找的数据， 而通过非聚集索引第一次只能查到记录对应的主键值 ，需要回表再使用主键的值通过聚集索引查找到需要的数据；因此与非聚集索引相比，聚集索引有着更快的检索速度；

六、适用场景

聚集索引适用情况：

1、含有大量非重复值的列；2、使用BETWEEN,>,>=,<或<=返回一个范围值的列；3、被连续访问的列；4、返回大型结果集的查询；5、经常被使用连接或GROUP BY子句的查询访问的列；

下表总结了何时使用聚集索引或非聚集索引：

七、总结

本篇文章主要总结了MySQL中聚集索引和非聚集索引，从概念、数据存储、数据查询过程、两者区别、适用场景等方面，总结了两者的不同以及在不同的存储引擎中的实现。众所周知，索引有助于提高检索性能，但过多或不当的索引也会导致系统低效或者索引碎片，因为索引也会占用磁盘空间。因此我们要记住不是所有的表都适合建立索引，只有数据量大表才适合建立索引，且建立在选择性高的列上面性能才会提高。由于笔者水平有限，文中如有不对之处，希望指正，希望这篇文章对大家有所帮助。

参考资料：

学习资料分享

MySQL高性能书籍_第3版（中文）MySQL技术内幕-InnoDB存储引擎_第2版