常见的索引面试题

select * from test03;
show index from test03;

create index idx_test03_c1234  on test03(c1, c2, c3, c4);
--  遵守最佳左前缀
explain  select * from test03 where c1 = 'a1' and c2 = 'a2' and c3 = 'a3' and c4 = 'a4'; 
-- 因为mysql 优化查询器做了优化。
explain  select * from test03 where c1 = 'a1' and c2 = 'a2'  and c4 = 'a4'  and c3 = 'a3'; 

-- 因为mysql 查询优化器做了优化。
explain  select * from test03 where c4 = 'a4'  and c3 = 'a3' and c1 = 'a1' and c2 = 'a2'  ; 
--  建议遵守最佳左前缀法则， 减少mysql 底层的优化


--  范围之后全失效， 所以第三个字段之后就失效了，还导致了type 变成了range（第三个字段只作用于了排序， 没作用于查询）
explain  select * from test03 where c1 = 'a1' and c2 = 'a2' and c3 > 'a3' and c4 = 'a4'; 
--  范围之后全失效， 因为mysql  做了调优， 查询优化器将 c3 ,c4 做了互换， 因为c4是最后一个了，所以是四个字段都用到了，只是type成了range
explain  select * from test03 where c1 = 'a1' and c2 = 'a2' and   c4 = 'a4' and c3 > 'a3'; 


-- c3  只用于了排序， 没在查询中用到， 所以不统计在keylen 中
explain  select * from test03 where c1 = 'a1' and c2 = 'a2'  and c4 = 'a4'  order by  c3 ; 



--  索引中间断开了，并要按照断开后的字段进行排序，mysql 自身就会进行一次文件内排序，导致性能变差
explain select * from test03 where c1 = 'a1' and c2 = 'a2' order by   c4 ; 


-- 排序的时候按照索引建立的顺序， 正常使用
explain select * from test03 where c1 = 'a1' and c2 = 'a2' order by   c3, c4 ; 
-- 排序的时候没有按照索引建立的顺序， 就会出现filesort 
explain select * from test03 where c1 = 'a1' and c2 = 'a2' order by   c4, c3 ; 



-- Order by 后面的字段如果顺序和索引建立的顺序 不一样,一般都会产生filesort, 但是有一种情况不会,
-- 就是order by 后面的字段已经作为常量出现了, 常量就只有一个, 就不会产生filesort 
explain select * from test03 where c1 = 'a1' and c2 = 'a2' order by   c3, c2; 

-- Group by 和order by 一样, 都能做排序,只是group by 是分组后排序, group by 会产生临时表. 排序的字段如果和索引所建的字段不一致,也是会产生filesort 
explain select * from test03 where c1 = 'a1' and c2 = 'a2' GROUP BY   c4, c3; 

-- 分组后的排序，如果有常量的话， 即使排序的位置和索引所建立的不一样, 也不会产生filesort 
explain select * from test03 where c1 = 'a1' and c2 = 'a2' GROUP BY   c3, c2;