閱讀目錄

• Myisam引擎(非聚集索引)
• Innodb引擎(聚集索引)
• 磁碟數據頁的存儲結構
• 主鍵索引頁存儲結構

什麼是索引：

　　索引是一種高效獲取數據的存儲結構，例：hash、 二叉、 紅黑。

　　Mysql為什麼不用上面三種數據結構而採用B+Tree:

　　　　若僅僅是 select * from table where id=45 , 上面三種演算法可以輕易實現，但若是select * from table where id<6 , 就不好使了，它們的查找方式就類似於”全表掃描”，因為他們的高度是不可控的(如下圖)。B+Tree的高度是可控的，mysql通常是3到5層。注意：B+Tree只在最末端葉子節點存數據，葉子節點是以鏈表的形式互相指向的。

B+Tree的特性

　　(1)由圖能看出，單節點能存儲更多數據，使得磁碟IO次數更少。

　　(2)葉子節點形成有序鏈表，便於執行範圍操作。

　　(3)聚集索引中，葉子節點的data直接包含數據；非聚集索引中，葉子節點存儲數據地址的指針。

回到頂部

Myisam引擎(非聚集索引)

　　若以這個引擎創建資料庫表Create table user （…..），它實際是生成三個文件：

　　user.myi 索引文件 user.myd數據文件 user.frm數據結構類型。

　　如下圖：當我們執行 select * from user where id = 1的時候，它的執行流程。

　　　　(1)查看該表的myi文件有沒有以id為索引的索引樹。

　　　　(2)根據這個id索引找到葉子節點的id值，從而得到它裡面的數據地址。(葉子節點存的是索引和數據地址)。

　　　　(3)根據數據地址去myd文件裡面找到對應的數據返回出來。

回到頂部

Innodb引擎(聚集索引)

　　若以這個引擎創建資料庫表Create table user （…..），它實際是生成兩個文件：

　　user.ibd 表索引和數據文件 user.frm 表結構類型

　　因為innodb引擎創建表默認就是以主鍵為索引，所以不需要myi文件。

　　下圖為innodb表的結構圖：很顯然它與myisam最大的區別是將整條數據存在葉子節點，而不是地址。(葉子節點存的是主鍵索引和數據信息)

　　若此時，你在其他列創建索引例如name，它就會另外創建一個以name為索引的索引樹，(葉子節點存的是索引和主鍵索引)。

　　你在執行select * from user where name = 『吳磊』，他的執行過程如下：

　　　　(1)找到name索引樹

　　　　(2)根據name的值找到該樹下葉子的name索引和主鍵值

　　　　(3)用主鍵值去主鍵索引樹去葉子節點到該條數據信息

MyISAM引擎和InnoDB引擎的區別
　　MyISAM：支持全文索引;不支持事務;它是表級鎖;會保存表的具體行數.
　　InnoDB：5.6以後才有全文索引;支持事務；它是星級鎖;不會保存表的具體行數.
　　一般：不用事務的時候,count計算多的時候適合myisam引擎。對可靠性要求高就是用innodby引擎。推薦用InnoDB引擎.

　　加了索引之後能夠大幅度地提高查詢速度，但是索引也不是越多越好，一方面它會佔用存儲空間，另一方面它會使得寫操作變得很慢。通常我們對查詢次數比較頻繁，值比較多的列才建索引。
　　例如：select * from user where sex = “女”， 這個就不需要建立索引，因為性別一共就兩個值，查詢本身就是比較快的。
　　　　　select * from user where user_id = 1995 ,這個就需要建立索引，因為user_id的值是非常多的。

回到頂部

磁碟數據頁的存儲結構

　　磁碟中的數據頁是按順序一頁一頁存放的，然後兩兩相鄰的數據頁之間會採用雙向鏈表的格式互相引用。每一行數據都會按照主鍵大小進行排序存儲，同時每一行數據都有指針指向下一行數據的位置，組成單向鏈表。剛開始第一行是個起始行，他的行類型是2，就是最小的一行，然後他有一個指針指向了下一行數據，每一行數據都有自己每個欄位的值，然後每一行通過一個指針不停地指向下一行數據，普通的數據行的類型都是0，最後一行是一個類型為3的，就是代表最大的一行。

　　我們剛說了，數據頁中的數據行一定是按照主鍵大小正序排列的。下一頁的主鍵也一定會比上一頁的大。如果我們是自增還好，若是自己生成的則會出現順序錯亂情況。mysql則會通過頁分裂的機制將數據挪動到上一個數據頁，保證下一個數據頁里的主鍵值都比上一個數據頁里的主鍵值要大。

回到頂部

主鍵索引頁存儲結構

　　主鍵索引的目錄結構，只要在一個主鍵索引里包含每個數據頁跟他最小主鍵值，就可以組成一個索引目錄。然後後續你查詢主鍵值，就可以在目錄里二分查找直接定位到那條數據所屬的數據頁，接著到數據頁里二分查找定位那條數據就可以了。

　　現在問題來了，你的表裡的數據可能很多很多，比如有幾百萬，幾千萬，甚至單表幾億條數據都是有可能的，所以此時你可能有大量的數據頁，然後你的主鍵目錄里就要存儲大量的數據頁和最小主鍵值，這怎麼行呢？所以在考慮這個問題的時候，實際上是採取了一種把索引數據存儲在數據頁里的方式來做的。也就是我們上面提到的b+tree結構。

　　比如我現在要找id=45的數據，就會先從35頁找到23頁，再找到6頁，最後找到第4頁。然後它就指向了葉子節點(數據頁)。

　　若是你基於非主鍵欄位name 建立了一個索引，那麼此時你插入數據的時候，就會重新搞一顆B+樹，B+樹的葉子節點也是數據頁，但是這個數據頁里僅僅放主鍵欄位和name欄位、數據行按name大小排列，就不是具體數據了。先找到name對應的主鍵地址，再去主鍵索引樹找到具體數據信息。這種行為叫做回表查詢(若select的所有欄位都是索引中的欄位則直接就返回了，不用去主鍵索引樹中找了。這種行為叫做覆蓋索引)