資料庫索引學習，資料庫索引

資料庫索引學習，資料庫索引
一、索引介紹

資料庫索引調優是一項技術活，不能僅僅靠理論，因為實際情況千變萬化，而且各種資料庫本身存在很複雜的機制，如查詢最佳化策略和各種引擎的實現等。但同時這些理論是索引調優的基礎，只有在明白理論的基礎上，才能對調優策略進行合理推斷並瞭解其背後的機制，然後結合實踐中不斷的實驗和摸索。

索引其實就是在資料庫儲存資料之外，還維護著另外的滿足某種查詢演算法的資料結構，這些資料結構再以某種方式指向真實的儲存資料，然後在現有的資料結構上實現快速查詢。（找了張很不錯的二叉樹的圖來理解索引，現在大多的索引都是B+樹的結構，基本上沒有使用二叉樹的）。

二、資料庫操作時間佔用都在IO操作上

索引本身也很大，不可能全部儲存在記憶體中，往往以索引檔案的形式儲存在磁碟上。所以索引尋找過程中就要產生磁碟I/O消耗，相對於記憶體存取，I/O存取的消耗要高出好多好多，所以評價一個資料結構作為索引的優劣最重要的指標就是在尋找過程中磁碟I/O操作次數的複雜度。換句話說，索引的結構組織要盡量減少尋找過程中磁碟I/O的存取次數。而B+樹結構在實際的應用中剛好滿足減少物理I/O操作的要求，來勝任索引結構再合適不過了（下面介紹）。

三、隨機IO、順序IO

    資料庫查詢大部分時間都消耗在了I/O上，而記憶體I/O要比物理I/O快的多，所以 一次查詢操作物理I/O的次數很大程度決定了查詢的最終時間。同時物理I/O的種類又分了 順序、隨機I/O, 順序和隨機，是指本次IO給出的初始扇區地址，和上一次IO的結束扇區地址，是不是完全連續的，或者相隔不多的，如果是，則本次IO應該算是一個連續IO，如果相差太大，則算一次隨機IO。連續IO，因為本次初始扇區和上次結束扇區相隔很近，則磁頭幾乎不用換道或換道時間極短；如果相差太大，則磁頭需要很長的換道時間，如果隨機IO很多，導致磁頭不停換道，效率大大降底。

    隨機訪問的特點是每次IO請求的資料在磁碟上的位置跨度很大（如：分布在不同的扇區），因此N個非常小的IO請求會以N次IO請求才能擷取到相應的資料。

    順序訪問的特點跟隨機訪問相反，它請求的資料在磁碟的位置是連續的。當系統發起N個非常小的IO請求（如：1K）時，因為一次IO是有代價的，系統會取完整的一塊資料（如4K、8K），所以當第一次IO完成時，後續IO請求的資料可能已經有了。這樣可以減少IO請求的次數。

    隨機訪問的特點是每次IO請求的資料在磁碟上的位置跨度很大（如：分布在不同的扇區），因此N個非常小的IO請求會以N次IO請求才能擷取到相應的資料。

    順序訪問的特點跟隨機訪問相反，它請求的資料在磁碟的位置是連續的。當系統發起N個非常小的IO請求（如：1K）時，因為一次IO是有代價的，系統會取完整的一塊資料（如4K、8K），所以當第一次IO完成時，後續IO請求的資料可能已經有了。這樣可以減少IO請求的次數。

四、檔案系統和資料庫系統中常用的B+ 樹結構

在學校時候理解資料庫中的查詢就認為是一般的順序尋找，現在再回想這種複雜度為O(n)的演算法在資料量很大的情況下簡直不敢想想，那麼多牛逼的查詢演算法像二分尋找、二叉樹尋找等 在實際的應用上還真沒有好好琢磨過。找了些對比B-Tree、B+Tree結構的資料（很好詮釋了兩種結構的區別）。

1、B-Tree、B+Tree主要區別：
    B+樹的非葉子結點只包含導航資訊，不包含實際的值，所有的葉子結點和相連的節點使用鏈表相連，便於區間尋找和遍曆。

2、B+ 樹的優點在於：

• B+樹在非葉子節點上不儲存data，只儲存key，因此在載入到記憶體中時能夠存放更多的key；
• 非葉子節點特別小，出度大的情況下非葉子節點少，可以完全載入到記憶體中減少 IO次數； 
• 資料key值存放的更加緊密，具有更好的空間局部性。因此訪問葉子幾點上關聯的資料也具有更好的快取命中率。
• B+樹的葉子結點都是相鏈的，因此對整棵樹的便利只需要一次線性遍曆葉子結點即可。而且由於資料順序排列並且相連，所以便於區間尋找和搜尋，可以被連續載入；
• 而B樹則需要進行每一層的遞迴遍曆。相鄰的元素可能在記憶體中不相鄰，所以快取命中性沒有B+樹好；
• 但是B樹也有優點，其優點在於，由於B樹的每一個節點都包含key和value，因此經常訪問的元素可能離根節點更近，因此訪問也更迅速；

五、複合式索引使用情境

常索引有 唯一索引、主鍵索引、叢集索引，有時候為了了更多的提高mysql效率可建立複合式索引，遵循“最左首碼“原則。最左首碼：顧名思義，就是最左優先。例如，對於表title(id,no,name,age)我們建立了以三個欄位的組合多列索引(no,name,age),相當於建立了(no)單列索引，(name,fname)複合式索引以及(no,name,age)複合式索引。

 其他的不多說了，這次主要是對複合式索引的學習，下面列出複合式索引“最左首碼”的情境 (就以上表title為例、通過查看執行計畫是否走了索引)：

1、可以走索引的全列匹配

• 當查詢條件 where no=n and name=b and age =20  或者 where no=n  and  name=b  或者where no=n 都可以走進索引；

• 項mysql查詢最佳化工具會自動調整where子句的條件順序以使用適合的索引，所以把條件順序做下調整不會影響結果 如：where age=20  and  name=b  and  no=n 同樣可以走到索引；

• 在條件中使用“in”文法同樣可以走到索引；

2、最左首碼，中間的一個條件未提供

如 where no = n  and age = 20  少了索引中間列 name ，這時候只用到了索引的第一列no ，後面age雖然也在索引中，由於缺少name列 無法和左首碼串連；

3、查詢條件沒有指明 索引第一列 

     如果少了索引的第一列no  如：where name = ‘jeck’ and age = 20 這樣不匹配最左首碼，就無法走索引了。

4、索引欄位中用到了 %的模糊比對文法

     如果在某列中使用了 % 模糊比對，要看%的位置判斷是否能走到索引， %在字串的最後則可以使用索引 ，如：
where  no = 1  and name like  'luc%’ ； 但是，如果%在前面就不行  如：where no = 1 and name like ‘%cy’ ;

5、範圍尋找 

     如果在sql中用到了 < 、 >的範圍尋找 ，且只有一個範圍列的 就可以走到索引，否則都走不到索引 ；
如：where no < 20  and   name = ‘lucy’ ; 

6、查詢結果中含有函數運算式

   有函數運算式也無法走到索引；

六、建索引注意事項 

1、某個列作為條件式篩選的結果仍然很大

像”性別”屬性，資料庫中只會有男、女兩種類型，這時候完全沒必要使用索引，反而會導致查詢結果更糟糕。因為如果不走索引將是個全表的順序查詢（一個隨機IO），但是這時候走了索引並且還返回了一個表的很大部分記錄，會出現很多的隨機IO，無形中增加了操作的IO。

2、複合式索引欄位順序

     所以在使用複合式索引時候，索引欄位的順序對索引結構影響不大，但是會影響到最終的排序，最終影響以部分欄位作為查詢條件時候會不會走上索引的問題。

3、哪些情境不適合建索引

     對於記錄比較少的表，增加索引不會帶來速度的最佳化反而浪費了儲存空間，因為索引是需要儲存空間的，而且有個致命缺點是對於update/insert/delete的每次執行，欄位的索引都必須重新計算更新；

4、建索引考慮的條件

     索引盡量少、索引盡量短、合理組織索引欄位順序、索引欄位更新頻率盡量少、索引應該是常用查詢欄位且能快速定位少量記錄。 

更多學習參考：

http://www.ituring.com.cn/article/986http://www.cnblogs.com/yangecnu/archive/2014/03/29/Introduce-B-Tree-and-B-Plus-Tree.htmlhttp://blog.codinglabs.org/articles/theory-of-mysql-index.htmlhttp://www.blogjava.net/happyenjoylife/archive/2011/12/17/366639.html

著作權聲明：本文為博主原創文章，未經博主允許不得轉載。