MySQL高级之索引面试题分析

java1234

共 1471字,需浏览 3分钟

 ·

2021-04-25 10:11

点击上方蓝色字体,选择“标星公众号”

优质文章,第一时间送达

76套java从入门到精通实战课程分享

索引优化简单案例

单表


需求:查询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前面,索引都没有失效


3、order by 单列 


4、order by 多列 



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


5、group by 

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

————————————————

版权声明:本文为CSDN博主「走慢一点点」的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接及本声明。

原文链接:

https://blog.csdn.net/wuseyukui/article/details/72238817






锋哥最新SpringCloud分布式电商秒杀课程发布

👇👇👇

👆长按上方微信二维码 2 秒





感谢点赞支持下哈 

浏览 16
点赞
评论
收藏
分享

手机扫一扫分享

分享
举报
评论
图片
表情
推荐
点赞
评论
收藏
分享

手机扫一扫分享

分享
举报