且构网

分享程序员开发的那些事...
且构网 - 分享程序员编程开发的那些事

数据库索引知识,你要了解的本文都有!

更新时间:2022-06-16 01:49:16

概述


数据库索引好比是一本书前面的目录,能加快数据库的查询速度。索引是对数据库表中一个或多个列(例如,User 表的 ‘姓名’ 列)的值进行排序的结构。如果想按特定用户的姓名来查找他或她,则与在表中搜索所有的行相比,索引有助于更快地获取信息。


建立索引主要有如下的优点:


  • 大大加快数据的检索速度;


  • 创建唯一性索引,保证数据库表中每一行数据的唯一性;


  • 加速表和表之间的连接;


  • 在使用分组和排序子句进行数据检索时,可以显著减少查询中分组和排序的时间。

当然有优点就有缺点,索引的缺点如下:



  • 索引需要占用数据表以外的物理存储空间


  • 创建索引和维护索引要花费一定的时间


  • 当对表进行更新操作时,索引需要被重建,这样降低了数据的维护速度。


常见的索引类型


索引的出现是为了提高查询效率,但是实现索引的方式却有很多种,所以这里也就引入了索引模型的概念。可以用于提高读写效率的数据结构很多,这里主要讲三种常见、也比较简单的数据结构:哈希表有序数组搜索树


哈希表


哈希表是一种以键 - 值(key-value)存储数据的结构,我们只要输入待查找的值即 key,就可以找到其对应的值即 Value。哈希的思路很简单,把值放在数组里,用一个哈希函数把 key 换算成一个确定的位置,然后把 value 放在数组的这个位置。


如果多个 key 值经过哈希函数的换算,可能会出现同一个值的情况,解决这个冲突的方法是拉出一个链表。这种处理方法与JAVA中的HashMap类似。


假设,你现在维护着一个身份证号码和姓名的表,需要根据身份证号查找对应的名字,这时对应的哈希索引的示意图如下所示:


数据库索引知识,你要了解的本文都有!


图中,User2 和 User4 根据身份证号算出来的值都是 N,那么这就变成了一个链表。假设,这时候你要查 ID_card_n2 对应的名字是什么,处理步骤就是:首先,将ID_card_n2 通过哈希函数算出 N;然后,按顺序遍历,找到 User2。


需要注意的是,图中四个 ID_card_n 的值并不是递增的,这样做的好处是增加新的 User时速度会很快,只需要往后追加。但缺点是,因为不是有序的,所以哈希索引做区间查询的速度是很慢的。


你可以设想下,如果你现在要找身份证号在 [ID_card_X, ID_card_Y] 这个区间的所有用户,就必须全部扫描一遍了。


所以,哈希表这种结构适用于只有等值查询的场景,包括=、IN()、<>,比如 Memcached 及其他一些NoSQL 引擎。


有序数组


而有序数组在等值查询和范围查询场景中的性能就都非常优秀。还是上面这个根据身份证号查名字的例子,如果我们使用有序数组来实现的话,示意图如下所示:


数据库索引知识,你要了解的本文都有!


这里的数组就是按照身份证号递增的顺序保存的。这时候如果你要查 ID_card_n2 对应的名字,用二分法就可以快速得到,这个时间复杂度是O(log(N))。


同时很显然,这个索引结构支持范围查询。你要查身份证号在 [ID_card_X, ID_card_Y] 区间的 User,可以先用二分法找到 ID_card_X(如果不存在 ID_card_X,就找到大于ID_card_X 的第一个 User),然后向右遍历,直到查到第一个大于 ID_card_Y 的身份证号,退出循环。


如果仅仅看查询效率,有序数组就是***的数据结构了。但是,在需要更新数据的时候就麻烦了,你往中间插入一个记录就必须得挪动后面所有的记录,成本太高。


所以,有序数组索引只适用于静态存储引擎,比如你要保存的是 2017 年某个城市的所有人口信息,这类不会再修改的数据。


搜索树


二叉搜索树也是课本里的经典数据结构了。还是上面根据身份证号查名字的例子,如果我们用二叉搜索树来实现的话,示意图如下所示:


数据库索引知识,你要了解的本文都有!


二叉搜索树的特点是:每个节点的左儿子小于父节点,父节点又小于右儿子。这样如果你要查 ID_card_n2 的话,按照图中的搜索顺序就是按照 UserA -> UserC -> UserF ->User2 这个路径得到。这个时间复杂度是 O(log(N))。


单从查询的角度来说二叉树是搜索效率最高的,但是实际上大多数的数据库存储却并不使用二叉树,而是选择使用多叉树,也就是B树、B+树这种每个节点可以有多个子节点的树。理由如下:


当数据量比较大的时候,索引是需要非常大的存储空间的,因此将索引保存在内存里显然不太现实,所以索引都是保存在磁盘空间里面,使用的时候再从磁盘读取到内存,这就是磁盘IO。


当使用索引查询数据时,计算机需要从一页一页的磁盘页中将索引读取到内存中,每页磁盘页对应着树结构的一个节点。每查询一个树节点相当于经过一次磁盘IO,读取了一页磁盘页,磁盘IO每次花费时间差不多8-10ms的样子,这个时间开销是特别巨大的,所以树越矮,查询过程中需要经过的树节点越少,需要时间也就越少。这就是我们不使用二叉树而使用多叉树的原因!


我们用一张图来看一下二叉树和多叉树之间的区别:


数据库索引知识,你要了解的本文都有!


很显然,节点可以挂载的叶子节点越多树就会越矮。


在InnoDB 引擎中叶子节点差不多是 1200个。一棵树高为 4 的B+树,可以存 1200 的 3 次方个值,这已经 17 亿了。考虑到树根的数据块总是在内存中的,一个 10 亿行的表上一个整数字段的索引,查找一个值最多只需要访问 3 次磁盘。其实,树的第二层也有很大概率在内存中,那么访问磁盘的平均次数就更少了。


InnoDB 的索引模型


在 MySQL 中,索引是在存储引擎层实现的,所以并没有统一的索引标准,即不同存储引擎的索引的工作方式并不一样。而即使多个存储引擎支持同一种类型的索引,其底层的实现也可能不同。由于 InnoDB 存储引擎在 MySQL 数据库中使用最为广泛,所以下面我就以 InnoDB 为例,和你分析一下其中的索引模型。


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


每一个索引在 InnoDB 里面对应一棵 B+ 树。


假设,我们有一个主键列为 ID 的表,表中有字段 k,并且在 k 上有索引。


这个表的建表语句是:


mysql> create table T(
id int primary key,
k int not null,
name varchar(16),
index (k))engine=InnoDB;


表中 R1~R5 的 (ID,k) 值分别为 (100,1)、(200,2)、(300,3)、(500,5) 和 (600,6),两棵树的示例示意图如下。


数据库索引知识,你要了解的本文都有!


从图中不难看出,根据叶子节点的内容,索引类型分为主键索引和非主键索引。

主键索引的叶子节点存的是整行数据。在 InnoDB 里,主键索引也被称为聚簇索引(clustered index)。


非主键索引的叶子节点内容是主键的值。在 InnoDB 里,非主键索引也被称为二级索引(secondary index)。


根据上面的索引结构说明,我们来讨论一个问题:基于主键索引和普通索引的查询有什么区别?


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


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


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


索引维护


B+ 树为了维护索引有序性,在插入新值的时候需要做必要的维护。以上面这个图为例,如果插入新的行 ID 值为 700,则只需要在 R5 的记录后面插入一个新记录。如果新插入的ID 值为 400,就相对麻烦了,需要逻辑上挪动后面的数据,空出位置。


而更糟的情况是,如果 R5 所在的数据页已经满了,根据 B+ 树的算法,这时候需要申请一个新的数据页,然后挪动部分数据过去。这个过程称为页分裂。在这种情况下,性能自然会受影响。


除了性能外,页分裂操作还影响数据页的利用率。原本放在一个页的数据,现在分到两个页中,整体空间利用率降低大约 50%。


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


基于上面的索引维护过程说明,我们来讨论一个案例:


你可能在一些建表规范里面见到过类似的描述,要求建表语句里一定要有自增主键。当然事无绝对,我们来分析一下哪些场景下应该使用自增主键,而哪些场景下不应该。


自增主键是指自增列上定义的主键,在建表语句中一般是这么定义的: NOT NULL PRIMARY KEY AUTO_INCREMENT


插入新记录的时候可以不指定 ID 的值,系统会获取当前 ID 最大值加 1 作为下一条记录的ID 值。


也就是说,自增主键的插入数据模式,正符合了我们前面提到的递增插入的场景。每次插入一条新记录,都是追加操作,都不涉及到挪动其他记录,也不会触发叶子节点的分裂。


而有业务逻辑的字段做主键,则往往不容易保证有序插入,这样写数据成本相对较高。


除了考虑性能外,我们还可以从存储空间的角度来看。假设你的表中确实有一个唯一字段,比如字符串类型的身份证号,那应该用身份证号做主键,还是用自增字段做主键呢?


由于每个非主键索引的叶子节点上都是主键的值。如果用身份证号做主键,那么每个二级索引的叶子节点占用约 20 个字节,而如果用整型做主键,则只要 4 个字节,如果是长整型(bigint)则是 8 个字节。


显然,主键长度越小,普通索引的叶子节点就越小,普通索引占用的空间也就越小。


所以,从性能和存储空间方面考量,自增主键往往是更合理的选择。


有没有什么场景适合用业务字段直接做主键的呢?还是有的。比如,有些业务的场景需求是这样的:


  1. 只有一个索引;
  2. 该索引必须是唯一索引。


你一定看出来了,这就是典型的 KV 场景。由于没有其他索引,所以也就不用考虑其他索引的叶子节点大小的问题。


这时候我们就要优先考虑上一段提到的“尽量使用主键查询”原则,直接将这个索引设置为主键,可以避免每次查询需要搜索两棵树。


索引原则


介绍索引原则前我们先来看一个例子:


在下面这个表 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;


  1. 再到 ID 索引树查到 ID=300 对应的 R3;


  1. 在 k 索引树取下一个值 k=5,取得 ID=500;


  1. 再回到 ID 索引树查到 ID=500 对应的 R4;


  1. 在 k 索引树取下一个值 k=6,不满足条件,循环结束。
    在这个过程中,回到主键索引树搜索的过程,我们称为回表。可以看到,这个查询过程读了 k 索引树的 3 条记录(步骤 1、3 和 5),回表了两次(步骤 2 和 4)。
    在这个例子中,由于查询结果所需要的数据只在主键索引上有,所以不得不回表。那么,有没有可能经过索引优化,避免回表过程呢?


覆盖索引


如果执行的语句是 select ID from T where k between 3 and 5,这时只需要查 ID 的值,而 ID 的值已经在 k 索引树上了,因此可以直接提供查询结果,不需要回表。也就是说,在这个查询里面,索引 k 已经“覆盖了”我们的查询需求,我们称为覆盖索引。


由于覆盖索引可以减少树的搜索次数,显著提升查询性能,所以使用覆盖索引是一个常用的性能优化手段。


需要注意的是,在引擎内部使用覆盖索引在索引 k 上其实读了三个记录,R3~R5(对应的索引 k 上的记录项),但是对于 MySQL 的 Server 层来说,它就是找引擎拿到了两条记录,因此 MySQL 认为扫描行数是 2。


基于上面覆盖索引的说明,我们来讨论一个问题:在一个市民信息表上,是否有必要将身份证号和名字建立联合索引?


假设市民信息表的建表语句如下:


CREATE TABLE `tuser` (
    `id` int(11) NOT NULL,
    `id_card` varchar(32) DEFAULT NULL,
    `name` varchar(32) DEFAULT NULL,
    `age` int(11) DEFAULT NULL,
    `ismale` tinyint(1) DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `id_card` (`id_card`),
    KEY `name_age` (`name`,`age`)
) ENGINE=InnoDB


我们知道,身份证号是市民的唯一标识。也就是说,如果有根据身份证号查询市民信息的需求,我们只要在身份证号字段上建立索引就够了。而再建立一个(身份证号、姓名)的联合索引,是不是浪费空间?


如果现在有一个高频请求,要根据市民的身份证号查询他的姓名,这个联合索引就有意义了。它可以在这个高频请求上用到覆盖索引,不再需要回表查整行记录,减少语句的执行时间。


当然,索引字段的维护总是有代价的。因此,在建立冗余索引来支持覆盖索引时就需要权衡考虑了。这正是业务 DBA,或者称为业务数据架构师的工作。


最左前缀原则


看到这里你一定有一个疑问,如果为每一种查询都设计一个索引,索引是不是太多了。如果我现在要按照市民的身份证号去查他的家庭地址呢?虽然这个查询需求在业务中出现的概率不高,但总不能让它走全表扫描吧?反过来说,单独为一个不频繁的请求创建一个(身份证号,地址)的索引又感觉有点浪费。应该怎么做呢?


这里,我先和你说结论吧。B+ 树这种索引结构,可以利用索引的“最左前缀”,来定位记录。


为了直观地说明这个概念,我们用(name,age)这个联合索引来分析。


数据库索引知识,你要了解的本文都有!


可以看到,索引项是按照索引定义里面出现的字段顺序排序的。


当你的逻辑需求是查到所有名字是“张三”的人时,可以快速定位到 ID4,然后向后遍历得到所有需要的结果。


如果你要查的是所有名字第一个字是“张”的人,你的 SQL 语句的条件是"where name like ‘张 %’"。这时,你也能够用上这个索引,查找到第一个符合条件的记录是 ID3,然后向后遍历,直到不满足条件为止。


可以看到,不只是索引的全部定义,只要满足最左前缀,就可以利用索引来加速检索。这个最左前缀可以是联合索引的最左 N 个字段,也可以是字符串索引的最左 M 个字符。

基于上面对最左前缀索引的说明,我们来讨论一个问题:在建立联合索引的时候,如何安排索引内的字段顺序。


这里我们的评估标准是,索引的复用能力。因为可以支持最左前缀,所以当已经有了 (a,b)这个联合索引后,一般就不需要单独在 a 上建立索引了。因此,第一原则是,如果通过调整顺序,可以少维护一个索引,那么这个顺序往往就是需要优先考虑采用的。


所以现在你知道了,这段开头的问题里,我们要为高频请求创建 (身份证号,姓名)这个联合索引,并用这个索引支持“根据身份证号查询地址”的需求。


那么,如果既有联合查询,又有基于 a、b 各自的查询呢?查询条件里面只有 b 的语句,是无法使用 (a,b) 这个联合索引的,这时候你不得不维护另外一个索引,也就是说你需要同时维护 (a,b)、(b) 这两个索引。


这时候,我们要考虑的原则就是空间了。比如上面这个市民表的情况,name 字段是比age 字段大的 ,那我就建议你创建一个(name,age) 的联合索引和一个 (age) 的单字段索引。


索引下推


上一段我们说到满足最左前缀原则的时候,最左前缀可以用于在索引中定位记录。这时,你可能要问,那些不符合最左前缀的部分,会怎么样呢?


我们还是以市民表的联合索引(name, age)为例。如果现在有一个需求:检索出表中“名字第一个字是张,而且年龄是 10 岁的所有男孩”。那么,SQL 语句是这么写的:

mysql> select * from tuser where name like '张 %' and age=10 and ismale=1;


你已经知道了前缀索引规则,所以这个语句在搜索索引树的时候,只能用 “张”,找到第一个满足条件的记录 ID3。当然,这还不错,总比全表扫描要好。

然后呢?


当然是判断其他条件是否满足。


在 MySQL 5.6 之前,只能从 ID3 开始一个个回表。到主键索引上找出数据行,再对比字段值。


而 MySQL 5.6 引入的索引下推优化(index condition pushdown), 可以在索引遍历过程中,对索引中包含的字段先做


判断,直接过滤掉不满足条件的记录,减少回表次数。


下面是这两个过程的执行流程图。


数据库索引知识,你要了解的本文都有!


数据库索引知识,你要了解的本文都有!


第一张图在 (name,age) 索引里面我特意去掉了 age 的值,这个过程 InnoDB 并不会去看age 的值,只是按顺序把“name 第一个字是’张’”的记录一条条取出来回表。因此,需要回表 4 次。


第二张图和第一张图的区别是,InnoDB 在 (name,age) 索引内部就判断了 age 是否等于 10,对于不等于 10 的记录,直接判断并跳过。在我们的这个例子中,只需要对 ID4、ID5 这两条记录回表取数据判断,就只需要回表 2 次。