mysql复合索引(联合索引)abc的使用

OStack程序员社区-中国程序员成长平台 › 门户 › 编程› C++›C++教程

原作者: [db:作者] 来自: [db:来源] 收藏邀请

本博文中所用数据版本为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 的情况下不会用到索引

含a的都可以用到索引，优化器自动调整顺序，不含a的用不到索引。

实际结果如下：
数据库版本如上文 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 四种组合结果

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

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

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

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

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

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

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

结论：
复合索引（abc）所有情况的罗列，最根本的还是最左原则
abc 能用到索引
bca 能用到索引
cab 能用到索引
ab 能用到索引
ac 能用到索引
ba 能用到索引
ca 能用到索引
bc 用不到索引
b 用不到索引
c 用不到索引
cb 用不到索引
————————————————

原文链接：https://blog.csdn.net/liuqun0319/article/details/103730224

本博文中所用数据版本为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 的情况下不会用到索引

含a的都可以用到索引，优化器自动调整顺序，不含a的用不到索引。

实际结果如下：
数据库版本如上文 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 四种组合结果

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

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

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

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

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

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

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