![SQL优化典型案例分析_第1页](http://file3.renrendoc.com/fileroot_temp3/2021-12/6/d8ea5292-9814-4355-b9ea-4ebf8da99dec/d8ea5292-9814-4355-b9ea-4ebf8da99dec1.gif)
![SQL优化典型案例分析_第2页](http://file3.renrendoc.com/fileroot_temp3/2021-12/6/d8ea5292-9814-4355-b9ea-4ebf8da99dec/d8ea5292-9814-4355-b9ea-4ebf8da99dec2.gif)
![SQL优化典型案例分析_第3页](http://file3.renrendoc.com/fileroot_temp3/2021-12/6/d8ea5292-9814-4355-b9ea-4ebf8da99dec/d8ea5292-9814-4355-b9ea-4ebf8da99dec3.gif)
![SQL优化典型案例分析_第4页](http://file3.renrendoc.com/fileroot_temp3/2021-12/6/d8ea5292-9814-4355-b9ea-4ebf8da99dec/d8ea5292-9814-4355-b9ea-4ebf8da99dec4.gif)
![SQL优化典型案例分析_第5页](http://file3.renrendoc.com/fileroot_temp3/2021-12/6/d8ea5292-9814-4355-b9ea-4ebf8da99dec/d8ea5292-9814-4355-b9ea-4ebf8da99dec5.gif)
版权说明:本文档由用户提供并上传,收益归属内容提供方,若内容存在侵权,请进行举报或认领
文档简介
1、 关键字:充分利用过滤条件原始sql查询SELECT DISTINCT '' AS rowid, '' AS id, a.cstguid, b.cstname, Isnull(b.cardid,'') AS cardid, Isnull(b.mobiletel,'') + ' ' + Isnull(b.hometel,'') + ' ' + Isnull(b.officetel,'') AS lxtel, '' AS oppguid, a.buguid
2、, '' AS projguid, c.memlevel, c.memcode, c.memguid, c.buguid AS hjbuguid, '' AS username, '' AS userguid, d.buname AS hjbunameFROM p_cstattach a LEFT JOIN p_customer b ON a.cstguid = b.cstguid LEFT JOIN h_member c ON b.cstguid = c.cstguid AND c.memstatus = '正式会员' LEFT
3、 JOIN mybusinessunit d ON c.buguid = d.buguidWHERE (1 = 1) AND (Isnull(b.mobiletel,'') + ' ' + Isnull(b.hometel,'') + ' ' + Isnull(b.officetel,'') LIKE '')优化前的IO表'Worktable'。扫描计数0,逻辑读取0 次,物理读取0 次,预读0 次,lob 逻辑读取0 次,lob 物理读取0 次,lob 预
4、读0 次。表'h_Member'。扫描计数1,逻辑读取4818 次,物理读取0 次,预读0 次,lob 逻辑读取0 次,lob 物理读取0 次,lob 预读0 次。表'p_CstAttach'。扫描计数1,逻辑读取12897 次,物理读取0 次,预读0 次,lob 逻辑读取0 次,lob 物理读取0 次,lob 预读0 次。表'p_Customer'。扫描计数1,逻辑读取27222 次,物理读取0 次,预读0 次,lob 逻辑读取0 次,lob 物理读取0 次,lob 预读0 次。表'myBusinessUnit'。扫描计数1,逻
5、辑读取6 次,物理读取0 次,预读0 次,lob 逻辑读取0 次,lob 物理读取0 次,lob 预读0 次。优化前的执行计划(总估计子树成本:56.239)优化过程分析第一步:从业务角度分析。这里要找手机号码客户,拥有该手机号码的客户不会很多,为什么会有这么高的IO呢?很可能没有利用到索引,扫描产生这么大的IO;第二步:看一下执行计划。确实是索引扫描。那么,有过滤条件为什么没有利用到索引呢?第三步:分析一下sql。过滤条件不符合 sARG规范:1、过滤条件左侧有函数;2、like条件左侧有百分号(%);怎么办?第四步:再分析业务。操作员什么情况下会对手机号码做模糊查
6、询?可能是这种情况:异地手机录入时前面加了0。买房人有多少使用异地手机呢?我统计了一下wk2008_5_13日的数据库,异地手机只占0.65%。为了满足0.65%的查询准确性牺牲99.35%查询性能有些不值得J;第五步:我们能否这样改造呢?在普通查询中提供手机号码的精确查询,满足99.35%查询要求,在高级查询中使用模糊查询,满足另外0.65%的查询。改造后的sql查询SELECT DISTINCT '' AS rowid, '' AS id, a.cstguid, b.cstname, Isnull(b.cardid,'') AS cardid
7、, Isnull(b.mobiletel,'') + ' ' + Isnull(b.hometel,'') + ' ' + Isnull(b.officetel,'') AS lxtel, '' AS oppguid, a.buguid, '' AS projguid, c.memlevel, c.memcode, c.memguid, c.buguid AS hjbuguid, '' AS username, '' AS userguid, d.bu
8、name AS hjbunameFROM p_cstattach a LEFT JOIN p_customer b ON a.cstguid = b.cstguid LEFT JOIN h_member c ON b.cstguid = c.cstguid AND c.memstatus = '正式会员' LEFT JOIN mybusinessunit d ON c.buguid = d.buguidWHERE (1 = 1) AND b.mobiletel='#39;优化后的IO表'myBusinessUnit'。扫描计数1,
9、逻辑读取2 次,物理读取0 次,预读0 次,lob 逻辑读取0 次,lob 物理读取0 次,lob 预读0 次。表'h_Member'。扫描计数3,逻辑读取12 次,物理读取0 次,预读0 次,lob 逻辑读取0 次,lob 物理读取0 次,lob 预读0 次。表'p_CstAttach'。扫描计数3,逻辑读取12 次,物理读取0 次,预读0 次,lob 逻辑读取0 次,lob 物理读取0 次,lob 预读0 次。表'p_Customer'。扫描计数1,逻辑读取12 次,物理读取0 次,预读0 次,lob 逻辑读取0 次,lob 物理读取0 次,
10、lob 预读0 次。优化后的执行计划(总估计子树成本:0.035):没有了索引扫描。优化提升:99.9%关键字:合理利用索引原始sql查询SELECT Top 1 cstnameFROM vh_memberWHERE xckid = '2003026955' AND buguid = 'F6B010E6-5C72-48E3-865F-FF354C6CF7CF'优化前的IO表'p_Customer'。扫描计数0,逻辑读取3 次,物理读取0 次,预读0 次,lob 逻辑读取0 次,lob 物理读取0 次,lob 预读0 次。表'h_Membe
11、r'。扫描计数9,逻辑读取7453 次,物理读取0 次,预读0 次,lob 逻辑读取0 次,lob 物理读取0 次,lob 预读0 次。表'Worktable'。扫描计数0,逻辑读取0 次,物理读取0 次,预读0 次,lob 逻辑读取0 次,lob 物理读取0 次,lob 预读0 次。优化前的执行计划(总估计子树成本:5.110)优化过程分析第一步:IO较高,原因是进行了索引扫描,而且占用了99%的成本。最终结果只有一条记录,应该是没有建索引。第二步:由于XckID选择度较高,所以在这个字段上建索引最合适。在XckID上建非聚集索引NCX_XckID。优化后的IO表
12、39;p_Customer'。扫描计数0,逻辑读取3 次,物理读取0 次,预读0 次,lob 逻辑读取0 次,lob 物理读取0 次,lob 预读0 次。表'h_Member'。扫描计数1,逻辑读取6 次,物理读取0 次,预读0 次,lob 逻辑读取0 次,lob 物理读取0 次,lob 预读0 次。优化后的执行计划(总估计子树成本:0.010):没有了索引扫描。优化提升:99.8%关键字:合理利用包含索引原始sql查询SELECT Count(*)FROM vs_getinforpos LEFT JOIN (SELECT roominfo,roomguid FROM
13、ep_room) a ON vs_getinforpos.roomguid = a.roomguidWHERE (posguid IS NULL AND (projguid IN ('02d6afbd-95d6-4d22-8111-31b497739390', '83674692-a9a6-4551-a28e-dd57450c1485', '7ab45da2-38d6-4f4c-8cc5-ec7103cfe217', '983d1a73-fe30-4a53-9c2c-4064ba26509c', 'cf3a760b-b0b
14、a-4ce9-be21-91ee723f85ea', '50667a97-55d9-4e7d-883b-801f2c8a2988', '0c631f1c-9d78-4159-9657-f21324977df3', '939cf402-ee1d-44f3-845a-3576996a0daa', '8ae67b31-09eb-48b3-a288-07796be3f4a4') AND getdate >= '2008-05-01' AND getdate <= '2008-05-31'
15、 AND posterminal = '建行(深银联)') AND Isnull(holdbank,'') <> ''优化前的IO表's_Voucher'。扫描计数0,逻辑读取570 次,物理读取0 次,预读0 次,lob 逻辑读取0 次,lob 物理读取0 次,lob 预读0 次。表's_Getin'。扫描计数1,逻辑读取13210 次,物理读取0 次,预读0 次,lob 逻辑读取0 次,lob 物理读取0 次,lob 预读0 次。优化前的执行计划(总估计子树成本:8.000)优化过程分析第一步:S_
16、GetIn上的IO很高=13210,可能是表扫描或索引扫描,或者大数据量的LookUp或键查找;第二步:分析执行计划,果然进行了4304次“键查找”,说明索引查找使用的索引(s_GetIn5)没有包含要取的字段,所以,根据索引查找得到的GetinGUID再进行一次键查找。由于键查找占用99%的成本,我们可以包含索引。改造索引s_GetIn5 在“包含性列”中加入字段:VouchGUID,RmbAmount,PostGUID,PostTerminal,HoldBank;第三步:索引改造完成,再看一下IO和成本。优化后的IO表's_Voucher'。扫描计数0,逻辑读取570 次,
17、物理读取0 次,预读0 次,lob 逻辑读取0 次,lob 物理读取0 次,lob 预读0 次。表's_Getin'。扫描计数1,逻辑读取48 次,物理读取0 次,预读0 次,lob 逻辑读取0 次,lob 物理读取0 次,lob 预读0 次。优化后的执行计划(总估计子树成本:0.048):优化提升:99.4%关键字:组合索引顺序对查询的影响原始sql查询- sql作了简化SELECT MemNameFROM h_prememberWHERE infostatus = '待处理' AND buguid = ('f9ec7691-8bc1-459e-8a7
18、0-a1c7909b689b') AND (1 = 1) AND (2 = 2) AND joinpath NOT IN ('现场入会','网上入会')优化前的IO- 按joinpath、infostatus、buguid建组合索引表'h_Premember'。扫描计数2,逻辑读取295 次,物理读取0 次,预读0 次,lob 逻辑读取0 次,lob 物理读取0 次,lob 预读0 次。优化前的执行计划(总估计子树成本:0.630)优化过程分析第一步:joinpath过滤条件不符合sARG规范,创建索引不是很合适。infostatus和b
19、uguid按哪个字段建索引更合适呢?第二步:按选择度高的字段建索引会比较合适,也就是说哪个过滤后的记录最少。分别按infostatus和buguid字段过滤记录数:1346和16273。所以按如下组合顺序建索引比较合适:buguid、infostatus、joinpath。优化后的IO- 按buguid、infostatus、joinpath建组合索引表'h_Premember'。扫描计数2,逻辑读取28 次,物理读取0 次,预读0 次,lob 逻辑读取0 次,lob 物理读取0 次,lob 预读0 次。优化后的执行计划(总估计子树成本:0.390):优化提升:38.1%关键字
20、:按需更新原始sql UPDATE p_roomSET roomguid = '57fcfcf8-820b-4677-b8d6-7d80af2757b9', calcrentmode = '', rentunit = '', bz = '', rentprice = Cast('0' AS money), rentamount = Cast('0' AS money), rentstatus = '租控', bproducttypecode = '01.05', is
21、virtualroom = '0', huxing = 'C', roomstru = '三室二厅二卫', sight = '', west = '南北', tdno = '', cqfznum = '', bldarea = Cast('133.9' AS money), tnarea = Cast('115.09' AS money), areastatus = '实测', djarea = '套内面积', zxbz
22、 = '', zxprice = Cast('0' AS money), zxtotal = Cast('0' AS money), istempletroom = '0', htroom = '19号楼单元室', price = Cast('9475.41' AS money), tnprice = Cast('11024.04' AS money), total = Cast('1268757' AS money), isannexe = '0',
23、 ysxkzdate = '1990-01-01', ysxkzno = '', yyjfdate = NULL, salerentable = '出售', cararea = Cast('0' AS money), ltarea = Cast('0' AS money), dxsarea = Cast('0' AS money), hyarea = Cast('0' AS money), rhhyarea = Cast('0' AS money), iscannot
24、sale = '0', cannotsalereason = '', discount_discnt = Cast('0' AS money), discount_count = Cast('0' AS money), isareamodify = '0', variety = 'C', productattribute = '', bedeckkind = '毛坯'WHERE roomguid = '57fcfcf8-820b-4677-b8d6-7d80a
25、f2757b9' 优化前的IO表'p_Room'。扫描计数0,逻辑读取94 次,物理读取1 次,预读0 次,lob 逻辑读取0 次,lob 物理读取0 次,lob 预读0 次。优化前的执行计划(总估计子树成本:0.15)优化过程分析第一步:从业务角度分析。房间资料编辑页面不会修改每个字段,但是所有字段都更新了。第二步:试试按需更新。改造后的sqlUPDATE p_roomSET bedeckkind = '装修'WHERE roomguid = '57fcfcf8-820b-4677-b8d6-7d80af2757b9'优化后的IO表
26、39;p_Room'。扫描计数0,逻辑读取3 次,物理读取0 次,预读0 次,lob 逻辑读取0 次,lob 物理读取0 次,lob 预读0 次。优化后的执行计划(总估计子树成本:0.013):优化提升:91.3%关键字:合理选择过滤条件原始sql SELECT top 1 null as OppGUID ,'放弃选择' as cstname ,'' as topic,'' as lxdh,'' as username FROM es_Opportunity WHERE RoomGUID is null and Statu
27、s='激活' and Process<>'预约排号' and (es_Opportunity.ProjGUID='42e6e1a5-b5fc-4dd9-92cc-baaa81db4368' AND es_Opportunity.UserGUID='bd7e66e4-ef9c-464f-9807-bdb94029c17c' AND (es_Opportunity.CstGUID1 IN ('b56426b8-7bad-45c5-9649-247ab8203a2d') OR es_Opportunity.C
28、stGUID2 IN ('b56426b8-7bad-45c5-9649-247ab8203a2d') OR es_Opportunity.CstGUID3 IN ('b56426b8-7bad-45c5-9649-247ab8203a2d') OR es_Opportunity.CstGUID4 IN ('b56426b8-7bad-45c5-9649-247ab8203a2d') Union all SELECT OppGUID,cstname,topic,lxdh,username FROM es_Opportunity WHERE Roo
29、mGUID is null and Status='激活' and Process<>'预约排号' and (es_Opportunity.ProjGUID='42e6e1a5-b5fc-4dd9-92cc-baaa81db4368' AND es_Opportunity.UserGUID='bd7e66e4-ef9c-464f-9807-bdb94029c17c' AND (es_Opportunity.CstGUID1 IN ('b56426b8-7bad-45c5-9649-247ab8203a2d
30、9;) OR es_Opportunity.CstGUID2 IN ('b56426b8-7bad-45c5-9649-247ab8203a2d') OR es_Opportunity.CstGUID3 IN ('b56426b8-7bad-45c5-9649-247ab8203a2d') OR es_Opportunity.CstGUID4 IN ('b56426b8-7bad-45c5-9649-247ab8203a2d')优化前的IO表'myUser'。扫描计数0,逻辑读取2 次,物理读取0 次,预读0 次,lob 逻辑读取
31、0 次,lob 物理读取0 次,lob 预读0 次。表'p_Customer'。扫描计数0,逻辑读取2406 次,物理读取0 次,预读0 次,lob 逻辑读取0 次,lob 物理读取0 次,lob 预读0 次。表'Worktable'。扫描计数0,逻辑读取0 次,物理读取0 次,预读0 次,lob 逻辑读取0 次,lob 物理读取0 次,lob 预读0 次。表's_Opp2Cst '。扫描计数0,逻辑读取22140 次,物理读取0 次,预读0 次,lob 逻辑读取0 次,lob 物理读取0 次,lob 预读0 次。表's_Opportun
32、ity'。扫描计数2,逻辑读取4592 次,物理读取0 次,预读0 次,lob 逻辑读取0 次,lob 物理读取0 次,lob 预读0 次。优化前的执行计划(总估计子树成本:0.727551)优化过程分析第一步:从业务角度分析。该SQL可能是想查出指定客户的销售机会,过滤条件中直接使用es_Opportunity 的CstGUID1234最简单,4次or搞定:es_Opportunity.CstGUID1 IN ('b56426b8-7bad-45c5-9649-247ab8203a2d') OR es_Opportunity.CstGUID2 IN ('b56
33、426b8-7bad-45c5-9649-247ab8203a2d') OR es_Opportunity.CstGUID3 IN ('b56426b8-7bad-45c5-9649-247ab8203a2d') OR es_Opportunity.CstGUID4 IN ('b56426b8-7bad-45c5-9649-247ab8203a2d')但里面的CstGUID1234字段都需要进行多次连接接,性能损耗较大,所以可以改为用s_Opp2Cst进行子查询过滤,es_Opportunity.oppGUID IN (select OppGUID fr
34、om s_Opp2Cst where CstGUID = 'b56426b8-7bad-45c5-9649-247ab8203a2d')两种写法完全等价,但性能明显不同改造后的sqlSELECT top 1 null as OppGUID ,'放弃选择' as cstname ,'' as topic,'' as lxdh,'' as username FROM es_Opportunity WHERE RoomGUID is null and Status='激活' and Process<
35、>'预约排号' and (es_Opportunity.ProjGUID='42e6e1a5-b5fc-4dd9-92cc-baaa81db4368' AND es_Opportunity.UserGUID='bd7e66e4-ef9c-464f-9807-bdb94029c17c'AND es_Opportunity.oppGUID IN (select OppGUID from s_Opp2Cst where CstGUID = 'b56426b8-7bad-45c5-9649-247ab8203a2d')Union a
36、ll SELECT OppGUID,cstname,topic,lxdh,username FROM es_Opportunity WHERE RoomGUID is null and Status='激活' and Process<>'预约排号' and (es_Opportunity.ProjGUID='42e6e1a5-b5fc-4dd9-92cc-baaa81db4368' AND es_Opportunity.UserGUID='bd7e66e4-ef9c-464f-9807-bdb94029c17c' AN
37、D es_Opportunity.oppGUID IN (select OppGUID from s_Opp2Cst where CstGUID = 'b56426b8-7bad-45c5-9649-247ab8203a2d')优化后的IO表'myUser'。扫描计数0,逻辑读取2 次,物理读取0 次,预读0 次,lob 逻辑读取0 次,lob 物理读取0 次,lob 预读0 次。表'p_Customer'。扫描计数0,逻辑读取3 次,物理读取0 次,预读0 次,lob 逻辑读取0 次,lob 物理读取0 次,lob 预读0 次。表'Wo
38、rktable'。扫描计数0,逻辑读取0 次,物理读取0 次,预读0 次,lob 逻辑读取0 次,lob 物理读取0 次,lob 预读0 次。表's_Opp2Cst '。扫描计数2,逻辑读取27 次,物理读取0 次,预读0 次,lob 逻辑读取0 次,lob 物理读取0 次,lob 预读0 次。表's_Opportunity'。扫描计数0,逻辑读取6 次,物理读取0 次,预读0 次,lob 逻辑读取0 次,lob 物理读取0 次,lob 预读0 次。优化后的执行计划(总估计子树成本:0.0864879):优化提升:88.1%关键字:还是Convert原始
39、sql SELECT sum(Amount) as Amount FROM s_Getin WHERE convert(varchar(36),SaleGUID)='c874a49e-44da-46cb-b43c-d071b181efef' AND FlagInfo='待退'优化前的IO表'Worktable'。扫描计数0,逻辑读取0 次,物理读取0 次,预读0 次,lob 逻辑读取0 次,lob 物理读取0 次,lob 预读0 次。表's_Getin'。扫描计数9,逻辑读取8746 次,物理读取0 次,预读0 次,lob 逻辑读
40、取0 次,lob 物理读取0 次,lob 预读0 次。优化前的执行计划(总估计子树成本:6.17585)优化过程分析不再多说。改造后的sqlSELECT sum(Amount) as Amount FROM s_Getin WHERE SaleGUID='c874a49e-44da-46cb-b43c-d071b181efef' AND FlagInfo='待退'优化后的IO表's_Getin'。扫描计数1,逻辑读取7 次,物理读取1 次,预读0 次,lob 逻辑读取0 次,lob 物理读取0 次,lob 预读0 次。优化后的执行计划(总估计子树成本:0.0134):优化提升:99%关键字:快速查询vs高级查询原始sql SELECT COUNT(*) FROM es_Contract WHERE (R
温馨提示
- 1. 本站所有资源如无特殊说明,都需要本地电脑安装OFFICE2007和PDF阅读器。图纸软件为CAD,CAXA,PROE,UG,SolidWorks等.压缩文件请下载最新的WinRAR软件解压。
- 2. 本站的文档不包含任何第三方提供的附件图纸等,如果需要附件,请联系上传者。文件的所有权益归上传用户所有。
- 3. 本站RAR压缩包中若带图纸,网页内容里面会有图纸预览,若没有图纸预览就没有图纸。
- 4. 未经权益所有人同意不得将文件中的内容挪作商业或盈利用途。
- 5. 人人文库网仅提供信息存储空间,仅对用户上传内容的表现方式做保护处理,对用户上传分享的文档内容本身不做任何修改或编辑,并不能对任何下载内容负责。
- 6. 下载文件中如有侵权或不适当内容,请与我们联系,我们立即纠正。
- 7. 本站不保证下载资源的准确性、安全性和完整性, 同时也不承担用户因使用这些下载资源对自己和他人造成任何形式的伤害或损失。
最新文档
- 人教版九年级数学下册相似《相似三角形(第3课时)》示范教学课件
- 安徽省县中联盟2024-2025学年高三上学期9月联考 地理试题
- 中班教案散文诗《树真好》
- Y5-47N04离心锅炉引风机设计
- 台式风扇结构设计
- 2025届新高考语文冲刺热点复习:文言文中的判断句
- 统编版《道德与法治》六年级下册第4课《地球-我们的家园》精美课件(第1课时)
- 设备维护保养计划清单
- 《奶茶店员工手册》
- 《计算机应用基础》
- 某公司汽车零部件作业指导书范例
- 初中英语复习用所给词的正确形式填空
- 第一学期高三数学教学计划
- 欧科模块化风冷冷水热泵机组报警代码和维修步骤
- 工业园区安全生产工作面临的主要问题及应对措施
- 《趣味古诗》PPT课件.ppt
- (完整版)生产设备故障处理流程图
- “皆是阿罗汉诸漏已尽无复烦恼逮得己利尽诸有结心得自在”.doc
- 基于西门子PLC洗碗机控制系统设计本科论文
- 新港铁路举水河大桥防洪评价0911
- 娄山关镇中心学校学习雷锋活动简报
评论
0/150
提交评论