关于联合索引失效的一些测试验证

这篇文章主要是验证联合索引在什么情况下会失效。

什么是联合索引

将两个或者更多的字段结合在一起建立索引就叫联合索引，比如说下面这条SQL，就是将deleted和create_time创建联合索引。

alter table article add index idx_create_time_deleted(deleted, create_time desc);

最左匹配原则

最左匹配原则是数据库索引优化中的一个重要概念，特别适用于联合索引。在MySQL中，最左匹配原则指的是在一个联合索引中，如果查询条件涉及到索引的多个列，那么查询时只能使用联合索引中的最左边的列或者列组合来实现索引的快速匹配。

用几个例子来说明吧，假设现在表T有联合索引(a, b, c)，下面哪些查询语句会走索引？如果走索引的话是哪些字段可以使用索引？

1. select * from T where a = 1 and b = 2 and c = 3;
2. select * from T where a = 1 and b > 2 and c = 3;
3. select * from T where c = 1 and a = 2 and b = 3;
4. select * from T where a = 2 and c = 3;
5. select *from T where b = 2 and c = 3;
6. select (a,b) from T where a = 1 and b > 2;

1. 符合最左匹配原则， abc全部可以走
2. 在遇到b > 2范围查询时结束索引匹配，只有 ab使用了索引
3. 因为MySQL有优化器，所以where c = 1 and a = 2 and b = 3;会被优化成和第一条一样，结论同第一条
4. a可以走索引，到b的时候无法匹配，所以c无法使用索引
5. 无法匹配a，所以没有任何字段能利用到索引
6. 同第二条

联合索引范围查询

**从上面例子可以看出即使查询条件包括了全部的索引字段，也会出现一些字段无法利用上索引的情况。**这种情况跟范围查询有很大的关系，接下来会用几个例子来验证不同情况的索引利用情况。

PS：以下所有例子都建立了联合索引(age, duration)，文末会提供本文中所用到的建表语句。

非等值范围查询

以下面SQL为例：

select * from t_user where age > 10 and duration = 5;

由于联合索引是按照age来排序的，所以在扫描联合索引树时会将所有符合age > 10条件的记录全部查出来，所以age字段毫无疑问可以使用索引，但是在这些符合条件的记录里面。duration的值是无序的，所以没有办法利用到。我们可以使用执行计划来确定这一点。

从执行计划中可以看到，key_len为4，证明只有age字段使用了索引。（如果age允许为空这里会是5）

等值范围查询

以下面SQL为例：

select * from t_user where age >= 10 and duration = 5;

和上面的SQL唯一不同的是age >= 0，上面是只有大于。同样查看执行计划如下所示：

可以看到key_len的值是8，证明两个字段都使用了索引。对于age字段不用多说，符合age >= 10的记录里面可以duration确实是无序的，但是对于age = 10的所有记录里面，duration是有序的，所以在扫描索引时当扫描到age = 10的记录时可以通过duration = 5来缩小查询范围。我个人觉得可以从另外一个角度思考方便理解：age >= 10会被MySQL拆成两个条件，一个是age > 10，另外一个是age = 10，即下面的SQL。

select * from t_user where age > 10 and duration = 5
union
select * from t_user where age = 10 and duration = 5;

Between ... And ...

以下面SQL为例：

select * from t_user where age between 15 and 30 and duration = 5;

在MySQL中，between ... and ...相当于>= and <=，从下面的执行计划可以看出两个字段都使用了联合索引。

Like查询

以下面SQL为例：

explain select * from t_user where name like 't%' and duration = 12;

联合索引以t开头的索引中duration确实是无序的，但是对于name = t的记录里duration是有序的，所以可以利用到索引。查看执行计划也能得出结论，下面的key_len等于70，这个70是怎么算出来的呢？name字段是varchar(16) default null ，使用的编码是utf8mb4，所以占用字节数是 16 * 4 + 1 + 1 = 66，duration是int not null，所以最终的key_len就是66 + 4 = 70。

小结

联合索引的最左匹配原则对于 > <时就会停止匹配，但是对于 >= <= between ... and .. like 'xxx%'并不会终止匹配。

本文所用SQL语句：

# MySQL版本8.0.16
CREATE TABLE `t_user` (
  `id` int(11) NOT NULL,
  `name` varchar(16) NOT NULL,
  `age` int(11) NOT NULL,
  `string_number` varchar(5) DEFAULT NULL COMMENT '测试字段，验证类型转换索引失效问题',
  `duration` int(11) NOT NULL COMMENT '创建时长 单位:天',
  PRIMARY KEY (`id`),
  KEY `idx_age_duration` (`age`,`duration`),
  KEY `idx_name_duration` (`name`,`duration`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

/*Data for the table `t_user` */

insert  into `t_user`(`id`,`name`,`age`,`string_number`,`duration`) values 
(1,'路飞',10,'12',5),
(5,'test',21,'12',12),
(10,'山治',22,'10',22),
(15,'乌索普',20,'15',5),
(20,'香克斯',39,'201',5),
(22,'test',12,'22',78),
(27,'卡卡西',30,'11',5);