SQL Server–索引

一,索引的概述

 

         1,概念:  資料庫索引是對資料表中一個或多個列的值進行排序的結構,就像一本書的目錄一樣,索引提供了在行中快速查詢特定行的能力.

 

      2,優缺點:

            2.1優點:  1,大大加快搜尋資料的速度,這是引入索引的主要原因.

                            2,建立唯一性索引,保證資料庫表中每一行資料的唯一性.

                            3,加速表與表之間的串連,特別是在實現資料的參考完整性方面特別有意義.

                            4,在使用分組和排序子句進行資料檢索時,同樣可以減少其使用時間.

            2,2缺點:  1,索引需要佔用物理空間,叢集索引占的空間更大.

                            2,建立索引和維護索引需要耗費時間,這種時間會隨著資料量的增加而增加.

                            3,當向一個包含索引的列的資料表中添加或者修改記錄時,SQL server 會修改和維護相應的索引，這樣增加系統的額外開銷，降低處理速度。

 

    3，索引的分類：

             1，按儲存結構可分為：

                      a,叢集索引：指實體儲存體順序與索引順序完全相同，它由上下兩層組成，上層為索引頁，下層為資料頁，只有一種排序方式，因此每個表中只能建立一個叢集索引。

                      b,非叢集索引：指儲存的資料順序一般和表的物理資料的儲存結構不同。通過下表我們可以分析出：（其中在學號上建立非叢集索引）

              2，根基索引索引值是否唯一，可以判定是否為唯一索引；基於多個欄位的組合建立索引的為複合式索引。  二，索引的操作：

 

        1，建立：（1）,原則：a,只有表的所有者可以在同一個表中建立索引；

                                                     b,每個表中只可以建立一個叢集索引；

                                                     c,每個表中最多可以建立249個非叢集索引；    

                                                     d,在經常查詢的欄位上建立索引；

                                                     e,定義text,image和bit資料類型的裂傷不能建立索引；

                                                     f,在外鍵列上可以建立索引，主鍵上一定要有索引；

                                                     g,在那些重複值比較多的，查詢較少的列上不要建立索引。

                      （2），方法：a,使用SQL server Management Studio建立索引。

                                                  b,使用T-SQL語句中的create Index語句建立索引

                                                  c,使用Create table或者alter Table語句為表列定義主鍵約束或者唯一性限制式時，會自動建立主鍵索引和惟一索引。

                   這裡說說T-sql語句建立索引：

                    文法：

create relational index create[unique][clustered|nonclustered]   index index_name  on<object>(cloumn[asc|desc][,……n])  [include (column_name[,……n])]     [with(<relational_index_option>[,……n])] [onfilegroup_name] 

 

                        說明：1，include (column_name[,……n])指定要添加到非叢集索引的分葉層級的非鍵列。

                                     2，on filegroup_name，為指定檔案組建立指定索引。

                   例如：在course表中，對“課程代號”列建立叢集索引zindex.

                                         

use db_student create clustered index zindex on course(課程代號)

        2，查看索引：（1），使用SQL ServerManagement Studio查看索引資訊

                       （2），使用系統預存程序查詢索引資訊，用SP_helpindex可以返回表中的所有索引資訊

                       例如：查看course表的索引資訊

                                    

use db_student   execsp_helpindex course

        3,修改索引：

                    （1），在SQL Server Management Studio 中修改索引

                    （2），使用Alter Index語句修改索引

                        在這裡為大家舉一個例子：

                       在course資料表中，修改所有的索引，並指定選項

                      

use db_student    alterindex all on course rebuild with (fillfactor=80,sort_in_tempdb=on,statistics_norecompute=on)

        4，刪除索引：

                      （1），使用SQL Server Management  Studio 刪除索引

                       （2），使用Drop index語句刪除索引

                             例如：在course表中，刪除zindex索引

                                       

use db_student drop index course.zindex

 三，索引的分析和維護：

 

        
分析：1，使用showplan 語句

                              文法：set showplan_all{on|off}，set showplan_next{on|off}

                         例子：顯示表course的課程代號，課程類型，課程內容，並顯示查詢過程

                                                

use db_student set showplan_all on  select 課程代號，課程類型 課程內容 from course where 課程內容='loving'

                         2,使用statistics io語句

                          文法：statistics io{on|off}   on和off分別為顯示和不顯示，使用方法和上一樣。

         維護： 1，使用dbcc showcontig語句,顯示指定表的資料和索引的片段資訊。當對錶中進行大量修改或添加資料後，應該執行此語句查看有無片段。

                      文法：dbcc  showcontig[{table_name|table_id|view_name|view_id},index_name|index_id] with fast

                       2,使用dbcc dbreindex語句，意思是重建資料庫中表的一個或多個索引。

                   文法：

dbcc dbreindex (['database.owner.table_name'[,index_name[,fillfactor]]])  [withno_infomsgs]

                   說明： database.owner.table_name，重建立立索引的表名              

                             index_name，是要重建的索引名

                                   fillfactor，要建立索引時每個索引頁上要用於儲存資料的空間百分比。  

                                  with no_infomsgs，禁止顯示所有資訊性訊息     

   

                     3，使用dbcc indexdefrag,整理指定的表或視圖的叢集索引和輔助索引片段。

                          文法：

dbcc  indexdefrag   ({database_name|database_id|0},{table_name|table_id|'view_name'|view_id},{index_name|index_id})   with no_infomsgs                  

              總結，只有我們對索引有了充分了熟悉；我們掌握了索引的增刪改查四項基本操作，學會利用SQL Server Manager  Sdudio去實現這些功能，和學會利用T-SQL語句去實現（自我感覺利用SQL Server Manager Sdudio 簡單一些）；當然還要懂得學會分析和維護索引，這樣才會更好的讓它為咱們服務！