MySQL高级之索引二

一、底层结构的选择过程

1. Hash结构

1.1 优点

• Hash结构的效率非常高，查询的时间复杂度仅为O(1)。

1.2 缺点

• Hash索引仅能满足(=)、(!=) 和IN查询。如果进行范围查询，哈希型的索引，时间复杂度会退化为O(N)；而树型的“有序”特性，依然能够保持O(log2N)的高效率。
• Hash索引还有一个缺陷，数据的存储是没有顺序的，在ORDER BY的情况下，使用Hash索引还需要对数据重新排序。
• 对于联合索引的情况，Hash值是将联合索引键合并后一起来计算的，无法对单独的一个键或者几个索引键进行查询。
• 对于等值查询来说，通常Hash索引的效率更高，不过也存在一种情况，就是索引列的重复值如果很多，效率就会降低。这是因为遇到Hash冲突时，需要遍历桶中的指针来进行比较，找到查询的关键字，非常耗时。所以，Hash索引通常不会用到重复值多的列上，比如列为性别、年龄的情况等。

1.3 引申：自适应Hash

• InnoDB本身不支持Hash索引，但是提供自适应Hash索引(Adaptive Hash Index)。如果某个数据经常被访问，当满足一定条件的时候， 就会将这个数据页的地址存放到Hash表中。这样下次查询的时候，就可以直接找到这个页面的所在位置。这样让B+树也具备了Hash索引的优点。采用自适应Hash索引目的是方便根据SQL的查询条件加速定位到叶子节点，特别是当B+树比较深的时候，通过自适应Hash索引可以明显提高数据的检索效率。

2. 二叉搜索树结构

2.1 优点

• 对存放的字段进行了排序，在范围查找时，可以降低时间复杂度。

2.2 缺点

• 二叉搜索树可能出现不平衡的极端场景，导致二叉搜索树变为线性结构。
• 如果我们利用二叉树作为索引结构，那么磁盘的IO次数和索引树的高度是相关的，树的深度每加深一层，查询数据就可能多进行一次IO操作，而二叉树容易出现是树的层次很深的情况，极端情况下甚至可能退化成链表，因此为了提高查询效率，就需要减少磁盘IO数，不可以采用二叉树。

3. AVL树（平衡二叉树）结构

3.1 优点

• AVL树的左右两个子树高度差的绝对值不超过1，而且左右两个子树都是平衡二叉树，解决上面二叉查找树退化成链表的问题

3.2 缺点

• 前面也说过，磁盘的IO次数和索引树的高度是相关的，AVL的深度还是太深了。

4. B树结构

4.1 优点

• B树作为多路平衡查找树，它的每一个节点最多可以包括 M个子节点，M 称为B树的阶。每个磁盘块中包括了关键字和子节点的指针。如果一个磁盘块中包括了x个关键字，那么指针数就是x+1。对于一个100阶的B树来说，如果有3层的话最多可以存储约100万的索引数据。对于大量的索引数据来说，采用B树的结构是非常适合的，因为树的高度要远小于AVL树的高度。
• B树相比于AVL树来说磁盘I/O 操作要少，在数据查询中比AVL树效率要高。所以只要树的高度足够低，IO次数足够少， 就可以提高查询性能。

4.2 特点

• B树在插入和删除节点的时候如果导致树不平衡，就通过自动调整节点的位置来保持树的自平衡。
• 关键字集合分布在整棵树中，即叶子节点和非叶子节点都存放数据。搜索有可能在非叶子节点结束。

5. 为什么选择B+树

5.1 B+树和B树的对比

• B+树有k个孩子的节点就有k个关键字。也就是孩子数量=关键字数，而B树中，孩子数量=关键字数 +1。
• B+树中，非叶子节点的关键字会同时存在在子节点中，而且是在叶子节点中的所有关键字的最小（或最大），而B树非叶子结点的关键字不会出现在子节点中。
• B+树非叶子节点仅用于索引，不保存数据记录，跟记录有关的信息都放在叶子节点中且B+树所有关键字都在叶子节点出现，叶子节点构成一个有序链表，而且叶子节点本身按照关键字的大小从小到大顺序链接。而B树中，非叶子节点既保存索引，也保存数据记录 。

5.2 B+树的优越性

• B+ 树查询效率更稳定。因为B+树每次只有访问到叶子节点才能找到对应的数据，而在B树中，非叶子节点也会存储数据，这样就会造成查询效率不稳定的情况，有时候访问到了非叶子节点就可以找到关键字，而有时需要访问到叶子节点才能找到关键字。
• B+树的查询效率更高。这是因为通常B+树此B树更矮胖（同样的磁盘页大小，B+树可以存储更多的节点关键字），查询所需要的磁盘I/O也会更少。
• 在范围查询上，B+树的效率也比B树高。这是因为所有关键字都出现在B+树的叶子节点中，叶子节点之间会有指针，数据又是递增的，这使得我们范围查找可以通过指针连接查找。而在B树中则需要通过中序遍历才能完成查询范围的查找，效率要低很多。

二、索引的常见面试问题

1. 为了减少IO，索引树会一次性加载吗？

• 数据库索引是存储在磁盘上的，如果数据量很大，必然导致索引的大小也会很大，超过几个G。当我们利用索引查询时候，是不可能将全部几个G的索引都加载进内存的，我们能做的只能是逐一加载每一个磁盘页，因为磁盘页对应着索引树的节点。

2. B+树的存储能力如何？为何说一般查找行记录，最多只需1~3次磁盘IO？

• InnoDB存储引擎中页的大小为16KB,，一般表的主键类型为INT (占用4个字节)或BIGINT (占用8个字节)，指针类型也一般为4或8个字节，也就是说一个页（B+Tree 中的一个节点）中大概存储16KB/(8B+8B)=1K个键值，(因为是估值，为方便计算，这里的K取值为1000，也就是说一个深度为3的B+Tree索引可以维护10亿条记录。(这里假定一个数据页也存储10^3条行记录数据了)
• 实际情况中每个节点可能不能填充满，因此在数据库中，B+Tree的高度一般都在2到4层。MySQL的InnoDB存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要1到3次磁盘I/O操作。

三、MyISAM引擎的索引

1. MyISAM索引的底层结构

MyISAM的索引方案也使用树形结构，但是它却将索引和数据分开存储

• 将表中的记录按照记录的插入顺序单独存储在一个文件中，称之为数据文件。这个文件并不划分为若干个数据页，有多少记录就往这个文件中塞多少记录就成了。由于在插入数据的时候并没有刻意按照主键大小排序，所以我们并不能在这些数据上使用二分法进行查找。
• 使用MyISAM存储引擎的表会把索引信息另外存储到一个称为索引文件的另一个文件中。 MyISAM 会单独为表的主键创建一个索引只不过在索引的叶子节点中存储的不是完整的用户记录，而是主键值+数据记录地址的组合。
• MyISAM中，没有聚簇索引和非聚簇索引的概念，所有的索引都是非聚簇索引。在MyISAM中，主键索引和二级索引在结构上没有任何区别，只是主键索引要求key是唯一的， 而二级索引的key可以重复。
  

2. MyISAM和InnoDB的索引对比

• 在InnoDB存储引擎中，我们只需要根据主键值对聚簇索引进行一次查找就能找到对应的记录，而在MyISAM中却需要进行一次回表操作，意味着MyISAM中建立的索引相当于全部都是二级索引 。
• InnoDB的数据文件本身就是索引文件，而MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。
• InnoDB的非聚簇索引data域存储相应记录主键的值 ，而MyISAM索引记录的是地址 。换句话说， InnoDB的所有非聚簇索引都引用主键作为data域。
• MyISAM的回表操作是十分快速的，因为是拿着地址偏移量直接到文件中取数据的，反观InnoDB是通获取主键之后再去聚簇索引里找记录，虽然说也不慢，但还是比不上直接用地址去访问。
• InnoDB要求表必须有主键（MyISAM可以没有）。如果没有显式指定，则MySQL系统会自动选择一个非空且唯一标识数据记录的列作为主键。如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整型。