MYSQL性能调优: 对聚簇索引和非聚簇索引的认识
聚簇索引是对磁盘上实际数据重新组织以按指定的一个或多个列的值排序的算法。特点是存储数据的顺序和索引顺序一致。一般情况下主键会默认创建聚簇索引，且一张表只允许存在一个聚簇索引。
在《数据库原理》一书中是这么解释聚簇索引和非聚簇索引的区别的：聚簇索引的叶子节点就是数据节点，而非聚簇索引的叶子节点仍然是索引节点，只不过有指向对应数据块的指针。
因此，MYSQL中不同的数据存储引擎对聚簇索引的支持不同就很好解释了。下面，我们可以看一下MYSQL中MYISAM和INNODB两种引擎的索引结构。
如原始数据为：
MyISAM引擎的数据存储方式如图：
MYISAM是按列值与行号来组织索引的。它的叶子节点中保存的实际上是指向存放数据的物理块的指针。从MYISAM存储的物理文件我们能看出，MYISAM引擎的索引文件（.MYI）和数据文件(.MYD)是相互独立的。
而InnoDB按聚簇索引的形式存储数据，所以它的数据布局有着很大的不同。它存储数据的结构大致如下：
注：聚簇索引中的每个叶子节点包含主键值、事务ID、回滚指针(rollback pointer用于事务和MVCC）和余下的列(如col2)。
INNODB的二级索引与主键索引有很大的不同。InnoDB的二级索引的叶子包含主键值，而不是行指针(row pointers)，这减小了移动数据或者数据页面分裂时维护二级索引的开销，因为InnoDB不需要更新索引的行指针。其结构大致如下：
INNODB和MYISAM的主键索引与二级索引的对比：
InnoDB的的二级索引的叶子节点存放的是KEY字段加主键值。因此，通过二级索引查询首先查到是主键值，然后InnoDB再根据查到的主键值通过主键索引找到相应的数据块。而MyISAM的二级索引叶子节点存放的还是列值与行号的组合，叶子节点中保存的是数据的物理地址。所以可以看出MYISAM的主键索引和二级索引没有任何区别，主键索引仅仅只是一个叫做PRIMARY的唯一、非空的索引，且MYISAM引擎中可以不设主键。
MySQL 聚簇索引
聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。具体的细节依赖于其实现方式，但innoddb 的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行。
当表有聚簇索引时，它的数据实际上存放在索引的叶子页（leaf page）中。术语‘聚簇’表示数据行和相邻的键值进错的存储在一起。因为无法同时把数据行存放在两个不同的地方，所以在一个表中只能有一个聚簇索引 （不过，覆盖索引可以模拟多个聚簇索引的情况）。
因为存储引擎负责实现索引，因此不是所有的存储引擎都支持聚簇索引。
一些数据库服务器允许选择哪个索引作为聚簇索引，但直到本书协写作之前，还没有任何一个MySQL内奸的存储引擎支持这一点。InnoDb将通过主键聚集数据。
如果没有定义主键，InnoDB 会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB 会隐式定义一个主键来作为聚簇索引。InnoDB值聚集在同一个页面中的记录。。包含相邻键值的页面可能会相距很远。
聚簇索引可能对性能有帮助，但也可能导致严重的性能问题。所以需要咨询的考虑聚簇索引，尤其是将表的存储引擎从InnoDB 该成其他的引擎的时候（返回来也一样）。
聚簇索引的一些重要优点：
可以吧相关的数据保存在一起。例如，实现电子邮箱时，可以根据用户id来聚集数据这样只需要从磁盘读取少数的数据页就能获取某个用户的全部邮件。如果没有使用聚簇索引，则每封邮件都肯能导致一次io。
数据访问更快。聚簇索引将索引和数据保存在同一个B-Tree中，因此从聚簇索引中获取数据通常比非聚簇索引中快。
使用覆盖索引扫描的查询可以直接使用页节点中的主键值。
聚簇索引的缺点：
聚簇索引最大限度的提高了io密集型应用的性能，但如果数据全部存放在内存中，则访问的顺序就没那么重要了，聚簇索引也就没有什么优势了。
插入速度严重依赖插入顺序。按照主键的顺序插入是加载数据到innodb表中速度最快的方式。但如果不是按照主键顺序加载数据，那么加载完成后最好使用OPTIMIZE TABLE 命令来重新组织一下表。
更新聚簇索引的代价很高，因为会强制InooDB将每个更新的数据移动到新的位置。
基于聚簇索引的表在插入行，或者主键被更新导致需要移动行的时候，可能面临’页分裂（page split）‘的问题。当行的主键值要求必须将这一行插入到某个已满的页中时。存储引擎，存储引擎会将该页分裂成两个页面来容纳该行，这就是一次页分裂操作。页分裂会导致表占用更多的存储空间。
聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候。
二级索引（非聚簇索引）可能比想象的要更大，因为在二级索引的子节点包含了最优一个几点可能让人有些疑惑，为什么二级索引需要两次索引查找？答案在于二级索引中保存的“行指针”的实质。要记住，二级索引叶子节点保存的不是只想物理位置的指针，而是行的主键值。
这意味着通过二级索引进行查找行，存储引擎需要找到二级索引的子节点获得对应的主键值，然后根据这个值去聚簇索引总超找到对应的行。这里做了重复的工作：两次B-Tree查找，而不是一次。对于InnoDB，自适应哈希索引能够减少这样重复工作。
InnoDB 和 MyISAM的数据分布对比
聚簇索引和非聚簇索引的数据分布有区别，以及对应的主键索引和二级索引的数据分布也有区别，通常会让人感到困惑和意外。来看看InnoDB和MyISAM是如何存储下面的这个表的：
CREATE TABLE layout_test(
col1 int not null,
col2 int not null,
primary key (col1),
key(col2)
);
假设该表的主键取值为1-1w，按照随机顺序插入，并使用OPTIMIZE TABLE命令做了优化。换句话说，数据在磁盘的存储方式已经最优，但进行的顺序是随机的。列col2的值时从1-100之间随机赋值，所以有很多重复的值。
MyISAM 的数据分布.。 MyISAM的数据分布非常简单，所以先介绍它。MyIsam按照数据插入的顺序存储在磁盘上。
实际上，MyISAM 中主键索引和其他索引在结构上没有什么不同。主键索引就是一个名为PRIMARY的唯一非空索引。
InnoDB 的数据分布。因为InnoDB支持聚簇索引，索引使用非常不同的方式存储同样的数据。在InnoDB中，聚簇索引“就是”表，所以不像myISAM那样需要独立的行存储。聚簇索引的每一个叶子节点都包含了主键值、事务id，用于事务和MVCC的回滚指针。这样的策略减少了当前出现行移动或者数据页分裂是二级索引的维护工作。使用主键值当作指针会让二级索引占用更多的存储空间，存储，换来的好处是，InnoDB在移动行时，无需更新二级索引中的这个指针。InnoDB 的非叶子节点包含了索引列和一个纸箱下级节点的指针（下级节点可以是叶子节点，也可以是非叶子节点）。这对聚簇索引和二级索引都使用。
在InnoDB表中按照主键顺序插入行
如果正在使用InnoDB 表并且没有什么数据需要聚集，那么可以定义一个代理键（surrogate key）作为主键，这种主键的数据应该和应用无关，组件的的方法是使用AUTO_INCREMENT自增列。这样可以保证数据行是按照顺序写入，对于根据主键做关联的操作性能也会更好。
最好避免随机的（不连续，且值的分布范围非常大的）聚簇索引，特别是对于io密集型的应用。例如，从性能的角度考虑，使用UUID来作为聚簇索引则会很糟糕：它使得聚簇索引的插入变得完全随机，这是最坏的情况，使得数据没有任何聚集特性。
因为主键的值时顺序的，索引InnoDB 把每一条记录都存储在上一条记录的后面。当达到页的最大填充因子时（InnoDB 默认的最大填充因子是页大小的15/16 ,留出部分空间用于以后修改），下一条记录就会写入到新的页中。一旦数据按照这种顺序的方式加载，主键页就会近似于被顺序的记录填满，这也正是所期望的结果（然而二级索引页可能不一样）。
使用UUID聚簇索引的表插入数据，因为新的行的主键值不一定比之前插入的大，所以InnoDB 无法简单的总是把新行插入到索引的最后，而是需要为新的行寻找到合适的位置--通常是已有数据的中间位置--并且分配空间。这会增加很多的额外操作。并导致数据分布不够优化。下面是总结的一些缺点：
写入的目标页可能已经数到磁盘上并从缓存中移除，或者是还没有被加载到缓存中，InnoDB在插入之前不得不先找到并从磁盘读取目标页到内存中。这将导致大量的磁盘io。
因为写入是乱序的，InnoDB 不得不频繁的做分页操作，以便为新的行分配空间。页分裂会导致移动大量数据，一次插入最少需要修改三个页面，而不是一个页。
由于频繁的页分裂，页会变得稀疏，并且被不规则的填充，所以最终数据会有碎片。
总结：使用InnoDB 时应该尽可能地按照主键顺序插入数据，并且尽可能地使用单调增加的聚簇键的值来插入新行。
79
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
