Mysql底層十大基礎(chǔ)知識

相關(guān)基本概念及DDL語句在這里就不贅述了，本篇文章主要聊一聊mysql底層的東西。

一、架構(gòu)

mysql的架構(gòu)，主要分為server層和引擎層，大體分布如下：


mysql架構(gòu)

MySQL基架大致包括如下幾大模塊組件：

 （1）MySQL向外提供的交互接口（Connectors）

 （2）管理服務(wù)組件和工具組件(Management Service & Utilities)

 （3）連接池組件(Connection Pool)

 （4）SQL接口組件(SQL Interface)

 （5）查詢分析器組件(Parser)

 （6）優(yōu)化器組件（Optimizer）

 （7）緩存主件（Caches & Buffers）

 （8）插件式存儲引擎（Pluggable Storage Engines）

 （9）物理文件（File System）
mysql架構(gòu)

（一）MySQL向外提供的交互接口（Connectors）

Connectors組件，是MySQL向外提供的交互組件，如java,.net,php等語言可以通過該組件來操作SQL語句，實現(xiàn)與SQL的交互。

（二）管理服務(wù)組件和工具組件(Management Service & Utilities)

提供對MySQL的集成管理，如備份(Backup),恢復(fù)(Recovery),安全管理(Security)等

（三）連接池組件(Connection Pool)

負(fù)責(zé)監(jiān)聽對客戶端向MySQL Server端的各種請求，接收請求，轉(zhuǎn)發(fā)請求到目標(biāo)模塊。每個成功連接MySQL Server的客戶請求都會被創(chuàng)建或分配一個線程，該線程負(fù)責(zé)客戶端與MySQL Server端的通信，接收客戶端發(fā)送的命令，傳遞服務(wù)端的結(jié)果信息等。

（四）SQL接口組件(SQL Interface)

接收用戶SQL命令，如DML,DDL和存儲過程等，并將最終結(jié)果返回給用戶。

（五）查詢分析器組件(Parser)

首先分析SQL命令語法的合法性，并嘗試將SQL命令分解成數(shù)據(jù)結(jié)構(gòu)，若分解失敗，則提示SQL語句不合理。

（六）優(yōu)化器組件（Optimizer）

對SQL命令按照標(biāo)準(zhǔn)流程進行優(yōu)化分析。

（七）緩存主件（Caches & Buffers）

緩存和緩沖組件

（八）MySQL存儲引擎

（九）物理文件（File System）

實際存儲MySQL 數(shù)據(jù)庫文件和一些日志文件等的系統(tǒng)，如Linux，Unix,Windows等。

二、一個查詢的主要流程

查詢流程
1、連接器
  • 連接器負(fù)責(zé)跟客戶端建立連接、獲取權(quán)限、維持和管理連接
  • 在完成經(jīng)典的TCP握手后，連接器就要開始認(rèn)證你的身份，這個時候用的就是你輸入的用戶名和密碼
  • 一個用戶成功建立連接之后，即使你用管理員賬號對這個用戶的權(quán)限做了修改，也不會影響已經(jīng)存在的連接的權(quán)限。修改完成之后，只有再新建的連接才會使用新的權(quán)限配置
2、查詢緩存
  • 拿到一個查詢請求后，會先到查詢緩存中查看
  • 之前執(zhí)行過的語句及其結(jié)果可能會以key-value的形式被直接緩存在內(nèi)存中。key是查詢的語句，value是結(jié)果。如果你的查詢能夠直接在這個緩存中找到key，那么這個value就會直接返回給客戶端
3、分析器
  • 如果沒有命中查詢緩存，就要開始真正執(zhí)行語句
  • 分析器會先做“詞法分析”。你輸入的是由多個字符串和空格組成的一條sql語句，mysql需要識別出里面的字符串分別是什么，代表什么，將整個sql語句打碎成一個個單詞（token）
  • 做完了這些識別之后，就要做“語法分析”。根據(jù)詞法分析的結(jié)果，語法分析器會根據(jù)語法規(guī)則，判斷你輸入的這個sql語句是否滿足sql語法，生成對應(yīng)的抽象語法樹AST，提供給優(yōu)化器
4、優(yōu)化器
  • 優(yōu)化器是在表里面有多個索引的時候，決定使用哪個索引

  • 或者在一個語句中有多表關(guān)聯(lián)的時候，決定各個表的連接順序


    舉個例子
  • 這兩種執(zhí)行方法的邏輯結(jié)果是一樣的，但是執(zhí)行的效率會有所不同，而優(yōu)化器的作用就是決定選擇使用哪一個方案。
5、執(zhí)行器
  • 先判斷一下你對這個表有沒有執(zhí)行查詢的權(quán)限
  • 有權(quán)限，就在打開表的時候，執(zhí)行器會根據(jù)表的引擎定義，去使用這個引擎提供的接口
  • 在這個例子中，sex字段沒有索引，那么執(zhí)行器的執(zhí)行流程是這樣的：
    調(diào)用InnoDB引擎接口取這個表的第一行，判斷ID值是不是10，如果不是則跳過，如果是則將這行存在結(jié)果集中；調(diào)用引擎接口取“下一行”，重復(fù)相同的判斷邏輯，直到取到這個表的最后一行；執(zhí)行器將上述遍歷過程所有滿足條件的行組成的記錄集作為結(jié)果集返回給客戶端

三、行鎖

  • InnoDB是支持行鎖的，默認(rèn)情況下是采用行級鎖
  • MylSAM引擎不支持行鎖
    InnoDB實現(xiàn)了以下兩種類型的行鎖：
  • 共享讀鎖（s）：允許一個事務(wù)去讀一行，阻止其他事務(wù)獲得相同數(shù)據(jù)集的排它鎖
  • 排他寫鎖（x）：允許獲得排他鎖的事務(wù)更新數(shù)據(jù)，阻止其他事務(wù)取得相同數(shù)據(jù)集的共享讀鎖和排他寫鎖。對于update、insert、delete語句，InnoDB會自動給涉及數(shù)據(jù)集加排他鎖（x）

行鎖的兩階段鎖協(xié)議

  • 在InnoDB事務(wù)中，行鎖是需要的時候才加的，但并不是不需要了就立即釋放，而是需要等到事務(wù)結(jié)束的時候才釋放。這個就是兩階段鎖協(xié)議
  • 如果你的事務(wù)中需要鎖多個行，要把最可能造成鎖沖突、最可能影響并發(fā)度的鎖盡量往后放

假如有一個類似信用卡取現(xiàn)的業(yè)務(wù)，需要用戶a向銀行b取現(xiàn)100元，這個業(yè)務(wù)涉及以下操作：
1.從用戶a賬戶增加100元
2.從銀行b賬戶減少100元
3.記錄一條交易日志
個人賬戶一般情況下，只有一個人在使用，但是銀行賬戶在a用戶使用的同時有可能c用戶、d用戶同時向它取現(xiàn)，這些事務(wù)沖突的就是操作2，那么根據(jù)兩階段鎖協(xié)議，就需要把操作2放在事務(wù)的最后執(zhí)行，這就最大程度地減少了事務(wù)之間的鎖等待，提升了并發(fā)度

  • InnoDB行鎖是通過索引上的索引項加鎖來實現(xiàn)的，InnoDB這種行鎖實現(xiàn)特點意味著：只有通過索引條件檢索數(shù)據(jù)，InnoDB才使用行級鎖，否則，InnoDB將使用表鎖！
  • 這里的行鎖退化表鎖是會鎖上聚簇索引中的所有記錄，并且會鎖上聚簇索引中的所有間隙鎖
  • 只有執(zhí)行計劃真正使用了索引，才能使用行鎖：即便在條件中使用了索引字段，但是否使用索引來檢索數(shù)據(jù)是由mysql通過判斷不同執(zhí)行計劃的代價來決定的。如果mysql認(rèn)為全局掃描效率更高，比如一些很小的表，它就不會使用索引，這種情況下InnoDB將使用表鎖，而不是行鎖。因此，在分析鎖沖突時，別忘了檢查slq的執(zhí)行計劃（可以通過explain檢查sql的執(zhí)行計劃），以確認(rèn)是否真正使用了索引
  • mysql的行鎖是針對索引加的鎖，不是針對記錄加的鎖，所以雖然多個session是訪問不同行的記錄，但是如果是使用相同的索引鍵，是會出現(xiàn)沖突的

四、表鎖

先來看一段代碼：

SET AUTOCOMMIT=0;
LOCK TABLES t1 WRITE,t2 READ......;
[do something with tables t1 and t2 here]
COMMIT;
UNLOCK TABLES;
  • 在用LOCK TABLES對InnoDB表加鎖時要注意，要將AUTOCOMMIT設(shè)為0，否則mysql不會給表枷鎖；
  • 事務(wù)結(jié)束前，不要用UNLOCK TABLES釋放表鎖，因為UNLOCK TABLES會隱含地提交事務(wù)；
  • COMMIT或ROLLBACK并不能釋放用LOCK TABLES加的表級鎖，必須用UNLOCK TABLES釋放表鎖。

五、元數(shù)據(jù)鎖MDL

在Mysql5.5版本中引入了MDL，當(dāng)對一個表做增刪改查操作的時候，加MDL讀鎖；當(dāng)要對表做結(jié)構(gòu)變更操作的時候，加MDL寫鎖。不用顯式加鎖

六、InnoDB索引模型

  • mysql中索引分為主鍵索引和非主鍵索引
  • 主鍵索引的葉子節(jié)點存的是整行數(shù)據(jù)。在InnoDB中，主鍵索引也被稱為聚簇索引
  • 非主鍵索引的葉子節(jié)點存放的內(nèi)容是主鍵的值。在InnoDB中，非主鍵索引也被稱為二級索引
    兩者查詢的區(qū)別
  • 如果語句是select * from T where ID = 500，即主鍵查詢方式，則只需要搜索ID這棵B+樹
  • 如果語句是select * from T where k = 5，即普通索引查詢方式，則需要先搜索k索引樹，得到ID的值為500，再到ID索引樹搜索一次。這個過程稱為回表
  • 基于非主鍵索引的查詢需要多掃描一棵索引樹。

七、隔離級別

隔離級別

八、bin log

  • binlog是mysql的server層實現(xiàn)的，記錄的是這個語句的原始邏輯
  • binlog是可以追加寫入的?！白芳訉憽笔侵竍inlog文件寫到一定大小會切換到下一個，并不會覆蓋以前的日志
日志恢復(fù)
  • 前提是數(shù)據(jù)定期做備份，保證musql中一段時間的binlog
  • 當(dāng)發(fā)生誤操作進行數(shù)據(jù)恢復(fù)時，先找到最近一次全量備份，恢復(fù)到數(shù)據(jù)庫
  • 從備份的時間點開始，將備份的binlog依次取出來，重放到誤刪除表之前的那個時刻。

九、redo-log

先看一段sql語句：

UPDATE person SET person_name = 'heiwu' WHERE id = 5;
  • redo log是保存在引擎層的
  • 如果每一次的更新操作都要寫進磁盤，然后磁盤也要找到對應(yīng)的那條記錄，然后再更新，整個IO成本、查找成本都很高
  • mysql使用wal技術(shù)來優(yōu)化更新操作，wal的全程是Write-Ahead logging，它的關(guān)鍵點就是先寫日志，再寫磁盤。
  • 當(dāng)有一條記錄需要更新的時候，InnoDB引擎就會先把記錄寫到redo log中去，并更新內(nèi)存，這個時候更新就算完成了。同時，InnoDB引擎會在適當(dāng)?shù)臅r候，將這個操作記錄更新到磁盤里面，而這個更新往往是系統(tǒng)比較空閑的時候做
  • InnoDB的redo log是固定大小的，比如可以配置為一組4個文件，每個文件的大小是1GB，那么這塊文件總共就可以記錄4GB的操作
  • 有了redo log,InnoDB就可以保證即使數(shù)據(jù)庫發(fā)生異常重啟，之前提交的記錄都不會丟失，這個能力稱之為crash-safe。

十、redo-log vs binlog

還是上面的那個更新操作語句：

UPDATE person SET person_name = 'heiwu' WHERE id = 5;
  • 執(zhí)行器先找引擎（InnoDB）取ID=5這一行，ID是主鍵，引擎直接用樹搜索找到這一行。如果ID=5這一行所在的數(shù)據(jù)頁本來就在內(nèi)存中，就直接返回給執(zhí)行器；否則，就需要先從磁盤讀入緩存，然后再返回；
  • 執(zhí)行器拿到引擎給的行數(shù)據(jù)，把這個值改為“heiwu”，比如原得到一行新的一行數(shù)據(jù)，再調(diào)用引擎接口寫入這行新數(shù)據(jù)
  • 引擎將這行新數(shù)據(jù)更新到內(nèi)存中，同時將這個更新操作記錄到redo log中，此時redo log處于prepare狀態(tài)。然后告知執(zhí)行器執(zhí)行完成了，隨時可以提交事務(wù)
  • 執(zhí)行器生成這個操作的binlog，并把binlog寫入到磁盤中去
  • 執(zhí)行器調(diào)用引擎的事務(wù)提交接口，引擎把剛剛寫入的redo log改成提交(commit)狀態(tài)，更新完成
最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點，簡書系信息發(fā)布平臺，僅提供信息存儲服務(wù)。

友情鏈接更多精彩內(nèi)容