Mysql---索引命中

 

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

　　　　1.1、最左前缀原则

?

1 2 3 4 5 6 7 8 9 10 11

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

 

?

1 2 3 4 5 6 7

explain select * from test_demo where job ='' and  company=''   结果：type：all   explain select * from test_demo where company='' and job=''   结果：type：all

 

?

1 2 3 4 5 6 7

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

 

?

1 2 3 4 5 6 7 8 9 10 11

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不会命中索引

?

1 2 3

explain select * from test_demo where sex like '%%'   结果：type:all

 　　　　1.3、or命中索引

?

1 2 3

explain select * from test_demo where sex='' or sex =''   结果:type : ref、key：idx

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

?

1 2 3 4 5 6 7

explain select * from test_demo where sex!=''   结果：type:all   explain select * from test_demo where sex<>''   结果：type:all