MySQL系列(一)mysql架構設計及存儲引擎

mysql架構設計

1.連接配接層

最上層是一些用戶端和連接配接服務，包含本地sock通信和大多數基于用戶端/服務端工具實作的類似于tcp/ip的通信。主要完成一些類似于連接配接處理、授權認證、及相關的安全方案。在該層上引入了線程池的概念，為通過認證安全接入的用戶端提供線程。同樣在該層上可以實作基于SSL的安全連結。伺服器也會為安全接入的每個用戶端驗證它所具有的操作權限。

2.服務層

第二層架構主要完成大多數的核心服務功能，如SQL接口，并完成緩存的查詢，SQL的分析和優化及部分内置函數的執行。所有跨存儲引擎的功能也在這一層實作，如過程、函數等。在該層，伺服器會解析查詢并建立相應的内部解析樹，并對其完成相應的優化如确定查詢表的順序，是否利用索引等，最後生成相應的執行操作。如果是select語句，伺服器還會查詢内部的緩存。如果緩存空間足夠大，這樣在解決大量讀操作的環境中能夠很好的提升系統的性能。

3.引擎層

存儲引擎層，存儲引擎真正的負責了MySQL中資料的存儲和提取，伺服器通過API與存儲引擎進行通信。不同的存儲引擎具有的功能不同，這樣我們可以很據自己的實際需要進行選取，後面介紹MyISAM和InnoDB

4.存儲層

資料存儲層，主要是将資料存儲在運作于裸裝置的檔案系統之上，并完成與存儲引擎的互動

SQL的執行和加載過程

1.用戶端發送一條查詢給伺服器；

2.伺服器通過權限檢查之後，會先檢查查詢緩存，如果命中了緩存，則立即傳回存儲在緩存中的結果。否則進入下一階段；

3.伺服器端進行SQL解析、預處理，再有優化器根據該SQL所涉及到的資料表的統計資訊進行計算，生成對應的執行計劃；

4.MySQL根據優化器生成的執行計劃，調用存儲引擎的API來執行查詢；

5.将結果傳回給用戶端。

mysql常用存儲引擎

Mysql支援很多類型的存儲引擎，如MyISAM、InnoDB、Memory、Archive、Example等，主要介紹兩種MyISAM和InnoDB。這裡我們要注意資料的存儲引擎是可以設定到表層面的，也就是同一個庫不同的表是允許不用的存儲引擎的。

InnoDB存儲引擎

InnoDB存儲引擎從MySQL5.5版本開始是預設的存儲引擎（之前的版本InnoDB存儲引擎僅在Windows下是預設的存儲引擎），該存儲引擎是第一個完整支援ACID事務的MySQL存儲引擎，其特點是行鎖設計、支援MVCC、支援外鍵、提供一緻性非鎖定讀、同時被設計用來最有效的利用以及使用記憶體和CPU。

MyIsam存儲引擎

MyIsam 是mysql5.5之前預設的存儲引擎，缺點是 不支援事務，不支 持外鍵，優點：批量插入和查詢資料快，不支援事務安全或者主要以查詢，插入為主的業務表的建立，可以定義為myisam存儲引擎。

myisam表支援三種不同的存儲格式：靜态表（長度固定）、動态表、壓縮表。

靜态表(預設)： 靜态表中的字段都是非變長字段，這樣每個記錄都是固定長度的，這種存儲方式的優點是存儲非常迅速，容易緩存，出現故障容易恢複；缺點是占用空間通常比動态表多。

動态表: 動态表中包含變長字段，記錄長度不是固定的，存儲有點是占用空間較少，但是頻繁更新和删除記錄會産生碎片，需要定期執行optimize table 語句或 myisamchk-r指令來改善性能，在出現故障時恢複相對比較困難。

**壓縮表：**由 myisampack工具建立，占據非常小的磁盤空間。因為每個記錄是呗單獨壓縮的，是以隻有非常小的通路開支。

Innodb存儲引擎和MyIsam存儲引擎對比

對比項	MyIsam	InnoDB
外鍵	不支援	支援
事務	不支援	支援
行表鎖	表鎖，即使操作(寫資料)一條記錄也會鎖住整張表，不适合高并發的操作	行鎖，操作時(寫資料)隻鎖某一行，不對其他行有影響
緩存	隻緩存索引，不緩存真實資料	即緩存索引又緩存真實資料，對記憶體要求較高，而且記憶體大小對性能有決定性的影響
表空間	小	大
關注點	性能	事務
5.6版之前FULLTEXT類型的索引	支援	不支援

這裡我們要注意，Innodb存儲引擎的行鎖的前提是通過索引檢索，若不通過索引檢索也會是表鎖。(索引我們會在後面做具體介紹)

修改mysql存儲引擎：

alter table tablename type = 存儲引擎;

檢視某個庫下指定表使用的存儲引擎：

show table status from 庫名 where name=‘表名’；