MySQL實戰45講之基礎篇

前言

本文主要記錄學習MySQL實戰45講之基礎篇程序中一些新的識訓，以及總結主要內容，其中包括SQL如何運行、日志系統、事務隔離、索引和鎖等，

基礎架構

連接器，建立連接的程序中，連接器會查詢用戶的權限，本次之后的操作都依賴此時查詢出來的權限，意味著，即使你用管理員賬號修改了用戶的權限，也不會影響已經存在的連接，

查詢快取，大部分場景下查詢快取都是弊大于利，因為一旦表發生修改，快取就會失效，除非是靜態表，即永遠不發生修改的，并且，MySQL8.0已經將該功能移除，

分析器，對SQL進行詞法分析、語法分析，

優化器，通過分析器知道了要干什么，優化器則是選擇干的方案，比如說選擇哪個索引來執行？全表掃描還是索引掃描再回表？等等，

執行器，開始執行SQL，在執行之前，會先驗證用戶是否有查詢該表的權限，
為什么不在之前驗證表查詢權限呢？因為此處不僅要驗證表權限，可能還有觸發器權限，這個必須在運行時才能確認，

日志系統

redo log

1、用于崩潰恢復，由InnoDB引擎提供，
2、物理日志，記錄“在某個資料頁上做了什么修改”，通過redo log實作執行SQL之后，不需要馬上刷到磁盤，而是先寫日志，即WAL（Write Ahead Logging）技術，
3、日志固定大小，通過兩個指標回圈寫，比如 0 ~ write_pos ~ check_point ~ end，write_pos ~ check_point之間的為可寫空間，check_point之后回圈到write_pos為待重繪到磁盤的資料，當write_pos追到check_point時會停止寫，先將check_point后的資料刷到磁盤，

bin log

1、用于操作歸檔及主從同步，由Server層提供，任何引擎都可以使用，
2、邏輯日志，SQL模式，記錄執行的SQL陳述句；row模式，記錄更新前和更新后的行記錄，
3、單個日志檔案有最大值，滿了后創建新的追加寫，

總結

redo log和bin log使用兩階段提交來保證兩個日志的一致性，此外，通過全量備份和bin log可以恢復到之前任意時刻的資料庫狀態，

那每周一備和每日一備如何選擇？
每周一備，資料恢復時間大概率更長，但需要的存盤空間更少；而每日一備，資料的恢復時間更短，但需要的存盤空間更多，如何選擇，則看業務的重要性考慮，

隔離原理

在可重復讀隔離級別下，對于同一個值，不同時刻啟動的事務可能讀取到不同的值，也叫快照讀，這個同一條記錄多個版本就是多版本并發控制MVCC，不同版本的記錄即回滾日志，是在undo log中存盤的，

當然，這個回滾日志不能一直存在，如果沒有比這潭訓滾日志更早的視圖時，日志就會被洗掉，insert操作在事務提交后會直接洗掉，update和delete操作會寫到undo log list中，當判定不會被使用后，要么重用，要么放到洗掉串列等待purge執行緒清除，

由上可知，長事務存在一個弊端就是會有很多老視圖存在資料庫中占據存盤空間，所以，建議開啟事務的自動提交set autocommit=1，如果想避免每次開啟事務的互動，可以考慮用commit work and chain優化，提交事務并啟動下一個事務，

索引結構

哈希表，只適用于等值查詢的場景，比如Nosql資料庫等，對于范圍查詢必須全表掃描，

有序陣列，只從查詢效率上考慮的話是很好的結構，但是插入洗掉時需要移動元素，所以，只適用于靜態存盤的場景，即從不發生改變的表，

樹，增刪改查效率都比較好，由于對于同樣大小的資料，二叉樹高度遠高于N叉樹，所以為了減少查詢時的IO讀取，選擇的是N叉樹，且N差不多為1200，這樣的樹第4層的節點就有上億個，即大部分存盤場景只需要3~4層就可以滿足，并且，一般會提前將1 or 2層先加載到記憶體，

主鍵索引選擇

主鍵索引一般默認選擇自增主鍵，原因如下：
1、性能上，自增主鍵插入索引樹，默認是追加，可以有效地避免頁分裂，頁分裂需要創建新的頁，并拷貝資料，必然影響性能，
2、存盤空間上，自增主鍵一般只占用4 or 8位元組，采用其他像字串一樣的作為索引需要更多的存盤空間，并且，主鍵值型別占用空間越多，普通索引的占用空間也越大，

重建索引

為什么需要重建索引？
因為洗掉資料或頁分裂，可能導致索引出現空洞，雖然后續操作會盡可能地填補空洞，但始侄訓存在存盤空間的浪費，而重建索引后，可以讓資料緊湊，消除資料空洞，提高空間利用率，

不過注意，如果要重建主鍵索引，默認會洗掉所有的索引樹再重新創建，此時，可以考慮用Alter Table t Engine = InnoDB，

全域鎖

全域鎖，就是對資料庫物件上鎖，MySQL支持全域讀鎖，Flush Table With Read Lock（FTWRL），上鎖后會阻塞增刪改操作，DDL操作（創建表、修改表結構等）和更新類事務操作，

全域鎖的一個典型場景是全量備份，備份期間，業務停止作業且主從庫之間資料同步停止，這是比較低效的，不過，這是不支持事務的存盤引擎使用的備份方式，對于支持事務的引擎，可以通過在可重復讀隔離級別下，開啟單個事務來備份，即不用上鎖就可以實作一致性讀，

表級鎖

表級鎖，分為兩種表鎖和元資料鎖（meta data lock，MDL），表鎖就是對表資料顯式上鎖和釋放，避免同時對表資料修改；而元資料鎖，則是隱式對表結構上鎖和釋放，分為讀鎖和寫鎖，讀取資料時上讀鎖，修改表結構上寫鎖，讀寫和寫寫互斥，避免讀取資料時其他事務修改表結構，

注意，如果在一個事務中，先出現DML讀取資料，再進行DDL修改表結構，則會阻塞后續的所有讀寫操作，

那如何安全地給小表加欄位？
1、如果有長事務存在，考慮先暫停DDL或者kill掉這個長事務，
2、如果修改的表是熱點表，并且不得不加欄位，此時kill基本沒用，考慮給DDL設定等待時間，失敗了等一段時間再重試吧，

行鎖

在一個事務中對某一行加鎖并處理完后，并不會馬上釋放鎖，而是遵循兩階{段鎖協議，所以，為了盡量減少鎖沖突，盡量讓可能發生鎖沖突的操作在事務最末尾執行，

1. 一階段鎖協議
   直接嘗試一次性獲取所有鎖資源，如果其中一個獲取失敗，就不執行事務，并在事務尾端釋放所有資源，
   一階段鎖協議解決了死鎖問題，但事務并發度不高，

2. 兩階段鎖協議
   整個事務分為兩個階段，第一階段進行上鎖，可以處理資料，但不能釋放鎖；第二階段開始釋放鎖，也可以處理資料，但不能再加鎖，
   兩階段鎖協議的并發度較高，因為釋放鎖不必在事務末尾，但它沒有解決死鎖問題，因為加鎖階段沒有順序要求，

死鎖

因為MySQL采用兩段鎖協議進行加鎖，如果加鎖順序不合理時，會產生死鎖，解決辦法有兩種，鎖超時和死鎖檢測，

鎖超時

如果因為發生死鎖一直鎖等待，到達超時時間后會自動回滾超時事務，MySQL默認設定了鎖等待超時時間，innodb_lock_wait_timeout=50s，并且通過下面陳述句操作：

查看：SHOW GLOBAL VARIABLES LIKE "innodb_lock_wait_timeout";
設定：SET GLOBAL innodb_lock_wait_timeout=1500;

死鎖檢測

MySQL Server層提供的自動檢測機制，當發現兩個或多個事務形成死鎖時，會回滾其中一個或多個較小代價的事務，并且，MySQL默認開啟了死鎖檢測（innodb_deadlock_detect=on），

當事務并發量大時，死鎖檢測十分損耗CPU，

那如何處理熱點行更新導致的性能問題？

首先，為什么會有性能問題呢？因為熱點行可能存在同一時刻大量事務更新同一個行，此時會出現大量鎖等待，并觸發死鎖檢測，每個死鎖檢測都是O（n）的時間復雜度，導致損耗大量的CPU資源，

第一個頭痛醫頭的方法是，如果可以確認不會發生死鎖，可以考慮關閉死鎖檢測，但這一般不采用，

第二個方法是減少并發度，避免同一時刻觸發太多的死鎖檢測，比如控制同一時刻每行最多只有10個執行緒在更新，或者將原先的一行拆分成多行，這樣就可以將原先的并發量縮減為原來的1/n，但視業務場景，可能要考慮拆分成多行造成的副作用，做一個詳細方案的設計，

快照讀和當前讀

快照讀

快照讀是可重復讀隔離級別下默認的查詢方式，在每個事務啟動時，都會先創建一份讀視圖，根據視圖讀取資料，

這個視圖實際上就是一個事務id陣列，表示當前事務啟動時，全庫范圍內“活躍”的未提交事務id，每個事務都有一個唯一的事務id，由InnoDB事務系統在事務創建前分配，并且按申請順序嚴格遞增，

此外，真實的快照資料存盤在undo log中，在事務更新行記錄前，都會在undo log中存盤歷史版本資料，并記錄上當前事務的id，表示row trx_id，從而，通過當前執行的事務id和行的歷史版本中的row trx_id比較，就可以判斷哪些資料可見，

undo log是邏輯日志，存盤的是與更新時相反的邏輯，就是下圖中的U1、U2、U3，而V1、V2、V3、V4是不存在的，需要臨時計算出來，

可見性結論：
1、版本未提交，不可見，
2、版本已提交，但是在視圖創建后提交的，不可見，
3、版本已提交，在視圖創建前提交，可見，

當前讀

當前讀用于更新陳述句，或者加鎖的查詢操作（SELECT * FROM t lock in share mode或者SELECT * FROM for update，分別加了共享鎖和排他鎖），讀取資料時會獲取最新版本，

總結而言，可重復讀依賴快照讀實作，當要更新資料時，則采用當前讀，

另外，提交讀隔離級別下也用到了視圖，不過與可重復讀創建視圖的時機不同，可重復讀下，只在事務啟動時創建視圖，提交讀則是每次執行陳述句前都創建一次視圖，

參考

• [1] MySQL 45講
• [2] MySQL undo log日志

標籤：MySQL

上一篇：MySQL全域鎖、表鎖、行鎖決議

下一篇：MySQL全域鎖、表鎖、行鎖決議