带你学MySQL系列 | “数据分析师”面试最怕被问到的SQL优化问题(下)(一)

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,高可用系列 2核4GB
简介: 带你学MySQL系列 | “数据分析师”面试最怕被问到的SQL优化问题(下)(一)

本文大纲

前面我已经带着大家学习了本文的第1-4个部分,今天就带大家学习这剩下的5-8个部分。MySQL优化问题对于新手学习,一般是个难题!我的教程自认为已经是很通俗易懂的。如果你学习了这个教程后,仍然不太理解,可以去B站找到一个视频浏览一遍,然后再回头看我的文章。

image.png


讲解使用的数据源

在上篇最后,我们已经给出了本文需要使用到的数据代码,这里我直接给出这3张表的图示。

image.png


5. explain执行计划常用关键字详解

1)id关键字的使用说明、

① 案例:查询课程编号为2 或 教师证编号为3 的老师信息;

# 查看执行计划
explain select t.*
from teacher t,course c,teacherCard tc
where t.tid = c.tid and t.tcid = tc.tcid
and (c.cid = 2 or tc.tcid = 3);


结果如下:

image.png

接着,在往teacher表中增加几条数据。


insert into teacher values(4,'ta',4);
insert into teacher values(5,'tb',5);
insert into teacher values(6,'tc',6);


再次查看执行计划。

# 查看执行计划
explain 
select 
  t.*
from 
  teacher t,course c,teacherCard tc
where 
  t.tid = c.tid and t.tcid = tc.tcid
  and (c.cid = 2 or tc.tcid = 3);


结果如下:

image.png

这里先记住一句话:表的执行顺序 ,因表数量改变而改变的原因:笛卡尔积。怎么回事呢?看看下面这个例子。


# 下面举一个例子
a   b   c
2   3   4
最终:2 * 3 * 4  = 6 * 4 = 24
c   b   a
4   3   2
最终:4 * 3 * 2 = 12 * 2 = 24


分析:最终执行的条数,虽然是一致的。但是中间过程,有一张临时表是6,一张临时表是12,很明显6 < 12,对于内存来说,数据量越小越好,因此优化器肯定会选择第一种执行顺序。


结论:id值相同,从上往下顺序执行。表的执行顺序因表数量的改变而改变,数量越小,越在前面执行。


② 案例:查询教授SQL课程的老师的描述(desc)

# 查看执行计划
explain 
select 
  tc.tcdesc 
from 
  teacherCard tc 
where tc.tcid = 
(
    select t.tcid from teacher t 
    where  t.tid =  
    (select c.tid from course c where c.cname = 'sql')
);


结果如下:

image.png

结论:id值不同,id值越大越优先查询。这是由于在进行嵌套子查询时,先查内层,再查外层。


③ 针对②做一个简单的修改

# 查看执行计划
explain 
select 
  t.tname ,tc.tcdesc 
from 
  teacher t,teacherCard tc 
where 
  t.tcid= tc.tcid
  and t.tid = (select c.tid from course c where cname = 'sql') ;


结果如下:

image.png

结论:id值有相同,又有不同。id值越大越优先;id值相同,从上往下顺序执行。


2)select_type关键字的使用说明:查询类型

select_type关键字共有如下常用的6种类型,下面我分别带着大家梳理一下,它们各自的含义。

image.png


① simple:简单查询

不包含子查询,不包含union查询。

explain select * from teacher;


结果如下:

image.png


② primary:包含子查询的主查询(最外层)

③ subquery:包含子查询的主查询(非最外层)

关于primary和subquery,我们就拿下面的这个例子进行演示。从代码中可以看到这个SQL语句是存在子查询的,换句话说,这个SQL语句包含子查询。where内层(非最外层)使用到的c表属于非最外层,因此是subquery关键字。where外层使用到了t表 和tc表,因此是primary关键字。


# 查看执行计划
explain 
select 
  t.tname ,tc.tcdesc 
from 
  teacher t,teacherCard tc 
where 
  t.tcid= tc.tcid
  and t.tid = (select c.tid from course c where cname = 'sql') ;


结果如下:

image.png

相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
9天前
|
存储 SQL 关系型数据库
MySQL进阶突击系列(03) MySQL架构原理solo九魂17环连问 | 给大厂面试官的一封信
本文介绍了MySQL架构原理、存储引擎和索引的相关知识点,涵盖查询和更新SQL的执行过程、MySQL各组件的作用、存储引擎的类型及特性、索引的建立和使用原则,以及二叉树、平衡二叉树和B树的区别。通过这些内容,帮助读者深入了解MySQL的工作机制,提高数据库管理和优化能力。
|
1月前
|
SQL 关系型数据库 MySQL
大厂面试官:聊下 MySQL 慢查询优化、索引优化?
MySQL慢查询优化、索引优化,是必知必备,大厂面试高频,本文深入详解,建议收藏。关注【mikechen的互联网架构】,10年+BAT架构经验分享。
大厂面试官:聊下 MySQL 慢查询优化、索引优化?
|
2月前
|
存储 关系型数据库 MySQL
阿里面试:为什么要索引?什么是MySQL索引?底层结构是什么?
尼恩是一位资深架构师,他在自己的读者交流群中分享了关于MySQL索引的重要知识点。索引是帮助MySQL高效获取数据的数据结构,主要作用包括显著提升查询速度、降低磁盘I/O次数、优化排序与分组操作以及提升复杂查询的性能。MySQL支持多种索引类型,如主键索引、唯一索引、普通索引、全文索引和空间数据索引。索引的底层数据结构主要是B+树,它能够有效支持范围查询和顺序遍历,同时保持高效的插入、删除和查找性能。尼恩还强调了索引的优缺点,并提供了多个面试题及其解答,帮助读者在面试中脱颖而出。相关资料可在公众号【技术自由圈】获取。
|
1月前
|
SQL 缓存 关系型数据库
美团面试:Mysql 有几级缓存? 每一级缓存,具体是什么?
在40岁老架构师尼恩的读者交流群中,近期有小伙伴因未能系统梳理MySQL缓存机制而在美团面试中失利。为此,尼恩对MySQL的缓存机制进行了系统化梳理,包括一级缓存(InnoDB缓存)和二级缓存(查询缓存)。同时,他还将这些知识点整理进《尼恩Java面试宝典PDF》V175版本,帮助大家提升技术水平,顺利通过面试。更多技术资料请关注公号【技术自由圈】。
美团面试:Mysql 有几级缓存? 每一级缓存,具体是什么?
|
1月前
|
SQL 算法 关系型数据库
面试:什么是死锁,如何避免或解决死锁;MySQL中的死锁现象,MySQL死锁如何解决
面试:什么是死锁,死锁产生的四个必要条件,如何避免或解决死锁;数据库锁,锁分类,控制事务;MySQL中的死锁现象,MySQL死锁如何解决
|
1月前
|
SQL 关系型数据库 MySQL
美团面试:Mysql如何选择最优 执行计划,为什么?
在40岁老架构师尼恩的读者交流群中,近期有小伙伴面试美团时遇到了关于MySQL执行计划的面试题:“MySQL如何选择最优执行计划,为什么?”由于缺乏系统化的准备,小伙伴未能给出满意的答案,面试失败。为此,尼恩为大家系统化地梳理了MySQL执行计划的相关知识,帮助大家提升技术水平,展示“技术肌肉”,让面试官“爱到不能自已”。相关内容已收录进《尼恩Java面试宝典PDF》V175版本,供大家参考学习。
|
2月前
|
SQL 关系型数据库 MySQL
阿里面试:MYSQL 事务ACID,底层原理是什么? 具体是如何实现的?
尼恩,一位40岁的资深架构师,通过其丰富的经验和深厚的技術功底,为众多读者提供了宝贵的面试指导和技术分享。在他的读者交流群中,许多小伙伴获得了来自一线互联网企业的面试机会,并成功应对了诸如事务ACID特性实现、MVCC等相关面试题。尼恩特别整理了这些常见面试题的系统化解答,形成了《MVCC 学习圣经:一次穿透MYSQL MVCC》PDF文档,旨在帮助大家在面试中展示出扎实的技术功底,提高面试成功率。此外,他还编写了《尼恩Java面试宝典》等资料,涵盖了大量面试题和答案,帮助读者全面提升技术面试的表现。这些资料不仅内容详实,而且持续更新,是求职者备战技术面试的宝贵资源。
阿里面试:MYSQL 事务ACID,底层原理是什么? 具体是如何实现的?
|
2月前
|
SQL 关系型数据库 MySQL
京东面试:什么情况下 mysql RR不能解决幻读? RR隔离mysql如何实现?
老架构师尼恩在其读者交流群中分享了关于MySQL事务隔离级别的深入解析,特别针对RR级隔离如何解决幻读问题进行了详细讨论。文章不仅解释了ACID中的隔离性概念,还列举了四种事务隔离级别(未提交读、提交读、可重复读、串行读)的特点及应用场景。尼恩通过具体的例子和图表,清晰地展示了不同隔离级别下的并发事务问题(脏读、不可重复读、幻读)及其解决方案,特别是RR级隔离下的MVCC机制如何通过快照读和当前读来防止幻读。此外,尼恩还提供了相关面试题的解答技巧和参考资料,帮助读者更好地准备技术面试。更多详细内容和实战案例可在《尼恩Java面试宝典》中找到。
|
2月前
|
SQL 关系型数据库 MySQL
美团面试:mysql 索引失效?怎么解决? (重点知识,建议收藏,读10遍+)
本文详细解析了MySQL索引失效的多种场景及解决方法,包括破坏最左匹配原则、索引覆盖原则、前缀匹配原则、`ORDER BY`排序不当、`OR`关键字使用不当、索引列上有计算或函数、使用`NOT IN`和`NOT EXISTS`不当、列的比对等。通过实例演示和`EXPLAIN`命令分析,帮助读者深入理解索引失效的原因,并提供相应的优化建议。文章还推荐了《尼恩Java面试宝典》等资源,助力面试者提升技术水平,顺利通过面试。
|
2月前
|
存储 关系型数据库 MySQL
面试官:MySQL一次到底插入多少条数据合适啊?
本文探讨了数据库插入操作的基础知识、批量插入的优势与挑战,以及如何确定合适的插入数据量。通过面试对话的形式,详细解析了单条插入与批量插入的区别,磁盘I/O、内存使用、事务大小和锁策略等关键因素。最后,结合MyBatis框架,提供了实际应用中的批量插入策略和优化建议。希望读者不仅能掌握技术细节,还能理解背后的原理,从而更好地优化数据库性能。

推荐镜像

更多