Mysql---索引命中

 

1、假设 test_demo表中有个 复合索引 idx(sex,company,job)

　　　　1.1、最左前缀原则

explain select * from test_demo where sex='' and job ='' and  company=''

结果：type：ref、key：idx

explain select * from test_demo where job ='' and sex='' and  company=''

结果：type：ref、key：idx

explain select * from test_demo where company='' and job ='' and sex='' 

结果：type：ref、key：idx

 

explain select * from test_demo where job ='' and  company=''

结果：type：all

explain select * from test_demo where company='' and job=''

结果：type：all

 

explain select * from test_demo where sex='' and job =''

结果：type：ref、key：idx

explain select * from test_demo where sex='' and company =''

结果：type：ref、key：idx

 

explain select * from test_demo where sex=''

结果：type：ref、key:idx    

explain select * from test_demo where job =''

结果：type：all

explain select * from test_demo where company =''

结果：type：all

 　　　　1.2、like不会命中索引

explain select * from test_demo where sex like '%%'

结果：type:all

 　　　　1.3、or命中索引

explain select * from test_demo where sex='' or sex =''

结果:type : ref、key：idx

 　　　　1.4、负向条件不会命中索引

explain select * from test_demo where sex!=''

结果：type:all 

explain select * from test_demo where sex<>''

结果：type:all