一條查詢語句的執行流程

連接器

連接器：管理連接、權限校驗

管理連接

管理連接：由于連接成本高，連接池會復用連接。

成本高：TCP三次握手；發起系統調用；高并發場景可能耗盡文件資源描述符；

復用連接的問題（長連接問題）：連接在斷開時才會釋放占用資源，而不是用完就釋放；長連接可能導致占用內存變大，比如大的查詢；長時間積累會導致mysql被系統殺掉OOM // 現象：mysql重啟

長連接問題解決方案：

• 定期斷開連接
• mysql_reset_connection 重置

空閑連接最大空閑時間：wait_timeout=default 8h

權限校驗

到權限表中查找擁有的權限；之后這個連接驗證的全局權限用的都是此時的權限快照；即使后續修改權限，也只會在新會話中生效，不會改變當前會話; // 但db權限修改了，就會生效，但如果進入了use db1；那修改了也不會改變use db1里的會話。

分析器

分析器：詞法分析、語法分析

詞法分析：檢查表、列是否存在；若列不存在，則返回報錯

語法分析：比如 elect * from t; 會報語法錯誤；錯誤會在use near后面

優化器

優化器：多條執行計劃成本對比，智能選擇索引

成本對比：對比CPU計算、內存消耗、大概掃描行數(采樣統計)、是否排序、是否使用臨時表等

采樣統計

一個索引上不同值（基數）越多，區分度越高；mysql通過采樣統計得到索引的基數；

innodb_status_persistent：

on 持久化 N=20（采樣頁數）M=10（1/10個頁數變動就重新采樣）

off 僅存在內存 N=8 M=16

優化器選錯索引改正

• 掃描行數不準：analyze table t修正
• force index 強制修改索引
  • 問題：不優雅、維護字段變動就需要手動修改、遷移數據庫可能語句不兼容 // 主要就是關注變更的及時性
• 修改sql語義
• 業務思考，刪掉有影響的無效索引

執行器

執行前會判斷有無操作表的權限；在進優化器前會先precheck(粗檢查)，執行器進行細檢查，比如視圖、存儲過程等復雜對象在precheck檢查不了。

存儲過程舉例：

delimiter ;;
create procedure idata()
begin...
end ;;
delimiter ;

走查詢緩存時，也會先查權限；

查詢緩存

失效頻繁：表更新時，所有查詢緩存都會被清空；更新壓力大的數據庫緩存命中率低；

查詢緩存適合靜態表，比如系統配置表；

存儲引擎

比如innodb，存儲引擎以插件的方式加入

一條update語句的執行流程

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

server層執行流程與select相同，下面主要介紹引擎層的執行流程 // 部分流程節點不在引擎中，比如binlog

redo log

redo log buffer結構

redo log buffer類似go ring buffer, 是固定大小的環狀結構。write_pos是當前記錄的位置，write_pos到check_point的綠色部分還能寫入，其余位置是新的寫入。如果write_pos追上check_point，就需要先落盤，更新check_point的位置。// 此時落盤是prepare狀態的redo log

redo log一般有4GB，由4個1GB的文件組成。如果redo log設的太小，會出現磁盤壓力小，但數據庫出現間歇性的性能下跌，因為系統頻繁的中斷業務刷臟，更新check point位置

redo log日志類型

redo log是物理日志，記錄了數據頁的具體修改，比如哪一行的那個字段由啥改成啥；

redo log記錄的是操作，而不是數據本身，數據存在內存（buffer pool）和磁盤上；

寫入時機

redo log在修改數據前順序寫入，是WAL（Write Ahead Log），是崩潰恢復的重要保證機制；

redo log是順序寫入，比直接寫入磁盤更快（磁盤I/O慢、寫B+樹），降低了服務崩潰，數據丟失的風險。

配置innodb_flush_log_at_trx_commit

0：只寫到buffer中（內存緩存)，等待定時刷新

1：事務提交時持久化到磁盤 // 推薦

2：會推到page cache中（os緩存），定時持久化

binlog

redo log怎么找到對應的binlog：有個xid，關聯他們。

mysql有全局變量global_query_id，每次執行語句會給它發一個query_id，然后把這個變量+1。如果這個語句是事務的第一條語句，就會把這個query_id給xid。每次sql重啟都會清空global_query_id 。

redo log和binlog 對比

redo log	binlog
物理日志	邏輯日志，有三種格式，比如statement記錄的就是sql語句
innodb引擎特有，用于崩潰恢復	mysql上的歸檔日志，主要用于主從復制
循環寫入	順序追加記錄，追加寫不會覆蓋

配置

sync_binlog:

0：就寫到binlog buffer中，等待定時刷新

1：事務提交立即刷新 // 推薦

N：提交累積N個后刷新

兩階段提交協議

兩階段提交協議保證了redo log和binlog的一致性；

崩潰時機

在流程圖的時機A崩潰：redo log處于prepare狀態，未寫入binlog: 服務重新啟動時，認為事務提交失敗，回滾事務

在流程圖的時機B崩潰：redo log處于prepare狀態，寫入binlog完成：服務重新啟動時，認為事務提交成功，回放事務，將redo log prepare狀態改為commit

在流程圖的時機C崩潰: redo log若處于prepare狀態，同時機B；若處于commit狀態，則完成事務；

WAL保證了崩潰數據不丟失，prepare狀態的引入，保證了事務提交的一致性。