相信各位在面试时,通常会被问到“什么是索引?” 而你肯定可以脱口而出:索引是提升查询速度的一种数据结构。而索引之所以能提升查询速度,在于它在插入时对数据进行了排序。
在实际业务中,我们会遇到很多复杂的场景,比如对多个列进行查询。这时,可能会要求用户创建多个列组成的索引,如列 a 和 b 创建的组合索引,但究竟是创建(a,b)的索引,还是(b,a)的索引,结果却是完全不同的。
今天,我们就来聊聊更贴近业务实战的组合索引,一起来感受一下组合索引的威力。(当然咯,文章中讲的索引指的是B+树索引,就是那个矮胖子啦)
组合索引
组合索引(Compound Index)是指由多个列所组合而成的 B+树索引,这和B+ 树索引的原理完全一样,只是单列索引是对一个列排序,现在是对多个列排序。
从上图可以看到,组合索引只是排序的键值从 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)。
但是下面的sql无法使用组合索引(name,workcode),因为(name,workcode)排序并不能推出(workcode,name)排序。
select * from test where workcode='20190169';