SQL的主鍵和外鍵的作用 索引

1、SQL Server中索引使用及維護 2008-11-25 11:14 小 大 來源: 站長論壇 評論: 我來評論 分享至: 百度權(quán)重查詢 詞庫網(wǎng) 網(wǎng)站監(jiān)控 服務(wù)器監(jiān)控 SEO監(jiān)控 手機游戲 iPhone游戲 在應(yīng)用系統(tǒng)中,尤其在聯(lián)機事務(wù)處理系統(tǒng)中,對數(shù)據(jù)查詢及處理速度已成為衡量應(yīng)用系統(tǒng)成敗的標準。而采用索引來加快數(shù)據(jù)處理速度也成為廣大數(shù)據(jù)庫用戶所接受的優(yōu)化方法。 在良好的數(shù)據(jù)庫設(shè)計基礎(chǔ)上，能有效地使用索引是SQL Server取得高性能的基礎(chǔ)，SQL Server采用基于代價的優(yōu)化模型，他對每一個提交的有關(guān)表的查詢，決定是否使用索引或用哪一個索引。因為查詢執(zhí)行的大部分開銷是磁盤I/O，使用索

2、引 提高性能的一個主要目標是避免全表掃描，因為全表掃描需要從磁盤上讀表的每一個數(shù)據(jù)頁，如果有索引指向數(shù)據(jù)值，則查詢只需讀幾次磁盤就能了。所以如果建 立了合理的索引，優(yōu)化器就能利用索引加速數(shù)據(jù)的查詢過程。不過，索引并不總是提高系統(tǒng)的性能，在增、刪、改操作中索引的存在會增加一定的工作量，因此，在 適當?shù)牡胤皆黾舆m當?shù)乃饕牟缓侠淼牡胤絼h除次優(yōu)的索引，將有助于優(yōu)化那些性能較差的SQL Server應(yīng)用。實踐表明，合理的索引設(shè)計是建立在對各種查詢的分析和預(yù)測上的，只有正確地使索引和程式結(jié)合起來,才能產(chǎn)生最佳的優(yōu)化方案。本文就 SQL Server索引的性能問題進行了一些分析和實踐。 一、聚簇索引(c

3、lustered indexes的使用 聚簇索引是一種對磁盤上實際數(shù)據(jù)重新組織以按指定的一個或多個列的值排序。由于聚簇 索引的索引頁面指針指向數(shù)據(jù)頁面，所以使用聚簇索引查找數(shù)據(jù)幾乎總是比使用非聚簇索引快。每張表只能建一個聚簇索引，并且建聚簇索引需要至少相當該表 120%的附加空間，以存放該表的副本和索引中間頁。建立聚簇索引的思想是： 1、大多數(shù)表都應(yīng)該有聚簇索引或使用分區(qū)來降低對表尾頁的競爭，在一個高事務(wù)的環(huán)境中，對最后一頁的封鎖嚴重影響系統(tǒng)的吞吐量。 2、在聚簇索引下，數(shù)據(jù)在物理上按順序排在數(shù)據(jù)頁上，重復(fù)值也排在一起，因而在那些 包含范圍檢查(between、=或使用group by或ord

4、er by的查詢時，一旦找到具有范圍中第一個鍵值的行，具有后續(xù)索引值的行確保物理上毗連在一起而不必進一步搜索，避免了大范圍掃描，能大大提高查詢速度。 3、在一個頻繁發(fā)生插入操作的表上建立聚簇索引時，不要建在具有單調(diào)上升值的列(如IDENTITY上，否則會經(jīng)常引起封鎖沖突。 4、在聚簇索引中不要包含經(jīng)常修改的列，因為碼值修改后，數(shù)據(jù)行必須移動到新的位置。 5、選擇聚簇索引應(yīng)基于where子句和連接操作 秒在op_date上建非聚簇索引查詢1 57秒查詢2 57秒在op_date上建聚簇索引查詢1 1秒查詢2 52秒在pay_month、op_date、pri_surplus1上建索引查詢1 34

5、秒查詢2 1秒在op_date、pay_month、pri_surplus1上建索引查詢1 1秒查詢2 1秒 從以上查詢效果分析，索引的有無，建立方式的不同將會導(dǎo)致不同的查詢效果，選擇什么樣的索引基于用戶對數(shù)據(jù)的查詢條件,這些條件體現(xiàn)于where從句和join表達式中。一般來說建立索引的思路是： (1、主鍵時常作為where子句的條件，應(yīng)在表的主鍵列上建立聚簇索引，尤其當經(jīng)常用他作為連接的時候。 (2、有大量重復(fù)值且經(jīng)常有范圍查詢和排序、分組發(fā)生的列，或非常頻繁地被訪問的列，可考慮建立聚簇索引。 (3、經(jīng)常同時存取多列，且每列都含有重復(fù)值可考慮建立復(fù)合索引來覆蓋一個或一組查詢，并把查詢引用最頻

6、繁的列作為前導(dǎo)列，如果可能盡量使關(guān)鍵查詢形成覆蓋查詢。 (4、如果知道索引鍵的所有值都是唯一的，那么確保把索引定義成唯一索引。 (5、在一個經(jīng)常做插入操作的表上建索引時，使用fillfactor(填充因子來減少頁分裂，同時提高并發(fā)度降低死鎖的發(fā)生。如果在只讀表上建索引，則能把fillfactor置為100。 (6、在選擇索引鍵時，設(shè)法選擇那些采用小數(shù)據(jù)類型的列作為鍵以使每個索引頁能夠容納盡可能多的索引鍵和指針，通過這種方式，可使一個查詢必須遍歷的索引頁面降到最小。此外，盡可能地使用整數(shù)為鍵值，因為他能夠提供比所有數(shù)據(jù)類型都快的訪問速度。 五、索引的維護 上面講到,某些不合適的索引影響到SQL

7、Server的性能,隨著應(yīng)用系統(tǒng)的運行,數(shù)據(jù)不斷地發(fā)生變化,當數(shù)據(jù)變化達到某一個程度時將會影響到索引的使用。這時需要用戶自己來維護索引。索引的維護包括： 1、重建索引 隨著數(shù)據(jù)行的插入、刪除和數(shù)據(jù)頁的分裂，有些索引頁可能只包含幾頁數(shù)據(jù)，另外應(yīng)用在執(zhí)行大塊I/O的時候，重建非聚簇索引能降低分片，維護大塊I/O的效率。重建索引實際上是重新組織B-樹空間。在下面情況下需要重建索引： (1、數(shù)據(jù)和使用模式大幅度變化。 (2、排序的順序發(fā)生改動。 (3、要進行大量插入操作或已完成。 (4、使用大塊I/O的查詢的磁盤讀次數(shù)比預(yù)料的要多。 (5、由于大量數(shù)據(jù)修改，使得數(shù)據(jù)頁和索引頁沒有充分使用而導(dǎo)致空間的使

8、用超出估算。 (6、dbcc檢查出索引有問題。 當重建聚簇索引時,這張表的所有非聚簇索引將被重建。 2、索引統(tǒng)計信息的更新 當在一個包含數(shù)據(jù)的表上創(chuàng)建索引的時候，SQL Server會創(chuàng)建分布數(shù)據(jù)頁來存放有關(guān)索引的兩種 統(tǒng)計信息：分布表和密度表。優(yōu)化器利用這個頁來判斷該索引對某個特定查詢是否有用。但這個統(tǒng)計信息并不 動態(tài)地重新計算。這意味著，當表的數(shù)據(jù)改動之后，統(tǒng)計信息有可能是過時的，從而影響優(yōu)化器追求最有工作的目標。因此，在下面情況下應(yīng)該運行update statistics命令： (1、數(shù)據(jù)行的插入和刪除修改了數(shù)據(jù)的分布。 (2、對用truncate table刪除數(shù)據(jù)的表上增加數(shù)據(jù)行。

9、(3、修改索引列的值。 六、結(jié)束語 實踐表明，不恰當?shù)乃饕粌H于事無補，反而會降低系統(tǒng)的執(zhí)行性能。因為大量的索引在插入、修改和刪除操作時比沒有索引花費更多的系統(tǒng)時間。例如下面情況下建立的索引是不恰當?shù)模?1、在查詢中非常少或從不引用的列不會受益于索引，因為索引非常少或從來不必搜索基于這些列的行。 2、只有兩個或三個值的列，如男性和女性(是或否，從不會從索引中得到好處。 另外，鑒于索引加快了查詢速度，但減慢了數(shù)據(jù)更新速度的特點?？赏ㄟ^在一個段上建表，而在另一個段上建其非聚簇索引，而這兩段分別在獨立的物理設(shè)備上來改善操作性能。 索引： 進入查詢窗口后，輸入下面的語句： CREATE INDEX m

10、ycolumn_index ON mytable (myclumn 這個語句建立了一個名為mycolumn_index的索引。你可以給一個索引起任何名字，但你應(yīng)該在索引名中包含所索引的字段名，這對你將來弄清楚建立該索引的意圖是有幫助的。 注意： 在本書中你執(zhí)行任何SQL語句，都會收到如下的信息： This command did not return data,and it did not return any rows 這說明該語句執(zhí)行成功了。 索引mycolumn_index對表mytable的mycolumn字段進行。這是個非聚簇索引，也是個非唯一索引。（這是一個索引的缺省屬性） 如果你

11、需要改變一個索引的類型，你必須刪除原來的索引并重建 一個。建立了一個索引后，你可以用下面的SQL語句刪除它： DROP INDEX mytable.mycolumn_index 注意在DROP INDEX 語句中你要包含表的名字。在這個例子中，你刪除的索引是mycolumn_index，它是表mytable的索引。 要建立一個聚簇索引，可以使用關(guān)鍵字CLUSTERED。）記住一個表只能有一個聚簇索引。（這里有一個如何對一個表建立聚簇索引的例子： CREATE CLUSTERED INDEX mycolumn_clust_index ON mytable(mycolumn 如果表中有重復(fù)的記錄，

12、當你試圖用這個語句建立索引時，會出現(xiàn)錯誤。但是有重復(fù)記錄的表也可以建立索引；你只要使用關(guān)鍵字ALLOW_DUP_ROW把這一點告訴SQL Sever即可： CREATE CLUSTERED INDEX mycolumn_cindex ON mytable(mycolumn WITH ALLOW_DUP_ROW 這個語句建立了一個允許重復(fù)記錄的聚簇索引。你應(yīng)該盡量避免 在一個表中出現(xiàn)重復(fù)記錄，但是，如果已經(jīng)出現(xiàn)了，你可以使用這種方法。 要對一個表建立唯一索引，可以使用關(guān)鍵字UNIQUE。對聚簇索引和非聚簇索引都可以使用這個關(guān)鍵字。這里有一個例子： CREATE UNIQUE COUSTERED

13、INDEX myclumn_cindex ON mytable(mycolumn 這是你將經(jīng)常使用的索引建立語句。無論何時，只要可以，你應(yīng)該盡量對一個對一個表建立唯一聚簇索引來增強查詢操作。 最后，要建立一個對多個字段的索引復(fù)合索引在索引建立語句中同時包含多個字段名。下面的例子對firstname和lastname兩個字段建立索引： CREATE INDEX name_index ON username(firstname,lastname 這個例子對兩個字段建立了單個索引。在一個復(fù)合索引中，你最多可以對16個字段進行索引。 用事務(wù)管理器建立索引 用事務(wù)管理器建立索引比用SQL語句容易的多。使

14、用事務(wù)管理器，你可以看到已經(jīng)建立的索引的列表，并可以通過圖形界面選擇索引選項。 使用事務(wù)管理器你可以用兩種方式建立索引：使用Manage Tables窗口或使用Manage Indexes窗口。 要用Manage Tables 窗口建立一個新索引，單擊按鈕Advanced Options(它看起來象一個前面有一加號的表。這樣就打開了Advanced Options對話框。這個對話框有一部分標名為Primary Key（見圖11.1）。 圖11。1 要建立一個新索引，從下拉列表中選擇你想對之建立索引的字段名。如果你想建立一個對多字段的索引，你可以選擇多個字段名。你還可以選擇索引是聚簇的還是非聚簇

15、的。在保存表信息后，索引會自動被建立。在Manage Tables窗口中的字段名旁邊，會出現(xiàn)一把鑰匙。 你已經(jīng)為你的表建立了“主索引”。主索引必須對不包含空值的字段建立。另外，主索引強制一個字段成為唯一值字段。 要建立沒有這些限制的索引，你需要使用Manage Indexes窗口。從菜單中選擇Manage|Indexes，打開Manage Indexes 窗口。在Manage Indexes 窗口中，你可以通過下拉框選擇表和特定的索引。（見圖11.2）。要建立一個新索引，從Index下拉框中選擇New Index.，然后就可以選擇要對之建立索引的字段。單擊按鈕Add，把字段加人到索引中。 圖1

16、1。2 你可以為你的索引選擇許多不同的選項。例如，你可以選擇該索引是聚簇的還是非聚簇的。你還可以指定該索引為唯一索引。設(shè)計好索引后，單擊按鈕Build，建立該索引。 注意： 唯一索引是指該字段不能有重復(fù)的值，而不是只能建立這一個索引。 SQL核心語句 在第十章，你學(xué)會了如何用SQL SELECT 語句從一個表中取數(shù)據(jù)。但是，到現(xiàn)在為止，還沒有討論如何添加，修改或刪除表中的數(shù)據(jù)。在這一節(jié)中，你將學(xué)習(xí)這些 內(nèi)容。 插入數(shù)據(jù) 向表中添加一個新記錄，你要使用SQL INSERT 語句。這里有一個如何使用這種語句的例子： INSERT mytable (mycolumn VALUES (some dat

17、a 這個語句把字符串some data插入表mytable的mycolumn字段中。將要被插入數(shù)據(jù)的字段的名字在第一個括號中指定，實際的數(shù)據(jù)在第二個括號中給出。 INSERT 語句的完整句法如下： INSERT INTO table_name|view_name (column_list DEFAULT VALUES | Values_list | select_statement 如果一個表有多個字段，通過把字段名和字段值用逗號隔開，你可以向所有的字段中插入數(shù)據(jù)。假設(shè)表mytable有三個字段first_column,second_column,和third_column。下面的INSERT

18、語句添加了一條三個字段都有值的完整記錄： INSERT mytable (first_column,second_column,third_column VALUES (some data,some more data,yet more data 注意： 你可以使用INSERT語句向文本型字段中插入數(shù)據(jù)。但是，如果你需要輸入很長的字符串，你應(yīng)該使用WRITETEXT語句。這部分內(nèi)容對本書來說太高級了，因此不加討論。要了解更多的信息，請參考Microsoft SQL Sever 的文檔。 如果你在INSERT 語句中只指定兩個字段和數(shù)據(jù)會怎么樣呢？換句話說，你向一個表中插入一條新記錄，但有一個字

19、段沒有提供數(shù)據(jù)。在這種情況下，有下面的四種可能： 如果該字段有一個缺省值，該值會被使用。例如，假設(shè)你插入新記錄時沒有給字段third_column提供數(shù)據(jù)，而這個字段有一個缺省值some value。在這種情況下，當新記錄建立時會插入值some value。 如果該字段可以接受空值，而且沒有缺省值，則會被插入空值。 如果該字段不能接受空值，而且沒有缺省值，就會出現(xiàn)錯誤。你會收到錯誤信息： The column in table mytable may not be null. 最后，如果該字段是一個標識字段，那么它會自動產(chǎn)生一個新值。當你向一個有標識字段的表中插入新記錄時，只要忽略該字段，標識

20、字段會給自己賦一個新值。 注意： 向一個有標識字段的表中插入新記錄后，你可以用SQL變量identity來訪問新記錄 的標識字段的值?？紤]如下的SQL語句： INSERT mytable (first_column VALUES(some value INSERT anothertable(another_first,another_second VALUES(identity,some value 如果表mytable有一個標識字段，該字段的值會被插入表anothertable的another_first字段。這是因為變量identity總是保存最后一次插入標識字段的值。 字段another

21、_first應(yīng)該與字段first_column有相同的數(shù)據(jù)類型。但是，字段another_first不能是應(yīng)該標識字段。Another_first字段用來保存字段first_column的值。 刪除記錄 要從表中刪除一個或多個記錄，需要使用SQL DELETE語句。你 。是能確定另一張表記錄的字段，用于保持數(shù)據(jù)的一致性。比如，A表中的一個字段，是B表的主鍵，那他就可以是A表的外鍵。 二、主鍵、外鍵和索引的區(qū)別 定義： 主鍵-唯一標識一條記錄，不能有重復(fù)的，不允許為空 外鍵-表的外鍵是另一表的主鍵, 外鍵可以有重復(fù)的, 可以是空值 索引-該字段沒有重復(fù)值，但可以有一個空值 作用： 主鍵-用來保證

22、數(shù)據(jù)完整性 外鍵-用來和其他表建立聯(lián)系用的 索引-是提高查詢排序的速度 個數(shù)： 主鍵-主鍵只能有一個 外鍵-一個表可以有多個外鍵 索引-一個表可以有多個唯一索引 創(chuàng)建SQL的主鍵和外鍵約束的方法： create table Student -建表格式:create table 自定義的表名 ( -字段名一般為有一定意義的英文 StudentName nvarchar(15, - 格式：字段名類型(括號里面的是允許輸入的長度 StudentAge int, -int型的后面不需要接長度 StudentSex nvarchar(2 -最后一個字段后面不要逗號 -在創(chuàng)建表時就可以對字段加上約束: c

23、reate table Student ( StudentNo int PRIMARY KEY IDENTITY(1,1, -加主鍵約束,還有標識列屬性（兩者構(gòu)成實體完整性） StudentName nvarchar(15 not null, -加非空約束,不加not null 默認為：可以為空 StudentSchool text(20 FOREIGN KEY REFERENCES SchoolTable(SchoolName, -加外鍵約束,格式:FOREIGN KEY REFERENCES 關(guān)聯(lián)的表名(字段名 StudentAge int DEFAULT (0, -加默認值約束 Stud

24、entSex nvarchar(2 CHECK(StudentSex=N男 or StudentSex=N女 -加檢查約束,格式:check (條件表達式 -如果在表創(chuàng)建好了以后再加約束，則格式分別為： - 主鍵: alter table 表名 add constraint PK_字段名-PK為主鍵的縮寫，字段名為要在其上創(chuàng)建主鍵的字段名,PK_字段名就為約束名 primary key (字段名 -字段名同上 -唯一約束: alter table 表名 add constraint UQ_字段名 unique (字段名 -外鍵約束： alter table 表名 add constraint

25、FK_字段名-FK為外鍵的縮寫 foreign key (字段名 references 關(guān)聯(lián)的表名(關(guān)聯(lián)的字段名 -注意關(guān)聯(lián)的表名和關(guān)聯(lián)的字段名 alter table 表A add constraint FK_B foreign key (ticket_no references 表B(ticket_no alter table 表A add constraint FK_C foreign key (person_no references 表C(person_no alter table 成績表 add constraint FK_StudentNo foreign key (Studen

26、tNo references Student (StudentNo ON UPDATE CASCADE ON DELETE CASCADE 級聯(lián)更新，級聯(lián)刪除，這樣在刪除主表Student時，成績表中該學(xué)生的所有成績都會刪除。 -檢查約束: alter table 表名 add constraint CK_字段名 check (條件表達式 -條 件表達式中的條件用關(guān)系運算符連接 -默認值約束: alter table 表名 add constraint DF_字段名 default 默認值 for 字段名-其中的默認值為你想要默認的值，注意for -刪除創(chuàng)建的約束: alter table 表名 drop