MySQL高级之索引面试题分析

索引优化简单案例

单表

 

需求：查询category_id为1 且 comments大于1 的情况下，views最多的id

1、无索引的情况下： 

很显然，type是ALL，即最坏的情况，Extra还出现了Using filesort也是最坏的情况，必须优化

 

2、优化一：where条件全部建索引 

 

复合索引中的使用到的“comments > 1”是一个范围检索，带来的好处是将type提升为range，只需检索部分索引，但却导致mysql无法利用索引再对后面的views部分进行检索，即导致后面views索引的失效，“ORDER BY views DESC”不得不进行文件排序

 

补充： 

BTree索引的工作原理：先排序category_id，如果遇到相同的category_id则再排序comments，如果遇到相同的comments，则再排序views。

 

3、优化二：删除失效的索引 

 

两表

 

 

 

需求：两表关联查询 

1、无索引的情况下 

 

2、优化一：左表建索引 

 

3、优化二：右表建索引 

 

由上可见，左连接查询，左表数据一定都有，不可避免的需要全表扫描，所以右表是我们优化的关键，需要建立合适的索引提高检索效率。同理，右连接左表索引是关键。

 

三表

 

 

 

 

1、无索引的情况下 

 

2、优化：右表建索引 

 

 

总结： 

1、尽可能减少join语句中的NestedLoop的循环总次数，永远用小表（小的结果集）驱动大表（大的结果集） 

2、优先优化NestedLoop的内层循环 

3、保证join语句中被驱动的表上Join条件字段已经别索引

 

索引面试题分析

数据准备： 

 

问题：创建了复合索引idx_test03_c1234，根据以下sql分析索引使用情况？ 

 

 

 

1、sql字段顺序变化 

mysql查询优化器会自动对sql语句进行优化，最终都会优化成符合索引顺序的sql也就是第一条sql的样子。

 

2、范围之后全失效 + sql字段顺序变化 

第一条sql由于c3范围查找，导致后面的c4索引失效 

第二条sql由于mysql查询优化器的优化，c3被优化到c4前面，索引都没有失效

 

３、order by 单列 

 

4、order by 多列 

 

 

c2已经按常量检索了，值是固定的，再排序就没有意义了，所以不会产生文件排序。

 

5、group by 

group by之前基本上都需要排序，如果没有排序，排序时还会产生零时表。