程式師世界 >> 數據庫知識 >> SqlServer數據庫 >> 關於SqlServer >> 數據庫如何優化索引

數據庫如何優化索引

編輯：關於SqlServer

眾所周知，索引可以提升查詢的性能，原來需要10分鐘甚至一個小時的查詢，通過簡簡單單創建一個索引之後，可以優化到秒級別甚至毫秒級別。因此，DBA或者開發人員創建了一個又一個索引，創建之初性能可能提升明確，當索引越多數據量越大的時候就可能會發生性能反而下降了。這又是為什麼呢？大家忽略了索引是需要維護的，索引是需要額外的性能開銷的，我們創建了太多的索引，有些我們根本用不到，而我們需要的可能又沒正確創建起來。那麼我們怎麼知道應該創建哪些索引，以及哪些索引我們是不需要的呢，如何更有效地使用索引？請跟隨筆者的步伐，讓我們一起來學習……

【正文】

一、如何分析現有索引的使用情況

1.1 分析的方法和工具

要分析現在索引的使用情況，需要用到SQL Server提供的系統動態管理視圖，語句如下：
SELECT
DB_NAME() AS DBNAME,
object_name(a.object_id) AS table_name,
COALESCE(name,'object with no clustered index') AS index_name,
type_desc AS index_type,
user_seeks,
user_scans,
user_lookups,
user_updates
FROM sys.dm_db_index_usage_stats a INNER JOIN sys.indexes b
ON a.index_id = b.index_id AND a.object_id = b.object_id
WHERE database_id = DB_ID('DATABASENAME') AND a.object_id > 1000
其中DATABASENAME表示實際的數據庫名稱。
此語句的作用是查詢自從上次SQL Server服務重啟之後到當前時間為止特定數據庫（DATABASENAME指定）中所有索引的使用情況。

查詢結果解讀如下：
1、 DBNAME
數據庫的名稱
2、 Table_Name
表的名稱
3、 Index_Name
索引的名稱
4、 Index_Type
索引的類型：包括聚集索引、非聚集索引、堆表。
5、 User_Seeks
用戶執行的T-SQL語句中用到索引查找的次數
6、 User_Scans
用戶執行的T-SQL語句中用到全表掃描的次數
7、 User _Lookups
用戶執行的T-SQL語句中用到的書簽查找的次數
8、 User_Updates
用戶執行的T-SQL語句中對於索引的更新的次數

1.2 如何對結果進行分析

分析索引的使用效率是一個綜合的事情，一般來講，我們可以這麼來分析，可以看幾個數字的對比：
1、比如說，user_updates或者User_scans很大，而發現user_seeks很少或者就是0，那就說明該索引一直在更新或者一直被全表掃描，也就是說從來不被使用，僅僅創建和修改，沒有為查詢提供任何幫助，就可以考慮刪除了。
2、如果User_seeks或者user_lookups很大，而發現user_updates或者User_scans比較小，說明索引經常被使用，而且帶來的維護成本較低。
3、如果User_seeks或者user_lookups很大，而user_updates或者User_scans也比較大，那表明索引經常被使用，但是其帶來的維護成本比較高。這個時候就需要我們去平衡索引對於應用的重要性以及性能成本這兩者之間孰重孰輕了。

二、如何分析缺少哪些索引

2.1 分析的方法和工具

要分析現在索引的使用情況，需要用到SQL Server提供的系統動態管理視圖，語句如下：
SELECT b.name ,
        a.statement ,
        a.equality_columns ,
        inequality_columns ,
        included_columns
FROM    sys.[dm_db_missing_index_details] a
        JOIN sys.databases b ON a.database_id = b.database_id
此語句的作用是查詢自從上次SQL Server服務重啟之後到當前時間為止全部數據庫中可能缺少哪些索引

查詢結果解讀如下：
1、 NAME
數據庫的名稱
2、 Statement
缺失索引的表的名稱
3、 Equality_Columns
經常用於“等值”比較的列，比如經常用於 EmployeeID=value
4、 InEquality_Columns
經常用於“不等值”比較的列，比如經常用於 EmployeeID>value或EmployeeID<>value
5、 Include_Columns
建議在索引中涵蓋或者包含的列

2.2 如何對結果進行分析

分析索引的缺失比分析索引的使用情況相對來說會簡單一點，但是在判斷需要創建哪些索引時也是有難度的。
1、從結果來看，已經很明確告訴我們哪個庫的哪個表的哪一列缺少索引，並且也提醒我們需要包含哪些列。一般情況下，我只需要按照這份建議，把相關的索引都創建了即可。
2、但我們不能忽略一點，創建過多的索引會導致開銷的增大。所以當我們按照以上結果創建了索引之後，需要定期再分析一下索引的使用情況。也是說，分析索引的使用情況和分析缺少哪些索引是一個相輔相成的過程，是一個持續的過程。

三、索引創建的建議

前面我們介紹了如何分析現有索引的使用情況，也介紹了如何分析缺少哪些索引。毫無疑問，接下來我們就需要創建一些必要的索引，以及刪除一起價值不高的索引。那一般情況下，我們在創建索引的時候有哪些需要方面重點考慮的呢？

1、在條件列上創建索引在條件列上創建索引，當返回的記錄是高選擇性的時候，往往能用到索引。

2、在搜索列上創建索引（索引涵蓋查詢）在搜索列上創建索引，使用組合索引或者索引篩選，能夠用到索引，索引篩選或者索引掃描，我們稱這個過程為索引涵蓋查詢。

3、使用索引包含索引包含類似於組合索引，區別在於索引包含的包含列只存在於索引的葉級頁中，而不會出現在非葉級頁，可以減少索引所占的空間。
索引包含的定義如下：
CREATE INDEX NI_ProductID ON dbo.Orders(ProductId)
INCLUDE (Quantity)

4、使用索引篩選某些表的某些列包含了很多NULL值，而我們常常查詢那些非NULL的值;我們通常只在近期的數據中查詢記錄;我們只在某個范圍內查詢記錄……
上面列舉的這幾種情況下，我們是要在符合某個條件的范圍內去查詢記錄，我們並不需要從全部的數據中查詢記錄。類似於這種情況，我們在為這些列創建索引的時候可以進行篩選，簡稱“索引篩選”。
索引篩選的定義如下：
CREATE INDEX NI_ProductID ON dbo.Orders(ProductId)
WHERE ProductId>2000
這樣一來，索引頁中就不會包含ProductID小於或等於2000的數據。