5.MySQL 基礎結構

LLSS發表於2021-04-10
MySql

MySQL簡單結構

1、底層結構的簡單理解

如下圖所示：
5.MySQL 基礎結構

MySQL = 客戶端 + 服務端
客戶端 = Connection(語言聯結器例如 ：PHP-pdo , Mysli)
服務端 = SQL層 + 儲存引擎
SQL層 = 連結/執行緒處理 + 查詢快取 + 解析器 + 優化器
儲存引擎 = InnoDb + MariaDB +….

Connection：這一塊其實主要是其他語言的連線，並不屬於MySQL本身；主要是其他語言對於MySQL的連線操作的工具比如PHP中的：pdo，mysqli或者Navicat for MySQL
SQL層：功能主要包括許可權判斷，SQL解析功能和查詢優化功能等。

  1. 連結/執行緒處理：主要用來處理客戶端的請求、身份驗證和資料安全性的驗證等。
  2. 查詢快取和查詢分析器是SQL層的核心部分，其中主要涉及查詢的解析、優化、快取、以及所有內建的函式，儲存過程，觸發器，檢視等
  3. 優化器：主要負責儲存和獲取所有儲存在MySQL中的資料

2 MySQL物理檔案型別

日誌檔案包括：

  1. 錯誤日誌（Error log）
  2. 二進位制日誌（Binary Log）
  3. 事務日誌（InnoDB redo Log & undo Log）
  4. 慢查詢日誌（Slow Query Log）
  5. 查詢日誌（Query Log）

資料庫檔案：

  1. ‘.frm’檔案
  2. ‘.MYD’檔案
  3. ‘.MYI’檔案
  4. ‘.ibd’檔案和’.ibdata’檔案

其他配置檔案

  1. 系統配置檔案（my.ini或者my.cnf）
  2. pid,err檔案
  3. socket檔案

2.1日誌檔案

2.1.1 錯誤日誌

在資料庫文中，錯誤日誌功能是預設開啟的。預設情況下，錯誤日誌儲存在mysql 資料庫的資料目錄中。錯誤日誌檔案通常的名稱為 hostname.err ，hostname也就是主機名。
錯誤日誌資訊可以自己進行配置的，錯誤日誌所記錄的資訊是可以通過 log_error 和 log_warnings 來定義的，其中log-error是定義是否啟用錯誤日誌的功能和錯誤日誌的儲存位置，log-warnings是定義是否將警告資訊也定義至錯誤日誌中。
預設情況下，錯誤日誌主要記錄一下資訊

  1. 伺服器啟動和關閉過程中的資訊（未必是錯誤資訊，如mysql如何啟動InnoDB的表空間檔案的、如何初始化自己的儲存引擎的等等）
  2. 伺服器執行過程中的錯誤資訊、時間排程器執行一個事件時產生的資訊、在伺服器上啟動程式時產生的資訊 
    show global variables like '%log_error%';

5.MySQL 基礎結構

2.1.2 二進位制日誌

二進位制日誌，也就是我們常說的binlog。二進位制日誌記錄了MySQL所有修改資料庫的操作，然後以二進位制的形式記錄日誌在日誌檔案中，其中還包括每個調語句所執行的時間和消耗的資源，以及相關的事務資訊。
預設情況下二進位制日誌功能是沒有開啟的，啟動可以配置log-bin[=file_name]開啟，

5.MySQL 基礎結構
作用：

  1. 以二進位制形式記錄更改資料庫的SQL語句（insert，update，delete，create，drop，alter等）。
  2. 用於MySQL主從複製。
  3. 增量資料備份及恢復。

設定二進位制日誌是在在my.cnf或者my.ini 中新增配置 

server-id=1
log_bin=mysql-bin

啟用該選項資料庫效能降低1%，但保障資料庫完整性，對於重要資料庫值得以效能換完整。有些類似於oracle開啟歸檔模式。
命令

-- 檢視所有二進位制檔案資訊
show binary logs;
-- 檢視最新二進位制檔案
show master status;
-- 重新整理日誌
flush logs;

-- 檢視二進位制日誌資訊
語法格式：SHOW BINLOG EVENTS[IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]

-- show binlog events用於在二進位制日誌中顯示事件。如果未指定'log_name'，則顯示第一個二進位制日誌。
help show binlog events;  --獲取幫助資訊
show binlog events\G;
show  binlog events in 'mysql-bin.000014'\G;

命令列查：
5.MySQL 基礎結構

官網二進位制檔案恢復資料 dev.mysql.com/doc/refman/5.7/en/po...

2.1.3. 事務日誌(InnoDB)

檢視儲存引擎：show engines;

InnoDB引擎線上Redo日誌記錄了InnoDB所做的所有物理變更和事務資訊。通過Redo日誌和Undo資訊，InnoDB大大地加強了事務的安全性。InnDB線上Redo日誌預設存放在data命令下，可通過設定innodb_log_griyo_home_dir選項來更改日誌檔案存放位置，通過innodb_log_files_in_group選項來說何止日誌的數量

使用事務日誌，儲存引擎在修改表的資料時只需要修改其記憶體拷貝，再把修改行為記錄到持久在硬碟上的事務日誌中，而不用每次都將修改的資料本身持久到磁碟。事務日誌採用追加的方式，因此寫日誌的操作是磁碟上一小塊區域內的順序I/O，而不像隨機I/O需要在磁碟的多個地方移動磁頭，所以採用事務日誌的方式相對來說要快得多。事務日誌持久以後，記憶體中被修改的資料在後臺可以慢慢的刷回到磁碟。目前大多數的儲存引擎都是這樣實現的。

2.1.4 慢查詢日誌

顧名思義，慢查詢日誌中記錄的是執行時間較長的query，也就是我們常說的slowquery。

專業一點：慢查詢日誌是值所有SQL執行的實際超過long_query_time變數的語句和達到min_examined_row_limit條舉例的語句。使用者可以針對這部分語句效能調優。慢查詢日誌通過設定log-slow_queries[=file_name]選項開啟後，將記錄日誌所在的路勁和名稱。MySQL系統預設的慢查詢日誌的檔名是show.log，預設目錄是data目錄。
命令：

-- 檢視慢查詢是否開啟
show variables like "log_slow_queries";
-- 檢視慢查詢日誌的定義：
show global variables like  '%slow_query_log%';
-- 檢視long_query_time設定時間
show global variables like '%long%';
-- 檢視具體資訊
-- slow_query_log： off關閉狀態  on開啟狀態
-- slow_query_log_file  慢查詢日誌存放地點
show variables like "%slow%" ;

開啟慢查詢：

slow_query_log
slow_query_log_file=[file_name] 檔案地址
long_query_time=2 -- 最大等待時間

2.2 資料檔案

MySQL 資料庫會在data 目錄下建立一個以資料庫為名的檔案來儲存資料庫中的表資料。不同的資料引擎，每個表的副檔名也不一樣，例如 MyISAM 用 “.MYD”作為副檔名，Innodb 用“.ibd”，Archive 用“.arc”，CSV 用“.csv

2.2.1 “.frm”檔案

無論是那種儲存引擎，建立表之後就一定會生成一個以表明命名的’.frm’檔案。frm檔案主要存放與表相關的資料資訊，主要包括表結構的定義資訊。當資料庫崩潰時，使用者可以通過frm檔案來恢復資料表結構。

2.2.2 “.MYD”

“.MYD”檔案是MyISAM儲存引擎專用，存放MyISAM表的資料。每一個MyISAM表都會有一個“.MYD”檔案與之對應，同樣存放於所屬資料庫的資料夾下，和“.frm”檔案在一起。

2.2.3 “.MYI”

“.MYI”檔案也是專屬於MyISAM儲存引擎的，主要存放MyISAM表的索引相關資訊。對於MyISAM儲存來說，可以被cache 的內容主要就是來源於“.MYI”檔案中。每一個MyISAM表對應一個“.MYI”檔案，存放於位置和“.frm”以及“.MYD”一樣。

2.2.4 “.ibd”檔案和ibdata檔案

這兩種檔案都是存放Innodb資料的檔案，之所以有兩種檔案來存放Innodb的資料（包括索引），是因為Innodb的資料儲存方式能夠通過配置來決定是使用共享表空間存放儲存資料，還是獨享表空間存放儲存資料。獨享表空間儲存方式使用“.ibd”檔案來存放資料，且每個表一個“.ibd”檔案，檔案存放在和MyISAM資料相同的位置。如果選用共享儲存表空間來存放資料，則會使用ibdata檔案來存放，所有表共同使用一個（或者多個，可自行配置）ibdata檔案。
ibdata檔案可以通過innodb_data_home_dir（資料存放目錄）和innodb_data_file_path(配置每個檔案的名稱)兩個引數配置組成

innodb_data_file_path中可以一次配置多個ibdata檔案

#innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
配置方式

共享表空間以及獨佔表空間都是針對資料的儲存方式而言的。
共享表空間: 某一個資料庫的所有的表資料，索引檔案全部放在一個檔案中。
獨佔表空間: 每一個表都將會生成以獨立的檔案方式來進行儲存，每一個表都有一個.frm表描述檔案，還有一個.ibd檔案。其中這個檔案包括了單獨一個表的資料內容以及索引內容。
兩者對比
（1.）共享表空間：
優點：

可以放表空間分成多個檔案存放到各個磁碟上。資料和檔案放在一起方便管理。
缺點：

所有的資料和索引存放到一個檔案中，多個表及索引在表空間中混合儲存，這樣對於一個表做了大量刪除操作後表空間中將會有大量的空隙，特別是對於統計分析，日值系統這類應用最不適合用共享表空間。
（2.）獨立表空間：
優點:

  1. 每個表都有自已獨立的表空間。
  2. 每個表的資料和索引都會存在自已的表空間中。
  3. 可以實現單表在不同的資料庫中移動。
  4. 空間可以回收

    a) Drop table操作自動回收表空間，如果對於統計分析或是日值表，刪除大量資料後可以通過:altertable TableName engine=innodb;回縮不用的空間。

    b) 對於使用獨立表空間的表，不管怎麼刪除，表空間的碎片不會太嚴重的影響效能，而且還有機會處理。

缺點：單表增加過大，如超過100個G。

相比較之下，使用獨佔表空間的效率以及效能會更高一點

共享表空間和獨立表空間之間的轉換

show variables like “innodb_file_per_table”; ON代表獨立表空間管理，OFF代表共享表空間管理；

修改資料庫的表空間管理方式

修改innodb_file_per_table的引數值即可，但是修改不能影響之前已經使用過的共享表空間和獨立表空間；

innodb_file_per_table=1 為使用獨佔表空間

innodb_file_per_table=0 為使用共享表空間

2.3 其他檔案

  1. 系統核心配置檔案
    位置：linux/mac : etc/my.cnf;windows : mysql/my.ini
    MySQL 的系統配置檔案一般都是my.cnf，預設存放在”/etc”目錄下，my.cnf檔案中包含多種引數選項組（group），每一種引數組都通過中括號給定了固定的組名，如“[mysqld]”組中包括了mysqld服務啟動時候的初始化引數，“[client]”組中包含著客戶端工具程式可以讀取的引數。

  2. socket file
    MySQL伺服器啟動後socket檔案會自動生成，該檔案主要用來連線客戶端
    在有些時候連線MySQL會出現如下的問題

5.MySQL 基礎結構
通常的解決辦法，可以嘗試重啟一下MySQL的伺服器

3.MySQL 執行流程

5.MySQL 基礎結構

MySQL結構圖：

5.MySQL 基礎結構

執行流程分析：

3.1.1 啟動

  1. 通過命令net start mysql(windows) / service mysql start(linux)啟動MySQL服務
  2. 呼叫初始模組；初始化模組就是在資料庫啟動的時候，對整個資料庫做的一些初始化操作，比如各種系統環境變數的初始化，各種快取，儲存引擎初始化設定等。

核心api：MySQL資料庫核心api主要實現了資料庫底層操作的優化功能，其中主要包括IO操作、格式化輸出、高效能儲存資料結果演算法的優化，字串的處理，其中最重要的是記憶體管理。

3.1.2 連線

  1. 使用者傳送一條SQL，這個時候會被網路互動模組監聽到使用者的操作請求，傳遞給‘連線管理模組’
  2. 接收到請求轉發到‘進/執行緒連線模組’
  3. 呼叫‘使用者模組’來進行許可權檢測（訪問資料庫的許可權）
  4. 通過檢測之後就會去‘連線進/執行緒模組’從‘執行緒連線池’中查詢空閒的被快取的連線執行緒和客戶端請求對接，如果失敗則建立一個新的連線請求
  5. 返回連線執行緒

網路互動模組：對外提供可以接收和傳送資料的api介面，其他模組需要互動的時候，可以通過api介面呼叫

連線管理模組、進/執行緒連線模組、執行緒連線池：連線管理模組負責監聽MySQL Server的各種請求，根據不同的請求，然後轉發到執行緒管理模組，每個客戶請求都會被資料庫自動分配一個獨立的執行緒為其單獨服務，而連線執行緒的主要工作就是負責MySQL Server與客戶端通訊，執行緒管理模組負責管理這些生成的執行緒。

3.1.3 處理

  1. 在使用者許可權校驗成功之後，並且獲得新的連線池之後就會去‘命令分發器’，判斷命令的型別如果是select就會去訪問‘查詢快取’，如果沒有就會往下執行；
  2. 如果是select，並且開啟’查詢快取’之後就會去快取中查詢是否有與之相匹配的SQL,如果有就會校驗使用者訪問該資料的許可權,通過就返回不通過就會返回錯誤資訊. 如果資料沒有就會往下執行
  3. 會記錄過程中的SQL操作過程到日誌檔案中
  4. 在第8,9步 沒有滿足相應條件之後往下執行進入 ‘命令解析器’,經過詞法分析,語法分析後生成解析樹
  5. 根據操作轉到對應的模組處理(預處理階段)，根據SQL選擇執行的模組
  6. 模組收到請求後，通過’訪問控制模組’檢查所連線的使用者是否有訪問目標表和目標欄位的許可權（是指訪問這些資料的許可權）
  7. 有許可權’表管理模組’先檢視table cache中是否存在，有則直接對應的表和獲取鎖，負責重新開啟表檔案
  8. 根據表的ENGINE資料，獲取表的儲存引擎型別等資訊
  9. 通過介面呼叫對應的儲存引擎處理
  10. 返回查詢之後資料內容

使用者模組：主要功能是用於控制使用者登入連線的許可權和使用者授權管理。

訪問控制模組：主要用於監控使用者的每一個操作。訪問控制模組實現的功能就是根據使用者模組中不同的使用者授權，以及根據其資料庫的各種約束來控制使用者對資料的訪問。使用者模組和訪問控制模組結合起來，就組成了MySQL資料庫的許可權管理功能。

查詢優化器：這個模組主要是講客戶端傳送的查詢請求，在之前演算法的基礎上分析，計算出一個最優的查詢策略，優化之後會提高查詢訪問的速度，最後根據其最優策略返回查詢語句。

表變更管理模組：主要負責完成DML和DDl的查詢，列如，insert，update，delete，create table，alter table等語句處理。

表維護模組：主要用於檢測表的狀態，分析，優化表結構，以及修復表。

複製模組：複製模組分為Master模組和Slave模組兩部分。Master模組主要負責複製環境中讀取Master端的binary日誌，以及Slave端的I/O執行緒互動等工作。

狀態模組：在客戶端請求系統狀態的時候，系統狀態模組主要負責將各種狀態的資料返回給使用者。最常用的一些查詢狀態的命令包括show status，show variable是等，都是通過這個模組負責返回的。

表管理模組：主要就是維護系統生成的表檔案。列如MyISAM儲存引擎就生成frm，myd，myi檔案，維護這些檔案，江哥哥表結構的資訊快取起來，另外該模組還管理表級別的鎖。

儲存引擎介面模組：MySQL實現了其資料庫底層儲存引擎的外掛師管理，將各種資料處理高度抽象畫。

3.1.4 結果

  1. 命令執行完了之後，將結果集返回給’理解進/執行緒模組’(返回的也可以是相應標識，成功失敗)
  2. ‘理解進/執行緒模組’進行後續的清理工作，並繼續等待請求或斷開與客戶端的連線
本作品採用《CC 協議》，轉載必須註明作者和本文連結

相關文章