当表的数据量比较大时,查询操作会比较耗时。建立索引是加快查询速度的有效手段。数据库索引类似于一本书中的目录,用于快速找出在某个列中有一特定值的行,不使用索引,MySQL必须从第一条记录开始读完整个表,直到找出相关的行,表越大,查询数据所花费的时间就越多,如果表中查询的列有一个索引,MySQL能够快速到达一个位置去搜索数据文件,而不必查看所有数据,这将会节省很大一部分时间。
一、索引的常见模型
索引的出现是为了提高查询效率,但是实现索引的方式却有很多种,所以我们首先要了解下索引模型的概念。可以用于提高读写效率的数据结构很多,这里先介绍三种常见、也比较简单的数据结构,它们分别是哈希表、有序数组和搜索树。接下来主要从使用的角度,来简单分析一下这三种模型的区别。
1.1 哈希表
哈希表是一种以键 - 值(key-value)存储数据的结构,我们只要输入待查找的键即 key,就可以找到其对应的值即 Value。哈希的思路很简单,把值放在数组里,用一个哈希函数把 key 换算成一个确定的位置,然后把 value 放在数组的这个位置。
不可避免地,多个 key 值经过哈希函数的换算,会出现同一个值的情况。处理这种情况的一种方法是,拉出一个链表。
假设,我们现在维护着一个身份证信息和姓名的表,需要根据身份证号查找对应的名字,这时对应的哈希索引的示意图如下所示:
图中,User2 和 User4 根据身份证号算出来的值都是 N,但没关系,后面还跟了一个链表。假设,这时候你要查 ID_card_n2 对应的名字是什么,处理步骤就是:首先,将 ID_card_n2 通过哈希函数算出 N;然后,按顺序遍历,找到 User2。
需要注意的是,图中四个 ID_card_n 的按照放置顺序的值并不是递增的,这样做的好处是增加新的 User 时速度会很快,只需要往后追加。但缺点是,因为不是有序的,所以哈希索引做区间查询的速度是很慢的。
可以设想下,如果我们现在要找身份证号在[ID_card_X, ID_card_Y]这个区间的所有用户,那就必须将整个哈希表全部扫描一遍。
所以,哈希表这种结构适用于只有等值查询的场景,比如 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))。
树可以有二叉,也可以有多叉。多叉树就是每个节点有多个儿子,儿子之间的大小保证从左到右递增。二叉树是搜索效率最高的,但是实际上大多数的数据库存储却并不使用二叉树。其原因是,索引不止存在内存中,还要写到磁盘上。想象一下一棵 100 万节点的平衡二叉树,这个树高为 20。一次查询可能需要访问 20 个数据块。在机械硬盘时代,从磁盘随机读一个数据块需要 10 ms 左右的寻址时间。也就是说,对于一个 100 万行的表,如果使用二叉树来存储,单独访问一个行可能需要 20 个 10 ms 的时间,这个速度显然太慢。
为了让一个查询尽量少地读磁盘,我们就必须考虑让查询过程访问尽量少的数据块。那么,我们就不应该使用二叉树,而是要使用“N 叉”树。这里,“N 叉”树中的“N”取决于数据块的大小。
以 InnoDB 的一个整数字段索引为例,这个 N 差不多是 1200。这棵树高是 4 的时候,就可以存 1200 的 3 次方个值,这已经 17 亿了。考虑到树根的数据块总是在内存中的,一个 10 亿行的表上一个整数字段的索引,查找一个值最多只需要访问 3 次磁盘。其实,树的第二层也有很大概率在内存中,那么访问磁盘的平均次数就更少了。
N 叉树由于在读写上的性能优点,以及适配磁盘的访问模式,已经被广泛应用在数据库引擎中了,具体的实现就是B+树。
但不管是哈希还是有序数组,或者 N 叉树,它们都是不断迭代、不断优化的产物或者解决方案。数据库技术发展到今天,跳表、LSM 树等数据结构也被用于引擎设计中,这里就不再一一展开了。
在 MySQL 中,索引是在存储引擎层实现的,所以并没有统一的索引标准,即不同存储引擎的索引的工作方式并不一样。而即使多个存储引擎支持同一种类型的索引,其底层的实现也可能不同。由于 InnoDB 存储引擎在 MySQL 数据库中使用最为广泛,所以在下面我们着重分析InnoDB的索引模型。
二、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),根据主键索引ID和k值上的索引所建立的两棵B+树的示意图如下:
从图中可以看出,我们可以根据叶子节点的内容,将索引分为主键索引和非主键索引。
主键索引的叶子节点存的是整行数据。在 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%。
当然有分裂就有合并。当相邻两个页由于删除了数据,利用率很低之后,会将数据页做合并。合并的过程,可以认为是分裂过程的逆过程。
四、回表及覆盖索引
看下这个问题:
在下面这个表 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查询语句的执行流程:
- 在 k 索引树上找到 k=3 的记录,取得 ID = 300;
- 再到 ID 索引树查到 ID=300 对应的 R3;
- 在 k 索引树取下一个值 k=5,取得 ID=500;
- 再回到 ID 索引树查到 ID=500 对应的 R4;
- 在 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,或者称为业务数据架构师的工作。
五、最左前缀原则
如果我们为每一种查询都设计一个索引,那这样索引就太多了。假设我们现在要按照市民的身份证号去查他的家庭地址。虽然这个查询需求在业务中出现的概率不高,我们要么让它走全表扫描吧,要么创建一个(身份证号,地址)的索引又感觉有点浪费。这两种解决方案都有些不足,但幸运的是,我们可以利用最左匹配来解决这个问题。
5.1 什么是最左匹配
所谓最左匹配指的是如果我们的SQL语句中用到了联合索引中的最左边的索引(这个最左可以是联合索引的最左N个字段,也可以是字符串索引的最左M个字串),那么这条SQL语句就可以利用这个联合索引去进行匹配,值得注意的是,当遇到范围查询(>、<、between、like)就会停止匹配。
假设我们对(a,b,c,d)字段建立一个索引,那么所有满足“最左”的where子句后面的字段条件有:
- a=x
- a=x,b=y
- a=x,b=y,c=z
- a=x,b=y,c=z,d=s
需要注意的是,如果我们执行:
b= 2 and a =1
也是能匹配到索引的,因为MySQL有优化器会自动调整a,b的顺序与索引顺序一致。但是我们执行b=2
是匹配不到索引的,因为违反了“最左”原则。
假设where后条件为:
a = 1 and b = 2 and c > 3 and d = 4
那么,a,b,c三个字段能用到索引,而d就匹配不到。因为遇到了范围查询。
5.2 最左匹配的原理
假设我们对(姓名,年龄)字段建立索引,如下图所示:
如图所示这些字段是按照姓名来进行排序,在姓名相等的情况下,才按照年龄来进行排序。
因此我们看到姓名是有序的,“李四、王五、张刘、张三、张三、张三“。而年龄是一种全局无序,局部有序状态。从全局来看,年龄的值为”20、10、30、10、10、20“,是无序的,因此直接执行b=10这种查询条件没有办法利用到索引。
但是从局部来看,当姓名的值确定的时候,年龄是有序的。例如当姓名是”张三“的时候,年龄是”10、10、20“是有序的状态。因此,当我们执行
姓名=xx and 年龄=y
的时候能用到这个联合索引,当我们执行
姓名=张X and 年龄=y
的时候,姓名可以用到索引,而年龄用不到索引,因为姓名是一个范围,不是固定的,在这个范围内年龄不是有序的,因此年龄字段无法用到索引,所以最左匹配原则在遇到范围查询的时候就会停止匹配。
5.3 联合索引的使用
基于上面对最左前缀索引的说明,我们来讨论一个问题:在建立联合索引的时候,如何安排索引内的字段顺序。
这里我们的评估标准是,索引的复用能力。因为可以支持最左前缀,所以当已经有了 (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), 可以在索引遍历过程中,对索引中包含的字段先做判断,直接过滤掉不满足条件的记录,减少回表次数。图 3 和图 4,是这两个过程的执行流程图。
无索引下推执行流程:
索引下推执行流程:
在图 3 和 4 这两个图里面,每一个虚线箭头表示回表一次。图 3 中,在 (name,age) 索引里面我特意去掉了 age 的值,这个过程 InnoDB 并不会去看 age 的值,只是按顺序把“name 第一个字是’张’”的记录一条条取出来回表。因此,需要回表 4 次。图 4 跟图 3 的区别是,InnoDB 在 (name,age) 索引内部就判断了 age 是否等于 10,对于不等于 10 的记录,直接判断并跳过。在我们的这个例子中,只需要对 ID4、ID5 这两条记录回表取数据判断,就只需要回表 2 次。