Sql Server系列：索引基礎

libingql發表於2014-11-26
SQLServer索引

1 索引概念

　　索引用於快速查詢在某個列中某個特定值的行，不使用索引，資料庫必須從第1條記錄開始讀完整個表，知道找出需要的行。表越大，查詢資料所花費的時間越多。如果表中查詢的列有索引，資料庫能快速到達一個位置去查詢資料，而不必遍歷所有資料。

　　索引是一個單獨的、儲存在磁碟上的資料庫結構，包含對資料表裡所有記錄的引用指標。使用索引用於快速找出一個或多個列中有特定值的行，對相關列使用索引是降低查詢操作時間的最佳途徑。索引包含由表或試圖中的一列或多列生成的鍵。

　　索引的優點：

　　◊ 通過建立唯一索引，可以保證資料庫表中每一行資料的唯一性。

　　◊ 可以大大加快資料的查詢速度，這也是建立索引的最主要的原因。

　　◊ 實現資料的參照完整性，可以加速表和表之間的連線。

　　◊ 在使用分組和排序子句進行資料查詢時，可以顯著減少查詢中分組和排序的時間。

　　索引的缺點：

　　◊ 建立索引和維護索引需要耗費時間，並且隨著資料量的增加所消耗的時間會增加。

　　◊ 索引需要佔用磁碟空間，除了資料表佔資料空間之外，每一個索引還要佔用一定的物理空間。如果有大量的索引，索引檔案可能比資料檔案更快達到最大檔案大小。

　　◊ 當對錶中的資料進行新增、修改和刪除的時候，索引需要動態維護，這樣降低了資料的維護速度。

2 索引的分類

　　SQL Server中索引有兩種：聚集索引和非聚集索引。它們的區別是在物理資料的儲存方式上。

2.1 聚集索引

　　聚集索引基於資料行的鍵值，在表內排序和儲存這些資料行。每個表只能有一個聚集索引，因為資料行本身只能按一個順序儲存。

　　建立聚集索引時需要考慮的幾個因素：

　　◊ 每個表只能有一個聚集索引

　　◊ 表中的物理順序和索引中行的物理順序是相同的，建立任何非聚集索引之前要首先建立聚集索引，這是因為聚集索引改變了表中行的物理順序。

　　◊ 關鍵值的唯一性使用UNIQUE關鍵字或者由內部的唯一識別符號明確維護。

　　◊ 在索引的建立過程中，SQL Server臨時使用當前資料庫的磁碟空間，所以要保證有足夠的空間建立聚集索引。

2.2 非聚集索引

　　非聚集索引具有完全獨立於資料行的結構，使用非聚集索引不用將物理資料頁的資料按列排序。非聚集索引包含索引鍵值和指向表資料儲存位置的行定位器。

　　可以對錶或索引檢視建立多個非聚集索引。設計非聚集索引是為了改善經常使用的、沒有建立聚集索引的查詢的效能。

　　查詢優化器在查詢資料值時，先查詢非聚集索引以找到資料值在表中的位置，然後直接從該位置檢索資料。這使得非聚集索引成為完全匹配查詢的最佳選擇，因為索引中包含所查詢的資料值在表中的精確位置的項。

　　考慮使用非聚集索引的查詢情況：

　　◊ 使用JOIN或GROUP BY子句。應為連線和分組操作中所涉及的列建立多個非聚集索引，為任何外來鍵列建立聚集索引。

　　◊ 包含大量唯一值的欄位。

　　◊ 不返回大型結果集的查詢。建立篩選索引以覆蓋從大型表中返回定義完善的行子集的查詢。

　　◊ 經常包含在查詢的搜尋條件中的列。

3 建立索引

　　SQL Server中建立索引的兩中方法：在SQL Server Management Studio的物件資源管理器中，通過圖形化工具建立或使用T-SQL語句建立。

3.1 使用SQL Server Management Studio物件資源管理器建立

　　◊ 在【物件資源管理器】中，展開【資料庫】找到需要建立索引的資料表節點，展開該節點下的子節點，右擊【索引】節點，在彈出的快捷選單中選擇【新建索引】->【非聚集索引】。

　　◊ 在開啟的【新建索引】介面選擇需要建立索引的列，進而建立索引。

3.2 T-SQL建立索引

　　CREATE INDEX語法：

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name 
    ON <object> ( column [ ASC | DESC ] [ ,...n ] ) 
    [ INCLUDE ( column_name [ ,...n ] ) ]
    [ WHERE <filter_predicate> ]
    [ WITH ( <relational_index_option> [ ,...n ] ) ]
    [ ON { partition_scheme_name ( column_name ) 
         | filegroup_name 
         | default 
         }
    ]
    [ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ]

[ ; ]

<object> ::=
{
    [ database_name. [ schema_name ] . | schema_name. ] 
    table_or_view_name
}

<relational_index_option> ::=
{
    PAD_INDEX = { ON | OFF }
  | FILLFACTOR = fillfactor
  | SORT_IN_TEMPDB = { ON | OFF }
  | IGNORE_DUP_KEY = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF }
  | STATISTICS_INCREMENTAL = { ON | OFF }
  | DROP_EXISTING = { ON | OFF }
  | ONLINE = { ON | OFF }
  | ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS = { ON | OFF }
  | MAXDOP = max_degree_of_parallelism
  | DATA_COMPRESSION = { NONE | ROW | PAGE} 
     [ ON PARTITIONS ( { <partition_number_expression> | <range> } 
     [ , ...n ] ) ]
}

　　建立索引：

IF EXISTS (SELECT name from sys.indexes
             WHERE name = N'IX_Product_CategoryID')
    DROP INDEX IX_Product_CategoryID ON [dbo].[Product];
GO
CREATE NONCLUSTERED INDEX IX_Product_CategoryID 
    ON [dbo].[Product]([CategoryID]);

　　建立過濾索引:

CREATE NONCLUSTERED INDEX IX_Product_CategoryID 
ON [dbo].[Product]([CategoryID])
WHERE [UnitPrice] > 10

4 檢視錶或試圖的索引資訊

　　系統儲存過程sp_helpindex可以返回某個表或試圖的索引資訊。

　　語法：

sp_helpindex [ @objname = ] 'name'

　　檢視錶中包含的全部索引：

EXEC sp_helpindex N'Product'

5 檢視索引的統計資訊

　　索引的統計資訊可以用來分析索引效能，更好地維護索引。

DBCC SHOW_STATISTICS (N'Portal.dbo.Product', N'IX_CategoryID')

6 重新命名索引

　　系統儲存過程sp_rename可以用於更改索引的名稱，其語法格式如下：

sp_rename [ @objname = ] 'object_name' , [ @newname = ] 'new_name' 
    [ , [ @objtype = ] 'object_type' ]

　　示例：

EXEC sp_rename 'dbo.Product.IX_CategoryID' , 'IX_Product_CategoryID' , 'INDEX'

7 修改索引

7.1 ALTER INDEX語法

ALTER INDEX { index_name | ALL }
    ON <object>
    { REBUILD
        [ PARTITION = ALL ]
        [ WITH ( <rebuild_index_option> [ ,...n ] ) ] 
        | [ PARTITION = partition_number 
              [ WITH ( <single_partition_rebuild_index_option> ) [ ,...n ] ]
          ]  
    | DISABLE
    | REORGANIZE 
        [ PARTITION = partition_number ]
        [ WITH ( LOB_COMPACTION = { ON | OFF } ) ]
    | SET ( <set_index_option> [ ,...n ] ) 
    }
[ ; ]

<object> ::=
{
    [ database_name. [ schema_name ] . | schema_name. ]
    table_or_view_name
}

<rebuild_index_option > ::=
{
    PAD_INDEX = { ON | OFF }
  | FILLFACTOR = fillfactor 
  | SORT_IN_TEMPDB = { ON | OFF }
  | IGNORE_DUP_KEY = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF }
  | STATISTICS_INCREMENTAL = { ON | OFF }
  | ONLINE = { ON [ ( <low_priority_lock_wait> ) ] | OFF } 
  | ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS = { ON | OFF }
  | MAXDOP = max_degree_of_parallelism
  | DATA_COMPRESSION = { NONE | ROW | PAGE | COLUMNSTORE | COLUMNSTORE_ARCHIVE } 
     [ ON PARTITIONS ( { <partition_number_expression> | <range> } 
     [ , ...n ] ) ]
}
<range> ::= 
<partition_number_expression> TO <partition_number_expression>

<single_partition_rebuild_index_option> ::=
{
    SORT_IN_TEMPDB = { ON | OFF }
  | MAXDOP = max_degree_of_parallelism
  | DATA_COMPRESSION = { NONE | ROW | PAGE | COLUMNSTORE | COLUMNSTORE_ARCHIVE} }
  | ONLINE = { ON [ ( <low_priority_lock_wait> ) ] | OFF }
}

<set_index_option>::=
{
    ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS = { ON | OFF }
  | IGNORE_DUP_KEY = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF }
}

<low_priority_lock_wait>::=
{
    WAIT_AT_LOW_PRIORITY ( MAX_DURATION = <time> [ MINUTES ] , 
                           ABORT_AFTER_WAIT = { NONE | SELF | BLOCKERS } )
}

7.2 示例

　　禁用索引

ALTER INDEX [IX_CategoryID] ON [dbo].[Product] DISABLE

　　重新啟用索引

ALTER INDEX [IX_CategoryID] ON [dbo].[Product] REBUILD

8 刪除索引

　　DROP INDEX語法：

DROP INDEX
{ <drop_relational_or_xml_or_spatial_index> [ ,...n ] 
| <drop_backward_compatible_index> [ ,...n ]
}

<drop_relational_or_xml_or_spatial_index> ::=
    index_name ON <object> 
    [ WITH ( <drop_clustered_index_option> [ ,...n ] ) ]

<drop_backward_compatible_index> ::=
    [ owner_name. ] table_or_view_name.index_name 

<object> ::=
{
    [ database_name. [ schema_name ] . | schema_name. ] 
    table_or_view_name
}

<drop_clustered_index_option> ::=
{
    MAXDOP = max_degree_of_parallelism
  | ONLINE = { ON | OFF }
  | MOVE TO { partition_scheme_name ( column_name ) 
            | filegroup_name
            | "default" 
            }
  [ FILESTREAM_ON { partition_scheme_name 
            | filestream_filegroup_name 
            | "default" } ]
}

　　示例：

DROP INDEX [dbo].[Product].[IX_Product_CategoryID]
DROP INDEX [IX_Product_CategoryID] ON [dbo].[Product]

相關文章