mysql基礎問題三問（底層邏輯;正在執行;日誌觀察）

Mrwhite86發表於2022-12-05

背景：經常面試會遇到且實際工作中也會應用到的三個場景：

- - - - - - - - - -分割線- - - - - - - - - - -
一.mysql查詢時的底層原理是什麼？
mysql底層使用的元件主要分別兩個部分：包含Server層與Store層
Server層
主要包括聯結器、查詢快取、分析器、最佳化器、執行器等，涵蓋 MySQL 的大多數核心服務功能，以及所有的內建函式（如日期、時間、數學和加密函式等），所有跨儲存引擎的功能都在這一層實現，比如儲存過程、觸發器、檢視等。
Store層
儲存引擎層負責資料的儲存和提取。其架構模式是外掛式的，支援 InnoDB、MyISAM、Memory 等多個儲存引擎。現在最常用的儲存引擎是 InnoDB，它從 MySQL 5.5.5 版本開始成為了預設儲存引擎。也就是說如果我們在create table時不指定表的儲存引擎型別,預設會給你設定儲存引擎為InnoDB。
1.聯結器：
使用各種客戶端連線mysql時會使用到聯結器，這些客戶端要向mysql發起通訊都必須先跟Server端建立通訊連線，而建立連線的工作就是有聯結器完成的。
聯結器負責跟客戶端建立連線、獲取許可權、維持和管理連線。連線命令一般是這麼寫的：

[root@192 ~]# mysql -h host[資料庫地址] -u root[使用者] -p root[密碼] -P 3306

mysql -u root -p

聯結器就要開始認證你的身份，這個時候用的就是你輸入的使用者名稱和密碼;如果使用者名稱或密碼不對，你就會收到一個"Access denied for user"的錯誤，然後客戶端程式結束執行

聯結器會到許可權表裡面查出你擁有的許可權。之後，這個連線裡面的許可權判斷邏輯，都將依賴於此時讀到的許可權,連線成功如下

show grants for root@"%"; 檢視當前使用者的許可權

MySQL 拿到一個查詢請求後，會先到查詢快取看看，之前是不是執行過這條語句。之前執行過的語句及其結果可能會以 key-value 對的形式，被直接快取在記憶體中。key 是查詢的語句，value 是查詢的結果。如果你的查詢能夠直接在這個快取中找到 key，那麼這個 value 就會被直接返回給客戶端。

檢視當前mysql例項是否開啟快取機制：

mysql> show global variables like "%query_cache_type%";

監控查詢快取資訊：

mysql> show status like'%Qcache%'; //檢視執行的快取資訊

如果語句不在查詢快取中，就會繼續後面的執行階段。執行完成後，執行結果會被存入查詢快取中。你可以看到，如果查詢命中快取，MySQL 不需要執行後面的複雜操作，就可以直接返回結果，這個效率會很高建議大家在靜態表裡使用查詢快取，什麼叫靜態表呢？就是一般我們極少更新的表。比如，一個系統配置表、字典表，那這張表上的查詢才適合使用查詢快取。好在 MySQL 也提供了這種“按需使用”的方式。你可以將my.cnf引數 query_cache_type 設定成 DEMAND。

my.cnf
#query_cache_type有3個值 0代表關閉查詢快取OFF，1代表開啟ON，2（DEMAND）代表當sql語句中有SQL_CACHE關鍵詞時才快取
query_cache_type=2

2.分析器

如果沒有命中查詢快取，就要開始真正執行語句了。首先，MySQL 需要知道你要做什麼，因此需要對 SQL 語句做解析。

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

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

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

如果你的語句不對，就會收到“You have an error in your SQL syntax”的錯誤提醒，比如下面這個語句 from 寫成了 "rom"。

分析器分成6個步驟：

（1）詞法分析（2）語法分析（3）語義分析（4）生成執行樹（5）生成執行計劃（6）計劃執行

3.最佳化器

最佳化器決定了要使用的索引，各個表的連線順序

最佳化器是在表裡面有多個索引的時候，決定使用哪個索引；或者在一個語句有多表關聯（join）的時候，決定各個表的連線順序。比如你執行下面這樣的語句，這個語句是執行兩個表的 join：

既可以先從表 test1 裡面取出 name=yangguo的記錄的 ID 值，再根據 ID 值關聯到表 test2，再判斷 test2 裡面 name的值是否等於 yangguo。

也可以先從表 test2 裡面取出 name=xiaolongnv 的記錄的 ID 值，再根據 ID 值關聯到 test1，再判斷 test1 裡面 name 的值是否等於 yangguo。

這兩種執行方法的邏輯結果是一樣的，但是執行的效率會有不同，而最佳化器的作用就是決定選擇使用哪一個方案。最佳化器階段完成後，這個語句的執行方案就確定下來了，然後進入執行器階段

4.執行器

開始執行的時候，要先判斷一下你對這個表 T 有沒有執行查詢的許可權，如果沒有，就會返回沒有許可權的錯誤，如下所示 (在工程實現上，如果命中查詢快取，會在查詢快取返回結果的時候，做許可權驗證。查詢也會在最佳化器之前呼叫 precheck 驗證許可權)。

如果有許可權，就開啟表繼續執行。開啟表的時候，執行器就會根據表的引擎定義，去使用這個引擎提供的介面。

比如我們這個例子中的表 test 中，ID 欄位沒有索引，那麼執行器的執行流程是這樣的：

（1）呼叫 InnoDB 引擎介面取這個表的第一行，判斷 ID 值是不是 10，如果不是則跳過，如果是則將這行存在結果集中；

（2）呼叫引擎介面取“下一行”，重複相同的判斷邏輯，直到取到這個表的最後一行。

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

至此，這個語句就執行完成了。對於有索引的表，執行的邏輯也差不多。第一次呼叫的是“取滿足條件的第一行”這個介面，之後迴圈取“滿足條件的下一行”這個介面，這些介面都是引擎中已經定義好的。你會在資料庫的慢查詢日誌中看到一個 rows_examined 的欄位，表示這個語句執行過程中掃描了多少行。這個值就是在執行器每次呼叫引擎獲取資料行的時候累加的。在有些場景下，執行器呼叫一次，在引擎內部則掃描了多行，因此引擎掃描行數跟 rows_examined 並不是完全相同的。

5.bin-log歸檔

刪庫是不需要跑路的，因為我們的SQL執行時，會將sql語句的執行邏輯記錄在我們的bin-log當中，什麼是bin-log呢？

binlog是Server層實現的二進位制日誌,他會記錄我們的cud操作。Binlog有以下幾個特點：

（1）Binlog在MySQL的Server層實現（引擎共用）

（2）Binlog為邏輯日誌,記錄的是一條語句的原始邏輯

（3）Binlog不限大小,追加寫入,不會覆蓋以前的日誌

如果，我們誤刪了資料庫,可以使用binlog進行歸檔!要使用binlog歸檔，首先我們得記錄binlog，因此需要先開啟MySQL的binlog功能。

配置my.cnf

配置開啟binlog
log-bin=/usr/local/mysql/data/binlog/mysql-bin
注意5.7以及更高版本需要配置本項：server-id=123454（自定義,保證唯一性）;
#binlog格式，有3種statement,row,mixed
binlog-format=ROW
#表示每1次執行寫入就與硬碟同步，會影響效能，為0時表示，事務提交時mysql不做刷盤操作，由系統決定
sync-binlog=1
複製程式碼

binlog命令：

mysql> show variables like '%log_bin%'; 檢視bin-log是否開啟
mysql> flush logs; 會多一個最新的bin-log日誌
mysql> show master status; 檢視最後一個bin-log日誌的相關資訊
mysql> reset master; 清空所有的bin-log日誌

二.如何檢視正在執行的mysql語句？

首先：processlist表位於information_schema庫中，主要是儲存的MySQL執行緒的一些基本資訊。我們使用

desc information_schema.processlist來檢視錶結構：

ID: 執行緒的id
USER: 執行緒屬於哪一個使用者
HOST：客戶端的host資訊：hostname+埠
DB：執行緒在哪一個資料庫下
COMMAND：執行緒使用哪一種命令在執行，空閒的執行緒狀態為sleep
TIME:執行緒已經執行的時間，秒為單位
STATE：執行緒正在做什麼：當前的狀態，行為，或者事件
INFO：執行緒正在執行的語句，但是這個並不是很準確，所以需要使用其他的方式來完成我們的目標。
使用show processlist 或者 select * from information_schema.processlist檢視processlist表

並且可以直接kill掉卡死的程式資訊

show processlist 
select * from information_schema.processlist

1.拿到正在執行的processlist_id

select id from information_schema.processlist

2.拿到與processlist_id對應的thread_id

select thread_id from performance_schema.threads where processlist_id in (上一步拿到的processlist_id列表)

3.拿到正在執行的sql語句

select thread_id, sql_text from performance_schema.events_statements_current where thread_id in (上一步拿到的thread_id列表)

4.完整的sql語句如下：

SELECT a.*, c.thread_id, c.sql_text from information_schema.processlist a
LEFT JOIN performance_schema.threads b on a.id = b.PROCESSLIST_ID
LEFT JOIN performance_schema.events_statements_current c on c.THREAD_ID = b.THREAD_ID;