快捷導(dǎo)航

mysql如何執(zhí)行流程

更新時間：2024年11月22日 14:30:25 作者：風(fēng)舞紅葉

MySQL主要分為server層和存儲引擎層,server層負(fù)責(zé)連接、查詢緩存、分析、優(yōu)化和執(zhí)行,存儲引擎層負(fù)責(zé)數(shù)據(jù)存儲和提取,SQL查詢執(zhí)行流程包括連接、認(rèn)證、權(quán)限檢查、分析、優(yōu)化和執(zhí)行,更新語句執(zhí)行涉及重做日志（redolog）和歸檔日志（binlog）

MySQL

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

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

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

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

select * from T where ID=10;

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

連接命令

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

分析器

對 SQL 語句做解析

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

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

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

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

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

優(yōu)化器

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

執(zhí)行器

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

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

select * from T where ID=10

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

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

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

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

對于有索引的表，執(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 引擎特有日志)

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

記錄這個頁做了什么改動

redo log 用于保證 crash-safe 能力。innodb_flush_log_at_trx_commit 這個參數(shù)設(shè)置成 1 的時候，表示每次事務(wù)的 redo log 都直接持久化到磁盤。這個參數(shù)我建議你設(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í)行完成了，隨時可以提交事務(wù)。

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

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

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

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

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

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

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

先寫 redo log 后寫 binlog。

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

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

先寫 binlog 后寫 redo log。

如果在 binlog 寫完之后 crash，由于 redo log 還沒寫，崩潰恢復(fù)以后這個事務(wù)無效，所以這一行 c 的值是 0。但是 binlog 里面已經(jīng)記錄了“把 c 從 0 改成 1”這個日志。所以，在之后用 binlog 來恢復(fù)的時候就多了一個事務(wù)出來，恢復(fù)出來的這一行 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 在查找對應(yīng)的記錄時，并不會直接從樹中找出對應(yīng)的行記錄，它只能記錄獲取記錄所在頁，將整個頁加載到內(nèi)存中，再通過 Page Directory 中存儲的稀疏索引和 n_owned、next_record 屬性取出對應(yīng)的記錄，這部分是在內(nèi)存中進(jìn)行的，通常忽略這部分查找耗時；

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

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

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

鎖