写点什么

由 SELECT * 引发的多个生产故障,问题藏太深了吧……

2020 年 9 月 29 日

由SELECT *引发的多个生产故障,问题藏太深了吧……

本文由 dbaplus 社群授权转载。


在众多的 SQL 审核产品中,几乎都会提到一个审核规则,即select *,规则描述几乎一致:禁止使用select *,必须明确选择所需的列。而这个规则其实有着很多真实的生产故障案例,下面介绍几个比较常见的案例:


案例 1


用户反馈生产环境有两条 SQL 语句,可以确认区别只有表名的不同(实际参数相同),但性能上却有 10 倍以上的差距。



通过生成的监视报告可发现 SQL1 执行时间 8s,IO 403MB,如下图:



SQL2 执行时间 2.1m,IO 则有 15GB,如下图:



根据业务反馈,SQL1 中表是影子表,数据量,表结构跟对应表几乎相同,那么为什么执行时间差距这么大呢?


DBA 在此之前已经在准生产环境多次通过DBMS_SHARED_POOL.PURGE删除对应的执行计划,换参数多次重复解析,均没获得正确的执行计划。


通过分析对比监视报告发现,SQL1 中 with 语句正常物化,执行计划中存在临时表转化操作,即TEMP TABLE TRANSFORMATION,而 SQL2 中由于没做临时表转化操作,IM_HISMESSAGE表被访问多次,效率低下。



通过再次观察整个 SQL 运行期间的等待事件,我们可以快速发现,其实 SQL 慢了 10 倍的原因并非是执行计划引起的(主要等待事件为直接路径读,读写临时表比例很低,而且并没有出现经典的大表作为 NL 被驱动表的情况),对比运行数据,可以发现 IO 的增量主要来源于对IM_HISMESSAGE表的扫描。


影子表的差异


通过逐行对比号返回列的详细信息,我们终于发现了谜底:


原始表是有 LOB 字段的,影子表没有 LOB 字段,IO 量小了很多。同时由于存在 LOB 字段,with 语句无法进行临时表转化。


而 SQL 文本中经典的 select 则完美的掩盖了这一差异,开发人员图方便写出来的 select * 查询了根本不需要的 LOB 字段,导致了性能的急剧下降。


案例 2


客户生产环境的 AWR 报告上有一条夸张的 TOPSQL,占全天 DBTIME 的 84%



原始 SQL 不展示了,SQL 本身其实比较简单,模拟下来如下:


select * from test_a where object_id =11;
复制代码


执行计划也很简单,所以很快也能发现问题,TABLE ACCESS BY INDEX ROWID的 COST 相对异常的高,排查下表的统计信息时,惊奇的发现,这是张宽表,有 400+列,当宽表遇上select *时,性能就急剧下降了。




问题定位虽然很快,但处理起来却并不方便,毕竟需要找到开发改 SQL,这快不了。当然没什么疑问的是,系统的性能问题出在 SQL 代码的质量。


案例 3


准备环境如下:从dba_objects中复制两张表 t1,t2 作为测试环境表。



准备了两个查询,相同的条件,区别主要在于一个只查单列,另外一个查询全列。


通过模拟,可以发现,use_merge这种表连接方式情况下,排序操作的内存消耗有较大的差距,这种差距会在有索引情况下,且指定查询列也能命中索引走索引快速全扫描时被大幅放大。


查询全列,SORT JOIN内存消耗 1810K:




查询 id,SORT JOIN内存消耗 424K:




如果是 HASH JOIN 的话,join 操作影响相对较小,可以换 hint 再测试看看。


案例 4


有些场景,SQL 查询的表数据量较大,查询字段也较多(无法全部走索引)的时候,这里暂时不考虑*增加的不需要使用的列在数据库返回数据到应用时网络层的消耗。


如果你的机器刚好是 EXADATA,那么smart scan也会让select *与指定列的查询有明显的性能差异。


这个限于篇幅推荐直接参考 Oracle 官方技术博客:


https://blogs.oracle.com/exadatacn/exadata-v5


总结


通过这些案例,select *这个规则,变得立体了许多。


select *写法方便快捷,但带来的问题却藏得很深,这种问题在上线后,随着系统的维护,都将变成修复成本极高的隐患。


作者介绍


蒋健,薄冰科技创始人,Oracle ACE,11g OCM,多年 Oracle 设计、管理及实施经验,精通数据库优化。


原文链接


SELECT *引发的多个生产故障,问题藏太深了吧……


2020 年 9 月 29 日 10:001006

评论

发布
暂无评论
发现更多内容

Mysql常用删除方式比较

云也退

MySQL

基于 Markdown 的中文文档排版规范

Murphy

markdown 排版规范 GitHub GFM 物联网学前班

为什么你要学习 Go?

司徒公子

go golang 编程语言 谷歌Google

产业区块链:产业是本质,区块链是工具

CECBC区块链专委会

新基建 CECBC 区块链技术 中国电子

vue-router 容易被忽视的几个地方

꯭🇫꯭

Vue vuejs vue-router router

Cassandra可调一致性的使用及原理

老任物联网杂谈

大数据 分布式 Cassandra 可调一致性

贴吧电纸书资深用户,从7个方面详谈BOOX Poke2上手体验!

DT极客

Spring Bean生命周期——初始化和销毁

xiaoxi666

Java spring

写给产品经理的信(6):时间管理

夜来妖

极客时间,项目管理 职场 产品经理 时间分配 时间管理

ARTS_20200529

凌轩

Java ARTS 打卡计划

毫无意义的人生唯有编织图案

xyz

【CSS】为什么a标签的伪类选择器要注意书写顺序?

学习委员

CSS html css3 前端 Web

大厂为什么不招30岁以上程序员,看这篇就够了

金刚小书童

职业规划 技术管理 程序员成长 程序员次第 职业成长

谈谈控制感(11):这样提升控制感,谁都能做到

史方远

心理 成长

阿里巴巴为什么让初始化集合时必须指定大小?

王磊

Java 性能

Vol.10 Java 25岁了!

Lanpeng20

Java jdk 编程语言 Java25周年

广告的发展历程

子悠

广告 计算广告 广告系统 互联网广告 RTB

如何存储1个二进制位&锁存器的核心和本质

姜海天

计算机 数字逻辑

Vite for Vue 是什么?

꯭🇫꯭

Vue vuejs vite Vue3

图片与标题的Ken Burns动效

寇云

CSS css3

终于找到了一篇文章!通俗地讲解计算机工作原理

图灵社区

cpu 存储器 编译器 计算机工作原理

k8s 上运行我们的 springboot 服务之——大文件读写

柠檬

Java nio

JUC整理笔记四之梳理VarHandle(上)

JFound

Java

平台化服务的基石:权限模型设计

孤岛旭日

企业架构 用户权限 数据建模

我们可能都误解了什么是情商

七镜花园-董一凡

情绪

关于字符编码那些你应该知道的事情

꯭🇫꯭

Java MySQL emoji utf-8 ASCII

备案问题汇总

云也退

网站 备案

Spring源码-BeanFactory创建Bean

云淡风轻

spring 源码

Django ListView DetailView等基于类的视图如何添加装饰器?

Young先生

Python django LiveView 装饰器

只需CSS的下拉式导航菜单

寇云

CSS css3

一位测试工程师的自我介绍

姬翔

测试

NLP领域的2020年大事记及2021展望

NLP领域的2020年大事记及2021展望

由SELECT *引发的多个生产故障,问题藏太深了吧……-InfoQ