对mysql的联合索引的深刻理解

背景

对mysql的联合索引的考察是Java程序员面试高频考点！必须深刻理解掌握否则容易丢分非常可惜。

技术难点

考察对最左侧匹配原理理解。

原理

暂且不表。网上讲这非常多。我理解就是，B+树每个非叶子节点的值都是有序存放索引的值。

比如对A、B、C 三个字段做联合索引。B+输每个节点都存储A、B、C的值。首先保证A有序其次保证B有序。

重点看实战。

实战

我们做一次实战记录，避免下次面试再次犯错。

环境

mysql版本 5.7.38-log

建表语句

CREATE TABLE `user_cart_item` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `user_id` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '用户id',
  `goods_id` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '商品id',
  `goods_sku_id` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '商品id 有skuid的话为什么要存商品id',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '物品篮项目创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '物品篮项目更新时间',
  `sku_price` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '价格',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_user_cart_item` (`user_id`,`goods_id`,`goods_sku_id`) USING BTREE
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC COMMENT='物品篮项';

视代码可见我们创建一个联合索引 idx_user_cart_item

查询

 查询条件是user_id、goods_id、goods_sku_id，一定能匹配上索引没问题

EXPLAIN SELECT
	user_id,goods_id, goods_sku_id,sku_price
FROM
	user_cart_item 
WHERE
	user_id=248 and goods_id = 1 and goods_sku_id = 1

结果如下： 

 查询条件是user_id、goods_id，一定能匹配上索引没问题

EXPLAIN SELECT
	user_id,goods_id, goods_sku_id,sku_price
FROM
	user_cart_item 
WHERE
	user_id=248 and goods_id = 1 

结果如下：

 查询条件是user_id，一定能匹配上索引没问题

EXPLAIN SELECT
	user_id,goods_id, goods_sku_id,sku_price
FROM
	user_cart_item 
WHERE
	user_id=248 

结果如下：

查询条件是user_id，goods_sku_id ，按最左侧匹配原则也是能使用索引

EXPLAIN SELECT
	user_id,goods_id, goods_sku_id,sku_price
FROM
	user_cart_item 
WHERE
	user_id=248 and goods_sku_id = 1 

 结果如下：

查询条件是goods_sku_id 看能否使用索引

EXPLAIN SELECT
	user_id,goods_id, goods_sku_id,sku_price
FROM
	user_cart_item 
WHERE
  goods_sku_id = 1 

结果如下：

重点

不满足最左侧匹配是不能使用联合索引的。

特殊情况不满足最左侧匹配也能使用联合索引

EXPLAIN SELECT
	user_id,goods_id, goods_sku_id
FROM
	user_cart_item 
WHERE
  goods_sku_id = 1 

结果如下：

索引类型不是ref，而是index。是在索引范围内查找。甚过All 全表扫描

重点

当联合索引能覆盖查询字段（覆盖索引）。但查询条件并不满足最左侧匹配原则。mysql查询优化器也推荐我们使用联合索引。但索引是index。 也就是索引内查找。