联合索引特殊案例

CREATE TABLE t(

     c1 varchar(10) not null,

    c2 varchar(10) not null,

    c3 varchar(10) not null,

    c4 varchar(10) not null,

    c5 varchar(10) not null

)ENGINE InnoDB CHARSET UTF8;

    

   alter table t add index c1234(c1,c2,c3,c4);

   

 insert into t VALUES('1','1','1','1','1'),('2','2','2','2','2'),('3','3','3','3','3'),('4','4','4','4','4'),('5','5','5','5','5');

案例1：不影响索引走四个

explain select * from t where c1 like '3' and c2 = '1' and c3 = '1' and c4 = '1';

 案例2：between也不影响走四个

explain select * from t where c1 between '1' and '3' and c2 = '1' and c3 = '1' and c4 = '1';

 

 

 案例3：走一个 c1索引

explain select * from t where c1 > '3' and c2 = '1' and c3 = '1' and c4 = '1';

案例4：走c1,c2两个索引

explain select * from t where c1 = '1' and c2 > '1' and c3 = '1' and c4 = '1';