軟體開發人員真的瞭解SQL索引嗎(索引原理)

      上篇文章我粗略的總結了些SQL叢集索引與非叢集索引的區別，但看起來好像不太清晰，這篇我通過索引原理來再一次分析下。
     

      說明：下面所指的B樹是指平衡樹。
      索引是為檢索而存在的,就是說索引並不是一個表必須的。表索引由多個頁面組成，這些頁面一起組成了一個樹形結構,即我們通常說的B樹， 首先來看下錶索引的組成部分：
  
      根極節點,root,它指向另外兩個頁，把一個表的記錄從邏輯上分成非葉級節點Non-Leaf Level(枝)，它指向了更加小的葉級節點Leaf Level(葉)。 根節點、非葉級節點和葉級節點都位於索引頁中，統稱為索引分葉節點，屬於索引頁的範籌。這些“枝”、“葉”最終指向資料頁Page。根級節點和葉級節點之間的葉又叫資料中間頁。根節點對應了sysindexes表的Root欄位，記載了非葉級節點的物理位置（即指標）；非葉級節點位於根節點和分葉節點之間，記載了指向葉級節點的指標；而葉級節點則最終指向資料頁，這就是最後的B樹。   
  
      資料庫是怎樣訪問表資料的：
  
     第一：沒有建立任何索引的表。
     這種表我們稱為堆表，因為所有的資料頁都是無序的，雜亂無章的，在查詢資料時，需要一條一條記錄查詢，有時第一條記錄就能找到，最壞的情況是在最後一條記錄中尋找到,但是千萬不要認為SQL此時尋找到資料後會當成結果立即返回，SQL即使尋找到了記錄，也會將所有資料遍曆一次，這能從最終的執行計畫中得知，就是平時說的表掃描，對於沒有索引的表也能查詢，就是效率會特別低，如果資料量稍大的話。
  
     問題：SQL是如何得知表沒有索引呢？
    SQL在接到查詢請求的時候，會分析sysindexes表中索引標誌符(INDID: Index ID)的欄位的值，如果該值為0，表示這是一張資料表而不是索引表，SQL就會使用sysindexes表的另一個欄位FirstIAM值中找到該表的IAM 頁鏈也就是所有資料頁集合。至於什麼是IAM,大家可以網上搜尋下。
  
     第二：訪問建立有非叢集索引的表。
     非叢集索引可以建多個,形成B樹結構，葉級節點不包含資料頁，只包含索引行。如果表中只有非叢集索引，則每個索引行包含了非叢集索引索引值以及行定位器（ROW ID,RID），他們指向具有該索引值的資料行。RID由檔案ID、頁編號和在頁中行的編號組成。當 INDID的值在2-250之間時，說明表中存在非叢集索引頁。SQL調用ROOT欄位的值指向非叢集索引B樹的ROOT，尋找與被查詢最相近的值，根據這個值找到在非葉級節點中的頁號，在葉級節點相應的頁面中找到該值的RID，最後根據這個RID在Heap中定位所在的頁和行並返回到查詢端。
   上篇文章的cityid上建立了非叢集索引，執行Select * From student Where cityid=’0101’時，查詢過程是：
   1:在sysindexes表查詢INDID值為2,說明有非叢集索引；
   2:從根出發，在非葉級節點中定位最接近0101的值(枝節點)，查到其位於葉級頁面的第n頁；
   3:在葉級頁面的第n頁下搜尋0101的RID，其RID顯示為N∶i∶j，表示cityid欄位中名為0101的記錄位於堆的第i頁的第j行，N代表檔案的ID值。
   4:在堆的第 i頁第j行將該記錄返回給用戶端。

   可做參考：
                                 
  

    第三：訪問建立有叢集索引的表。
    叢集索引中，資料所在的資料頁是葉級，索引資料所在的索引頁是非葉級。原理和上述非叢集索引的查詢差不多，由於記錄是按叢集索引索引值進行排序，即叢集索引的索引索引值也就是具體的資料頁。這種情況比起非叢集索引要簡單很多,因為比非叢集索引少了一層節點查詢。
   上篇文章的username欄位上建立了叢集索引，此時執行Select* From student Where username=’1’時，查詢過程是：
   1:在sysindexes表查詢INDID值為1，說明表中建立了聚集索；
   2:從根出發，在非葉級節點中定位最接近1的值(枝節點)，再查到其位於葉級頁面的第n頁；
   3:在葉級頁面第n頁下搜尋值為1的條目，而這一條目就是資料記錄本身；
   4:將該記錄返回用戶端。
   可做參考：

                           

    第四：怎樣訪問既有叢集索引、又有非叢集索引的資料表：
    username欄位上建立了叢集索引，cityid上建立了非叢集索引，當執行Select * From student Where cityid=’0101’時，查詢過程是：
    1:在sysindexes表查詢INDID值為2,說明有非叢集索引；
    2:從根出發，在cityid的非叢集索引的非葉級節點中定位最接近0101的條目；
    3:從上麵條目下的葉級頁面中查到0101的邏輯位置，是叢集索引的指標；
    4:根據指標所指示位置，進入位於username的叢集索引中的葉級頁面中找到0101資料記錄；
    5:將該記錄返回用戶端。
  
    通過上面資料庫訪問索引的原理，我們就很容易解釋叢集索引與非叢集索引的區別了，原理都一樣，關鍵看什麼場合應用什麼索引了,下一篇我來總結一些不同場合最適合採用什麼樣的索引，不對之外多多指點。

    註：此篇文章的圖以及部分文字均來自網上。

作者：薑敏
出處：http://www.cnblogs.com/aspnet2008/