MySQL Index 联合索引探讨

联合索引也叫多列索引, 其常见的实现方式为连接索引(concatenated index), 它是通过将一列的值追加的一列后面形成的, 其连接的顺序由创建索引是指定, MySQL便是使用的这种方式. 另一种方式成为多维索引(multi-dimensional index), 这种方式比较复杂, 有兴趣的同学可自行搜索相关资料.

联合索引的创建

1. 随表创建

CREATE TABLE `t_index_explain` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `a` varchar(20) DEFAULT NULL,
  `b` varchar(20) DEFAULT NULL,
  `c` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_a_b_c` (`a`,`b`,`c`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2. 单独创建

ALTER TABLE `t_index_explain` 
ADD INDEX `index_a_b_c`(`a`, `b`, `c`) USING BTREE;

如上两种方式, 都是创建了一个a, b, c 的联合索引, 其定义的顺序是abc,故在实际索引的中的每一个值为(a, b, c)这样一个连接组合形式, 甚至可以简单粗暴的理解为索引列的值就是a+b+c(以字符串的方式连接在一起). 如此就把多列索引变成了我们熟悉的单列索引. 其工作原理也是一致的.

用法与注意事项

联合索引遵循最左前缀原则, 原因是其索引值连接顺序为定义的从左至右连接, 因此如下SQL是可以使用index_a_b_c索引的:

SELECT * FROM t_index_explain WHERE a  = "1" and b = "1";

如下是该SQL的解释结果:

image.png

其中Extra 说明了我们确实使用了index, possible_keys说明了可能的index只有 index_a_b_c.

常见误区: 很多人认为, 所谓的最左前缀原则, 需要我们编写的SQL条件顺序与定义顺序一致, 其实不是的(查询优化器没那么傻), 如下佐证:

SELECT * FROM t_index_explain WHERE b = "2" and a  = "1";

把a, b 条件的顺序调换一下, 也会得到同样的解释结果. 其成立的条件应该是条件中是否包含index定义最左边的字段. 如下的sql与执行结果可证明这一点.

SELECT * FROM t_index_explain WHERE c = "2" and a  = "1";

image.png
不能使用索引的情况, 在本例中只要不包含a字段的查询, 都不能使用该联合索引(除非索引覆盖).

SELECT * FROM t_index_explain WHERE c = "2";
SELECT * FROM t_index_explain WHERE b = "2";
SELECT * FROM t_index_explain WHERE c = "2" and b = "1";

以上SQL解释执行结果:

image.png

其中Extra提示了Using Index但是possible_keys为NULL, key 中出现了我们创建的索引index_a_b_c, 其原因是发生了索引覆盖. 如果表中的字段/数据丰富一些, 多一些可以观察的不一样的执行结果, 有兴趣的同学可自证.

索引与排序

众所周知, 索引是有序的, 但是实际开发中索引字段, 与排序字段往往不是一个比如订单表中我们需要查询某个用户的数据, 但是按支付时间排序, SQL如下:

表:

CREATE TABLE `t_order`  (
  `id` bigint(0) NOT NULL AUTO_INCREMENT,
  `userid` bigint(0) NULL,
  `pay_time` datetime(0) NULL,
  PRIMARY KEY (`id`)
  KEY `index_userid` (`userid`) USING BETREE
);

select * from t_order where userid = 1 order by pay_time

如果创建的是userid的单列索引, 那么如上查询语句解释结果

image.png

可以看到Extra中出现了 Using filesort 表明该操作需要单独进行一次排序操作.

修改索引为联合索引:

ALTER TABLE `micro_stories`.`t_order` 
DROP INDEX `index_userid`,
ADD INDEX `index_userid_pay_time`(`userid`, `pay_time`) USING BTREE;

image.png

如上解释结果可以看出, 没有filesort了. 所以索引不只可以来做查询条件的, 也可以利用索引的有序性来做排序优化.

总结一下

• 联合索引的每个索引值是以索引定义中字段的顺序, 连接在一起组成的, 其索引的基本结构仍然是B+树
• 联合索引生效需要满足条件中存在索引定义最左边的字段(最左前缀原则)
• 联合所以可用于辅助排序, 提升查询效率