索引與自定義函數(shù)省名師優(yōu)質(zhì)課賽課獲獎課件市賽課一等獎課件

引入索引機制,是為了提升對數(shù)據(jù)庫查詢效率。索引、創(chuàng)建與管理方法6.1索引第1頁1INSERTINTOASDVALUES(7,'F',79,22)INSERTINTOASDVALUES(2,'B',89,21)INSERTINTOASDVALUES(5,'E',94,20)INSERTINTOASDVALUES(10,'D',76,22)INSERTINTOASDVALUES(6,'A',64,20)INSERTINTOASDVALUES(4,'C',88,21)INSERTINTOASDVALUES(1,'H',91,22)INSERTINTOASDVALUES(8,'G',68,19)CreatetableASD(NOintnotnull,NAMEnchar(4)notnull,SCOREnumeric(4,1)null,AGEintnotnull)go先建立一個名為“ASD”表，并插入統(tǒng)計：在未建立主鍵約束,并未建立索引時,統(tǒng)計顯示次序是物理次序第2頁21．索引概念(1)創(chuàng)建索引，能夠防止全表掃描，從而提升查詢速度.(2)索引是數(shù)據(jù)庫對象,分別用CREATE命令建立,用DROP命令刪除,用ALTER命令修改.(3)索引與與表（或視圖）關聯(lián)，并按表中指定列值排列次序映象表。建立索引后表存放由兩部分組成:一是用來存放表數(shù)據(jù)頁面;二是用來存放索引索引頁面。索引就存放在索引頁面上。(4)SQLServer讀取數(shù)據(jù)過程首先確定是否存在索引，然后查詢優(yōu)化器（負責生成查詢優(yōu)化執(zhí)行計劃組件）從表掃描和使用索引這兩種方法中，確定對于數(shù)據(jù)訪問哪種方式更為有效。(5)數(shù)據(jù)檢索方式:先搜索索引頁面，從中找到所需數(shù)據(jù)指針，再直接經(jīng)過指針從數(shù)據(jù)頁面中讀取數(shù)據(jù)。（6）應注意問題:

不應該在每一個列上都創(chuàng)建索引,以免降低系統(tǒng)速度。

插入、刪除或更新索引列比非索引列要花更長時間。

第3頁3索引次序1A2B3C4D5E6F7G8HCREATEINDEXASD_NAME_INDONASD(NAME,ASD)索引頁面數(shù)據(jù)頁面行定位器已按表中NAME字段建立索引表存放。第4頁4索引鍵值次序被引用數(shù)據(jù)行存放次序12345674713256行定位器索引頁面數(shù)據(jù)頁面第5頁52．索引存放結構(1)簇索引(Clusteredlndex)對表物理數(shù)據(jù)頁中數(shù)據(jù)按列進行排序，然后再重新存放到磁盤上。所以一個表只能有一個簇索引。簇索引查找數(shù)據(jù)很快。（2)適于使用簇索引情況

經(jīng)慣用于排序從表中檢索數(shù)據(jù)列。

經(jīng)常次序訪問列。每個索引行包含一個鍵值和一個指針，分別指向數(shù)據(jù)行。

(3）表中按次序排列序列（如拼音次序、日期和數(shù)字次序）在查詢時候不需要逐行查找，所以查詢速度快。

(4)以下數(shù)據(jù)類型列不可用作聚集索引鍵：ntext、text、varchar(max)、nvarchar(max)、varbinary(max)、xml或image

第6頁6CREATECLUSTEREDINDEXASD_CLU_NO

ONASD(NOASC)

建立簇索引后統(tǒng)計重新按指定列值排列了次序,且則行定位器中存放是簇索引索引鍵：索引頁行定位器數(shù)據(jù)頁第7頁72)非簇索引(Nonclusteredlndex)。非簇索引將行定位器按關鍵字值，用一定方式排序。則行定位器存放是指向數(shù)據(jù)行指針。非簇索引檢索效率較低。一個表最多能夠建248個非簇索引。索引列中數(shù)據(jù)頻繁更改時應建立非簇索引。第8頁8索引次序1A2B3C4D5E6F7G8HCREATEINDEXASD_NAME_INDONASD(NAME,ASD)索引頁面數(shù)據(jù)頁面行定位器

在非簇索引中，行定位器存放是指向數(shù)據(jù)行指針。數(shù)據(jù)沒有按索引鍵重新排列次序。第9頁912345674713256索引鍵值次序被引用數(shù)據(jù)行存放次序行定位器第10頁10CREATECLUSTEREDINDEXAsd_clu_noˉcf2ONASD(NOASC)建立了聚集索引后,統(tǒng)計按索引順序重新物理地排列了統(tǒng)計次序.一個表只能有一種物理排列方式,所以一個表只能建一個聚集索引.第11頁113．索引鍵組成依據(jù)索引鍵組成，能夠將索引分為三種類型。1)唯一索引創(chuàng)建唯一索引，可確保表內(nèi)索引列中不包含重復數(shù)據(jù)內(nèi)容。2)復合索引在一個表中，經(jīng)過連接或附接兩個或多個列值而創(chuàng)建索引。3)覆蓋索引當索引中包含了需要全部信息時，這個索引稱為覆蓋索引。它鍵值包含了滿足查詢條件全部數(shù)據(jù)。第12頁126．1．2創(chuàng)建索引1.創(chuàng)建索引時，要注意幾點:(1)當在表中創(chuàng)建主關鍵字約束或唯一性約束時，SQLServer自動創(chuàng)建一個唯一性索引。(2)假如表中已經(jīng)有數(shù)據(jù)，那么在創(chuàng)建索引時，SQLServer會檢驗數(shù)據(jù)正當性。當有不正當數(shù)據(jù)時，創(chuàng)建索引將失敗。(3)當有多個列作為關鍵字時，應創(chuàng)建復合索引，即索引包含有兩個或多個列。(4)基于相同列但列次序不一樣復合索引也是不一樣。第13頁13簡單建立索引命令CREATEINDEX[索引類型]<索引名>ON<表或視圖>(列名排序方式[，…n])索引類型有:CLUSTERED------------簇索引NONCLUSTERED------非簇索引UNIQUE------------------唯一索引第14頁142.創(chuàng)建索引完整格式：CREATE[索引類型]INDEX<索引名>ON<表或視圖名>(列名1[ASCIDESC][,…n])[WITH<索引選項>][ON<文件組>]其中:索引類型有:UNIQUE、CLUSTERED、NONCLUSGTERED索引選項為以下屬性組合：{PADINDEXFILLFACTOR=填充因子IGNORE_DUP_KEYDROP_EXISTINGSTATISTICS_NORECOMPUTESORT_IN_TEMPDB其中，第15頁151)UNIQUE創(chuàng)建唯一索引。創(chuàng)建唯一索引后，假如執(zhí)行INSERT或UPDATE操作后會造成有重復索引值出現(xiàn)時，該INSERT或UPDATE操作會失敗。2)CLUSTERED指明創(chuàng)建索引為簇索引。默認為創(chuàng)建索引為非簇索引。3)NONCLUSTERED創(chuàng)建索引為非簇索引。第16頁164)PAD_INDEX指定填充索引內(nèi)部節(jié)點行數(shù)，最少應大于等于兩行。PAD_INDEX選項只有在FILLFACTOR選項指定后才起作用，因為PAD_INDEX使用與FILLFACTOR相同百分比。默認時SQLServer確保每個索引頁最少有能容納一條最大索引行數(shù)據(jù)空閑空間。假如FILLFACTOR指定百分比不夠容納一行數(shù)據(jù)，SQLServer會自動內(nèi)部更改百分比。第17頁175)FILLFACTOR；填充因子它指定創(chuàng)建索引時每個索引頁數(shù)據(jù)占索引頁大小百分比。fillfactor值為1到100，它其實同時指出了索引頁保留自由空間占索引頁大小百分比，即100-fillfactor。

第18頁186)IGNOREDUPKEY此選項控制了包含一個唯一約束列中插入重復數(shù)據(jù)時SQLServer所作反應。當選擇此選項時，SOLServer返回一個錯誤信息，跳過此行數(shù)據(jù)插入繼續(xù)執(zhí)行下面插入數(shù)據(jù)操作。當沒選擇此選項時，SQLServer不但會返回一個錯誤信息，還會回滾整個INSERT語句。7)DROPEXISTING指定要刪除同名索引并重新創(chuàng)建。第19頁19

[例]在圖書表中為出版社創(chuàng)建索引。CREATEINDEXidx_pressONbook(press)[例]創(chuàng)建出版社和作者復合索引。使用DROPEXISTING是因為前面例子已經(jīng)創(chuàng)建了索引idx_press，所以先刪除同名索引，然后再創(chuàng)建新索引。CREATEINDEXidx_pressONbook(press，author)WITHDROP_EXISTING第20頁20[例6-3]創(chuàng)建唯一非簇索引。CREATEUNIQUEINDEXidx_pressONbook(book_id)WITHDROP_EXISTING[例6-4]使用填充因子創(chuàng)建唯一非簇索引。CREATEUNIQUEINDEXidx_pressONbook(book_id)WITHPAD_INDEX,FILLFACTOR=50,DROPEXISTING第21頁216．1．3刪除索引1.索引刪除語法：DROPlNDEX表名.索引名[,…n]2.幾點說明：(1)DROPlNDEX命令不能刪除由CREATETABLE或ALTERTABLE命令創(chuàng)建PRIMARYKEY或UNIQUE約束索引。(2)不能刪除系統(tǒng)表中索引。(3)在刪除簇索引時，表中全部非簇索引都將被重建。第22頁226．1．4索引優(yōu)化關于創(chuàng)建索引提議以下。(1)將更新盡可能多行查詢寫入單個語句內(nèi)，而不要使用多個查詢更新相同行(2)使用索引優(yōu)化分析查詢并取得索引提議。(3)對聚集索引使用整型鍵，另外，在唯一列、非空列或標識列上創(chuàng)建聚集索引能夠取得較佳性能。(4)在查詢經(jīng)慣用到全部列上創(chuàng)建非聚集索引。(5)檢驗列唯一性。第23頁237．2自定義函數(shù)第24頁247.2.1自定義函數(shù)基本概念1.自定義函數(shù):是用戶為實當代碼封裝和重用,將一組T-SQL語句按一定格式定義得到,并有輸入?yún)?shù),運行后有返回值。2.自定義函數(shù)優(yōu)點：(1)允許模塊化程序設計。一次創(chuàng)建可屢次調(diào)用。(2)執(zhí)行速度更加快(首次優(yōu)化編譯，今后直接運行)。(3)降低網(wǎng)絡流量。3.自定義函數(shù)與存放過程比較項目自定義函數(shù)存放過程參數(shù)不允許輸出參數(shù)允許多個輸入／輸出參數(shù)返回值有且只有一個返回值能夠沒有返回值調(diào)用能夠嵌入查詢語句中使用必須單獨調(diào)用第25頁257．2．2自定義函數(shù)三種類型1．標量函數(shù)(1)函數(shù)返回單個數(shù)值(返回值類型在RETURNS子句中指定)。(2)函數(shù)返回單個數(shù)值，返回由BEGIN…END塊指定多個值。返回值不可為text、ntext、image、cursor和timestamp數(shù)據(jù)類型。第26頁262．表值函數(shù)(1)內(nèi)嵌表值函數(shù)返回由選擇結果組成統(tǒng)計集—表。它能夠替換視圖,且比視圖邏輯功效愈加強大。(2)多語句表值函數(shù)返回由選擇結果組成統(tǒng)計集—表,函數(shù)需要由BEGIN…END限定函數(shù)體。第27頁27多語句表值函數(shù)主體中只允許使用語句：賦值語句。除錯誤捕捉語句TRY…CATCH外流程控制語句。定義局部數(shù)據(jù)變量和局部游標DECLARE語句。SELECT語句，其中選擇列表包含為局部變量分配值表示式。游標操作，該操作引用在函數(shù)中申明、打開、關閉和釋放局部游標。只允許使用以INTO子句向局部變量賦值FETCH語句；不允許使用將數(shù)據(jù)返回到客戶端FETCH語句。修改table局部變量INSERT、UPDATE和DELETE語句。調(diào)用擴展存放過程EXECUTE語句。

第28頁287．2．3創(chuàng)建與使用用戶自定義函數(shù)CREATEFUNCTION<函數(shù)名>([@參數(shù)名數(shù)據(jù)類型[=默認值]],…])RETURNS返回值數(shù)據(jù)類型[WITH選項]AS[BEGIN]SQL語句RETURN返回表示式[END]

其中：選項有二：（1）ENCRYPTION能夠實現(xiàn)用戶自定義函數(shù)加密；（2）SCHEMABINDING將自定義函數(shù)綁定到它所引用數(shù)據(jù)庫對象。第29頁29[例7-17]自定義標量函數(shù)，實現(xiàn)對圖書價格高與低評價。

USE圖書館IfEXISTS(SELECTnameFROMsysobjectsWHEREname='fc_price'ANDtype='FN')DROPFUNCTIONfc_priceGOCREATEFUNCTIONfc_price(@priceinputmoney)RETURNSnvarchar(10)BEGINDECLARE@returnstrnvarchar(10)If@priceinput>40SET@returnstr='較貴圖書'ELSESET@returnstr='廉價圖書'RETURN@returnstrEND使用該函數(shù)。SELECTtop20book_name,price,dbo.fc_price(price)FROMbook

第30頁30[例7-18]自定義內(nèi)嵌表值函數(shù)fc_press，依據(jù)指定出版社參數(shù)查詢該出版社出版圖書，返回結果統(tǒng)計集。USElibraryGOCREATEFUNCTIONfc_press(@pressvarchar(30))RETURNStableASRETURN(SELECTbookname，author，press，priceFROMbookWHEREpress=@press)GO下面語句說明了怎樣使用新建立內(nèi)嵌表值函數(shù)來獲取清華大學出版社出版圖書信息。

select*Fromfc_press('清華大學出版社')第31頁31CreatefunctionXY_fc(@專業(yè)nvarchar(8))ReturnstableASReturnselect*from班級

WHERE專業(yè)=@專業(yè)GOselect*FROMXY_fc('軟件工程')第32頁32第33頁33[例7-19]自定義多語句表值函數(shù)fc_press_1，依據(jù)指定出版社參數(shù)查詢該出版社出版圖書，返回結果統(tǒng)計集。請注意與定義內(nèi)嵌表值函數(shù)fc_press對比。

USElibraryGOCREATEFUNCTIONfc_press_1(@pressnvarchar(30))RETURNS@tb_presstable(book_namenvarChar(30)NOTNULL，authornvarchar(20)NULL，pressnvarChar(30)NULL，plicemoneyNULL)ASBEGININSERT@tb_PressSELECTbook_name,author,press,priceFROMbookWHEREpress=@pressRETURNENDGO下面這兩個語句分別調(diào)用多語句表值函數(shù)fcjoress_l宋查詢清華大學出版社和科學出版社出版圖書。SELECT*fromdbo．fcpressl(‘清華大學出版社’)GOSELECT*fromdbo．fc_press_l(‘科學出版社’)·GO第34頁347．2．4自定義函數(shù)管理1．查看自定義函數(shù)EXECsp_help<函數(shù)名>如要查看fc』rice信息詳細語句為：EXECsp_helpfcprice利用這個語句，能夠看到函數(shù)名稱及相關參數(shù)。但要看到函數(shù)詳細定義，需要用系統(tǒng)存放過程sp—helptext。其語法以下：EXECsp_helptext函數(shù)名查看自定義函數(shù)fcprice語句以下：EXECsp_helptextfc_price

第35頁352．修改自定義函數(shù)修改自定義函數(shù)