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

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;

INSERT INTO test VALUES('a01','b01','c01');
INSERT INTO test VALUES('a02','b02','c02');
INSERT INTO test VALUES('a03','b03','c03');

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



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

#abc 能用到索引
explain select * from test where aid='a01' and bid='b01' and cid='c01';

#bca能用到索引
explain select * from test where bid='b01' and cid='c01'and aid='a01';

#cab能用到索引
explain select * from test where  cid='c01'and aid='a01' and bid='b01';

#ab 能用到索引
explain select * from test where aid='a01' and bid='b01';

#ac 能用到索引
explain select * from test where aid='a01'  and cid='c01';
#ba 能用到索引
explain select * from test where bid='b01'  and aid='a01';
#ca 能用到索引
explain select * from test where cid='c01' and aid='a01';
#bc 不能用到索引
explain select * from test where bid='b01' and cid='c01';

#b 用不到索引
explain select * from test where bid='b01';

#c 用不到索引
explain select * from test where cid='c01';

#cb 用不到索引
explain select * from test where cid='c01' and bid='b01';





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