实战上亿数据如何秒查.docx
《实战上亿数据如何秒查.docx》由会员分享，可在线阅读，更多相关《实战上亿数据如何秒查.docx（15页珍藏版）》请在冰豆网上搜索。
实战上亿数据如何秒查
实战：
上亿数据如何秒查
最近在忙着优化集团公司的一个报表。
优化完成后，报表查询速度有从半小时以上（甚至查不出）到秒查的质变。
从修改SQL查询语句逻辑到决定创建存储过程实现，花了我3天多的时间，在此总结一下，希望对朋友们有帮助。
数据背景首先项目是西门子中国在我司实施部署的MES项目，由于项目是在产线上运作（3years+），数据累积很大。
在项目的数据库中，大概上亿条数据的表有5个以上，千万级数据的表10个以上，百万级数据的表，很多...（历史问题，当初实施无人监管，无人监控数据库这块的性能问题。
ps:
我刚入职不久...）不多说，直接贴西门子中国的开发人员在我司开发的SSRS报表中的SQL语句：
ViewCode这个查询语句，实际上通过我的检测和调查，在B/S系统前端已无法查出结果，半小时，一小时...。
因为我直接在SQL查询分析器查，半小时都没有结果。
（原因是里面对一张上亿级数据表和3张千万级数据表做全表扫描查询）不由感慨，西门子中国的素质（或者说责任感）就这样？
下面说说我的分析和走的弯路（思维误区），希望对你也有警醒。
探索和误区首先相关表的索引，没有建全的，把索引给建上。
索引这步完成后，发现情况还是一样，查询速度几乎没有改善。
后来想起相关千万级数据以上的表，都还没有建立表分区。
于是考虑建立表分区以及数据复制的方案。
这里有必要说明下：
我司报表用的是一个专门的数据库服务器，数据从产线订阅而来。
就是常说的“读写分离”。
如果直接在原表上建立表分区，你会发现执行表分区的事物会直接死锁。
原因是：
表分区操作本身会锁表，产线还在推数据过来，这样很容易“阻塞”，“死锁”。
我想好的方案是：
建立一个新表（空表），在新表上建好表分区，然后复制数据过来。
正打算这么干。
等等！
我好像进入了一个严重的误区！
分析：
原SQL语句和业务需求，是对产线的数据做产品以及序列号的追溯，关键是查询条件里没有有规律的"条件"（如日期、编号），贸然做了表分区，在这里几乎没有意义！
反而会降低查询性能！
好险！
还是一步一步来，先做SQL语句分析。
一.对原SQL语句的分析1.查询语句的where条件，有大量@varin...or（@var=''）的片段2.where条件有like'%'+@var+'%'3.where条件有case...end函数4.多次连接同一表查询，另外使用本身已嵌套的视图表，是不是必须，是否可替代？
5.SQL语句有*号，视图中也有*号出现二.优化设计首先是用存储过程改写，好处是设计灵活。
核心思想是：
用一个或多个查询条件（查询条件要求至少输入一个）得到临时表，每个查询条件如果查到集合，就更新这张临时表，最后汇总的时候，只需判断这个临时表是否有值。
以此类推，可以建立多个临时表，将查询条件汇总。
这样做目前来看至少两点好处：
1.省去了对变量进行=@varor（@var=''）的判断；2.抛弃sql拼接，提高代码可读性。
再有就是在书写存储过程，这个过程中要注意：
1.尽量想办法使用临时表扫描替代全表扫描；2.抛弃in和notin语句，使用exists和notexists替代；3.和客户确认，模糊查询是否有必要，如没有必要，去掉like语句；4.注意建立适当的，符合场景的索引；5.踩死"*"号；6.避免在where条件中对字段进行函数操作；7.对实时性要求不高的报表，允许脏读（with（nolock））。
三.存储过程如果想参考优化设计片段的详细内容，请参阅SQL代码：
1/**
2*某某跟踪报表
3**/
4--execspName1'','','','','','','公司代号'
5CREATEProcedurespName1
6@MESOrderIDnvarchar（320）,--工单号,最多30个
7@LotNamenvarchar（700）,--产品序列号,最多50个
8@DateCodenvarchar（500）,--供应商批次号,最多30个
9@BatchIDnvarchar（700）,--组装件序列号/物料批号,最多50个
10@comdefnvarchar（700）,--组装件物料编码,最多30个
11@SNCustnvarchar（1600）,--外部序列号,最多50个
12@OnPlantnvarchar（20）--平台
13AS
14BEGIN
15SETNOCOUNTON;
16/**
17*1）定义全局的临时表，先根据六个查询条件的任意一个，得出临时表结果
18**/
19CREATETABLE#FinalLotName
20（
21LotNameNVARCHAR（50）,--序列号
22SourceLotNameNVARCHAR（50）,--来源序列号
23SNCustNVARCHAR（128）--外部序列号
24）
25--1.1
26IF@LotName<>''
27BEGIN
28SELECTValINTO#WorkLotFROMfn_String_To_Table（@LotName,',',1）
29SELECTLotPK,LotNameINTO#WorkLotPKFROMMMLotsWITH（NOLOCK）WHEREEXISTS（SELECT1FROM#WorkLotbWHEREb.Val=MMLots.LotID）
30
31--求SourceLotPK只能在这里求
32SELECTa.LotPK,a.SourceLotPKinto#WorkSourcePKFROMMMLotOperationsaWITH（NOLOCK）WHEREEXISTS（SELECT1FROM#WorkLotPKbWHEREb.LotPK=a.LotPK）ANDa.SourceLotPKISNOTNULL
33
34SELECTa.LotPK,a.SourceLotPK,b.LotNameINTO#WorkSourcePK2FROM#WorkSourcePKaJOIN#WorkLotPKbONa.LotPK=b.LotPK
35
36INSERTINTO#FinalLotNameSELECTa.LotName,b.LotNameASSourceLotName,NULLFROM#WorkSourcePK2aJOIN（SELECTLotPK,LotNameFROMMMLotsWITH（NOLOCK））bona.SourceLotPK=b.LotPK--b的里面加不加WHERERowDeleted=0待确定
37SELECTa.LotName,a.SourceLotName,b.SNCustINTO#FinalLotNameX1FROM#FinalLotNameaLEFTJOINCO_SN_LINK_CUSTOMERbWITH（NOLOCK）ONa.LotName=b.SNMes
38DELETEFROM#FinalLotName
39INSERTINTO#FinalLotNameSELECTLotName,SourceLotName,SNCustFROM#FinalLotNameX1
40END
41--1.2
42IF@BatchID<>''
43BEGIN
44SELECTValINTO#WorkSourceLotFROMfn_String_To_Table（@BatchID,',',1）
45IFEXISTS（SELECT1FROM#FinalLotName）--如果@LotName也不为空
46BEGIN
47SELECTa.LotName,a.SourceLotName,a.SNCustINTO#FinalLotNameX2FROM#FinalLotNameaWHEREEXISTS（SELECT1FROM#WorkSourceLotbWHEREa.SourceLotName=b.Val）
48DELETEFROM#FinalLotName
49INSERTINTO#FinalLotNameSELECTLotName,SourceLotName,SNCustFROM#FinalLotNameX2
50END
51ELSE--@LotName条件为空
52BEGIN
53SELECTLotPKASSourceLotPK,LotNameASSourceLotNameINTO#2FROMMMLotsWITH（NOLOCK）WHEREEXISTS（SELECT1FROM#WorkSourceLotbWHEREb.Val=MMLots.LotID）
54SELECTa.LotPK,a.SourceLotPKinto#21FROMMMLotOperationsaWITH（NOLOCK）WHEREEXISTS（SELECT1FROM#2bWHEREb.SourceLotPK=a.SourceLotPK）
55SELECTa.LotPK,a.SourceLotPK,b.SourceLotNameINTO#22FROM#21aJOIN#2bONa.SourceLotPK=b.SourceLotPK
56INSERTINTO#FinalLotNameSELECTb.LotName,a.SourceLotName,NULLFROM#22aJOIN（SELECTLotPK,LotNameFROMMMLotsWITH（NOLOCK））bona.LotPK=b.LotPK--b的里面加不加WHERERowDeleted=0待确定
57SELECTa.LotName,a.SourceLotName,b.SNCustINTO#FinalLotNameX21FROM#FinalLotNameaLEFTJOINCO_SN_LINK_CUSTOMERbWITH（NOLOCK）ONa.LotName=b.SNMes
58DELETEFROM#FinalLotName
59INSERTINTO#FinalLotNameSELECTLotName,SourceLotName,SNCustFROM#FinalLotNameX21
60END
61END
62--1.3
63IF@SNCust<>''
64BEGIN
65SELECTValINTO#WorkCustomSNFROMfn_String_To_Table（@SNCust,',',1）
66IFEXISTS（SELECT1FROM#FinalLotName）--前面两个条件至少有一个有值
67BEGIN
68SELECTa.LotName,a.SourceLotName,a.SNCustINTO#FinalLotNameX3FROM#FinalLotNameaWHEREEXISTS（SELECT1FROM#WorkCustomSNbWHEREa.SNCust=b.Val）
69DELETEFROM#FinalLotName
70INSERTINTO#FinalLotNameSELECTLotName,SourceLotName,SNCustFROM#FinalLotNameX3
71END
72ELSE
73BEGIN
74SELECTa.SNMesINTO#WorkLotXFROMCO_SN_LINK_CUSTOMERaWITH（NOLOCK）WHEREEXISTS（SELECT1FROM#WorkCustomSNbWHEREa.SNCust=b.Val）
75-------------------以下逻辑和变量1（@LotName）类似[先根据外部序列号求解序列号,再照搬第一个判断变量的方式]
76SELECTLotPK,LotNameINTO#WorkLotPKXFROMMMLotsWITH（NOLOCK）WHEREEXISTS（SELECT1FROM#WorkLotXbWHEREb.SNMes=MMLots.LotID）
77
78--求SourceLotPK只能在这里求
79SELECTa.LotPK,a.SourceLotPKinto#WorkSourcePKXFROMMMLotOperationsaWITH（NOLOCK）WHEREEXISTS（SELECT1FROM#WorkLotPKXbWHEREb.LotPK=a.LotPK）ANDa.SourceLotPKISNOTNULL
80
81SELECTa.LotPK,a.SourceLotPK,b.LotNameINTO#WorkSourcePK2XFROM#WorkSourcePKXaJOIN#WorkLotPKXbONa.LotPK=b.LotPK
82
83INSERTINTO#FinalLotNameSELECTa.LotName,b.LotNameASSourceLotName,NULLFROM#WorkSourcePK2XaJOIN（SELECTLotPK,LotNameFROMMMLotsWITH（NOLOCK））bona.SourceLotPK=b.LotPK--b的里面加不加WHERERowDeleted=0待确定
84SELECTa.LotName,a.SourceLotName,b.SNCustINTO#FinalLotNameX31FROM#FinalLotNameaLEFTJOINCO_SN_LINK_CUSTOMERbWITH（NOLOCK）ONa.LotName=b.SNMes
85DELETEFROM#FinalLotName
86INSERTINTO#FinalLotNameSELECTLotName,SourceLotName,SNCustFROM#FinalLotNameX31
87-----------------------
88END
89END
90
91/**
92*2）定义全局的临时表，用于替换第一个全局临时表。
93**/
94CREATETABLE#FinalCO_SN
95（
96SNNVARCHAR（50）,
97SourceSNNVARCHAR（50）,
98SNCustNVARCHAR（128）,
99matl_def_idNVARCHAR（50）,--sn的物料ID
100ComMaterialsNVARCHAR（50）,--SourceSN的物料ID
101MESOrderIDNVARCHAR（20）,
102OnPlantIDNVARCHAR（20）,
103VendorIDNVARCHAR（20）,
104DateCodeNVARCHAR（20）,
105SNNoteNVARCHAR（512）
106）
107--2.1
108IF@MESOrderID<>''
109BEGIN
110-------------------------------将MESOrderID做特殊处理-----------------------------------
111SELECTValINTO#WorkMESOrderIDFROMfn_String_To_Table（@MESOrderID,',',1）
112IF@OnPlant='Comba'
113BEGIN
114UPDATE#WorkMESOrderIDSETVal='C000'+ValWHERELEN（Val）=9
115END
116ELSE
117BEGIN
118UPDATE#WorkMESOrderIDSETVal='W000'+ValWHERELEN（Val）=9
119END
120SELECTSN,MaterialID,MESOrderID,OnPlantIDINTO#WorkCO_SN1FROMCO_SN_GENERATIONaWITH（NOLOCK）
121WHERESNType='IntSN'ANDSNRuleName='ProductSNRule'ANDOnPlantID=@OnPlant
122ANDEXISTS（SELECT1FROM#WorkMESOrderIDbWHEREa.MESOrderID=b.Val）
123------------------------------------------------------------------------------------------
124--条件判断（逻辑分析）开始
125IFEXISTS（SELECT1FROM#FinalLotName）--如果前面判断的查询条件有值
126BEGIN
127--查出SourceLotName对应的查询字段
128SELECTa.SNASSourceLotName,a.VendorID,a.DateCode,a.SNNote,a.MaterialIDASComMaterialsINTO#SourceLotNameTableFROMCO_SN_GENERATIONaWITH（NOLOCK）WHEREEXISTS（SELECT1FROM#FinalLotNamebWHEREa.SN=b.SourceLotName）
129
130INSERTINTO#FinalCO_SN
131SELECTa.LotName,a.SourceLotName,d.SNCust,b.MaterialID,c.ComMaterials,b.MESOrderID,b.OnPlantID,c.VendorID,c.DateCode,c.SNNoteFROM#FinalLotNamea
132LEFTJOIN#WorkCO_SN1bONa.LotName=b.SN
133LEFTJOIN#SourceLotNameTablecONa.SourceLotName=c.SourceLotName
134LEFTJOINCO_SN_LINK_CUSTOMERdWITH（NOLOCK）ONa.LotName=d.SNMes
135END
136ELSE
137BEGIN
138--已知SN集合求解对应的SourceSN和SNCust集合------------------------------------------
139SELECTLotPK,LotNameINTO#WorkLotPK410FROMMMLotsWITH（NOLOCK）WHEREEXISTS（SELECT1FROM#WorkCO_SN1bWHEREb.SN=MMLots.LotID）
140SELECTa.LotPK,a.SourceLotPKinto#WorkSourcePK420FROMMMLotOperationsaWITH（NOLOCK）WHEREEXISTS（SELECT1FROM#WorkLotPK410bWHEREb.LotPK=a.LotPK）ANDa.SourceLotPKISNOTNULL
141SELECTa.LotPK,a.SourceLotPK,b.LotNameINTO#WorkSourcePK430FROM#WorkSourcePK420aJOIN#WorkLotPK410bONa.LotPK=b.LotPK
142INSERTINTO#FinalLotNameSELECTa.LotName,b.LotNameASSourceLotName,NULLFROM#WorkSourcePK430aJOIN（SELECTLotPK,LotNameFROMMMLotsWITH（NOLOCK））bona.SourceLotPK=b.LotPK--b的里面加不加WHERERowDeleted=0待确定
143
144SELECTa.LotName,a.SourceLotName,b.SNCustINTO#FinalLotNameX440FROM#FinalLotNameaLEFTJOINCO_SN_LINK_CUSTOMERbWITH（NOLOCK）ONa.LotName=b.SNMes
145DELETEFROM#FinalLotName
146INSERTINTO#FinalLotNameSELECTLotName,SourceLotName,SNCustFROM#FinalLotNameX440
147-------------------------------------------------------------------------------------
148SELECTa.SNASSourceLotName,a.VendorID,a.DateCode,a.SNNote,a.MaterialIDASComMaterialsINTO#SourceLotNameTable2FROMCO_SN_GENERATIONaWITH（NOLOCK）WHEREEXISTS（SELECT1FROM#FinalLotNamebWHEREa.SN=b.SourceLotName）
149
150INSERTINTO#FinalCO_SN
151SELECTa.LotName,a.SourceLotName,a.SNCust,b.MaterialID,c.ComMaterials,b.MESOrderID,b.OnPlantID,c.VendorID,c.DateCode,c.SNNoteFROM#FinalLotNamea
152LEFTJOIN#WorkCO_SN1bONa.LotName=b.SN
153LEFTJOIN#SourceLotNameTable2cONa.SourceLotName=c.SourceLotName
154EN
升级会员 