innodb 聚簇索引_innodb索引结构「建议收藏」

之前学习了数据库实战45讲的前半部分，觉得应该需要停下来沉淀一下，过一阵子再去继续学习。

今天，来分析一下InnoDB存储引擎管理的InnoDB表如何基于聚簇索引建立的！

其实之前在学校的数据库课程上，我一直以为聚簇索引是一种索引类型，然后结合InnoDB存储引擎，自然而然的便认为聚簇索引即表中的B+树主键索引，殊不知一直理解的是错误的概念，前阵子在学习<高性能MySQL>这本书中，才真正了解到聚簇索引的真正含义，下面结合书中的内容，来做一个全面的分析：

首先聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。

innodb 聚簇索引_innodb索引结构「建议收藏」 — **聚簇索引的数据分布**

当表中存在聚簇索引时，它的数据行实际上存放在索引的叶子页(leaf page)中，对应到B+树索引上就是其叶子节点。术语”聚簇”表示数据行和相邻的键值(这里应该默认说的是主键)紧凑的存储在一起。因为无法同时把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引(但是，我们知道覆盖索引的特点，在使用覆盖索引的时候，便可以模拟多个聚簇索引的情况)。

注释：由于索引是由存储引擎来实现的，因此不是所有的存储引擎都支持聚簇索引。

如果表没有定义主键，InnoDB会选择一个唯一的非空索引代替(一般为主键索引)。如果没有这样的索引，InnoDB会隐式定义一个主键来作为聚簇索引(这里我们知道一定是rowid隐藏主键索引)。

下面依次来看看聚簇索引的优点与缺点：

优点：

可以把相关数据保存在一起。例如实现电子邮箱时，可以根据用户ID来聚集数据，这样只需要从磁盘读取少数的数据页就能获取某个用户的全部邮件。如果没有使用聚簇索引，则每封邮件都可能导致一次磁盘I/O。
数据访问更快。聚簇索引将索引和数据保存在同一个B-Tree中，因此从聚簇索引中获取数据通常比在非聚簇索引中查找要快。
使用覆盖索引扫描的查询可以直接使用页节点中的主键值。

缺点：

聚簇数据最大限度地提高了I/O密集型应用的性能，但如果数据全部都存在内存中，则访问的顺序就没那么重要了，聚簇索引也就没什么优势了。
插入速度严重依赖于插入顺序。按照主键的顺序插入是加载数据到InnoDB表中速度最快的方式。但如果不是按照主键顺序加载数据，那么在加载完成后最好使用optimize table命令重新组织一下表。
更新聚簇索引列的代价很高，因为会强制InnoDB将每个被更新的行移动到新的位置。
基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面龙”页分裂”的问题。当行的主键值要求必须将这一行插入到某个已满的页中时，存储引擎会将该页分裂成两个页面来容纳改行，这就是一次页分裂操作。页分裂会导致表占用更多的磁盘空间。
聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候。
二级索引(非聚簇索引)可能比想象的要更大，因为在二级索引的叶子节点包含了引用行的主键列。
二级索引访问需要两次索引查找，而不是一次。(未使用到覆盖索引优化的情况下——回表查询)

由于通过二级索引查找行，存储引擎需要找到二级索引的叶子节点获得对应的主键值，然后根据这个值去聚簇索引中查找对应的行，这个操作我们通常也称为回表。这里需要注意的是，千万不要把这种事情看代成简单的非聚簇索引树查询一遍，聚簇索引树查询一遍，这么简单。首先显而易见的是，这必然会造成扫描大量的页，进而深入分析，当一级索引和二级索引的关联度极低(这里指二级索引上叶子节点存储的主键值有序度与一级索引上的主键索引值有序度出现了严重的不一致)，这种情况下，根据我们对B+索引树的理解，显然会造成一种现象：通过二级索引获取一个page，进而在获取所需行数据的时候，在一级索引树上产生了大量的”跳读”现象，这将严重影响查询的性能。

对于InnoDB而言，自适应哈希索引能够减少这样的重复工作，这里简单叙述一下自适应哈希索引的原理(因为这篇博客的重点是聚簇索引，要有主次之分嘻嘻)：

自适应哈希索引简要分析：

Innodb存储引擎会监控对表上二级索引的查找，如果发现某二级索引被频繁访问，二级索引成为热数据，建立哈希索引可以带来速度的提升。经常访问的二级索引数据会自动被生成到hash索引里面去(最近连续被访问三次的数据)，自适应哈希索引通过缓冲池的B+树构造而来，因此建立的速度很快。
总而言之就是，如果在一张表中经常采用二级索引查找，InnoDB存储引擎便会进行监控，对这类经常被查询的热点二级索引行数据，建立哈希索引，这样一来，当之后的再次使用二级索引查找的时候，便会先到自适应哈希索引上尝试去寻找，这里需要说明一下自适应哈希索引会占用innodb buffer pool，相对于去回表查询行数据这种”重复性工作”，自适应哈希索引便大大的优化了多次磁盘I/O操作带来的效率低问题。
但是万事均有利必有弊，比如自适应哈希索引只能基于等值比较，并且存在冲突的可能，需要进行维护，占用buffer pool等等，我们在考虑是否使用自适应哈希索引的时候，应该考虑到这些使用前后的收付比(收益与付出的比例)，然后在做决定。好了就简要了解到这里。

InnoDB和MyISAM的数据分布对比

通过上面的介绍，我们对聚簇索引有了大概的认识，但是还是比较抽象，这里我们从对比的角度更加全面的去理解聚簇索引(小声bb一句，高性能MySQL的作者是真滴秀，书编的真好)。

聚簇索引和非聚簇索引的数据分布有区别，以及对应的主键索引和二级索引的数据分布也有区别，通常会让人感到困扰和意外。接下来我们来看看InnoDB和MyISAM是如何存储下面这个表的：

CREATE TABLE layout_test (
col1 INT NOT NULL,
col2 INT NOT NULL,
PRIMARY KEY(col1),
KEY(col2)
) ENGINE = InnoDB / MyISAM;

假设该表的主键取值为1~10000，按照随机顺序插入并使用OPTIMIZE TABLE命令做了优化。换句话说，数据在磁盘上的存储方式已经最优，但行的顺序是随机的。列col2的值是从1~100之间随机赋值，所以有很多重复的值。

MyISAM的数据分布：

MyISAM按照数据插入的顺序存储在磁盘上，如下图所示：

在行的旁边显示了行号，从0开始递增。因为行是定长的，所以MyISAM可以从表的开头跳过所需的字节找到需要的行(MyISAM并不总是使用图中的”行号”，而是根据定长还是变长使用不同策略)。

这种分布式很容易创建索引。下面给出表的主键分布图：

下面再次给出非主键索引分布图：

通过MyISAM中主键索引和其他索引的分布图，我们可以知道它们在结构上没有什么不同。主键索引就是一个名为primary的唯一非空索引。

InnoDB的数据分布：

因为InnoDB支持聚簇索引，所以使用非常不同的方式存储同样的数据。InnoDB以下图所示的方式存储数据：

仔细看会发现，该图显示了整个表，而不是只有索引。因为在InnoDB中，聚簇索引”就是”表，所以不像MyISAM那样需要独立的行存储。聚簇索引的每一个叶子节点都包含了主键值、事务ID、用于事务和MVCC的回滚指针以及所有的剩余列(在这个例子中是col2)。如果主键是一个列前缀索引，InnoDB也会包含完整的主键列和剩下的其他列。

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

下面给出InnoDB中二级索引的分布图：