深入理解组合索引

前言

上文提到了,索引分单列索引组合索引。单列索引,即一个索引只包含单个列,一个表可以有多个单列索引,但这不是组合索引。

组合索引(Composite Index),又称复合索引、联合索引、多列索引,为什么可以有这么多别名,下文中可任意替代!

顾名思义,是建立在多个列上的索引,适用在多个列必须一起使用或者是从左到右方向部分连续列一起使用的业务场景。创建索引时,你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件,且条件不为OR)。

组合索引的本质

ALTER TABLE table_name ADD INDEX (col1,col2,col3);

当我们创建(col1,col2,col3)联合索引时,

相当于创建了 (col)单列索引(clo1,clo2)联合索引以及 (col1,col2,col3)联合索引,想要索引生效,只能使用col1col1,col2col1,col2,col3三种组合;当然,col1,col3组合也可以,但实际上只用到了col1的索引,col3并没有用到!

组合索引是有明确的先后顺序的,由此引出组合索引的最左前缀匹配原则。

组合索引的意义

一个顶三个

如同本质,建了一个(a,b,c)的复合索引,那么实际等于建了(a),(a,b),(a,b,c)三个索引,因为每多一个索引,都会增加写操作的开销和磁盘空间的开销。对于大量数据的表,这可是不小的开销!

覆盖索引

如果有组合索引(a,b,c),如果有如下的sql(select的字段全部命中在组合索引上):

select a, b, c from table where a=1 and b = 1

那么MySQL可以直接通过遍历索引取得数据,而无需回表,这减少了很多的随机io操作。减少io操作,特别的随机io其实是dba主要的优化策略。所以,在真正的实际应用中,覆盖索引是主要的提升性能的优化手段之一。

索引列越多,通过索引筛选出的数据越少

索引列越多,通过索引筛选出的数据越少。有1000W条数据的表,有如下sql:

select from table where a = 1 and b =2 and c = 3

假设假设每个条件可以筛选出10%的数据,

如果只有单值索引,那么通过该索引能筛选出1000w * 10% = 100w 条数据,然后再回表从100w条数据中找到符合b=2 and c= 3的数据,然后再排序,再分页;

如果是复合索引,通过索引筛选出1000w * 10% * 10% *10% = 1w,然后再排序、分页,哪个更高效,一眼便知。

概括:多个单列索引在多条件查询时只会生效第一个索引!所以多条件联合查询时最好建联合索引!

最左前缀匹配原则

在MySQL建立联合索引时会遵守最左前缀匹配原则,即最左优先,在检索数据时从联合索引的最左边开始匹配。

首先,最左前缀原则是发生在复合索引上的,只有复合索引才会有所谓的左和右之分。

要想理解联合索引的最左匹配原则,先来理解下索引的底层原理。索引的底层是一颗B+树,那么联合索引的底层也就是一颗B+树,只不过联合索引的B+树节点中存储的是键值。由于构建一棵B+树只能根据一个值来确定索引关系,所以数据库依赖联合索引最左的字段来构建。

下图是一个(a,b)的联合索引的索引树:

其中a是有顺序的,而b是没有顺序的,但是在a等值的情况下,b值又是按顺序排列的,这种顺序是相对的。

这是因为MySQL创建联合索引时首先对联合索引的最左边字段排序,在最左字段的排序基础上,然后在对次左字段进行排序。所以直接查询非最左字段无法利用该联合索引。

对于这个结论,我们可以通过EXPLAIN语句来快速验证。

看个例子

疑问

最近,在 mysql 测试最左前缀原则,发现了匪夷所思的事情。根据最左前缀原则,本来应该索引失效,走全表扫描的,但是,却发现可以正常走索引。

1
2
3
4
5
6
7
8
9
10
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(32) COLLATE utf8mb4_bin DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`address` varchar(128) COLLATE utf8mb4_bin DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_user` (`name`,`age`,`address`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

INSERT INTO user(`id`, `name`, `age`, `address`) VALUES (1, 'zs', 12, 'beijing');

表中总共有四个字段。 id 为主键,还有一个由 name,age,address 组成的联合索引。 存储引擎为 InnoDB,并插入一条测试数据。

根据最左前缀原则,以下 sql ,肯定会使索引失效的。

1
EXPLAIN SELECT * FROM user WHERE address='beijing';

然而通过查看EXPLAIN语句,发现它竟然走了索引。

解答

先利用覆盖索引原理,只查询特定的字段(只有主键和联合索引字段):

1
EXPLAIN SELECT id,name,age,address FROM student WHERE address='beijing';

问题来了,此时违反了最左前缀原则,但是符合覆盖索引,为什么就走索引了呢?

我们对比一下,若用最左列,和不用最左列,它们的执行计划有何不同。

会发现,若不符合最左前缀原则,则 type为 index,若符合,则 type 为 ref。

  • index 代表的是会对整个索引树进行扫描,如例子中的,最右列 address,就会导致扫描整个索引树。
  • ref 代表 mysql 会根据特定的算法查找索引,这样的效率比 index 全扫描要高一些。但是,它对索引结构有一定的要求,索引字段必须是有序的。而联合索引就符合这样的要求!

联合索引内部就是有序的,我们可以把它理解为类似于 order by name,age,address 这样的排序规则。会先根据 name 排序,若name 相同,再根据 age 排序,依次类推。

所以,这也解释了,为什么我们要遵守最左前缀原则。

当最左列有序时,才可以保证右边的索引列有序,此时会走组合索引。

退而求其次,若不符合最左前缀原则,但是符合覆盖索引,就可以扫描整个索引树,从而找到覆盖索引对应的列(避免了回表)。

若不符合最左前缀原则,且也不符合覆盖索引(形同 select *),则需要扫描整个索引树。完成之后,还需要再回表,查询对应的行记录。此时,查询优化器会认为,这样的两次查询索引树,不如全表扫描来的快(因为联合索引此时不符合最左前缀原则,要比普通单列索引查询慢的多),此时就会走全表扫描

所以回到问题:

1
2
3
4
5
-- 最初的sql
EXPLAIN SELECT * FROM user WHERE address='beijing';

-- 等同于
EXPLAIN SELECT id, name, age, address FROM user WHERE address='beijing';

这个结构就是我们上边讨论的情况:不符合最左前缀原则,但是符合索引覆盖。这种情况,是会走索引的。


参考资料:

为什么最左前缀原则失效了? - 知乎 (zhihu.com)

欢迎关注我的其它发布渠道