凡人,来感受一下组合索引的威力吧

大家好,我是飘渺!

相信各位在面试时,通常会被问到“什么是索引?” 而你肯定可以脱口而出:索引是提升查询速度的一种数据结构。而索引之所以能提升查询速度,在于它在插入时对数据进行了排序。

在实际业务中,我们会遇到很多复杂的场景,比如对多个列进行查询。这时,可能会要求用户创建多个列组成的索引,如列 a 和 b 创建的组合索引,但究竟是创建(a,b)的索引,还是(b,a)的索引,结果却是完全不同的。

今天,我们就来聊聊更贴近业务实战的组合索引,一起来感受一下组合索引的威力。(当然咯,文章中讲的索引指的是B+树索引,就是那个矮胖子啦)

组合索引

组合索引(Compound Index)是指由多个列所组合而成的 B+树索引,这和B+ 树索引的原理完全一样,只是单列索引是对一个列排序,现在是对多个列排序。

image-20210717222617272

从上图可以看到,组合索引只是排序的键值从 1 个变成了多个,本质还是一颗 B+ 树索引。但是你一定要意识到(a,b)和(b,a)这样的组合索引,其排序结果是完全不一样的。

假如有如下一张表test,给其创建了一个组合索引union_index

create table test
(
    id       int auto_increment primary key,
    name     varchar(50) null,
    workcode varchar(50) null,
    age      int         null
);

create index union_index on test (name, workcode);

那对于组合索引(name,workcode)来说,因为它对name,workcode做了排序,所以它可以对下面两个查询进行优化

select * from test  where  name = 'zhang' ;
select * from test  where  name = 'zhang' and workcode='20190169';

值得注意的是,where后查询列name 和workcode的顺序无关,即使写成where workcode = '20190169' and name ='zhang'仍然可以使用组合索引(name,workcode)。

image-20210710205716526

但是下面的sql无法使用组合索引(name,workcode),因为(name,workcode)排序并不能推出(workcode,name)排序。

select * from test where  workcode='20190169';

此外,同样由于索引(name,workcode)已排序,因此下面这条 SQL 依然可以使用组合索引(name,workcode),以此提升查询的效率:

select * from test  where  name = 'zhang' order by workcode;

同样的原因,索引(name,workcode)排序不能得出(workcode,name)排序,因此下面的 SQL 无法使用组合索引(name,workcode):

select * from test  where  workcode = '20190169' order by name ;

讲到这儿,你已经掌握了组合索引的基本内容,接下来我们就看一看怎么在业务实战中正确地设计组合索引?

业务索引设计实战

避免额外排序

在真实的业务场景中,你会遇到根据某个列进行查询,然后按照时间排序的方式逆序展示。

比如在微博业务中,用户的微博展示的就是根据用户 ID 查询出用户订阅的微博,然后根据时间逆序展示;又比如在电商业务中,用户订单详情页就是根据用户 ID 查询出用户的订单数据,然后根据购买时间进行逆序展示。

接着我们看一下我们线上一个真实的商机表,已经对字段做了简化,只保留几个关键字段,同时为了方便测试,直接初始化了70多万的数据。

CREATE TABLE t_opp_base
(
    id                  int            primary key auto_increment,
    opp_code            varchar(50)    NOT NULL,  -- 商机编码
    opp_name            varchar(200)   NOT NULL,
    principal_user      varchar(50)    NOT NULL,  -- 责任人
    opp_status          char(1)        NOT NULL,
    opp_amount     			decimal(15, 2) NOT NULL,
    opp_date            date           NOT NULL,
    opp_priority        char(15)       NOT NULL,
    remark              varchar(79)    NOT NULL,
    KEY `idx_opp_code` (opp_code),
    KEY `idx_principal_user` (principal_user)
);

其中:

  • 字段 id 是 INT 类型的主键;
  • 字段 opp_code,principal_user 由于查询的场景比较多,所以添加了单字段索引
  • 字段 opp_date、opp_status、opp_amount、opp_priority 用于商机的基本详情,分别表示商机时间、当前商机的状态、商机的总价值、商机的优先级。

在有了上述商机表后,当用户查看javadaily负责的商机信息,并且需要根据商机时间排序查询时,可通过下面的 SQL:

select * from t_opp_base  where principal_user = 'javadaily' order by opp_date DESC

但由于上述表结构的索引设计时,索引 idx_principal_user 仅对列 principal_user 排序,因此在取出用户的数据后,还需要一次额外的排序才能得到结果,可查看执行计划 EXPLAIN 确认:

image-20210716111431856

通过上面的执行计划可以看出,SQL 语句的确可以使用索引 idx_principal_user,但在 Extra 列中显示的 Using filesort,表示还需要一次额外的排序才能得到最终的结果。

由于已对列 principal_user 创建索引,因此上述 SQL 语句并不会执行得特别慢,但是在高并发的情况下,每次 SQL 执行都需要排序就会对业务的性能产生非常明显的影响,比如 CPU 负载变高,QPS 降低。

要解决这个问题,最好的方法是:在取出结果时已经根据字段 opp_date 排序,这样就不用额外的排序了。

所以,我们在表t_opp_base上创建一个新的组合索引,idx_principal_oppdate,对字段(principal_user,opp_date)进行索引。

create index idx_principal_oppdate
    on t_opp_base (principal_user,opp_date);

这是再执行之前的sql,根据时间展示责任人负责的商机项目,其执行计划为:

image-20210716140737174

这样我们就消除了Using filesort,提高了执行效率。

索引覆盖,避免回表

基础概念:

SQL需要二级索引查询得到主键值,然后再根据主键值搜索主键索引,最后定位到完整的数据。这一过程叫 回表。
但是由于二级组合索引的叶子节点,包含索引键值和主键值,若查询的字段在二级索引的叶子节点中,则可直接返回结果,无需回表。这种通过组合索引避免回表的优化技术也称为 索引覆盖(Covering Index)。

比如有下面一条SQL:

select principal_user,opp_date,opp_amount from t_opp_base  where principal_user = 'javadaily' ;

查看其执行计划:

-> Index lookup on t_opp_base using idx_principal_oppdate (principal_user='javadaily')  (cost=312.51 rows=321) (actual time=0.452..0.908 rows=321 loops=1)

它的执行计划显示使用了之前创建的组合索引idx_principal_user,但是,由于组合索引的叶子节点只包含(principal_user,opp_date,id),没有字段 opp_amount 的值,所以需要通过 id 回表找到对应的 opp_amount

执行计划中显示执行成本cost为312.51。(cost=312.51 表示的就是这条 SQL 当前的执行成本。不用关心 cost 的具体单位,你只需明白cost 越小,开销越小,执行速度越快。)

如果想要避免回表,可以通过索引覆盖技术,创建(principal_user,opp_date,opp_amount)的组合索引,如:

alter table t_opp_base add index
 idx_principal_oppdate_amount(principal_user,opp_date,opp_amount);

再次查看执行计划:

-> Index lookup on t_opp_base using idx_principal_oppdate_amount (principal_user='javadaily')  (cost=41.52 rows=321) (actual time=0.149..0.337 rows=321 loops=1)

执行成本有明显的下降,从312.51降到了41.52,执行效率大大提高。

image-20210716143942546

可以看到执行计划选择了idx_principal_oppdate_amount索引,同时Extra列显示为 Using index,这就表示使用了覆盖索引技术。

上面这条SQL一共返回321条记录,这意味着在未使用索引覆盖技术前,这条 SQL 需要总共回表 321 次, 每次从二级索引读取到数据,就需要通过主键去获取字段 opp_amount。在使用索引覆盖技术后,无需回表,减少了 321次的回表开销,这就是为什么执行成本会减少这么多的原因。

接下来我们再看看这条SQL

select principal_user,sum(opp_amount) from t_opp_base  group by principal_user;

这条SQL根据商机责任人分组汇总,找出每个责任人负责的商机价值总额,对责任人进行考核。

为了让大家直观感受一下索引覆盖的威力,我先删掉之前创建的索引idx_principal_oppdate_amount

ALTER TABLE t_opp_base
drop INDEX idx_principal_oppdate_amount;

查看其执行计划

image-20210716152451026

image-20210716152308960

可以看到,这条 SQL 优化选择了索引 idx_principal_oppdate,但由于该索引没有包含字段opp_amount,因此需要回表,根据 rows 预估出大约要回表 717912 次。同时也可以看到执行成本为76850.31,执行时间为10.9秒。

然后我们再次加上组合索引idx_principal_oppdate_amount

alter table t_opp_base add index
 idx_principal_oppdate_amount(principal_user,opp_date,opp_amount);

再次查看执行计划

image-20210716153719022

可以看到,这次的执行计划提升使用了组合索引 idx_principal_oppdate_amount,并且通过Using index 的提示,表示使用了索引覆盖技术。同时执行时间为1.74s,SQL性能大大提升。

image-20210716160351452

这就是索引覆盖技术的威力,而且这还只是基于 t_opp_base 表总共 70 万条记录。若表 t_opp_base 的记录数越多,需要回表的次数也就越多,通过索引覆盖技术性能的提升也就越明显。

小结

组合索引也是一颗 B+ 树,只是索引的列由多个组成,组合索引既可以是主键索引,也可以是二级索引。组合索引主要有以下三个优势:

  • 覆盖多个查询条件,如(a,b)索引可以覆盖查询 a = ? 或者 a = ? and b = ?;

  • 避免 SQL 的额外排序,提升 SQL 性能,如 WHERE a = ? ORDER BY b 这样的查询条件;

  • 利用组合索引包含多个列的特性,可以实现索引覆盖技术,提升 SQL 的查询性能,用好索引覆盖技术,性能提升 10 倍不是难事。