MySQL-索引结构详解

推荐参考：http://blog.codinglabs.org/articles/theory-of-mysql-index.html

InnoDB的主键索引（Primary Key）是Cluster形式的（聚簇索引）。

InnoDB的非主键索引（Secondary Index）是普通的B+Tree索引。

两种索引在Root Node和Branch Node是一样的，在Leaf Node就不一样了。

Primary Key存放的是表的实际数据，不仅包含关键字段的数据，还包括其它字段的数据，整个数据以主键之有序排列。

Secondary Index存放索引键的相关信息，此外还有InnoDB的主键值（当移动行或数据页的分裂，这个策略降低了维护次索引的工作）。

所以InnoDB通过主键来访问数据效率是非常高的，而通过Secondary Index来访问的话要经过两步，从Secondary Index的Leaf Node获取主键值，再通过主键值检索一次。

MyISAM的主键索引和非主键索引的差别很小（区别是主键必须是唯一且非空的键）。

其存储结构和Secondary Index也基本相同，差别是Leaf Node存放索引键的相关信息外，存放能够直接定位到MyISAM数据文件中相应数据行的信息，而不是主键值。

MyISAM的数据布局相对简单，在硬盘上是按照插入的顺序来存储的。

下面从网上copy过来的示意图，一目了然^-^：

InnoDB中按照主键的顺序来插入行，如果不需要特殊的聚簇，定义一个代理键是个好注意（最简单的是AUTO_INCREMENT列），

这样保证数据插入保持着连续顺序并且对于使用主键连接会获得更好的性能。所以->

 

关于聚簇索引使用连续顺序的键是最好的场景：

最好避免使用随机的聚簇键。比如，从性能的角度来说，使用UUID是个不好的方法：它使聚簇索引的插入是随机的。这是最不好的场景了。并且对于数据的聚集也没有什么帮助；UUID作为主键的表不仅插入速度慢，而且索引还非常的大。一些原因是由于主键变大，而另一些原因不用怀疑的就是页的分裂和产生的一些碎片。

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

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

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

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

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

 

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

能使相关联的数据距离很近。比如，当要实现一个mailbox，你可以通过user_id来聚簇，因此你能通过获取硬盘上一小部分的页来获得一个单独用户的所有消息。如果你不做聚簇，那么每个消息可能都需要各自的硬盘I/O。 

数据访问更加快速。一个聚簇索引在B-TREE上即保存了索引也保存了数据。因此从聚集索引获取行一般要快于在非聚集索引中比较查找。 

使用覆盖索引的语句可以使用包含在叶子节点的主键值。 

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

聚簇会大幅提高IO限制（IO-BOUND）工作量。如果数据在内存中的顺序对数据的访问并不是什么问题的话，聚簇就不能带来那么多好处了。 

插入的顺序影响插入的速度。按照主键的速度插入行是最快的读入数据到InnoDB表的方法。如果你没有依照主键的顺序来读取数据，那么在读取很多数据之后，使用OPTIMIZE TABLE来重新组织表是个很好的主意。 

更新聚簇索引的列消耗是非常高的。因为它迫使InnoDB把每一行移动到新的位置。 

当新的一行插入或行的主键更新，这样会导致聚簇索引的页的分裂。当一个行的键值决定了该行以及它所有的数据一定要放在一页里的时候，页的分裂就发生了。因为存储引擎必须把页分为两个来容纳这个行。页的分裂会导致表占用更多的空间。 

聚簇的表会降低检索整张表的速度。尤其是在由于页的分裂，造成行没有被压缩或者没有连续的存储的情况下，问题就很严重。 

非聚簇索引可能比你所想像的要大很多。因为它们的叶子节点包含了它们引用行的主键列。 

非聚簇索引的访问需要两个索引的查找。 

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

 

 

前缀索引兼顾索引大小和查询速度，但是其缺点是不能用于ORDER BY和GROUP BY操作，也不能用于Covering index（即当索引本身包含查询所需全部数据时，不再访问数据文件本身）。

‍

 

 

 

 

转载于:https://blog.51cto.com/huanghualiang/1208230