MySQL中的(非)聚簇索引与索引覆盖详解_myisam非聚簇索引-CSDN博客

本文链接：https://blog.csdn.net/J080624/article/details/78415124

关联博文：
MySQL索引基础入门详解
 MySQL索引中的数据结构详解
 MySQL中的(非)聚簇索引与索引覆盖详解

这篇文章主要介绍mysql中innodb的聚簇索引和非聚簇索引，那首先我们要先看下聚簇索引和非聚簇索引的概念是什么，是干什么用的。

【1】聚簇索引

① 什么是聚簇索引

索引按是否聚集可以分为聚簇(聚集)索引和非聚簇(聚集)索引两种 。我们也把非聚集索引称为二级索引或辅助索引。

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。具体的细节依赖于其实现方式，但InnoDB的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行，这里的B-Tree指的就是B+树。

当表有聚簇索引时，它的数据行实际上存放在索引的叶子页中。术语“聚簇”表示数据行和相邻的键值紧凑地存储在一起。因为无法同时把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。

聚簇索引的每一个叶子节点都包含了主键值、事务ID、用于事务和MVCC的回滚指针以及所有的剩余列。即 B+树的叶子节点存储的是完整的用户记录。所谓完整的用户记录，就是指这个记录中存储了所有列的值（包括隐藏列）。

如果主键是一个列前缀索引，InnoDB页会包含完整的主键列和剩下的其余列。

聚簇索引使用记录主键值的大小进行记录和页的排序，这包括三个方面的含义：

页内的记录是按照主键的大小顺序排成一个单向链表
各个存放用户记录的页也是根据页中用户记录的主键大小顺序排成一个双向链表
存放目录项(索引项)记录的页分为不同的层次，在同一层次中的页也是根据页中目录项记录的主键大小顺序排成一个双向链表。

将数据存储与索引放到了一块，找到索引也就找到了数据。InnoDB的数据文件本身就是主键索引文件，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键

② 什么时候创建聚簇索引

聚簇索引并不需要我们在MySQL语句中显示的使用INDEX语句去创建，InnoDB存储引擎会自动的为我们创建聚簇索引。

每个InnoDB表都有一个特殊的索引，称为聚簇索引，用于存储行数据。

如果创建了一个主键，InnoDB会将其用作聚簇索引(如果主键没有逻辑唯一且非空的列或列集，最好是设置成自动递增的)
如果没有为表创建主键，则MySQL会在所有键列都不为NULL的情况下找到第一个UNIQUE索引，InnoDB会将其用作聚集索引
如果表没有PRIMARY KEY或合适的UNIQUE索引，则InnoDB在包含行ID值的合成列上内部生成一个名为GEN_CLUST_INDEX的隐藏的聚集索引(隐藏的是看不到的，也就是说不会出现在desc table中，行ID是一个6字节的字段，随着插入新行而单调增加)

从这三种情况来看的话，就是说不管你有没有创建主键，mysql都会给你弄一个聚簇索引给安排上，你创建了就用你设置的主键为聚簇索引，没有创建就给你来个隐藏的。

为了充分利用聚簇索引的聚簇的特性，所以InnoDB表的主键列尽量选取有序的顺序ID，而不建议用无序ID，比如UUID、MD5、HASH、字符串列作为主键无法保证数据的顺序增长

③ 优缺点

优点

数据访问更快。聚簇索引将索引和数据保存在同一个BTree中，因此从聚簇索引中获取数据通常比在非聚簇索引查找要快。

聚簇索引对于主键的排序查找和范围查找速度非常快。

按照聚簇索引排序顺序，查询显示一定范围数据的时候，由于数据都是紧密相连，数据库不用从多个数据块中提取数据，所以节省了大量的磁盘IO操作。

缺点

插入速度严重依赖于插入顺序。按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键。

更新聚簇索引列的代价很高。因为会强制innodb将每个被更新的行移动到新的位置。因此，对于InnoDB表，我们一般定义主键为不可更新。

基于聚簇索引的表在插入新行或者主键被更新导致需要移动行的时候可能面临“页分裂”的问题。当行的主键值要求必须将这一行插入到某个已满的页中时，存储引擎会将该页分裂成两个页面来容纳该行，这就是一次页分裂操作。页分裂会导致表占用更多的磁盘空间。

对于MySQL数据库目前只有InnoDB存储引擎支持聚簇索引，而MyISAM并不支持聚簇索引。

【2】非聚簇索引

① InnoDB

或者又称之为辅助索引、二级索引等等。innodb中，基于聚簇索引创建的索引称之为辅助索引，辅助索引访问数据总是需要二次查找。

非聚簇索引都是辅助索引，像复合索引、前缀索引、唯一索引等。

辅助索引叶子节点存储了主键值。二级索引（非聚簇索引）可能比想象的要更大，因为在二级索引的叶子节点包含了引用行的主键列。要记住，InnoDB下二级索引叶子节点保存的不是指向行的物理位置的指针，而是行的主键值(以及当前索引列键值)。

这也是为什么不建议使用过长的字段作为主键，因为所有二级索引都引用主键索引，过长的主键索引会令二级索引变得过大。

辅助索引访问需要两次索引查找。非聚簇索引查询数据需要先查到聚簇索引的key，然后用这个key去聚簇索引(InnoDB下)查询真正的数据(这个过程称为回表)。

聚簇索引的非叶子节点包含了索引列(主键列)和一个指向子节点(数据页)的指针（下级节点可以是非叶子节点，也可以是叶子节点）。这对于聚簇索引和二级索引都适用。

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

所以能走聚簇索引的尽量走聚簇索引(也可以说是尽量走主键)，看起来都是走索引，实际上主键要更快。而且主键索引如果是自增的int类型，因为长度比较小，占用的空间也比较小。
在这里插入图片描述

InnoDB和MyISAM默认的索引都是BTree索引(在MySQL官方中BTree指的是B+树)，而Memory默认的索引是hash索引（InnoDB和MyISAM不支持Hash索引）。

② MyISAM

MyISAM的B+Tree的叶子节点上的data，并不是数据本身，而是数据记录的地址。主索引和辅助索引没啥区别，只是主索引中的key一定是唯一的而二级索引的key可以重复。

MyISAM的索引都是非聚簇索引，其将索引和数据分开存储：

将表中的记录按照记录的插入顺序单独存储在一个文件中，称之为数据文件。这个文件并不划分为若干个数据页，有多少记录就往这个文件中塞多少记录就成了。由于在插入数据的时候并没有刻意按照主键大小排序，所以我们并不能在这些数据上使用二分法进行查找。
使用MyISAM存储引擎的表会把索引信息另外存储到一个称为索引文件的另一个文件中。MyISAM会单独为表的主键创建一个索引，只不过在索引的叶子节点中存储的不是完整的用户记录，而是主键值+数据记录地址的组合。

MyISAM还采用压缩机制存储索引，比如，第一个索引为“her”，第二个索引为“here”，那么第二个索引会被存储为“3,e”，这样的缺点是同一个节点中的索引只能采用顺序查找。

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

③ InnoDB的B+树索引形成过程

每当为某个表创建一个B+树索引（聚簇索引不是人为创建的，默认就有）的时候，都会为这个索引创建一个根节点页面，最开始表中没有数据的时候，每个B+树索引对应的根节点中既没有用户记录，也没有目录项记录。

随后向表中插入用户记录时，先把用户记录存储到这个根节点中。

当根节点中的可用空间用完时继续插入记录，此时会将根节点中的所有记录复制到一个新分配的页，比如页a中。然后对这个新页进行页分裂的操作，得到另一个新页，比如页b。这时新插入的记录根据键值（也就是聚簇索引中的主键值，二级索引中对应的索引列的值）的大小就会被分配到页a 或者页 b 中，而根节点便升级为存储目录项记录的页。

这个过程特别注意的是：一个B+树索引的根节点自诞生之日起，便不会再移动。这样只要我们对某个表建立一个索引，那么它的根节点的页号便会被记录到某个地方，然后凡是 InnoDB存储引擎需要用到这个索引的时候，都会从那个固定的地方取出根节点的页号，从而来访问这个索引。

那么内节点目录页（非叶子节点）是如何存放的呢？保证内节点中目录项记录的唯一性。 为了让新插入记录能找到自己在哪个页里，我们需要保证B+树的同一层内节点的目录项记录除页号这个字段以外是唯一的。所以对于二级索引的内节点的目录项记录的内容实际上是由三个部分构成的：索引列的值、主键值以及页号。

④ 何时使用聚集索引或非聚集索引

索引按是否聚集可以分为聚簇索引和非聚簇索引两种。

聚簇索引是按照数据存放的物理位置为顺序的，而非聚簇索引就不一样了。
聚簇索引能提高多行检索的速度，而非聚簇索引对于单行的检索很快。

动作描述	使用聚集索引	使用非聚集索引
列经常被分组排序	使用	使用
返回某范围内的数据	使用	不使用
一个或极少不同值	不使用	不使用
小数目的不同值	使用	不使用
大数目的不同值	不使用	使用
频繁更新的列	不使用	使用
外键列	使用	使用
主键列	使用	使用
频繁修改索引列	不使用	使用

【3】覆盖索引|索引覆盖

① 什么是覆盖索引？

索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据。因此它不必读取整个行，毕竟索引叶子节点存储了它们索引的数据。当能通过读取索引就可以得到想要的数据，那就不需要读取行了。一个索引包含了满足查询结果的数据就叫做覆盖索引。

其是非聚簇复合索引的一种形式，它包括在查询里的select、join和where子句用到的所有列（即建索引的字段正好是覆盖查询条件中所涉及的字段）。

简单说就是，索引列+主键包含select到from之间查询的列。

如果要查询的字段都建立过索引，那么引擎会直接在索引表中查询而不会访问原始数据（否则只要有一个字段没有建立索引就会做全表扫描），这叫索引覆盖。

因此我们需要尽可能的在select后只写必要的查询字段，以增加索引覆盖的几率。这里值得注意的是不要想着为每个字段建立索引，因为优先使用索引的优势就在于其体积小。

比如我们要查询上面的test表中的age和name两个字段。

select id，age，name from test where age = 13;

直接查询的话，会根据age的索引找到id的key，然后再用id去查询出数据。但是如果我们创建一个(age，name)的联合索引，因为要返回的值，id在联合索引指向的主键上，age和name共同组成了联合索引，因此数据都在(age，name)的联合索引上，并不需要回表再去查询一次，可以大大提高查询得效率。

② 覆盖索引的好处

索引条目通常远小于数据行大小，所以如果只需要读取索引，那么MySQL就会极大地减少数据访问量。这对缓存的负载非常重要，因为这种情况下响应时间大部分花费在数据拷贝上。

覆盖索引对于IO密集型的应用也有帮助，因为索引比数据更小，更容易全部放入内存中（这对于MyISAM尤其重要，因为MyISAM能压缩索引以变得更小）。

因为索引是按照列值顺序存储的（至少在单个页内是如此），所以对于IO密集型的范围查询会比随机从磁盘读取每一行数据的IO要少的多。对于某些存储引擎，例如MyISAM和Percona XtraDB，甚至可以通过OPTIMIZE命令使得索引完全顺序排列，这让简单的范围查询能使用完全顺序的索引访问。

由于覆盖索引是按键值的顺序存储的，对于IO密集型的范围查找来说，对比随机从磁盘读取每一行的数据IO要少的多，因此利用覆盖索引在访问时也可以把磁盘的随机读取的IO转变成索引查找的顺序IO。比如查询一个范围时，如果需要回表，那么数据可能并不在同一个数据页(段或者区)，则就是近似一个随机IO。如果不需要回表，那么在二级索引上就是一个近似的顺序IO。

由于InnoDB的聚簇索引，覆盖索引对于InnoDB表特别有用。InnoDB的二级索引在叶子节点保存了行的主键值，所以如果是二级主键鞥能够覆盖查询，则可以避免对主键索引的二次查询。

注: 还有在某些count聚合函数使用的时候可以使用覆盖索引来优化count，比如说select count(age) from test。因为age是有索引了，直接使用到的也是age，所以覆盖索引了，无需回表。

覆盖索引能提高查询效率，主要是避免了回表的操作，查询得时候根据具体情况建立合适的索引走覆盖索引提高查询速度。

不是所有类型的索引都可以成为覆盖索引。覆盖索引必须要存储索引列的值，而哈希所以、空间索引和全文索引等都不存储索引列的值，所以MySQL只能使用B-Tree索引做覆盖索引。