在工作上遇到过索引的坑，就此看看联合索引在不同情况下是否会生效

创建测试表

CREATE TABLE `test` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  `c` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_a_b_c` (`a`,`b`,`c`) USING BTREE
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

插入数据

INSERT INTO `blog`.`test`(`a`, `b`, `c`) VALUES ('1', '1', '1')

explain分析语句

先分析常规的最左原则：A，AB,ABC,AC  

A

explain select * from test where a = 1;

AB

explain select * from test where a = 1 and b = 2;

ABC

explain select * from test where a = 1 and b = 2 and c = 3;

AC

explain select * from test where a = 1 and c = 3;

后面看一下其他情况

以C开头的

CBA 

explain select * from test where a = 1 and b = 2 and c = 3;

CA

explain select * from test where c = 1 and a = 3;

CB

explain select * from test where c = 1 and b = 3;

C

explain select * from test where c = 1 ;

CAB

explain select * from test where c = 1 and a = 2 and b = 3;

现在来看看 以B开头的

B 

explain select * from test where b = 1 ;

BA

explain select * from test where b = 1 and a = 1;

BC

explain select * from test where b = 1 and c = 1;

BAC

explain select * from test where b = 1 and a = 1 and c = 1;

BCA

explain select * from test where b = 1 and c = 1 and a = 1;

BC

explain select * from test where b = 1 and c = 1;

根据explain分析里面的参数，发现都是有走索引，主要区别是 type的类型是ref还是index,其中 ref 的效果会好点，但总体来说比type=all全表扫描效果好 

很赞哦！(3)

本文阅读量 2435‧发布于 2020年5月8日

您的访问IP 3.15.197.49‧最早于 2024年5月14日 23时02分50秒 阅读过本文 ‧ 为本文提供了 1 热度 1 阅读量