《MySQL實戰45講》(1-7)筆記

《MySQL實戰45講》筆記

目錄

• 《MySQL實戰45講》筆記
  • 第一節: 基礎架構：一條SQL查詢語句是如何執行的？
    • 聯結器
    • 查詢快取
    • 分析器
    • 優化器
    • 執行器
  • 第二節：日誌系統：一條SQL更新語句是如何執行的？
    • 重要的日誌模組：redo log
    • 重要的日誌模組：binlog
    • 兩階段提交
    • 總結
  • 第三節：03.事務隔離：為什麼你改了我還看不見？
    • 隔離性與隔離級別
    • 事務隔離的實現
    • 事務的啟動方式
    • 如何避免長事務
  • 第四節： 深入淺出索引（上）
  • 第五節： 深入淺出索引（下）
  • 第六節： 全域性鎖和表鎖 ：給表加個欄位怎麼有這麼多阻礙
  • 第七節： 行鎖功過：怎麼減少行鎖對效能的影響？

第一節: 基礎架構：一條SQL查詢語句是如何執行的？

1. MySQL的基本架構示意圖

大體來說，MySQL可以分為Server層和儲存引擎層兩部分。

Server層包括聯結器、查詢快取、分析器、優化器、執行器等，涵蓋MySQL的大多數核心服務 功能，以及所有的內建函式（如日期、時間、數學和加密函式等），所有跨儲存引擎的功能都在 這一層實現，比如儲存過程、觸發器、檢視等。

而儲存引擎層負責資料的儲存和提取。其架構模式是外掛式的，支援InnoDB、MyISAM、 Memory等多個儲存引擎。

現在最常用的儲存引擎是InnoDB，它從MySQL 5.5.5版本開始成為了 預設儲存引擎。

• 聯結器

  連線命令寫法如下，輸入完命令後在互動對話裡面輸入密碼。也可以直接在-p後面寫在命令列中

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

  使用show processlist命令查詢當前連線狀態

  客戶端如果太長時間沒動靜，聯結器就會自動將它斷開。這個時間是由引數wait_timeout控制 的，預設值是8小時。

  建立連線的過程通常是比較複雜的，儘量減少建立連線的動作，使用長連線。MySQL在執行過程中使用的記憶體是管理在連線物件裡的，只有在連線斷開的時候才會釋放。所以長連線容易導致記憶體問題。

  如何解決長連線導致的記憶體問題：

  • 定期斷開長連線
  • 對於MySQL 5.7或更新的版本，在執行較大的操作後，通過執行mysql_reset_connection來重新初始化連線資源。這個過程不需要重連和重新做許可權驗證， 但是會將連線恢復到剛剛建立完時的狀態。

• 查詢快取

  MySQL拿到一個查詢請求後，先會在快取中查詢，找不到就會繼續後面的執行階段。執行完成後，結果會存入到查詢快取中。

  由於對錶的更新操作會導致快取清空，對於更新壓力大的資料庫，查詢快取的命令率比較低。對於靜態表，不更新或者查詢很多更新很少的表，才適合使用快取查詢。MySQL也提供了這種“按需使用”的方式。你可以將引數query_cache_type設定成DEMAND，這樣對於預設的SQL語句都不使用查詢快取。而對於你確定要使用查詢快取的語句，可以用SQL_CACHE顯式指定select SQL_CACHE * from T where ID=10；

  MySQL 8.0版本已經將查詢快取的整塊功能刪掉了，之後的版本都沒有查詢快取了

• 分析器

  先做"詞法分析"，再做"語法分析"，判斷輸入的SQL是否滿足MySQL語法

• 優化器

  在表裡面有多個索引的時候，決定使用哪個索引；或者在一個語句有多表關聯（join） 的時候，決定各個表的連線順序。

• 執行器

  先判斷一下你對當前操作的表有沒有執行查詢的許可權。有許可權，就開啟表繼續執行。開啟表的時候，執行器就會根據表的引擎定義，去使用這個引 擎提供的介面。

  select * from T where ID=10;

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

  1. 呼叫InnoDB引擎介面取這個表的第一行，判斷ID值是不是10，如果不是則跳過，如果是則 將這行存在結果集中；
  2. 呼叫引擎介面取“下一行”，重複相同的判斷邏輯，直到取到這個表的最後一行。
  3. 執行器將上述遍歷過程中所有滿足條件的行組成的記錄集作為結果集返回給客戶端。

第二節：日誌系統：一條SQL更新語句是如何執行的？

1. 重要的日誌模組：redo log

   redo log是 InnoDB引擎特有的日誌。

   MySQL裡經常說到的WAL技術，WAL的全稱是WriteAhead Logging，它的關鍵點就是先寫日誌，再寫磁碟。

   當有一條記錄需要更新的時候，InnoDB引擎就會先把記錄寫到redo log裡面，並更新記憶體，這個時候更新就算完成了。同時，InnoDB引擎會在適當的時候，將這個操作記錄更新到磁碟裡面，而這個更新往往是在系統比較空閒的時候做。

   InnoDB的redo log是固定大小的，比如可以配置為一組4個檔案，每個檔案的大小是 1GB，那麼這塊“粉板”總共就可以記錄4GB的操作。從頭開始寫，寫到末尾就又回到開頭迴圈 寫，如下面這個圖所示。

   

   write pos是當前記錄的位置，一邊寫一邊後移，寫到第3號檔案末尾後就回到0號檔案開頭。
   checkpoint是當前要擦除的位置，也是往後推移並且迴圈的，擦除記錄前要把記錄更新到資料檔案。
   write pos和checkpoint之間的是“粉板”上還空著的部分，可以用來記錄新的操作。如果write pos追上checkpoint，這時候不能再執行新的更新，得停下來先擦掉一些記錄，把checkpoint推進一下。
   有了redo log，InnoDB就可以保證即使資料庫發生異常重啟，之前提交的記錄都不會丟失，這個能力稱為crash-safe。

• 重要的日誌模組：binlog

  Server層的日誌，稱為binlog（歸檔日誌）

  兩種日誌有以下三點不同:

  1. redo log是InnoDB引擎特有的；binlog是MySQL的Server層實現的，所有引擎都可以使用。
  2. redo log是物理日誌，記錄的是“在某個資料頁上做了什麼修改”；binlog是邏輯日誌，記錄的是這個語句的原始邏輯，比如“給ID=2這一行的c欄位加1 ”。
  3. redo log是迴圈寫的，空間固定會用完；binlog是可以追加寫入的。“追加寫”是指binlog檔案寫到一定大小後會切換到下一個，並不會覆蓋以前的日誌。

• 執行更新的過程

  建立表： create table T(ID int primary key, c int);

  更新： update T set c=c+1 where ID=2;

  執行器和InnoDB引擎在執行這個簡單的update語 句時的內部流程。

  1. 執行器先找引擎取ID=2這一行。ID是主鍵，引擎直接用樹搜尋找到這一行。如果ID=2這一 行所在的資料頁本來就在記憶體中，就直接返回給執行器；否則，需要先從磁碟讀入記憶體，然 後再返回。
  2. 執行器拿到引擎給的行資料，把這個值加上1，比如原來是N，現在就是N+1，得到新的一行 資料，再呼叫引擎介面寫入這行新資料。
  3. 引擎將這行新資料更新到記憶體中，同時將這個更新操作記錄到redo log裡面，此時redo log處 於prepare狀態。然後告知執行器執行完成了，隨時可以提交事務。
  4. 執行器生成這個操作的binlog，並把binlog寫入磁碟。
  5. 執行器呼叫引擎的提交事務介面，引擎把剛剛寫入的redo log改成提交（commit）狀態更新完成。

  update語句的執行流程圖，圖中淺色框表示是在InnoDB內部執行的，深色框表示是在執行器中執行的

redo log的寫入拆成了兩個步驟：prepare和 commit,這就是"兩階段提交"。

• 兩階段提交

  • 為什麼日誌需要“兩階段提交”

    由於redo log和binlog是兩個獨立的邏輯，如果不用兩階段提交，要麼就是先寫完redo log再寫binlog，或者採用反過來的順序。我們看看這兩種方式會有什麼問題。
    仍然用前面的update語句來做例子。假設當前ID=2的行，欄位c的值是0，再假設執行update語句過程中在寫完第一個日誌後，第二個日誌還沒有寫完期間發生了crash，會出現什麼情況呢？

    1. 先寫redo log後寫binlog。假設在redo log寫完，binlog還沒有寫完的時候，MySQL程式異常重啟。由於我們前面說過的，redo log寫完之後，系統即使崩潰，仍然能夠把資料恢復回來，所以恢復後這一行c的值是1。
       但是由於binlog沒寫完就crash了，這時候binlog裡面就沒有記錄這個語句。因此，之後備份日誌的時候，存起來的binlog裡面就沒有這條語句。然後你會發現，如果需要用這個binlog來恢復臨時庫的話，由於這個語句的binlog丟失，這個臨時庫就會少了這一次更新，恢復出來的這一行c的值就是0，與原庫的值不同。
    2. 先寫binlog後寫redo log。如果在binlog寫完之後crash，由於redo log還沒寫，崩潰恢復以後這個事務無效，所以這一行c的值是0。但是binlog裡面已經記錄了“把c從0改成1”這個日誌。所以，在之後用binlog來恢復的時候就多了一個事務出來，恢復出來的這一行c的值就是1，與原庫的值不同。
       可以看到，如果不使用“兩階段提交”，那麼資料庫的狀態就有可能和用它的日誌恢復出來的庫的狀態不一致。

    簡單說，redo log和binlog都可以用於表示事務的提交狀態，而兩階段提交就是讓這兩個狀態保 持邏輯上的一致。

• 總結

  物理日誌redo log和邏輯日誌binlog

  redo log用於保證crash-safe能力。innodb_flush_log_at_trx_commit這個引數設定成1的時候， 表示每次事務的redo log都直接持久化到磁碟。這個引數建議你設定成1，這樣可以保證 MySQL異常重啟之後資料不丟失。

  sync_binlog這個引數設定成1的時候，表示每次事務的binlog都持久化到磁碟。這個引數也建 議你設定成1，這樣可以保證MySQL異常重啟之後binlog不丟失。

  ---

第三節：03.事務隔離：為什麼你改了我還看不見？

1. 隔離性與隔離級別

   事務的4個特性：ACID（Atomicity、Consistency、Isolation、Durability，即原子性、一致性、隔離性、永續性）

   當資料庫上有多個事務同時執行的時候，就可能出現髒讀（dirty read）、不可重複讀（non- repeatable read）、幻讀（phantomread）的問題，為了解決這些問題，就有了“隔離級別”的概 念。

   隔離得越嚴實，效率就會越低。因此很多時候，我們都要在二者之間尋找一個平衡點。SQL標準的事務隔離級別包括：讀未提交（read uncommitted）、 讀提交（read committed）、可重複讀（repeatable read）和序列化（serializable ）。

   讀未提交是指，一個事務還沒提交時，它做的變更就能被別的事務看到。

   讀提交是指，一個事務提交之後，它做的變更才會被其他事務看到。

   可重複讀是指，一個事務執行過程中看到的資料，總是跟這個事務在啟動時看到的資料是一 致的。當然在可重複讀隔離級別下，未提交變更對其他事務也是不可見的。

   序列化，顧名思義是對於同一行記錄，“寫”會加“寫鎖”，“讀”會加“讀鎖”。當出現讀寫鎖衝突 的時候，後訪問的事務必須等前一個事務執行完成，才能繼續執行。

   • 舉例說明這幾種隔離級別

     假設資料表T中 只有一列，其中一行的值為1，下面是按照時間順序執行兩個事務的行為。

   create table T(c int) engine=InnoDB; 
   
   insert into T(c) values(1)·
   

   

   ​ 若隔離級別是“讀未提交”， 則V1的值就是2。這時候事務B雖然還沒有提交，但是結果已經被 A看到了。因此，V2、V3也都是2。

   ​ 若隔離級別是“讀提交”，則V1是1，V2的值是2。事務B的更新在提交後才能被A看到。所以， V3的值也是2。

   ​ 若隔離級別是“可重複讀”，則V1、V2是1，V3是2。之所以V2還是1，遵循的就是這個要求： 事務在執行期間看到的資料前後必須是一致的。

   ​ 若隔離級別是“序列化”，則在事務B執行“將1改成2”的時候，會被鎖住。直到事務A提交後， 事務B才可以繼續執行。所以從A的角度看， V1、V2值是1，V3 的值是2。

   在實現上，資料庫裡面會建立一個檢視，訪問的時候以檢視的邏輯結果為準。在“可重複讀”隔離 級別下，這個檢視是在事務啟動時建立的，整個事務存在期間都用這個檢視。在“讀提交”隔離級 別下，這個檢視是在每個SQL語句開始執行的時候建立的。這裡需要注意的是，“讀未提交”隔離 級別下直接返回記錄上的最新值，沒有檢視概念；而“序列化”隔離級別下直接用加鎖的方式來避 免並行訪問。

   查詢當前MySQL隔離級別

   show variables like 'transaction_isolation';
   

2. 事務隔離的實現

   在MySQL中，實際上每條記錄在更新的時候都會同時記錄一條回滾操作。記錄上的最新值，通 過回滾操作，都可以得到前一個狀態的值。

   假設一個值從1被按順序改成了2、3、4，在回滾日誌裡面就會有類似下面的記錄。

   

   當前值是4，但是在查詢這條記錄的時候，不同時刻啟動的事務會有不同的read-view。如圖中看 到的，在檢視A、B、C裡面，這一個記錄的值分別是1、2、4，同一條記錄在系統中可以存在多 個版本，就是資料庫的多版本併發控制（MVCC）。對於read-viewA，要得到1，就必須將當前 值依次執行圖中所有的回滾操作得到。

   同時你會發現，即使現在有另外一個事務正在將4改成5，這個事務跟read-viewA、B、C對應的 事務是不會衝突的。

• 回滾日誌什麼時候刪除？

  系統會判斷，當沒有事務再需要用到這些回滾日誌時，回滾日誌會被刪除。什麼時候才不需要了呢？就是當系統裡沒有比這個回滾日誌更早的read-view的時候。

• 為什麼建議儘量不要使用長事務？

  長事務意味著系統裡面會存在很老的事務檢視。由於這些事務隨時可能訪問資料庫裡面的任何數 據，所以這個事務提交之前，資料庫裡面它可能用到的回滾記錄都必須保留，這就會導致大量佔 用儲存空間。

  對回滾段的影響，長事務還佔用鎖資源，也可能拖垮整個庫，

3. 事務的啟動方式

   MySQL的事務啟動方式有以下幾種：

   • 顯式啟動事務語句， begin 或 start transaction。配套的提交語句是commit，回滾語句是 rollback。
   • set autocommit=0，這個命令會將這個執行緒的自動提交關掉。意味著如果你只執行一個 select語句，這個事務就啟動了，而且並不會自動提交。這個事務持續存在直到你主動執行 commit 或 rollback 語句，或者斷開連線。

   有些客戶端連線框架會預設連線成功後先執行一個set autocommit=0的命令。這就導致接下來的 查詢都在事務中，如果是長連線，就導致了意外的長事務。

   建議使用set autocommit=1, 通過顯式語句的方式來啟動事務。

   在autocommit為1的情況下，用begin顯式啟動的事務，如果執行commit則提交事務。如果執行 commit work and chain，則是提交事務並自動啟動下一個事務，這樣也省去了再次執行begin語 句的開銷。

   在information_schema庫的innodb_trx這個表中查詢長事務

   #查詢持續時間超過60s的事務
   select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60
   

4. 如何避免長事務

   • 從應用開發端來看：

     • 確認是否使用了set autocommit=0,觀察general_log

       general log 將所有到達MySQL Server的SQL語句記錄下來。

       一般不會開啟開功能，因為log的量會非常龐大。但個別情況下可能會臨時的開一會兒general log以供排障使用。
       相關引數一共有3：general_log、log_output、general_log_file

       show variables like 'general_log'; -- 檢視日誌是否開啟

       set global general_log=on; -- 開啟日誌功能

       show variables like 'general_log_file'; -- 看看日誌檔案儲存位置

       set global general_log_file='tmp/general.lg'; -- 設定日誌檔案儲存位置

       show variables like 'log_output'; -- 看看日誌輸出型別 table或file

       set global log_output='table'; -- 設定輸出型別為 table

       set global log_output='file'; -- 設定輸出型別為file

     • 確認是否有不必要的只讀事務，只讀事務可以去掉

     • 通過SETMAX_EXECUTION_TIME命令， 來控制每個語句執行的最長時間，避免單個語句意外執行太長時間。

   • 從資料庫端來看：

     • 監控 information_schema.Innodb_trx表，設定長事務閾值，超過就報警/或者kill；
     • Percona的pt-kill這個工具
     • 測試階段要求輸出所有的general_log，分析日誌行為提前發現問題；
     • MySQL 5.6或者更新版本，把innodb_undo_tablespaces(用於設定建立的undo表空間的個數,在mysql_install_db時初始化後，就再也不能被改動了，修改該值會導致MySQL無法啟動)設定成2（或更大的 值）。如果真的出現大事務導致回滾段過大，這樣設定後清理起來更方便。

第四節： 深入淺出索引（上）

1. 索引常見的三種資料結構：雜湊表、有序陣列和搜尋樹。

2. 三種模型的區別：

   • 雜湊表這種結構適用於只有等值查詢的場景。比如Memcached及其他一些NoSQL引 擎。
   • 有序陣列，更新資料的時候往中間插入一個記錄就必須得挪動後面所有的記錄，成本太高。等值查詢和範圍查詢場景中的效能就都非常優秀。只適用於靜態儲存引擎。
   • 搜尋樹比較均衡。

3. InnoDB 的索引模型

   在MySQL中，索引是在儲存引擎層實現的，所以並沒有統一的索引標準，

   InnoDB使用了B+樹索引模型，每一個索引在InnoDB裡面對應一棵B+樹。

   舉例說明：

   #主鍵列為ID的表，表中有欄位k，並且在k上有索引。
   
   create table T(
   id int primary key,
   k int not null,
   name varchar(16),
   index (k))engine=InnoDB;
   

   表中R1~R5(ID,k)值分別為(100,1)、(200,2)、(300,3)、(500,5)和(600,6)

   

   從圖中不難看出，根據葉子節點的內容，索引型別分為主鍵索引和非主鍵索引。

   主鍵索引的葉子節點存的是整行資料。在InnoDB裡，主鍵索引也被稱為聚簇索引（clustered index）。

   非主鍵索引的葉子節點內容是主鍵的值。在InnoDB裡，非主鍵索引也被稱為二級索引 （secondary index）。

   從上面的示例可以看出：

   • 如果語句是select *fromTwhere ID=500，即主鍵查詢方式，則只需要搜尋ID這棵B+樹；
   • 如果語句是select *fromTwhere k=5，即普通索引查詢方式，則需要先搜尋k索引樹，得到ID 的值為500，再到ID索引樹搜尋一次。這個過程稱為回表。

   基於非主鍵索引的查詢需要多掃描一棵索引樹

4. 索引維護

   B+樹為了維護索引有序性，在插入新值的時候需要做必要的維護。以上面這個圖為例，如果插 入新的行ID值為700，則只需要在R5的記錄後面插入一個新記錄。如果新插入的ID值為400，就 相對麻煩了，需要邏輯上挪動後面的資料，空出位置。

   而更糟的情況是，如果R5所在的資料頁已經滿了，根據B+樹的演算法，這時候需要申請一個新的 資料頁，然後挪動部分資料過去。這個過程稱為頁分裂。在這種情況下，效能自然會受影響。

   除了效能外，頁分裂操作還影響資料頁的利用率。原本放在一個頁的資料，現在分到兩個頁中， 整體空間利用率降低大約50%。

   當相鄰兩個頁由於刪除了資料，利用率很低之後，會將資料頁做合併。合 並的過程，可以認為是分裂過程的逆過程。

   自增主鍵是指自增列上定義的主鍵，在建表語句中一般是這麼定義的： NOTNULL PRIMARY KEY AUTO_INCREMENT。

   插入新記錄的時候可以不指定ID的值，系統會獲取當前ID最大值加1作為下一條記錄的ID值。 自增主鍵的插入資料模式，正符合了我們前面提到的遞增插入的場景。每次插入一條 新記錄，都是追加操作，都不涉及到挪動其他記錄，也不會觸發葉子節點的分裂。

   主鍵長度越小，普通索引的葉子節點就越小，普通索引佔用的空間也就越小。 所以，從效能和儲存空間方面考量，自增主鍵往往是更合理的選擇

   ---

   索引可能因為刪除，或者頁分 裂等原因，導致資料頁有空洞，重建索引的過程會建立一個新的索引，把資料按順序插入，這樣 頁面的利用率最高，也就是索引更緊湊、更省空間。

   ---

​

第五節： 深入淺出索引（下）

1. 案例

   執行 select *fromTwhere k between 3 and 5，需要執行幾次樹的搜 索操作，會掃描多少行？

   create table T (
   ID int primary key,
   k int NOT NULL DEFAULT 0,
   s varchar(16) NOT NULL DEFAULT '',
   index k(k))
   engine=InnoDB;
   insert into T values(100,1, 'aa'),(200,2,'bb'),(300,3,'cc'),(500,5,'ee'),(600,6,'ff'),(700,7,'gg');
   

   

   這條SQL查詢語句的執行流程：

   1. 在k索引樹上找到k=3的記錄，取得 ID = 300；
   2. 再到ID索引樹查到ID=300對應的R3；
   3. 在k索引樹取下一個值k=5，取得ID=500；
   4. 再回到ID索引樹查到ID=500對應的R4；
   5. 在k索引樹取下一個值k=6，不滿足條件，迴圈結束

   回到主鍵索引樹搜尋的過程，我們稱為回表。這個查詢過程讀了k 索引樹的3條記錄（步驟1、3和5），回表了兩次（步驟2和4）

在這個例子中，由於查詢結果所需要的資料只在主鍵索引上有，所以不得不回表。那麼，有沒有 可能經過索引優化，避免回表過程呢？

2. 覆蓋索引

   如果執行的語句是select ID fromTwhere k between 3 and 5，這時只需要查ID的值，而ID的值 已經在k索引樹上了，因此可以直接提供查詢結果，不需要回表。也就是說，在這個查詢裡面， 索引k已經“覆蓋了”我們的查詢需求，我們稱為覆蓋索引。

   由於覆蓋索引可以減少樹的搜尋次數，顯著提升查詢效能，所以使用覆蓋索引是一個常用 的效能優化手段。

   需要注意的是，在引擎內部使用覆蓋索引在索引k上其實讀了三個記錄，R3~R5（對應的索引k 上的記錄項），但是對於MySQL的Server層來說，它就是找引擎拿到了兩條記錄，因此MySQL 認為掃描行數是2。

3. 最左字首原則

   B+樹這種索引結構，可以利用索引的“最左字首”，來定位記錄。

   CREATE TABLE `tuser` (
   `id` int(11) NOT NULL,
   `id_card` varchar(32) DEFAULT NULL,
   `name` varchar(32) DEFAULT NULL,
   `age` int(11) DEFAULT NULL,
   `ismale` tinyint(1) DEFAULT NULL,
   PRIMARY KEY (`id`),
   KEY `id_card` (`id_card`),
   KEY `name_age` (`name`,`age`)
   ) ENGINE=InnoDB
   

   

   索引項是按照索引定義裡面出現的欄位順序排序的。

   不只是索引的全部定義，只要滿足最左字首，就可以利用索引來加速檢索。這個最左 字首可以是聯合索引的最左N個欄位，也可以是字串索引的最左M個字元

   在建立聯合索引的時候，如何安排索 引內的欄位順序?

   第一原則是，如果通過調整順序，可 以少維護一個索引，那麼這個順序往往就是需要優先考慮採用的。

   ，name欄位是比age欄位大的 ，建立一個（name,age)的聯合索引和一個(age)的單欄位索引。

4. 索引下推

   #檢索出表中“名字第一個字是張，而且年齡是10歲的所有男孩”
   select * from tuser where name like '張%' and age=10 and ismale=1
   

   執行步驟：

   • 這個語句在搜尋索引樹的時候，只能用 “張”，找到第一個滿足 條件的記錄ID3。

   • 當然是判斷其他條件是否滿足。

     • 在MySQL 5.6之前，只能從ID3開始一個個回表。到主鍵索引上找出資料行，再對比欄位值。
     • MySQL 5.6 引入的索引下推優化（index condition pushdown)， 可以在索引遍歷過程中，對索 引中包含的欄位先做判斷，直接過濾掉不滿足條件的記錄，減少回表次數。

     執行流程圖(每一個虛線箭頭表示回表一次)

     ---

     

     在(name,age)索引裡面我特意去掉了age的值，這個過程InnoDB並不會去看age的值， 只是按順序把“name第一個字是’張’”的記錄一條條取出來回表。因此，需要回表4次。

     ---

     

     InnoDB在(name,age)索引內部就判斷了age是否等於10，對於不等於10的 記錄，直接判斷並跳過。在我們的這個例子中，只需要對ID4、ID5這兩條記錄回表取資料判 斷，就只需要回表2次。

     ---

第六節： 全域性鎖和表鎖 ：給表加個欄位怎麼有這麼多阻礙

​ 根據加鎖的範圍，MySQL裡面的鎖大致可以分成全域性鎖、表級鎖和行鎖三類。

1. 全域性鎖

   全域性鎖就是對整個資料庫例項加鎖。MySQL提供了一個加全域性讀鎖的方法，命令是 Flush tables with read lock (FTWRL)。當你需要讓整個庫處於只讀狀態的時候，可以使用這個命 令，之後其他執行緒的以下語句會被阻塞：資料更新語句（資料的增刪改）、資料定義語句（包括 建表、修改表結構等）和更新類事務的提交語句。

   全域性鎖的典型使用場景是，做全庫邏輯備份

   邏輯備份工具是mysqldump。當mysqldump使用引數–single-transaction的時候，導資料之前就會啟動一個事務，來確保拿到一致性檢視。而由於MVCC的支援，這個過程中資料是可以正常更新的。

   single-transaction方法只適用於所有的表使用事務引擎的庫。如果有的表使用了不 支援事務的引擎，那麼備份就只能通過FTWRL方法。這往往是DBA要求業務開發人員使用 InnoDB替代MyISAM的原因之一。

   全庫只讀，為什麼不使用set global readonly=true的方式呢？

   • 一是，在有些系統中，readonly的值會被用來做其他邏輯，比如用來判斷一個庫是主庫還是備 庫。因此，修改global變數的方式影響面更大，我不建議你使用。
   • 二是，在異常處理機制上有差異。如果執行FTWRL命令之後由於客戶端發生異常斷開，那麼 MySQL會自動釋放這個全域性鎖，整個庫回到可以正常更新的狀態。而將整個庫設定為 readonly之後，如果客戶端發生異常，則資料庫就會一直保持readonly狀態，這樣會導致整個 庫長時間處於不可寫狀態，風險較高。

2. 表級鎖

   表鎖一般是在資料庫引擎不支援行鎖的時候才會被用到的

   MySQL裡面表級別的鎖有兩種：一種是表鎖，一種是後設資料鎖（meta data lock，MDL)。

   • 表鎖的語法是 lock tables …read/write。與FTWRL類似，可以用unlock tables主動釋放鎖， 也可以在客戶端斷開的時候自動釋放。需要注意，lock tables語法除了會限制別的執行緒的讀寫 外，也限定了本執行緒接下來的操作物件。

     舉個例子, 如果在某個執行緒A中執行lock tables t1 read, t2 write; 這個語句，則其他執行緒寫t1、讀 寫t2的語句都會被阻塞。同時，執行緒A在執行unlock tables之前，也只能執行讀t1、讀寫t2的操 作。連寫t1都不允許，自然也不能訪問其他表。

   • 後設資料鎖MDL不需要顯式使用，在訪問一個表的時候會被 自動加上。MDL的作用是，保證讀寫的正確性。

   在MySQL 5.5版本中引入了MDL，當對一個表做增刪改查操作的時候，加MDL讀鎖；當 要對錶做結構變更操作的時候，加MDL寫鎖。

   • 讀鎖之間不互斥，因此你可以有多個執行緒同時對一張表增刪改查。
   • 讀寫鎖之間、寫鎖之間是互斥的，用來保證變更表結構操作的安全性。因此，如果有兩個線 程要同時給一個表加欄位，其中一個要等另一個執行完才能開始執行。

3. 示例

   

   session A先啟動，這時候會對錶t加一個MDL讀鎖。由於session B需要的也是 MDL讀鎖，因此可以正常執行。

   之後session C會被blocked，是因為session A的MDL讀鎖還沒有釋放，而session C需要MDL寫 鎖，因此只能被阻塞。

   如果只有session C自己被阻塞還沒什麼關係，但是之後所有要在表t上新申請MDL讀鎖的請求也 會被session C阻塞。前面我們說了，所有對錶的增刪改查操作都需要先申請MDL讀鎖，就都被 鎖住，等於這個表現在完全不可讀寫了。

   如果某個表上的查詢語句頻繁，而且客戶端有重試機制，也就是說超時後會再起一個新session 再請求的話，這個庫的執行緒很快就會爆滿。

   事務中的MDL鎖，在語句執行開始時申請，但是語句結束後並不會馬上釋 放，而會等到整個事務提交後再釋放。

4. 課後問題：

   備份一般都會在備庫上執行，你在用–single-transaction方法做邏 輯備份的過程中，如果主庫上的一個小表做了一個DDL，比如給一個表上加了一列。這時候，從 備庫上會看到什麼現象呢？

   Q1:SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
   Q2:START TRANSACTION WITH CONSISTENT SNAPSHOT；
   /* other tables */
   Q3:SAVEPOINT sp;
   /* 時刻 1 */
   Q4:show create table `t1`;
   /* 時刻 2 */
   Q5:SELECT * FROM `t1`;
   /* 時刻 3 */
   Q6:ROLLBACK TO SAVEPOINT sp;
   /* 時刻 4 */
   /* other tables */
   

   在備份開始的時候，為了確保RR（可重複讀）隔離級別，再設定一次RR隔離級別(Q1);

   啟動事務，這裡用 WITH CONSISTENT SNAPSHOT確保這個語句執行完就可以得到一個一致性 檢視（Q2)；

   設定一個儲存點，這個很重要（Q3）；

   showcreate 是為了拿到表結構(Q4)，然後正式導資料 （Q5），回滾到SAVEPOINT sp，在這 裡的作用是釋放 t1的MDL鎖 （Q6。當然這部分屬於“超綱”，上文正文裡面都沒提到。

   DDL從主庫傳過來的時間按照效果不同，我打了四個時刻。題目設定為小表，我們假定到達後， 如果開始執行，則很快能夠執行完成。

   參考答案如下：

   1. 如果在Q4語句執行之前到達，現象：沒有影響，備份拿到的是DDL後的表結構。
   2. 如果在“時刻 2”到達，則表結構被改過，Q5執行的時候，報 Table definition has changed, please retry transaction，現象：mysqldump終止；
   3. 如果在“時刻2”和“時刻3”之間到達，mysqldump佔著t1的MDL讀鎖，binlog被阻塞，現象： 主從延遲，直到Q6執行完成。
   4. 從“時刻4”開始，mysqldump釋放了MDL讀鎖，現象：沒有影響，備份拿到的是DDL前的表 結構。

第七節： 行鎖功過：怎麼減少行鎖對效能的影響？

​ MySQL的行鎖是在引擎層由各個引擎自己實現的。但並不是所有的引擎都支援行鎖

1. 從兩階段鎖說起

   在下面的操作序列中，事務B的update語句執行時會是什麼現象呢？假設字 段id是表t的主鍵

   

   這個問題的結論取決於事務A在執行完兩條update語句後，持有哪些鎖，以及在什麼時候釋放。

   實際上事務B的update語句會被阻塞，直到事務A執行commit之後，事務B才 能繼續執行。事務A持有的兩個記錄的行鎖，都是在commit的時候才釋放的。

   在InnoDB事務中，行鎖是在需要的時候才加上的，但並不是不需要了就立刻釋 放，而是要等到事務結束時才釋放。這個就是兩階段鎖協議。

   如果你的事務中需要鎖多個行，要把 最可能造成鎖衝突、最可能影響併發度的鎖儘量往後放。

2. 死鎖和死鎖檢測

   

   這時候，事務A在等待事務B釋放id=2的行鎖，而事務B在等待事務A釋放id=1的行鎖。 事務A和 事務B在互相等待對方的資源釋放，就是進入了死鎖狀態。當出現死鎖以後，有兩種策略：

   • 一種策略是，直接進入等待，直到超時。這個超時時間可以通過引數 innodb_lock_wait_timeout來設定。
   • 另一種策略是，發起死鎖檢測，發現死鎖後，主動回滾死鎖鏈條中的某一個事務，讓其他事 務得以繼續執行。將引數innodb_deadlock_detect設定為on，表示開啟這個邏輯。

   在InnoDB中，innodb_lock_wait_timeout的預設值是50s