深入瞭解MySQL的索引

標籤：規劃   變數   start   方式   目的   磁碟   效能調優   等價   關係   

（一）關於儲存引擎

   建立合適的索引是SQL效能調優中最重要的技術之一。在學習建立索引之前，要先瞭解MySql的架構細節，包括在硬碟上面如何組織的，索引和記憶體用法和操作方式，以及儲存引擎的差異如何影響到索引的選擇。   MySQL有很多種衍生版本，這些衍生版本支援更多不同種類的儲存引擎。本文主要討論三種MySQL引擎。

MyISAM一種非事務性的儲存引擎，是MySQL 5.5之前版本預設的儲存引擎。

InnoDB 最流行的事務性儲存引擎，從5.5版開始成為MySQL預設的引擎。

Memory 基於記憶體的，非事務性的以及非持久性的儲存引擎。

注意：

    從5.5版本開始，MySQL表的預設儲存引擎從MyISAM換成InnoDB，將會使使用者安裝那些依賴預設設定或者專門為MyISAM編寫的軟體包時帶來很大的影響。

（二）MySQL索引類型

    MySQL支援在所有關聯式資料庫表中建立主鍵、唯一鍵、不唯一的非主碼索引等多種類型的索引。此外MySQL還支援純文字和空間索引類型。   MySQL內建的儲存引擎對各種索引技術有不同的實現方式，包括：B-樹，B+樹，R-樹以及散列類型。   索引資料結構理論：

1.B-樹

B-樹中有兩種節點類型：索引節點和葉子節點。葉子節點是用來儲存資料的，而索引節點則用來告訴使用者儲存在葉子節點中的資料順序，並協助使用者找到相應的資料。   B-樹的搜尋，從根節點開始，對節點內的關鍵字有序進行二分尋找，如果命中則結束，否則進入查詢關鍵字所屬範圍的兒子節點，重複。直到所對應的兒子指標為空白，或已經是葉子節點。   B-樹是一種多路搜尋樹：   (1). 定義任意非葉子節點最多有M個兒子，且M>2;   (2). 根節點的兒子數為[2,M];   (3). 除根節點以外的非葉子節點的兒子數為[M/2,M];   (4). 每個節點存放至少M/2-1(取上整)和至多M-1個關鍵字;   (5). 非葉子節點的關鍵字個數=指向兒子節點的指標的個數-1;     (6). 非葉子節點的關鍵字：k[i]   (7). 非葉子節點的指標：p[1]，p[2]，·····，p[M]；其中p[1]指向的關鍵字小於k[1]的子樹，p[M]指向的關鍵字大於K[m-1]的子樹;   (8). 所有的葉子節點位於同一層;  2.B+樹B+樹資料結構是B-樹實現的增強版本。儘管B+樹支援B-樹索引的所有特性，它們之間最顯著的不同點在於B+樹中底層資料是根據被提及的索引列進行排序的。B+樹還通過葉子節點之間的附加引用來最佳化掃描效能。   B+搜尋和B-搜尋不同，區別是B+樹只有達到葉子節點才命中（B-樹可以在非葉子節點命中），其效能等價於關鍵字全集做一次二分搜尋。  B+樹的特性： （1）所有關鍵字都出現在葉子節點的鏈表中，葉子節點相當於儲存資料的資料層。 （2）不可能在非葉子節點上命中。 （3）非葉子節點相當於是葉子節點的索引，葉子節點相當於資料層。

3.散列

 散列表資料結構是一種很簡單的概念，它將一種演算法應用到給定值中以在底層資料存放區系統中返回一個唯一的指標或位置。散列表的優點是始終以線性時間複雜度找到需要讀取的行的位置，而不像B-樹那樣需要橫跨多層節點來確定位置。

4.通訊R-樹

R-樹資料結構支援基於資料類型對幾何資料進行管理。目前只有MyISAM使用R-樹實現支援空間索引，使用空間索引也有很多限制，比如只支援唯一的NOT NULL列等。

5.全文本

 全文本結構也是一種MySQL採用的基本資料結構。這種資料結構目前只有目前的版本MySQL中的MyISAM儲存引擎支援。5.6版本將要在InnoDB儲存引擎中加入全文本功能。全文本索引在大型系統中並沒有什麼實用的價值，因為大規模系統有很多專門的檔案檢索產品。所以不用在介紹。

MySQL實現

   對B-樹，B+樹和散列等資料結構的基本概念有了一些瞭解之後，我們就可以開始討論MySQL通過支援它們的儲存引擎如何?不同的演算法。同時每種實現也對磁碟和記憶體使用量情況有不同的影響，這一點在大型資料庫系統中是非常重要的考慮因素。

1.MyISAM的B-樹

 MyISAM儲存引擎使用B-樹資料結構來實現主碼索引、唯一索引以及非主碼索引。在MyISAM實現資料目錄和資料庫模式子目錄中，使用者可以找到和每個MySQL表對應的.MYD和.MYI檔案。資料庫表上定義的索引資訊就儲存在MYI檔案中，該檔案的塊大小是1024位元組。這個大小是可以通過myisam-block-size系統變數分配。$  ls -1h /var/lib/mysql/book/source_words.MY*-rw-rw---- 1 mysql mysql  9.2M 2015-05-07 19:08source_words.MYD

-rw-rw---- 1 mysql mysql 7.8M 2015-05-07 19:08

source_words.MYI 這些檔案結構的內部格式可以從MySQL免費原始碼中找到，也可以查看MySQL內部手冊。

在MyISAM中，非主碼索引的B-樹結構儲存索引值和一個指向主碼資料的指標，這是MyISAM和InnoDB的一個顯著區別。這一點導致了兩個儲存引擎的索引的不同工作方式。

 MyISAM索引是在記憶體的一個公用緩衝中管理的，這個緩衝的大小可以通過key_buffer_size或者其他命名金鑰快取來定義。這是根據統計和規劃的表索引的大小來設定緩衝大小時主要的考慮因素。

2. InnoDB的B+樹聚簇主碼

   InnoDB儲存引擎在它的主碼索引（也被稱為聚簇主碼）中使用了B+樹，這種結構把所有資料都和對應的主碼組織在一起，並且在葉子節點這一層上添加額外的向前和向後的指標，這樣就可以更方便地進行範圍掃描。

   在檔案系統層面，所有InnoDB資料和索引資訊都預設在公用InnoDB資料表空間中管理，否則管理員就通過innodb_data_file_path這個變數指定檔案路徑。這是一個叫ibdatal檔案。

   由於InnoDB用聚簇主碼儲存資料，底層資訊佔用的磁碟空間的大小很大程度上取決於頁面的填滿因數。對於按序排列的主碼，InnoDB會用16K頁面的15/16作為填滿因數。對於不是按序排列的主碼，預設情況下InnoDB會插入初始資料的時候為每一個頁面分配50%作為填滿因數。

   在改索引的實現方式中B+樹的葉子節點上是data就是資料本身，key為主鍵，如果是一般索引的話，data便會指向對應的主索引。在B+樹的每一個葉子節點上面增加一個指向相鄰葉子節點的指標，就形成了帶有順序訪問指標的B+樹。其目的是提高區間訪問的效能。

3.InnoDB的B-樹非主碼

InnoDB中的非主碼索引使用了B-樹資料結構，但InnoDB中的B-樹結構實現和MyISAM中並不一樣。在InnoDB中，非主碼索引儲存的是主碼的實際值。而MyISAM中，非主碼索引儲存的包含主碼值的資料指標。這一點很重要。首先，當定義很大的主碼的時候，InnoDB的非主碼索引可能回更大，隨著非主碼索引數量的增加，索引之間大小差別可能會變得很大。另一個不同點在於非主碼索引當前可以包含主鍵的值，並且可以不是索引必須有的部分。

4.記憶體散列索引

在預設MySQL的引擎索引中，只有MEMORY引擎支援散列資料結構，散列結構的強度可以表示為直接鍵尋找的簡單性，散列索引的相似性模式比對查詢比直接查詢慢。也可以為MEMORY引擎指定一個B-樹索引實現。

5.記憶體B-樹索引

對於大型MEMORY表來說，使用散列索引進行索引範圍搜尋的效率很低，B-樹索引在執行直接鍵查詢時確實比使用預設的散列索引快。根據B-樹的不同深度，B-樹索引在個別操作中的確可能比散列演算法快。

6.InnoDB內部散列索引

InnoDB儲存引擎在聚簇B+樹索引中儲存主碼：但在InnoDB內部還是使用記憶體中的散列表來更高效地進行主碼查詢。這個機制有InnoDB儲存引擎來管理，使用者只能通過innodb_adaptive_hash_index配置項來選擇是否啟用這個唯一的配置選項。

深入瞭解MySQL的索引