假设某个表有一个联合索引（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
create table t4 (
c1 tinyint(1) not null default 0,
c2 tinyint(1) not null default 0,
c3 tinyint(1) not null default 0,
c4 tinyint(1) not null default 0,
c5 tinyint(1) not null default 0,
index c1234(c1,c2,c3,c4)
);
insert into t4 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都能用上. 如下:
mysql> explain select * from t4 where c1=1 and c2=2 and c4>3 and c3=3 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t4
type: range
possible_keys: c1234
key: c1234
key_len: 4 #可以看出c1,c2,c3,c4索引都用上
ref: NULL
rows: 1
Extra: Using where
对于B: select * from t4 where c1=1 and c2=2 and c4=3 order by c3
c1 ,c2索引用上了,在c2用到索引的基础上,c3是排好序的,因此不用额外排序.
而c4没发挥作用.
mysql> explain select * from t4 where c1=1 and c2=2 and c4=3 order by c3 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t4
type: ref
possible_keys: c1234
key: c1234
key_len: 2
ref: const,const
rows: 1
Extra: Using where
1 row in set (0.00 sec)
mysql> explain select * from t4 where c1=1 and c2=2 and c4=3 order by c5 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t4
type: ref
possible_keys: c1234
key: c1234
key_len: 2
ref: const,const
rows: 1
Extra: Using where; Using filesort
1 row in set (0.00 sec)
对于 C: 只用到c1索引,因为group by c3,c2的顺序无法利用c2,c3索引
mysql> explain select * from t4 where c1=1 and c4=2 group by c3,c2 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t4
type: ref
possible_keys: c1234
key: c1234
key_len: 1 #只用到c1,因为先用c3后用c2分组,导致c2,c3索引没发挥作用
ref: const
rows: 1
Extra: Using where; Using temporary; Using filesort
1 row in set (0.00 sec)
mysql> explain select * from t4 where c1=1 and c4=2 group by c2,c3 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t4
type: ref
possible_keys: c1234
key: c1234
key_len: 1
ref: const
rows: 1
Extra: Using where
1 row in set (0.00 sec)
D语句: C1确定的基础上,c2是有序的,C2之下C3是有序的,因此c2,c3发挥的排序的作用.
因此,没用到filesort
mysql> explain select * from t4 where c1=1 and c5=2 order by c2,c3 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t4
type: ref
possible_keys: c1234
key: c1234
key_len: 1
ref: const
rows: 1
Extra: Using where
1 row in set (0.00 sec)
E: 这一句等价与 elect * from t4 where c1=1 and c2=3 and c5=2 order by c3;
因为c2的值既是固定的,参与排序时并不考虑
mysql> explain select * from t4 where c1=1 and c2=3 and c5=2 order by c2,c3 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t4
type: ref
possible_keys: c1234
key: c1234
key_len: 2
ref: const,const
rows: 1
Extra: Using where
1 row in set (0.00 sec)
一道面试题:
有商品表, 有主键,goods_id, 栏目列 cat_id, 价格price
说:在价格列上已经加了索引,但按价格查询还是很慢,
问可能是什么原因,怎么解决?
答: 在实际场景中,一个电商网站的商品分类很多,直接在所有商品中,按价格查商品,是极少的,一般客户都来到分类下,然后再查.
改正: 去掉单独的Price列的索引, 加 (cat_id,price)复合索引
再查询.