概述

今天主要講下mysql數據庫引擎的一些概念和mysql數據庫本質，一句話總結：

文件夾-文件：一個數據庫其實就是一個的文件夾，數據庫里面的表就是文件夾里的一個或者多個文件（根據數據庫引擎不同而不同，MyISAM是3個，InnoDB是2.5個）

mysql的數據庫其實就是存放在MySQLdata下的一個個的文件夾

數據庫里面的表就是文件夾里的一個或者多個文件（根據數據庫引擎不同而不同）

一、為什么要合理選擇數據庫存儲引擎？

在Oracle 和SQL Server等數據庫中只有一種存儲引擎，所有數據存儲管理機制都是一樣的。而MySql數據庫提供了多種存儲引擎。

MySQL中的數據用各種不同的技術存儲在文件（或者內存）中。這些技術中的每一種技術都使用不同的存儲機制、索引技巧、鎖定水平并且最終提供廣泛的不同的功能和能力。通過選擇不同的技術，你能夠獲得額外的速度或者功能，從而改善你的應用的整體功能。

這些不同的技術以及配套的相關功能在MySQL中被稱作存儲引擎(也稱作表類型)。MySQL默認配置了許多不同的存儲引擎，可以預先設置或者在MySQL服務器中啟用。你可以選擇適用于服務器、數據庫和表格的存儲引擎，以便在選擇如何存儲你的信息、如何檢索這些信息以及你需要你的數據結合什么性能和功能的時候為你提供最大的靈活性。

二、myisam表引擎存儲

1、myisam的存儲結構

每一個表都有3個文件，都位于數據庫目錄中.

tb_name.frm 表結構定義
tb_name.MYD 表數據
tb_name.MYI 表索引

2、myisam索引結構

MyISAM引擎使用B+Tree作為索引結構，葉節點的data域存放的是數據記錄的地址。下圖是MyISAM索引的原理圖：

這里設表一共有三列，假設我們以Col1為主鍵，則上圖是一個MyISAM表的主索引（Primary key）示意。可以看出MyISAM的索引文件僅僅保存數據記錄的地址。在MyISAM中，主索引和輔助索引（Secondary key）在結構上沒有任何區別，只是主索引要求key是唯一的，而輔助索引的key可以重復。如果我們在Col2上建立一個輔助索引，則此索引的結構如下圖所示：

同樣也是一顆B+Tree，data域保存數據記錄的地址。因此，MyISAM中索引檢索的算法為首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，則取出其data域的值，然后以data域的值為地址，讀取相應數據記錄。

MyISAM的索引方式也叫做“非聚集”的，之所以這么稱呼是為了與InnoDB的聚集索引區分。

三、innodb表引擎存儲（有數據的共享區域，myisam沒有）

1、InnoDB的存儲結構

InnoDB使用頁面存儲結構，下面是InnoDB的表空間結構圖：

Innodb表：有2種存儲方式：

1 默認，每一個表有一個獨立的表結構定義文件 和 一個多表數據+索引共享文件

tb_name.frm 表結構 位于指定數據庫中

ibdata# 共享表空間 位于數據庫目錄中datadir

2 使用獨立的表空間 每一個表有一個獨立的表結構文件和一個獨立的表空間文件

tb_name.frm 表結構 位于指定數據庫中

tb_name.ibd 表數據 和 索引文件

Page頁面存儲格式如下圖所示：

一個頁面的存儲由以下幾部分組成：

1. 頁頭（Page Header）：記錄頁面的控制信息，共占150字節，包括頁的左右兄弟頁面指針、頁面空間使用情況等，頁頭的詳細說明會在下一篇中描述。
2. 最小虛記錄、最大虛記錄：兩個固定位置存儲的虛記錄，本身并不存儲數據。最小虛記錄比任何記錄都小，而最大虛記錄比任何記錄都大。
3. 記錄堆（record heap）：指上圖的橙黃色部分。表示頁面已分配的記錄空間，也是索引數據的真正存儲區域。記錄堆分為兩種，即有效記錄和已刪除記錄。有效記錄就是索引正常使用的記錄，而已刪除記錄表示索引已經刪除，不在使用的記錄，如上圖的深藍色部分。隨著記錄的更新和刪除越來越頻繁，記錄堆中已刪除記錄將會越多，即會出現越來越多的空洞（碎片）。這些已刪除記錄連接起來，就會成為頁面的自由空間鏈表。
4. 未分配空間：指頁面未使用的存儲空間，隨著頁面不斷使用，未分配空間將會越來越小。當新插入一條記錄時，首先嘗試從自由空間鏈表中獲得合適的存儲位置（空間足夠），如果沒有滿足的，就會在未分配空間中申請。
5. slot區：slot是一些頁面有效記錄的指針，每個slot占兩個字節，存儲了記錄相對頁面首地址的偏移。如果頁面有n條有效記錄，那么slot的數量就在n/8+2~n/4+2之間。下一節詳細介紹slot區，它是記錄頁面有序和二分查找的關鍵。
6. 頁尾（Page Tailer）：頁面最后部分，占8個字節，主要存儲頁面的校驗信息。

頁面中的頁頭，最大/最小虛記錄以及頁尾都是頁面中有固定的存儲位置。

2、InnoDB的索引結構

InnoDB使用B+Tree的方式存儲索引。

Innodb的一個表可能包含多個索引，每個索引都使用B+樹來存儲。而索引包括聚集索引和二級索引，聚集索引使用表的主鍵作為索引鍵，包含表的所有字段。二級索引只包含索引鍵和聚集索引鍵（主鍵）的內容，不包括其他字段。每一個索引都是一棵B+樹，每棵B+樹由很多頁面組成，而每個頁面大小一般為16K。從B+樹的組織結構來看，B樹的頁面可分為：

葉子節點：B樹層次為0的頁面，存儲記錄的所有內容。

非葉子節點：B樹層次大于0的頁面，只存儲索引鍵和頁面指針。

雖然InnoDB也使用B+Tree作為索引結構，但具體實現方式卻與MyISAM截然不同。

第一個重大區別是InnoDB的數據文件本身就是索引文件。從上文知道，MyISAM索引文件和數據文件是分離的，索引文件僅保存數據記錄的地址。而在InnoDB中，表數據文件本身就是按B+Tree組織的一個索引結構，這棵樹的葉節點data域保存了完整的數據記錄。這個索引的key是數據表的主鍵，因此InnoDB表數據文件本身就是主索引。

可以看到葉節點包含了完整的數據記錄。這種索引叫做聚集索引。因為InnoDB的數據文件本身要按主鍵聚集，所以InnoDB要求表必須有主鍵（MyISAM可以沒有），如果沒有顯式指定，則MySQL系統會自動選擇一個可以唯一標識數據記錄的列作為主鍵，如果不存在這種列，則MySQL自動為InnoDB表生成一個隱含字段作為主鍵，這個字段長度為6個字節，類型為長整形。

第二個與MyISAM索引的不同是InnoDB的輔助索引data域存儲相應記錄主鍵的值而不是地址。換句話說，InnoDB的所有輔助索引都引用主鍵作為data域。例如，下圖為定義在Col3上的一個輔助索引：

這里以英文字符的ASCII碼作為比較準則。聚集索引這種實現方式使得按主鍵的搜索十分高效，但是輔助索引搜索需要檢索兩遍索引：首先檢索輔助索引獲得主鍵，然后用主鍵到主索引中檢索獲得記錄。

了解不同存儲引擎的索引實現方式對于正確使用和優化索引都非常有幫助，例如知道了InnoDB的索引實現后，就很容易明白為什么不建議使用過長的字段作為主鍵，因為所有輔助索引都引用主索引，過長的主索引會令輔助索引變得過大。再例如，用非單調的字段作為主鍵在InnoDB中不是個好主意，因為InnoDB數據文件本身是一顆B+Tree，非單調的主鍵會造成在插入新記錄時數據文件為了維持B+Tree的特性而頻繁的分裂調整，十分低效，而使用自增字段作為主鍵則是一個很好的選擇。