-
-
-
- 1. 存儲引擎：
- 2. 索引檔案：
-
- 2.1. MyISAM的索引檔案：
- 2.2. InnoDB的索引檔案：
- 3. 索引類型：
-
- 3.1. 主鍵索引(Primary Key)：
- 3.2. 二級索引(輔助索引)：
- 4. 索引資料結構：
-
- 4.1. MyISAM索引實作：
- 4.2. InnoDB 索引實作：
-
- 4.2.1. InnoDB 主鍵索引實作：
- 4.2.2. InnoDB 輔助索引實作：
- 4.2.3. InnoDB 聯合索引實作：
- 5. 聚簇索引和非聚簇索引：
-
- 5.1. 非聚簇索引：
- 5.2. 非聚簇索引：
- 6. 關于InnoDB索引小結：
-
-
1. 存儲引擎：
存儲引擎是不同資料檔案在磁盤上的不同組織形式
- MySQL 5.5 之前，MyISAM 引擎是 MySQL 的預設存儲引擎
- MySQL 5.5 之後，MySQL 引入了 InnoDB（事務性資料庫引擎），MySQL 5.5 版本後預設的存儲引擎為 InnoDB
【差別】：
- MyISAM 隻有表級鎖(table-level locking)；而 InnoDB 支援行級鎖(row-level locking)和表級鎖,預設為行級鎖。
- MyISAM 不提供事務支援；而InnoDB 提供事務支援，具有送出(commit)和復原(rollback)事務的能力。
- MyISAM 不支援外鍵；而 InnoDB 支援。
- MyISAM 不支援資料庫異常崩潰後的安全恢複；而 InnoDB 支援，資料庫重新啟動的時候會保證資料庫恢複到崩潰前的狀态
- MyISAM 不支援MVCC；而 InnoDB 支援。
2. 索引檔案：
在安裝目錄的data檔案夾可以看到每個庫的表檔案
MySQL不同版本檔案有些差別，MySQL8之後，沒有.frm
2.1. MyISAM的索引檔案：
MyISAM存儲引擎表相關檔案有三個：
- .frm是存儲表結構的
- .MYD是存儲表資料的（D代表Date）
- .MYI是存儲索引的（I代表Index）
2.2. InnoDB的索引檔案：
- .frm是存儲表結構的
- .ibd存儲資料和索引
3. 索引類型：
3.1. 主鍵索引(Primary Key)：
- 資料表的主鍵列使用的就是主鍵索引。
- 在 MySQL 的 InnoDB 的表中，當沒有顯示的指定表的主鍵時，InnoDB 會自動先檢查表中是否有唯一索引的字段，如果有，則選擇該字段為預設的主鍵，否則 InnoDB 将會自動建立一個 6Byte 的自增主鍵。
- InnoDB中，主鍵索引的葉子節點存儲的是完整資料
- MyISAM中，主鍵索引的葉子節點存儲的是資料位址
3.2. 二級索引(輔助索引)：
- 唯一索引，普通索引，字首索引等索引屬于二級索引。
- InnoDB中，輔助索引的葉子節點存儲的是主鍵
- MyISAM中，輔助索引的葉子節點存儲的是資料位址
4. 索引資料結構：
MySQL中，無論是MyISAM還是InnoDB，其索引資料結構都是B+樹（不懂B+樹的可以參考這篇文章）
4.1. MyISAM索引實作：
MyISAM引擎使用B+Tree作為索引結構，葉節點的data域存放的是資料記錄的位址。下圖是MyISAM主鍵索引的原理圖：
- 假設要查找Col1為20的資料，由主鍵索引找到20的葉子節點，該節點的date存儲資料的位址（對應.MYD的資料）
- 根據該位址就能去.MYD檔案查詢到全部資料
下圖是MyISAM輔助索引的原理圖，在查找資料上和主鍵索引是一樣的
4.2. InnoDB 索引實作：
4.2.1. InnoDB 主鍵索引實作：
下圖是InnoDB 主鍵索引的原理圖，葉子節點的key是主鍵，date存儲了完整的表字段資料
比如查找主鍵20的資料，根據主鍵能找到葉子節點，該葉子節點存儲了完整資料，是以就能查到所有字段資料
4.2.2. InnoDB 輔助索引實作：
下圖是InnoDB 輔助索引的原理圖，葉子節點的key是索引鍵，date存儲的是主鍵。
- 比如查找Eric的資料，根據輔助索引找到Eric的葉子節點
- 該節點存儲了主鍵，想要獲得其他字段資料，就要去主鍵再檢索一邊才能擷取所有字段資料（也就是回表查詢了）
4.2.3. InnoDB 聯合索引實作：
聯合索引和輔助索引一樣，葉子節點存儲的是主鍵，但其非葉子節點的Key由所有索引列構成。
- 比如有一個聯合索引（b,c,d）,現在要select * from table where b =3 and c=4 and d=5
- 會先根據b=3（如果有b相同再根據c、d）,從第一層非葉子節點查找，獲得第二層非葉子節點位址，每從磁盤加載節點資料，就進行一次IO
- 在第二層非葉子節點，依次比對c=4，d=5，查找到符合的葉子節點位址，又進行了一次IO
- 從葉子節點中擷取存儲的主鍵，根據主鍵去主鍵索引在檢索一遍，擷取select * 所有字段資料
5. 聚簇索引和非聚簇索引：
InnoDB存儲引擎既有聚簇索引，也有非聚簇索引（需要回表的時候）
·
MyISAM存儲引擎隻有非聚簇索引，沒有聚簇索引
5.1. 非聚簇索引：
資料跟索引存儲在一起（InnoDB存儲引擎的.ibd檔案），像InnoDB的主鍵索引就是聚簇索引
5.2. 非聚簇索引：
資料跟索引不存儲在一起（MyISAM存儲引擎的.MYD和.MYI檔案）
6. 關于InnoDB索引小結：
- 非葉子節點會存儲下一節點磁盤(指針)位址
- 通過二分查找從非葉子節點查找到葉子節點，看索引類型，決定是否需要回表
- 主鍵索引的葉子節點存儲完整字段資料；輔助索引葉子節點存儲主鍵，需要在去主鍵檢索一遍才能擷取所有字段資料（回表查詢）
【參考】：
MySQL索引及其資料結構
聯合索引在B+Tree上的存儲結構及資料查找方式