一個大資料量表訪問最佳化–聯動下拉框查詢最佳化

問題描述
有一資料表(產品標籤表,每個產品一個唯一的SN，每月100萬左右）,查詢介面上有2個聯動下拉框，【規格】____,【批次】______
使用者選擇一個規格後（目前200來個規格），列出該規格下達過的生產計劃的批次。

原有方式
規格列表
select 規格 from 標籤表 group by 規格

根據規格擷取批次
select 批次 From 標籤表 Where 規格=‘某一規格’ group by 批次

由於要進行全表掃描,當資料到50萬以上時速度就明顯慢下來了，而到200萬時上面任意一個查詢都要1分鐘左右。

 

解決方案

方案1：建立索引
組合“規格，批次”建立一個索引（批次單獨建立了叢集索引）。
問題，因為一條記錄對應一個索引條目，所以這個方案需要很多額外的空間，另外原來表上已經有3個索引了，過多的索引會導致更新與插入效能下降，並且死結風險會提高，作為流水線上使用的模組，性是有要求的。

方案2：建立一張規格批次對應表，記錄規格跟批次的對關係
由於規格200來個並且比較穩定，批次一月也就100來個，而且這100個批次只分配給二三十個批次所以這兩者的組合一個月也就1500-2000條左右。

考慮使用規格批次對應表後，進一步就是確定怎麼維護對應表的資料，
方式一，就是每次生產任務分解產生標籤的同時更新規格批次對應表（即標籤表記錄的增刪改時做對應的操作）。考慮那個任務分解代碼已經夠糾結了，不打算大量修改程式碼。

方式二，資料庫建立個作業,定期累加式更新
生產任務會做調整(刪除或更改)，不過變動幾率不高，而且多數改動都在上班時間內任務下達後1-2小時內修改（隔天的基本已經在執行狀態或已經完成了）
所以作業安排在晚上12點進行，而作業執行點之後標籤表新增記錄的規格與批次的對應關係則沒包括在對應表中，因此下拉框的查詢結果來自兩部，作業執行點前的規格批次對應關係來自對應表，而作業執行點後規格批次對應關係則直接查詢標籤表，由於每次作業執行點都記錄當前統計時最大的記錄ID號，因此查詢標籤表時會使用如下的查詢語句:
Select  批次 From 標籤表 Where 規格='某一規格' And Id>xxxx ,由於在Id上建了索引，而每天心記錄在3,4萬條，所以這個查詢在執行時間上基本穩定。

完成的代碼類似下面:

View Code

ALTER Proc [dbo].[Get批次By規格]@規格 nvarchar(20) As Create Table #t_CT_BNO_MAP_tmp (   批次 nvarchar(20) )  Declare @MaxLblId bigint Declare @SQL nvarchar(2000) Set @MaxLblId=0 select @MaxLblId= Max(LblId) From 規格批次對應表 Set @SQL='    select 批次 From  (  Select 批次 from 標籤表  where Id>='+cast(@MaxLbLId as nvarchar(50))+' And  規格='''+@規格+'''  group by 批次    Union  Select 批次  From 規格批次對應表  where 規格='''+ @規格+'''   ) as t group by 批次'Insert Into #t_CT_BNO_MAP_tmpExec(@SQL)Select * from #t_CT_BNO_MAP_tmpDrop Table #t_CT_BNO_MAP_tmp

注意點：
上面採用了動態SQL來執行包含" Id>='+cast(@MaxLbLId as nvarchar(50))+" 的語句來擷取標籤表中的規格批次對應關係，如果不採用動態SQL,直接使用
Id>@MaxLblId的條件，那麼由於是預存程序MSSQL查詢最佳化工具不清楚MaxLblId可能是多少，而忽略Id上已建的索引，而進行全表掃描。
兩者的執行過程如:
(靜態SQL語句)

(動態SQL語句)

作業任務代碼:

View Code

declare @MaxLblId bigintDeclare @SQL nvarchar(2000)Set @MaxLblId=0select @MaxLblId= Max(LblId) From dbo.規格批次表Set @SQL='Insert Into 規格批次表(規格,batno,LblId)select 規格,Batno,LblIdFrom (    Select 規格,batno Batno,Max(Id) LblId from 標籤表    where Id>='+cast(@MaxLbLId as nvarchar(50))+'    group by 規格, batno        Except    Select 規格,Batno,LblId    From KB_Lable_CT_BNO_MAP     ) as t'Exec(@SQL)