一，MySQL基本架構

MySQL基礎架構可以分為兩大類：Server層和存儲引擎層。

Server層： Server層涵蓋了MySQL大部分核心業務功能，并且所有存儲引擎的功能都在這一層實現。

存儲引擎層：存儲引擎有很多，各自有著各自的特點，可以根據場景來選擇不同的存儲引擎來操作數據

連接器：負責跟客戶端建立連接，獲取權限，維持和管理連接。包括用戶名和密碼的驗證，查詢權限信息，分配對應的權限，可以使用show processlist查看現在的連接，如果太長時間沒有動靜，就會自動斷開，通過wait_timeout控制，默認8小時。

分析器：詞法分析：MySQL需要把輸入的字符串進行識別，每個部分代表什么意思，比如，把字符串 T 識別成表名 T，把字符串 ID 識別成列 ID。語法分析：根據語法規則判斷這個sql語句是否滿足MySQL的語法，如果不符合就會報錯“You have an error in your SQL synta”。

優化器：在具體執行SQL語句之前，要先經過優化器的處理。當表中有多個索引的時候，決定用哪個索引，當sql語句需要做多表關聯的時候，決定表的連接順序等等。

執行器：通過分析器知道了做什么，通過優化器知道了怎么做，這就遇到了一個問題，誰來做？可想而知就是執行器開始執行SQL語句。開始執行的時候，要先判斷一下你對表是否有執行查詢的權限，如果沒有就會報出錯誤的提示信息。如果有權限，就打開表繼續執行。執行器會根據表的引擎來調用提供的引擎接口，開始執行。

二，存儲引擎

1，什么是存儲引擎

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

例如，如果你在研究大量的臨時數據，你也許需要使用內存MySQL存儲引擎。內存存儲引擎能夠在內存中存儲所有的表格數據。又或者，你也許需要一個支持事務處理的數據庫(以確保事務處理不成功時數據的回退能力)。

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

2，查看MySQL的存儲引擎

我們可以用SHOW ENGINES; 來查詢數據庫的存儲引擎：

Support列的值表示某種引擎是否能使用：YES表示可以使用、NO表示不能使用、DEFAULT表示該引擎為當前默認的存儲引擎。

3，MyISAM

每個MyISAM在磁盤上存儲成3個文件，其中文件名和表名都相同，但是擴展名分別為：

.frm文件存儲的是表結構

.MYD存儲數據

.MYI存儲索引

MyISAM引擎的索引結構為B+Tree，其中B+Tree的數據域存儲的內容為實際數據的地址，也就是說它的索引和實際的數據是分開的，只不過是用索引指向了實際的數據，這種索引就是所謂的非聚集索引。如下圖所示：

這里設表一共有兩列，假設我們以id為主鍵，則上圖是一個MyISAM表的主索引(Primary key)示意。可以看出MyISAM的索引文件僅僅保存數據記錄的地址。在MyISAM中，主索引和輔助索引(Secondary key)在結構上沒有任何區別，只是主索引要求key是唯一的，而輔助索引的key可以重復。

輔助索引可以通過輔助鍵直接找到數據地址，不用再訪問主鍵。

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

在設計之時就考慮到數據庫被查詢的次數要遠大于更新的次數。因此，ISAM執行讀取操作的速度很快，而且不占用大量的內存和存儲資源。由于數據索引和存儲數據分離，MyISAM引擎的索引結構是B+Tree，其中B+Tree的數據域存儲的內容為實際數據的地址，也就是所他的索引和實際數據是分開的。不過索引指向實際的數據，這種索引也就是非聚合索引。因此，MyISAM中索引檢索的算法為首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，則取出其data域的值，然后以data域的值為地址，讀取相應數據記錄。

但是它沒有提供對數據庫事務的支持，也不支持表級鎖，因此當INSERT(插入)或UPDATE(更新)數據時即寫操作需要鎖定整個表，效率便會低一些。不過和Innodb不同，MyIASM中存儲了表的行數，于是SELECT COUNT(*) FROM TABLE時只需要直接讀取已經保存好的值而不需要進行全表掃描。如果表的讀操作遠遠多于寫操作且不需要數據庫事務的支持，那么MyIASM也是很好的選擇。

4，InnoDB

每個InnoDB在磁盤上存儲成2個文件

.frm文件存儲的是表結構

.idb存儲的是數據和索引文件

InnoDB存儲引擎的索引也是使用B+Tree結構來存儲的，但是InnoDB的索引文件本身就是數據文件，即B+Tree的數據域存儲的就是實際數據，這種索引就是聚簇索引。這個索引的key就是數據表的主鍵，因此InnoDB表數據文件本身就是主索引。

并且和MyISAM不同，InnoDB的輔助索引數據域存儲的也是相應記錄主鍵的值而不是地址，所以當以輔助索引查找時，會先根據輔助索引找到主鍵，再根據主鍵索引找到實際的數據。所以Innodb不建議使用過長的主鍵，否則會使輔助索引變得過大。建議使用自增的字段作為主鍵，這樣B+Tree的每一個結點都會被順序地填滿，而不會頻繁的分裂調整，會有效的提升插入數據的效率。

注意：

InnoDB是通過B+Tree結構對主鍵創建索引，然后葉子節點中存儲記錄，如果沒有主鍵，那么會選擇唯一鍵，如果沒有唯一鍵，那么會生成一個6位的row_id來作為主鍵

如果創建索引的鍵是其他字段，那么在葉子節點中存儲的是該記錄的主鍵，然后再通過主鍵索引找到對應的記錄,叫做回表

Innodb的優點

針對事務的支持，事務(ACID)，行級鎖，外鍵，Java中使用事務處理，首先要求數據庫支持事務。如使用MySQL的事務功能，就要求MySQL的表類型為Innodb才支持事務

使用MVCC多版本控制器來控制事務。

5，MyISAM和InnoDB區別

InnoDB 支持事務，MyISAM 不支持，對于 InnoDB 每一條 SQL 語言都默認封裝成事務，自動提交，這樣會影響速度，所以最好把多條 SQL 語言放在 begin 和 commit 之間，組成一個事務；

InnoDB 支持外鍵，而 MyISAM 不支持。對一個包含外鍵的 InnoDB 表轉為 MYISAM會失敗；

InnoDB 是聚集索引，數據文件是和索引綁在一起的，必須要有主鍵，通過主鍵索引效率很高。但是輔助索引需要兩次查詢，先查詢到主鍵，然后再通過主鍵查詢到數據。因此，主鍵不應該過大，因為主鍵太大，其他索引也都會很大。而MyISAM 是非聚集索引，數據文件是分離的，索引保存的是數據文件的指針。主鍵索引和輔助索引是獨立的。

InnoDB 不保存表的具體行數，執行 select count(*) from table 時需要全表掃描。而MyISAM 用一個變量保存了整個表的行數，執行上述語句時只需要讀出該變量即可，速度很快；

Innodb 不支持全文索引，而 MyISAM 支持全文索引，查詢效率上 MyISAM 要高；(在MySQL5.7 版本中已經支持全文索引)

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

6，如何選擇

是否要支持事務，如果要請選擇 innodb，如果不需要可以考慮 MyISAM

如果表中絕大多數都只是讀查詢，可以考慮 MyISAM，如果既有讀寫也挺頻繁，請使用 InnoDB。

系統崩潰后，MyISAM 恢復起來更困難，能否接受；

MySQL5.5 版本開始 Innodb 已經成為 Mysql 的默認引擎(之前是 MyISAM)，說明其優勢是有目共睹的，如果你不知道用什么，那就用 InnoDB，至少不會差。

總結

以上是生活随笔為你收集整理的innodb和my查询速度_吃透MySQL：MyISAM和InnoDB存储引擎详细介绍的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。