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 用不到索引
mysql 复合索引(联合索引) a b c的使用
最新推荐文章于 2023-06-07 18:48:55 发布