記一次苦逼的SQL查詢優化
最近在維護公司項目時，需要加載某頁面，總共加載也就4000多條數據，竟然需要35秒鐘，要是數據增長到40000條，我估計好幾分鐘都搞不定。臥槽，要我是用戶的話估計受不了，趁閑著沒事，就想把它優化一下，走你。
先把查詢貼上：
select Pub_AidBasicInformation.AidBasicInfoId,
Pub_AidBasicInformation.UserName,
Pub_AidBasicInformation.District,
Pub_AidBasicInformation.Street,
Pub_AidBasicInformation.Community,
Pub_AidBasicInformation.DisCard,
Pub_Application.CreateOn AS AppCreateOn,
Pub_User.UserName as DepartmentUserName,
Pub_Consult1.ConsultId,
Pub_Consult1.CaseId,
Clinicaltb.Clinical,AidNametb.AidName,
Pub_Application.IsUseTraining,
Pub_Application.ApplicationId,
tab.num
FROM Pub_Consult1
INNER JOIN Pub_Application ON Pub_Consult1.ApplicationId = Pub_Application.ApplicationId
INNER JOIN Pub_AidBasicInformation ON Pub_Application.AidBasicInfoId = Pub_AidBasicInformation.AidBasicInfoId
INNER JOIN(select ConsultId,dbo.f_GetClinical(ConsultId) as Clinical
from Pub_Consult1) Clinicaltb on Clinicaltb.ConsultId=Pub_Consult1.ConsultId
left join (select distinct ApplicationId, sum(TraniningNumber) as num from dbo.Review_Aid_UseTraining_Record where AidReferralId is null group by ApplicationId) tab on tab.ApplicationId=Pub_Consult1.ApplicationId
INNER JOIN(select ConsultId,dbo.f_GetAidNamebyConsult1(ConsultId) as AidName from Pub_Consult1) AidNametb on AidNametb.ConsultId=Pub_Consult1.ConsultId
LEFT OUTER JOIN Pub_User ON Pub_Application.ReviewUserId = Pub_User.UserId
WHERE Pub_Consult1.Directory = 0
order by Pub_Application.CreateOn desc 執行后有圖有真相：
這么慢，沒辦法就去看看查詢計劃是怎么樣：
這是該sql查詢里面執行三個函數時生成查詢計劃的截圖，一看就知道，執行時開銷比較大，而且都是花費在聚集索引掃描上，把鼠標放到聚集索引掃描的方塊上面，依次看到如下詳細計劃：
從這幾張圖里，可以看到查詢I/O開銷，運算符開銷，估計行數，以及操作的對象和查詢條件，這些都為優化查詢提供了有利證據。第1，3張圖IO開銷比較大，第2張圖估計行數比較大，再根據其它信息，首先想到的應該是去建立索引，不行的話再去改查詢。
先看看數據庫引擎優化顧問能給我們提供什么優化信息，有時候它能夠幫我們提供有效的信息，比如創建統計，索引，分區什么的。
先打開SQL Server Profiler 把剛剛執行的查詢另存為跟蹤(.trc)文件,再打開數據庫引擎優化顧問，做如下圖操作
最后生成的建議報告如下：
在這里可以單擊查看一些建議，分區，創建索引，根據提示創建了如下索引：
CREATE NONCLUSTERED INDEX index1 ON [dbo].[Pub_AidBasicInformation]
(
[AidBasicInfoId] ASC
)
CREATE NONCLUSTERED INDEX index1 ON [dbo].[Pub_Application]
(
[ApplicationId] ASC,[ReviewUserId] ASC,[AidBasicInfoId] ASC,[CreateOn] ASC
)
CREATE NONCLUSTERED INDEX index1 ON [dbo].[Pub_Consult1]
(
[Directory] ASC,[ApplicationId] ASC
)
CREATE NONCLUSTERED INDEX idnex1 ON [dbo].[Review_Aid_UseTraining_Record]
(
[AidReferralId] ASC,[ApplicationId] ASC
)索引創建后，再次執行查詢，原以為可提高效率，沒想到我勒個去，還是要30幾秒，幾乎沒什么改善，優化引擎顧問有時候也會失靈，在這里只是給大家演示有這種解決方案去解決問題，有時候還是靠譜的，只是這次不靠譜。沒辦法，只有打開函數仔細瞅瞅，再結合上面的查詢計劃詳細圖，刪除先前創建的索引，然后創建了如下索引：
CREATE NONCLUSTERED INDEX index1 ON dbo.Report_AdapterAssessment_Aid
(
AdapterAssessmentId ASC, ProductDirAId ASC
)
CREATE NONCLUSTERED INDEX index1 ON dbo.Report_AdapterAssessment
(
ConsultId ASC
)再次執行查詢
好了，只需3.5秒，差不多提高10倍速度，看來這次是湊效了哈。
再來看看查詢計劃是否有改變，上張圖來說明下問題：
從上圖當中我們可以看到，索引掃描不見了，只有索引查找，聚集索引查找，鍵查找，而且運算符開銷，I/O開銷都降低了很多。索引掃描(Index Scan),聚集索引掃描(Clustered Index Scan)跟表掃描(Table Scan)差不多，基本上是逐行去掃描表記錄，速度很慢，而索引查找(Index Seek)，聚集索引查找，鍵查找都相當的快。優化查詢的目的就是盡量把那些帶有XXXX掃描的去掉，換成XXXX查找。
這樣夠了嗎？但是回頭又想想，4000多條數據得3.5秒鐘，還是有點慢了，應該還能再快點，所以決定再去修改查詢。看看查詢，能優化的也只有那個三個函數了。
為了看函數執行效果先刪除索引，看看查詢中函數f_GetAidNamebyConsult1要干的事情，截取查詢中與該函數有關的子查詢：
select Pub_Consult1.ConsultId,AidName from (select ConsultId,dbo.f_GetAidNamebyConsult1(ConsultId) as AidName from Pub_Consult1) AidNametb inner join Pub_Consult1 on AidNametb.ConsultId=Pub_Consult1.ConsultId
得到下圖的結果：
沒想到就這么點數據竟然要46秒，看來這個函數真的是罪魁禍首。
該函數的具體代碼就不貼出來了，而且該函數里面還欠套的另外一個函數，本身函數執行起來就慢，更何況還函數里子查詢還包含函數。其實根據幾相關聯的表去查詢幾個字段，并且把一個字段的值合并到同一行，這樣沒必要用函數或存儲過程，用子查詢再加sql for xml path就行了，把該函數改成如下查詢：
with cte1 as
(
select A.AdapterAssessmentId,case when B.AidName is null then A .AidName else B.AidName end AidName
from Report_AdapterAssessment_Aid as A left join Pub_ProductDir as B
on A.ProductDirAId=B.ProductDirAId
),
cte2 as
(
--根據AdapterAssessmentId分組并合并AidName字段值
select AdapterAssessmentId,(select AidName+',' from cte1
where AdapterAssessmentId= tb.AdapterAssessmentId
for xml path(''))as AidName
from cte1 as tb
group by AdapterAssessmentId
),
cte3 as
(
select ConsultId,LEFT(AidName,LEN(AidName)-1) as AidName
from
(
select Pub_Consult1.ConsultId,cte2.AidName from Pub_Consult1,Report_AdapterAssessment,cte2
where Pub_Consult1.ConsultId=Report_AdapterAssessment.ConsultId
and Report_AdapterAssessment.AdapterAssessmentId=cte2.AdapterAssessmentId
and Report_AdapterAssessment.AssessTuiJian is null
) as tb)這樣查詢出來的結果在沒有索引的情況下不到1秒鐘就行了。再把主查詢寫了：
select distinct Pub_AidBasicInformation.AidBasicInfoId,
Pub_AidBasicInformation.UserName,
Pub_AidBasicInformation.District,
Pub_AidBasicInformation.Street,
Pub_AidBasicInformation.Community,
Pub_AidBasicInformation.DisCard,
Pub_Application.CreateOn AS AppCreateOn,
Pub_User.UserName as DepartmentUserName,
Pub_Consult1.ConsultId,
Pub_Consult1.CaseId,
Clinicaltb.Clinical,
cte3.AidName,
Pub_Application.IsUseTraining,
Pub_Application.ApplicationId,
tab.num
from Pub_Consult1
INNER JOIN Pub_Application ON Pub_Consult1.ApplicationId = Pub_Application.ApplicationId
INNER JOIN Pub_AidBasicInformation ON Pub_Application.AidBasicInfoId = Pub_AidBasicInformation.AidBasicInfoId
INNER JOIN(select ConsultId,dbo.f_GetClinical(ConsultId) as Clinical
from Pub_Consult1) Clinicaltb on Clinicaltb.ConsultId=Pub_Consult1.ConsultId
left join (select distinct ApplicationId, sum(TraniningNumber) as num from dbo.Review_Aid_UseTraining_Record
where AidReferralId is null
group by ApplicationId) tab
on tab.ApplicationId=Pub_Consult1.ApplicationId
left JOIN cte3 on cte3.ConsultId=Pub_Consult1.ConsultId
LEFT OUTER JOIN Pub_User ON Pub_Application.ReviewUserId = Pub_User.UserId
where Pub_Consult1.Directory = 0
order by Pub_Application.CreateOn desc這樣基本上就完事了，在沒有建立索引的情況下需要8秒鐘，比沒索引用函數還是快了27秒。
把索引放進去，就只需1.6秒了，比建立索引用函數而不用子查詢和sql for xml path快了1.9秒
查詢里面還有個地方用了函數，估計再優化下還能提高執行效率，因為時間有限再加上篇幅有點長了，在這里就不多講了。
最后做個總結吧，查詢優化不外乎以下這幾種辦法：
1：增加索引或重建索引。通常在外鍵，連接字段，排序字段，過濾查詢的字段建立索引，也可通過數據庫引擎優化顧問提供的信息去建索引。有時候當你創建索引時，會發現查詢還是按照索引掃描或聚集索引掃描的方式去執行，而沒有去索引查找，這時很可能是你的查詢字段和where條件字段沒有全部包含在索引字段當中，解決這個問題的辦法就是多建立索引，或者在創建索引時Include相應的字段，讓索引字段覆蓋你的查詢字段和where條件字段。
2：調整查詢語句，前提要先看懂別人的查詢，搞清楚業務邏輯。
3：表分區，大數據量可以考慮。
4：提高服務器硬件配置。