|
军事领域自古以来就非常讲究速度与效率，所谓兵贵神速。当年楚汉相争，西楚霸王项羽与汉高祖刘邦的彭城之战，项羽凭借3万精锐骑兵大破刘邦率领的56万大军，这里面体现的就是骑兵的神速与高效，当然也少不了项羽对骑兵如神一般的指挥。 用将军事领域重视速度与效率的理念来看MySQL的话，索引可以说是MySQL里面神速的骑兵。索引可以大大提升MySQL的检索速度，对于MySQL的高效运行起到非常重要的作用。 MySQL B+树索引相关的概念复杂繁多容易忘记，本文尝试通过结合思维导图来讲解B+树索引，以便容易理解、记忆以及方便日后随时查看。完整版的思维导图下载方式在文末。 1，MySQL索引的总类 MySQL索引总体可以分为B+树索引、哈希索引、全文索引三大类。本文将会着重介绍B+树索引。 2，B+树索引 B+树是为直接存取辅助设备（如磁盘）设计的一种平衡查找树，MySQL里面的B+树索引其实就是B+树索引是B+树在数据库中的实现。B+树索引具有高扇出性的特点，并且由于树的高度通常在2~4层之间，所以通过B+树索引查找某一键值对应的行记录是一般只需2~4次IO操作，耗时比较低。这里有一点需要注意的是，B+树索引并不能通过给定的键值直接找到具体的数据行，而是找到数据行所在的页，然后数据库将该页读进内存并在内存中查找数据。 B+树索引可以分为聚集索引（clustered index）和辅助索引（secondary index），其中辅助索引也称为非聚集索引。 聚集索引 聚集索引就是将表的主键用来构造一棵B+树，并且将整张表的行记录数据存放在该B+树的叶子节点中。由于聚集索引是利用表的主键构建的，所以每张表只能拥有一个聚集索引。 聚集索引的叶子节点就是数据页。换句话说，数据页上存放的是完整的每行记录。因此聚集索引的一个优点就是：通过过聚集索引能获取完整的整行数据。另一个优点是：对于主键的排序查找和范围查找速度非常快。也许会有人认为聚集索引的存储在是物理上连续的，但实际上这是不对的，因为这样做的话，索引的维护成本将会非常高。索引聚集索引的连续只是逻辑上的。 辅助索引 辅助索引的叶子节点是不会包含行记录的全部数据，所以在利用辅助索引查找记录的时候，会先找到叶子节点中的主键值，然后再利用主键值到聚集索引中找到行记录的全部数据。辅助索引的存在不会对聚集索引中数据的组织形式，索引一张表上是可以创建多个辅助索引的。 辅助索引的叶子节点中还会包含一个书签，书签中保存的就是对应记录的聚集索引的键，书签的存在使得在使用辅助索引的过程中数据库引擎能知道在哪里可以找到对应的行记录。 如果是使用辅助索引查找行记录的话，是需要比直接使用聚集索引多几次IO操作的。例如一个辅助索引对应的树的高度为4，那么查找数据的时候就需要对这棵辅助索引的树遍历4次才能找到对应的主键，然后再到聚集索引里面查找才能最终找到完整的行数据所在的页。 3，索引的分裂 B+树在插入数据时如果空间已满的情况的话，则要进行分裂操作。所以B+树索引也存在类似的分裂操作，不过B+树索引的分裂与B+树的分裂略有不同，B+树索引的分裂不一定是从页中间的数据开始分裂。 看一个例子。例如我们现在有1,2,3,4,5这几个数据，如果是从中间的数据开始分裂的话，就会产生： 1,2（G1） 3,4,5（G2） 这样两组数据。如果数据的插入是按顺序的，那么G1这个页是不会再有数据插入，这样就会造成空间的浪费。 当B+树要进行分裂的时候，InnoDB引擎会根据Page Header中的PAGE_LAST_INSERT、PAGE_DIRECTION和PAGE_N_DIRECTION这三个信息来决定如何进行分裂操作。 4，索引中的Cardinality值 Cardinality是指索引中不重复记录的预估值，注意Cardinality是一个预估值，并不是一个准确值。Cardinality可以通过show index from table来查看。如下图，我们可以看到每个索引的Cardinality是多少。 为什么Cardinality只是一个预估值而不是准确值？因为在真实的生产环境中，索引的更新是非常频繁的，如果每次索引被更新时都对Cardinality进行统计的话，将会给数据库带来不少的负担。索引Cardinality的统计是通过“采样”的方式来进行的。 通常Cardinality的自动重新统计会发生在insert和update这两个操作中，但这里需要满足一定的条件： 1，表中有1/6的数据已经更新 2，stat_modified_counter>2,000,000,000 当然，我们也可以手工来触发Cardinality的更新，运行analyze table table、show table status、show index from table 命令即可。 Cardinality值的意义在于我们可以用它来评估索引的价值。我们可以通过计算“Cardinality/表的记录数量”的值来评估，值越近1越好。Cardinality与表中记录的数量越接近这表示索引的重复数量少，反之则表示索引的重复数量很大，那么索引的价值就会降低了。 5，在不同应用中使用B+数索引 绝大部分大部分应用都分为“OLTP(On-Line Transaction Processing)应用”和“OLAP(On-Line Analytical Processing)应用”。 OLTP是指那些联网事物处理应用，例如企业中的业务系统。这种应用的特点是一次查询操作一般都只是获取一部分数据，所以索引的建立可以只针对特定的字段，在相对微观的层面进行。 OLAP是指那些联机分析处理应用，例如报表系统。这些应用是需要大量获取数据库中的数据，并根据这些数据来计算出符合要求的查询结果。对于这类型的应用，添加索引时就需要考虑比较宏观角度，例如考虑在时间维度的字段上添加索引。 6，覆盖索引 覆盖索引表示通过辅助索引中就可以直接得到要查询的数据，而无需再去查询聚集索引。 例如table_a中的key字段是有辅助索引并且其数量比聚集索引少的话，那么select count(*) from table_a这个语句就会直接通过辅助索引来完成。覆盖索引可以很好地减少IO操作。 写在最后 限于篇幅，B+树索引中许多特点并没有在本文中一一介绍，例如“FIC（Fast Index Creation）创建索引方式”、“优化器选择不使用索引的情况”、“索引提示（index hints）”等比较有趣的概念。不过在完整版的思维导图中都有介绍，感兴趣的朋友可以通过以下方式下载完整版的“MySQL B+树索引”思维导图。 |
|
|
