1、熟悉第8章索引和查詢最優(yōu)化，牙齒章節(jié)的要點(diǎn)：索引的概念，分類把握索引方法。掌握T-SQL索引語句。了解查詢優(yōu)化的一般準(zhǔn)則。了解查詢最優(yōu)化算法掌握簡(jiǎn)單的查詢?cè)O(shè)計(jì)方法。摘要：8.1索引8 . 1 . 1 . 1群集索引8.1.2非群集索引8.1.3索引查詢效率實(shí)例分析(了解)8.1.4索引結(jié)構(gòu)(了解)8.1.5SQL server 2000單行或行組可以索引。索引使用樹結(jié)構(gòu)b。b樹按搜索關(guān)鍵字排序。通常，需要為用于數(shù)據(jù)查詢的列創(chuàng)建索引。目的：動(dòng)態(tài)保持高搜索效率！均衡的m叉子樹結(jié)構(gòu)！實(shí)例：SQL Server2000提供的pubs示例數(shù)據(jù)庫中，employee表在emp_id列中具有索引。下圖說明了

2、索引如何存儲(chǔ)每個(gè)emp_id值，并指向包含各個(gè)值的表中的數(shù)據(jù)行。emp_id列中的employee表索引，順序祖懷平均時(shí)間復(fù)雜度O(n/2)！例如：平分祖懷平均時(shí)間復(fù)雜度O(log2n)！設(shè)置emp_id列索引，如何查詢use pubs select * from employee where EMP _ id like l %？SQL Server自動(dòng)為特定類型的約束(例如PRIMARY KEY和UNIQUE約束)創(chuàng)建索引。缺點(diǎn)：索引有價(jià)格。用于插入、更新和刪除數(shù)據(jù)的命令需要更長(zhǎng)的時(shí)間來維護(hù)索引。設(shè)計(jì)和構(gòu)建索引時(shí)，應(yīng)確保性能比存儲(chǔ)空間和處理資源成本顯著提高。表索引1，Microsoft SQ

3、L Server 2000支持在表的所有列(包括計(jì)算列)中定義的索引。2、如果表不創(chuàng)建索引，則不會(huì)按特定順序保存數(shù)據(jù)行。牙齒結(jié)構(gòu)稱為堆。SQL服務(wù)器索引的兩種茄子類型按相同的物理順序存儲(chǔ)表的唱片行和鍵值：簇索引、非簇索引、8.1.1簇索引： 1、簇索引。像電話簿一樣。2、主表生成主鍵簇索引。3、每個(gè)表只能有一個(gè)聚集索引。4、如果表沒有聚集索引，則相應(yīng)的數(shù)據(jù)行存儲(chǔ)為堆。(sysindexes中堆的id，字段indid=0。)、實(shí)例、4、群集索引結(jié)構(gòu)：1)在sysindexes中標(biāo)識(shí)群集索引，字段indid=1。2)Microsoft SQL Server 2000將索引組織為b樹。索引中的每個(gè)頁

4、面都有前綴，前綴后面是索引行。3)每個(gè)索引行都包含鍵值和指向較低級(jí)頁面或數(shù)據(jù)行的指針。實(shí)例、4)索引中的每個(gè)頁面稱為索引節(jié)點(diǎn)。b樹的頂層節(jié)點(diǎn)稱為根節(jié)點(diǎn)。索引的基本節(jié)點(diǎn)稱為葉節(jié)點(diǎn)。5)對(duì)等索引的頁面鏈接位于雙向鏈接列表中。實(shí)例、6)群集索引：數(shù)據(jù)頁組成葉節(jié)點(diǎn)。根和葉之間的所有索引級(jí)別統(tǒng)稱為中間階段。7)匯總sysindexes.root指向上方的索引。實(shí)例、集群索引的結(jié)構(gòu)、返回、customerID列中的Northwind.customers集群索引、按索引鍵排序和存儲(chǔ)的數(shù)據(jù)、索引的葉層次結(jié)構(gòu)是表中的實(shí)際數(shù)據(jù)、filenumber 3360頁編號(hào)、filenumber 3360頁編號(hào)Select

5、 * from customers where customerid=Anton，使用集群索引查找選擇id，indid，Root from sys indexes where id=object _ idSQL SERVER通過根地址掃描頁面，查找要通過的路徑。葉頁面已鏈接。，使用群集索引查找。customers按city列創(chuàng)建群集索引。city值不是唯一的。群集索引應(yīng)用于許多非冗馀列。使用between、=、和=運(yùn)算符返回值范圍的查詢。連續(xù)訪問的列。返回大結(jié)果集的查詢。經(jīng)常訪問使用聯(lián)接或GROUP BY子句的查詢的列。需要非?？焖俚膯涡凶鎽?通常通過主鍵)的OLTP類型應(yīng)用程序。必須在主鍵上

6、創(chuàng)建群集索引。群集索引不適用。頻繁更改的列寬鍵列值過長(zhǎng)。8.1.2非群集索引(類似于書本目錄)非群集索引的特征：1)索引與數(shù)據(jù)行的存儲(chǔ)順序無關(guān)。2)索引用作有關(guān)表的附加信息。3)對(duì)單行查詢有利。范圍查詢非群集索引可以在具有群集索引的表、堆或索引視圖中定義。在非群集索引中，從索引行到數(shù)據(jù)行的指針稱為行定位器。行定位器的結(jié)構(gòu)取決于數(shù)據(jù)頁存儲(chǔ)為堆或聚集的方式。對(duì)于堆，行定位器是指向行的指針。對(duì)于具有聚集索引的表，行定位器是聚集關(guān)鍵字。非群集索引和群集索引的兩個(gè)茄子主要區(qū)別在于：(1)數(shù)據(jù)行未按非群集密鑰的順序排序和存儲(chǔ)。(2)非群集索引的葉層不包含數(shù)據(jù)頁。葉節(jié)點(diǎn)包含索引行。非叢集索引的結(jié)構(gòu)：1，非叢

7、集索引：indid值全部介于2到250之間。根列指向非群集索引b樹的頂部。2、非群集索引數(shù)據(jù)存儲(chǔ)在一個(gè)位置，索引存儲(chǔ)在其他位置，索引具有指向數(shù)據(jù)的指針存儲(chǔ)位置。3，搜索數(shù)據(jù)值時(shí)，首先搜索非集群索引，查找表中數(shù)據(jù)值的位置，然后直接從該位置檢索數(shù)據(jù)。4、非聚集索引通常用于1)未返回大結(jié)果集的查詢。2)需要經(jīng)常聯(lián)接和分組的決策支持系統(tǒng)應(yīng)用節(jié)目。必須在用于聯(lián)接和分組操作的列中創(chuàng)建多個(gè)非集群索引，并在所有外鍵列中創(chuàng)建集群索引。3)在特定查詢中，復(fù)蓋一個(gè)表中的所有列。非集群索引的結(jié)構(gòu)、返回、使用堆的非集群索引、按行ID查找、渡邊杏使用集群索引(使用堆)、索引中未存儲(chǔ)實(shí)際數(shù)據(jù)、按行ID或集群索引鍵讀取、堆中

8、的非集群索引祖懷、行ID、索引部分、數(shù)據(jù)部分、使用集群索引CCD1表示對(duì)象使用的范圍。堆存儲(chǔ)實(shí)例、使用無索引查詢時(shí)的第1步、sysindexes表查詢、查找FirstIAM頁地址2、訪問IAM頁、查找分配的頁和范圍3、訪問IAM頁上找到的數(shù)據(jù)頁(如Select id、indid)如果要對(duì)表格執(zhí)行大量更新和插入，您可以在建立索引時(shí)使用較小的填滿系數(shù)，以釋放更多空間。如果這是不變的只讀表，則在創(chuàng)建索引時(shí)，可以使用大填充因子以減少索引的物理大小和磁盤讀取數(shù)。只能在創(chuàng)建索引時(shí)應(yīng)用填充系數(shù)。索引不僅提高了搜索選定行的速度，而且通常還提高了更新和刪除的速度。這是因?yàn)镾QL Server在更新或刪除行時(shí)必須

9、首先查找該行。使用索引位置行可以提高效率。通常，如果表中的索引不多，可以補(bǔ)充更新索引所需的額外開銷。，8.1.3索引查詢效率案例分析(興趣者自學(xué))，(1)公用術(shù)語摘要索引字段：在文件中定義索引的字段(一個(gè)或一組字段)。索引檔案(索引也是文件)。資料檔：已建立索引的檔案。注：索引文件比數(shù)據(jù)文件小得多(唱片少，域少)。(2)實(shí)例分析(詳細(xì)了解各種索引的基本工作原理)1)聚集索引，例如聚集索引字段是主關(guān)鍵字?；舅饕卜Q為實(shí)例1。記下索引文件中的第I個(gè)索引條目。其中k(i)是數(shù)據(jù)文件中的關(guān)鍵值。P(i)是存儲(chǔ)該數(shù)據(jù)記錄的磁盤塊地址的指針。如果唱片R的索引域值為K，如果K (I) K (I 1)，則

10、R存儲(chǔ)在p(i)的磁盤塊中。查詢唱片r的基本過程：在索引文件中查找滿足條件k(i)k k(i 1)的索引記錄。在數(shù)據(jù)文件中，將磁盤塊p(i)讀取到主存儲(chǔ)緩沖區(qū)中，然后在緩沖區(qū)中找到唱片r。使用默認(rèn)索引查找數(shù)據(jù)記錄比直接在數(shù)據(jù)文件中查找記錄節(jié)省時(shí)間。例如，有序檔案F具有唱片數(shù)r=30000、唱片長(zhǎng)度/L=100B、塊容量/塊B=1024B。因此，磁盤塊存儲(chǔ)唱片數(shù)B/L=10個(gè)/塊。直接存儲(chǔ)的f的磁盤塊數(shù)b=30000/10=3000塊。如果使用二進(jìn)制祖懷方法，則需要log230000=12次塊訪問才能在f中查找記錄。設(shè)置數(shù)據(jù)檔案F的密鑰字段長(zhǎng)度V=9B，索引文件的地址指針字段長(zhǎng)度P=6B，索引記

11、錄的長(zhǎng)度I=V P=15B。磁盤塊存儲(chǔ)索引唱片數(shù)B/I=68個(gè)/塊。索引文件中的唱片數(shù)30000/10=3000。存儲(chǔ)索引文件所需的磁盤塊數(shù)為3000/68=45個(gè)塊。使用二進(jìn)制祖懷方法時(shí)，在索引文件中查找記錄需要log245=6次訪問，以及對(duì)查詢記錄所需F的磁盤塊的訪問共需要7次磁盤訪問。由每個(gè)塊的第一部分組成！2)非群集索引您可以在一個(gè)文件中創(chuàng)建多個(gè)非群集索引，從而可以擁有多個(gè)非群集索引域。例如，索引字段(考慮一個(gè)字段)是未排序的字段，并且數(shù)據(jù)文件中聚集索引文件中唱片條件的記錄相同。索引文件中的第I個(gè)索引條目被記錄，索引文件按K(i)的大小順序排序。其中k(i)是數(shù)據(jù)文件中索引字段的值。P

12、(i)是存儲(chǔ)指向數(shù)據(jù)記錄所在磁盤塊地址的指針。注意：P(i)是塊指針，不是唱片指針。如果唱片R的索引域值為K，如果K (I) K (I 1)，則R存儲(chǔ)在p(i)的磁盤塊中。查詢唱片r的基本過程：在索引文件中查找滿足條件k(i)k k(i 1)的索引記錄。在數(shù)據(jù)文件中，將磁盤塊p(i)讀取到主存儲(chǔ)緩沖區(qū)中，然后在緩沖區(qū)中找到唱片r。使用非聚集索引查找數(shù)據(jù)記錄比直接在數(shù)據(jù)文件中查找記錄節(jié)省時(shí)間。例如，無序檔案F具有唱片數(shù)r=30000個(gè)、唱片長(zhǎng)度/L=100B、塊容量/塊B=1024B。因此，磁盤塊存儲(chǔ)唱片數(shù)B/L=10個(gè)/塊。直接存儲(chǔ)的f的磁盤塊數(shù)b=30000/10=3000塊。由于未排序，在線性祖懷算法F中查找記錄需要平均b/2=1500個(gè)塊存儲(chǔ)。實(shí)例，數(shù)據(jù)檔案F的索引字段長(zhǎng)度V=9B，索引文件的地址指針字段長(zhǎng)度P=6B，因此索引記錄的長(zhǎng)度I=V P=15B。磁盤塊存儲(chǔ)索引唱片數(shù)B/I=68。索引文件中的唱片數(shù)為30，000。存儲(chǔ)索引文件所需的磁盤塊數(shù)30000/68=442。使用