三、設計與應用題

　　45在進行某學校教務管理系統的數據庫設計時，數據庫設計人員設計了如下幾個關系模式：

　　系(系號，系名)，系號為主碼

　　學生(學號，姓名，所在系號)，學號為主碼

　　課程(課程號，課程名，開課系號)，課程號為主碼

　　選課(學號，課程號，選課時間)，學號和課程號為主碼

　　開發人員在將關系模式實施到SQL Server 2008的“教務”數據庫時，使用了如下表結構定義語句：

　　CREATE TABLE系(

　　系號varchar(10)NOT NULL，

　　系名varchar(100)

　　)

　　CREATE TABLE學生(

　　學號varchar(50)NOT NULL，

　　姓名varchar(50)，

　　所在系號varchar(10)

　　)

　　CREATE TABLE課程(

　　課程號varchar(50)NOT NULL，

　　課程名varchar(100)，

　　開課系號varchar(10)

　　)

　　CREATE TABLE選課(

　　學號varchar(50)NOT NULL，

　　課程號varchar(50)NOT NULL，

　　選課時間datetime

　　)

　　在執行如下查詢語句時發現執行效率很低：

　　SELECT *FROM選課JOIN學生0N學生.學號=選課.學號

　　JOIN系ON系.系號=學生.所在系號

　　JOIN課程0N課程.課程號=選課.課程號

　　WHERE系.系號=ˊ012 ˊ

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

　　(1)在查找原因時發現建表語句有問題。請指出問題并說明該問題是否會影響此查詢語句的執行效率。

　　(2)設已在“選課”表的“選課時間”列及“學生”表的“所在系號”列上建立了索引。請問這兩個索引是否能夠提高該查詢語句的執行效率?如果不能，請說明原因。

　　參考解析：

　　(1)【解題思路】

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

　　【參考答案】

　　建表時沒有設置主鍵，也沒有說明外鍵，但不會影響此查詢語句的執行效率。

　　(2)【解題思路】

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

　　【參考答案】

　　“選課”表的“選課時問”可以建立索引，從而提高了查詢效率，而“學生”表的“所在系號”建立索引不會提高查詢效率。索引的意義就是將記錄按目標關鍵字順序排列，這樣查找某個目標關鍵字的對應值的位置就縮小了查找范圍。“選課時間”的重復率低，所以可以作為索引，而學生“所在系號”的重復率太高，則不會提高查詢效率。

　　46某采購中心采購了一套商品批發查詢管理系統，此系統采用SQLServer 2008數據庫管理系統，該系統需要經常處理百萬級以上的數據查詢。同時該系統提供了第三方人員開發的SQL接口，第三方人員可以根據自己的需要開發自己的應用程序來訪問數據庫中的相關數據。

　　(1)系統在使用的過程中，業務人員反應系統操作速度很慢。經過工程師檢查測試后，數據庫系統本身及網絡傳輸過程中存在著一些問題，請給出針對數據庫系統本身及網絡傳輸過程中可能的一些調優方案。

　　(2)工程師在完成數據庫系統本身和網絡問題的優化后，發現第三方開發人員的SQL查詢語句存在很多沒有優化的問題，請從第三方開發人員角度給出一些優化方案。

　　參考解析：

　　【解題思路】

　　從數據庫本身和網絡傳輸的角度出發，數據庫性能低下的因素一般有：I/O吞吐量小，形成了瓶頸效應;沒有創建計算列導致查詢不優化;SQL Server數據庫內存不足;網絡速度慢;查詢出的數據量過大(可以采用多次查詢或其他的方法降低數據量)以及鎖或者死鎖(這也是查詢慢最常見的問題，是程序設計的缺陷)。

　　從SQL查詢語句角度來考慮優化響應時間，應該從查詢條件、臨時表、游標、返回數據量、大事務操作等方面進行調優。

　　【參考答案】

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

　　②縱向、橫向分割表，減少表的尺寸。

　　③升級硬件，擴大服務器的內存，Windows 2000和SQL Server 2000能支持4-8G的內存。配置虛擬內存，虛擬內存大小應基于計算機上并發運行的服務進行配置。增加服務器CPU個數。

　　④分布式分區視圖可用于實現數據庫服務器聯合體。聯合體是一組分開管理的服務器，但它們相互協作分擔系統的處理負荷。這種通過分區數據形成數據庫服務器聯合體的機制能夠擴大一組服務器，以支持大型的多層Web站點的處理需要。

　　⑤重建索引：DBCC REINDEX，DBCC INDEXDEFRAG;收縮數據和日志：DBCC SHRINKDB，DBCCSHRINKFILE。設置自動收縮日志，對于大的數據庫不要設置數據庫自動增長，它會降低服務器的性能。

　　⑥優化鎖結構。

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

　　②應盡量避免在where子句中使用!=或< >操作符，否則引擎將放棄使用索引而進行全表掃描。

　　③任何地方都不要使用select*from進行全表掃描，用具體的字段列代替“*”，不要返回冗余字段。

　　④避免頻繁創建和刪除臨時表，以減少系統表資源的消耗。

　　⑤盡量避免使用游標，因為游標的效率較差，如果游標操作的數據超過1萬行，那么就應該考慮改寫。

　　⑥盡量避免大事務操作，提高系統并發能力。

　　相關推薦：

　　2015計算機三級《數據庫》上機試題及答案匯總

　　2015年計算機三級《網絡技術》高分突破試卷匯總

　　2015計算機三級《數據庫技術》練習題及答案匯總