MySQL系列之體系結構原理學習筆記

MySQL系列之體系結構原理學習筆記

參照來自官網的圖，MySQL Server架構從上到下依次為網路連線層(Connectors)、服務層（MySQL Server）、儲存引擎層(Plugable Storage Engines)、系統檔案層(File System)

中文圖示：

畫思維導圖：

一、網路連線層（Connectors）

• 使用者端聯結器：處理使用者端與mysql伺服器端的連線、授權認證、安全等，需要編寫api、使用者端驅動程式實現與伺服器端的連線
• 支援的程式語言：java、C、Python、.Net等等，基本支援所有主流的程式語言

二、服務層（MySQL Server）

這一層是MySQL的核心元件，主要包括系統管理和控制工具(Management Services&Utilities)、連線池（Connection Pool）、SQL介面（SQL Interface）、解析器（Parser）、查詢解析器（Optimizer）、快取（Cache&Buffer）

• 連線池（Connection Pool）

負責儲存和管理使用者端與資料庫的連線，一個現場管理一個連線

• 系統管理和控制工具(Management Services&Utilities)

系統管理和控制工具負責備份恢復、安全管理、叢集管理等

• SQL介面（SQL Interface）

用於接受使用者端傳送的各種SQL命令，並且返回使用者需要查詢的結
果。比如DML、DDL、儲存過程、檢視、觸發器等。

• 解析器（Parser）

負責將請求的SQL解析生成一個"解析樹"，然後根據一些MySQL規則進一步檢查解析樹是否合法

• 查詢解析器（Optimizer）

當「解析樹」通過解析器語法檢查後，再通過優化器將其轉化成執行計劃，然後與儲存引擎互動

• 快取（Cache&Buffer）

MySQL的快取機制由多部分組成，比如錶快取，記錄快取，許可權快取，引擎快取等，查詢語句一般先查快取，快取查不到才會進行處理，快取的目的是避免硬解析，提供系統效能

三、儲存引擎層（Plugable Storage Engines）

Mysql架構的儲存引擎是設計得很靈活的，是一種Plugable的，可以支援拓展的，比如阿里內部就自行進行拓展以適應高並行，詳情可以瞭解一下alisql

Mysql的事務回滾等處理是給儲存引擎負責的，下面介紹一下幾種常見的MySQL儲存引擎：

• InnoDB儲存引擎
  InnoDB儲存引擎是mysql預設的儲存引擎，也是使用最廣泛的儲存引擎，前面也說了，InnoDB是一種可以支援事務的儲存引擎，InnoDB是mysql 5.1才開始出現的，之前名稱是稱為InnoDB plugin，直到被oracle收購mysql後，才正式命名為InnoDB。InnoDB採用MVCC來支援事務一致性和並行，並且實現了4個基本的隔離級別，預設級別是PREATABLE READ(可重複讀)，並且通過間隙鎖(next-key loking)策略來防止歡讀的出現，

• MyISAM儲存引擎
  在MySQL5.1之前版本，MyISAM是預設的儲存引擎，MyISAM提供了全文索引、壓縮、空間函數等，但是MyISAM是不支援事務和行級鎖的，MyISAM會將表儲存在兩個檔案中，資料檔案和索引檔案，拓展名分別是.MYD和.MYI，MyISAM雖然沒有行級鎖，不過是可以支援表鎖的，所以在一些業務系統還是可以做到並行控制的

對比項	MyISAM	InnoDB
主外來鍵	不支援	支援
事務	不支援	支援
行表鎖	使用表鎖，操作一條記錄就會鎖住整張表，不適合做高並行操作	使用行鎖，操作記錄，只鎖一行記錄，適合做高並行處理
表空間	相對比較小	相對比較大

• Archive儲存引擎
  Archive儲存引擎只支援INSERT和SELECT操作，在Mysql5.1版本之前不支援索引的，Archive引擎會快取所有的寫資料，利用zlib對寫入的資料進行壓縮，所以需要的磁碟/IO比MyISAM更少的，但是SELECT是全表掃描的，因此Archive表適合用於紀錄檔和資料採集類的應用，Archive引擎是支援行級鎖的

• Blackhole儲存引擎
  Blackhole引擎沒有儲存機制，不做儲存處理，但是伺服器還是會記錄Blackhole表的紀錄檔，所以可以用於複製資料到備庫，或者用於記錄紀錄檔

• CSV儲存引擎
  CSV引擎可以將普通的CSV檔案作為MySql的表處理，可以在資料庫執行時拷貝檔案，將Excel表另存為CSV檔案，然後就可以直接複製到資料庫，不過這種表是不支援索引的

• Federated儲存引擎
  Federated引擎是存取其它伺服器的代理，會 創 建 一 個 到 遠 程 MySQL 服 務 器 的 客 戶 端 連 接， 並 將 查 詢 傳 輸 到 遠 程 服 務 器 執 行， 然 後 提 取 或 者 發 送 需 要 的 數 據。

• Memory儲存引擎
  Memory引擎適用與需要快速存取資料，這些資料是儲存在記憶體的，Memory支援hash索引，所以查詢速度非常快，不過不支援BLOB或者TXT型別資料，是用表鎖做並行的

• Merge儲存引擎
  Merge引擎是由多個MyISAM表合併而來的表，可以用於儲存紀錄檔或者用於當資料存庫

• NDB叢集儲存引擎
  這是Mysql推出的可以用於叢集的儲存引擎，提供了SQL和NDB協定之間的介面，原先是收購了NDB資料庫，然後發展來的

• 自定義儲存引擎
  Mysql的儲存引擎是支援可以自己開發和修改的，比如阿里的mysql就不是用預設InnoDB

修改儲存引擎SQL:

ALTER TABLE mytable ENGINE = InnoDB;

四、系統檔案層（File System）

系統檔案層負責將資料庫的資料和紀錄檔儲存在檔案系統，並完成與儲存引擎的互動，主要包含紀錄檔檔案，資料檔案，組態檔，pid 檔案，socket 檔案等

• 紀錄檔檔案
  mysql紀錄檔檔案
  

  • 錯誤紀錄檔（Error log）
    錯誤紀錄檔預設是開啟的，指令碼查詢：show variables like '%log_error%';
    

  • 通用查詢紀錄檔（General query log）
    記錄一般查詢語句，show variables like '%general%';

  • 二進位制紀錄檔（binary log）
    二進位制紀錄檔記錄了資料庫執行的更新操作，不記錄select、show等不修改資料的SQL，同時也有記錄執行時間、時長，所以這個特性可以用於資料恢復和主從複製

  查詢是否開啟二進位制紀錄檔：
  二進位制紀錄檔引數檢視：

  • 慢查詢紀錄檔（slow query log）
    慢查詢紀錄檔可以用於排查執行時間超時的sql，預設是10s，主要用於效能調優
    查詢是否開啟：show variables like '%slow_query%';， 查詢時長：show variables like '%long_query_time%';
    要開啟慢查詢紀錄檔，可以通過兩種方法：

  一種是修改mysql，my.ini組態檔：

  [mysqld]
  slow_query_log =1
  slow_query_log_file=/usr/local/mysql/data/localhost-slow.log

  另外一種是通過sql命令：

  // 開啟慢查詢紀錄檔
  mysql> SET GLOBAL slow_query_log=ON;
  Query OK, 0 rows affected (0.05 sec)
  //設定慢查詢超時時間
  mysql> SET GLOBAL long_query_time=3;
  Query OK, 0 rows affected (0.00 sec)

• 組態檔
  用於存放MySQL所有的設定資訊檔案，比如my.cnf、my.ini等

my.ini組態檔：

[mysql]
# 設定mysql使用者端預設字元集
default-character-set=utf8 
[mysqld]
#設定3306埠
port = 3306 
# 設定mysql的安裝目錄
basedir="D:\mysql-8.0.13-winx64"
# 設定mysql資料庫的資料的存放目錄
datadir="D:\mysql-8.0.13-winx64\data"
# 允許最大連線數
max_connections=200
# 伺服器端使用的字元集預設為8位元編碼的latin1字元集
character-set-server=utf8
# 建立新表時將使用的預設儲存引擎
default-storage-engine=INNODB
#log-bin=ON
binlog-format=ROW
log-bin=mysqlbinlog

• 資料檔案
  
  mysql資料檔案，用於儲存表資料等等，可以通過設定my.ini設定存放路徑：datadir="D:\mysql-8.0.13-winx64\data"

  • ​db.opt 檔案：記錄這個庫的預設使用的字元集和校驗規則
  • frm 檔案：儲存與表相關的後設資料（meta）資訊，包括表結構的定義資訊等
  • ​MYD 檔案：MyISAM 儲存引擎專用，存放 MyISAM 表的資料（data)
  • ​MYI 檔案 ：MyISAM 儲存引擎專用，存放 MyISAM 表的索引相關資訊
  • ​ibd檔案和 IBDATA 檔案：InnoDB儲存引擎專用，存放 InnoDB 的資料檔案（包括索引）

  新建一個資料庫之後，如果是 InnoDB引擎的，新建一張表就新建對應的ibd檔案，命名為：表名.ibd
  
  InnoDB 儲存引擎有兩種表空間方式：獨享表空間和共用表空間。獨享表空間使用 .ibd 檔案來存放資料，每一張 表對應一個 .ibd 檔案。共用表空間使用 .ibdata 檔案，所有表共同使用一個或多個.ibdata 檔案

  • ​ibdata1 檔案
    系統表空間資料檔案，儲存表後設資料、Undo紀錄檔等
    

  • ​ib_logfile0、ib_logfile1 檔案：Redo log 紀錄檔檔案
    

• pid檔案
  pid 檔案是 mysqld 應用程式在 Unix/Linux 環境下的一個程序檔案，用於儲存著自己的程序 id

• socket檔案
  socket 檔案也是在 Unix/Linux 環境下才有的檔案，使用者在 Unix/Linux 環境下使用者端連線可以不通過TCP/IP 網路而直接使用 Unix Socket 來連線 MySQL