三、設(shè)計(jì)與應(yīng)用題

　　45在進(jìn)行某學(xué)校教務(wù)管理系統(tǒng)的數(shù)據(jù)庫(kù)設(shè)計(jì)時(shí)，數(shù)據(jù)庫(kù)設(shè)計(jì)人員設(shè)計(jì)了如下幾個(gè)關(guān)系模式：

　　系(系號(hào)，系名)，系號(hào)為主碼

　　學(xué)生(學(xué)號(hào)，姓名，所在系號(hào))，學(xué)號(hào)為主碼

　　課程(課程號(hào)，課程名，開(kāi)課系號(hào))，課程號(hào)為主碼

　　選課(學(xué)號(hào)，課程號(hào)，選課時(shí)間)，學(xué)號(hào)和課程號(hào)為主碼

　　開(kāi)發(fā)人員在將關(guān)系模式實(shí)施到SQL Server 2008的“教務(wù)”數(shù)據(jù)庫(kù)時(shí)，使用了如下表結(jié)構(gòu)定義語(yǔ)句：

　　CREATE TABLE系(

　　系號(hào)varchar(10)NOT NULL，

　　系名varchar(100)

　　)

　　CREATE TABLE學(xué)生(

　　學(xué)號(hào)varchar(50)NOT NULL，

　　姓名varchar(50)，

　　所在系號(hào)varchar(10)

　　)

　　CREATE TABLE課程(

　　課程號(hào)varchar(50)NOT NULL，

　　課程名varchar(100)，

　　開(kāi)課系號(hào)varchar(10)

　　)

　　CREATE TABLE選課(

　　學(xué)號(hào)varchar(50)NOT NULL，

　　課程號(hào)varchar(50)NOT NULL，

　　選課時(shí)間datetime

　　)

　　在執(zhí)行如下查詢語(yǔ)句時(shí)發(fā)現(xiàn)執(zhí)行效率很低：

　　SELECT *FROM選課JOIN學(xué)生0N學(xué)生.學(xué)號(hào)=選課.學(xué)號(hào)

　　JOIN系ON系.系號(hào)=學(xué)生.所在系號(hào)

　　JOIN課程0N課程.課程號(hào)=選課.課程號(hào)

　　WHERE系.系號(hào)=ˊ012 ˊ

　　AND convert(vvarchar(10)，選課時(shí)間，120)>=ˊ2010-01-01ˊ

　　(1)在查找原因時(shí)發(fā)現(xiàn)建表語(yǔ)句有問(wèn)題。請(qǐng)指出問(wèn)題并說(shuō)明該問(wèn)題是否會(huì)影響此查詢語(yǔ)句的執(zhí)行效率。

　　(2)設(shè)已在“選課”表的“選課時(shí)間”列及“學(xué)生”表的“所在系號(hào)”列上建立了索引。請(qǐng)問(wèn)這兩個(gè)索引是否能夠提高該查詢語(yǔ)句的執(zhí)行效率?如果不能，請(qǐng)說(shuō)明原因。

　　參考解析：

　　(1)【解題思路】

　　本題中查詢語(yǔ)句的功能是得到12系全體學(xué)生在2010年1月1日后的選課情況的匯總表。在每個(gè)數(shù)據(jù)表的定義時(shí)都必須嚴(yán)格定義表中的完整性約束條件，包括主鍵的設(shè)置，否則之后會(huì)出現(xiàn)主鍵有相同值的情況，破壞了數(shù)據(jù)的完整性。

　　【參考答案】

　　建表時(shí)沒(méi)有設(shè)置主鍵，也沒(méi)有說(shuō)明外鍵，但不會(huì)影響此查詢語(yǔ)句的執(zhí)行效率。

　　(2)【解題思路】

　　“選課”表的“選課時(shí)問(wèn)”列上建立了索引，從而能夠提高執(zhí)行效率。經(jīng)常出現(xiàn)在Where子句中的字段，特別是大表的字段，應(yīng)該建立索引。索引的作用就類似于書的目錄，即會(huì)按照章節(jié)的順序排列。因此如果在一本數(shù)百頁(yè)的書里面查找某個(gè)章節(jié)位置的時(shí)候，就可以只掃描書的目錄。掃描的范圍縮了n倍，查詢的效率自然就會(huì)提高。另外，在SQL Server內(nèi)存夠用的情況下，索引會(huì)被放到內(nèi)存中，在內(nèi)存中查找自然又會(huì)提高效率，所以必須合理利用索引。

　　【參考答案】

　　“選課”表的“選課時(shí)問(wèn)”可以建立索引，從而提高了查詢效率，而“學(xué)生”表的“所在系號(hào)”建立索引不會(huì)提高查詢效率。索引的意義就是將記錄按目標(biāo)關(guān)鍵字順序排列，這樣查找某個(gè)目標(biāo)關(guān)鍵字的對(duì)應(yīng)值的位置就縮小了查找范圍�！斑x課時(shí)間”的重復(fù)率低，所以可以作為索引，而學(xué)生“所在系號(hào)”的重復(fù)率太高，則不會(huì)提高查詢效率。

　　46某采購(gòu)中心采購(gòu)了一套商品批發(fā)查詢管理系統(tǒng)，此系統(tǒng)采用SQLServer 2008數(shù)據(jù)庫(kù)管理系統(tǒng)，該系統(tǒng)需要經(jīng)常處理百萬(wàn)級(jí)以上的數(shù)據(jù)查詢。同時(shí)該系統(tǒng)提供了第三方人員開(kāi)發(fā)的SQL接口，第三方人員可以根據(jù)自己的需要開(kāi)發(fā)自己的應(yīng)用程序來(lái)訪問(wèn)數(shù)據(jù)庫(kù)中的相關(guān)數(shù)據(jù)。

　　(1)系統(tǒng)在使用的過(guò)程中，業(yè)務(wù)人員反應(yīng)系統(tǒng)操作速度很慢。經(jīng)過(guò)工程師檢查測(cè)試后，數(shù)據(jù)庫(kù)系統(tǒng)本身及網(wǎng)絡(luò)傳輸過(guò)程中存在著一些問(wèn)題，請(qǐng)給出針對(duì)數(shù)據(jù)庫(kù)系統(tǒng)本身及網(wǎng)絡(luò)傳輸過(guò)程中可能的一些調(diào)優(yōu)方案。

　　(2)工程師在完成數(shù)據(jù)庫(kù)系統(tǒng)本身和網(wǎng)絡(luò)問(wèn)題的優(yōu)化后，發(fā)現(xiàn)第三方開(kāi)發(fā)人員的SQL查詢語(yǔ)句存在很多沒(méi)有優(yōu)化的問(wèn)題，請(qǐng)從第三方開(kāi)發(fā)人員角度給出一些優(yōu)化方案。

　　參考解析：

　　【解題思路】

　　從數(shù)據(jù)庫(kù)本身和網(wǎng)絡(luò)傳輸?shù)慕嵌瘸霭l(fā)，數(shù)據(jù)庫(kù)性能低下的因素一般有：I/O吞吐量小，形成了瓶頸效應(yīng);沒(méi)有創(chuàng)建計(jì)算列導(dǎo)致查詢不優(yōu)化;SQL Server數(shù)據(jù)庫(kù)內(nèi)存不足;網(wǎng)絡(luò)速度慢;查詢出的數(shù)據(jù)量過(guò)大(可以采用多次查詢或其他的方法降低數(shù)據(jù)量)以及鎖或者死鎖(這也是查詢慢最常見(jiàn)的問(wèn)題，是程序設(shè)計(jì)的缺陷)。

　　從SQL查詢語(yǔ)句角度來(lái)考慮優(yōu)化響應(yīng)時(shí)間，應(yīng)該從查詢條件、臨時(shí)表、游標(biāo)、返回?cái)?shù)據(jù)量、大事務(wù)操作等方面進(jìn)行調(diào)優(yōu)。

　　【參考答案】

　　(1)①把數(shù)據(jù)、日志、索引放到不同的I/O設(shè)備上，增加讀取速度，數(shù)據(jù)量(尺寸)越大，提高I/O越重要。

　�、诳v向、橫向分割表，減少表的尺寸。

　�、凵�(jí)硬件，擴(kuò)大服務(wù)器的內(nèi)存，Windows 2000和SQL Server 2000能支持4-8G的內(nèi)存。配置虛擬內(nèi)存，虛擬內(nèi)存大小應(yīng)基于計(jì)算機(jī)上并發(fā)運(yùn)行的服務(wù)進(jìn)行配置。增加服務(wù)器CPU個(gè)數(shù)。

　�、芊植际椒謪^(qū)視圖可用于實(shí)現(xiàn)數(shù)據(jù)庫(kù)服務(wù)器聯(lián)合體。聯(lián)合體是一組分開(kāi)管理的服務(wù)器，但它們相互協(xié)作分擔(dān)系統(tǒng)的處理負(fù)荷。這種通過(guò)分區(qū)數(shù)據(jù)形成數(shù)據(jù)庫(kù)服務(wù)器聯(lián)合體的機(jī)制能夠擴(kuò)大一組服務(wù)器，以支持大型的多層Web站點(diǎn)的處理需要。

　�、葜亟ㄋ饕篋BCC REINDEX，DBCC INDEXDEFRAG;收縮數(shù)據(jù)和日志：DBCC SHRINKDB，DBCCSHRINKFILE。設(shè)置自動(dòng)收縮日志，對(duì)于大的數(shù)據(jù)庫(kù)不要設(shè)置數(shù)據(jù)庫(kù)自動(dòng)增長(zhǎng)，它會(huì)降低服務(wù)器的性能。

　�、迌�(yōu)化鎖結(jié)構(gòu)。

　　(2)①對(duì)查詢進(jìn)行優(yōu)化，盡量避免全表掃描，首先應(yīng)考慮在where及order by涉及的列上建立索引。

　�、趹�(yīng)盡量避免在where子句中使用!=或< >操作符，否則引擎將放棄使用索引而進(jìn)行全表掃描。

　�、廴魏蔚胤蕉疾灰褂胹elect*from進(jìn)行全表掃描，用具體的字段列代替“*”，不要返回冗余字段。

　�、鼙苊忸l繁創(chuàng)建和刪除臨時(shí)表，以減少系統(tǒng)表資源的消耗。

　�、荼M量避免使用游標(biāo)，因?yàn)橛螛?biāo)的效率較差，如果游標(biāo)操作的數(shù)據(jù)超過(guò)1萬(wàn)行，那么就應(yīng)該考慮改寫。

　�、薇M量避免大事務(wù)操作，提高系統(tǒng)并發(fā)能力。

　　相關(guān)推薦：

　　2015計(jì)算機(jī)三級(jí)《數(shù)據(jù)庫(kù)》上機(jī)試題及答案匯總

　　2015年計(jì)算機(jī)三級(jí)《網(wǎng)絡(luò)技術(shù)》高分突破試卷匯總

　　2015計(jì)算機(jī)三級(jí)《數(shù)據(jù)庫(kù)技術(shù)》練習(xí)題及答案匯總