背景
B树，对于从事数据库行业的技术人员来说，是必须要掌握的知识。我研究生时期的一个在国内足够权威的数据库老师说过，“我想不通为什么在《数据库原理》这门数据库必修课的教学大纲中，竟然没有将B树这章列入其中，这是多么荒唐的事情”，从这句话也可以看出，B树对于数据库而言，是多么重要的一个技术。
或者换一个概念，我们所熟悉的——索引，是我们日常工作必须要打交道的，因为我们所了解的大多数数据库，其索引都是通过B树或类B树实现的，今天的主题InnoDB，正是其中一员，所以从这个角度来看，索引和B树，就可以等同起来，不过更加精确的讲，InnoDB是使用B+树来实现其索引功能的。具体到B+树和B树的区别，如上所言，这是应该在大学期间学习到的，如果真的没有学过，也没太大关系，此时还有机会，继续往下看。
B+树及B树区别
首先，关于什么是B树，或者B+树，这些内容教科书或者网上有很多，可以直接获取到，但他们之间的区别，一般都是理论方面的，以数据库应用的角度去解释可能更加容易理解，通过总结，这两种数据结构的不同之处有如下五点：
1.B树中同一键值不会出现多次，它有可能出现在叶子节点，也有可能出现在内节点中；而B+树的键一定会出现在叶子节点，同时有可能在非叶子节点中也重复出现，简单来说，B+树的内节点存储的都是键值，键值对应的具体数据都存储在叶子节点中。
2.由于B树的每一个节点都存储了真实的数据，这会导致每一个节点存储的数据量变小，那么整个B树，层数就会相对变高，在数据量大了之后，维护代价是比较大的，而且层数越高，搜索或修改的性能就会越低；而B+树的内节点中，只存储键值，相对而言，一个内节点存储记录个数比B树多很多，随着B+树中数据量的增长，由于它是横向扩展的，最终会成长为一个矮胖子，不像B树一样是纵向扩展，最终只会变成一个瘦高个子，这样整体而言，B+树在搜索时，从上到下直到叶子节点只需要遍历层数个节点而已，性能就会比较高。
3.B树的查询效率与键在B树中的位置有关，最大时间复杂度与B+树相同(在叶子节点的时候)，最小时间复杂度为1(在根节点的时候)；而B+树的复杂度对某建成的树是固定的。
4.B树键位置不定，且在整个树结构中只出现一次，虽然可以节省存储空间，但这使得插入、删除等操作复杂度明显增加。而且性能不平衡，有可能会很快的找到了合适的位置，也有可能需要做比较多的IO操作才能找到。而B+树相对来说是一种较好的折中，因为内节点相对叶子节点而言，是一个索引的功能，在插入过程中，只需要通过在每一层搜索一个节点，依次找到叶子节点之后，在叶子节点做插入操作即可，只是在遇到一个节点存储满了的情况下会进行B+树分裂，但总体而言性能还是比较稳定。
5.B树中，所有数据都只存储一份；而B+树中，除了存储了所有数据的叶子节点外，还有只存储键值数据的内节点，所以占用空间量方面，B+树比B树会多占用一些空间，这部分空间就是B+树的内节点的所有空间，但B+树通过这种方式提高了整体性能，更适合于性能要求很高的文件检索。
索引的设计
数据库是用来存储数据的工具，存进去，是为了更方便的取出来，并且是越快越好，那么性能的要求就非常高了。在计算机上面运行一个任务，涉及到性能的一般有三个部分，分别是内存大小，CPU及磁盘的速度等，而索引是一个存储的方式，与它相关的最重要的部分就是磁盘了，所以磁盘的性能高低，直接影响了在数据库查找数据的效率。
磁盘的性能与读写顺序有关，针对我们普通的机械硬盘而言，顺序读写会比随机读写快很多，这里涉及到的机械硬盘存储原理有很多相关书籍介绍，就不再赘述了。那我们在设计如何加快数据库中数据的读写速度时，需要尽可能的避免随机读写，也就是说尽可能的读取连续的数据，这样性能自然就会好一些。
除了硬盘的读写顺序影响之外，还需要考虑读写操作本身的效率，因为在读写时，有些数据是必须要操作的，也就是我们真正需要的那部分数据，这被称为有效数据，这部分数据之外的被同时读写的数据，被称为无效数据。索引的设计，必须要尽可能的降低无效数据的读写访问。
考虑到我们关系型数据库的结构，有如下特点：
数据都是以行为单位一行行存放的，一行中包括一个表（聚簇索引）中或者一个索引（二级索引）中定义的所有列，多行数据可以连续一起存储。
一行数据中，一般都会有一个键，同时有其它附属的列，我们可以称之为值，那简单可以理解为一行数据就是一个键值对。可能有些人有一个疑问，如果不定义主键怎么办，没关系，这个InnoDB已经替你想到了，它会在内部加上一个主键，即我们所熟知的RowID值，这样也就有了一个默认的键，相应的表中所有用户所定义的列就是值了，也照样形成了一个键值对。
键值对中，键值是可以排序的，也可以是组合键值。
综合上面的特性，再综合B+树的特性，这哥俩感觉是天生的一对儿，所以设计存储方式如下：
磁盘空间，或者存储文件被划分为许多大小相同的块儿（Block）或者页(Page)，而在一个块儿中，可以存储多个数据行，这多个数据行在一个块内的存储格式可以先不考虑，但这样设计就是迎合了磁盘顺序读取性能比较高的特性，因为读取一条数据时，也很有可能读取其周边数据，这对于相对固定的块来说，一次顺序IO就可以读取很多数据出来，在性能提高上是非常适合的，而如果不通过块来管理行的话，行为单位的管理会非常碎，IO会非常随机，性能就变得差了。
在一个块儿内，所有数据行的组织管理也是需要讲究的，因为数据有可能经常会变，并且它的大小是相对固定的，有可能会存储满，所以内部是通过链表或数组的方式来管理的。
上面已经提到，每行数据就是一个键值对，并且键可以排序，在一个块内，所有的行数据也可以有序，这样利用经典的二分查找算法就可以很快的根据指定的键来找到对应的键值对数据或者一个范围的很多数据。
一个块的问题解决之后，如何形成一颗B+树呢？现在很容易想到，可以让一个块作为一个B+树的节点，这样通过块来承载数据，通过B+树这个数据结构来组织不同块之间的关系，最终形成一个矮矮胖胖的B+树结构。
因为行是一个键值对，而B+树的特点是通过在内节点中只存储键来提高搜索性能，这两个特性正好匹配。很自然的，在B+树中，内节点存储了行数据中的键，而叶子节点存储所有的行数据。通过内节点的键值及一个位置信息，内节点与下层节点或者叶子节点之间的指针，就可以找到其孩子节点了。
现在这个B+树（索引）雏形就出来了，再结合其它章节所讲的InnoDB文件管理方法，就可以串起来了，这里所说的块，其实就是一个页面，B+树所用到的块，就是被一个段，或者簇所管理的。
聚簇索引(Clustered Index)和辅助（二级）索引(Secondary Index)
我们在查询数据时，一般都会在经常被查询的字段上面建立一个索引，这正是利用了索引中被排序的键值，通过内节点的索引功能及叶子节点中数据的有序性利用二分查找极大的提高了查找的性能，所以索引在数据库中的作用是至关重要的。
在之前章节中介绍表空间文件管理时已经提到，一个表可以建立多个索引，但每一个表都有一个索引是存储了所有数据的，这个索引我们一般称之为“聚簇索引”，聚簇索引在一个表中只有一个且是建立在主键上面的，这个主键所包含的列可以是被隐藏的Rowid列，也可以是自增列，还可以是明确定义的不含NULL值的组合列等，除聚簇索引之外的所有索引，我们都称之为二级索引，那么很明显，二级索引可以有多个，并且一般没有上限，想建多少都可以，不过如果两个索引建立在同样的列，或者列组合上面，那这两个索引我们称之为重复索引或者冗余索引，这个在MySQL中一般都会以一个警告给出，通过show warnings我们可以看到警告信息如下：
mysql> show warnings\G
*************************** 1. row ***************************
Level: Note
Code: 1831
Message: Duplicate index 'its3' defined on the table 'local.ts'. This is deprecated and will be disallowed in a future release.
1 row in set (0.00 sec)
从上面的特性我们可以知道，一个表中，聚簇索引占用的空间肯定是最大的，因为它是存储了全部数据的，而二级索引，是建立在某几个需要经常查询的列上面的，除了这几个列之外，剩下的就是用来“回表”的指针信息了，所以相对而言，二级索引的占用空间都会比聚簇索引小很多，特别是在一个表的列数很多或是这些列中包含大字段的情况下，因为我们一般都不会在大字段上直接建立索引。那这样比较下来，在我们统计一个表总的精确行数时（查COUNT*），一些优化器就会选择表中最小的索引来作为统计的目标索引，因为它占用空间最小，IO也会最小，性能相应的更快一些。
上面说到了“回表”，所谓回表，就是在使用二级索引时，因为二级索引只存储了部分数据，如果根据键值查找到的数据不能包括全部目标数据时，就需要通过二级索引的指针，也就是键值对中的值，来找到聚簇索引的全部数据，然后根据完整的数据取出所需要的列的过程。这种在二级索引中不能找到所有需要的数据列的现象，被称为非覆盖索引，反之称为覆盖索引。因为回表本身是需要去另一个索引（聚簇索引）中查找数据的，性能必然会受到影响，那为了尽可能的提高性能就需要尽量的减少回表次数，所以可以试着将出现频率非常高的语句中所有使用到的列以合适的顺序建一个二级索引，这样所有需要的列都被这个二级索引覆盖了，就不需要回表了，从而一定程度上提高了性能。这虽然是一个好的做法，但需要去权衡，因为需要考虑语句中涉及到的列数，这个语句出现的频率及最终这个索引的大小。最坏的情况是建一个和聚簇索引差不多大的二级索引，这样一方面是占用空间比较大，另一方面是维护这个二级索引对这个表的整体修改性能也是有影响的，所以各方面都需要去权衡，然后再决定是不是要这样做。
上面还说到了，在统计总行数的时候，可以直接使用二级索引来做，是因为有一个很明显但很重要的前提：每个二级索引与聚簇索引的总行数是一样的，并且一对一。只不过在每一个索引中，数据行的排序顺序不同，可以想象二级索引行与聚簇索引行行之间都有虚线相连，并且二级索引中每一行都有且只有一条虚线指向聚簇索引中的一行数据，而聚簇索引的每一行，都会有相同个数的虚线指进来，这个数目就是二级索引的个数。至于二级索引与聚簇索引究竟是如何连起来的，我们后面会详细讲述。
关于二级索引的个数，虽然没有限制，但可以想象一下，任何事物都是有两面性的：建一个索引，是为了提高性能，但这是以损失写入性能为代价的。因为所有索引，在表需要写入数据时，都需要去维护索引数据以保证所有索引都是最新、最准确的，那可想而知，索引越多，写入性能也就越差，对索引上锁的时间也会越长。更严重的问题是，如果有唯一索引时，为了保证这个唯一特性，每次修改都会去检查唯一性，在RR隔离级别下，经常会造成死锁，所以在建索引时一定要仔细权衡，建出来的索引要个个为精，个个有用，这样才能保证在最大程度提高性能的情况下，最小程度的影响对表的修改。
二级索引的指针
现在已经知道，聚簇索引存储了所有数据，二级索引只存储了部分数据，但二级索引是为了提高性能的，所以经常会被使用到，那如果二级索引中的数据不能满足需求怎么办？这就用到了我们上面提到的“回表”，也就是二级索引中每行记录中指针的作用。
关于聚簇索引及二级索引列之间的逻辑关系，我们分类如下：
自定义主键的聚簇索引：
索引结构：[主键列][TRXID][ROLLPTR][其它建表创建的非主键列]
参与记录比较的列：主键列
内结点KEY列：[主键列]+PageNo指针
未定义主键的聚簇索引：
索引结构：[ROWID][TRXID][ROLLPTR][其它建表创建的非主键列]
参与记录比较的列：只ROWID一列而已
内结点KEY列：[ROWID]+PageNo指针
自定义主键的二级唯一索引：
索引结构：[唯一索引列][主键列]
参与记录比较的列：[唯一索引列][主键列]
内结点KEY列：[唯一索引列]+PageNo指针
自定义主键的二级非唯一索引：
索引结构：[非唯一索引列][主键列]
参与记录比较的列：[非唯一索引列][主键列]
内结点KEY列：[非唯一索引列][主键列]+PageNo指针
未定义主键的二级唯一索引：
索引结构：[唯一索引列][ROWID] 参与记录比较的列：[唯一索引列][ROWID]
内结点KEY列：[唯一索引列]+PageNo指针
未定义主键的二级非唯一索引：
索引结构：[非唯一索引列][ROWID] 参与记录比较的列：[非唯一索引列][ROWID]
内结点KEY列：[非唯一索引列][ROWID]+PageNo指针
通过这六种情况，讲清楚了聚簇索引记录包含的列，二级索引记录包括的列，以及在非叶子节点中分别包含的列，因为索引是用来检索数据的，所以还讲述了用来检查记录时，在二级索引及聚簇索引中，参与比较记录大小的列分别是什么，唯一索引与非唯一索引的区别等。
需要注意的一点是，上面讲述的索引列的顺序关系，与实际索引中记录的物理存储不是一回事，记录的存储格式是记录的格式，而这个是索引在内存中是元组的组织关系，这个元组的顺序体现的就是每个索引自己的逻辑顺序，以什么列建的索引，什么列就会在最前面起到优先排序的作用。
我们这里特别关注一下二级唯一索引的元组逻辑顺序，二级唯一索引中，作为索引本身的索引列，就是我们上面所说的“键”，当这个元组需要回表时，在元组中存储的聚簇索引列信息，就是我们所说的“值”，这样就形成了键值对。而对于二级非唯一索引而言，因为只有索引列本身再加上主键列才能保证索引记录是唯一的，所以这二者合起来才能构成我们所说的“键”，而“值”就为空了，也就是说，二级非唯一索引中，在记录构成方面，非叶结节点只是比叶子节点多了一个PageNo指针信息。
从上面可以看到，二级索引元组中，首先存储的就是每个索引定义的索引列，接着就是这条记录对应的聚簇索引的主键列的值，而主键列是唯一的，所以二级索引回表时对应的记录也是唯一的，这样就形成了一种指针的效果。
不过有一点需要注意一下，二级索引回表时对应的聚簇索引，如果是用户自定义的，有可能是自增列，也有可能是有逻辑意义的单列或者组合列的聚簇索引，如果用户没有自定义，则InnoDB会自动给聚簇索引分配一个主键列，不过是隐藏的列，即我们所熟知的Rowid列。基于此，如果是用户自定义的聚簇索引，则二级索引指针指向的就是聚簇索引所包含的列，如果没有自定义主键，那该指针就指向Rowid列了。
扫一扫
2144
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
