SQL-索引

理解“聚集索引”和“非聚集索引”

① 聚集索引（clustered   index，也稱聚類索引、簇集索引）：把內容本身就是一種按照一定規則排列的目錄稱為“聚集索引”

       我們的漢語字典的正文本身就是一個聚集索引，比如，我們要查“安”字，就會很自然地翻開字典的前幾頁，因為“安”的拼音是“an”，而按照拼音排序漢字的字典是以英文字母“a”開頭并以“z”結尾的，那么“安”字就自然地排在字典的前部，如果您翻完了所有以“a”開頭的部分仍然找不到這個字，那么就說明您的字典中沒有這個字；同樣的，如果查“張”字，那您也會將您的字典翻到最后部分，因為“張”的拼音是“zhang”，也就是說，字典的正文部分本身就是一個目錄，您不需要再去查其他目錄來找到您需要找的內容，

② 非聚集索引（nonclustered   index，也稱非聚類索引、非簇集索引）：目錄純粹是目錄，正文純粹是正文的排序方式稱為“非聚集索引”

       如果您認識某個字，您可以快速地從自動中查到這個字，但您也可能會遇到您不認識的字，不知道它的發音，這時候，您就不能按照剛才的方法找到您要查的字，而需要去根據“偏旁部首”查到您要找的字，然后根據這個字后的頁碼直接翻到某頁來找到您要找的字，但您結合“部首目錄”和“檢字表”而查到的字的排序并不是真正的正文的排序方法，比如您查“張”字，我們可以看到在查部首之后的檢字表中“張”的頁碼是672頁，檢字表中“張”的上面是“馳”字，但頁碼卻是63頁，“張”的下面是“弩”字，頁面是390頁，很顯然，這些字并不是真正的分別位于“張”字的上下方，現在您看到的連續的“馳、張、弩”三字實際上就是他們在非聚集索引中的排序，是字典正文中的字在非聚集索引中的映射，我們可以通過這種方式來找到您所需要的字，但它需要兩個程序，先找到目錄中的結果，然后再翻到您所需要的頁碼，

由以上解釋，就很容易理解：每個表只能有一個聚集索引，因為目錄只能按照一種方法進行排序，

索引使用的誤區

① 主鍵就是聚集索引

        通常，我們會在每個表中都建立一個 ID 列，以區分每條資料，并且這個 ID 列是自動增大的，步長一般為 1 ，這種 ID 是自動生成，我們并不知道每條記錄的ID號，所以我們很難在實踐中用 ID 號來進行查詢（ 若要查詢需要提前知道要查詢記錄的 ID 號，這就有點本末倒置了 ），

② 只要建立索引就能顯著提高查詢速度

        并非是在任何欄位上簡單地建立索引就能提高查詢速度，因此建立“適當”的聚合索引對于我們提高查詢速度是非常重要的

③ 把所有需要提高查詢速度的欄位都加進聚集索引，以提高查詢速度

        僅用聚集索引的起始列作為查詢條件和同時用到復合聚集索引的全部列的查詢速度是幾乎一樣的，甚至比用上全部的復合索引列還要略快（在查詢結果集數目一樣的情況下）；而如果僅用復合聚集索引的非起始列作為查詢條件的話，這個索引是不起任何作用的，

索引使用經驗總結

① 用聚合索引比用不是聚合索引的主鍵速度快

② 用聚合索引比用一般的主鍵作order   by時速度快，特別是在小資料量情況下

        如果資料量很小的話，用聚集索引作為排序列要比使用非聚集索引速度快得明顯的多；而資料量如果很大的話，如10萬以上，則二者的速度差別不明顯，

③ 使用聚合索引內的時間段，搜索時間會按資料占整個資料表的百分比成比例減少，而無論聚合索引使用了多少個

④ 日期列不會因為有分秒的輸入而減慢查詢速度

⑤ 建立一個“適當”的索引體系，特別是對聚合索引的創建

        引有助于提高檢索性能，但過多或不當的索引也會導致系統低效，因為用戶在表中每加進一個索引，資料庫就要做更多的作業，過多的索引甚至會導致索引碎片，

不良的SQL

   不良的 SQL 往往來自于 不恰當的索引設計、不充份的連接條件 和 不可優化的 where 子句 ，在對它們進行適當的優化后，其運行速度有了明顯地提高！

① 不恰當的索引設計

預設情況下建立的索引是非聚集索引，但有時它并不是最佳的；合理的索引設計要建立在對各種查詢的分析和預測上，一般來說：

? 有大量重復值、且經常有范圍查詢（ between , > , < , >= , <= ）和 order by 、group by 發生的列，可考慮建立聚集索引；

? 經常同時存取多列，且每列都含有重復值可考慮建立組合索引； 

? 組合索引要盡量使關鍵查詢形成索引覆寫，其前導列一定是使用最頻繁的列，

② 不充份的連接條件

eg：表 card 有 7896行，在 card_no 上有一個非聚集索引，表 account 有 191122行，在 account_no 上有一個非聚集索引，試看在不同的表連接條件下，兩個 SQL 的執行情況：

1 select sum(a.amount) from account a,card b where a.card_no = b.card_no（20秒）
2 -- 將SQL改為：
3 select sum(a.amount) from account a,card b where a.card_no = b.card_no and a.account_no=b.account_no（< 1秒）

4 -- 在第一個連接條件下，最佳查詢方案是將 account 作外層表，card 作內層表，利用 card 上的索引，其 I/O 次數可由以下公式估算為：
5 ---- 外層表 account 上的 22541頁 +（ 外層表 account 的 191122行 * 內層表 card 上對應外層表第一行所要查找的3頁 ）= 595907 次 I/O


6 -- 在第二個連接條件下，最佳查詢方案是將 card 作外層表，account 作內層表，利用 account 上的索引，其 I/O 次數可由以下公式估算為：
7 ---- 外層表 card 上的 1944頁 +（ 外層表 card 的 7896行 * 內層表 account 上對應外層表每一行所要查找的4頁 ）= 33528 次 I/O

可見，只有充份的連接條件，真正的最佳方案才會被執行，

        多表操作在被實際執行前，查詢優化器會根據連接條件，列出幾組可能的連接方案并從中找出系統開銷最小的最佳方案，連接條件要充份考慮帶有索引的表、行數多的表，

       內外表的選擇可由公式：外層表中的匹配行數 * 內層表中每一次查找的次數確定，乘積最小為最佳方案，

③ 不可優化的 where 子句

        # where子句中對列的任何操作結果都是在SQL運行時逐列計算得到的，因此它不得不進行表搜索，而沒有使用該列上面的索引；如果這些結果在查詢編譯時就能得到，那么就可以被SQL優化器優化，使用索引，避免表搜索，

eg：

 1 select * from record where substring ( card_no , 1 , 4) ='5378'(13秒)
 2 --改為下面的SQL
 3 select * from record where card_no like '5378%'（ <1秒 )
 4 
 5 select * from record where amount/30 < 1000 ( 11秒 )
 6 --改為下面的SQL
 7 select * from record where amount < 1000*30（<1秒）
 8 
 9 select * from record where convert ( char(10) , date , 112 ) = '19991201'（10秒）
10 --改為下面的SQL
11 select * from record where date= '1999/12/01'（< 1秒）

# 所謂優化即where子句利用了索引，不可優化即發生了表掃描或額外開銷

eg：表 stuff 有 200000 行，id_no 上有非聚集索引，請看下面這個 SQL ：

select count(*) from stuff where id_no in('0','1') （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秒，

? 用更好的方法，寫一個簡單的存盤程序：

 1 create proc count_stuff as
 2 declare @a int
 3 declare @b int
 4 declare @c int
 5 declare @d char(10)
 6 begin
 7 select @a=count(*) from stuff where id_no='0'
 8 select @b=count(*) from stuff where id_no='1'
 9 end
10 select @c=@a+@b
11 select @d=convert(char(10),@c)
12 print @d

直接算出結果，執行時間同上面一樣快！

由以上三點，可以總結以下結論

 

1.任何對列的操作都將導致表掃描，它包括資料庫函式、計算運算式等等，查詢時要盡可能將操作移至等號右邊，

 

2. in 、or 子句常會使用作業表，使索引失效；如果不產生大量重復值，可以考慮把子句拆開；拆開的子句中應該包含索引，

 

3.要善于使用存盤程序，它使 SQL 變得更加靈活和高效，

 

---- 從以上這些例子可以看出，SQL 優化的實質就是在結果正確的前提下，用優化器可以識別的陳述句，充份利用索引，減少表掃描的 I/O 次數，盡量避免表搜索的發生，

 

FILLFACTOR （ 填充因子 ）

要理解填充因子的作用，首先需要理解什么是頁拆分

頁拆分

        在創建聚集索引時，表中的資料按照索引列中的值的順序存盤在資料庫的資料頁中，在表中插入新的資料行或更改索引列中的值時，SQL Server 必須重新組織表中的資料存盤，以便為新行騰出空間，保持資料的有序存盤，這同樣適用于非聚集索引，添加或更改資料時，SQL Server 不得不重新組織非聚集索引頁中的資料存盤，向一個已滿的索引頁添加某個新行時，SQL Server 把大約一半的行移到新頁中以便為新行騰出空間，這種重組稱為頁拆分，

        頁拆分會降低性能并使表中的資料存盤產生碎片，

填充因子的作用理解

       當創建一個新索引，或重建一個存在的索引時，你可以指定一個填充因子，它是在索引創建時索引里的資料頁被填充的數量，

       填充因子設定為 100 意味著每個索引頁 100％ 填滿，50％ 意味著每個索引頁 50％ 填滿， 如果你創建一個填充因子為 100 的聚集索引（ 在一個非單調遞增的列上 ），那意味著每當一個記錄被插入（ 或修改 ）時，頁拆分都會發生，因為在現存的頁上沒有這些資料的空間，

       eg：假定你剛剛用預設的填充因子新創建了一個索引，當sqlserver創建它時，它把索引放在相鄰的物理頁面上，因為資料能夠順序的讀所以這樣會有最優的i/o訪問，但當表隨著、、增加和改變時，發生了頁拆分，當頁拆分發生時，sqlserver必須在磁盤的某處分配一個新的頁，這些新的頁和最初的物理頁不是連續的，因此，訪問使用的是隨機的i/o，而不是有順序的i/o，這樣訪問索引頁會變得更慢，

填充因子如何設定比較好

原則：

低更改的表（讀寫比率為100：1）：100％的填充因子

高更改的表（寫超過讀）50-70％的填充因子

讀寫各一半的：80-90％的填充因子 

標籤：SQL Server

上一篇：MySQL45講之優化器選錯索引

下一篇：SQL-索引