MySQL索引結構詳細解析
簡介
在數據之外，數據庫系統還維護著滿足特定查找算法的數據結構，這些數據結構以某種方式引用（指向）數據，這樣就可以在這些數據結構上實現高級查找算法。這種數據結構，就是索引。
一般來說索引本身也很大，不可能全部存儲在內存中，因此索引往往以索引文件的形式存儲的磁盤上。
優點：
1、類似大學圖書館建書目索引，提高數據檢索的效率，降低數據庫的IO成本。
2、通過索引列對數據進行排序，降低數據排序的成本，降低瞭CPU的消耗。
缺點：
1、雖然索引大大提高瞭查詢速度，同時卻會降低更新表的速度，如對表進行INSERT、UPDATE和DELETE。因為更新表時，MySQL不僅要保存數據，還要保存一下索引文件。每次更新添加瞭索引列的字段，都會調整因為更新所帶來的鍵值變化後的索引信息。
2、實際上索引也是一張表，該表保存瞭主鍵與索引字段，並指向實體表的記錄，所以索引列也是要占用空間的
索引舉例：（用樹結構做索引）
左邊是數據表，一共有兩列七條記錄，最左邊的是數據記錄的物理地址。
為瞭加快Col2的查找，可以維護一個右邊所示的二叉查找樹，每個節點分別包含索引鍵值和一個指向對應數據記錄物理地址的指針，這樣就可以運用二叉查找在一定的復雜度內獲取到相應數據，從而快速的檢索出符合條件的記錄。
索引結構（樹）
如何通過索引加快數據庫表的查詢速度呢？為瞭方便講解，我們限定於數據庫表隻包含下面這樣兩個查詢需求：
1、select* from user where id=1234；
2、select *from user where id>1234 and id<2345;（按區間）
為什麼用樹，而不用哈希表
哈希表按值查詢的性能很好，時間復雜度是O(1)，但它不能支持按照區間快速查找數據，因此無法滿足要求。同理，盡管平衡二叉查找樹查詢性能很高，時間復雜度為O(logn)，而且對樹進行中序遍歷，可以輸出有序的數據序列，但也無法滿足按照區間快速查找數據的需求。
為瞭支持按照區間快速查找數據，我們對二叉查找樹進行改造，將二叉查找樹的葉子節點用鏈表串起來，如果要查找某個區間的數據，隻需要用區間的起始值，在樹中進行查找，當定位到有序鏈表中的某個節點之後，再從這個節點開始順著有序鏈表往後遍歷，直到有序鏈表中的節點數據值大於區間終止值為止。
又因為樹上的很多操作的時間復雜程度與樹的高度成正比，降低的樹的高度，就能減少磁盤IO操作。因此我們把索引構建成m叉樹（m>2），詳細介紹可看後文。
BTree索引
在介紹B+樹之前，先來瞭解一下B樹。
1、初始化介紹
一顆b樹，淺藍色的塊我們稱之為一個磁盤塊，可以看到每個磁盤塊包含幾個數據項（深藍色所示）和指針（黃色所示），如磁盤塊1包含數據項17和35，包含指針P1、P2、P3。P1表示小於17的磁盤塊，P2表示在17和35之間的磁盤塊，P3表示大於35的磁盤塊。
註意：
真實的數據隻存在於葉子節點，即3、5、9、10、13、15、28、29、36、60、75、79、90、99。（而且是多條數據組成的數據區間：3~ 5，… … ，90~ 99）
非葉子節點不存儲真實的數據，隻存儲指引搜索方向的數據項，如17、35並不真實存在於數據表中。
2、查找過程
如果要查找數據項29，那麼首先會把磁盤塊1由磁盤加載到內存，此時發生一次IO，在內存中用二分查找確定29在17和35之間，鎖定磁盤塊1的P2指針，內存時間因為非常短（相比磁盤的IO）可以忽略不計，通過磁盤塊1的P2指針的磁盤地址把磁盤塊3由磁盤加載到內存，發生第二次IO，29在26和30之間，鎖定磁盤塊3的P2指針，通過指針加載磁盤塊8到內存，發生第三次IO，同時內存中做二分查找找到29，結束查詢，總計三次IO。
B+Tree索引
B+樹和B樹類似，B+樹是B樹的改進版。 即：m叉查找樹與有序鏈表構建成的樹就是B+樹，也就是要存儲的樹索引
如圖：B+樹和B樹的主要區別有以下兩點：
1、B+樹的葉子節點用鏈表來串聯。 查找某個區間的數據，隻需要用區間的起始值，在樹中進行查找，當定位到有序鏈表中的某個節點之後，再從這個節點開始順著有序鏈表往後遍歷，直到有序鏈表中的節點數據值大於區間終止值為止。
2、B+樹中的任何節點都不存儲真實數據，隻是用來索引。 B樹直接通過葉子節點獲取到數據；而B+樹每個葉子節點存儲數據行的鍵值和地址信息，當查詢到某個葉子節點時，通過葉子節點的地址找到真實的數據信息。
聚簇索引與非聚簇索引
聚簇索引並不是一種單獨的索引類型，而是一種數據存儲方式。 術語‘聚簇’表示數據行和相鄰的鍵值聚簇的存儲在一起。
聚簇索引的好處：
按照聚簇索引排列順序，查詢顯示一定范圍數據的時候，由於數據都是緊密相連，數據庫不不用從多個數據塊中提取數據，所以節省瞭大量的io操作。
聚簇索引的限制：
1、對於mysql數據庫目前隻有innodb數據引擎支持聚簇索引，而Myisam並不支持聚簇索引。
2、由於數據物理存儲排序方式隻能有一種，所以每個Mysql的表隻能有一個聚簇索引。一般情況下就是該表的主鍵。
3、為瞭充分利用聚簇索引的聚簇的特性，所以innodb表的主鍵列盡量選用有序的順序id，而不建議用無序的id，比如uuid這種。
如下圖，左側的索引就是聚簇索引，因為數據行在磁盤的排列和索引排序保持一致。
索引分類
單值索引
即一個索引隻包含單個列，一個表可以有多個單列索引
隨表一起建索引： CREATE TABLE customer ( id INT(10) UNSIGNED AUTO_INCREMENT , customer_no VARCHAR(200), customer_name VARCHAR(200), PRIMARY KEY(id), KEY (customer_name) ); 單獨建單值索引： CREATE INDEX idx_customer_name ON customer(customer_name); 刪除索引： DROP INDEX idx_customer_name on customer;
唯一索引
索引列的值必須唯一，但允許有空值
隨表一起建索引： CREATE TABLE customer ( id INT(10) UNSIGNED AUTO_INCREMENT , customer_no VARCHAR(200), customer_name VARCHAR(200), PRIMARY KEY(id), KEY (customer_name), UNIQUE (customer_no) ); 單獨建唯一索引： CREATE UNIQUE INDEX idx_customer_no ON customer(customer_no); 刪除索引： DROP INDEX idx_customer_no on customer ;
主鍵索引
設定為主鍵後數據庫會自動建立索引，innodb為聚簇索引
隨表一起建索引： CREATE TABLE customer ( id INT(10) UNSIGNED AUTO_INCREMENT , customer_no VARCHAR(200), customer_name VARCHAR(200), PRIMARY KEY(id) ); CREATE TABLE customer2 ( id INT(10) UNSIGNED , customer_no VARCHAR(200), customer_name VARCHAR(200), PRIMARY KEY(id) ); 單獨建主鍵索引： ALTER TABLE customer add PRIMARY KEY customer(customer_no); 刪除建主鍵索引： ALTER TABLE customer drop PRIMARY KEY ; 修改建主鍵索引： 必須先刪除掉(drop)原索引，再新建(add)索引
復合索引
即一個索引包含多個列
隨表一起建索引： CREATE TABLE customer ( id INT(10) UNSIGNED AUTO_INCREMENT , customer_no VARCHAR(200), customer_name VARCHAR(200), PRIMARY KEY(id), KEY (customer_name), UNIQUE (customer_name), KEY (customer_no,customer_name) ); 單獨建索引： CREATE INDEX idx_no_name ON customer(customer_no,customer_name); 刪除索引： DROP INDEX idx_no_name on customer ;
性能分析
索引創建場景
哪些情況需要創建索引
1、主鍵自動建立唯一索引
2、頻繁作為查詢條件的字段應該創建索引
3、查詢中與其它表關聯的字段，外鍵關系建立索引
4、單鍵/組合索引的選擇問題， 組合索引性價比更高
5、查詢中排序的字段，排序字段若通過索引去訪問將大大提高排序速度
6、查詢中統計或者分組字段
哪些情況不要創建索引
1、表記錄太少
2、經常增刪改的表或者字段 原因：提高瞭查詢速度，同時卻會降低更新表的速度，如對表進行INSERT、UPDATE和DELETE。因為更新表時，MySQL不僅要保存數據，還要保存一下索引文件
3、Where條件裡用不到的字段不創建索引
4、過濾性不好的不適合建索引
到此這篇關於MySQL索引 詳細解析的文章就介紹到這瞭,更多相關MySQL 索引內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！