数据库索引，索引底层的实现，B+树的结构以及与普通查找树的优点

一、什么是数据库索引？

用一句话来描述：数据库索引就是一种加快海量数据查询的关键技术。
比如字典的首页对于每个字的解释有页码标注。

二、什么是联合索引？

一些包含多个字段的数据库索引，比如INDEX idx_test(col_a, col_b)。这种包含多个字段的索引就被称为“联合索引”

三、最左前缀匹配原则

这就引申出了联合索引的一个规则：联合索引中的字段，只有某个字段（笔画）左边的所有字段（部首）都被使用了，才能使用该字段上的索引。例如，有索引INDEX idx_i1(col_a, col_b)，如果查询条件为where col_b = 1，则无法使用索引idx_i1。
联合索引的另一个规则：联合索引中的字段，即使某个字段（部首）右边的其他字段（笔画）没有被使用，该字段之前（含）的所有字段仍然可以正常使用索引。例如，有索引INDEX idx_i2(col_a, col_b, col_c)，则查询条件where col_a = 1 and col_b = 2在字段col_a和col_b上仍然可以走索引。

最左前缀匹配原则的含义：对于一个联合索引，如果有一个SQL查询语句需要执行，则只有从索引最左边的第一个字段开始到SQL语句查询条件中不包含的字段（不含）或范围条件字段（含）为止的部分才会使用索引进行加速。

范围条件字段也会结束对索引上后续字段的使用

上图是一个联合索引idx_eg(col_a, col_b)的结构，如果我们希望查询一条满足条件col_a = 64 and col_b = 128的记录，那么我们可以一路确定地往下找到唯一的下级节点最终找到实际数据。这种情况下，索引上的col_a和col_b两个字段都能被使用。

但是如果我们将查询条件改为范围查询col_a > 63 and col_b = 128，那么我们就会需要查找所有符合条件col_a > 63的下级节点指针，最后不得不遍历非常多的节点及其子节点。这样的话对于索引来说就得不偿失了，所以在这种情况下，数据库会选择直接遍历所有满足条件col_a > 63的记录，而不再使用索引上剩下的col_b字段。数据库会从第一条满足col_a > 63的记录开始，横向遍历之后的所有记录，从里面排除掉所有不满足col_b = 128的记录。
这就是范围条件会终止使用联合索引上的后续字段的原因。

四、什么是聚集索引？什么是非聚集索引？

像拼音目录这样的索引，数据会根据索引中的顺序进行排列和组织的，这样的索引就被称为聚集索引，而非聚集索引就是其他的一般索引。因为数据只能按照一种规则排序，所以一张表至多有一个聚集索引，但可以有多个非聚集索引。

在MySQL数据库的InnoDB存储引擎中，主键索引就是聚集索引，所有数据都会按照主键索引进行组织；而在MyISAM存储引擎中，就没有聚集索引了，因为MyISAM存储引擎中的数据不是按索引顺序进行存储的。

五、索引的组织形式

目前绝大多数情况下使用的数据库索引都是使用B+树实现的，下面就以MySQL的InnoDB为例，介绍一下数据库索引的具体实现。

5.1、聚集索引

下面是一个以B+树形式组织的拼音索引，在B+树中，每一个节点里都有N个按顺序排列的值，且每个值的中间和节点的头尾都有指向下一级节点的指针。在查找过程中，按顺序从头到尾遍历一个节点中的值，当发现要找的目标值恰好在一个指针的前一个值之前、后一个值之后时，就通过这个指针进入下一级节点。当最后到达叶子节点，也就是最下层的节点时，就能够找到自己希望查找的数据记录了

在上图中如果希望找到险字，那么我们首先通过拼音首字母在根节点上按顺序查找到了X和Y之间的指针，然后通过这个指针进入了第二级节点···, xia, xian, xiang, ···。之后在该节点上找到了xian和xiang之间的指针，这样就定位到了第519页开始的一个目标数据块，其中就包含了我们想要找到的险字。

因为拼音索引是聚集索引，所以我们在叶子节点上直接就找到了我们想找的数据。

5.2、非聚集索引

下面是一个模拟部首索引的组织形式。我们由根节点逐级往下查询，但是在最后的叶子节点上并没有找到我们想找的数据，那么在使用这个索引时我们是如何得到最终的结果的呢？回忆之前字典中“检字表”的内容，我们可以看到，在每个字边上都有一个页码，这就相当于下面这一个索引中叶子节点上险字与院字中间的指针，这个指针会告诉我们真正的数据在什么地方

下图中，我们把非聚集索引（部首索引）和聚集索引（拼音索引）合在一起就能看出非聚集索引最后到底如何查找到实际数据了。非聚集索引叶子节点上的指针会直接指向聚集索引的叶子节点，因为根据聚集索引的定义，所有数据都是按聚集索引组织存储的，所以所有实际数据都保存在聚集索引的叶子节点中。而从非聚集索引的叶子节点链接到聚集索引的叶子节点查询实际数据的过程就叫做——回表。

5.3、面试题：场景的应用

• 从性能和存储空间方面考量，自增主键往往是更合理的选择
• 从业务KV场景，可能业务指定主键更为合理
  • 只有一个索引
  • 该索引必须是唯一索引。

5.4、面试题：基于下图索引组织分析执行几次搜索树和几次回表

如果我执行 select * from T where k between 3 and 5，需要执行几
次树的搜索操作，会扫描多少行？

这条 SQL 查询语句的执行流程：

1. 在 k 索引树上找到 k=3 的记录，取得 ID = 300；
2. 再到 ID 索引树查到 ID=300 对应的 R3；
3. 在 k 索引树取下一个值 k=5，取得 ID=500；
4. 再回到 ID 索引树查到 ID=500 对应的 R4；
5. 在 k 索引树取下一个值 k=6，不满足条件，循环结束。

在这个过程中，回到主键索引树搜索的过程，我们称为回表。可以看到，这个查询过程读了
k 索引树的 3 条记录（步骤 1、3 和 5），回表了两次（步骤 2 和 4）。
在这个例子中，由于查询结果所需要的数据只在主键索引上有，所以不得不回表。那么，有没有可能经过索引优化，避免回表过程呢？参考全覆盖索引来解决这个问题。

5.5、面试题：字符串如何添加索引

假设根据某个人的邮箱进行用户登陆信息查询

select f1, f2 from SUser where email='xxx';

添加索引避免全表扫描,2种方式 ，一种是完全字段，一种是前缀字段

mysql> alter table SUser add index index1(email);
或
mysql> alter table SUser add index index2(email(6));

这两种不同的定义在数据结构和存储上有什么区别呢？


由于 email(6) 这个索引结构中每个邮箱字段都只取前 6 个字节（即：zhangs），所以占用的空间会更小，这就是使用前缀索引的优势。但，这同时带来的损失是，可能会增加额外的记录扫描次数
如果使用的是 index1（即 email 整个字符串的索引结构），执行顺序是这样的：

1. 从 index1 索引树找到满足索引值是’zhangssxyz@xxx.com’的这条记录，取得 ID2的值；
2. 到主键上查到主键值是 ID2 的行，判断 email 的值是正确的，将这行记录加入结果集；
3. 取 index1 索引树上刚刚查到的位置的下一条记录，发现已经不满足email='zhangssxyz@xxx.com’的条件了，循环结束。这个过程中，只需要回主键索引取一次数据，所以系统认为只扫描了一行。

如果使用的是 index2（即 email(6) 索引结构），执行顺序是这样的：

1. 从 index2 索引树找到满足索引值是’zhangs’的记录，找到的第一个是 ID1；
2. 到主键上查到主键值是 ID1 的行，判断出 email 的值不是’zhangssxyz@xxx.com’，这行记录丢弃；
3. 取 index2 上刚刚查到的位置的下一条记录，发现仍然是’zhangs’，取出 ID2，再到ID 索引上取整行然后判断，这次值对了，将这行记录加入结果集；
4. 重复上一步，直到在 idxe2 上取到的值不是’zhangs’时，循环结束。
   在这个过程中，要回主键索引取 4 次数据，也就是扫描了 4 行

但是，对于这个查询语句来说，如果你定义的 index2 不是 email(6) 而是 email(7），也就是说取 email 字段的前 7 个字节来构建索引的话，即满足前缀’zhangss’的记录只有一个，也能够直接查到 ID2，只扫描一行就结束了。
也就是说使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本

5.6、面试题：当要给字符串创建前缀索引时，有什么方法能够确定我应该使用多长的前缀呢？

我们在建立索引时关注的是区分度，区分度越高越好。因为区分度越高，意味着重复的键值越少。因此，我们可以通过统计索引上有多少个不同的值来判断要使用多长的前缀。

1）计算不同值，来看下区分度大小

1 mysql> select count(distinct email) as L from SUser;

2）依次选取不同长度的前缀来看这个值，比如我们要看一下 4~7 个字节的前缀索引，可以用这个语句：

mysql> select
 count(distinct left(email,4)）as L4,
 count(distinct left(email,5)）as L5,
 count(distinct left(email,6)）as L6,
 count(distinct left(email,7)）as L7,
from SUser;

使用前缀索引很可能会损失区分度，所以你需要预先设定一个可以接受的损失比例，比如 5%。然后，在返回的 L4~L7 中，找出不小于 L * 95% 的值，假设这里 L6、L7 都满足，你就可以选择前缀长度为 6。

5.7、如果区分度很高，如何来设置索引

使用倒序存储和使用 hash 字段这两种方法的异同点。

首先，它们的相同点是，都不支持范围查询。倒序存储的字段上创建的索引是按照倒序字符串的方式排序的，已经没有办法利用索引方式查出身份证号码在 [ID_X, ID_Y] 的所有市民了。同样地，hash 字段的方式也只能支持等值查询。

它们的区别，主要体现在以下三个方面：

1. 从占用的额外空间来看，倒序存储方式在主键索引上，不会消耗额外的存储空间，而hash 字段方法需要增加一个字段。当然，倒序存储方式使用 4 个字节的前缀长度应该是不够的，如果再长一点，这个消耗跟额外这个 hash 字段也差不多抵消了。
2. 在 CPU 消耗方面，倒序方式每次写和读的时候，都需要额外调用一次 reverse 函数，而hash 字段的方式需要额外调用一次 crc32() 函数。如果只从这两个函数的计算复杂度来看的话，reverse 函数额外消耗的 CPU 资源会更小些。
3. 从查询效率上看，使用 hash 字段方式的查询性能相对更稳定一些。因为 crc32 算出来的值虽然有冲突的概率，但是概率非常小，可以认为每次查询的平均扫描行数接近 1。而倒序存储方式毕竟还是用的前缀索引的方式，也就是说还是会增加扫描行数。

六、全覆盖索引（避免回表）

那么如果我们只是想要验证险字的偏旁是否是双耳旁“阝”呢？这种情况下，我们只要在部首索引中阝下游的叶子节点中找到了险字就足够了。这种在索引中就获取到了SQL语句中需要的所有字段，所以不需要再回表查询的情况中，这个索引就被称为这个SQL语句的全覆盖索引。

在实际的数据库中，非聚集索引的叶子节点上保存的“指针”就是聚集索引中所有字段的值，要获取一条实际数据，就需要通过这几个聚集索引字段的值重新在聚集索引上执行一遍查询操作。如果数据量不多，这个开销是非常小的；但如果非聚集索引的查询结果中包含了大量数据，那么就会导致回表的开销非常大，甚至超过不走索引的成本。所以全覆盖索引可以节约回表的开销这一点在一些回表开销很大的情况下就非常重要了。

七、为什么使用B+树？

大家在数学课上一定听说过一个例子，在一堆已经排好序的数字当中找出一个特定的数字的最好办法是一种叫“二分查找”的方式。具体的过程就是先找到这些数字中间的那一个数，然后比较目标数字是大于还是小于这个数；然后根据结果继续在前一半或者后一半数字中继续查找。

这就类似于数据结构中的二叉树，二叉树就是如下的一种结构，树中的每个节点至多可以有两个子节点，而B+树每个节点则可以有N个子节点。

平衡的二叉树是内存中查询效率最高的一种数据结构就可以了。

但是目前常用的数据库中，绝大多数的索引都是使用B+树实现的。那么为什么明明是二叉树查询效率最高，数据库中却偏偏要使用B+树而不是二叉树来实现索引呢？
计算机存储层次结构

计算机中的存储结构分为好几个部分，从上到下大致可以分为寄存器、高速缓存、主存储器、辅助存储器。其中主存储器，也就是我们常说的内存；辅助存储器也被称为外存，比较常见的就是磁盘、SSD，可以用来保存文件。在这个存储结构中，每一级存储的速度都比上一级慢很多，所以程序访问越上层存储中的数据，速度就会越快。

有过编程经验的小伙伴都知道，程序运行过程中操作的基本都是内存，对外存中数据的访问往往需要写一些文件的读取和写入代码才能实现。这正是因为CPU的计算速度比存储的I/O速度（输入/输出速度）快很多所做的优化，因为CPU在每次计算完成之后就需要等待下一批的数据进入，这个等待的时间越短，计算机运行得越快。

所以对于数据库索引来说，因为数据量很大，所以基本都是保存在外存中的，这样的话数据库读取一个索引节点的成本就非常大了。在数据量一样大的情况下，我们可以知道，B+树的单个节点中包含的值个数越多那么树中需要的节点总数就会越少，这样查询一次数据需要访问的节点数就更少了。
如果我们把二叉树看做是特殊的B+树（每个节点只有一个值和前后两个指针的B+树），那么就可以得出结论：因为B+树的节点中包含的值个数（多个值）比二叉树（1个值）更多，所以在B+树中查询所需要的节点数就更少。那么如果每次读取的成本是一样的话，因为总成本=读取次数*单次读取成本，我们就可以证明B+树的查询成本就比二叉树小得多了。

节点读取成本
读取更多数据肯定会需要更大的成本，那么为什么数据库索引使用B+树还是会比二叉树更好呢？这就需要一些更高深的操作系统知识来解释了。

在现代的操作系统中，把数据从外存读到内存所使用的单位一般被称为“页”，每次读取数据都需要读入整数个的“页”，而不能读入半页或者0.8页。一页的大小由操作系统决定，常见的页大小一般为4KB=4096字节。所以不管我们是要读取1字节还是2KB，最后都是需要读入一个完整的4KB大小的页的，那么一个节点的读取成本就取决于需要读入的页数。

在这样的情况下，如果一个节点的大小小于一页的大小，那么就会有一部分时间花在读取我们根本不需要的数据上（节点之外的数据），二叉树在这方面就会浪费很多时间；而如果一个节点的大小大于一页，哪怕是一页的整数倍，那我们也可能在一个节点的中间就找到了我们需要的指针进入了下一级的节点，这样这个指针后面的数据都白白读取了，如果不需要这些数据可能我们就可以少读几页了。

所以，综上所述，数据库索引使用节点大小恰好等于操作系统一页大小的B+树来实现是效率最高的选择。

八、实战数据库索引实现

8.1、整理查询条件

我们设计索引的目的主要是为了加快查询，所以，设计索引的第一步是整理需要用到的查询条件，也就是我们会在where子句、join连接条件中使用的字段。一般来说会整理程序中除了insert语句之外的所有SQL语句，按不同的表分别整理出每张表上的查询条件。也可以根据对业务的理解添加一些暂时还没有使用到的查询条件。

对索引的设计一般会逐表进行，所以按数据表收集查询条件可以方便后面步骤的执行。

8.2、 分析字段的可选择性

整理出所有查询条件之后，我们需要分析出每个字段的可选择性，那么什么是可选择性呢？

字段的可选择性指的就是字段的值的区分度，例如一张表中保存了用户的手机号、性别、姓名、年龄这几个字段，且一个手机号只能注册一个用户。在这种情况下，像手机号这种唯一的字段就是可选择性最高的一种情况；而年龄虽然有几十种可能，但是区分度就没有手机号那么大了；性别这样的字段则只有几种可能，所以可选择性最差。所以俺可选择性从高到低排列就是：手机号 > 年龄 > 性别。

但是不同字段的值分布是不同的，有一些值的数量是大致均匀的，例如性别为男和女的值数量可能就差别不大，但是像年龄超过100岁这样的记录就非常少了。所以对于年龄这个字段，20-30这样的值就是可选择性很小的，因为每一个年龄都有非常多的记录；但是像100这样的值，那它的可选择性就非常高了。

如果我们在表中添加了一个字段表示用户是否是管理员，那么在查询网站的管理员信息列表时，这个字段的可选择性就非常高。但是如果我们要查询的是非管理员信息列表时，这个字段的可选择性就非常低了。

从经验上来说，我们会把可选择性高的字段放到前面，可选择性低的字段放在后面，如果可选择性非常低，一般不会把这样的字段放到索引里。

8.3、合并查询条件

虽然索引可以加快查询的效率，但是索引越多就会导致插入和更新数据的成本变高，因为索引是分开存储的，所有数据的插入和更新操作都要对相关的索引进行修改。所以设计索引时还需要控制索引的数量，不能盲目地增加索引。

一般我们会根据最左匹配原则来合并查询条件，尽可能让不同的查询条件使用同一个索引。例如有两个查询条件where a = 1 and b = 1和where b = 1，那么我们就可以创建一个索引idx_eg(b, a)来同时服务两个查询条件。

同时，因为范围条件会终止使用索引中后续的字段，所以对于使用范围条件查询的字段我们也会尽可能放在索引的后面。

8.4、考虑是否需要使用全覆盖索引

最后，我们会考虑是否需要使用全覆盖索引，因为全覆盖索引没有回表的开销，效率会更高。所以一般我们会在回表成本特别高的情况下考虑是否使用全覆盖索引，例如根据索引字段筛选后的结果需要返回其他字段或者使用其他字段做进一步筛选的情况。

例如，我们有一张用户表，其中有年龄、姓名、手机号三个字段。我们需要查询在指定年龄的所有用户的姓名，已有索引idx_age_name(年龄, 姓名)，目前我们使用下面这样的查询语句进行查询：

SELECT *
FROM 用户表
WHERE 年龄 = ?;

一般情况下，将一个索引优化为全覆盖索引有两种方式：
增加索引中的字段，让索引字段覆盖SQL语句中使用的所有字段

在这个例子中，我们可以创建一个同时包含所有字段的索引idx_all(年龄, 姓名, 手机号)，以此提高查询的效率。
减少SQL语句中使用的字段，使SQL需要的字段都包含在现有索引中
在这个例子中，其实更好的方法是将SELECT子句修改为SELECT 姓名，因为我们的需求只是查询用户的姓名，并不需要手机号字段，去掉SELECT子句多余的字段不仅能够满足我们的需求，而且也不用对索引做修改。