數(shù)據(jù)庫物理結(jié)構(gòu)設(shè)計

第6章物理結(jié)構(gòu)設(shè)計●講授1學(xué)時電子教案版本6.6數(shù)據(jù)庫技術(shù)2021/5/91●內(nèi)容概述教學(xué)進程6.1物理結(jié)構(gòu)設(shè)計的內(nèi)容6.2選取存取方法6.3設(shè)計存儲結(jié)構(gòu)6.4確定存儲位置6.5選取存儲介質(zhì)6.6評價物理結(jié)構(gòu)6.7SQLServer2005的索引機制2021/5/926.1物理結(jié)構(gòu)設(shè)計的內(nèi)容物理結(jié)構(gòu)：指數(shù)據(jù)庫在存儲介質(zhì)上的存取方法、存儲結(jié)構(gòu)和存放位置。物理結(jié)構(gòu)設(shè)計：指根據(jù)邏輯結(jié)構(gòu)設(shè)計的結(jié)果，設(shè)計邏輯結(jié)構(gòu)的最佳存取方法、存儲結(jié)構(gòu)和存放位置以及合理選擇存儲介質(zhì)等，從而設(shè)計出適合邏輯結(jié)構(gòu)的最佳物理環(huán)境（即：存儲模式）的過程。物理結(jié)構(gòu)設(shè)計的內(nèi)容：選取存取方法、設(shè)計存儲結(jié)構(gòu)、確定存放位置以及選擇存儲介質(zhì)等，因素包括：訪問類型、訪問時間、插入時間、刪除時間和空間開銷等。（1）存取方法：指用戶存取數(shù)據(jù)庫數(shù)據(jù)的方法和技術(shù)。（2）存儲結(jié)構(gòu)：指根據(jù)邏輯結(jié)構(gòu)的指標以及DBMS支持的數(shù)據(jù)類型，所確定的數(shù)據(jù)項的存儲類型和長度以及元組的存儲結(jié)構(gòu)等即：數(shù)據(jù)文件及其數(shù)據(jù)項在介質(zhì)上的具體存儲結(jié)構(gòu)。（3）存放位置：指根數(shù)據(jù)庫文件和索引文件等在介質(zhì)上的具體存儲位置。（4）存儲介質(zhì)：指用于存儲文件的物理存儲設(shè)備。包括：磁盤、磁帶、光盤、磁盤陣列、磁帶庫、光盤陣列等。具體包括：介質(zhì)的容量大小、存取速度與費用等。2021/5/93物理結(jié)構(gòu)設(shè)計的方法：（1）選擇存取方法。（2）設(shè)計存儲結(jié)構(gòu)。（3）確定存放位置。（4）選取存儲介質(zhì)。（5）評價物理結(jié)構(gòu)。結(jié)論：通過存取方法、存儲結(jié)構(gòu)、存放位置以及存儲介質(zhì)的合理設(shè)計，最終為邏輯模式設(shè)計出滿足應(yīng)用需求的最佳存儲模式。2021/5/946.2選擇存取方法實現(xiàn)數(shù)據(jù)庫快速訪問的最有效方法是使用索引機制。索引機制是指對于數(shù)據(jù)庫的數(shù)據(jù)表，根據(jù)數(shù)據(jù)表的查詢需要，按照查詢數(shù)據(jù)所對應(yīng)的關(guān)鍵屬性，為數(shù)據(jù)表建立相應(yīng)的用于快速檢索的索引文件，在執(zhí)行查詢操作時，先在索引文件中找到查詢的元組在數(shù)據(jù)表中位置（地址），然后再根據(jù)這個地址，去數(shù)據(jù)表中直接取出元組數(shù)據(jù)。這種先查詢索引文件，再從數(shù)據(jù)表中取值的檢索機制稱為索引機制。索引表是指把關(guān)鍵屬性（例如：主鍵）的值按照升序（或者降序）排序后，與它對應(yīng)的元組在數(shù)據(jù)表中的位置所組成的對照表。即：索引表是索引屬性值與元組地址的對照表。索引文件用于存儲索引表的文件。溫馨提示：索引文件需要配合數(shù)據(jù)文件一起使用，才能進行快速檢索，因此索引文件單獨使用沒有意義。2021/5/956.2選擇存取方法例如：一本字典是由字典正文和字典索引連部分組成。字典索引相當于索引表，字典正文相當于數(shù)據(jù)表。字典索引和字典正文一起配合使用實現(xiàn)查詢字的用法。具體查詢過程如下：（1）首先在字典索引中，查詢字在字典中的頁碼。（2）然后按照頁碼，字典正文中找到該字的用法。思考題1：對于任意數(shù)據(jù)表，使用索引機制進行檢索與不使用索引機制而直接對數(shù)據(jù)表進行檢索相比，是否前者一定比后者快？思考題2：在什么情況下，使用索引機制可以進行快速檢索？常用的存取方法括：平衡樹（BalanceTree，B樹）、聚簇（Cluster）和散列索引。其中：B樹索引是最常用的存取方法，具體操作見數(shù)據(jù)結(jié)構(gòu)。2021/5/96聚簇索引（了解）聚簇：指根據(jù)索引關(guān)鍵屬性的值直接找到數(shù)據(jù)的物理存儲位置，從而達到快速檢索數(shù)據(jù)的目的，提高檢索的效率。聚簇索引：指在按照關(guān)鍵屬性對數(shù)據(jù)表建立索引時，同時按照索引順序?qū)?shù)據(jù)表的相應(yīng)元組的物理存儲位置進行排序，使索引的順序與數(shù)據(jù)表中相應(yīng)元組的物理順序始終保持一致的索引過程。聚簇索引與非聚簇索引的區(qū)別：1）聚簇索引的順序與數(shù)據(jù)的物理存儲順序始終保持一致；非聚簇索引的順序與數(shù)據(jù)物理排列順序無關(guān)。2）聚簇索引B+樹的葉節(jié)點就是數(shù)據(jù)節(jié)點；非聚簇索引B+樹的葉節(jié)點仍然是索引節(jié)點，其指針指向?qū)?yīng)的元組或者數(shù)據(jù)塊。3）一個數(shù)據(jù)表只能有一個聚簇索引；非聚簇索引則可有多個。4）建立和維護非聚簇索引的開銷相對較小，而聚簇索引的開銷則相當大。5）聚簇索引適合于不需要更新或者更新比較少的應(yīng)用，非聚簇索引則適合于更新比較多的應(yīng)用。6）聚簇索引靈活性較差不建議經(jīng)常適應(yīng)，非聚簇索引性則相對比較靈活。2021/5/97聚簇索引（了解）使用聚簇索引注意：（1）經(jīng)常進行連接操作的數(shù)據(jù)表建議使用聚簇索引。（2）對于屬性組的利用率很高或者重復(fù)率很高的關(guān)系建議使用聚簇索引。（3）需要經(jīng)常進行插入、刪除或修改等更新操作，不建議使用聚簇索引。（4）對于更新操作遠多于連接操作的關(guān)系不建議使用聚簇索引。溫馨提示：聚簇索引雖然可以提高某些應(yīng)用的性能，但是會改變數(shù)據(jù)的物理存儲位置，而且會導(dǎo)致數(shù)據(jù)表的原有索引無效，同時維護費用很大，因此需要謹慎使用。2021/5/986.3設(shè)計存儲結(jié)構(gòu)存儲結(jié)構(gòu)設(shè)計的內(nèi)容：存儲的關(guān)系模式；關(guān)系模式的數(shù)據(jù)項；數(shù)據(jù)項的類型，寬度，是否主鍵，是否外鍵，是否索引鍵等?！纠?.8】：如果關(guān)系R(R1,…,R6，R7,…,R20)，包含20個屬性，但是前6個屬性的利用率非常高，而其它屬性的利用率非常低，則存儲時可以按照R(R1，…，R6)，S(R7，…，R20)兩個關(guān)系進行存儲（即：垂直分割），并通過邏輯模式/存儲模式映像作相應(yīng)的調(diào)整。思考題：如果關(guān)系R(R1,…,R6，R7,…,R20)，包含60萬個元組，但是前6萬個元組的利用率非常高，而其它元組的利用率非常低，則應(yīng)該如何設(shè)計存儲結(jié)構(gòu)。溫馨提示：建立兩個同結(jié)構(gòu)關(guān)系（即：水平分割）。2021/5/996.4確定存儲位置DBMS提供了數(shù)據(jù)庫、索引文件、聚日志文件、備份文件等文件的默認文件目錄結(jié)構(gòu)及其存儲路徑。為提高系統(tǒng)性能，需要進一步設(shè)計存放。例如：SQL2005的數(shù)據(jù)庫，可以設(shè)置數(shù)據(jù)庫和日志文件的存儲路徑等。存儲位置設(shè)計的基本原則：1）同一類文件存放在同一個目錄。2）易變部分與穩(wěn)定部分應(yīng)該分開存放。3）存取頻率高的部分和低的部分應(yīng)該分別存放到快速和慢速設(shè)備。例如：對于多磁盤計算機系統(tǒng)，為了提高系統(tǒng)性能，可以采用如下方案：方案1：把數(shù)據(jù)表和索引文件放在不同的磁盤上，使多磁盤并行工作。方案2：把大數(shù)據(jù)表分別放在不同的磁盤上，提高數(shù)據(jù)的存取速度。方案3：把日志文件和數(shù)據(jù)庫分別放在不同磁盤上，使多磁盤并行工作。方案4：把數(shù)據(jù)庫放在高速磁盤；把備份（即后備副本）放在磁帶。例如：例6.8的R(R1，…，R6)存入高速磁盤，S(R7，…，R20)存入光盤。4）根據(jù)應(yīng)用系統(tǒng)的文件類型和應(yīng)用需求，統(tǒng)一設(shè)計文件目錄結(jié)構(gòu)。統(tǒng)一考慮存取時間、存儲空間、維護費用等，對數(shù)據(jù)文件進行合里安排。2021/5/9106.6評價物理結(jié)構(gòu)對設(shè)計的多種物理結(jié)構(gòu)，通過評價，選擇最佳的物理結(jié)構(gòu)。評價物理結(jié)構(gòu)：包括評價內(nèi)容、評價指標和評價方法。評價內(nèi)容：存取方法選取的正確性、存儲結(jié)構(gòu)設(shè)計的合理性、文件存放位置的規(guī)范性、存儲介質(zhì)選取的標準性。評價指標：存儲空間的利用率、存取數(shù)據(jù)的速度和維護費用等。評價方法：根據(jù)物理結(jié)構(gòu)的評價內(nèi)容，統(tǒng)計存儲空間的利用率、數(shù)據(jù)的存取速度和維護費用指標結(jié)論：通過對比各項指標，選擇適合應(yīng)用的合理的最佳物理結(jié)構(gòu)。2021/5/9116.7SQLServer2005的索引機制建立索引修改索引刪除索引2021/5/9121建立索引格式：CREATE[UNIQUE][CLUSTER]INDEX<索引名>ON<表名>(<列名>[<次序>][,<列名>[<次序>]]…)功能：對指定的數(shù)據(jù)表，按照指定的屬性列以升序或者降序建立索引。說明：1）<索引名>：索引的名稱。索引名必須符合標識符的規(guī)則。2）<表名>：索引的基本表名稱。3）索引可以建立在該表的一列或多列上，各列名之間用逗號分隔4）<次序>指索引值的排列次序，升序：ASC，降序：DESC。缺省值：ASC。5）UNIQUE：索引的每一個索引值，只對應(yīng)唯一的記錄。即：唯一索引。6）CLUSTER：建立聚簇索引。對數(shù)據(jù)表建立聚簇索引后，表中數(shù)據(jù)也需要按指定的聚簇屬性值的升序或降序存放。亦即：聚簇索引的索引項順序與表中記錄的物理順序一致。7）索引的維護由DBMS自動完成。8）索引的使用由DBMS自動選擇是否使用索引以及使用哪些索引。2021/5/913【例6.10】StudentTestDB中，給Student，Course，StudentCourse建立索引。（1）Student表按學(xué)號升序建唯一索引（2）Course表按課程號升序建唯一索引（3）StudentCourse表按學(xué)號升序和課程號降序建唯一索引。SQLServer2005語句：CREATEUNIQUEINDEXStudentSNoONStudent(SNo)CREATEUNIQUEINDEXCourseCNoONCourse(CNo)CREATEUNIQUEINDEXSCSNoCNoONStudentCourse(SNoASC,CNoDESC)溫馨提示：已經(jīng)包含重復(fù)值的屬性列，不能建立UNIQUE索引。對于建立UNIQUE索引的屬性列，插入新記錄時DBMS會自動檢查新記錄在該列上是否取了重復(fù)值。相當于給屬性列增加UNIQUE約束。2021/5/914【例6.11】在Student表的SName（姓名）列上建立一個聚簇索引，而且Student表中的記錄將按照SName值的升序存放。SQLServer2005語句如下：CREATECLUSTEREDINDEXStudentSNameONStudent(SName)溫馨提示：在一個數(shù)據(jù)表上最多只能建立一個聚簇索引。2021/5/9156.7.2修改索引格式：ALTERINDEX{<索引名>|ALL}ON<表名>{REBUILD|DISABLE}功能：修改現(xiàn)有的數(shù)據(jù)表索引或者視圖索引。說明：（1）<索引名>：修改索引的名稱。（2）ALL指定與表或視圖相關(guān)聯(lián)的所有索引。（3）<表名>：修改索引的基本表名稱。（4）REBUILD：重新生成索引。（5）DISABLE：禁用索引。任何索引均可被禁用。2021/5/916【例6.17】重新生成索引。Employee中重新生成單個索引。SQLServer2005語句：USEAdventureWorksALTERINDEXPK_Employee_EmployeeIDONHumanResources.EmployeeREBUILD【例6.18】重新生成表的所有索引。使用ALL關(guān)鍵字，重新生成與表相關(guān)聯(lián)的所有索引。SQLServer2005語句：USEAdventureWorks;ALTERINDEXALLONProduction.ProductREBUILD2021/5/917【例6.19】禁用索引。禁用Employee的非聚集索引IX_Employee_ManagerID。SQLServer2005語句：USEAdventureWorksALTERINDEXIX_Employee_ManagerIDONHumanResources.EmployeeDISABLE【例6.20】啟用索引。啟用Employee的非聚集索引IX_Employee_ManagerID。SQLServer2005語句：USEAdventureWorksALTERINDEXIX_Employee_ManagerIDONHumanResources.EmployeeREBUILD2021/5/9183刪除索引格式：DROPINDEX<表名>.<索引名>功能：從當前數(shù)據(jù)庫中刪除索引。說明：（1）用<索引名>指定要刪除索引的名稱。（2）用<表名>指定要刪除索引的基本表名稱。溫馨提示：刪除索引時，系統(tǒng)會從數(shù)據(jù)字典中刪去有關(guān)該索引的描述。【例6.21】刪除Student的SName列上的聚簇索引StudentSName。

SQLServer2005語句：DROPINDEXStudent.StudentSName2021/5/919【例6.22】刪除ProductVendor的索引IX_ProductVendor_VendorID。

SQLServer2005語句：USEAdventureWorksDROPINDEXIX_ProductVendor_VendorIDONPurchasing.ProductVendor【例6.23】刪除多個索引。刪除單個事務(wù)中的兩個索引。SQLServer2005語句：USEAdventureWorksDROPINDEXIX_PurchaseOrderHeader_EmployeeIDONPurchasing.PurchaseOrderHeader,IX_VendorAddress_AddressIDONPurchasing.VendorAdd