數(shù)據(jù)庫(kù)課件：chap13選擇文件組織方式和索引

1、1,Chapter 13,物理數(shù)據(jù)設(shè)計(jì) 步驟4 選擇文件組織方式和索引,2,Chapter 13 目的,如何分析用戶事務(wù)來確定可能會(huì)影響性能的特性 基于對(duì)事物的分析，如何選擇合適的文件組織方式 何時(shí)選擇索引來改善系統(tǒng)性能,3,步驟4 選擇文件組織方式和索引,確定最佳文件組織方式來存儲(chǔ)基本表以及實(shí)現(xiàn)所要求性能的索引 包括如下幾步: 步驟4.1 分析事務(wù) 步驟4.2 選擇文件組織方式 步驟4.3 選擇索引,4,步驟4.1 分析事務(wù),理解運(yùn)行在數(shù)據(jù)庫(kù)上的事務(wù)的功能并分析重要的事務(wù)。 確定性能指標(biāo) 經(jīng)常運(yùn)行的事務(wù)對(duì)性能產(chǎn)生重大影響的事務(wù) 業(yè)務(wù)操作的關(guān)鍵事務(wù) 當(dāng)對(duì)數(shù)據(jù)庫(kù)有很高要求時(shí)，每日/每周內(nèi)訪問數(shù)據(jù)

2、庫(kù)的次數(shù) (最大負(fù)載) 使用這些信息來標(biāo)識(shí)可能會(huì)引起性能問題的數(shù)據(jù)庫(kù)部分 需要標(biāo)識(shí)事務(wù)的高層功能，來選擇正確的文件組織方式和索引 更新事務(wù)中更新的列 查詢事務(wù)中檢索的列,5,步驟 4.1 分析事務(wù),研究最重要的那些事務(wù) 為了幫助標(biāo)識(shí)要研究的那些事務(wù) 事務(wù)/表交叉引用矩陣(transaction/table cross-reference matrix)，顯示了每個(gè)事務(wù)訪問的表 事務(wù)應(yīng)用圖(transaction usage map)，用圖形的方式表明了哪些表潛在地可能被多次使用 方法： (1)將所有事務(wù)路徑映射到表中 (2) 確定常被事務(wù)訪問的表 (3) 數(shù)據(jù)應(yīng)用分析,6,交叉引用的事務(wù)和表,

3、e) 輸入在一指定分公司登記的新會(huì)員的詳細(xì)信息,此表中還可以表示一定時(shí)間間隔（每小時(shí)、每天、每周）內(nèi)訪問的數(shù)量,7,交叉引用的事務(wù)和表,8,示例事務(wù)的事務(wù)使用映射圖,s,Video和VideoForRent表被事務(wù)（p、q、r、s）訪問，由于VideoForRent表較大，因此應(yīng)盡可能有效地訪問該表,9,數(shù)據(jù)應(yīng)用分析,對(duì)每個(gè)事務(wù)而言，應(yīng)確定： 訪問表和列以及訪問的類型 類型：插入、更新、刪除、檢索 “更新或刪除”的列，有可能是二級(jí)檢索的候選，需要重新建立索引，影響性能。 在查詢條件中使用的列 模式匹配，如，（name LIKE %Smith%） 范圍查找，如，（Salary BETWEEN 3

4、0000 AND 40000） 準(zhǔn)確匹配的鍵值索引，例如（Salary = 30000） 包含在兩個(gè)或更多表的連接列（主鍵/外鍵） 這些列可能是訪問結(jié)構(gòu)的候選，建立索引，提高性能。 事務(wù)運(yùn)行的預(yù)期頻率 例如，每天大約事務(wù)執(zhí)行的次數(shù) 事務(wù)的性能目標(biāo) 例如，事務(wù)必須在幾秒中完成 對(duì)常用和關(guān)鍵事務(wù)中的查詢條件使用的列，需要比訪問結(jié)構(gòu)有更高的優(yōu)先級(jí),10,樣例事務(wù)分析模式,11,步驟4.2 選擇文件組織方式,確定每個(gè)基本表的有效文件組織方式 文件組織方式包括： 堆（ Heap） 哈希（Hash） 索引順序訪問方法（ISAM） B+樹 一些DBMS文件組織方式是固定的，無法更改,12,步驟4.2 選擇文

5、件組織方式,確定每個(gè)基本表的有效文件組織方式 文件組織方式包括： 堆（ Heap） 哈希（Hash） 索引順序訪問方法（ISAM） B+樹 一些DBMS文件組織方式是固定的，無法更改,13,選擇文件組織方式指南,堆（ Heap） 適于無序訪問的表，例如，檢索StayHome中所有會(huì)員地址。 有索引鍵的表。 哈希（Hash） 檢索是基于與哈希字段值精確匹配的表。例如，如果在Member表的memberNo列上進(jìn)行哈希，則基于會(huì)員號(hào)的檢索非常有效。 哈希列被經(jīng)常更新時(shí)，會(huì)影響性能。 索引順序訪問方法（ISAM） 支持基于模式匹配或者基于范圍值的檢索 ISAM索引是靜態(tài)的，它在文件被創(chuàng)建時(shí)建立，隨著

6、表的不斷更新，性能會(huì)降低。 B+樹 它的索引是動(dòng)態(tài)的，對(duì)于經(jīng)常修改的表，采用此模式更有效,14,步驟 4.3 選擇索引,索引（Index）： 是一種數(shù)據(jù)結(jié)構(gòu)，類似于書的目錄。它是與要查找信息的文件相關(guān)聯(lián)的一個(gè)輔助結(jié)構(gòu)。 一個(gè)索引結(jié)構(gòu)是與一個(gè)特定的查詢關(guān)鍵字相關(guān)的，并且包含由關(guān)鍵字和包含這個(gè)值的邏輯記錄組成的記錄。 包含邏輯記錄的文件稱為數(shù)據(jù)文件，而含有索引記錄的文件稱為索引文件。 索引文件的值是根據(jù)索引字段的值來排序的，這個(gè)索引字段通常是基于表中的某個(gè)列的,15,步驟 4.3 選擇索引,索引類型： 主索引：數(shù)據(jù)文件根據(jù)一個(gè)有序的鍵字段（候選碼）順序排序，并且索引字段是建立在有序的鍵字段上的。鍵

7、字段保證了每條記錄的唯一性。 聚簇索引：數(shù)據(jù)文件根據(jù)非鍵字段的值排序，并且索引字段是建立在這些非鍵字段上的，因此對(duì)應(yīng)索引字段的一個(gè)值可能會(huì)有多條記錄。非鍵字段被稱為聚簇字段。 二級(jí)索引：定義在一個(gè)數(shù)據(jù)文件中的非排序字段上的索引。數(shù)據(jù)文件不一定是按照索引字段排序的。 一個(gè)文件最多只能有一個(gè)主索引或一個(gè)聚簇索引，但可以有多個(gè)二級(jí)索引,16,步驟 4.3 選擇索引,確定添加索引是否會(huì)改善系統(tǒng)性能 一種方法是保持記錄的無序性并且創(chuàng)建所需數(shù)目的二級(jí)索引。 通過指定主鍵或聚簇索引使表中記錄為有序的。 使用索引的情況： 經(jīng)常用于連接操作的列，因?yàn)檫@樣使連接更有效率 在表中經(jīng)常按某列的順序訪問記錄的列,17,

8、步驟 4.3 選擇索引,如果選擇排序的列是表的鍵，那么該索引就是主索引，如果排序的列不是鍵，那么該索引就是聚簇索引。 每個(gè)文件只能有一個(gè)主索引或者是一個(gè)聚簇索引，而不能兩者兼有。但可以有多個(gè)二級(jí)索引,18,指定索引,為Video表創(chuàng)建基于catalogNo列的主索引： CREATE UNIQUE INDEX catalogno_index ON Video(catalogNo) 為VideoForRent表創(chuàng)建基于catalogNo列的聚簇索引： CREATE INDEX catalogno_index ON VideoForRent(catalogNo) CLUSTER 注：VideoFor

9、Rent(videoNo,available,catalogNo,branchNo) 刪除索引 DROP INDEX catalogno_index,19,選擇二級(jí)索引,二級(jí)索引提供了為基本表指定其他鍵的機(jī)制，便于更有效地檢索數(shù)據(jù)。 例如，Member表在memberNo上進(jìn)行了Hash映射，也就是主索引。但是，可能會(huì)有經(jīng)常使用基于lName列來訪問數(shù)據(jù)，這時(shí)可以增加lName作為二級(jí)索引,20,選擇二級(jí)索引,在保持和使用二級(jí)索引時(shí)還要考慮到當(dāng)檢索數(shù)據(jù)時(shí)，必須要平衡改善性能。 當(dāng)在表中插入一條記錄時(shí)，都要給每個(gè)二級(jí)索引增加一個(gè)索引記錄。 當(dāng)表中相應(yīng)記錄被更新時(shí)，也要更新二級(jí)索引。 需要使用額外

10、的磁盤空間來存儲(chǔ)二級(jí)索引。 在查詢優(yōu)化期間，性能可能退化，因?yàn)樵谶x擇最佳執(zhí)行策略之前，查詢優(yōu)化器可能考慮所有的二級(jí)索引,21,選擇索引“意愿表”的方針,1) 不必為小表創(chuàng)建索引。在內(nèi)存中查詢?cè)摫頃?huì)比存儲(chǔ)額外的索引結(jié)構(gòu)更加有效。 (2) 如果沒有文件組織方式的鍵，則索引表的主鍵 (3) 為檢索數(shù)據(jù)時(shí)大量使用的列增加二級(jí)索引 如，為Member表增加基于lName列的二級(jí)索引 (4) 如果經(jīng)常給予外鍵訪問數(shù)據(jù)，則為該外鍵增加二級(jí)索引 對(duì)于經(jīng)常使用branchNo連接VideoForRent表和Branch表，需為VideoForRent表創(chuàng)建基于branchNo的二級(jí)索引，以提高效率,22,選擇索

11、引“意愿表”的方針,5) 為經(jīng)常有如下情況的列增加二級(jí)索引 查詢或者連接條件 ORDER BY GROUP BY 其他操作包括排序（例如UNION或DISTINCT） (6) 為在內(nèi)置函數(shù)包含的列增加二級(jí)索引，包括內(nèi)置函數(shù)中用于聚簇的列。 SELECT branchNo, AVG(salary) FROM Staff GROUP BY branchNo; 由于有GROUP BY子句，可以考慮為branchNo添加索引，可是該查詢又用了聚簇列，因此為branchNo和salary共同創(chuàng)建索引會(huì)更加有效。DBMS只根據(jù)索引中的數(shù)據(jù)就可以完成整個(gè)查詢，而不需要?jiǎng)?chuàng)建數(shù)據(jù)文件,23,選擇索引“意愿表”的

12、方針,7) 避免為經(jīng)常被更新的列或表設(shè)置索引 (8) 如果查詢將檢索表中記錄的大部分（例如25%），也不創(chuàng)建索引。 (9) 避免為由長(zhǎng)字符串組成的列創(chuàng)建索引 (10) 對(duì)于列值變化較小的列，不適宜對(duì)該列作索引。 如，在VideoForRent表中，available列被事務(wù)用作查詢條件。但是，這個(gè)列只有兩個(gè)值（Y或N），并不值得為該列建立索引,24,關(guān)于條件查詢,如果查詢條件包含多個(gè)條件，并且條件中包含一個(gè)OR子句，而該條件沒有索引/排序，那么為其他列添加索引將不會(huì)改善查詢速度，因?yàn)槿匀恍枰淼木€性查找。 例如，假定只有Video表的category和dailyRental列有索引，要使用下列

13、查詢： SELECT * FROM Video WHERE （category=Action OR dailyRental3 OR price 15); 由于price沒有索引，因此，本查詢的效率得不到改善。但是如果條件是AND，則該兩列的索引就可以達(dá)到優(yōu)化查詢,25,關(guān)于大量插入記錄,如果大量的記錄被插入到有索引的表中，可以先刪除索引，在執(zhí)行插入數(shù)據(jù)后，再創(chuàng)建索引會(huì)更加有效。 作為一個(gè)不成文的規(guī)則，如果插入動(dòng)作將增加表的10%的大小，那么暫時(shí)將索引刪除,26,分析工具,Oracle EXPLAIN PLAN診斷工具 DB2 EXPLAIN實(shí)用工具 Microsoft Access 性能分析器,27,StayHome的Branch視圖的附加索引,28,StayHome的Branch視圖的查詢事務(wù)所需的表,29,StayHome的Branch視圖的查詢事務(wù)所需的表（續(xù),30,小結(jié),步驟4中，分析事務(wù)并基于這個(gè)分析，設(shè)計(jì)文件的組織方式和用于存儲(chǔ)基本表的索引。 在理解要支持的事務(wù)的細(xì)