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

黃光慈，周菊，萬(wàn)林林，曹希龍

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

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

1.1 表的設(shè)計(jì)

表（Table）是數(shù)據(jù)庫(kù)的基本結(jié)構(gòu)，表中能夠存儲(chǔ)不同類(lèi)型的字段，如整形（int）、浮點(diǎn)型（float）等等。對(duì)于需要保存數(shù)據(jù)，可以根據(jù)數(shù)據(jù)的類(lèi)型來(lái)設(shè)計(jì)表。根據(jù)筆者這幾年的開(kāi)發(fā)經(jīng)驗(yàn)，總結(jié)出了以下幾條表設(shè)計(jì)的原則：

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

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

圖1 中各個(gè)字段的釋義如下：

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

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

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

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

（2）某些類(lèi)型的對(duì)象，可能會(huì)有一個(gè)唯一的標(biāo)識(shí)，這個(gè)唯一的標(biāo)識(shí)在數(shù)據(jù)庫(kù)中被稱(chēng)為主鍵。主鍵具有唯一性和不可修改性，就像是人的身份證號(hào)一樣。如在圖1 中，Produce_Missions 的第一個(gè)字段 Code，就是該表的主鍵。

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

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

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

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

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

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

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

圖2 中各個(gè)字段的釋義如下：

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

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

圖3 GUID 列內(nèi)容

1.2 信息相關(guān)表的設(shè)計(jì)

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

首先，在主表中，必須要有該行數(shù)據(jù)的唯一標(biāo)識(shí)，即主鍵。因?yàn)橹麈I是該行內(nèi)容的唯一代表，因此可以將主鍵的信息放到附表中，附表通過(guò)該表中的主鍵信息的備份查詢(xún)到對(duì)應(yīng)的主表中的內(nèi)容。

其次，附表可以有主鍵，也可以沒(méi)有。如果有，則不能把附表中主鍵信息的備份作為主鍵，因此會(huì)導(dǎo)致附表的信息也不能重復(fù)，由此將無(wú)法滿(mǎn)足客戶(hù)的需求。此時(shí)推薦使用上文所述的自增列來(lái)滿(mǎn)足附表對(duì)主鍵的需求。

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

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

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

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

2 數(shù)據(jù)庫(kù)的應(yīng)用

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

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

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

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

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'

查詢(xún)的結(jié)果如下：

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

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

2.2 適當(dāng)增加索引來(lái)提高查詢(xún)效率

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

打個(gè)比方，在一個(gè)圖書(shū)館中，有那么多書(shū)，怎么管理呢？我們可以建立一個(gè)字母開(kāi)頭的目錄，例如：a 開(kāi)頭的書(shū)，在第一排，b 開(kāi)頭的在第二排，這樣在找什么書(shū)就好說(shuō)了，這個(gè)就是一個(gè)索引。或者我們可以再寫(xiě)一個(gè)目錄，按照作者進(jìn)行排序，顯示某某作者的書(shū)分別在第幾排，這同樣也是一個(gè)索引。

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

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

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

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

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

（4）對(duì)于那些查詢(xún)中很少涉及的列，重復(fù)值比較多的列不要建立索引。

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

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

3 總結(jié)

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