Schema的优化和索引 - 高性能的索引策略 - 聚簇索引(Clustered Indexes)

聚簇索引并不是一个独立的索引类型。确切的说它们是存储数据的一个途径。在不同实现之间，还是有一些细节上的变化，但是InnoDB的聚簇索引实际是在相同的结构中把B-TREE索引和这些行一并的存储。

 

当一个表中有聚簇索引，它的行实际存储在索引的叶子的页（leaf pages）。“clustered”所指的意思是行的相邻键值彼此存储的非常近。你可能每张表只会有一个聚簇索引，因为你不能同时的在两个地方存储这些行。

 

因为存储引擎负责实现索引，因此并不是所有的存储引擎都支持聚簇索引。目前来说，solidDB和InnoDB支持。这部分我们重点放在InnoDB上，但是对于那些已经实现或者将要实现聚簇索引的引擎来说，在概念上至少有部分的正确。

 

下图展示了记录是怎样分布在聚簇索引上的。注意的是叶子的页（leaf pages）包含了整个行，但是节点的页仅仅包含了索引的列。这个例子中，索引列包含了整型值。

 

一些数据库服务器使你可以选择哪个索引可以聚簇，但是MySQL目前的存储引擎是不能那么做的。InnoDB通过主键来集中数据的。也就是说上图的索引列就是主键列。

 

如果你没有定义主键，InnoDB会选择一个唯一非空的索引来替代主键索引。如果没有这么个索引，InnoDB就会定义一个隐藏的主键。InnoDB仅仅在一个页中聚集数据。页所伴随的临近的键值可能彼此相互远离。

 

一个聚簇主键索引对性能有所帮助，但是也可能会导致严重的性能问题。因此，你应该仔细的思考聚簇，尤其是当你改变一个表的存储引擎从InnoDB转为其他的引擎。

 

聚簇数据有很多重要的优势。

 

• 能使相关联的数据距离很近。比如，当要实现一个mailbox，你可以通过user_id来聚簇，因此你能通过获取硬盘上一小部分的页来获得一个单独用户的所有消息。如果你不做聚簇，那么每个消息可能都需要各自的硬盘I/O。
• 数据访问更加快速。一个聚簇索引在B-TREE上即保存了索引也保存了数据。因此从聚集索引获取行一般要快于在非聚集索引中比较查找。
• 使用覆盖索引的语句可以使用包含在叶子节点的主键值。

如果你设计表和语句的时候好好利用它们，对性能的提高大有帮助。然而聚簇索引也有很多缺点

• 聚簇会大幅提高IO限制（IO-BOUND）工作量。如果数据在内存中的顺序对数据的访问并不是什么问题的话，聚簇就不能带来那么多好处了。
• 插入的顺序影响插入的速度。按照主键的速度插入行是最快的读入数据到InnoDB表的方法。如果你没有依照主键的顺序来读取数据，那么在读取很多数据之后，使用OPTIMIZE TABLE来重新组织表是个很好的主意。
• 更新聚簇索引的列消耗是非常高的。因为它迫使InnoDB把每一行移动到新的位置。
• 当新的一行插入或行的主键更新，这样会导致聚簇索引的页的分裂。当一个行的键值决定了该行以及它所有的数据一定要放在一页里的时候，页的分裂就发生了。因为存储引擎必须把页分为两个来容纳这个行。页的分裂会导致表占用更多的空间。
• 聚簇的表会降低检索整张表的速度。尤其是在由于页的分裂，造成行没有被压缩或者没有连续的存储的情况下，问题就很严重。
• 非聚簇索引可能比你所想像的要大很多。因为它们的叶子节点包含了它们引用行的主键列。
• 非聚簇索引的访问需要两个索引的查找。

最后的一条可能有些困惑，为什么非聚簇索引需要两次索引的查找？答案就隐藏在非聚簇索引所存储的“行指针”上。记住一个叶子节点存储的指针并不是引用行的物理地址，而是存储了行的主键值。

 

意思就是从非聚簇索引中查找一行，首先，存储引擎会在非聚簇索引找到叶子节点并且之后只用它所存储的主键值找到主键并且找到这个行。这是个双重工作：两个B-TREE的导航。

 

InnoDB和MyISAM数据布局的比较

聚簇和非聚簇数据的布局并且在它们之间不同的交互都让我们很困惑和吃惊。让我们看看InnoDB和MyISAM在下列表中是怎样布局的。

CREATE TABLE layout_test (
   col1 int NOT NULL,
   col2 int NOT NULL,
   PRIMARY KEY(col1),
   KEY(col2)
);

 

假使这张表的的主键数据为1到1000，并且随即的插入它们，之后再用optimize table来进行优化。也就是说数据最佳的分布在硬盘上，但是行可能是随机的顺序。col2的值在1到100随机的赋值。因此有许多重复。

MyISAM数据布局

MyISAM数据布局还是相对来说简单一些。所以我们先说说它。MyISAM在硬盘中是按照插入的顺序来存储的。图如下

我们发现行的数是从0开始的。因为行是定长的，因此MyISAM会通过从表的开始部分查找所需要的字节数来找到任意的行。

 

这种布局很容易创建索引。我们用抽象过的图来举例。每个叶子节点都包含了行的数字。来看看表主键的索引图

 

 

 

 

我们忽略了一些细节的问题，比如多少个B-TREE节点层级，但是这并不会影响我们理解在非聚簇索引引擎的基本数据布局。

 

那么col2是什么样子的？和上面的有什么区别？。其实没有任何区别。和其他索引也一样。

 

事实上，在MyISAM中，在主键和其他键的索引结构上并没有区别。一个主键就是唯一的，不能为NULL，并命名为PRIMARY的索引。

InnoDB 数据布局

InnoDB存储同样的数据，是和MyISAM不一样的，因为聚簇的结构。如图，InnoDB存储表。

 

乍看起来，并没有和上一张图有什么区别。但是再一看，这个示例显示的是整个表，而不仅仅是索引。因为聚簇索引在InnoDB中就是一张表。并不像MyISAM有独立的行存储。

在聚簇索引中的每个叶子节点都包含了主键，事物ID,用来MVCC和事务的回滚指针，还有就是剩下的列。如果主键在一个列的前缀上，InnoDB就包括这个列的值和剩下的所有的列。

相比较MyISAM,次要索引和聚簇索引也不是很相同。InnoDB次要索引的叶子节点包含了主键的值，就像指向行的指针一样。当移动行或者数据页的分裂，这个策略就会降低维护次要索引的工作量了。使用行的主键作为指针会使索引变大，但是也意味着InnoDB在移动行的时候不会更新指向这行的指针。

下面就是次要索引在col2的图

 

每个叶子节点都包含了已索引的列（这里是col2），紧跟着它的就是主键值（col1）。

这个图的事例是B-Tree叶子节点，但是我们忽略了不是叶子的节点。InnoDB不是B-TREE节点包含了已索引的列，加上指向更深层次的节点的指针（既可能是叶子节点也可能不是叶子节点）。以上说的适用于所有的索引，聚簇索引和次要索引。

下面的图是抽象的InnoDB和MyISAM数据的分布。更清晰的可以看到InnoDB和MyISAM存储数据和索引的不同。

 

如果你还不明白聚簇和非聚簇索引的的不同以及它们的重要性，请不要担心。在以后的讲解中会逐渐的清晰。在以后的章节中，例子会更加的复杂。需要用一些来更好的理解它们。

InnoDB中按照主键的顺序来插入行。

如果你使用的是InnoDB并且不需要特殊的聚簇。定义一个代理键（surrogate key）是个好的主意。意思就是这个主键并不是来自于你的应用程序的数据。最简单的方法就是使用AUTO_INCREMENT列。这能保证数据插入保持着连续的顺序并且对于使用主键连接会获得更好的性能。

最好避免使用随机的聚簇键。比如，从性能的角度来说，使用UUID是个不好的方法：它使聚簇索引的插入是随机的。这是最不好的场景了。并且对于数据的聚集也没有什么帮助。

为了演示这个，我们测试两个例子。第一个使用integerID插入一个userinfo。userinfo表定义如下

CREATE TABLE userinfo (

id int unsigned NOT NULL AUTO_INCREMENT,

name varchar(64) NOT NULL DEFAULT '',

email varchar(64) NOT NULL DEFAULT '',

password varchar(64) NOT NULL DEFAULT '',

dob date DEFAULT NULL,

address varchar(255) NOT NULL DEFAULT '',

city varchar(64) NOT NULL DEFAULT '',

state_id tinyint unsigned NOT NULL DEFAULT '0',

zip varchar(8) NOT NULL DEFAULT '',

country_id smallint unsigned NOT NULL DEFAULT '0',

gender ('M','F') NOT NULL DEFAULT 'M',

account_type varchar(32) NOT NULL DEFAULT '',

verified tinyint NOT NULL DEFAULT '0',

allow_mail tinyint unsigned NOT NULL DEFAULT '0',

parrent_account int unsigned NOT NULL DEFAULT '0',

closest_airport varchar(3) NOT NULL DEFAULT '',

PRIMARY KEY (id),

UNIQUE KEY email (email),

KEY country_id (country_id),

KEY state_id (state_id),

KEY state_id_2 (state_id,city,address)

) ENGINE=InnoDB

要注意主键是自增的。

第二个例子的表命名为userinfo_uuid。和userinfo唯一的区别就是主键是UUID的。

CREATE TABLE userinfo_uuid (

uuid varchar(36) NOT NULL,

。。。

我们对两个表做基准测试。首先我们对两个表插入100W数据。这个时候有足够的内存来存放索引。第二次我们插入300W数据，索引已经超出了内存的大小了。如下就是两个数据的比较

 

我们发现UUID作为主键的表不仅插入速度慢，而且索引还非常的大。一些原因是由于主键变大，而另一些原因不用怀疑的就是页的分裂和产生的一些碎片。

为了说明为什么会这样，让我们来看看当我们插入第一张表的时候，在索引中发生了些什么状况。

 

因为新行的主键并不是必须的比前一个主键值要大，因此InnoDB不能总是把新行放到这个索引之后。必须要为新行找到一个合适的位置-平均位置，也就是在靠近已存在数据的中部为新的数据生成一个空间。这就导致了一些额外的工作以及不理想的数据布局。下面就是一些缺陷：

1.目标页要刷新到硬盘并且要清除缓存。这个例子中，InnoDB在插入新行之前，要找到它和从硬盘中读取它。这就导致了很多的随机IO。

2.InnoDB有的时候会分裂页，这样做的目的是为新行创建空间。这需要移动很多的数据。

3.因为页的分裂，页会变得稀疏并且不规则的填充。因此最终数据是碎片的。

在读取许多随机值到聚簇索引中之后，你应该使用OPTIMIZE TABLE语句重新构建表并且优化的填充页。

这个例子告诉我们在使用InnoDB的时候要尽可能的顺序的插入数据，并且使用增长的聚簇索引。