MySQL之SQL分析三部曲实际案例(五)--临时表,优化器的选择

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,高可用系列 2核4GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: -------------------------------------------------------------------------------------------------正文-----------------------------------...
-------------------------------------------------------------------------------------------------正文---------------------------------------------------------------------------------------------------------------
问题出现于生产环境,为了方便表述,在测试环境构造了类似的场景

所有操作都是基于MySQL-5.6.26下进行的,并在MySQL-5.7.7-rc下进行了对比试验

背景:有用户在抱怨生产系统上,某一个Web的详情页面太慢,忍无可忍
问题分析过程:略
问题聚焦:最终确定是某一个SQL语句太慢,查询时间用了10s(慢查询日志给出的信息)
出现这种语句的原因:开发人员使用框架,利用模板统一生成的SQL

出问题的SQL语句(同类型构造,简化版,代码插入用不了,直接贴上来了_(:з」∠)_)
select t3.* 
from (select t1.*,t2.gname from students t1 inner join grade t2 on t1.sid=t2.f_sid) t3 
where t3.sid > 10005;
相关表的具体数据:
student

grade


分析:这个语句简化以后,优化策略很明显,把外围的select *去掉,然后t3的选择条件下推到里面的join里面去,其实就好了;
对应在优化器的语法优化策略中, 就是把投影条件(t3.*)和选择条件(t3.sid>10005)下推,一直推到对应的表上面,然后再把嵌套子查询上提,去掉最外层的select *;
但是为何实际上的SQL执行速度这么慢呢?看一下MySQL给出的执行计划

可以看到优化器并没有如想象中的方式去改写语句,那么看看trace里面的内容, 稍微观察一下优化器提供的一些信息
这里截取部分信息(对trace内容的分析仅限于个人的理解,如有偏差之处,希望能指出,学无止境 _(:з」∠)_ )
在join的准备阶段,优化器把这个SQL解析成为了两个部分,最外层的查询:select #1,和内层的查询select #2

在join的优化阶段,优化器没有下推t3.sid > 10005的条件,而是作为了临时表t3的查询条件保留了下来,


从而使得这个查询以一个比较糟糕的方式在执行,
而根据之前的分析,稍微改写一下这个语句:
select t1.*,t2.gname
from students t1
inner join grade t2 on t1.sid=t2.f_sid 

where t1.sid > 10005;
然后看看实际的执行计划

最终的执行结果,和之前的语句也是一致的;

结论:所以如果生产库上出现这种语句,使用5.6.26或者5.6.26之前的版本时,优化器并不会去改写这种类型语句,需要DBA或者开发自行改写。
如果使用了模板自动生成的话,会比较麻烦,需要和开发好好深入的沟通一下了....._(:з」∠)_

那么问题来了,MySQL的优化器真的这么烂,就没救了么?
一起看看“对优化器有多项重大优化和改进”的MySQL-5.7.7-rc的表现

从执行计划来看,在MySQL-5.7.7-rc中,优化器对这种语句进行了改写,那么看一下trace里面的内容
在最开始的join的准备阶段,优化器合并了t3

在join的优化阶段,最外围的t3条件已经下推到了t1表中


看样子MySQL-5.7.7-rc确实是对优化器做了一些改进~MySQL的5.7版本的还是值得期待的~
-------------------------------------------------------------------------------------------------结尾---------------------------------------------------------------------------------------------------------------
PS:只是一些个人的看法,模板和框架确实极大的简化了开发的工作,多人协作/代码review方面也确实便利了很多,但是感觉框架和模板终归还是定死了一些条条框框的东西,始终还是缺乏一些灵活性
也有可能只是我所见的开发并没有用好这些工具,也不一定
相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发,帮助IT从业者们通过“阿里云Web应用上云解决方案”,了解一个企业级Web应用上云的常见架构,了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。   相关的阿里云产品:云数据库RDS MySQL 版 阿里云关系型数据库RDS(Relational Database Service)是一种稳定可靠、可弹性伸缩的在线数据库服务,提供容灾、备份、恢复、迁移等方面的全套解决方案,彻底解决数据库运维的烦恼。 了解产品详情: https://www.aliyun.com/product/rds/mysql 
目录
相关文章
|
10天前
|
缓存 关系型数据库 BI
使用MYSQL Report分析数据库性能(下)
使用MYSQL Report分析数据库性能
45 3
|
10天前
|
缓存 监控 关系型数据库
使用MYSQL Report分析数据库性能(上)
最终建议:当前系统是完美的读密集型负载模型,优化重点应放在减少行读取量和提高数据定位效率。通过索引优化、分区策略和内存缓存,预期可降低30%的CPU负载,同时保持100%的缓冲池命中率。建议每百万次查询后刷新统计信息以持续优化
62 6
|
10天前
|
缓存 监控 关系型数据库
使用MYSQL Report分析数据库性能(中)
使用MYSQL Report分析数据库性能
57 1
|
1月前
|
存储 关系型数据库 MySQL
深入理解MySQL索引类型及其应用场景分析。
通过以上介绍可以看出各类MySQL指标各自拥有明显利弊与最佳实践情墁,在实际业务处理过程中选择正确型号极其重要以确保系统运作流畅而稳健。
111 12
|
2月前
|
存储 SQL 关系型数据库
MySQL的Redo Log与Binlog机制对照分析
通过合理的配置和细致的管理,这两种日志机制相互配合,能够有效地提升MySQL数据库的可靠性和稳定性。
114 10
|
2月前
|
SQL 关系型数据库 MySQL
MySQL group by 底层原理详解。group by 执行 慢 原因深度分析。(图解+秒懂+史上最全)
MySQL group by 底层原理详解。group by 执行 慢 原因深度分析。(图解+秒懂+史上最全)
MySQL group by 底层原理详解。group by 执行 慢 原因深度分析。(图解+秒懂+史上最全)
|
5月前
|
SQL 关系型数据库 MySQL
【MySQL】SQL分析的几种方法
以上就是SQL分析的几种方法。需要注意的是,这些方法并不是孤立的,而是相互关联的。在实际的SQL分析中,我们通常需要结合使用这些方法,才能找出最佳的优化策略。同时,SQL分析也需要对数据库管理系统,数据,业务需求有深入的理解,这需要时间和经验的积累。
169 12
|
4月前
|
缓存 JSON 关系型数据库
MySQL 查询优化分析 - 常用分析方法
本文介绍了MySQL查询优化分析的常用方法EXPLAIN、Optimizer Trace、Profiling和常用监控指标。
|
6月前
|
关系型数据库 MySQL OLAP
无缝集成 MySQL,解锁秒级 OLAP 分析性能极限,完成任务可领取三合一数据线!
通过 AnalyticDB MySQL 版、DMS、DTS 和 RDS MySQL 版协同工作,解决大规模业务数据统计难题,参与活动完成任务即可领取三合一数据线(限量200个),还有机会抽取蓝牙音箱大奖!
|
8月前
|
关系型数据库 MySQL 数据库
mysql慢查询每日汇报与分析
通过启用慢查询日志、提取和分析慢查询日志,可以有效识别和优化数据库中的性能瓶颈。结合适当的自动化工具和优化措施,可以显著提高MySQL数据库的性能和稳定性。希望本文的详解和示例能够为数据库管理人员提供有价值的参考,帮助实现高效的数据库管理。
210 11

推荐镜像

更多