SQL优化典型案例分析

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