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 用不到索引
