MySQL中的索引有什麼用

索引

1、索引的優點

（1）提高查詢效率（降低IO使用率）

（2）降低CPU使用率

例如查詢order by age desc，因為B 索引樹本身就是排好序的，所以再查詢如果觸發索引，就不用再重新查詢了。

2、索引的弊端

（1）索引本身很大，可以存放在記憶體或硬碟上，通常儲存在硬碟上。

（2）索引不是所有情況都使用，例如①少量資料②頻繁變化的欄位③很少使用的欄位

（3）索引會降低增刪改的效率

3、索引的分類

（1）單一值索引

（2）唯一索引

（3）聯合索引

（4）主鍵索引

備註：唯一索引和主鍵索引唯一的差異：主鍵索引不能為null

4、建立索引

alter table user add INDEX `user_index_username_password` (`username`,`password`)

5、MySQL索引原理-> B 樹

MySQL索引的底層資料結構是B 樹

B Tree是在B- Tree基礎上的最佳化，使其更適合實現外部儲存索引結構，InnoDB儲存引擎就是用B Tree實現其索引結構。

B-Tree結構圖中每個節點中不僅包含資料的key值，還有data值。而每一個頁的儲存空間是有限的，如果data資料較大時將會導致每個節點（即一個頁）能儲存的key的數量很小，當儲存的資料量很大時同樣會導致B- Tree的深度較大，增加查詢時的磁碟I/O次數，進而影響查詢效率。在B Tree中，所有資料記錄節點都是按照鍵值大小順序存放在同一層的葉子節點上，而非葉子節點上只存儲key值信息，這樣可以大大加大每個節點存儲的key值數量，降低B Tree的高度。

B Tree相對於B-Tree有幾點不同：

非葉子節點只儲存鍵值資訊。
所有葉子節點之間都有一個鏈指標。
資料記錄都存放在葉子節點中。
將上一節中的B-Tree優化，由於B Tree的非葉子節點只儲存鍵值訊息，假設每個磁碟區塊能儲存4個鍵值及指標訊息，則變成B Tree後其結構如下圖所示：

通常在B Tree上有兩個頭指針，一個指向根節點，另一個指向關鍵字最小的葉子節點，而且所有葉子節點（即資料節點）之間是一種鍊式環結構。因此可以對B Tree進行兩種查找運算：一種是對於主鍵的範圍查找和分頁查找，另一種是從根節點開始，進行隨機查找。

可能上面例子中只有22筆資料記錄，看不出B Tree的優點，下面做一個推算：

InnoDB儲存引擎中頁的大小為16KB，一般表的主鍵類型為INT（佔用4個位元組）或BIGINT（佔用8個位元組），指標型別也一般為4或8個位元組，也就是說一個頁（B Tree中的一個節點）中大概儲存16KB/( 8B 8B)=1K個鍵值（因為是估值，為方便計算，這裡的K取值為〖10〗^3）。也就是說一個深度為3的B Tree索引可以維護10^3 * 10^3 * 10^3 = 10億 筆記錄。

實際情況中每個節點可能無法填滿，因此在資料庫中，B Tree的高度一般都在2~4層。 MySQL的InnoDB儲存引擎在設計時是將根節點常駐記憶體的，也就是說尋找某一鍵值的行記錄時最多只需要1~3次磁碟I/O操作。

資料庫中的B Tree索引可以分為聚集索引（clustered index）和輔助索引（secondary index）。上面的B Tree範例圖在資料庫中的實作即為聚集索引，聚集索引的B Tree中的葉子節點存放的是整張表的行記錄資料。輔助索引與聚集索引的區別在於輔助索引的葉子節點並不包含行記錄的全部數據，而是儲存相應行數據的聚集索引鍵，即主鍵。當透過輔助索引來查詢資料時，InnoDB儲存引擎會遍歷輔助索引找到主鍵，然後再透過主鍵在聚集索引中找到完整的行記錄資料。

以上是MySQL中的索引有什麼用的詳細內容。更多資訊請關注PHP中文網其他相關文章！