本节内容:
1)索引基础
2)索引类型(Hash索引、有序数组、B+树)
3)索引的几个常见问题
1)联合索引
2)最左前缀原则
3)覆盖索引
4)索引下推
1. 索引基础
索引对查询的速度有着至关重要的影响,理解索引也是进行数据库性能调优的起点,索引就是为了提高数据查询的效率。索引可以包含一个或多个列的值,如果索引包含多个列的值,则列的顺序也十分重要,因为MySQL只能高效地使用索引的最左前缀列。
2. 索引类型
用于提高读写效率的数据结构有很多,这里先介绍常见的3种,分别是:
- 哈希表
- 有序数组
- 搜索树(重点)
2.1 哈希索引
哈希表是一种以键-值(key-value)的方式存储数据的结构,我们只要输入待查找的值(即key),就可以找到其对应的值(即Value)。哈希的思路很简单,把值放在数组里,用一个哈希函数把key换算成一个确定的位置,然后把value放在数组的这个位置,即idx = Hash(key)
。如果出现哈希冲突,就采用拉链法解决。
因为哈希表中存放的数据不是有序的,因此不适合做区间查询,适用于只有等值查询的场景。
2.2 有序数组
有序数组在等值查询和范围查询场景中的性能都非常优秀。用二分法就可以快速找到(时间复杂度为O(logN))。但是如果要往中间插入一条数据,则必须挪动后面的所有记录,成本较高。因此,有序数组只适用于静态存储引擎,即数据表一旦建立后不再会修改。
2.3 B+树索引(InnoDB)
首先,得先好好理解什么是B+树!看单独介绍B树、B+树的文章,基于篇幅不在此赘述。简单的说,是因为使用B+树存储数据可以让一个查询尽量少的读磁盘,从而减少查询时磁盘I/O的时间。
在 InnoDB 中,表都是根据主键顺序以索引的形式存放的,这种存储方式的表称为索引组织表。又因为前面我们提到的,InnoDB 使用了 B+ 树索引模型,所以数据都是存储在 B+ 树中的。每一个索引在 InnoDB 里面对应一棵 B+ 树。
假设,有这样一张表:该表主键为ID,且还有一个字段为k,并在k上有索引。
CREATE TABLE T(
id int primary key,
k int not null,
index (k)
)engine=InnoDB;
表中有5条记录,分别为R1~R5,(100,1)、(200,2)、(300,3)、(500,5)和(600,6)。则在InnoDB中的索引组织结构是这样的:
根据叶子结点的内容,索引类型分为主键索引和非主键索引。
- 主键索引的叶子结点存的是整条记录,主键索引也被称为聚簇索引(clustered index)。
- 非主键索引的叶子结点存的是主键的值,非主键索引也被称为二级索引(secondary index)/普通索引/辅助索引。
那么,基于主键索引和非主键索引的查询有什么区别?
- 如果语句是 select * from T where ID=500,即主键查询,则只需要搜索ID这棵树。
- 如果语句是 select * from T where k=5,即非主键索引查询,则需要先搜索k索引树,得到ID的值为500,再到ID索引树搜索一次。从非主键索引回到主键索引的过程称为回表。
也就是说,基于非主键索引的查询需要多扫描一棵索引树。因此,我们在应用中应该尽量使用主键查询。而从存储空间的角度讲,因为非主键索引树的叶结点存放的是主键的值,那么,应该考虑让主键的字段尽量短,这样非主键索引的叶子结点就越小,非主键索引占用的空间也就越小。一般情况下,建议创建一个自增主键,这样非主键索引占用的空间最小。
3. 联合索引
联合索引是指对表上的多个列进行索引。下面以一个例子进行说明。假设有下面这样一张表,有这样一个需求,我们需要查询某个用户的购物情况,并按照时间进行排序,取出某用户近几次的购物情况。(注:例子来源于《MySQL技术内幕》)
// 表
CREATE TABLE buylog(
userid int not null,
buy_date DATE
)ENGINE=InnoDB;
// 插入数据
insert into buylog values(1, '2019-08-13');
insert into buylog values(2, '2019-08-14');
insert into buylog values(3, '2019-08-15');
insert into buylog values(1, '2019-08-11');
insert into buylog values(3, '2019-08-10');
insert into buylog values(1, '2019-08-12');
// 添加索引
alter table buylog add index(userid);
alter table buylog add index(userid, buy_date);
// (或用key关键字也一样的)
alter table buylog add key(userid);
alter table buylog add key(userid, buy_date);
上面的代码建立了两个索引,两个索引都包含了userid字段。
如果只对于userid进行查询,如:
select * from buylog where userid=2;
通过explain
查看该语句的执行情况,如下,(explain的用法,简单了解)
可以看到,possible_keys在这里有两个索引可供使用,分别是userid索引和(userid,buy_date)联合索引。优化器最终选择的索引(即key)是userid,因为该索引的叶子节点只包含单个键值,所以理论上一页能存放的记录会更多(意味着可以减少查询的次数)。
接着假定要查询userid为1的最近两次的购买记录,如:
select * from buylog where userid=1 order by buy_date desc limit 2;
同样的,我们看一下它的执行过程是怎样的,如下:
可以看到,这一次查询优化器选择的索引是userid_2(也就是(userid, buy_date)联合索引)。为什么呢?因为在这个联合索引中,记录已经分别根据userid和buy_date排好序了,利用这个索引则可以直接取出相应的数据而无需再对buy_date额外做一次排序操作了。如果强制使用userid索引,则它的执行计划如下:
从Extra字段可以看出,该语句的执行需要使用fliesort,也就是需要一次额外的排序操作才能完成查询。显然,这个排序就是对buy_date字段的排序,因为这里仅使用了userid索引,该索引未对buy_date进行排序。
总结:
联合索引(a, b)是根据a, b进行排序(先根据a排序,如果a相同则根据b排序)。因此,下列语句可以直接使用联合索引得到结果(事实上,也就是用到了最左前缀原则):
select ... from xxx where a=xxx;
select ... from xxx where a=xxx order by b;
而下列语句则不能使用联合查询:
select ... from xxx where b=xxx;
对于联合索引(a, b, c),下列语句同样可以直接通过联合索引得到结果:
select ... from xxx where a=xxx order by b;
select ... fr