01 | 基礎架構:一條SQL查詢語句是如何執行的?
1. MySQL 可以分為 Server層?和?存儲引擎層?兩部分。Server 層包括連接器、查詢緩存、分析器、優化器、執行器等。存儲引擎層支持 InnoDB、MyISAM等.
(1)?連接器:管理連接,權限認證。
(2)?查詢緩存:命中則直接返回結果。
(3) 分析器:詞法分析,語法分析。
(4) 優化器:生成執行計劃,選擇索引。
(5) 執行器:操作引擎,返回結果。
(6) 存儲引擎:存儲數據,提供讀寫接口。
2. 臨時內存累積問題: 在執行過程中臨時使用的內存會保留在連接對象里面,在連接斷開的時候才釋放。所以長連接累積下來,會導致內存占用太大,被系統強行殺掉(OOM)。
? ? 解決辦法:可以執行 mysql_reset_connection 來重新初始化連接資源,釋放內存。而且它不需要重連和重做權限驗證。
3.?查詢緩存往往弊大于利,MySQL 8.0 版本將查詢緩存的功能刪掉了。
02 | 日志系統:一條SQL更新語句是如何執行的?
1. MySQL 最重要的兩個日志:物理日志 redo log 和邏輯日志 binlog。
2.?redo log 相關:
(1) 作用:避免每一次update語句都需要查找磁盤,降低 IO 成本。
(2) 簡述:處理update語句時,不查找對應的記錄去寫入,而是把數據寫進內存和日志,并把日志本身持久化到磁盤。之后空閑了再把日志對應的數據同步到磁盤。
(3) WAL 技術,關鍵點就是先寫日志,再寫磁盤。 應用于 redo log。
(4) redo log寫入方式:環形方式,先從頭開始寫,寫到末尾就又回到開頭循環寫。
(5)?redo log的數據是寫入磁盤的,所以可以保證即使數據庫發生異常重啟,之前提交的記錄都不會丟失,這個能力稱為 crash-safe。
2. binlog 相關:
(1) 作用:記錄原始的SQL語句,用于數據恢復操作。
3.?redo log 和 binlog 的區別:
(1)?redo log 是為了降低IO成本;binlog 是為了可以恢復數據。
(2)?redo log 是 屬于SQL的存儲引擎層(InnoDB )的;binlog 是屬于 MySQL 的 Server 層的。
(3)?redo log 記錄的是“在某個數據頁上做了什么修改”;binlog 記錄的是這個語句的原始邏輯。
(4)?redo log 是循環寫的,空間用完后會擦除數據;binlog 是追加寫入的,并不會擦除以前的日志。
4.?update 語句基于redo log和binlog相關的內部流程:
(1) 在內存查找相關數據,不在內存的話就從硬盤讀取。
(2) 根據update語句,在內存中修改數據。
(3) 把操作記錄寫入到 redo log 里。redo log 設置為 prepare 狀態。
(4)?生成這個操作的 binlog,并把 binlog 寫入磁盤。
(5) 把redo log 設置成commit狀態。
(6) 如果在上面(4)(5)步驟時出現了系統掛掉的情況,那么在系統重啟時,會接著做完(4)(5)步驟。
作用:上面的“兩階段提交模式”,保證了任何情況下,redo log 和 binlog都是同步一致的。
03 | 事務隔離:為什么你改了我還看不見?
1.?事務是在MySQL的引擎層實現的。原生的 MyISAM 引擎不支持事務,是 MyISAM 被 InnoDB 取代的重要原因之一。
2. 有多個事務同時執行的時候,可能出現臟讀、幻讀的問題。SQL通過 “隔離級別”的概念,來解決這些問題。
3.?SQL 標準的事務隔離級別包括:讀未提交、讀提交、可重復讀?和 串行化。
(1)?讀未提交:一個事務 還沒提交,它做的變更,在別的事務里 已經生效。
(2)?讀提交:一個事務 提交之后,它做的變更,在別的事務里 才生效。
(3)?可重復讀:一個事務執行過程中看到的數據,與事務在啟動時的數據是一致的。未提交的變更,對其他事務是不生效的。
(4)?串行化:對于同一行記錄,“寫”會加“寫鎖”,“讀”會加“讀鎖”。后訪問的事務必須等前一個事務執行完成,才能繼續執行。
4.??在實現上,數據庫里會創建一個視圖,訪問時以視圖的結果為準。
(1)?讀未提交:沒有視圖概念,直接返回記錄上的最新值。
(2)?讀提交是指:視圖是在每個 SQL 語句開始執行時創建的。
(3)?可重復讀是指:視圖在事務啟動時創建,整個事務存在期間都用這個視圖。
(4)?串行化:沒有視圖概念,用加鎖的方式來避免并行訪問。
5. MySql的事務隔離級別是可重復讀,Oracle是讀提交。做數據遷移時要注意修改。
6.?事務隔離的實現:每條記錄在更新的時候,都會同時記錄一條回滾操作。通過這些回滾操作,可以得到前一個狀態的值。也就可以獲得各個隔離級別狀態下的值。
7.?回滾日志帶來的問題:當沒有事務再需要用到這些回滾日志時,回滾日志才會被刪除。時間很長的事務,會導致大量占用存儲空間。應盡量避免使用長事務。
8.?建議通過顯式語句來啟動事務(begin 或 start transaction),具體是使用 set autocommit=1。