MySQL

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

MySQL主要而分為server層和存儲(chǔ)引擎層兩部分

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

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

select * from T where ID=10;

• 連接器（負(fù)責(zé)跟客戶端建立連接、獲取權(quán)限、維持和管理連接）

連接命令

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

連接命令中的 mysql 是客戶端工具，用來(lái)跟服務(wù)端建立連接。

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

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

如果用戶名密碼認(rèn)證通過(guò)，連接器會(huì)到權(quán)限表里面查出你擁有的權(quán)限。之后，這個(gè)連接里面的權(quán)限判斷邏輯，都將依賴于此時(shí)讀到的權(quán)限。

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

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

客戶端如果太長(zhǎng)時(shí)間沒(méi)動(dòng)靜，連接器就會(huì)自動(dòng)將它斷開(kāi)。這個(gè)時(shí)間是由參數(shù) wait_timeout 控制的，默認(rèn)值是 8 小時(shí)。

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

解決這個(gè)問(wèn)題,以下兩種方案

定期斷開(kāi)長(zhǎng)連接。使用一段時(shí)間，或者程序里面判斷執(zhí)行過(guò)一個(gè)占用內(nèi)存的大查詢后，斷開(kāi)連接，之后要查詢?cè)僦剡B。

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

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

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

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

• 分析器

對(duì) SQL 語(yǔ)句做解析

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

MySQL 從你輸入的"select"這個(gè)關(guān)鍵字識(shí)別出來(lái)，這是一個(gè)查詢語(yǔ)句。它也要把字符串“T”識(shí)別成“表名 T”，把字符串“ID”識(shí)別成“列 ID”。

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

如果你的語(yǔ)句不對(duì)，就會(huì)收到“You have an error in your SQL syntax”的錯(cuò)誤提醒，比如下面這個(gè)語(yǔ)句 select 少打了開(kāi)頭的字母“s”。

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

• 優(yōu)化器

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

• 執(zhí)行器

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

如果有權(quán)限，就打開(kāi)表繼續(xù)執(zhí)行。打開(kāi)表的時(shí)候，執(zhí)行器就會(huì)根據(jù)表的引擎定義，去使用這個(gè)引擎提供的接口。

select * from T where ID=10

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

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

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

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

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

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

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

SQL更新語(yǔ)句執(zhí)行

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

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

記錄這個(gè)頁(yè)做了什么改動(dòng) 

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

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

Binlog有兩種模式，

statement 格式的話是記sql語(yǔ)句，

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

• 日志區(qū)別

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

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

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

• update 語(yǔ)句時(shí)的內(nèi)部流程

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

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

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

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

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

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

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

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

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

需要恢復(fù)到指定的某一秒時(shí)

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

先寫 redo log 后寫 binlog。

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

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

先寫 binlog 后寫 redo log。

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

InnoDB

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

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

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

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

鎖

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

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

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

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

總結(jié)

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

最新評(píng)論