mysql 复合索引(联合索引) a b c的使用

本博文中所用数据版本为mysql 5.7.28
通过命令行查看 mysql版本信息如下：

~ mysql --version
mysql Ver 14.14 Distrib 5.7.28, for macos10.14 (x86_64) using EditLine wrapper

缘由：经常面试被问到 符合索引 (a,b,c) 这三个字段组成的符合复合(联合索引)是否使用的问题。网上答案 通常这样解释

复合索引（联合索引）情况下
a b c 三个字段

在用到 where 条件为

• a && b && c 的情况下 会用到联合索引
• a && b 的情况下也会用到索引
• a && c 的情况下不会用到索引
• b && c 的情况下不会用到索引

上述解释为 错误的 因此，重新整理博文供大家参考。

实际结果如下：
数据库版本如上文 mysql 5.7.28
建表语句为

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `aid` varchar(20) NOT NULL DEFAULT '' COMMENT 'aid',
  `bid` varchar(20) NOT NULL DEFAULT '' COMMENT 'bid',
  `cid` varchar(20) NOT NULL DEFAULT '' COMMENT 'cid',
  PRIMARY KEY (`id`),
  KEY `abc` (`aid`,`bid`,`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

为测试插入数据

UPDATE `study`.`test` SET `aid` = 'a01', `bid` = 'b01', `cid` = 'c01' WHERE `id` = 1;
UPDATE `study`.`test` SET `aid` = 'a02', `bid` = 'b02', `cid` = 'c02' WHERE `id` = 2;
UPDATE `study`.`test` SET `aid` = 'a03', `bid` = 'b02', `cid` = 'c03' WHERE `id` = 3;

组合结果为 abc ab ac bc 四种组合结果

1. abc 组合结果为

mysql> explain select * from test where aid='a01' and bid='b01' and cid='c01'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test
   partitions: NULL
         type: ref
possible_keys: abc
          key: abc
      key_len: 186
          ref: const,const,const
         rows: 1
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

ERROR:
No query specified

可能用到复合索引，实际用到复合索引。

2. ab 的组合结果如下

mysql> explain select * from test where aid='a01' and bid='b01' \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test
   partitions: NULL
         type: ref
possible_keys: abc
          key: abc
      key_len: 124
          ref: const,const
         rows: 1
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

ERROR:
No query specified

可能用到复合索引，实际用到复合索引。

3. ac 的组合情况下

explain select * from test where aid='a01'  and cid='c01'\G;
*************************** 1. row ***************************
          id: 1
 select_type: SIMPLE
       table: test
  partitions: NULL
        type: ref
possible_keys: abc
         key: abc
     key_len: 62
         ref: const
        rows: 1
    filtered: 33.33
       Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)

ERROR:
No query specified

可能用到复合索引，实际用到复合索引。

4. bc 的组合情况下

explain select * from test where bid='b01' and cid='c01'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test
   partitions: NULL
         type: index
possible_keys: NULL
          key: abc
      key_len: 186
          ref: NULL
         rows: 3
     filtered: 33.33
        Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)

ERROR:
No query specified

可能用到的索引 ：无 ，实际用到复合索引。