(3)mysql index

生活中的索引

最常見的書籍是有目錄的，也可以叫做為索引。

為啥用索引？

為快不破，為了快速的查找到我們想要的東西，書中的索引可以快速查詢到我們想看的章節内容。DB中的索引亦是如此。

索引本質

本質就是查找算法和資料結構，學過很多查找算法，順序查找、二分查找、歸并查找等等，如果DB不加索引就是對全表進行掃描，所說的全表掃描就是順序查找。而mysql InnoDB中的索引就是有二分查找算法中的二分思想，二分查找是需要資料的排列是有序的。

索引是對資料表中的一個或者多個列進行排序的資料結構，以協助快速查詢、更新資料庫表中資料。

mysql索引類型

B+ 樹索引

全文索引

哈希索引

常用的和最有效的是B+樹索引，本篇主要說B+樹索引。

磁盤IO與預讀

考慮到磁盤IO是非常昂貴的操作，計算機作業系統做了一下優化，當一次IO時，不僅把目前磁盤位址的資料讀取到記憶體緩沖區，也會把相鄰的資料也會讀取到緩存區。因為當計算機通路一個位址的時候，與其相鄰的資料也會很快被通路到，每一次IO讀取稱為一頁。

mysql一個區中有多少頁被順序通路時，InnoDB存儲才啟用預讀？

我們可以檢視參數innodb_read_ahead_threshold

mysql> show variables like 'innodb_read_ahead_threshold';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| innodb_read_ahead_threshold | 56    |
+-----------------------------+-------+
1 row in set (0.01 sec)
           

當一個區中的56個頁已經被通路過并且為順序通路的，則會預讀下一個區的所有頁。（來自《mysql内幕 InnoDB存儲引擎》）

B+樹索引

B+樹索引的構造類似于二叉樹，根據（key value）快速找到資料。

B+數（balance Tree）能找到被查找資料行所在的頁。然後資料庫通過把頁讀入到記憶體，再在記憶體中進行查找，最後得到要查找的資料。

索引是對資料表中一個或者多個列的值進行排序的資料結構，以協助快速查詢更新資料庫表中的資料。索引加快了通路速度，因為存儲引擎不會再去掃描整張表得到需要的資料；相反，他從根節點開始，根節點儲存了子節點的指針，存儲引擎會根據指針快速的尋找資料。）

非葉子節點: 不存儲真實的資料，隻存儲指引搜尋方向的資料項。

葉子節點：存儲真實的資料，每個磁盤塊中是一頁，一頁4k或8k

葉子和葉子之間：B+tree所有的葉子節點通過指針連接配接在一起

B+樹的查找過程及其性質

例如查找3，從根節點開始查找，會把根節點的磁盤塊加載到記憶體，此時會發生一次IO，然後根據磁盤塊1的指針，找到磁盤塊2，把磁盤塊2加載到記憶體，然後通過指針指向找到磁盤塊5，把磁盤塊5加載到記憶體。

在記憶體中用二分查找法查找到3。是以找到3一共發生了三次IO，3層的b+樹可以表示上百萬的資料，如果上百萬資料查找需要三次IO，性能将是很大的提高，如果索引順序查找百萬資料成本是非常高的。

一般索引要求索引字段越小越好，一頁的大小是固定的，當索引字段（例如int bigint ）所占位元組大的，磁盤塊中存放的資料就會少，樹的高度就會越高，這樣IO次數就會增加，反之就會減少。

最左比對原則

(A,B,C)組合索引，索引生效的是A AB ABC ，而AC隻是A走索引了，C不會走索引，而BC也完全不會走索引。因為b+樹是按照從左向右的順序來建立搜尋樹的，隻有當搜尋到A才會搜尋到B，才會找到C。也就是搜尋的方向确定了，如果是AC，隻會搜尋到A，下一個字段B缺失了。

為什麼B+樹比B樹更适合作為資料庫和檔案的索引?

資料庫采用B+樹為非B樹： B+樹隻要周遊葉子節點就可以實作整棵樹的周遊，而在資料庫中基于範圍的查找是非常頻繁的，而B樹查找隻能用中序周遊，效率較低。

B+樹查詢效率穩定：葉子節點是索引，非葉子節點是指向，任何查找都是從根節點到葉子節點的路程，是以查找路徑長度相同，每個資料的查詢效率相當。

什麼情況下需要建立索引?

1 、表的主鍵會自動添加上主鍵索引。

2、經常與其他表進行連接配接的字段，需要在這個字段上添加上索引。

3、經常出現在where字段後，特别是需要大表查詢的，需要建立索引。

4、索引需要建立在小的字段上，對于大文本或者超長的字段不要建立索引。

5、複合索引建立需要仔細分析；盡量用單字段索引代替。

6、如果複合索引中包含的字段超過3個，考慮必要性，考慮減少複合自斷。

7、複合索引字段是否經常以and方式出現在where字句中？單個查詢是否極少或者沒有？如果是，則建立複合索引，否則需要建立單個索引。

8、經常出現在order by 、group by、distinct後面字段。

索引注意事項

以”%”号開頭的like模糊比對語句，不會走索引。

or語句前後沒有同時使用索引。

類型出現隐式轉換，不會使用索引。

最左比對原則。

索引分類

普通索引和唯一索引：索引列值唯一性

單個索引和複合索引：索引列包含的列數

聚集索引和非聚集索引：

聚集索引的葉子節點是資料頁、每個頁通過一個雙連結清單來進行連結的；聚集是以不是實體上連續，而是邏輯上的連續。

非聚集索引（輔助索引）：葉子節點除了包含鍵值外，每個葉節點還包括一個書簽。當通過非聚集索引葉子節點的指針，來指向主鍵索引的主鍵，然後通過主鍵索引來找一個完整的行記錄。（通過非聚集索引找到聚集索引）

索引與磁盤

我們知道固态硬碟的讀寫速度要大于普通的機械硬碟。mysql資料庫使用ssd呢？

http://fewstreet.com/2014/01/06/mysql-hard-disk-vs-SSD-performance.html

但是也有文章說放到ssd上并沒有很大的效果，可能是我們的索引資料結構在機械硬碟和固态硬碟上并沒有什麼差别，沒有利用固态硬碟的優勢。

https://blog.2ndquadrant.com/tables-and-indexes-vs-hdd-and-ssd/

而且固态硬碟資料不容易恢複，價格也比普通的機械硬碟昂貴。

書上這樣說的，

固态硬碟的接口規範、定義、功能和使用等方面與傳統機械硬碟相同，但是他們的内部構造完全不同，固态硬碟沒有讀寫磁頭，讀取資料需要圍繞中心軸旋轉，是以，在随機讀取性能上有了質的飛躍。

mysql官方文檔:https://dev.mysql.com/doc/refman/5.6/en/optimizing-innodb-diskio.html

歡迎留言讨論哈

參考：http://blog.csdn.net/justloveyou_/article/details/78308460

參考：https://tech.meituan.com/mysql-index.html

參考：

https://mp.weixin.qq.com/s?__biz=MzI2NjA3NTc4Ng==&mid=2652079363&idx=1&sn=7c2209e6b84f344b60ef4a056e5867b4&chksm=f1748ee6c60307f084fe9eeff012a27b5b43855f48ef09542fe6e56aab6f0fc5378c290fc4fc&scene=0&pass_ticket=75GZ52L7yYmRgfY0HdRdwlWLLEqo5BQSwUcvb44a7dDJRHFf49nJeGcJmFnj0cWg#rd