MySQL實戰45講（06--10）-筆記

目錄

• 06 | 全局鎖和表鎖 ：給表加個字段怎麼有這麼多阻礙？
  • 全局鎖
  • 表級鎖
  • 小結
• 07 | 行鎖功過：怎麼減少行鎖對性能的影響？
  • 死鎖和死鎖檢測
• 08 | 事務到底是隔離的還是不隔離的？
  • “快照”在 MVCC 裡是怎麼工作的？
  • 更新邏輯
• 09 | 普通索引和唯一索引，應該怎麼選擇？
  • 查詢過程
  • 更新過程
  • change buffer 的使用場景
  • 索引選擇和實踐
  • change buffer 和 redo log
• 10 | MySQL為什麼有時候會選錯索引？
  • 優化器的邏輯
  • 索引選擇異常和處理

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

MySQL 提供了一個加全局讀鎖的方法，指令是Flush tables with read lock (FTWRL)。

當你需要MySQL處于隻讀狀态，其他線程的語句會阻塞。

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

MySQL 裡面表級别的鎖有兩種：一種是表鎖，一種是中繼資料鎖（meta data lock，MDL)。

表鎖的文法是 lock tables … read/write。

例如： lock tables t1 read, t2 write;

則其他線程寫t1、讀寫 t2 的語句都會被阻塞。

線程 在執行 unlock tables 之前，也隻能執行讀 t1、寫 t2 的操作。連讀 t2 都不允許，自然也不能通路其他表。

另一類表級的鎖是 MDL（metadata lock)。

MDL 不需要顯式使用，在通路一個表的時候會被自動加上。MDL 的作用是，保證讀寫的正确性。

在 MySQL 5.5 版本中引入了 MDL，當對一個表做增删改查操作的時候，加 MDL 讀鎖；

當要對表做結構變更操作的時候，加 MDL 寫鎖。

1. 讀鎖之間不互斥，是以你可以有多個線程同時對一張表增删改查。
2. 讀寫鎖之間，寫鎖之間是互斥的，用來保證變更表結構操作的安全性。

是以，如果有兩個線程要同時給一個表加字段，其中一個要等另一個執行完才能開始執行。（MDL 鎖是系統預設會加）

一個小坑：給一個小表加個字段，導緻整個庫挂了。

A啟動，對t加MDL讀鎖----》B需要MDL讀鎖，正常----》C會blocked，因為A沒有釋放，而C要MDL的寫鎖----》之後所有的新申請也鎖住了----》假如用戶端有重試機制----》線程很快就會爆

如何安全地給小表加字段？

比較理想的機制是，在 alter table 語句裡面設定等待時間，如果在這個指定的等待時間裡面能夠拿到 MDL 寫鎖最好，拿不到也不要阻塞後面的業務語句，先放棄。

	建議你選擇使用–single-<transaction 參數
表鎖	一般是在資料庫引擎不支援行鎖的時候才會被用到的

行鎖就是針對資料表中行記錄的鎖

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

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

當出現死鎖以後，有兩種政策：

1. 一種政策是，直接進入等待，直到逾時。這個逾時時間可以通過參數innodb_lock_wait_timeout 來設定。
2. 另一種政策是，發起死鎖檢測，發現死鎖後，主動復原死鎖鍊條中的某一個事務，讓其他事務得以繼續執行。将參數 innodb_deadlock_detect 設定為 on，表示開啟這個邏輯。

怎麼解決由這種熱點行更新導緻的性能問題呢？

一個思路是控制并發度，這個并發控制要做在資料庫服務端，修改 MySQL 源碼。

不能實作這樣的方案，可以從設計上優化，考慮通過将一行改成邏輯上的多行來減少鎖沖突。

在 MySQL 裡，有兩個“視圖”的概念：

1. 一個是 view。它是一個用查詢語句定義的虛拟表，在調用的時候執行查詢語句并生成結果。建立視圖的文法是 create view，而它的查詢方法與表一樣。
2. 另一個是 InnoDB 在實作 MVCC 時用到的一緻性讀視圖，即 consistent read view，用于支援 RC（Read Committed，讀送出）和 RR（Repeatable Read，可重複讀）隔離級别的實作。

它沒有實體結構，用來在事務執行期間定義“我能看到什麼資料”。

InnoDB 裡面每個事務有一個唯一的事務 ID，叫作 transaction id。它是在事務開始的時候向InnoDB 的事務系統申請的，是按申請順序嚴格遞增的。

虛線箭頭，就是 undo log（復原日志），V1、V2、V3 并不是實體上真實存在的，而是每次需要的時候根據目前版本和 undo log 計算出來的。

InnoDB 代碼實作上，一個事務隻需要在啟動的時候，找到所有已經送出的事務 ID 的最大值，記為 up_limit_id；

然後聲明說，“如果一個資料版本的 row trx_id 大于 up_limit_id，我就不認，我必須要找到它的上一個版本”。

當然，如果一個事務自己更新的資料，它自己還是要認的。

InnoDB 利用了“所有資料都有多個版本”的這個特性，實作了“秒級建立“快照”的能力。

例子：

mysql> CREATE TABLE `t` (     `id` int(11) NOT NULL,     `k` int(11) DEFAULT NULL,     PRIMARY KEY (`id`)     ) ENGINE=InnoDB;     insert into t(id, k) values(1,1),(2,2);           

Q1傳回的是3---------Q2傳回的是1

好，現在事務 A 要來讀資料了，它的 up_limit_id 是 99。當然了，讀資料都是從目前版本讀起的。是以，Q2 的讀資料流程是這樣的：

• 找到 (1,3) 的時候，判斷出 row trx_id=101 大于 up_limit_id，要不起；
• 接着，找到上一個曆史版本，一看 row trx_id=102，還是要不起；
• 再往前找，終于找到了（1,1)，它的 row trx_id=90，是可以承認的資料。

這樣執行下來，事務 A 讀到的這個資料，跟它在剛開始啟動的時候讀到的相同，是以我們稱之為一緻性讀。

這裡你可以順便再想一個問題。(1,1) 這個曆史版本，什麼時候可以被删除掉呢？

答案是，當沒有事務再需要它的時候，就可以删掉。

事務 B 的 update 語句，讀的到底是哪個版本？

在更新的時候，目前讀取到的資料是 (1,2)，更新後生成了新版本的資料 (1,3)，這個新版本的 row trx_id 是 101。

更新資料都是先讀後寫的，而這個讀，隻能讀目前的值，稱為“目前讀（current read）。

下面這兩個 select 語句，分别加了讀鎖（S 鎖，共享鎖）和寫鎖（X 鎖，排他鎖）。

mysql> select k from t where id=1 lock in share mode;     mysql> select k from t where id=1 for update;           

現在，我們再回到文章開頭的問題：事務的可重複讀的能力是怎麼實作的？

可重複讀的核心就是一緻性讀（consistent read）；而事務更新資料的時候，隻能用目前讀。

如果目前的記錄的行鎖被其他事務占用的話，就需要進入鎖等待。

而讀送出的邏輯和可重複讀的邏輯類似，它們最主要的差別是：

• 在可重複讀隔離級别下，隻需要在事務開始的時候找到那個 up_limit_id，之後事務裡的其他查詢都共用這個 up_limit_id；
• 在讀送出隔離級别下，每一個語句執行前都會重新算一次 up_limit_id 的值。

事務 A 的 Q2 語句開始執行的時候，由于事務 B（101）、C（102）都已經送出     是以 Q2 的 up_limit_id 的值就應該是事務 C 的 transaction id，     即 102。那麼，它在讀到（1,3)的時候，就滿足了 up_limt_id(102) ≥row trx_id(101) 的條件，是以傳回了 k=3。     顯然地，語句 Q1 的查詢結果 k=3。           

InnoDB 的行資料有多個版本，每個資料版本有自己的 row trx_id，每個事務或者語句有自己up_limit_id。

普通查詢語句是一緻性讀，一緻性讀會根據 row trx_id 和 up_limit_id 的大小決定資料版本的可見性。

• 對于可重複讀，查詢隻承認在事務啟動前就已經送出完成的資料；
• 對于讀送出，查詢隻承認在語句啟動前就已經送出完成的資料；

而目前讀，總是讀取已經送出完成的最新版本。

假設，執行查詢的語句是 select id from T where k=5。這個查詢語句在索引樹上查找的過程，先是通過 B+ 樹從樹根開始，按層搜尋到葉子節點，也就是圖中右下角的這個資料頁，然後可以認為資料頁内部通過二分法來定位記錄。

先介紹一下change buffer

是可以持久化的資料，在記憶體中有拷貝，也會被寫入到磁盤上。

當需要更新一個資料頁時，如果資料頁在記憶體中就直接更新，

而如果這個資料頁還沒有在記憶體中的話，在不影響資料一緻性的前提下，InooDB 會将這些更新操作緩存在 change buffer 中。

change buffer 用的是 buffer pool 裡的記憶體，是以不能無限增大。change buffer 的大小，可以通過參數 innodb_change_buffer_max_size 來動态設定。

什麼條件下可以使用 change buffer 呢？

對于唯一索引來說，所有的更新操作都要先判斷這個操作是否違反唯一性限制。判斷唯一的時候就需要将資料頁讀入記憶體，是以沒有必要使用change buffer。

實際上也隻有普通索引可以使用。

如果要在這張表中插入一個新記錄的話，InnoDB 的處理流程是怎樣的？

1. 第一種情況是，這個記錄要更新的目标頁在記憶體中。

   普通索引和唯一索引對更新語句性能影響的差别，隻是一個判斷沖突，隻會耗費微小的CPU 時間。

2. 第二種情況是，這個記錄要更新的目标頁不在記憶體中。

   這時，InnoDB 的處理流程如下：

   對于唯一索引來說，需要将資料頁讀入記憶體，判斷到沒有沖突，插入這個值，語句執行結束；

   對于普通索引來說，則是将更新記錄在 change buffer，語句執行就結束了。

将資料從磁盤讀入記憶體涉及随機 IO 的通路，是資料庫裡面成本最高的操作之一。change buffer 因為減少了随機磁盤通路，是以對更新性能的提升是會很明顯的。

寫多讀少的業務來說，頁面在寫完以後馬上被通路到的機率比較小，此時 change buffer 的使用效果最好。

這種業務模型常見的就是賬單類、日志類的系統。

更新模式是寫入之後馬上會做查詢，

那麼即使滿足了條件，将更新先記錄在 change buffer，但之後由于馬上要通路這個資料頁，會立即觸發 purge 過程。這樣随機通路 IO 的次數不會減少，反而增加了 change buffer 的維護代價。

我建議你盡量選擇普通索引，

如果所有的更新後面，都馬上伴随着對這個記錄的查詢，那麼你應該關閉 change buffer。而在其他情況下，change buffer 都能提升更新性能。

redo log 主要節省的是随機寫磁盤的 IO 消耗（轉成順序寫），

而 change buffer 主要節省的則是随機讀磁盤的 IO 消耗。

就是優化器還是有點”笨“

選擇索引是優化器的工作

掃描行數是怎麼判斷的？

MySQL 在真正開始執行語句之前，并不能精确地知道滿足這個條件的記錄有多少條，而隻能根據統計資訊來估算記錄數。這個統計資訊就是索引的“區分度”。

顯然，一個索引上不同的值越多，這個索引的區分度就越好。而一個索引上不同的值的個數，我們稱之為“基數”（cardinality）。也就是說，這個基數越大，索引的區分度越好。

MySQL 是怎樣得到索引的基數的呢？

“采樣統計”

在 MySQL 中，有兩種存儲索引統計的方式，可以通過設定參數 innodb_stats_persistent 的值來選擇：     設定為 on 的時候，表示統計資訊會持久化存儲。這時，預設的 N 是 20，M 是 10。     設定為 off 的時候，表示統計資訊隻存儲在記憶體中。這時，預設的 N 是 8，M 是 16。           

統計資訊不對，那就修正。analyze table

表名

指令，通過 analyze 指令可以解決很多問題，也有例外，後面說。

一種方法是，用 force index 強行選擇一個索引。（ force index 最主要的問題還是變更的及時性）

第二種方法就是，我們可以考慮修改語句，引導 MySQL 使用我們期望的索引。（例如：order by b limit 1 和 order by b,a limit 1）

第三種方法是，在有些場景下，我們可以建立一個更合适的索引，來提供給優化器做選擇，或删掉誤用的索引。