数据库优化 - 索引 - 1

假设某个表有一个联合索引（c1,c2,c3,c4）一下——只能使用该联合索引的c1,c2,c3部分

A where c1=x and c2=x and c4>x and c3=x

B where c1=x and c2=x and c4=x order by c3

C where c1=x and c4= x group by c3,c2

D where c1=x and c5=x order by c2,c3

E where c1=x and c2=x and c5=? order by c2,c3

创建表，插入数据

USE youhua
CREATE table t1(
    c1 tinyint not null default 0,
    c2 tinyint not null default 0,
    c3 tinyint not null default 0,
    c4 tinyint not null default 0,
    c5 tinyint not null default 0,
);
CREATE index c1234
on t1(c1,c2,c3,c4);

USE youhua
INSERT INTO t1 VALUES(1,3,5,6,7),(2,3,9,8,3),(4,3,2,7,5);

对于A:

c1=x and c2=x and c4>x and c3=x  <==等价==> c1=x and c2=x and c3=x and c4>x

因此 c1,c2,c3,c4都能用上. 如下:

 

转载于:https://www.cnblogs.com/meidang/p/7445497.html