常见的索引面试题讲解

常见的索引面试题

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
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;