MySql 组合索引的使用

MySql 组合索引的使用

测试Mysql组合索引在不同的查询条件组合下的索引使用情况。当有a+b+c 3个字的的组合索引时，按照MySql 的左匹配原则，a+b+c，a+b，a 是满足左匹配原则，肯定是会走索引的，但是其他的场景，比如a+c, b+c, b+a, b+a+c 等是否仍会走索引呢？

测试环境

MySql 5.7

测试表：

CREATE TABLE `report_user_achievement` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `user_id` int(11) NOT NULL COMMENT '用户id',
  `user_name` varchar(128) DEFAULT NULL COMMENT '用户名称',
  `is_inner` tinyint(1) NOT NULL COMMENT '是否为内部',
  `year` int(11) NOT NULL COMMENT '年',
  `month` int(11) NOT NULL COMMENT '月',
  `week_of_month` int(11) NOT NULL COMMENT '周（本月）',
  `date_time` datetime(6) NOT NULL COMMENT '日期',
  `achievement_amount` decimal(11,2) DEFAULT NULL COMMENT '业绩金额',
  `last_business_date_time` datetime(6) DEFAULT NULL COMMENT '最近的业务时间',
  PRIMARY KEY (`id`),
  KEY `idx__user_id__date_time__is_inner` (`user_id`,`date_time`,`is_inner`) COMMENT ''
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='用户报表-业绩表'

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

测试

测试肯定走索引的场景

a+b+c，a+b，a

EXPLAIN
SELECT * FROM `report_user_expert_network_achievement` WHERE user_id = 56;  

EXPLAIN
SELECT * FROM `report_user_expert_network_achievement` WHERE user_id = 56 AND date_time = '2023-12-07 00:00:00.000000';  

EXPLAIN
SELECT * FROM `report_user_expert_network_achievement` WHERE user_id = 56 AND date_time = '2023-12-07 00:00:00.000000' AND is_inner = FALSE;  
1
2
3
4
5
6
7
8

EXPLAIN 结果三条记录都是走索引

测试肯定不走索引的场景

b, c, b+c,c+b

EXPLAIN
SELECT * FROM `report_user_achievement` WHERE date_time = '2023-12-07 00:00:00.000000' ; 

EXPLAIN
SELECT * FROM `report_user_achievement` WHERE is_inner = FALSE;  #ALL Using where

EXPLAIN
SELECT * FROM `report_user_achievement` WHERE date_time = '2023-12-07 00:00:00.000000' AND is_inner = FALSE; 

EXPLAIN
SELECT * FROM `report_user_achievement` WHERE is_inner = FALSE AND date_time = '2023-12-07 00:00:00.000000';

1
2
3
4
5
6
7
8
9
10
11
12

EXPLAIN 结果都是ALL 走了全表扫描，没有走索引。

测试可能会走索引的场景

由于Mysql 会对查询条件进行优化，即使没有按照索引的顺序的查询语句，也会按照索引进行优化。例如：b+a+c, b+c+a, c+a+b, c+b+a, b+a

EXPLAIN
SELECT * FROM `report_user_achievement` WHERE date_time = '2023-12-07 00:00:00.000000' AND user_id = 56 AND is_inner = FALSE; 

EXPLAIN
SELECT * FROM `report_user_achievement` WHERE date_time = '2023-12-07 00:00:00.000000' AND is_inner = FALSE AND user_id = 56 ; 

EXPLAIN
SELECT * FROM `report_user_achievement` WHERE is_inner = FALSE AND user_id = 56 AND date_time = '2023-12-07 00:00:00.000000'; 

EXPLAIN
SELECT * FROM `report_user_achievement` WHERE is_inner = FALSE AND date_time = '2023-12-07 00:00:00.000000' AND user_id = 56 ; 

EXPLAIN
SELECT * FROM `report_user_achievement` WHERE date_time = '2023-12-07 00:00:00.000000' AND user_id = 56; 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

EXPLAIN 结果记录都是走索引，说明Where 语句中条件顺序不影响是否走索引。

测试索引下推

对于a+c，c+a 这组合，不完全满足左匹配，但又可以通过索引优化的组合。

EXPLAIN
SELECT * FROM `report_user_achievement` WHERE user_id = 56 AND is_inner = FALSE;  

EXPLAIN
SELECT * FROM `report_user_achievement` WHERE is_inner = FALSE AND user_id = 56; 
1
2
3
4
5

测试结果是仍然走了索引，但和完全走索引不同的是Extra 字段多了Using index condition 。
原因是索引下推 这个MySql 5.7 正式完全支持的功能，

这是一段GPT 对索引下推的描述：

MySQL的索引下推（Index Condition Pushdown，简称ICP）是一个优化查询执行的特性，它允许MySQL在查询过程中更高效地利用复合索引。在没有索引下推的情况下，MySQL会根据索引快速定位到满足条件的记录，然后再回表读取完整的行数据，接着在服务器层面对这些数据进行条件过滤。
索引下推优化则是在获取索引的同时，将那些可以由索引中的列来评估的过滤条件推送到存储引擎层。这样，存储引擎在利用索引查找记录的同时，就可以对索引中的列进行条件过滤，只有满足条件的记录才会被读取并返回给服务器层。这样可以减少不必要的回表操作和数据传输，从而提高查询的效率。
索引下推适用于以下情况：

• 查询使用了复合索引。
• 查询条件中包含了索引列的部分或全部列的条件。
• 存储引擎支持索引下推（InnoDB和MyISAM支持，而Memory存储引擎不支持）。

总结就是因为a+c 这个条件是可以在索引上直接进行过滤的，因此可以先通过索引过滤，再去回表去数据，从而提高效率。