数据库索引深入解析-优快云博客

本文详细探讨了数据库索引的三种常见模型：哈希表、有序数组和搜索树，重点介绍了B+树在InnoDB存储引擎中的应用。通过实例解析，讲解了主键索引与非主键索引的区别，以及索引维护、覆盖索引、最左匹配原则和索引下推等高级概念。

三种常见的索引模型

1、哈希表

2、有序数组

3、搜索树

哈希表

一种以键—值，存储的数据结构，哈希的思路很简单，处理步骤就是

把值放在数组里，用一个哈希函数把 key 换算成一个确定的位置，然后把 value 放在数组的这个位置。

缺点：

1、只适用于等值查询，区间查询效率低

有序数组

优点：

1、在等值查询和范围查询中很好

2、使用于静态索引引擎

缺点：

1、在需要更新数据的时候就麻烦了，你往中间插入一个记录就必须得挪动后面所有的记录，成本太高

搜索树

书中最经典的就是二叉搜索树。

二叉树是搜索效率最高的，但是实际上大多数的数据库存储却并不使用二叉树。其原因是，索引不止存在内存中，还要写到磁盘上。

可以想象，100万节点的平衡二叉树，树高20，一次查询可能要访问20个数据块，在机械硬盘时代，从磁盘随机读一个数据块需要 10 ms 左右的寻址时间。也就是说，对于一个 100 万行的表，如果使用二叉树来存储，单独访问一个行可能需要 20 个 10 ms 的时间，这个查询可真够慢的。

N叉树：

以 InnoDB 的一个整数字段索引为例，这个 N 差不多是 1200。这棵树高是 4 的时候，就可以存 1200 的 3 次方个值，这已经 17 亿了。考虑到树根的数据块总是在内存中的，一个 10 亿行的表上一个整数字段的索引，查找一个值最多只需要访问 3 次磁盘。其实，树的第二层也有很大概率在内存中，那么访问磁盘的平均次数就更少了。N 叉树由于在读写上的性能优点，以及适配磁盘的访问模式，已经被广泛应用在数据库引擎中了。

InnoDB 的索引模型

1、在InnoDB 中，数据都是根据主键顺序以索引的形式存放，这种存储方式的表称为索引组织表。又因为前面我们提到的，InnoDB 使用了 B+ 树索引模型，所以数据都是存储在 B+ 树中的。

2、每一个索引在 InNoDB 中都对应一棵 B+树。

索引类型分为：主键索引（聚簇索引）和非主键索引（二级索引）

那么主键索引和非主键索引有什么区别呢？

如果语句是 select * from T where ID=500，即主键查询方式，则只需要搜索 ID 这棵 B+ 树；

如果语句是 select * from T where k=5，即普通索引查询方式，则需要先搜索 k 索引树，得到 ID 的值为 500，再到 ID 索引树搜索一次。这个过程称为回表。

也就是说，基于非主键索引的查询需要多扫描一棵索引树。因此，我们在应用中应该尽量使用主键查询。

索引的维护

B+树为了维护索引有序性，在插入新值的时候，需要做必要的维护，其中可能就涉及，页分裂和页合并。

页分裂：

插入数据时发现当前数据页已经满了，根据 B+ 算法，需要申请一个新的数据页，并且移动部分数据。

页合并：

当相邻两个页由于删除了数据，利用率很低之后，会将数据页做合并。合并的过程，可以认为是分裂过程的逆过程

什么情况下应该使用自增主键，什么情况下有不应该使用？

主键长度越小，普通索引的叶子节点就越小，普通索引占据的空间就越小。

所以，从性能和空间的角度来看，自增主键往往是合适的。

订单

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

mysql> create table T (
    ID int primary key,
    k int NOT NULL DEFAULT 0, 
    s varchar(16) NOT NULL DEFAULT '',
    index k(k))
    engine=InnoDB;
    insert into T values(100,1, ‘aa'),(200,2,'bb'),(300,3,'cc'),(500,5,'ee'),(600,6,'ff'),          (700,7,'gg');

现在，我们一起来看看这条 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）。在这个例子中，由于查询结果所需要的数据只在主键索引上有，所以不得不回表。那么，有没有可能经过索引优化，避免回表过程呢？