MySQL浅析之索引
1. 前言
如果我们用到数据库,那么不可避免的就会提到索引。我们都知道,它类似于一本书的目录,能够加快我们的访问,使查找变得迅速。
但是索引到底是什么,它又是如何工作的呢,我们今天就需要来了解了解。
2. 常见的几种索引模型
说到索引,说白了就是提高查询速率,那么是如何提高的呢?
思考下,我们应该能够明白一个概念,它的底层肯定是通过某些算法和数据结构来实现它的功能的,
这就是我要提到的索引模型。因为,使用不同的算法和数据结构所得到的索引就不同。
那么常见的索引模型有哪些呢,其实也就这三种:哈希表、有序数组和搜索树。
我们简单的来看看这三种模型的区别吧
哈希表
提到这,相信大家都是熟悉的。哈希表是一种以键-值(key-value)存储数据的结构,我们只要输入待查找的值即key,就可以找到其对应的值即Value。哈希的思路很简单,把值放在数组里,用一个哈希函数把key换算成一个确定的位置,然后把value放在数组的这个位置。不可避免地,多个key值经过哈希函数的换算,会出现同一个值的情况。处理这种情况的一种方法是,拉出一个链表。
说白了,哈希表就是由数组和链表组成的。
看到这里,我们应该记得一个集合也是用数组和链表实现的。想一想,没错,就是HashMap(1.7)。
举个例子:
设id为索引,那么就会根据id进行hash函数运算,得到一个数组的值。假设值为1吧。
那么由图不难发现当id = 2、id = 7、id = 12时,他们的hash函数值是一样的。因此根据顺序,先将id = 1放入数组1的位置,之后牵一个链表出来,一次存放id = 7、id = 12。
那么用这种模型有什么好处呢?
我们不难发现,当新增数据时,只需要将数据进行hash运算,左后放入数组或链表即可,不用去移动或整理哈希表。因此,它的优点是:新增数据块。
缺点也不难发现:因为不是有序的,当我们要查询某个区间的数据时,将会变得很慢,我们很可能要全部扫描一遍才能得出结果。
所以,哈希表这种结构适用于只有等值查询的场景,比如Memcached及其他一些NoSQL引擎。
有序数组
怎么解决区间查找快呢?很简单,使用有序数组就可以了。
很明显,这个时候,如果你要查找某一区间的数据就简单多了。
比如要查找id = 2 到id = 5之间的数据:
我们只需要使用二分查找法,找到id = 2,之后向右遍历找到id = 5即可。
那么它的缺点是什么呢?
如果仅仅看查询效率,有序数组就是最好的数据结构了。但是,在需要更新数据的时候就麻烦了,你往中间插入一个记录就必须得挪动后面所有的记录,成本太高。
所以,有序数组索引只适用于静态存储引擎。
二叉搜索树
这个不用多说,直接上图理解:
二叉搜索树的特点是:每个节点的左儿子小于父节点,父节点又小于右儿子。
假设我们需要查找id = 6 ,只需要按照 id = 5 --> id = 9 --> id = 7 --> id = 6即可。这个时间复杂度是O(log(N))。
当然为了维持O(log(N))的查询复杂度,你就需要保持这棵树是平衡二叉树。为了做这个保证,更新的时间复杂度也是O(log(N))。
看上去二叉树非常完美,但是实际上大多数的数据库存储却并不使用二叉树。
其原因是,索引不止存在内存中,还要写到磁盘上。
假设一棵树的高度为50层,那么我要查找底层的某一数据,则就要访问50个数据块。在机械硬盘时代,从磁盘随机读一个数据块需要10 ms左右的寻址时间。也就是说,我们查询这一行数据需要花费50 * 10ms的时间,这就非常慢了。
那么我们该如何解决呢?
显然二叉树是不行的,想一想,我们的目的是什么减少树的高度。
如何减少?没错我么只需要将子结点扩展即可。即由左右两个结点扩展到3、4、5多个结点不就减少了树的高度,即n叉树。
3. MySQL中InnoDB的索引模型
InnoDB是使用B+树来存储索引数据的。
因为我们讲的是索引,因此也不具体展开将B树和B+树。
但是我们可以简单看两张图来了解下:
-
B+树非叶子节点不存放数据,只存放keys。
-
B+树的叶子节点之间存在指针相连,而且是单链表
我们大概记住这两点即可。
我们可以看看B+树作为索引模型的好处:
假设查找 6 ,树的高度只有3。而假设我们查找6到16,因为有单链表,查找区间的效率也高。
有优点当然也有缺点:它的插入与删除数据。具体的我们看看下面提到的索引维护。
4. 了解下回表
前面我本基本上都是将主键作为索引,那么将非主键作为索性会怎么样呢?我们来看看这个例子:
create table Student(
id int primary key,
k int not null,
name varchar(5)
index (k))engine=InnoDB;
insert into Student values(1,20,a);
insert into Student values(2,20,b);
insert into Student values(3,30,c);
insert into Student values(5,50,d);
insert into Student values(6,60,e);
insert into Student values(11,110,f);
insert into Student values(15,150,g);
我们将k作为索引,这时索引就分为了主键索引和非主键索引。因此,在内存中将会产生两棵树:
主键索引的叶子节点存的是整行数据。在InnoDB里,主键索引也被称为聚簇索引(clustered index)。
非主键索引的叶子节点内容是主键的值。在InnoDB里,非主键索引也被称为二级索引(secondary index)。
那么它们又是那么区别呢?
- 如果语句是select * from Student where ID=5,即主键查询方式,则只需要搜索id这棵B+树;
- 如果语句是select * from Student where k=50,即普通索引查询方式,则需要先搜索k索引树,得到id的值为5,再到id索引树搜索一次。这个过程称为回表。
5. 索引维护
B+树为了维护索引有序性,在插入新值的时候需要做必要的维护。以上面这个图为例,如果插入新的行id值为16,则只需要在后面插入一个新记录。如果新插入的id值为11,就相对麻烦了,需要逻辑上挪动后面的数据,空出位置。
而更糟的情况是,如果id为15所在的数据页已经满了,根据B+树的算法,这时候需要申请一个新的数据页,然后挪动部分数据过去。这个过程称为页分裂。在这种情况下,性能自然会受影响。
除了性能外,页分裂操作还影响数据页的利用率。原本放在一个页的数据,现在分到两个页中,整体空间利用率降低大约50%。
当然有分裂就有合并。当相邻两个页由于删除了数据,利用率很低之后,会将数据页做合并。合并的过程,可以认为是分裂过程的逆过程。
基于上面的索引维护过程说明,我们来讨论一个案例:
你可能在一些建表规范里面见到过类似的描述,要求建表语句里一定要有自增主键。当然事无绝对,我们来分析一下哪些场景下应该使用自增主键,而哪些场景下不应该。
自增主键是指自增列上定义的主键,在建表语句中一般是这么定义的: NOT NULL PRIMARY KEY AUTO_INCREMENT。
插入新记录的时候可以不指定ID的值,系统会获取当前ID最大值加1作为下一条记录的ID值。
也就是说,自增主键的插入数据模式,正符合了我们前面提到的递增插入的场景。每次插入一条新记录,都是追加操作,都不涉及到挪动其他记录,也不会触发叶子节点的分裂。
而有业务逻辑的字段做主键,则往往不容易保证有序插入,这样写数据成本相对较高。
除了考虑性能外,我们还可以从存储空间的角度来看。假设你的表中确实有一个唯一字段,比如字符串类型的身份证号,那应该用身份证号做主键,还是用自增字段做主键呢?
由于每个非主键索引的叶子节点上都是主键的值。如果用身份证号做主键,那么每个二级索引的叶子节点占用约20个字节,而如果用整型做主键,则只要4个字节,如果是长整型(bigint)则是8个字节。
显然,主键长度越小,普通索引的叶子节点就越小,普通索引占用的空间也就越小。
所以,从性能和存储空间方面考量,自增主键往往是更合理的选择。
6. 索引相关的几个概念
了解了什么是索引后,我们再来了解下索引的几个概念。
接着上面的T表举例,假设我们需要执行一条语句
select * from Student where k between 20 and 60;
它在树中是如何进行扫描的呢?
- 在k索引树上找到k = 20的记录,取得 id = 2;
- 再到id索引树查到id = 2获取对应的行数据;
- 再在k索引树查到k = 60的记录,取得id = 6;
- 又回到id索引树查到id = 6获取对应的行数据;
- 最后还要取k索引树下一个值,判断条件,不满足结束。
我们能够发现,在这么条语句中,我们对k索引树进行了三系扫描(1、3、5),以及进行了两次回表(2、4)。
我们会发现,过程非常复杂,那么我们该如何避免呢?
覆盖索引
看看这条语句
select id from Student where k between 20 and 60;
这时,我们只需要查id的值,而id的值在k索引树上已有了,此时就无需进行回表操作了。即索引k已经覆盖了我们所要查找的值,我们称这为覆盖索引。
由于覆盖索引可以减少树的搜索次数,显著提升查询性能,所以使用覆盖索引是一个常用的性能优化手段。
例如如果我们要根据k查询name值,那么我们必须进行回表操作。但是,如果我们建立一个k、name联合索引,则可以直接在索引树中找到我们想要的值,无需再进行回表操作了。
最左前缀原则
首先我要明确一点的是,最左前缀原则主要使用在联合索引中
那么它到底是用来干什么的呢?其实,是用来快速定位记录的。
我们来看一个例子:
create table Student(
id int primary key,
name varchar(5),
age int,
KEY `name_age_INX` (name,age),
)engine=InnoDB;
同样是一个学生表,我们对姓名和年龄进行联合索引。
我们添加一些数据
insert into Student values(1,"张三",20);
insert into Student values(2,"张四",18);
insert into Student values(3,"李三",19);
insert into Student values(4,"李四",25);
有了数据后,我们来看看查找数据:
当你的逻辑需求是查到所有名字是“张三”的人时,可以快速定位,然后向后遍历得到所有需要的结果。
如果你要查的是所有名字第一个字是“张”的人,你的SQL语句的条件是
select * from Student where name like '张%'
这时,你也能够用上这个索引,查找到第一个符合条件的记录,然后向后遍历,直到不满足条件为止。
可以看到,不只是索引的全部定义,只要满足最左前缀,就可以利用索引来加速检索。这个最左前缀可以是联合索引的最左N个字段,也可以是字符串索引的最左M个字符。
我们再看看不遵循最左前缀原则会怎么样:
select * from Student where name like '%三'
当我们要查找名字带"三"的学生时,这时,索引就会失效,只能进行全表扫描。
类似的,举例下索引失效的几个案例:
- 如果条件中有or,即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)。注意:要想使用or,又想让索引生效,只能将or条件中的每个列都加上索引
- 对于多列索引,不是使用的第一部分,则不会使用索引(即不符合最左前缀原则)
- like查询是以%开头
- 如果列类型是字符串,那一定要在条件中将数据使用引号引用起来,否则不使用索引
- 如果mysql估计使用全表扫描要比使用索引快,则不使用索引
- 如果查询条件中含有函数或表达式,将导致索引失效而进行全表扫描
索引下推
select * from Student where name like '张%' and age = 20
我们知道,根据最左前缀原则,当找到性张的学生后,进行回表,之后再判断age是否等于20。
因此,我么们不难发现,我们根据索引,有几个姓张的学生,那么我们就要进行几次回表。
那么如何解决呢,没错再MySQL5.6后就对索引进行了优化,引入了索引下推。可以在索引遍历过程中,对索引中包含的字段先做判断,直接过滤掉不满足条件的记录,减少回表次数。
第一张图为无索引下推,当查询到姓张的学生后,进行两次回表,再判断age是否符合,最后获取数据。
第二张图为有索引下推,当查询到姓张的学生后,根据age判断是否符合,符合在进行回表获取数据,不符合则直接放弃。
7. 小结
了解了什么是索引,那么我们想一想:
索引能够加快查询速度,**为什么不每个字段加一个索引,或加多个联合索引呢?**这样查询速度不就更快。
其实答案我在文中提到过:
因为索引会占用额外的内存,因为需要一个B+树来表示索引,如果索引多了的话,那么每个索引都要一个B+树。可想可知,是比较占用空间的。
其次,再增加删除时,我们不难发现,我们要对B+树进行移动,有时还会进行页分裂,页合并。这些操作都是比较耗时的。索引一多,反而会降低插入删除速度。
而且,索引还需要在内存和磁盘上进行交换,索引一多,它们的交换时间可想而知。
那么我们以后再创建索引时要注意哪些呢?
首先我们讲到:为什么在建表中,要加一个自增主键,它的好处是什么。
其次讲到:索引相关的几个概念:覆盖索引、最左前缀原则、索引下推
这些都能帮助我们更好的运用索引。