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雖然沒有行級鎖，不過是可以支持表鎖的，所以在一些業務系統還是可以做到并發控制的

• 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