淺析SQL Server數(shù)據(jù)庫在攪拌站控制系統(tǒng)中的設計與應用

黃光慈，周菊，萬林林，曹希龍

（徐州徐工施維英機械有限公司，江蘇 徐州 221004）

1 數(shù)據(jù)庫的設計

1.1 表的設計

表（Table）是數(shù)據(jù)庫的基本結構，表中能夠存儲不同類型的字段，如整形（int）、浮點型（float）等等。對于需要保存數(shù)據(jù)，可以根據(jù)數(shù)據(jù)的類型來設計表。根據(jù)筆者這幾年的開發(fā)經(jīng)驗，總結出了以下幾條表設計的原則：

（1）一個表就只存儲屬于同一個類對象的數(shù)據(jù)。以某品牌的攪拌站控制系統(tǒng)為例，為了存儲攪拌站的生產(chǎn)任務，專門設計如下表，用來存儲生產(chǎn)任務的數(shù)據(jù)。如圖1 所示。

圖1 某控制系統(tǒng)的 Produce_Missions 表

圖1 中各個字段的釋義如下：

字段名 中文釋義 備注Code 任務編號DeliveryCode 交付任務編號 該任務所隸屬的交付任務編號LineCode 生產(chǎn)線編號 用以區(qū)別不同的生產(chǎn)線IsMortar 是否砂漿 指示該任務是否為生產(chǎn)砂漿ExpectedQuantity 期望生產(chǎn)方量Status 狀態(tài)FinishFlag 完成標志位FormulaCode 配方編號StartTime 任務開始時間FinishTime 任務結束時間ActualCycleCount 實際盤次數(shù)量ActualQuantity 實際生產(chǎn)方量Cache_Contract OrderCode 合同編號Cache_Contrace OrderItemCode 合同項編號Cache_ProjectName 項目名稱Cache_ProjecetAddress 項目地址Cache_CustomerCode 客戶編號Cache_CustomerName 客戶名稱Cache_PlanCode 計劃編號 該任務所隸屬的生產(chǎn)計劃編號Cache_Strenght 強度數(shù)值Cache_Slump 坍落度Cache_ConcreteSign 完整的強度信息Cache_Driver 司機Cache_CarCode 車號Cache_CastPart 澆筑部位

從圖1 可以看出，該表的名稱為“Produce_Missions”，并且擁有多個不同類型的字段，用來存儲重要的數(shù)據(jù)，比如混凝土的強度、坍落度和澆筑部位等。在設置字段的名稱時，最好將字段的名稱與實際所要存儲的字段意義相匹配，這樣會增加數(shù)據(jù)庫的可讀性。比如在上表中，項目名稱就用“Cache_Project Name”字段表示，強度則使用“Cache_Strength”表示。

如果有多個類對象的數(shù)據(jù)需要存儲，則可以設計多個表來存。如在攪拌站控制系統(tǒng)中，需要有生產(chǎn)訂單、生產(chǎn)計劃和生產(chǎn)任務三個類型的對象需要存儲，則可以分別設計三個不同的表，以此來滿足用戶的需求。

那么可不可在一個表中存儲生產(chǎn)訂單、生產(chǎn)計劃和生產(chǎn)任務的對象呢？答案是可以的，但是嚴重不推薦這樣做。因為這樣在查詢、修改和插入數(shù)據(jù)時會帶來很大的不便，而且這也會對閱讀數(shù)據(jù)庫造成比較大的困難。同時，如果一個表含有過多的字段，在對表進行操作時電腦資源的占用也會比較大。因此，在設計表時，最好一個表只用來存儲一個類型的對象的數(shù)據(jù)。

（2）某些類型的對象，可能會有一個唯一的標識，這個唯一的標識在數(shù)據(jù)庫中被稱為主鍵。主鍵具有唯一性和不可修改性，就像是人的身份證號一樣。如在圖1 中，Produce_Missions 的第一個字段 Code，就是該表的主鍵。

主鍵的唯一性有兩個含義。其一，具有主鍵的表，在向其中插入內(nèi)容時應該首先檢查要插入的主鍵是否已經(jīng)存在，然后再執(zhí)行插入操作，否則有可能會造成插入失敗。假如在圖1的表中已經(jīng)存在一個任務的編號為 25，如果再次插入一個編號為 25 的任務，將會產(chǎn)生一個主鍵重復的 SQL 異常，同時本次插入操作也將失敗。因此在插入之前，需要檢查要插入的編號是否存在，可以使用如下 SQL 語句進行查詢：

Select count(Code) from Produce_Missions where Code= '25'

如果該編號已經(jīng)存在，則將會返回的結果大于 0；如果編號不存在，則返回的結果為 0。

其二，主鍵的唯一性也指的是一個表只有能一個字段作為它的主鍵。

主鍵的不可修改性指的是一旦向主鍵中插入了內(nèi)容之后，將無法修改其中的內(nèi)容。

主鍵根據(jù)實際需要，可以是由程序指定的內(nèi)容，也可以是 SQL Server 自動生成的內(nèi)容。SQL 自動生成的主鍵編號，一般都是從 1 開始的整數(shù)疊加，即每次插入一行，編號自動加 1。使用這種方式時，無需向主鍵插入內(nèi)容，只需向改行的其他自動插入數(shù)據(jù)，主鍵的編號會自動生成。例如，在圖2 中所示的是某攪拌站控制系統(tǒng)的盤次信息表，其中的第一列 ID，即為 SQL 自增列。

圖2 某系統(tǒng)的 Recorder_MissionStatements 表

圖2 中各個字段的釋義如下：

字段名 中文釋義 備注ID 自增列 數(shù)據(jù)庫中的自增列序號指示改行數(shù)據(jù)所隸屬的父項編號，與圖1 中的Code 字段相對應LineCode 生產(chǎn)線編號 用以區(qū)別不同的生產(chǎn)線UnitIndex 盤次序號CreateTime 創(chuàng)建時間QuantityPerCycle 當前盤次方量Cache_Contract OrderCode 合同編號Cache_Contrace OrderItemCode 合同項編號Cache_ProjectName 項目名稱Cache_CustomerCode 客戶編號Cache_CustomerName 客戶名稱Cache_Strenght 強度數(shù)值Cache_SlumpRated 坍落度SetMixTime 預設的攪拌時間ActualMixTime 實際攪拌時間ParentCode 父項編號

除了以上兩種方式的主鍵外，主鍵還可以是 GUID列。GUID 同樣表示一行數(shù)據(jù)唯一的 ID，只是 GUID 是由計算機生成的標識列，雖然保證了唯一性，但是卻由一串完全無法找到規(guī)律的字母數(shù)字組成，容易給閱讀造成困難，因此除非必須要使用 GUID 列，否則一般不推薦使用該種方式的主鍵。筆者做了一個小實驗，由計算機生成十次 GUID 標識，生成的主鍵內(nèi)容如圖3 所示。

圖3 GUID 列內(nèi)容

1.2 信息相關表的設計

剛才所介紹的方式，都是表中的一行數(shù)據(jù)，就可以表示一個對象的基本內(nèi)容。但是有時候我們會碰到，一個對象的全部內(nèi)容需要從不同的表中獲取，比如在某控制系統(tǒng)中，一車生產(chǎn)完成之后會產(chǎn)生各種物料的消耗，而這些物料的消耗卻與該車的基本任務信息存儲在不同的表中，這樣該如何設計表呢？

首先，在主表中，必須要有該行數(shù)據(jù)的唯一標識，即主鍵。因為主鍵是該行內(nèi)容的唯一代表，因此可以將主鍵的信息放到附表中，附表通過該表中的主鍵信息的備份查詢到對應的主表中的內(nèi)容。

其次，附表可以有主鍵，也可以沒有。如果有，則不能把附表中主鍵信息的備份作為主鍵，因此會導致附表的信息也不能重復，由此將無法滿足客戶的需求。此時推薦使用上文所述的自增列來滿足附表對主鍵的需求。

例如在圖2 所示的表中，該表的 ParentCode 列所存儲的內(nèi)容，即為該表對應的主表的信息，通過該字段就可以獲取到主表中對應的信息。如在主表（圖1 所示的表）中，主鍵信息為“Plan_1_2_1”，可以查到一行數(shù)據(jù)（圖4），在附表（圖2 所示的表）中，可以查到三行數(shù)據(jù)（圖5），這四行數(shù)據(jù)完全可以通過 ParentCode字段獲取到全部的內(nèi)容。

圖4 主鍵信息為 "plan_1_2_1" 在主表中的搜索結果

圖5 主鍵信息為 "plan_1_2_1" 在附表中的搜索結果

在附表中設置主鍵字段內(nèi)容的備份，還有一個好處是可以在多表之間進行連接，SQL 提供了多種手動可以時間多表聯(lián)合查詢，如內(nèi)連接、外連接等。具體的內(nèi)容將在后面的部分進行敘述。

2 數(shù)據(jù)庫的應用

除了生產(chǎn)之外，攪拌站控制系統(tǒng)還必須要給用戶提供詳細的報表查詢功能，大到項目方量、材料用量，小到每車每盤的生產(chǎn)誤差，必須能夠完整、準確、快速地呈獻給客戶。因此，報表功能是控制系統(tǒng)中重要的組成部分。

2.1 多表聯(lián)合查詢

在 1.2 所述的例子中，只需要知道任務的編號，就可以分別從 Produce_Missions 和 Recorder_Mission Statements 表中查找到需要對信息。但是這種方式需要查詢兩次，在數(shù)據(jù)量大時會消耗比較長的時間。那么有沒有一種比較節(jié)約的方式呢？

答案是有的。使用 SQL Server 提供的內(nèi)連接方式查詢即可一次從兩個表中查詢出需要的信息。內(nèi)連接時需要指定兩個表中需要查詢的字段，同時指定內(nèi)連接的條件即可完成一次內(nèi)連接。例如在 1.2 的例子中，使用一條語句即可完成對兩個表的聯(lián)合查詢，SQL 語句如下：

Select Recorder_Mission Statements.Parent Code,Recorder_Mission Statements.Line Code,Recorder_Mission Statements.Quantity Per Cycle,Produce_Missions.formulacode from Recorder_Mission Statements inner join Produce_Missions on Recorder_Mission Statements.parentcode = Produce_Missions.code where Recorder_Mission Statements.parentcode = 'Plan_1_2_1'

查詢的結果如下：

圖6 內(nèi)連接查詢

為了節(jié)省頁面空間，在上面的例子中只選擇了Recorder_MissionStatements 表中的三個字段（圖6 中的前三列）和 Produce_Missions 表中的一個字段（圖6 中的最后一列）。內(nèi)連接也可以進行多層嵌套，但是嵌套的次數(shù)越多，對電腦性能的消耗越大，因此一般情況下除非必要，否則最好不要使用多層嵌套內(nèi)連接進行查詢。

2.2 適當增加索引來提高查詢效率

在現(xiàn)在的電腦硬件發(fā)展水平，對于數(shù)據(jù)量不多的數(shù)據(jù)庫而言，一般的查詢等待時間不會太久。但是隨著時間的增加，運行數(shù)據(jù)必然會越來越多，查詢等待時間也必然會延長。為了減少用戶的等待時間，我們可以向數(shù)據(jù)庫中某些關鍵字段增加索引，索引可以大大提供 SQL查詢的效率，降低用戶的等待時間。因此，好的數(shù)據(jù)庫必須要有好的索引設置來支撐。那么，什么是索引，又該如何使用索引呢？

打個比方，在一個圖書館中，有那么多書，怎么管理呢？我們可以建立一個字母開頭的目錄，例如：a 開頭的書，在第一排，b 開頭的在第二排，這樣在找什么書就好說了，這個就是一個索引?；蛘呶覀兛梢栽賹懸粋€目錄，按照作者進行排序，顯示某某作者的書分別在第幾排，這同樣也是一個索引。

索引是一類在物理上連續(xù)或邏輯上連續(xù)的數(shù)據(jù)，有索引的數(shù)據(jù)庫在查詢時使用索引字段能夠大大的提高查詢效率。因為這些數(shù)據(jù)都是在內(nèi)部按照一定規(guī)律排列好，只需直接從索引中查找就能完成。比如，將Produce_Missions 表的客戶字段設為索引，設為索引之后查詢的效率有了 20%～30% 左右的提升。查詢語句如下：

索引雖然能夠提升查詢的效率，但是索引太多也對會系統(tǒng)造成負擔。因此，對于什么樣的字段能夠創(chuàng)建索引，也有一些通用的規(guī)則。這些規(guī)則都是經(jīng)過前人大量的實踐而總結出來的，因此在設計索引時務必遵循以下幾條原則：

（1）對于經(jīng)常查詢的數(shù)據(jù)列最好建立索引。

（2）對于需要在指定范圍內(nèi)的快速或頻繁查詢的數(shù)據(jù)列。

（3）經(jīng)常用在 WHERE 子句中的數(shù)據(jù)列。經(jīng)常出現(xiàn)在關鍵字 order by、group by、distinct 后面的字段，建立索引。如果建立的是復合索引，索引的字段順序要和這些關鍵字后面的字段順序一致，否則索引不會被使用。

（4）對于那些查詢中很少涉及的列，重復值比較多的列不要建立索引。

（5）對于定義為 text、image 和 bit 的數(shù)據(jù)類型的列不要建立索引。

（6）對于經(jīng)常存取的列避免建立索引。

3 總結

數(shù)據(jù)庫是控制系統(tǒng)的記憶系統(tǒng)，如何能夠設計出良好的數(shù)據(jù)庫和如何能夠快速地從數(shù)據(jù)庫中查詢數(shù)據(jù)，是軟件開發(fā)者在開發(fā)軟件時重要指標。本文粗略的列出了筆者在數(shù)據(jù)庫的設計與應用中總結的經(jīng)驗，希望廣大的軟件開發(fā)者能夠從中獲取到自己所需的內(nèi)容。軟件優(yōu)化與發(fā)展之路永無止境，愿與各位軟件開發(fā)者共勉！