mysql索引之验证最左匹配原则

在8.0的mysql数据库中进行验证

select * from student;

show index from student;

create index index_stu_snameSageSex on student(sname,Sage,Ssex);
commit;

-- 火车头
select * from student where Sname='吴兰';
explain select * from student where Sname='吴兰';

-- 中间的兄弟
select * from student where Sname='吴兰' and Sage='1992-03-01';
explain select * from student where Sname='吴兰' and Sage='1992-03-01';

-- 老末
select * from student where Sname='吴兰' and Sage='1992-03-01' and Ssex='女';
explain select * from student where Sname='吴兰' and Sage='1992-03-01' and Ssex='女';

-- 去了火车头
select * from student where  Sage='1992-03-01' and Ssex='女';
explain select * from student where Sage='1992-03-01' and Ssex='女';

select * from student where  Sage='1992-03-01' and Ssex='女';
explain select * from student where Sage='1992-03-01' and Ssex='女';

select * from student where  Sage='1992-03-01' and Ssex='女';
explain select * from student where Ssex='女';


select * from student where  Sage='1992-03-01';
explain select * from student where Sage='1992-03-01' ;

结论：在8.0的数据库中，验证索引失效过程中发现，如果按照生成索引的规则进行查询数据。这时候数据查询type可以达到ref级别。

如果打断生成索引时的顺序的时候，依然会使用索引，但是这个时候type为index