MySQL

架構與SQL執(zhí)行流程

MySQL主要而分為server層和存儲引擎層兩部分

• Server 層包括連接器、查詢緩存、分析器、優(yōu)化器、執(zhí)行器等，涵蓋 MySQL 的大多數(shù)核心服務功能，以及所有的內(nèi)置函數(shù)（如日期、時間、數(shù)學和加密函數(shù)等），所有跨存儲引擎的功能都在這一層實現(xiàn)，比如存儲過程、觸發(fā)器、視圖等。
• 存儲引擎層負責數(shù)據(jù)的存儲和提取。其架構模式是插件式的，支持 InnoDB、MyISAM、Memory 等多個存儲引擎?，F(xiàn)在最常用的存儲引擎是 InnoDB，它從 MySQL 5.5.5 版本開始成為了默認存儲引擎。（在 create table 語句中使用 engine=memory, 來指定使用內(nèi)存引擎創(chuàng)建表。）

SQL查詢語句執(zhí)行流程

select * from T where ID=10;

• 連接器（負責跟客戶端建立連接、獲取權限、維持和管理連接）

連接命令

mysql -h$ip -P$port -u$user -p

連接命令中的 mysql 是客戶端工具，用來跟服務端建立連接。

在完成經(jīng)典的 TCP 握手后，連接器就要開始認證你的身份，這個時候用的就是你輸入的用戶名和密碼。

如果用戶名或密碼不對，你就會收到一個"Access denied for user"的錯誤，然后客戶端程序結束執(zhí)行。

如果用戶名密碼認證通過，連接器會到權限表里面查出你擁有的權限。之后，這個連接里面的權限判斷邏輯，都將依賴于此時讀到的權限。

一個用戶成功建立連接后，即使你用管理員賬號對這個用戶的權限做了修改，也不會影響已經(jīng)存在連接的權限。修改完成后，只有再新建的連接才會使用新的權限設置。

連接完成后，如果你沒有后續(xù)的動作，這個連接就處于空閑狀態(tài)，你可以在 show processlist 命令中看到它。

客戶端如果太長時間沒動靜，連接器就會自動將它斷開。這個時間是由參數(shù) wait_timeout 控制的，默認值是 8 小時。

全部使用長連接后，有些時候 MySQL 占用內(nèi)存漲得特別快，這是因為 MySQL 在執(zhí)行過程中臨時使用的內(nèi)存是管理在連接對象里面的。這些資源會在連接斷開的時候才釋放。所以如果長連接累積下來，可能導致內(nèi)存占用太大，被系統(tǒng)強行殺掉（OOM），從現(xiàn)象看就是 MySQL 異常重啟了。

解決這個問題,以下兩種方案

定期斷開長連接。使用一段時間，或者程序里面判斷執(zhí)行過一個占用內(nèi)存的大查詢后，斷開連接，之后要查詢再重連。

如果你用的是 MySQL 5.7 或更新版本，可以在每次執(zhí)行一個比較大的操作后，通過執(zhí)行 mysql_reset_connection 來重新初始化連接資源。這個過程不需要重連和重新做權限驗證，但是會將連接恢復到剛剛創(chuàng)建完時的狀態(tài)。

• 查詢緩存(MySQL 8.0 版本直接將查詢緩存的整塊功能刪掉了)

查詢緩存的失效非常頻繁，只要有對一個表的更新，這個表上所有的查詢緩存都會被清空

對于更新壓力大的數(shù)據(jù)庫來說，查詢緩存的命中率會非常低。除非你的業(yè)務就是有一張靜態(tài)表，很長時間才會更新一次。比如，一個系統(tǒng)配置表，那這張表上的查詢才適合使用查詢緩存。

• 分析器

對 SQL 語句做解析

分析器先會做“詞法分析”。你輸入的是由多個字符串和空格組成的一條 SQL 語句，MySQL 需要識別出里面的字符串分別是什么，代表什么。

MySQL 從你輸入的"select"這個關鍵字識別出來，這是一個查詢語句。它也要把字符串“T”識別成“表名 T”，把字符串“ID”識別成“列 ID”。

做完了這些識別以后，就要做“語法分析”。根據(jù)詞法分析的結果，語法分析器會根據(jù)語法規(guī)則，判斷你輸入的這個 SQL 語句是否滿足 MySQL 語法。

如果你的語句不對，就會收到“You have an error in your SQL syntax”的錯誤提醒，比如下面這個語句 select 少打了開頭的字母“s”。

一般語法錯誤會提示第一個出現(xiàn)錯誤的位置，所以你要關注的是緊接“use near”的內(nèi)容。

• 優(yōu)化器

優(yōu)化器是在表里面有多個索引的時候，決定使用哪個索引；或者在一個語句有多表關聯(lián)（join）的時候，決定各個表的連接順序

• 執(zhí)行器

開始執(zhí)行的時候，要先判斷一下你對這個表 T 有沒有執(zhí)行查詢的權限，如果沒有，就會返回沒有權限的錯誤，如下所示 (在工程實現(xiàn)上，如果命中查詢緩存，會在查詢緩存返回結果的時候，做權限驗證。查詢也會在優(yōu)化器之前調(diào)用 precheck 驗證權限)。 

如果有權限，就打開表繼續(xù)執(zhí)行。打開表的時候，執(zhí)行器就會根據(jù)表的引擎定義，去使用這個引擎提供的接口。

select * from T where ID=10

ID 字段沒有索引，那么執(zhí)行器的執(zhí)行流程是這樣的：

1、調(diào)用 InnoDB 引擎接口取這個表的第一行，判斷 ID 值是不是 10，如果不是則跳過，如果是則將這行存在結果集中；

2、調(diào)用引擎接口取“下一行”，重復相同的判斷邏輯，直到取到這個表的最后一行。

3、執(zhí)行器將上述遍歷過程中所有滿足條件的行組成的記錄集作為結果集返回給客戶端。

對于有索引的表，執(zhí)行的邏輯也差不多。第一次調(diào)用的是“取滿足條件的第一行”這個接口，之后循環(huán)取“滿足條件的下一行”這個接口，這些接口都是引擎中已經(jīng)定義好的。

你會在數(shù)據(jù)庫的慢查詢?nèi)罩局锌吹揭粋€ rows_examined 的字段，表示這個語句執(zhí)行過程中掃描了多少行。這個值就是在執(zhí)行器每次調(diào)用引擎獲取數(shù)據(jù)行的時候累加的。

在有些場景下，執(zhí)行器調(diào)用一次，在引擎內(nèi)部則掃描了多行，因此引擎掃描行數(shù)跟 rows_examined 并不是完全相同的

SQL更新語句執(zhí)行

• 與查詢流程不一樣的是，更新流程還涉及兩個重要的日志模塊；redo log（重做日志）和 binlog（歸檔日志）
• 物理日志 redo log(InnoDB 引擎特有日志)

當有一條記錄需要更新的時候，InnoDB 引擎就會先把記錄寫到 redo log 里面，并更新內(nèi)存，這個時候更新就算完成了。同時，InnoDB 引擎會在適當?shù)臅r候，將這個操作記錄更新到磁盤里面，而這個更新往往是在系統(tǒng)比較空閑的時候做

記錄這個頁做了什么改動 

redo log 用于保證 crash-safe 能力。innodb_flush_log_at_trx_commit 這個參數(shù)設置成 1 的時候，表示每次事務的 redo log 都直接持久化到磁盤。這個參數(shù)我建議你設置成 1，這樣可以保證 MySQL 異常重啟之后數(shù)據(jù)不丟失。

• 邏輯日志 binlog(Server 層日志)

Binlog有兩種模式，

statement 格式的話是記sql語句，

row格式會記錄行的內(nèi)容，記兩條，更新前和更新后都有。

• 日志區(qū)別

1、redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 層實現(xiàn)的，所有引擎都可以使用。 

2、redo log 是物理日志，記錄的是“在某個數(shù)據(jù)頁上做了什么修改”；binlog 是邏輯日志，記錄的是這個語句的原始邏輯，比如“給 ID=2 這一行的 c 字段加 1 ”。 

3、redo log 是循環(huán)寫的，空間固定會用完；binlog 是可以追加寫入的。“追加寫”是指 binlog 文件寫到一定大小后會切換到下一個，并不會覆蓋以前的日志。

• update 語句時的內(nèi)部流程

update T set c=c+1 where ID=2;

1、執(zhí)行器先找引擎取 ID=2 這一行。ID 是主鍵，引擎直接用樹搜索找到這一行。如果 ID=2 這一行所在的數(shù)據(jù)頁本來就在內(nèi)存中，就直接返回給執(zhí)行器；否則，需要先從磁盤讀入內(nèi)存，然后再返回。 

2、執(zhí)行器拿到引擎給的行數(shù)據(jù)，把這個值加上 1，比如原來是 N，現(xiàn)在就是 N+1，得到新的一行數(shù)據(jù)，再調(diào)用引擎接口寫入這行新數(shù)據(jù)。 

3、引擎將這行新數(shù)據(jù)更新到內(nèi)存中，同時將這個更新操作記錄到 redo log 里面，此時 redo log 處于 prepare 狀態(tài)。然后告知執(zhí)行器執(zhí)行完成了，隨時可以提交事務。 

4、執(zhí)行器生成這個操作的 binlog，并把 binlog 寫入磁盤。 

5、執(zhí)行器調(diào)用引擎的提交事務接口，引擎把剛剛寫入的 redo log 改成提交（commit）狀態(tài)，更新完成。 

兩階段提交(跨系統(tǒng)維持數(shù)據(jù)邏輯一致性時常用的一個方案)

數(shù)據(jù)庫恢復

binlog 會記錄所有的邏輯操作，并且是采用“追加寫”的形式

需要恢復到指定的某一秒時

• 找到最近的一次全量備份，從這個備份恢復到臨時庫；
• 從備份的時間點開始，將備份的 binlog 依次取出來，重放到指定時刻。
• 由于 redo log 和 binlog 是兩個獨立的邏輯,如果不使用“兩階段提交”，那么數(shù)據(jù)庫的狀態(tài)就有可能和用它的日志恢復出來的庫的狀態(tài)不一致。

先寫 redo log 后寫 binlog。

假設在 redo log 寫完，binlog 還沒有寫完的時候，MySQL 進程異常重啟。由于我們前面說過的，redo log 寫完之后，系統(tǒng)即使崩潰，仍然能夠把數(shù)據(jù)恢復回來，所以恢復后這一行 c 的值是 1。但是由于 binlog 沒寫完就 crash 了，這時候 binlog 里面就沒有記錄這個語句。因此，之后備份日志的時候，存起來的 binlog 里面就沒有這條語句。

然后你會發(fā)現(xiàn)，如果需要用這個 binlog 來恢復臨時庫的話，由于這個語句的 binlog 丟失，這個臨時庫就會少了這一次更新，恢復出來的這一行 c 的值就是 0，與原庫的值不同。

先寫 binlog 后寫 redo log。

如果在 binlog 寫完之后 crash，由于 redo log 還沒寫，崩潰恢復以后這個事務無效，所以這一行 c 的值是 0。但是 binlog 里面已經(jīng)記錄了“把 c 從 0 改成 1”這個日志。所以，在之后用 binlog 來恢復的時候就多了一個事務出來，恢復出來的這一行 c 的值就是 1，與原庫的值不同。

InnoDB

• 數(shù)據(jù)存儲：在InnoDB存儲引擎，數(shù)據(jù)被邏輯地存放到表空間中，表空間（tablespace）是存儲引擎中最高到的存儲邏輯單位，在表空間的下面包括段（segment）、區(qū)（extent）、頁（page）
• 存儲表：表的定義信息存儲在 .frm 文件中；數(shù)據(jù)索引存儲在 .ibd 文件中；
• 存儲記錄：InnoDB 使用頁作為磁盤管理的最小單位，數(shù)據(jù)在 InnoDB 中按行存儲，每個 16KB 大小的頁中可以存放 2-200 行的記錄
• B+Tree 在查找對應的記錄時，并不會直接從樹中找出對應的行記錄，它只能記錄獲取記錄所在頁，將整個頁加載到內(nèi)存中，再通過 Page Directory 中存儲的稀疏索引和 n_owned、next_record 屬性取出對應的記錄，這部分是在內(nèi)存中進行的，通常忽略這部分查找耗時；

索引：索引優(yōu)化是對查詢性能優(yōu)化的最有效手段；

B+Tree（平衡樹，查找任意節(jié)點耗時相同，比較次數(shù)就是樹高） 索引可以分為聚集索引和輔助索引

• 聚集索引：就是按照表中主鍵的順序構建的一顆 B+Tree ，并在葉子節(jié)點總存放表中的行記錄數(shù)據(jù)；使用聚集索引對表中的數(shù)據(jù)進行檢索時，可以直接獲得聚集索引對應的整條行記錄數(shù)據(jù)所在的葉，不需要進行第二次操作。
• 輔助索引：也是通過 B+Tree 實現(xiàn)，但是葉子節(jié)點并不包含行記錄的全部數(shù)據(jù)，僅包含索引中的所有鍵和一個用于查找對應行記錄的【書簽】，InnoDB 中這個書簽就是當前記錄的主鍵

鎖

• InnoDB 鎖種類
• 共享鎖（Shared Lock）和互斥鎖（Exclusive Lock）屬于行級鎖
• 意向鎖（Intention Lock）一種表級鎖
• 意向共享鎖：事務想要在獲取表中某些記錄的共享鎖，需要在表上先加意向共享鎖
• 意向互斥鎖：事務想要獲取表中某些記錄的互斥鎖，需要在表上先加意向互斥鎖

意向鎖不會阻塞全表掃描之外的任何請求，主要目的是為了表示是否有人請求鎖定標中某一行數(shù)據(jù)

• 意向共享鎖：事務想要在獲取表中某些記錄的共享鎖，需要在表上先加意向共享鎖
•  意向互斥鎖：事務想要獲取表中某些記錄的互斥鎖，需要在表上先加意向互斥鎖

意向鎖不會阻塞全表掃描之外的任何請求，主要目的是為了表示是否有人請求鎖定標中某一行數(shù)據(jù)

總結

以上為個人經(jīng)驗，希望能給大家一個參考，也希望大家多多支持腳本之家。