(Oracle)SQL优化案例:隐式转换优化

项目场景

项目现场的某个kettle模型执行非常缓慢,原因在于某个SQL执行效率非常的低。甲方得知此事要求公司赶紧优化,负责该模块的同事对SQL优化并不熟悉。所以作为一个立志成为优秀DBA的ETL工程师,我自告奋勇:不是DBA,就不能搞SQL优化了吗?

查询效率慢的SQL格式如下,因为涉及到项目隐私,所以对表名、字段名做了匿名化处理。

SELECT
INFO.ID,
INFO.NAME,
INFO.AGE,
INFO.SEX,
MP.ADDR,
MP.PHONE
FROM INFORMATION INFO
LEFT JOIN MP ON MP.ID = INFO.ID
WHERE INFO.ID = 1234567

很简单的一个SQL。每次查询都是按照 INFO.ID = xxx 这个条件进行查询,每次只会返回1行数据。且关联字段 MP.ID 与 INFO.ID 都有索引,没有大字段。但每次执行查询,大概都要耗时2s左右;不要觉得这个耗时很短,因为有大量的数据需要查询出来进行同步,这个速度就不可被接受了。 

两张表的数据量如下

SELECT COUNT(*) FROM INFORMATION --3667874
SELECT COUNT(*) FROM MP --2125263

问题分析

以下是上述SQL格式的执行计划,是项目上真实SQL的执行计划。我已经将对此次优化的无关执行计划信息删除,只保留了执行计划和谓词信息;且将涉及项目隐私部分的表名字段名改写

Plan hash value: 3295416379
 
-------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                   | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                        |      1 |        |       | 29513 (100)|          |      1 |00:00:01.20 |     108K|
|   1 |  NESTED LOOPS OUTER          |                        |      1 |      1 |   115 | 29513   (1)| 00:00:02 |      1 |00:00:01.20 |     108K|
|   2 |   TABLE ACCESS BY INDEX ROWID| INFORMATION            |      1 |      1 |    98 |     3   (0)| 00:00:01 |      1 |00:00:00.01 |       4 |
|*  3 |    INDEX UNIQUE SCAN         | UK_20230901211918_5341 |      1 |      1 |       |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |
|*  4 |   TABLE ACCESS FULL          | MP                     |      1 |      1 |    17 | 29510   (1)| 00:00:02 |      1 |00:00:01.20 |     108K|
-------------------------------------------------------------------------------------------------------------------------------------------------
 

Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("A"."ID"=1234567)
   4 - filter((TO_NUMBER("MP"."ID")=1234567 AND "A"."ID"=TO_NUMBER("MP"."ID")))

通过执行计划我们可以分析出:

  • 连接方式 (执行计划:Id=1)

MP表和INFO表的连接方式是 NESTED LOOPS OUTER;因为此查询返回数据量少,每次执行只会返回1行数据,所以走嵌套循环连接是正确的。

  • 访问路径 (执行计划:Id=2)

INFO.ID=1234567 这个谓词过滤是走了索引ROWID扫描的;返回数据量少,走索引,也是正确的。

  • 访问路径(执行计划:Id=4) 

对MP表的访问路径是TABLE ACCESS FULL(全表扫描)。这个就有问题了,MP表有上百万条数据,走全表扫描肯定是低效的、错误的。MP表上的ID字段是有索引的,为什么没有走索引呢?我们继续往下看。

  • 谓词信息 

从谓词信息我们可以看到MP表在进行谓词过滤时,将MP表的ID字段从varchar类型的值通过to_number()转换成了number类型;因为使用了to_number()函数,索引索引失效。此转换属于是Oracle在比对不同数据类型的字段或者表达式时,自动发生的隐式转换;隐式转换的目的肯定是好的,但是在此处对SQL查询效率影响可太大了。

那么对于这个SQL优化而言,需要做的其实就是消除隐式转换带来的影响。那怎么消除嘞?大家继续看下面的SQL改写。

我不晓得会不会有朋友疑问,为什么发生隐式转换的是MP.ID 而不是 INFOMATION.ID。

我在这里多解释一句:

可以从执行计划中看出来,

距离NESTED LOOPS OUTER这个连接方式关键字下方最近的一张表就是驱动表。

这也就意味着,驱动表INFOMATION传一个number类型的数据给MP表,那么MP表必须对自己的字段进行转换才可以进行等值匹配。

SQL改写 

 

这是改写后的SQL,现在查询只需要0.05左右,速度提高了几十倍。完全可以满足ETL要求了。

SELECT
INFO2.ID,
INFO2.NAME,
INFO2.AGE,
INFO2.SEX,
MP.ADDR,
MP.PHONE
FROM (SELECT CAST(INFO.ID AS VARCHAR2(10) AS ID,INFO.NAME,INFO.AGE,INFO.SEX FROM INFORMATION INFO WHERE INFO.ID = 1234567) INFO2
LEFT JOIN MP ON MP.ID = INFO2.ID

 该SQL的执行计划如下,同样的,为了隐私表名和字段名我做了匿名化处理。

Plan hash value: 3589640507
 
---------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                           | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                                |      1 |        |       |     9 (100)|          |      1 |00:00:00.01 |       8 |
|   1 |  NESTED LOOPS OUTER          |                                |      1 |      1 |   119 |     5   (0)| 00:00:01 |      1 |00:00:00.01 |       8 |
|   2 |   TABLE ACCESS BY INDEX ROWID| INFORMATION                    |      1 |      1 |    98 |     3   (0)| 00:00:01 |      1 |00:00:00.01 |       4 |
|*  3 |    INDEX UNIQUE SCAN         | UK_20230901211918_5341         |      1 |      1 |       |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |
|   4 |   TABLE ACCESS BY INDEX ROWID| MP                             |      1 |      1 |    21 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       4 |
|*  5 |    INDEX UNIQUE SCAN         | UK_20230901210612_192177       |      1 |      1 |       |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |
---------------------------------------------------------------------------------------------------------------------------------------------------------

 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("INFO"."ID"=1234567)
   5 - access("MP"."PID"=CAST("INFO"."ID" AS VARCHAR2(20)))

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:/a/540989.html

如若内容造成侵权/违法违规/事实不符,请联系我们进行投诉反馈qq邮箱809451989@qq.com,一经查实,立即删除!

相关文章

两步解决 Flutter Your project requires a newer version of the Kotlin Gradle plugin

在开发Flutter项目的时候,遇到这个问题Flutter Your project requires a newer version of the Kotlin Gradle plugin 解决方案分两步: 1、在android/build.gradle里配置最新版本的kotlin 根据提示的kotlin官方网站搜到了Kotlin的最新版本是1.9.23,如下图所示: 同时在Ko…

人事|基于SpringBoot+vue的人事管理系统设计与实现(源码+数据库+文档)

人事管理系统目录 基于SpringBootvue的人事管理系统设计与实现 一、前言 二、系统设计 三、系统功能设计 四、数据库设计 五、核心代码 六、论文参考 七、最新计算机毕设选题推荐 八、源码获取: 博主介绍:✌️大厂码农|毕设布道师,…

链表中常见的使用方法逻辑整理

文章目录 1. 链表特点2. 链表创建3. 链表遍历通用方法3.1 在链表的开头添加元素3.2 在链表的结尾添加元素3.3 删除链表的第一个元素3.4 删除链表的最后一个元素3.5 遍历链表3.6 查找链表中的元素3.7 反转链表 4. 常见面试题4.1 相交链表4.2 反转链表4.3 环形链表4.4 环形链表 I…

Nacos-默认token.secret.key-配置不当权限绕过漏洞复现

漏洞描述: Nacos 身份认证绕过漏洞(QVD-2023-6271),开源服务管理平台 Nacos在默认配置下未对 token.secret.key 进行修改,导致远程攻击者可以绕过密钥认证进入后台,造成系统受控等后果。 漏洞信息 公开时间:2023-03…

Windows安装MongoDB结合内网穿透轻松实现公网访问本地数据库

💝💝💝欢迎来到我的博客,很高兴能够在这里和您见面!希望您在这里可以感受到一份轻松愉快的氛围,不仅可以获得有趣的内容和知识,也可以畅所欲言、分享您的想法和见解。 推荐:kwan 的首页,持续学…

Java 基于微信小程序的校园失物招领小程序,附源码

博主介绍:✌程序员徐师兄、8年大厂程序员经历。全网粉丝15w、csdn博客专家、掘金/华为云/阿里云/InfoQ等平台优质作者、专注于Java技术领域和毕业项目实战✌ 🍅文末获取源码联系🍅 👇🏻 精彩专栏推荐订阅👇…

Java基础第十一课——类与对象(2)

由于类与对象这一部分的知识点很多,而且操作方法也有很多,所以这次将继续深入讨论一下关于类与对象中方法传参、方法重载、构造方法以及this关键字使用方面的知识。 一、方法传参 1.return关键字 return关键字作用 作用场景:方法内 作用…

一站式开源持续测试平台 MerterSphere 之测试跟踪操作详解

一、MeterSphere平台介绍 MeterSphere是一站式的开源持续测试平台,遵循 GPL v3 开源许可协议,涵盖测试跟踪、接口测试、UI 测试和性能测试等功能,全面兼容JMeter、Selenium 等主流开源标准,有效助力开发和测试团队充分利用云弹性…

NCF代码运行

keras 2.1.4 tensorflow 1.12.0 python 3.6.4 numpy 1.14.5 一、准备工作 1、安装虚拟环境 conda create -n tensorflow python3.6.4conda activate tensorflowconda install tensorflow1.12.0conda install keras2.1.4conda info2、安装相应依赖 cd Py3_Recommender-Syste…

4.2 面向对象程序设计-类的继承实验

本文仅供学习交流,严禁用于商业用途,如本文涉及侵权请及时联系将于24小时内删除 目录 1.实验内容 2.实验原理 2.1类的继承 2.2 继承的优点和缺点 2.3 继承的方式 3.实验代码 1.实验内容 创建一个父类CalcTime,在父类中依次定义用于保存…

对装饰器模式的理解

目录 一、场景二、面对场景中的新需求,我们怎么办?1、暴力法:直接修改原有的代码。2、子类继承法:既然要增强行为,那我搞一个子类,覆写不就完事了?3、装饰器模式 三、对装饰器模式的思考1、从代…

C语言-----结构体详解

前面已经向大家介绍过一点结构体的知识了,这次我们再来深度了解一下结构体。结构体是能够方便表示一个物体具有多种属性的一种结构。物体的属性可以转换为结构体中的变量。 1.结构体类型的声明 1.1 结构体的声明 struct tag {member-list;//结构体成员变量 }vari…

Shopee(虾皮)有哪些站点?

虾皮(Shopee)是东南亚地区的跨境平台,目前拥有多个站点,以下是对虾皮各站点的简要介绍: 中国台湾站:作为大多数新卖家的默认首站,台湾站没有语言障碍,运营起来更为方便,…

Java 中文官方教程 2022 版(一)

原文:docs.oracle.com/javase/tutorial/reallybigindex.html 教程:入门指南 原文:docs.oracle.com/javase/tutorial/getStarted/index.html 这个教程提供了关于开始使用 Java 编程语言的所有必要信息。 提供了 Java 技术作为一个整体的概述。…

android gradle版本无法下载

android gradle版本无法下载问题解决方法 在引入一个新的android项目的时候,通常会因为无法下载gradle版本而一直卡在同步界面,类似于下面的情况。 这是因为gradle运行时首先会检查distributionUrlhttps://services.gradle.org/distributions/gradle-5.6…

Springboot+Vue项目-基于Java+MySQL的社区团购系统(附源码+演示视频+LW)

大家好!我是程序猿老A,感谢您阅读本文,欢迎一键三连哦。 💞当前专栏:Java毕业设计 精彩专栏推荐👇🏻👇🏻👇🏻 🎀 Python毕业设计 &…

【linux篇】ubuntu安装教程

有道是工欲善其事必先利其器,在学习linux前,先得搭建好环境才能事半功倍。 1.VMware虚拟机安装 打开浏览器,可直接在搜索栏中输入VMware。

ControllerAdvice用法

ControllerAdvice用法 ControllerAdvice是一个组件注解,它允许你在一个地方处理整个应用程序控制器的异常、绑定数据和预处理请求。这意味着你不需要在每个控制器中重复相同的异常处理代码,从而使得代码更加简洁、易于管理。 主要特性 全局异常处理&a…

AI绘梦师新项目歪门邪道2.0游戏玩法,仅需拷贝,一键生成,单日盈利500

我们今天要介绍的项目是“AI绘梦师新项目歪门邪道2.0游戏玩法”。这个项目的核心是利用AI技术帮助企业将用户的梦境转化为美术作品。操作起来非常简单,只需复制用户描述的梦境内容,然后将其输入到AI绘画软件中,软件就能自动生成相应的画作。 …

mysql索引与优化问题

作为一个java程序员,mysql数据库面试应该是比较多的了;而关于数据库的面试,最多的就是性能问题,而以性能为起点,延伸出很多具体的问题。 我们使用第一性原理的方法来分析,为什么面试中一定会问数据库的索引…