今天主要就测试了一下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一个。
