hongyu`s blog

联合索引

发表于

1.建表

CREATE TABLE abc (
id int(11) NOT NULL AUTO_INCREMENT,
a int(11) DEFAULT NULL,
b int(11) DEFAULT NULL,
c int(11) DEFAULT NULL,
d varchar(255) CHARACTER SET utf8 DEFAULT NULL,
PRIMARY KEY (id),
KEY idx_a_b_c (a,b,c) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2.插入10000条数据

for (int x = 0; x < 100000; x++) {
Abc abc = new Abc();
abc.setA(x);
abc.setB(x);
abc.setC(x);
abc.setD(x+””);
abcMapper.insert(abc);
}

3.
EXPLAIN select * from abc where a = 9876 and b = 9876 and c = 9876;

EXPLAIN select * from abc where a = 9876 and b = 9876 and d = ‘8’;

EXPLAIN select * from abc where a = 9876 and b >= 9876 and c = 99;

EXPLAIN select * from abc where a = 9876 and b > 9876 and c = 99;

EXPLAIN select * from abc where a = 9876 and b BETWEEN 9876 and 9999 and c = 99;

EXPLAIN select * from abc where a = 9876 and c = 99;

EXPLAIN select * from abc where b = 9876 and c = 99;

EXPLAIN select * from abc where a = 9876 and d = ‘99’;

EXPLAIN select * from abc where a = ‘9876’ and b = 9876 and c = 9876;

EXPLAIN select * from abc where a = 9876 and b >= 9876 and d = ‘99’;