今天主要就测试了一下mysql最左前缀。mysql版本 5.7
create table student
(
id int auto_increment
primary key,
name varchar(10) null,
age int null,
address varchar(10) null,
parent varchar(10) null,
result int null
);
name,age,address做联合索引。
explain select *
from test.student
where name = '1'
结果type ref
explain select *
from test.student
where name = '1'
and age = 21
结果type ref
explain select *
from test.student
where name = '1'
and parent = 'ewrqweqe'
结果type ref
explain select *
from test.student
where parent = 'ewrqweqe'
and name = '1'
结果type ref
explain select *
from test.student
where parent = 'ewrqweqe'
and age = '21'
结果type all
explain select *
from test.student
where name = '1'
and age = '21'
and parent = 'qwe'
结果type ref
explain select *
from test.student
where name = '1'
and age = '21'
and result = 2
结果type ref
explain select *
from test.student
where name = '1'
and age = '21'
and parent = 'qwe'
结果type ref
explain select *
from test.student
where name = '1'
and address = 'ewrqweqe'
and parent = 'qwe'
结果type ref
总结
name,age,address联合索引。
如果一个也不出现,那么全表。如果出现name其他的不出现,也会走索引。如果不出现name,那么肯定会走全表。有了
name,age和address出现是否都会走索引。
我的理解是这样的上面的索引生成(name),(name,age),(name,age,address)。只有有存在name,那么就会走name这个索引,其他并不能走索引,但是结果显示这算走了索引。如果你走了name,address,result,那么索引也只会走name一个。