SQL索引在資料庫優化中占有一個非常大的比例， 一個好的索引的設計，可以讓你的效率提高幾十甚至幾百倍，在這裡将帶你一步步揭開他的神秘面紗。
1.1 什麼是索引？
SQL索引有兩種，聚集索引和非聚集索引，索引主要目的是提高了SQL Server系統的性能，加快資料的查詢速度與減少系統的響應時間
下面舉兩個簡單的例子：
圖書館的例子：一個圖書館那麼多書，怎麼管理呢？建立一個字母開頭的目錄，例如：a開頭的書，在第一排，b開頭的在第二排，這樣在找什麼書就好說了，這個就是一個聚集索引，可是很多人借書找某某作者的，不知道書名怎麼辦？圖書管理者在寫一個目錄，某某作者的書分别在第幾排，第幾排，這就是一個非聚集索引
字典的例子：字典前面的目錄，可以按照拼音和部首去查詢，我們想查詢一個字，隻需要根據拼音或者部首去查詢，就可以快速的定位到這個漢字了，這個就是索引的好處，拼音查詢法就是聚集索引，部首查詢就是一個非聚集索引.
看了上面的例子，下面的一句話大家就很容易了解了：聚集索引存儲記錄是實體上連續存在，而非聚集索引是邏輯上的連續，實體存儲并不連續。就像字段，聚集索引是連續的，a後面肯定是b，非聚集索引就不連續了，就像圖書館的某個作者的書，有可能在第1個貨架上和第10個貨架上。還有一個小知識點就是：聚集索引一個表隻能有一個，而非聚集索引一個表可以存在多個。
1.2 索引的存儲機制
首先，無索引的表，查詢時，是按照順序存續的方法掃描每個記錄來查找符合條件的記錄，這樣效率十分低下,舉個例子，如果我們将字典的漢字随即打亂，沒有前面的按照拼音或者部首查詢，那麼我們想找一個字，按照順序的方式去一頁頁的找，這樣效率有多底，大家可以想象。
聚集索引和非聚集索引的根本差別是表記錄的排列順序和與索引的排列順序是否一緻，其實了解起來非常簡單，還是舉字典的例子：如果按照拼音查詢，那麼都是從a-z的，是具有連續性的，a後面就是b，b後面就是c， 聚集索引就是這樣的，他是和表的實體排列順序是一樣的，例如有id為聚集索引，那麼1後面肯定是2,2後面肯定是3，是以說這樣的搜尋順序的就是聚集索引。非聚集索引就和按照部首查詢是一樣是，可能按照偏房查詢的時候，根據偏旁‘弓’字旁，索引出兩個漢字，張和弘，但是這兩個其實一個在100頁，一個在1000頁，（這裡隻是舉個例子），他們的索引順序和資料庫表的排列順序是不一樣的，這個樣的就是非聚集索引。
原理明白了，那他們是怎麼存儲的呢？在這裡簡單的說一下，聚集索引就是在資料庫被開辟一個實體空間存放他的排列的值，例如1-100，是以當插入資料時，他會重新排列整個整個實體空間，而非聚集索引其實可以看作是一個含有聚集索引的表，他隻僅包含原表中非聚集索引的列和指向實際實體表的指針。他隻記錄一個指針，其實就有點和堆棧差不多的感覺了
1.3 什麼情況下設定索引
| 動作描述 | 使用聚集索引 | 使用非聚集索引 |
| 外鍵列 | 應 | 應 |
| 主鍵列 | 應 | 應 |
| (order by) | 應 | 應 |
| 傳回某範圍内的資料 | 應 | 不應 |
| 小數目的不同值 | 應 | 不應 |
| 大數目的不同值 | 不應 | 應 |
| 頻繁更新的列 | 不應 | 應 |
| 頻繁修改索引列 | 不應 | 應 |
| 一個或極少不同值 | 不應 | 不應 |
建立索引的原則：
1) 定義主鍵的資料列一定要建立索引。
2) 定義有外鍵的資料列一定要建立索引。
3) 對于經常查詢的資料列最好建立索引。
;
WHERE子句中的資料列。
6) 經常出現在關鍵字order by、group by、distinct後面的字段，建立索引。如果建立的是複合索引，索引的字段順序要和這些關鍵字後面的字段順序一緻，否則索引不會被使用。
7) 對于那些查詢中很少涉及的列，重複值比較多的列不要建立索引。
text、image和bit的資料類型的列不要建立索引。
9) 對于經常存取的列避免建立索引
9) 限制表上的索引數目。對一個存在大量更新操作的表，所建索引的數目一般不要超過3個，最多不要超過5個。索引雖說提高了通路速度，但太多索引會影響資料的更新操作。
10) 對複合索引，按照字段在查詢條件中出現的頻度建立索引。在複合索引中，記錄首先按照第一個字段排序。對于在第一個字段上取值相同的記錄，系統再按照第二個字段的取值排序，以此類推。是以隻有複合索引的第一個字段出現在查詢條件中，該索引才可能被使用,是以将應用頻度高的字段，放置在複合索引的前面，會使系統最大可能地使用此索引，發揮索引的作用。
1.4 如何建立索引
1.41 建立索引的文法：
CREATE [UNIQUE][CLUSTERED | NONCLUSTERED] INDEX index_name
ON {table_name | view_name} [WITH [index_property [,....n]]
說明：
建立唯一索引。
建立聚集索引。
建立非聚集索引。
索引屬性。
索引既可以采用聚集索引結構，也可以采用非聚集索引的結構，如果不指明采用的索引結構，則SQL Server系統預設為采用非聚集索引結構。
1.42 删除索引文法：
DROP INDEX table_name.index_name[,table_name.index_name]
table_name: 索引所在的表名稱。
要删除的索引名稱。
1.43 顯示索引資訊：
sp_helpindex 檢視指定表的索引資訊。
執行代碼如下：
Exec sp_helpindex book1;
1.5 索引使用次數、索引效率、占用CPU檢測、索引缺失
當我們明白了什麼是索引，什麼時間建立索引以後，我們就會想，我們建立的索引到底效率執行的怎麼樣？好不好？我們建立的對不對？
動态管理視圖和函數傳回特定于實作的内部狀态資料。推出SQL Server 2005時，微軟介紹了許多被稱為dmvs的系統視圖，讓您可以探測SQL Server 的健康狀況，診斷問題，或檢視SQL Server執行個體的運作資訊。統計資料是在SQL Server運作的時候開始收集的，并且在SQL Server每次啟動的時候，統計資料将會被重置。當你删除或者重新建立其元件時，某些dmv的統計資料也可以被重置，例如存儲過程和表，而其它的dmv資訊在運作dbcc指令時也可以被重置。
dmv時，你需要緊記SQL Server收集這些資訊有多長時間了，以确定這些從dmv傳回的資料到底有多少可用性。如果SQL Server隻運作了很短的一段時間，你可能不想去使用一些dmv統計資料，因為他們并不是一個能夠代表SQL Server執行個體可能遇到的真實工作負載的樣本。另一方面，SQL Server隻能維持一定量的資訊，有些資訊在進行SQL Server性能管理活動的時候可能丢失，是以如果SQL Server已經運作了相當長的一段時間，一些統計資料就有可能已被覆寫。
dmv，當你檢視從SQL Server 2005的dmvs傳回的相關資料時，請務必将以上的觀點裝在腦海中。隻有當你确信從dmvs獲得的資訊是準确和完整的，你才能變更資料庫或者應用程式代碼。
dmv到底能帶給我們那些好的功能呢？
：索引使用次數
我們下看一下下面兩種查詢方式傳回的結果（這兩種查詢的查詢用途一緻）
----
declare @dbid int
select @dbid = db_id()
select objectname=object_name(s.object_id), s.object_id, indexname=i.name, i.index_id
, user_seeks, user_scans, user_lookups, user_updates
from sys.dm_db_index_usage_stats s,
sys.indexes i
where database_id = @dbid and objectproperty(s.object_id,'IsUserTable') = 1
and i.object_id = s.object_id
and i.index_id = s.index_id
order by (user_seeks + user_scans + user_lookups + user_updates) asc
傳回查詢結果
②：使用多的索引排在前面
SELECT objects.name ,
databases.name ,
indexes.name ,
user_seeks ,
user_scans ,
user_lookups ,
partition_stats.row_count
FROM sys.dm_db_index_usage_stats stats
LEFT JOIN sys.objects objects ON stats.object_id = objects.object_id
LEFT JOIN sys.databases databases ON databases.database_id = stats.database_id
LEFT JOIN sys.indexes indexes ON indexes.index_id = stats.index_id
AND stats.object_id = indexes.object_id
LEFT JOIN sys.dm_db_partition_stats partition_stats ON stats.object_id = partition_stats.object_id
AND indexes.index_id = partition_stats.index_id
WHERE 1 = 1
--AND databases.database_id = 7
AND objects.name IS NOT NULL
AND indexes.name IS NOT NULL
AND user_scans>0
ORDER BY user_scans DESC ,
stats.object_id ,
indexes.index_id
傳回查詢結果
通過使用者查詢執行的搜尋次數。
個人了解： 此統計索引搜尋的次數
通過使用者查詢執行的掃描次數。
個人了解：此統計表掃描的次數，無索引配合
user_lookups: 通過使用者查詢執行的查找次數。
個人了解：使用者通過索引查找，在使用RID或聚集索引查找資料的次數，對于堆表或聚集表資料而言和索引配合使用次數
user_updates: 通過使用者查詢執行的更新次數。
個人了解：索引或表的更新次數
我們可以清晰的看到，那些索引用的多，那些索引沒用過，大家可以根據查詢出來的東西去分析自己的資料索引和表
：索引提高了多少性能
SQL可以傳回連接配接缺失索引動态管理視圖，發現最有用的索引和建立索引的方法：
SELECT
avg_user_impact AS average_improvement_percentage,
avg_total_user_cost AS average_cost_of_query_without_missing_index,
'CREATE INDEX ix_' + [statement] +
ISNULL(equality_columns, '_') +
ISNULL(inequality_columns, '_') + ' ON ' + [statement] +
' (' + ISNULL(equality_columns, ' ') +
ISNULL(inequality_columns, ' ') + ')' +
ISNULL(' INCLUDE (' + included_columns + ')', '')
AS create_missing_index_command
FROM sys.dm_db_missing_index_details a INNER JOIN
sys.dm_db_missing_index_groups b ON a.index_handle = b.index_handle
INNER JOIN sys.dm_db_missing_index_group_stats c ON
b.index_group_handle = c.group_handle
WHERE avg_user_impact > = 40
傳回結果
40%或更高的索引。你可以清晰的看到每個索引提高的性能和效率了
：最占用CPU、執行時間最長指令
sql語句占用你的cpu最高
SELECT TOP 100 execution_count,
total_logical_reads /execution_count AS [Avg Logical Reads],
total_elapsed_time /execution_count AS [Avg Elapsed Time],
db_name(st.dbid) as [database name],
object_name(st.dbid) as [object name],
object_name(st.objectid) as [object name 1],
SUBSTRING(st.text, (qs.statement_start_offset / 2) + 1,
((CASE statement_end_offset WHEN - 1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)
/ 2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
WHERE execution_count > 100
ORDER BY 1 DESC;
傳回結果：
執行時間最長的指令
SELECT TOP 10 COALESCE(DB_NAME(st.dbid),
DB_NAME(CAST(pa.value as int))+'*',
'Resource') AS DBNAME,
SUBSTRING(text,
-- starting value for substring
CASE WHEN statement_start_offset = 0
OR statement_start_offset IS NULL
THEN 1
ELSE statement_start_offset/2 + 1 END,
-- ending value for substring
CASE WHEN statement_end_offset = 0
OR statement_end_offset = -1
OR statement_end_offset IS NULL
THEN LEN(text)
ELSE statement_end_offset/2 END -
CASE WHEN statement_start_offset = 0
OR statement_start_offset IS NULL
THEN 1
ELSE statement_start_offset/2 END + 1
) AS TSQL,
total_logical_reads/execution_count AS AVG_LOGICAL_READS
FROM sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(sql_handle) st
OUTER APPLY sys.dm_exec_plan_attributes(plan_handle) pa
WHERE attribute = 'dbid'
ORDER BY AVG_LOGICAL_READS DESC ;
語句，優化去吧。還等什麼呢？
：缺失索引
缺失索引就是幫你查找你的資料庫缺少什麼索引，告訴你那些字段需要加上索引，這樣你就可以根據提示添加你資料庫缺少的索引了
SELECT TOP 10
[Total Cost] = ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans),0)
, avg_user_impact
, TableName = statement
, [EqualityUsage] = equality_columns
, [InequalityUsage] = inequality_columns
, [Include Cloumns] = included_columns
FROM sys.dm_db_missing_index_groups g
INNER JOIN sys.dm_db_missing_index_group_stats s
ON s.group_handle = g.index_group_handle
INNER JOIN sys.dm_db_missing_index_details d
ON d.index_handle = g.index_handle
ORDER BY [Total Cost] DESC;
查詢結果如下：
1.6 适當建立索引覆寫
Sales表(SelesID,SalesDate,SalesPersonID,ProductID,Qty)的外鍵列(ProductID)上建立了一個索引，假設ProductID列是一個高選中性列，那麼任何在where子句中使用索引列(ProductID)的select查詢都會更快，如果在外鍵上沒有建立索引，将會發生全部掃描，但還有辦法可以進一步提升查詢性能。
Sales表有10,000行記錄，下面的SQL語句選中400行(總行數的4%)：
SELECT SalesDate, SalesPersonID FROM Sales WHERE ProductID = 112
SQL語句在SQL執行引擎中是如何執行的：
1)Sales表在ProductID列上有一個非聚集索引，是以它查找非聚集索引樹找出ProductID=112的記錄;
2)包含ProductID = 112記錄的索引頁也包括所有的聚集索引鍵(所有的主鍵鍵值，即SalesID);
3)針對每一個主鍵(這裡是400)，SQL Server引擎查找聚集索引樹找出真實的行在對應頁面中的位置;
SQL Server引擎從對應的行查找SalesDate和SalesPersonID列的值。
ProductID = 112的每個主鍵記錄(這裡是400)，SQL Server引擎要搜尋400次聚集索引樹以檢索查詢中指定的其它列(SalesDate，SalesPersonID)。
(SalesDate,，SalesPersonID)的值，SQL Server引擎可能不會執行上面的第3和4步，直接從非聚集索引樹查找ProductID列速度還會快一些，直接從索引頁讀取這三列的數值。
“覆寫索引”，在表列上建立覆寫索引時，需要指定哪些額外的列值需要和聚集索引鍵值(主鍵)一起存儲在索引頁中。下面是在Sales 表ProductID列上建立覆寫索引的例子：
CREATE INDEX NCLIX_Sales_ProductID--Index name
ON dbo.Sales(ProductID)--Column on which index is to be created
INCLUDE(SalesDate, SalesPersonID)--Additional column values to include
select查詢中常使用到的列上建立覆寫索引，但覆寫索引中包括過多的列也不行，因為覆寫索引列的值是存儲在記憶體中的，這樣會消耗過多記憶體，引發性能下降。
1.7 索引碎片
在資料庫性能優化一：資料庫自身優化一文中已經講到了這個問題，再次就不做過多的重複位址：javascript:void(0)
1.8 索引實戰（摘抄）
之是以這章摘抄，是因為下面這個文章已經寫的太好了，估計我寫出來也無法比這個好了，是以就摘抄了
SQL時往往會陷入一個誤區，即太關注于所得的結果是否正确，而忽略了不同的實作方法之間可能存在的性能差異，這種性能差異在大型的或是複雜的資料庫環境中（如聯機事務處理OLTP或決策支援系統DSS）中表現得尤為明顯。
SQL往往來自于不恰當的索引設計、不充份的連接配接條件和不可優化的where子句。
在對它們進行适當的優化後，其運作速度有了明顯地提高！
下面我将從這三個方面分别進行總結：
SQL運作時間均經過測試，不超過１秒的均表示為（< 1秒）。----
: 主機：HP LH II---- 主頻：330MHZ---- 記憶體：128兆----
Operserver5.0.4----
Sybase11.0.3
----
record有620000行，試看在不同的索引下，下面幾個 SQL的運作情況：
在date上建有一非個群集索引
秒)
秒)
秒)
分析：----
上有大量的重複值，在非群集索引下，資料在實體上随機存放在資料頁上，在範圍查找時，必須執行一次表掃描才能找到這一範圍内的全部行。
在date上的一個群集索引
（14秒）
（28秒）
（14秒）
分析：---- 在群集索引下，資料在實體上按順序在資料頁上，重複值也排列在一起，因而在範圍查找時，可以先找到這個範圍的起末點，且隻在這個範圍内掃描資料頁，避免了大範圍掃描，提高了查詢速度。
在place，date，amount上的組合索引
（26秒）
（27秒）
（< 1秒）
分析：---- 這是一個不很合理的組合索引，因為它的前導列是place，第一和第二條SQL沒有引用place，是以也沒有利用上索引；第三個SQL使用了place，且引用的所有列都包含在組合索引中，形成了索引覆寫，是以它的速度是非常快的。
在date，place，amount上的組合索引
秒)
（11秒）
（< 1秒）
分析：---- 這是一個合理的組合索引。它将date作為前導列，使每個SQL都可以利用索引，并且在第一和第三個SQL中形成了索引覆寫，因而性能達到了最優。
總結：----
預設情況下建立的索引是非群集索引，但有時它并不是最佳的；合理的索引設計要建立在對各種查詢的分析和預測上。
一般來說：
.有大量重複值、且經常有範圍查詢（between, >,< ，>=,< =）和order by、group by發生的列，可考慮建立群集索引；
.經常同時存取多列，且每列都含有重複值可考慮建立組合索引；
.組合索引要盡量使關鍵查詢形成索引覆寫，其前導列一定是使用最頻繁的列。
二、不充份的連接配接條件：
card有7896行，在card_no上有一個非聚集索引，表account有191122行，在account_no上有一個非聚集索引，試看在不同的表連接配接條件下，兩個SQL的執行情況：
（20秒）
（< 1秒）
分析：---- 在第一個連接配接條件下，最佳查詢方案是将account作外層表，card作内層表，利用card上的索引，其I/O次數可由以下公式估算為：
account上的22541頁+（外層表account的191122行*内層表card上對應外層表第一行所要查找的3頁）=595907次I/O
card作外層表，account作内層表，利用account上的索引，其I/O次數可由以下公式估算為：外層表card上的1944頁+（外層表card的7896行*内層表account上對應外層表每一行所要查找的4頁）= 33528次I/O
可見，隻有充份的連接配接條件，真正的最佳方案才會被執行。
總結：
多表操作在被實際執行前，查詢優化器會根據連接配接條件，列出幾組可能的連接配接方案并從中找出系統開銷最小的最佳方案。連接配接條件要充份考慮帶有索引的表、行數多的表；内外表的選擇可由公式：外層表中的比對行數*内層表中每一次查找的次數确定，乘積最小為最佳方案。
檢視執行方案的方法-- 用set showplanon，打開showplan選項，就可以看到連接配接順序、使用何種索引的資訊；想看更詳細的資訊，需用sa角色執行dbcc(3604,310,302)。
where子句
例：下列SQL條件語句中的列都建有恰當的索引，但執行速度卻非常慢：
秒)
（11秒）
（10秒）
分析：
子句中對列的任何操作結果都是在SQL運作時逐列計算得到的，是以它不得不進行表搜尋，而沒有使用該列上面的索引；
SQL優化器優化，使用索引，避免表搜尋，是以将SQL重寫成下面這樣：
（< 1秒）
（< 1秒）
（< 1秒）
SQL明顯快起來！
例：表stuff有200000行，id_no上有非群集索引，請看下面這個SQL：
（23秒）
---- where條件中的'in'在邏輯上相當于'or'，是以文法分析器會将in ('0','1')轉化為id_no ='0' or id_no='1'來執行。
or子句分别查找，再将結果相加，這樣可以利用id_no上的索引；
showplan）,它卻采用了"OR政策"，即先取出滿足每個or子句的行，存入臨時資料庫的工作表中，再建立唯一索引以去掉重複行，最後從這個臨時表中計算結果。是以，實際過程沒有利用id_no上索引，并且完成時間還要受tempdb資料庫性能的影響。
stuff有620000行時，執行時間竟達到220秒！還不如将or子句分開：
select count(*) from stuff where id_no='0'select count(*) from stuff where id_no='1'
3秒，在620000行下，時間也隻有4秒。
或者，用更好的方法，寫一個簡單的存儲過程：
create proc count_stuff asdeclare @a intdeclare @b intdeclare @c intdeclare @d char(10)beginselect @a=count(*) from stuff where id_no='0'select @b=count(*) from stuff where id_no='1'endselect @c=@a+@bselect @d=convert(char(10),@c)print @d
直接算出結果，執行時間同上面一樣快！
總結：---- 可見，所謂優化即where子句利用了索引，不可優化即發生了表掃描或額外開銷。
任何對列的操作都将導緻表掃描，它包括資料庫函數、計算表達式等等，查詢時要盡可能将操作移至等号右邊。
、or子句常會使用工作表，使索引失效；如果不産生大量重複值，可以考慮把子句拆開；拆開的子句中應該包含索引。