[ 更换 ]
热门城市
北京上海广州深圳成都杭州南京武汉天津西安重庆青岛沈阳长沙大连厦门无锡福州济南宁波昆明苏州郑州长春合肥南昌哈尔滨常州烟台南宁温州石家庄太原珠海南通扬州贵阳东莞徐州大庆佛山威海洛阳淮安呼和浩特镇江潍坊桂林中山临沂咸阳包头嘉兴惠州泉州三亚赣州九江金华泰安榆林许昌新乡舟山慈溪南阳聊城海口东营淄博漳州保定沧州丹东宜兴绍兴唐山湖州揭阳江阴营口衡阳郴州鄂尔多斯泰州义乌汕头宜昌大同鞍山湘潭盐城马鞍山襄樊长治日照常熟安庆吉林乌鲁木齐兰州秦皇岛肇庆西宁介休滨州台州廊坊邢台株洲德阳绵阳双流平顶山龙岩银川芜湖晋江连云港张家港锦州岳阳长沙县济宁邯郸江门齐齐哈尔昆山柳州绍兴县运城齐河衢州太仓张家口湛江眉山常德盘锦枣庄资阳宜宾赤峰余姚清远蚌埠宁德德州宝鸡牡丹江阜阳莆田诸暨黄石吉安延安拉萨海宁通辽黄山长乐安阳增城桐乡上虞辽阳遵义韶关泸州南平滁州温岭南充景德镇抚顺乌海荆门阳江曲靖邵阳宿迁荆州焦作丹阳丽水延吉茂名梅州渭南葫芦岛娄底滕州上饶富阳内江三明淮南孝感溧阳乐山临汾攀枝花阳泉长葛汉中四平六盘水安顺新余晋城自贡三门峡本溪防城港铁岭随州广安广元天水遂宁萍乡西双版纳绥化鹤壁湘西松原阜新酒泉张家界黔西南保山昭通河池来宾玉溪梧州鹰潭钦州云浮佳木斯克拉玛依呼伦贝尔贺州通化朝阳百色毕节贵港丽江安康德宏朔州伊犁文山楚雄嘉峪关凉山雅安西藏四川广东河北山西辽宁黑龙江江苏浙江安徽福建江西山东河南湖北湖南海南贵州云南陕西甘肃青海台湾内蒙古广西宁夏香港澳门
培训资讯网 - 为兴趣爱好者提供专业的职业培训资讯知识

一条SQL引发的“血案”:与SQL优化相关的4个案例

优化 案例 相关

导读:笔者早年间从事了多年开发工作,后因个人兴趣转做数据库。在长期的工作实践中,看到了数据库工作(特别是SQL优化)面临的种种问题。本文通过几个案例探讨一下SQL优化的相关问题。

作者:马立和 高振娇 韩锋

来源:华章科技

一条SQL引发的“血案”:与SQL优化相关的4个案例

案例01 一条SQL引发的“血案”

1. 案例说明

某大型电商公司数据仓库系统,正常情况下每天0~9点会执行大量作业,生成前一天的业务报表,供管理层分析使用。但某天早晨6点开始,监控人员就频繁收到业务报警,大批业务报表突然出现大面积延迟。原本8点前就应跑出的报表,一直持续到10点仍然没有结果。公司领导非常重视,严令在11点前必须解决问题。

DBA紧急介入处理,通过TOP命令查看到某个进程占用了大量资源,杀掉后不久还会再次出现。经与开发人员沟通,这是由于调度机制所致,非正常结束的作业会反复执行。

暂时设置该作业无效,并从脚本中排查可疑SQL。同时对比从线上收集的ASH/AWR报告,最终定位到某条SQL比较可疑。

经与开发人员确认系一新增功能,因上线紧急,只做了简单的功能测试。正是因为这一条SQL,导致整个系统运行缓慢,大量作业受到影响,修改SQL后系统恢复正常。

  • 具体分析
SELECT/*+INDEX(A1xxxxx)*/SUM(A2.CRKSL),SUM(A2.CRKSL*A2.DJ)...
FROMxxxxA2,xxxxA1
WHEREA2.CRKFLAG=xxxANDA2.CDATE>=xxxANDA2.CDATE<xxx;

这是一个很典型的两表关联语句,两张表的数据量都较大。下面来看看执行计划,如图1-1所示。

执行计划触目惊心,优化器评估返回的数据量为3505T条记录,计划返回量127P字节,总成本9890G,返回时间999:59:59。

一条SQL引发的“血案”:与SQL优化相关的4个案例

▲图1-1 执行计划

  • 分析结论

从执行计划中可见,两表关联使用了笛卡儿积的关联方式。我们知道笛卡儿连接是指两表没有任何条件限制的连接查询。一般情况下应尽量避免笛卡儿积,除非某些特殊场合,否则再强大的数据库也无法处理。

这是一个典型的多表关联缺乏连接条件,导致笛卡儿积,引发性能问题的案例。

2. 给我们的启示

从案例本身来讲并没有什么特别之处,不过是开发人员疏忽导致了一条质量很差的SQL。但从更深层次来讲,这个案例可以给我们带来如下启示。

  • 开发人员的一个疏忽造成了严重的后果,原来数据库竟是如此的脆弱。需要对数据库保持“敬畏”之心。
  • 电脑不是人脑,它不知道你的需求是什么,只能根据写好的逻辑进行处理。
  • 不要去责怪开发人员,谁都会犯错误,关键是如何从制度上保证不再发生类似的问题。

3. 解决之道

1)SQL开发规范

加强对数据库开发人员的培训工作,提高其对数据库的理解能力和SQL开发水平。将部分SQL运行检查的职责前置,在开发阶段就能规避很多问题。要向开发人员灌输SQL优化的思想,在工作中逐步积累,这样才能提高公司整体开发质量,也可以避免很多低级错误。

2)SQL Review制度

对于SQL Review,怎么强调都不过分。从业内来看,很多公司也都在自己的开发流程中纳入了这个环节,甚至列入考评范围,对其重视程度可见一斑。其常见典型做法是利用SQL分析引擎(商用或自研)进行分析或采取半人工的方式进行审核。审核后的结果可作为持续改进的依据。

SQL Review的中间结果可以保留,作为系统上线后的对比分析依据,进而可将SQL的审核、优化、管理等功能集成起来,完成对SQL整个生命周期的管理。

3)限流/资源控制

有些数据库提供了丰富的资源限制功能,可以从多个维度限制会话对资源(CPU、MEMORY、IO)的使用,可避免发生单个会话影响整个数据库的运行状态。

对于一些开源数据库,部分技术实力较强的公司还通过对内核的修改实现了限流功能,控制资源消耗较多的SQL运行数量,从而避免拖慢数据库的整体运行。

一条SQL引发的“血案”:与SQL优化相关的4个案例

案例02 糟糕的结构设计带来的问题

1. 案例说明

这是某公司后台的ERP系统,系统已经上线运行了10多年。随着时间的推移,累积的数据量越来越大。随着公司业务量的不断增加,数据库系统运行缓慢的问题日益凸显。

为提高运行效率,公司计划有针对性地对部分大表进行数据清理。在DBA对某个大表进行清理时出现了问题。这个表本身有数百吉字节,按照指定的清理规则只需要根据主键字段范围(运算符为>=)选择出一定比例(不超过10%)的数据进行清理即可。

但在实际使用中发现,该SQL是全表扫描,执行时间大大超出预期。DBA尝试使用强制指定索引方式清理数据,依然无效,整个SQL语句的执行效率达不到要求。为了避免影响正常业务运行,不得不将此次清理工作放在半夜进行,还需要协调库房等诸多单位进行配合,严重影响正常业务运行。

为了尽量减少对业务的影响,DBA求助笔者帮助协同分析。这套ERP系统是由第三方公司开发的,历史很久远,相关的数据字典等信息都已经找不到了,只能从纯数据库的角度进行分析。这是一个普通表(非分区表),按照主键字段的范围查询一批记录并进行清理。

按照正常理解,执行索引范围扫描应该是效率较高的一种处理方式,但实际情况都是全表扫描。进一步分析发现,该表的主键是没有业务含义的,仅仅是自增长的数据,其来源是一个序列。

但奇怪的是,这个主键字段的类型是变长文本类型,而不是通常的数字类型。当初定义该字段类型的依据,现在已经无从考证,但实验表明正是这个字段的类型“异常”,导致了错误的执行路径。

下面通过一个实验重现这个问题。

1)数据准备

两个表的数据类型相似(只是ID字段类型不同),各插入了320万数据,ID字段范围为1~3200000。

createtablet1asselect*fromdba_objectswhere1=0;
altertablet1addidintprimarykey;
createtablet2asselect*fromdba_objectswhere1=0;
altertablet2addidvarchar2(10)primarykey;

insertintot1
select"test","test","test",rownum,rownum,"test",sysdate,sysdate,"test","test","","","",rownum
fromdual
connectbyrownum<=3200000;
insertintot2
select"test","test","test",rownum,rownum,"test",sysdate,sysdate,"test","test","","","",rownum
fromdual
connectbyrownum<=3200000;
commit;
execdbms_stats.gather_table_stats(ownname=>"hf",tabname=>"t1",cascade=>true,estimate_percent=>100);
execdbms_stats.gather_table_stats(ownname=>"hf",tabname=>"t2",cascade=>true,estimate_percent=>100);

2)模拟场景

相关代码如下:

select*fromt1whereid>=3199990;
11rowsselected.
--------------------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|
---------------------------------------------------------------------------------
|0|SELECTSTATEMENT||11|693|4(0)|00:00:01|
|1|TABLEACCESSBYINDEXROWID|T1|11|693|4(0)|00:00:01|
|*2|INDEXRANGESCAN|SYS_C0025294|11||3(0)|00:00:01|
---------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1recursivecalls
0dbblockgets
6consistentgets
0physicalreads

对于普通的采用数值类型的字段,范围查询就是正常的索引范围扫描,执行效率很高。

select*fromt2whereid>="3199990";
755565rowsselected.
--------------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|
--------------------------------------------------------------------------
|0|SELECTSTATEMENT||2417K|149M|8927(2)|00:01:48|
|*1|TABLEACCESSFULL|T2|2417K|149M|8927(2)|00:01:48|
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1recursivecalls
0dbblockgets
82568consistentgets
0physicalreads

对于文本类型字段的表,范围查询就是对应的全表扫描,效率较低是显而易见的。

3)分析结论

  • 字符类型在索引中是“乱序”的,这是因为字符类型的排序方式与我们的预期不同。从“select * from t2 where id>= "3199990"”执行返回755 565条记录可见,不是直观上的10条记录。这也是当初在做表设计时,开发人员没有注意的问题。
  • 字符类型还导致了聚簇因子很大,原因是插入顺序与排序顺序不同。详细点说,就是按照数字类型插入(1..3200000),按字符类型("1"..."32000000")t排序。
selecttable_name,index_name,leaf_blocks,num_rows,clustering_factor
fromuser_indexes
wheretable_namein("T1","T2");
TABLE_NAMEINDEX_NAMELEAF_BLOCKSNUM_ROWSCLUSTERING_FACTOR
---------------------------------------------------------------------------
T1SYS_C00252946275320000031520
T2SYS_C0025295132713200000632615
  • 在对字符类型使用大于运算符时,会导致优化器认为需要扫描索引大部分数据且聚簇因子很大,最终导致弃用索引扫描而改用全表扫描方式。

4)解决方法

具体的解决方法如下:

select*fromt2whereidbetween"3199990"and"3200000";
--------------------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|
--------------------------------------------------------------------------------
|0|SELECTSTATEMENT||6|390|5(0)|00:00:01|
|1|TABLEACCESSBYINDEXROWID|T2|6|390|5(0)|00:00:01|
|*2|INDEXRANGESCAN|SYS_C0025295|6||3(0)|00:00:01|
--------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1recursivecalls
0dbblockgets
13consistentgets
0physicalreads

将SQL语句由开放区间扫描(>=),修改为封闭区间(between xxx and max_value)。使得数据在索引局部顺序是“对的”。如果采用这种方式仍然走全表扫描,还可以进一步细化分段或者采用“逐条提取+批绑定”的方法。

2. 给我们的启示

这是一个典型的由不好的数据类型带来的执行计划异常的例子。它给我们带来如下启示:

  • 糟糕的数据结构设计往往是致命的,后期的优化只是补救措施。只有从源头上加以杜绝,才是优化的根本。
  • 在设计初期能引入数据库审核,可以起到很好的作用。

一条SQL引发的“血案”:与SQL优化相关的4个案例

案例03 规范SQL写法好处多

1. 案例说明

某大型电商公司数据仓库系统,开发人员反映作业运行缓慢。经检查是一个新增业务中某条SQL语句导致。经分析是非标准的SQL引起优化器判断异常,将其修改成标准写法后,SQL恢复正常。

1)具体分析

看下面的代码:

select...from...
where
(
(
   order_creation_date>=to_date(20120208,"yyyy-mm-dd")and
order_creation_date<to_date(20120209,"yyyy-mm-dd")
)
or
(
   send_date>=to_date(20120208,"yyyy-mm-dd")andsend_date<to_date(20120209, "yyyy-mm-dd")
)
)
andnvl(a.bd_id,0)=1
--------------------------------------------------------------------------------
|Id|Operation|Name|Cost(%CPU)|Time|Pstart|Pstop|
--------------------------------------------------------------------------------
|0|SELECTSTATEMENT||2470K(100)||||
|1|SORTGROUPBY||||||
|2|TABLEACCESSBYGLOBALINDEXROWID
|XXXX|5(0)|00:00:01|ROWL|ROWL|
|3|NESTEDLOOPS||2470K(1)|08:14:11|||
|4|VIEW|VW_NSO_1|2470K(1)|08:14:10|||
|5|FILTER||||||
|6|HASHGROUPBY||2470K(1)|08:14:10|||
|7|TABLEACCESSBYGLOBALINDEXROWID
|XXXX|5(0)|00:00:01|ROWL|ROWL|
|8|NESTEDLOOPS||2470K(1)|08:14:10|||
|9|SORTUNIQUE||2340K(2)|07:48:11|||
|10|PARTITIONRANGEALL
||2340K(2)|07:48:11|1|92|
|11|TABLEACCESSFULL
|XXXX|2340K(2)|07:48:11|1|92|
|12|INDEXRANGESCAN
|XXXX|3(0)|00:00:01|||
|13|INDEXRANGESCAN|XXXX|3(0)|00:00:01|||
--------------------------------------------------------------------------------

这个SQL中涉及的主要表是一个分区表,从执行计划(Pstart、Pstop)中可见,扫描了所有分区,分区裁剪特性没有起效。

2)解决方法

见下面的代码:

select...
from...
where
order_creation_date>=to_date(20120208,"yyyy-mm-dd")and
order_creation_date<to_date(20120209,"yyyy-mm-dd")
unionall
select...
from...
where
send_date>=to_date(20120208,"yyyy-mm-dd")and
send_date<to_date(20120209,"yyyy-mm-dd")and
nvl(a.bd_id,0)=5

尝试通过引入union all来分解查询,以便于优化器做出更准确的判断。采用这个方法后,确实起效了,当然不可避免会扫描两遍表。

select...
from...
where
(
(
order_creation_date>=to_date(20120208,"yyyymmdd")and
order_creation_date<to_date(20120209,"yyyymmdd")
)
or
(
send_date>=to_date(20120208,"yyyymmdd")and
send_date<to_date(20120209,"yyyymmdd")
)
);
--------------------------------------------------------------------------------
|Id|Operation|Name|Cost(%CPU)|Time|Pstart|Pstop|
--------------------------------------------------------------------------------
|0|SELECTSTATEMENT||42358(1)|00:08:29|||
|1|SORTAGGREGATE||||||
|2|CONCATENATION||||||
|3|PARTITIONRANGESINGLE
||17393(1)|00:03:29|57|57|
|*4|TABLEACCESSFULL
|XXXX|17393(1)|00:03:29|57|57|
|*5|TABLEACCESSBYGLOBALINDEXRO

相关内容

怎么学?如何干?西藏7市(地)委书记在西藏日报发表文章

启航新征程 开创新局面为全面建设社会主义现代化新拉萨努力奋斗自治区党委常委、拉萨市委书记 普布顿珠党的二十大大笔擘画坚持以中国式现代化全面推进中华民族伟大复兴的宏伟蓝图,区党委十届三次全会全面铺开建设美丽幸福西藏、共圆伟大复兴梦想的壮阔实践···

2023年全区智慧旅游专业人才培训班圆满结业

6月2日,自治区旅游发展厅主办的全区智慧旅游专业人才培训班在拉萨圆满结业。培训共历时三天,累计完成全区旅游行政管理部门工作人员及涉旅企业专业技术人员培训100人。本次培训是区旅发厅深入推进学习贯彻习近平新时代中国特色社会主义思想主题教育重要···

徐汇萨迦共同举办大美西藏首届口腔学习班

随着现代医学的发展,口腔健康越来越受到人们的关注,为提高西藏地区口腔技术水平和服务质量,近日,徐汇区牙病防治所和萨迦县中心医院远程连线共同举办首届口腔学习班开班仪式。徐汇区卫生健康委副主任胡强,上海援藏干部、萨迦县委常务副书记、常务副县长沈···

自觉抵制“一对一”“一对多”等学科类培训!哈尔滨中小学生暑期预警来了

17日,记者从哈尔滨市各区教育局获悉,2023年暑假将至,南岗区、道里区、香坊区教育局向家长发出预警,自觉抵制违规培训,各区义务教育阶段学科类培训机构已经全部注销,暑假期间以任何形式开展的学科类培训均属于违规培训。家长要自觉抵制任何机构或个···

哈尔滨市道里区企投局举办“招商大讲堂”专题培训

黑龙江网讯(记者 王惠婷)10月24日,哈尔滨市道里区企投局组织开展“招商大讲堂”专题培训班,全区各招商专班负责同志及业务骨干参加培训。本次培训从实际需求出发,紧紧围绕当前招商工作中的热点、难点,对“什么是招商引资”“道里区的产业结构和主导···

团黑龙江省委举办全省青年文明号青年岗位能手学习宣传贯彻党的二十大精神培训交流会

中国青年报客户端讯(李海涛)为深入学习贯彻党的二十大精神,充分发挥青年文明号、青年岗位能手示范引领作用,在全省职业青年中掀起学习党的二十大精神热潮,11月22日,团黑龙江省委举办全省青年文明号青年岗位能手学习宣传贯彻党的二十大精神培训交流会···

辽宁葫芦岛举办外贸政策培训会推动外贸保稳增量

辽宁省葫芦岛市外贸政策培训会4月3日举办。 辽宁省贸促会供图中新网葫芦岛4月3日电 (李晛)辽宁省葫芦岛市外贸政策培训会4月3日举办。本次活动由辽宁省贸促会支持、葫芦岛市商务局主办,葫芦岛市贸促会、葫芦岛海关、中国出口信用保险辽宁分公司和辽···

山西运城:严查无证校外培训机构 查封9家警告2家

新华社太原8月5日电(记者王飞航)记者从山西省运城市政府了解到,运城市教育局近日联合市公安局等多家单位,对中心城区无证校外培训机构进行了一次突击检查,共检查了13家校外培训机构,查封9家,警告2家,发放整改通知书4份。今年7月,运城市教育局···

山西开展培训筑牢森林“防火墙”

山西新闻网3月30日讯(记者 卢奕如)今日,记者从山西省应急管理厅获悉,全省举办森林草原防灭火业务培训,邀请专家以视频会议形式,围绕森林扑火指挥实操、森林草原火灾防控经验做法、火灾现场各级各类指挥员具体操作中遇到的问题等内容进行授课。培训内···

校外培训机构治理工作取得进展 山西停办近1300所

资料图:小学生排队等待进入校园。中新社记者 刘文华 摄中新网5月11日电 据教育部网站消息,按照校外培训机构专项治理工作整体安排,教育部、民政部、国家市场监管总局启动了校外培训机构治理专项督查工作。5月9日至10日,督查组率先在北京市开展华···

山西综改区举办省技术创新中心申报培训

  8月18日,山西综改区科技金融部举办2023年度省技术创新中心申报培训会,来自区内企业、科研院所及有关单位代表160余人参加了培训。  山西省技术创新中心是以产业前沿引领技术和关键共性技术研发为核心的产业技术创新平台,承担着为区域和产业···

山西汾阳医院开展健康教育与控烟知识培训

来源:【吕梁日报-吕梁新闻网】本报讯 (记者 刘少伟) 5月18日,在“世界无烟日”到来之际,山西汾阳医院组织开展健康教育与控烟知识培训。近年来,山西汾阳医院全面落实健康中国战略,根据国家卫健委《关于2011年起全国医疗卫生系统全面禁烟的决···

山西省文物局年度田野考古技术培训班开班

10月10日,山西省文物局在运城闻喜上郭城址、邱家庄墓群举办2023年度田野考古技术培训班开班仪式。该次培训为期三个月,通过理论和实践两部分教学,旨在推进山西考古工作高质量发展,提升考古业务人员专业技术水平。本次培训由山西省考古研究院和山西···

最低每课时9元!全省学科类校外培训课时长和收费标准出台

近日,山西省发改委、省教育厅下发《关于中小学学科类校外培训收费标准及有关事项的通知》,明确全省中小学学科类校外培训收费标准,从12月17日起执行。《通知》对全省线上线下学科类校外培训基准收费标准和浮动幅度制定了科学标准。其中,义务教育阶段线···

山西天镇 阳光职业培训学校培养乡村“新农人”乡村振兴添动能

(记者 贺文生) 山西天镇县阳光职业培训学校紧紧围绕乡村振兴战略,按照“政府引导、农民自愿、立足产业、突出重点”的原则,创新高素质农民技能培训方式方法,采取以“授人以渔”的方式,让人才振兴成为助推农业农村现代化的内生动力,以高素质农民引领现···

山西:建立全过程 全链条 无缝隙安全培训制度

黄河新闻网讯(记者杨江涛)日前,山西省应急管理厅下发了《山西省安全培训管理暂行办法》(以下简称《办法》)。我省将进一步抓好安全生产这个基本盘、基本面,推动全省安全培训工作制度化、规范化、科学化,促进安全培训工作高质量发展。山西省应急管理厅厅···

山西:艺考培训机构纳入全国监管平台管理

央广网太原10月6日消息(记者郎麒) 日前,山西省教育厅、省发改委、省公安厅等部门联合制定《加强面向高中阶段学生艺考培训规范管理工作方案》,针对艺考培训的突出特点和实际情况,全面规范艺考培训行为,将艺考培训机构统一纳入全国校外教育培训监管与···

太平财险阳泉中支开展消防安全教育和有限空间作业培训

为强化员工安全意识,进一步提升员工消防和有限空间突发事件应急处理能力,9月14日,太平财险阳泉中支邀请北京市卫民安消防教育咨询中心山西分中心讲师向全体员工开展了一次消防安全教育和有限空间作业课程培训。按照防消结合、预防为主的原则,本次讲座通···

山西省数字化转型贯标试点工作宣贯培训会在太原举行

  10月20日消息,山西省数字化转型贯标试点工作宣贯培训会在太原举行。省工信厅介绍,作为国家数字化转型贯标试点省份,试点启动后将引导企业加快数字化转型,助力制造业高端化、智能化、绿色化发展。  今年,工信部启动数字化转型贯标试点工作,我省···

山西马兰花创业培训讲师大赛收官 太原市获多个奖项

山西新闻网8月31日讯(记者 冯耿姝)8月29日,山西省第四届马兰花创业培训讲师大赛圆满收官,太原市代表队在比赛中分获多个二、三等奖和优秀奖。本届大赛以“启迪创新思维·激发创业梦想”为主题,全省共有56名教师晋级复赛,其中,太原市有7名选手···