[TOC] ### 一、索引 #### 1、什么是索引 ??????索引是一種幫助mysql高效的獲取數據的數據結構，這些數據結構以某種方式引用數據，這種結構就是索引。可簡單理解為排好序的快速查找數據結構。如果要查“mysql”這個單詞，我們肯定需要定位到m字母，然后從下往下找到y字母，再找到剩下的sql。 具體的參考： http://www.2cto.com/database/201501/368126.html ??????作為索引中最為常見的一種類型，B-Tree索引大都采用的是 B+Tree數據結構來存儲數據（NDB集群存儲引擎內部實際上采用 T-Tree結構存儲這種索引）。B-Tree通常也意味著所有的值都是按順序存儲的。 #### 2、B-Tree數據結構 這點從磁盤讀寫上給出解釋，磁盤順序讀寫時才能達到其宣傳的數值(fio可以進行簡單的讀寫測試)，因為隨機讀寫，機械磁盤需要旋轉及尋道時間，哪怕是ssd，隨機讀寫也需要尋址時間；那么如果將索引tree構建的層數越低，使得key相近的數據都存在一起，伴隨磁盤預讀特性，能更進一步提高性能。 那么使用B+Tree的關鍵就是Tree層數低(3層)，有序的數據存儲位置接近，結合磁盤順序讀寫、OS預讀寫特性，使得能很快定位到數據；而使用RB-Tree時key值相近的數據會存儲的較遠，導致效率低下。 ### 二、MyISAM索引的實現 ??????MyISAM索引文件和數據文件是分離的，索引文件僅保存記錄所在頁的指針（物理位置），通過這些地址來讀取頁，進而讀取被索引的行。下圖是MyISAM的索引原理圖：（為了簡化，一個頁內只存放了兩條記錄。） ??????上圖所提供的示例表字段有Col1（ID）、Col2(age)、Col3（name）三個，其中Col1為Primary Key（主鍵），上圖很好地說明了樹中葉子保存的是對應行的物理位置。通過該值，存儲引擎能順利地進行回表查詢，得到一行完整記錄。同時，每個葉子頁也保存了指向下一個葉子頁的指針。從而方便葉子節點的范圍遍歷。 ??????而對于二級索引，在 MyISAM存儲引擎中以與上圖同樣的方式實現，可以看出MyISAM的索引文件僅僅保存數據記錄的地址。 ### 三、InnoDB索引的實現 #### 1、聚集索引 ??????與 MyISAM相同的一點是，InnoDB 也采用 B+Tree這種數據結構來實現 B-Tree索引。而很大的區別在于，InnoDB 存儲引擎采用“聚集索引”的數據存儲方式實現B-Tree索引，所謂“聚集”，就是指數據行和相鄰的鍵值緊湊地存儲在一起，注意 InnoDB 只能聚集一個葉子頁（16K）的記錄（即聚集索引滿足一定的范圍的記錄），因此包含相鄰鍵值的記錄可能會相距甚遠。 注意: innodb來說, 1: 主鍵索引 既存儲索引值,又在葉子中存儲行的數據 2: 如果沒有主鍵, 則會Unique key做主鍵 3: 如果沒有unique,則系統生成一個內部的rowid做主鍵. 4: 像innodb中,主鍵的索引結構中,既存儲了主鍵值,又存儲了行數據,這種結構稱為”聚簇索引” ??????下圖說明了 InnoDB聚集索引的實現方式，同時也體現了一張 innoDB表的結構，可以看到，InnoDB 中，主鍵索引和數據是一體的，沒有分開。： 這種實現方式，給予了 InnoDB 按主鍵檢索的超高性能。可以有目的性地選擇聚集索引，比如一個郵件表，可以選擇用戶ID來聚集數據，這樣只需要從磁盤讀取較少并且連續的數據頁就能獲得某個id的用戶全部的郵件，避免了讀取分散頁時所耗費的隨機I/O。 #### 2、輔助索引 ??????而對于輔助索引，InnoDB采用的方式是在葉子頁中保存主鍵值，通過這個主鍵值來回表（上圖）查詢到一條完整記錄，因此按輔助索引檢索實際上進行了二次查詢，效率肯定是沒有按照主鍵檢索高的。下圖是輔助索引的實現方式： ??????由于每個輔助索引都包含主鍵索引，因此，為了減小輔助索引所占空間，我們通常希望 InnoDB 表中的主鍵索引盡量定義得小一些（值得一提的是，MySIAM會使用前綴壓縮技術使得索引變小，而InnoDB按照原數據格式進行存儲。），并且希望InnoDB的主鍵是自增長的，因為如果主鍵并非自增長，插入時，由于寫入時亂序的，會使得插入效率變低。 ### 四、總結 #### 1、關于innoDB中索引的使用 ??????了解不同存儲引擎的索引實現方式對于正確使用和優化索引都非常有幫助，例如知道了InnoDB的索引實現后，就很容易明白為什么不建議使用過長的字段作為主鍵，因為所有輔助索引都引用主索引，過長的主索引會令輔助索引變得過大。再例如，用非單調的字段作為主鍵在InnoDB中不是個好主意，因為InnoDB數據文件本身是一顆B+Tree，非單調的主鍵會造成在插入新記錄時數據文件為了維持B+Tree的特性而頻繁的分裂調整，十分低效，而使用自增字段作為主鍵則是一個很好的選擇。 #### 2、什么時候選用myisam ??????myisam的主鍵索引的葉子節點只存放數據在物理磁盤上的指針，其他次索引也是一樣的； innodb的主鍵索引的葉子節點下面直接存放數據，其他次索引的葉子節點指向主鍵id； 由此可以挖掘出一個問題，就是如果Innodb有大數據列，比如 varchar(300)，這種比較多的話，那么排序的時候用主鍵id排序會比較慢，因為id主鍵下面放著所有數據列，而Myisam就不需要掃描數據列，要解決這個問題的話可以再建一個和主鍵id一起的聯合索引； ??????MyISAM表索引在處理文本索引時更具優勢，而INNODB表索引在其它類型上更具效率優勢。比如全文索引一般在CHAR、VARCHAR或TEXT列上創建，MyISAM表支持而INNODB表不支持，常見主要針對文本進行索引。同時MySQL高并發需要事務場景時，只能使用INNODB表。