《MySQL實戰45講》(8-15)筆記

MySQL實戰45講

目錄

• MySQL實戰45講
  • 第八節: 事務到底是隔離的還是不隔離的？
    • 在MySQL裡，有兩個“檢視”的概念：
    • “快照”在MVCC裡是怎麼工作的？
    • 我們繼續看一下圖1中的三個事務，分析下事務A的語句返回的結果，為什麼是k=1。
    • 更新邏輯
    • 事務的可重複讀的能力是怎麼實現的？
    • 小結
  • 第九節: 普通索引和唯一索引，應該怎麼選擇？
    • 查詢過程
    • 更新過程
    • change buffer的使用場景
    • 索引選擇和實踐
    • change buffer 和 redo log
  • 第十節: MySQL為什麼有時候會選錯索引？
    • 優化器的邏輯
    • 索引選擇異常和處理
  • 第十二節：為什麼我的MySQL會“抖”一下？
  • 第十三節:為什麼表資料刪掉一半，表檔案大小不變？
    • 引數innodb_file_per_table
    • 資料刪除流程
    • 重建表
    • Online 和 inplace
  • 第十四節:count(*)這麼慢，我該怎麼辦？
    • count(*)的實現方式
    • 不同的count用法（基於InnoDB引擎）
  • 第十五節: 答疑文章（一）：日誌和索引相關問題
    • 日誌相關問題

第八節: 事務到底是隔離的還是不隔離的？

示例：

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);

begin/start transaction 命令並不是一個事務的起點，在執行到它們之後的第一個操作InnoDB表 的語句，事務才真正啟動。如果你想要馬上啟動一個事務，可以使用start transaction with consistent snapshot 這個命令。

整個專欄裡面，都是預設 autocommit=1。

事務C沒有顯式地使用begin/commit，表示這個update語句本身就是一個事務， 語句完成的時候會自動提交。事務B在更新了行之後查詢; 事務A在一個只讀事務中查詢，並且時間順序上是在事務B的查詢之後。

事務B查到的k的值是3，而事務A查到的k的值是1,

1. 在MySQL裡，有兩個“檢視”的概念：

   • 一個是view。它是一個用查詢語句定義的虛擬表，在呼叫的時候執行查詢語句並生成結果。 建立檢視的語法是create view…，而它的查詢方法與表一樣。
   • 另一個是InnoDB在實現MVCC時用到的一致性讀檢視，即consistent read view，用於支援 RC（Read Committed，讀提交）和RR（Repeatable Read，可重複讀）隔離級別的實現

   它沒有物理結構，作用是事務執行期間用來定義“我能看到什麼資料”。

2. “快照”在MVCC裡是怎麼工作的？

   在可重複讀隔離級別下，事務在啟動的時候就“拍了個快照”。注意，這個快照是基於整庫的。

   • 這個快照是怎麼實現的

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

     而每行資料也都是有多個版本的。每次事務更新資料的時候，都會生成一個新的資料版本，並且 把transaction id賦值給這個資料版本的事務ID，記為rowtrx_id。同時，舊的資料版本要保留， 並且在新的資料版本中，能夠有資訊可以直接拿到它。

     也就是說，資料表中的一行記錄，其實可能有多個版本(row)，每個版本有自己的rowtrx_id。

     如圖2所示，就是一個記錄被多個事務連續更新後的狀態。

     

     圖中虛線框裡是同一行資料的4個版本，當前最新版本是V4，k的值是22，它是被transaction id 為25的事務更新的，因此它的rowtrx_id也是25。

   • 語句更新會生成undo log（回滾日誌）,那麼，undo log 在哪呢？

     圖2中的三個虛線箭頭，就是undo log；而V1、V2、V3並不是物理上真實存在的，而 是每次需要的時候根據當前版本和undo log計算出來的。比如，需要V2的時候，就是通過V4依 次執行U3、U2算出來。

   • InnoDB是怎麼定義快照 的?

     按照可重複讀的定義，一個事務啟動的時候，能夠看到所有已經提交的事務結果。但是之後，這 個事務執行期間，其他事務的更新對它不可見。

     因此，一個事務只需要在啟動的時候宣告說，“以我啟動的時刻為準，如果一個資料版本是在我 啟動之前生成的，就認；如果是我啟動以後才生成的，我就不認，我必須要找到它的上一個版本”。

     當然，如果“上一個版本”也不可見，那就得繼續往前找。還有，如果是這個事務自己更新的數 據，它自己還是要認的。

     在實現上， InnoDB為每個事務構造了一個陣列，用來儲存這個事務啟動瞬間，當前正在“活 躍”的所有事務ID。“活躍”指的就是，啟動了但還沒提交。

     陣列裡面事務ID的最小值記為低水位，當前系統裡面已經建立過的事務ID的最大值加1記為高水 位。

     這個檢視陣列和高水位，就組成了當前事務的一致性檢視（read-view）。

     而資料版本的可見性規則，就是基於資料的rowtrx_id和這個一致性檢視的對比結果得到的。 這個檢視陣列把所有的rowtrx_id 分成了幾種不同的情況。

     

     這樣，對於當前事務的啟動瞬間來說，一個資料版本的rowtrx_id，有以下幾種可能：

     1. 如果落在綠色部分，表示這個版本是已提交的事務或者是當前事務自己生成的，這個資料是 可見的；
     2. 如果落在紅色部分，表示這個版本是由將來啟動的事務生成的，是肯定不可見的；
     3. 如果落在黃色部分，那就包括兩種情況 a. 若 rowtrx_id在陣列中，表示這個版本是由還沒提交的事務生成的，不可見； b. 若 rowtrx_id不在陣列中，表示這個版本是已經提交了的事務生成的，可見。

     比如，對於圖2中的資料來說，如果有一個事務，它的低水位是18，那麼當它訪問這一行資料 時，就會從V4通過U3計算出V3，所以在它看來，這一行的值是11。

     有了這個宣告後，系統裡面隨後發生的更新，是不是就跟這個事務看到的內容無關了呢？ 因為之後的更新，生成的版本一定屬於上面的2或者3(a)的情況，而對它來說，這些新的資料版 本是不存在的，所以這個事務的快照，就是“靜態”的了。

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

3. 我們繼續看一下圖1中的三個事務，分析下事務A的語句返回的結果，為什麼是k=1。

   這裡，我們不妨做如下假設：

   1. 事務A開始前，系統裡面只有一個活躍事務ID是99；
   2. 事務A、B、C的版本號分別是100、101、102，且當前系統裡只有這四個事務；
   3. 三個事務開始前，(1,1）這一行資料的rowtrx_id是90。

   這樣，事務A的檢視陣列就是[99,100], 事務B的檢視陣列是[99,100,101], 事務C的檢視陣列是 [99,100,101,102]。

   為了簡化分析，我先把其他干擾語句去掉，只畫出跟事務A查詢邏輯有關的操作：

   

   從圖中可以看到，第一個有效更新是事務C，把資料從(1,1)改成了(1,2)。這時候，這個資料的最 新版本的rowtrx_id是102，而90這個版本已經成為了歷史版本。

   第二個有效更新是事務B，把資料從(1,2)改成了(1,3)。這時候，這個資料的最新版本（即row trx_id）是101，而102又成為了歷史版本。

   在事務A查詢的時候，其實事務B還沒有提交，但是它生成的(1,3)這個版本已 經變成當前版本了。但這個版本對事務A必須是不可見的，否則就變成髒讀了。

   好，現在事務A要來讀資料了，它的檢視陣列是[99,100]。當然了，讀資料都是從當前版本讀起 的。所以，事務A查詢語句的讀資料流程是這樣的：

   • 找到(1,3)的時候，判斷出rowtrx_id=101，比高水位大，處於紅色區域，不可見；
   • 接著，找到上一個歷史版本，一看rowtrx_id=102，比高水位大，處於紅色區域，不可見；
   • 再往前找，終於找到了（1,1)，它的rowtrx_id=90，比低水位小，處於綠色區域，可見。

   這樣執行下來，雖然期間這一行資料被修改過，但是事務A不論在什麼時候查詢，看到這行資料 的結果都是一致的，所以我們稱之為一致性讀。

   一個資料版本，對於一個事務檢視來說，除了自己的更新總是可見以外，有三種情況：

   1. 版本未提交，不可見；
   2. 版本已提交，但是是在檢視建立後提交的，不可見；
   3. 版本已提交，而且是在檢視建立前提交的，可見。

   現在，我們用這個規則來判斷圖4中的查詢結果，事務A的查詢語句的檢視陣列是在事務A啟動的 時候生成的，這時候：

   • (1,3)還沒提交，屬於情況1，不可見；
   • (1,2)雖然提交了，但是是在檢視陣列建立之後提交的，屬於情況2，不可見；
   • (1,1)是在檢視陣列建立之前提交的，可見。

4. 更新邏輯

   事務B的update語句，如果按照一致性讀，好像結果不對哦？

   圖5中，事務B的檢視陣列是先生成的，之後事務C才提交，不是應該看不見(1,2)嗎，怎麼能 算出(1,3)來？

   

   是的，如果事務B在更新之前查詢一次資料，這個查詢返回的k的值確實是1。

   但是，當它要去更新資料的時候，就不能再在歷史版本上更新了，否則事務C的更新就丟失了。 因此，事務B此時的set k=k+1是在（1,2）的基礎上進行的操作。

   所以，這裡就用到了這樣一條規則：更新資料都是先讀後寫的，而這個讀，只能讀當前的 值，稱為“當前讀”（current read）。

   因此，在更新的時候，當前讀拿到的資料是(1,2)，更新後生成了新版本的資料(1,3)，這個新版本 的rowtrx_id是101。

   所以，在執行事務B查詢語句的時候，一看自己的版本號是101，最新資料的版本號也是101，是 自己的更新，可以直接使用，所以查詢得到的k的值是3。

   這裡我們提到了一個概念，叫作當前讀。其實，除了update語句外，select語句如果加鎖，也是 當前讀。

   所以，如果把事務A的查詢語句select *fromt where id=1修改一下，加上lock in share mode 或 for update，也都可以讀到版本號是101的資料，返回的k的值是3。下面這兩個select語句，就是 分別加了讀鎖（S鎖，共享鎖）和寫鎖（X鎖，排他鎖）。

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

   假設事務C不是馬上提交的，而是變成了下面的事務C’，會怎麼樣呢？

   

   事務C’的不同是，更新後並沒有馬上提交，在它提交前，事務B的更新語句先發起了。前面說過 了，雖然事務C’還沒提交，但是(1,2)這個版本也已經生成了，並且是當前的最新版本。那麼，事 務B的更新語句會怎麼處理呢？

   這時候，我們在上一篇文章中提到的“兩階段鎖協議”就要上場了。事務C’沒提交，也就是說(1,2) 這個版本上的寫鎖還沒釋放。而事務B是當前讀，必須要讀最新版本，而且必須加鎖，因此就被 鎖住了，必須等到事務C’釋放這個鎖，才能繼續它的當前讀。

   

   到這裡，我們把一致性讀、當前讀和行鎖就串起來了。

5. 事務的可重複讀的能力是怎麼實現的？

   可重複讀的核心就是一致性讀（consistent read）；而事務更新資料的時候，只能用當前讀。如 果當前的記錄的行鎖被其他事務佔用的話，就需要進入鎖等待。

   而讀提交的邏輯和可重複讀的邏輯類似，它們最主要的區別是：

   • 在可重複讀隔離級別下，只需要在事務開始的時候建立一致性檢視，之後事務裡的其他查詢 都共用這個一致性檢視；
   • 在讀提交隔離級別下，每一個語句執行前都會重新算出一個新的檢視。

   那麼，我們再看一下，在讀提交隔離級別下，事務A和事務B的查詢語句查到的k，分別應該是多 少呢？

   “start transaction with consistent snapshot; ”的意思是從這個語句開始，創 建一個持續整個事務的一致性快照。所以，在讀提交隔離級別下，這個用法就沒意義了，等效於 普通的start transaction

   下面是讀提交時的狀態圖，可以看到這兩個查詢語句的建立檢視陣列的時機發生了變化，就是圖 中的read view框。（注意：這裡，我們用的還是事務C的邏輯直接提交，而不是事務C’）

   !

   這時，事務A的查詢語句的檢視陣列是在執行這個語句的時候建立的，時序上(1,2)、(1,3)的生成 時間都在建立這個檢視陣列的時刻之前。但是，在這個時刻：

   • (1,3)還沒提交，屬於情況1，不可見；
   • (1,2)提交了，屬於情況3，可見。

   所以，這時候事務A查詢語句返回的是k=2。 顯然地，事務B查詢結果k=3。

6. 小結

   InnoDB的行資料有多個版本，每個資料版本有自己的rowtrx_id，每個事務或者語句有自己的一 致性檢視。普通查詢語句是一致性讀，一致性讀會根據rowtrx_id和一致性檢視確定資料版本的 可見性。

   • 對於可重複讀，查詢只承認在事務啟動前就已經提交完成的資料；
   • 對於讀提交，查詢只承認在語句啟動前就已經提交完成的資料；

   而當前讀，總是讀取已經提交完成的最新版本。

   為什麼表結構不支援“可重複讀”？這是因為表結構沒有對應的行資料，也沒有 rowtrx_id，因此只能遵循當前讀的邏輯。

   MySQL 8.0已經可以把表結構放在InnoDB字典裡了，也許以後會支援表結構的可重複 讀。

第九節: 普通索引和唯一索引，應該怎麼選擇？

​ 從效能的角度考慮，你選擇唯一索引還是普通索引呢？選擇的依據是什麼 呢？

​ 還是用第四節： 深入淺出索引（上）中的例子來說明，假設欄位 k 上的值 都不重複。

接下來，我們就從這兩種索引對查詢語句和更新語句的效能影響來進行分析。

1. 查詢過程

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

   • 對於普通索引來說，查詢到滿足條件的第一個記錄(5,500)後，需要查詢下一個記錄，直到碰 到第一個不滿足k=5條件的記錄。
   • 對於唯一索引來說，由於索引定義了唯一性，查詢到第一個滿足條件的記錄後，就會停止繼 續檢索。

   這個不同帶來的效能差距是微乎其微的。

   InnoDB的資料是按資料頁為單位來讀寫的。也就是說，當需要讀一條記錄的時候， 並不是將這個記錄本身從磁碟讀出來，而是以頁為單位，將其整體讀入記憶體。在InnoDB中，每 個資料頁的大小預設是16KB。

   因為引擎是按頁讀寫的，所以說，當找到k=5的記錄的時候，它所在的資料頁就都在記憶體裡了。 那麼，對於普通索引來說，要多做的那一次“查詢和判斷下一條記錄”的操作，就只需要一次指標 尋找和一次計算。

   當然，如果k=5這個記錄剛好是這個資料頁的最後一個記錄，那麼要取下一個記錄，必須讀取下 一個資料頁，這個操作會稍微複雜一些。 但是，我們之前計算過，對於整型欄位，一個資料頁可以放近千個key，因此出現這種情況的概 率會很低。所以，我們計算平均效能差異時，仍可以認為這個操作成本對於現在的CPU來說可以 忽略不計。

2. 更新過程

   change buffer

   當需要更新一個資料頁時，如果資料頁在記憶體中就直接更新，而如果這個資料頁還沒有在記憶體中 的話，在不影響資料一致性的前提下，InooDB會將這些更新操作快取在change buffer中，這樣 就不需要從磁碟中讀入這個資料頁了。在下次查詢需要訪問這個資料頁的時候，將資料頁讀入內 存，然後執行change buffer中與這個頁有關的操作。通過這種方式就能保證這個資料邏輯的正 確性。

   雖然名字叫作change buffer，實際上它是可以持久化的資料。也就是 說，change buffer在記憶體中有拷貝，也會被寫入到磁碟上。

   將change buffer中的操作應用到原資料頁，得到最新結果的過程稱為merge。除了訪問這個資料 頁會觸發merge外，系統有後臺執行緒會定期merge。在資料庫正常關閉（shutdown）的過程中， 也會執行merge操作。

   顯然，如果能夠將更新操作先記錄在change buffer，減少讀磁碟，語句的執行速度會得到明顯 的提升。而且，資料讀入記憶體是需要佔用buffer pool的，所以這種方式還能夠避免佔用記憶體，提 高記憶體利用率。

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

     對於唯一索引來說，所有的更新操作都要先判斷這個操作是否違反唯一性約束。比如，要插入 (4,400)這個記錄，就要先判斷現在表中是否已經存在k=4的記錄，而這必須要將資料頁讀入記憶體 才能判斷。如果都已經讀入到記憶體了，那直接更新記憶體會更快，就沒必要使用change buffer 了。

   因此，唯一索引的更新就不能使用change buffer，實際上也只有普通索引可以使用。

   change buffer用的是buffer pool裡的記憶體，因此不能無限增大。change buffer的大小，可以通 過引數innodb_change_buffer_max_size來動態設定。這個引數設定為50的時候，表示change buffer的大小最多隻能佔用buffer pool的50%。

   如果要在這張表中插入一個 新記錄(4,400)的話，InnoDB的處理流程是怎樣的?

   第一種情況是，這個記錄要更新的目標頁在記憶體中。這時，InnoDB的處理流程如下：

   • 對於唯一索引來說，找到3和5之間的位置，判斷到沒有衝突，插入這個值，語句執行結束；
   • 對於普通索引來說，找到3和5之間的位置，插入這個值，語句執行結束。

   這樣看來，普通索引和唯一索引對更新語句效能影響的差別，只是一個判斷，只會耗費微小的 CPU時間。

   第二種情況是，這個記錄要更新的目標頁不在記憶體中。這時，InnoDB的處理流程如下：

   • 對於唯一索引來說，需要將資料頁讀入記憶體，判斷到沒有衝突，插入這個值，語句執行結 束；
   • 對於普通索引來說，則是將更新記錄在change buffer，語句執行就結束了。

   將資料從磁碟讀入記憶體涉及隨機IO的訪問，是資料庫裡面成本最高的操作之一。change buffer 因為減少了隨機磁碟訪問，所以對更新效能的提升是會很明顯的。

3. change buffer的使用場景

   用change buffer對更新過程的加速作用，change buffer只限於用在普通索引的場景下，而不適用於唯一索引。

   普通 索引的所有場景，使用change buffer都可以起到加速作用嗎？

   因為merge的時候是真正進行資料更新的時刻，而change buffer的主要目的就是將記錄的變更動 作快取下來，所以在一個資料頁做merge之前，change buffer記錄的變更越多（也就是這個頁面 上要更新的次數越多），收益就越大。

   因此，對於寫多讀少的業務來說，頁面在寫完以後馬上被訪問到的概率比較小，此時change buffer的使用效果最好。這種業務模型常見的就是賬單類、日誌類的系統。

   反過來，假設一個業務的更新模式是寫入之後馬上會做查詢，那麼即使滿足了條件，將更新先記 錄在change buffer，但之後由於馬上要訪問這個資料頁，會立即觸發merge過程。這樣隨機訪問 IO的次數不會減少，反而增加了change buffer的維護代價。所以，對於這種業務模式來 說，change buffer反而起到了副作用。

4. 索引選擇和實踐

   普通索引和唯一索引應該怎麼選擇。其實，這兩類索引在查詢能力上 是沒差別的，主要考慮的是對更新效能的影響。所以，我建議你儘量選擇普通索引。

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

   特別地，在使用機械硬碟時，change buffer這個機制的收效是非常顯著的。所以，當你有一個 類似“歷史資料”的庫，並且出於成本考慮用的是機械硬碟時，那你應該特別關注這些表裡的索 引，儘量使用普通索引，然後把change buffer 儘量開大，以確保這個“歷史資料”表的資料寫入 速度。

5. change buffer 和 redo log

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

   現在，我們要在表上執行這個插入語句：

   insert into t(id,k) values(id1,k1),(id2,k2);
   

   們假設當前k索引樹的狀態，查詢到位置後，k1所在的資料頁在記憶體(InnoDB buffer pool)中，k2所在的資料頁不在記憶體中。如圖2所示是帶change buffer的更新狀態圖。

   

   分析這條更新語句，你會發現它涉及了四個部分：記憶體、redo log（ib_log_fileX）、 資料表空間 （t.ibd）、系統表空間（ibdata1）。

   這條更新語句做了如下的操作（按照圖中的數字順序）：

   1. Page 1在記憶體中，直接更新記憶體；
   2. Page 2沒有在記憶體中，就在記憶體的change buffer區域，記錄下“我要往Page 2插入一行”這個 資訊;
   3. 將上述兩個動作記入redo log中（圖中3和4）。

   做完上面這些，事務就可以完成了。所以，你會看到，執行這條更新語句的成本很低，就是寫了 兩處記憶體，然後寫了一處磁碟（兩次操作合在一起寫了一次磁碟），而且還是順序寫的。

   同時，圖中的兩個虛線箭頭，是後臺操作，不影響更新的響應時間。

   那在這之後的讀請求，要怎麼處理呢？

   比如，我們現在要執行 select *fromt where k in (k1, k2)。這裡，畫了這兩個讀請求的流程 圖。

   如果讀語句發生在更新語句後不久，記憶體中的資料都還在，那麼此時的這兩個讀操作就與系統表 空間（ibdata1）和 redo log（ib_log_fileX）無關了。所以，在圖中就沒畫出這兩部分。

   

   從圖中可以看到：

   1. 讀Page 1的時候，直接從記憶體返回。有幾位同學在前面文章的評論中問到，WAL之後如果 讀資料，是不是一定要讀盤，是不是一定要從redo log裡面把資料更新以後才可以返回？其 實是不用的。你可以看一下圖3的這個狀態，雖然磁碟上還是之前的資料，但是這裡直接從 記憶體返回結果，結果是正確的。
   2. 要讀Page 2的時候，需要把Page 2從磁碟讀入記憶體中，然後應用change buffer裡面的操作 日誌，生成一個正確的版本並返回結果。

   可以看到，直到需要讀Page 2的時候，這個資料頁才會被讀入記憶體。

   redo log 主要節省的是隨 機寫磁碟的IO消耗（轉成順序寫），而change buffer主要節省的則是隨機讀磁碟的IO消 耗。

第十節: MySQL為什麼有時候會選錯索引？

我本地操作和課程中的結果不一致，本節內容就沿用課程中的吧

示例：

先建一個簡單的表，表裡有a、b兩個欄位，並分別建上索引：

CREATE TABLE `t` (
`id` int(11) NOT NULL,
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `a` (`a`),
KEY `b` (`b`)
) ENGINE=InnoDB;

使用儲存過程往表t中插入10萬行記錄，取值按整數遞增，即：(1,1,1)，(2,2,2)，(3,3,3) 直到 (100000,100000,100000)。

delimiter ;;
create procedure idata()
begin
declare i int;
set i=1;
while(i<=100000)do
insert into t values(i, i, i);
set i=i+1;
end while;
end;;
delimiter ;
call idata();

分析一條SQL語句：

select * from t where a between 10000 and 20000;

這條查詢語句的執行也確實符合預期，key這個欄位值是’a’，表示優化器選擇了索 引a。

---

再執行下面的操作

這時候，session B的查詢語句select *fromt where a between 10000 and 20000就不會再選擇 索引a了。我們可以通過慢查詢日誌（slowlog）來檢視一下具體的執行情況。

下面的三條SQL語句，就是這個實驗過程。

set long_query_time=0;
select * from t where a between 10000 and 20000; /*Q1*/
select * from t force index(a) where a between 10000 and 20000;/*Q2*//*使用force index(a)來讓優化器強制使用索引a*/

• 第一句，是將慢查詢日誌的閾值設定為0，表示這個執行緒接下來的語句都會被記錄入慢查詢日 志中；
• 第二句，Q1是session B原來的查詢；
• 第三句，Q2是加了force index(a)來和session B原來的查詢語句執行情況對比。

如圖3所示是這三條SQL語句執行完成後的慢查詢日誌。

我本地是版本是8.0.25，慢查詢日誌在mysql.slow_log這個表中

可以看到，Q1掃描了10萬行，顯然是走了全表掃描，執行時間是14毫秒。Q2掃描了10001行， 執行了10毫秒。也就是說，我們在沒有使用force index的時候，MySQL用錯了索引，導致了更長的執行時間。

1. 優化器的邏輯

   優化器選擇索引的目的，是找到一個最優的執行方案，並用最小的代價去執行語句。在資料庫 裡面，掃描行數是影響執行代價的因素之一。掃描的行數越少，意味著訪問磁碟資料的次數越 少，消耗的CPU資源越少。

   掃描行數並不是唯一的判斷標準，優化器還會結合是否使用臨時表、是否排序等因素進行 綜合判斷。

   我們這個簡單的查詢語句並沒有涉及到臨時表和排序，所以MySQL選錯索引肯定是在判斷掃描 行數的時候出問題了。

   • 掃描行數是怎麼判斷的？

     MySQL在真正開始執行語句之前，並不能精確地知道滿足這個條件的記錄有多少條，而只能根 據統計資訊來估算記錄數。

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

     我們可以使用show index方法，看到一個索引的基數。如圖4所示，就是表t的show index的結果 。雖然這個表的每一行的三個欄位值都是一樣的，但是在統計資訊中，這三個索引的基數值並不 同，而且其實都不準確。

     

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

     MySQL取樣統計的方 法:

     • 為什麼要取樣統計呢？因為把整張表取出來一行行統計，雖然可以得到精確的結果，但是代價太 高了，所以只能選擇“取樣統計”。
     • 取樣統計的時候，InnoDB預設會選擇N個資料頁，統計這些頁面上的不同值，得到一個平均 值，然後乘以這個索引的頁面數，就得到了這個索引的基數。
     • 而資料表是會持續更新的，索引統計資訊也不會固定不變。所以，當變更的資料行數超過1/M的 時候，會自動觸發重新做一次索引統計。

     在MySQL中，有兩種儲存索引統計的方式，可以通過設定引數innodb_stats_persistent的值來選 擇：

     • 設定為on的時候，表示統計資訊會持久化儲存。這時，預設的N是20，M是10。
     • 設定為off的時候，表示統計資訊只儲存在記憶體中。這時，預設的N是8，M是16。

     由於是取樣統計，所以不管N是20還是8，這個基數都是很容易不準的。

     從圖4中看到，這次的索引統計值（cardinality列）雖然不夠精確，但大體上還是差不多 的，選錯索引一定還有別的原因。

     其實索引統計只是一個輸入，對於一個具體的語句來說，優化器還要判斷，執行這個語句本身要 掃描多少行。

     接下來，我們再一起看看優化器預估的，這兩個語句的掃描行數是多少。

     

     rows這個欄位表示的是預計掃描行數。

     其中，Q1的結果還是符合預期的，rows的值是104620；但是Q2的rows值是37116，偏差就大 了。而圖1中我們用explain命令看到的rows是隻有10001行，是這個偏差誤導了優化器的判斷。

     這是因為，如果使用索引a，每次從索引a上拿到一個值，都要回到主鍵索引上查出整行資料， 這個代價優化器也要算進去的。

     而如果選擇掃描10萬行，是直接在主鍵索引上掃描的，沒有額外的代價。

     優化器會估算這兩個選擇的代價，從結果看來，優化器認為直接掃描主鍵索引更快。當然，從執 行時間看來，這個選擇並不是最優的。

     使用普通索引需要把回表的代價算進去，在圖1執行explain的時候，也考慮了這個策略的代價 ， 但圖1的選擇是對的。也就是說，這個策略並沒有問題。

     所以MySQL選錯索引，這件事兒還得歸咎到沒能準確地判斷出掃描行數。

   analyze table t 命令，可以用來重新統計索引資訊。

   發現explain的結果預估的rows值跟實際情況差距比較大，可以採用analyze table 表名這個 方法來處理。

   其實，如果只是索引統計不準確，通過analyze命令可以解決很多問題，但是前面我們說了，優 化器可不止是看掃描行數。

   依然是基於這個表t，我們看看另外一個語句：

   select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 100）alias limit 1;
   

   從條件上看，這個查詢沒有符合條件的記錄，因此會返回空集合。

   我們先來看一下a、b這兩個索引的結構圖。

   

   如果使用索引a進行查詢，那麼就是掃描索引a的前1000個值，然後取到對應的id，再到主鍵索 引上去查出每一行，然後根據欄位b來過濾。顯然這樣需要掃描1000行。

   如果使用索引b進行查詢，那麼就是掃描索引b的最後50001個值，與上面的執行過程相同，也是 需要回到主鍵索引上取值再判斷，所以需要掃描50001行。

   所以你一定會想，如果使用索引a的話，執行速度明顯會快很多。那麼，下面我們就來看看到底 是不是這麼一回事兒。

   圖8是執行explain的結果。

   explain select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b  limit 1;
   

   

   可以看到，返回結果中key欄位顯示，這次優化器選擇了索引b，而rows欄位顯示需要掃描的行 數是50198。

   從這個結果中，你可以得到兩個結論：

   1. 掃描行數的估計值依然不準確；
   2. . 這個例子裡MySQL又選錯了索引。

2. 索引選擇異常和處理

   一種方法是，像我們第一個例子一樣，採用force index強行選擇一個索引。

   MySQL會根據 詞法解析的結果分析出可能可以使用的索引作為候選項，然後在候選列表中依次判斷每個索引需 要掃描多少行。如果force index指定的索引在候選索引列表中，就直接選擇這個索引，不再評估 其他索引的執行代價。

   第二種方法就是，我們可以考慮修改 語句，引導MySQL使用我們期望的索引。

   比如，在這個例子裡，顯然把“order by b limit 1” 改 成 “order by b,a limit 1” ，語義的邏輯是相同的。

   之前優化器選擇使用索引b，是因為它認為使用索引b可以避免排序（b本身是索引，已經是有序 的了，如果選擇索引b的話，不需要再做排序，只需要遍歷），所以即使掃描行數多，也判定為 代價更小。

   現在order by b,a 這種寫法，要求按照b,a排序，就意味著使用這兩個索引都需要排序。因此，掃 描行數成了影響決策的主要條件，於是此時優化器選了只需要掃描1000行的索引a。

   當然，這種修改並不是通用的優化手段，只是剛好在這個語句裡面有limit 1，因此如果有滿足條 件的記錄， order byb limit 1和order byb,a limit 1 都會返回b是最小的那一行，邏輯上一致，才 可以這麼做。

   這裡還有一種改法

select * from (select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 100)alias limit 1;

在這個例子裡，我們用limit 100讓優化器意識到，使用b索引代價是很高的。其實是我們根據數 mysql> select * from (select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 100)alias limit 1; 據特徵誘導了一下優化器，也不具備通用性。

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

3. ##### 上期問題

+ 如果某次寫入使用了change buffer機制，之後主機異常 重啟，是否會丟失change buffer和資料？

  答案是不會丟失。雖然是隻更新記憶體，但是在事務提 交的時候，我們把change buffer的操作也記錄到redo log裡了，所以崩潰恢復的時候，change buffer也能找回來。

+ merge的過程是否會把資料直接寫回磁碟？

  merge的執行流程是這樣的：

  1. 從磁碟讀入資料頁到記憶體（老版本的資料頁）； 
  2. 從change buffer裡找出這個資料頁的change buffer 記錄(可能有多個），依次應用，得到新 版資料頁； 
  3.  寫redo log。這個redo log包含了資料的變更和change buffer的變更。

   到這裡merge過程就結束了。這時候，資料頁和記憶體中change buffer對應的磁碟位置都還沒有修 改，屬於髒頁，之後各自刷回自己的物理資料，就是另外一個過程了。

### 第十一節:怎麼給字串欄位加索引？

​	MySQL是支援字首索引的，也就是說，你可以定義字串的一部分作為索引。預設地，如果你建立索引的語句不指定字首長度，那麼索引就會包含整個字串。

```sql
create table SUser(
ID bigint unsigned primary key,
email varchar(64),
)engine=innodb;

比如，這兩個在email欄位上建立索引的語句：

alter table SUser add index index1(email);
alter table SUser add index index2(email(6));

第一個語句建立的index1索引裡面，包含了每個記錄的整個字串；而第二個語句建立的index2 索引裡面，對於每個記錄都是隻取前6個位元組。

!

從圖中你可以看到，由於email(6)這個索引結構中每個郵箱欄位都只取前6個位元組（即： zhangs），所以佔用的空間會更小，這就是使用字首索引的優勢。

但這同時帶來的損失是，可能會增加額外的記錄掃描次數。

接下來，我們再看看下面這個語句，在這兩個索引定義下分別是怎麼執行的。

select id,name,email from SUser where email='zhangssxyz@xxx.com';

如果使用的是index1（即email整個字串的索引結構），執行順序是這樣的：

1. 從index1索引樹找到滿足索引值是'zhangssxyz@xxx.com’的這條記錄，取得ID2的值；
2. 到主鍵上查到主鍵值是ID2的行，判斷email的值是正確的，將這行記錄加入結果集；
3. 取index1索引樹上剛剛查到的位置的下一條記錄，發現已經不滿足 email='zhangssxyz@xxx.com’的條件了，迴圈結束。

這個過程中，只需要回主鍵索引取一次資料，所以系統認為只掃描了一行。

---

如果使用的是index2（即email(6)索引結構），執行順序是這樣的：

1. 從index2索引樹找到滿足索引值是’zhangs’的記錄，找到的第一個是ID1；
2. 到主鍵上查到主鍵值是ID1的行，判斷出email的值不是’zhangssxyz@xxx.com’，這行記錄丟 棄；
3. 取index2上剛剛查到的位置的下一條記錄，發現仍然是’zhangs’，取出ID2，再到ID索引上取 整行然後判斷，這次值對了，將這行記錄加入結果集；
4. 重複上一步，直到在idxe2上取到的值不是’zhangs’時，迴圈結束。

在這個過程中，要回主鍵索引取4次資料，也就是掃描了4行。

通過這個對比，你很容易就可以發現，使用字首索引後，可能會導致查詢語句讀資料的次數變 多。

但是，對於這個查詢語句來說，如果你定義的index2不是email(6)而是email(7），也就是說取 email欄位的前7個位元組來構建索引的話，即滿足字首’zhangss’的記錄只有一個，也能夠直接查到 ID2，只掃描一行就結束了。

使用字首索引，定義好長度，就可以做到既節省空間，又不用額外增加太多的查 詢成本。

當要給字串建立字首索引時，有什麼方法能夠確定我應該使用多長的字首呢？

實際上，我們在建立索引時關注的是區分度，區分度越高越好。因為區分度越高，意味著重複的 鍵值越少。因此，我們可以通過統計索引上有多少個不同的值來判斷要使用多長的字首。

首先，你可以使用下面這個語句，算出這個列上有多少個不同的值：

select count(distinct email) as L from SUser;

然後，依次選取不同長度的字首來看這個值，比如我們要看一下4~7個位元組的字首索引，可以用 這個語句：

select
count(distinct left(email,4)）as L4,
count(distinct left(email,5)）as L5,
count(distinct left(email,6)）as L6,
count(distinct left(email,7)）as L7,
from SUser;

當然，使用字首索引很可能會損失區分度，所以你需要預先設定一個可以接受的損失比例，比如 5%。然後，在返回的L4~L7中，找出不小於 L * 95%的值，假設這裡L6、L7都滿足，你就可以 選擇字首長度為6。

字首索引對覆蓋索引的影響

前面我們說了使用字首索引可能會增加掃描行數，這會影響到效能。其實，字首索引的影響不止 如此，我們再看一下另外一個場景。

你先來看看這個SQL語句：

select id,email from SUser where email='zhangssxyz@xxx.com';

與前面例子中的SQL語句

select id,name,email from SUser where email='zhangssxyz@xxx.com';

相比，這個語句只要求返回id和email欄位。

所以，如果使用index1（即email整個字串的索引結構）的話，可以利用覆蓋索引，從index1查 到結果後直接就返回了，不需要回到ID索引再去查一次。而如果使用index2（即email(6)索引結 構）的話，就不得不回到ID索引再去判斷email欄位的值。

即使你將index2的定義修改為email(18)的字首索引，這時候雖然index2已經包含了所有的資訊， 但InnoDB還是要回到id索引再查一下，因為系統並不確定字首索引的定義是否截斷了完整信 息。

也就是說，使用字首索引就用不上覆蓋索引對查詢效能的優化了，這也是你在選擇是否使用字首 索引時需要考慮的一個因素。

其他方式

​ 第一種方式是使用倒序儲存。

select field_list from t where id_card = reverse('input_id_card_string');

​ 第二種方式是使用hash欄位。

alter table t add id_card_crc int unsigned, add index(id_card_crc);

​ 然後每次插入新記錄的時候，都同時用crc32()這個函式得到校驗碼填到這個新欄位。由於校驗碼 可能存在衝突，也就是說兩個不同的身份證號通過crc32()函 數得到的結果可能是相同的，所以你 的查詢語句where部分要判斷id_card的值是否精確相同。

select field_list from t where id_card_crc=crc32('input_id_card_string') and id_card='input_id_card_string';

​ 使用倒序儲存和使用hash欄位這兩種方法的異同點。

​ 首先，它們的相同點是，都不支援範圍查詢。倒序儲存的欄位上建立的索引是按照倒序字串的 方式排序的，已經沒有辦法利用索引方式查出身份證號碼在 [ID_X, ID_Y]的所有市民了。同樣 地，hash欄位的方式也只能支援等值查詢。

​ 它們的區別，主要體現在以下三個方面：

	1. 從佔用的額外空間來看，倒序儲存方式在主鍵索引上，不會消耗額外的儲存空間，而hash字 段方法需要增加一個欄位。當然，倒序儲存方式使用4個位元組的字首長度應該是不夠的，如 果再長一點，這個消耗跟額外這個hash欄位也差不多抵消了。 
	2.  在CPU消耗方面，倒序方式每次寫和讀的時候，都需要額外呼叫一次reverse函式，而hash 欄位的方式需要額外呼叫一次crc32()函式。如果只從這兩個函式的計算複雜度來看的 話，reverse函式額外消耗的CPU資源會更小些。 
	3.  從查詢效率上看，使用hash欄位方式的查詢效能相對更穩定一些。因為crc32算出來的值雖 然有衝突的概率，但是概率非常小，可以認為每次查詢的平均掃描行數接近1。而倒序儲存 方式畢竟還是用的字首索引的方式，也就是說還是會增加掃描行數。

小結

字串欄位建立索引的場景,可以使用的 方式有：

1. 直接建立完整索引，這樣可能比較佔用空間；
2. 建立字首索引，節省空間，但會增加查詢掃描次數，並且不能使用覆蓋索引；
3. 倒序儲存，再建立字首索引，用於繞過字串本身字首的區分度不夠的問題；
4. 建立hash欄位索引，查詢效能穩定，有額外的儲存和計算消耗，跟第三種方式一樣，都不支 持範圍掃描。

第十二節：為什麼我的MySQL會“抖”一下？

1. 你的SQL語句為什麼變“慢”了

   InnoDB在處理更新語句的時候，只做了寫日誌這一個磁碟操作。這個日誌 叫作redo log（重做日誌），在更新記憶體寫 完redo log後，就返回給客戶端，本次更新成功。

   把記憶體裡的資料寫入磁碟的過程，術語就是 flush。

   當記憶體資料頁跟磁碟資料頁內容不一致的時候，我們稱這個記憶體頁為“髒頁”。記憶體資料寫 入到磁碟後，記憶體和磁碟上的資料頁的內容就一致了，稱為“乾淨頁”。

   平時執行很快的更新操作，其實就是在寫記憶體和日誌，而 MySQL偶爾“抖”一下的那個瞬間，可能就是在刷髒頁（flush）。

   什麼情況會引發資料庫的flush過程呢？

   • 第一種場景是，InnoDB的redo log寫滿了。這時候系統會停止所有更新操作，把 checkpoint往前推進，redo log留出空間可以繼續寫。

     

     checkpoint可不是隨便往前修改一下位置就可以的。比如圖2中，把checkpoint位置從CP推進到 CP’，就需要將兩個點之間的日誌（淺綠色部分），對應的所有髒頁都flush到磁碟上。之後，圖 中從write pos到CP’之間就是可以再寫入的redo log的區域。

   • 第二種場景是，系統記憶體不足。當需要新的記憶體頁，而記憶體不夠用的時候，就要淘汰 一些資料頁，空出記憶體給別的資料頁使用。如果淘汰的是“髒頁”，就要先將髒頁寫到磁碟。難道不能直接把記憶體淘汰掉，下次需要請求的時候，從磁碟讀入資料 頁，然後拿redo log出來應用不就行了？這裡其實是從效能考慮的。如果刷髒頁一定會寫盤， 就保證了每個資料頁有兩種狀態：

     • 一種是記憶體裡存在，記憶體裡就肯定是正確的結果，直接返回；
     • 另一種是記憶體裡沒有資料，就可以肯定資料檔案上是正確的結果，讀入記憶體後返回。 這樣的效率最高。

   • 第三種場景是，MySQL認為系統“空閒”的時候。刷“髒頁”。

   • 第四種場景是，MySQL正常關閉的情況。這時候，MySQL會把記憶體的髒頁都flush到磁 盤上，這樣下次MySQL啟動的時候，就可以直接從磁碟上讀資料，啟動速度會很快。

   上面四種場景對效能的影響：

   • 第三種情況是屬於MySQL空閒時的操作，這時系統沒什麼壓力，而第四種場景是資料庫 本來就要關閉了。這兩種情況下，你不會太關注“效能”問題。
   • 第一種是“redo log寫滿了，要flush髒頁”，這種情況是InnoDB要儘量避免的。因為出現這種情況 的時候，整個系統就不能再接受更新了，所有的更新都必須堵住。如果你從監控上看，這時候更 新數會跌為0。
   • 第二種是“記憶體不夠用了，要先將髒頁寫到磁碟”，這種情況其實是常態。InnoDB用緩衝池 （buffer pool）管理記憶體，緩衝池中的記憶體頁有三種狀態：
     • 第一種是，還沒有使用的；
     • 第二種是，使用了並且是乾淨頁；
     • 第三種是，使用了並且是髒頁。

   InnoDB的策略是儘量使用記憶體，因此對於一個長時間執行的庫來說，未被使用的頁面很少。

   而當要讀入的資料頁沒有在記憶體的時候，就必須到緩衝池中申請一個資料頁。這時候只能把最久 不使用的資料頁從記憶體中淘汰掉：如果要淘汰的是一個乾淨頁，就直接釋放出來複用；但如果是 髒頁呢，就必須將髒頁先刷到磁碟，變成乾淨頁後才能複用。

   所以，刷髒頁雖然是常態，但是出現以下這兩種情況，都是會明顯影響效能的：

   1. 一個查詢要淘汰的髒頁個數太多，會導致查詢的響應時間明顯變長；
   2. 日誌寫滿，更新全部堵住，寫效能跌為0，這種情況對敏感業務來說，是不能接受的。

   所以，InnoDB需要有控制髒頁比例的機制，來儘量避免上面的這兩種情況。

   InnoDB刷髒頁的控制策略

   首先，你要正確地告訴InnoDB所在主機的IO能力，這樣InnoDB才能知道需要全力刷髒頁的時 候，可以刷多快。

   這就要用到innodb_io_capacity這個引數了，它會告訴InnoDB你的磁碟能力。這個值我建議你設 置成磁碟的IOPS。磁碟的IOPS可以通過fio這個工具來測試。

   下面的語句是用來測試磁碟隨機 讀寫的命令：

   fio -filename=$filename -direct=1 -iodepth 1 -thread -rw=randrw -ioengine=psync -bs=16k -size=500M -numjobs=10 -runtime=10 -group_reporting -name=mytest
   

   雖然我們現在已經定義了“全力刷髒頁”的行為，但平時總不能一直是全力刷吧？畢竟磁碟能力不 能只用來刷髒頁，還需要服務使用者請求。所以接下來，我們就一起看看InnoDB怎麼控制引擎按 照“全力”的百分比來刷髒頁。

   如果你來設計策略控制刷髒頁的速度，會參考哪些因 素呢？

   如果刷太慢，會出現什麼情況？首先是記憶體髒頁太多，其次是redo log寫 滿。

   InnoDB的刷盤速度就是要參考這兩個因素：一個是髒頁比例，一個是redo log寫盤速度。

   InnoDB會在後臺刷髒頁，而刷髒頁的過程是要將記憶體頁寫入磁碟。所以，無論 是你的查詢語句在需要記憶體的時候可能要求淘汰一個髒頁，還是由於刷髒頁的邏輯會佔用IO資源 並可能影響到了你的更新語句，都可能是造成你從業務端感知到MySQL“抖”了一下的原因

   要儘量避免這種情況，你就要合理地設定innodb_io_capacity的值，並且平時要多關注髒頁比 例，不要讓它經常接近75%。

   其中，髒頁比例是通過Innodb_buffer_pool_pages_dirty/Innodb_buffer_pool_pages_total得到 的，具體的命令參考下面的程式碼：

   select VARIABLE_VALUE into @a from global_status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_dirty';
   select VARIABLE_VALUE into @b from global_status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_total';
   select @a/@b;
   

   再看一個有趣的策略。

   一旦一個查詢請求需要在執行過程中先flush掉一個髒頁時，這個查詢就可能要比平時慢了。而 MySQL中的一個機制，可能讓你的查詢會更慢：在準備刷一個髒頁的時候，如果這個資料頁旁 邊的資料頁剛好是髒頁，就會把這個“鄰居”也帶著一起刷掉；而且這個把“鄰居”拖下水的邏輯還 可以繼續蔓延，也就是對於每個鄰居資料頁，如果跟它相鄰的資料頁也還是髒頁的話，也會被放 到一起刷。

   在InnoDB中，innodb_flush_neighbors 引數就是用來控制這個行為的，值為1的時候會有上述 的“連坐”機制，值為0時表示不找鄰居，自己刷自己的。

   找“鄰居”這個優化在機械硬碟時代是很有意義的，可以減少很多隨機IO。機械硬碟的隨機IOPS 一般只有幾百，相同的邏輯操作減少隨機IO就意味著系統效能的大幅度提升。

   而如果使用的是SSD這類IOPS比較高的裝置的話，建議你把innodb_flush_neighbors的值 設定成0。因為這時候IOPS往往不是瓶頸，而“只刷自己”，就能更快地執行完必要的刷髒頁操 作，減少SQL語句響應時間。

   在MySQL 8.0中，innodb_flush_neighbors引數的預設值已經是0了。

第十三節:為什麼表資料刪掉一半，表檔案大小不變？

​ 一個InnoDB表包含兩部 分，即：表結構定義和資料。在MySQL 8.0版本以前，表結構是存在以.frm為字尾的檔案裡。而 MySQL 8.0版本，則已經允許把表結 構定義放在系統資料表中了。因為表結構定義佔用的空間很 小，所以我們今天主要討論的是表資料。

1. 引數innodb_file_per_table

   表資料既可以存在共享表空間裡，也可以是單獨的檔案。這個行為是由引數 innodb_file_per_table控制的：

   • 這個引數設定為OFF表示的是，表的資料放在系統共享表空間，也就是跟資料字典放在一 起；
   • 這個引數設定為ON表示的是，每個InnoDB表資料儲存在一個以 .ibd為字尾的檔案中。

   從MySQL 5.6.6版本開始，它的預設值就是ON了。

   建議不論使用MySQL的哪個版本，都將這個值設定為ON。因為，一個表單獨儲存為一個文 件更容易管理，而且在你不需要這個表的時候，通過drop table命令，系統就會直接刪除這個文 件。而如果是放在共享表空間中，即使表刪掉了，空間也是不會回收的。

   將innodb_file_per_table設定為ON，是推薦做法，我們接下來的討論都是基於這個 設定展開的。

   在刪除整個表的時候，可以使用drop table命令回收表空間。

2. 資料刪除流程

   先再來看一下InnoDB中一個索引的示意圖。在前面提到過，InnoDB裡的資料都是用B+樹的結構組織的。

   

   假設，我們要刪掉R4這個記錄，InnoDB引擎只會把R4這個記錄標記為刪除。如果之後要再插入 一個ID在300和600之間的記錄時，可能會複用這個位置。但是，磁碟檔案的大小並不會縮小。

   現在，你已經知道了InnoDB的資料是按頁儲存的，那麼如果我們刪掉了一個資料頁上的所有記 錄，會怎麼樣？整個資料頁就可以被複用了。

   資料頁的複用跟記錄的複用是不同的。

   記錄的複用，只限於符合範圍條件的資料。比如上面的這個例子，R4這條記錄被刪除後，如果 插入一個ID是400的行，可以直接複用這個空間。但如果插入的是一個ID是800的行，就不能復 用這個位置了。

   而當整個頁從B+樹裡面摘掉以後，可以複用到任何位置。以圖1為例，如果將資料頁page A上的 所有記錄刪除以後，page A會被標記為可複用。這時候如果要插入一條ID=50的記錄需要使用新 頁的時候，page A是可以被複用的。

   如果相鄰的兩個資料頁利用率都很小，系統就會把這兩個頁上的資料合到其中一個頁上，另外一 個資料頁就被標記為可複用。

   進一步地，如果我們用delete命令把整個表的資料刪除呢？結果就是，所有的資料頁都會被標記 為可複用。但是磁碟上，檔案不會變小。

   delete命令其實只是把記錄的位置，或者資料頁標記為了“可複用”，但磁碟檔案 的大小是不會變的。也就是說，通過delete命令是不能回收表空間的。這些可以複用，而沒有被 使用的空間，看起來就像是“空洞”。

   不止是刪除資料會造成空洞，插入資料也會。

   如果資料是按照索引遞增順序插入的，那麼索引是緊湊的。但如果資料是隨機插入的，就可能造 成索引的資料頁分裂。

   假設圖1中page A已經滿了，這時我要再插入一行資料，會怎樣呢？

   

   可以看到，由於page A滿了，再插入一個ID是550的資料時，就不得不再申請一個新的頁面 page B來儲存資料了。頁分裂完成後，page A的末尾就留下了空洞（注意：實際上，可能不止1 個記錄的位置是空洞）。

   另外，更新索引上的值，可以理解為刪除一箇舊的值，再插入一個新值。不難理解，這也是會造 成空洞的。

   也就是說，經過大量增刪改的表，都是可能是存在空洞的。所以，如果能夠把這些空洞去掉，就 能達到收縮表空間的目的。

   而重建表，就可以達到這樣的目的。

3. 重建表

   在重建表的時候，InnoDB不會把整 張表佔滿，每個頁留了1/16給後續的更新用。也就是說，其實重建表之後不是“最”緊湊的。

   可以新建一個與表A結構相同的表B，然後按照主鍵ID遞增的順序，把資料一行一行地從表A 裡讀出來再插入到表B中。

   由於表B是新建的表，所以表A主鍵索引上的空洞，在表B中就都不存在了。顯然地，表B的主鍵 索引更緊湊，資料頁的利用率也更高。如果我們把表B作為臨時表，資料從表A匯入表B的操作完 成後，用表B替換A，從效果上看，就起到了收縮表A空間的作用。

   使用alter table A engine=InnoDB命令來重建表。在MySQL 5.5版本之前，這個命 令的執行流程跟我們前面描述的差不多，區別只是這個臨時表B不需要你自己建立，MySQL會自 動完成轉存資料、交換表名、刪除舊錶的操作。

   

   顯然，花時間最多的步驟是往臨時表插入資料的過程，如果在這個過程中，有新的資料要寫入到 表A的話，就會造成資料丟失。因此，在整個DDL過程中，表A中不能有更新。也就是說，這個 DDL不是Online的。

   而在MySQL 5.6版本開始引入的Online DDL，對這個操作流程做了優化。

   簡單描述一下引入了Online DDL之後，重建表的流程：

   1. 建立一個臨時檔案，掃描表A主鍵的所有資料頁；
   2. 用資料頁中表A的記錄生成B+樹，儲存到臨時檔案中；
   3. 生成臨時檔案的過程中，將所有對A的操作記錄在一個日誌檔案（rowlog）中，對應的是圖 中state2的狀態；
   4. 臨時檔案生成後，將日誌檔案中的操作應用到臨時檔案，得到一個邏輯資料上與表A相同的 資料檔案，對應的就是圖中state3的狀態；
   5. 用臨時檔案替換表A的資料檔案。

​ 可以看到，與圖3過程的不同之處在於，由於日誌檔案記錄和重放操作這個功能的存在，這個方 案在重建表的過程中，允許對錶A做增刪改操作。這也就是 Online DDL名字的來源。

​ 圖4的流程中，alter語句在啟動的時候需要獲取MDL寫鎖，但是這個寫鎖在真正拷貝資料 之前就退化成讀鎖了。 為什麼要退化呢？為了實現Online，MDL讀 鎖不會阻塞增刪改操作。 那為什麼不乾脆直接解鎖呢？為了保護自己，禁止其他執行緒對這個表同時做DDL。 而對於一個大表來說，Online DDL最耗時的過 程就是拷貝資料到臨時表的過程，這個步驟的執 行期間可以接受增刪改操作。所以，相對於整個DDL過程來說，鎖的時間非常短。對業務來說， 就可以認 為是Online的。

​ 上述的這些重建方法都會掃描原表資料和構建臨時檔案。對於很大的表來 說，這個操作是很消耗IO和CPU資源的。因此，如果是線上服務，你要很小心地控 制操作時 間。如果想要比較安全的操作的話，我推薦你使用GitHub開源的gh-ost來做。

5. Online 和 inplace

   在圖3中，我們把表A中的資料匯出來的存放位置叫作tmp_table。這是一個臨 時表，是在server層建立的。

   在圖4中，根據表A重建出來的資料是放在“tmp_file”裡的，這個臨時檔案是InnoDB在內部建立出 來的。整個DDL過程都在InnoDB內部完成。對於server層來說，沒有把資料挪動到臨時表，是 一個“原地”操作，這就是“inplace”名稱的來源。

   重建表的這個語句alter table t engine=InnoDB，其實隱含的意思是：

   alter table t engine=innodb,ALGORITHM=inplace;
   

   跟inplace對應的就是拷貝表的方式了，用法是：

   alter table t engine=innodb,ALGORITHM=copy;
   

   當你使用ALGORITHM=copy的時候，表示的是強制拷貝表，對應的流程就是圖3的操作過程。

   inplace跟Online是不是就是一個意思？

   其實不是的，只是在重建表這個邏輯中剛好是這樣而已。

   比如，如果我要給InnoDB表的一個欄位加全文索引，寫法是：

   alter table t add FULLTEXT(field_name)
   

   這個過程是inplace的，但會阻塞增刪改操作，是非Online的。

   如果說這兩個邏輯之間的關係是什麼的話，可以概括為：

   1. DDL過程如果是Online的，就一定是inplace的；
   2. 反過來未必，也就是說inplace的DDL，有可能不是Online的。截止到MySQL 8.0，新增全文 索引（FULLTEXTindex）和空間索引(SPATIAL index)就屬於這種情況。

   ---

   optimize table、analyze table和alter table這三種方式重建表的區別

   • 從MySQL 5.6版本開始，alter table t engine = InnoDB（也就是recreate）預設的就是上面圖4 的流程了；
   • analyze table t 其實不是重建表，只是對錶的索引資訊做重新統計，沒有修改資料，這個過程 中加了MDL讀鎖；
   • optimize table t 等於recreate+analyze。

第十四節:count(*)這麼慢，我該怎麼辦？

1. count(*)的實現方式

   在不同的MySQL引擎中，count(*)有不同的實現方式。

   • MyISAM引擎把一個表的總行數存在了磁碟上，因此執行count(*)的時候會直接返回這個數， 效率很高；
   • 而InnoDB引擎就麻煩了，它執行count(*)的時候，需要把資料一行一行地從引擎裡面讀出 來，然後累積計數。

   我們在這篇文章裡討論的是沒有過濾條件的count(*)，如果加了where 條件 的話，MyISAM表也是不能返回得這麼快的。

   ---

   為什麼InnoDB不跟MyISAM一樣，也把數字存起來呢？

   這是因為即使是在同一個時刻的多個查詢，由於多版本併發控制（MVCC）的原因，InnoDB 表“應該返回多少行”也是不確定的。這裡，我用一個算count(*)的例子來為你解釋一下。

   假設表t中現在有10000條記錄，我們設計了三個使用者並行的會話。

   • 會話A先啟動事務並查詢一次表的總行數；
   • 會話B啟動事務，插入一行後記錄後，查詢表的總行數；
   • 會話C先啟動一個單獨的語句，插入一行記錄後，查詢表的總行數。

   我們假設從上到下是按照時間順序執行的，同一行語句是在同一時刻執行的。

   

   你會看到，在最後一個時刻，三個會話A、B、C會同時查詢表t的總行數，但拿到的結果卻不同

   這和InnoDB的事務設計有關係，可重複讀是它預設的隔離級別，在程式碼上就是通過多版本併發 控制，也就是MVCC來實現的。每一行記錄都要判斷自己是否對這個會話可見，因此對於 count(*)請求來說，InnoDB只好把資料一行一行地讀出依次判斷，可見的行才能夠用於計算“基 於這個查詢”的表的總行數。

   ---

   當然，現在這個看上去笨笨的MySQL，在執行count(*)操作的時候還是做了優化的。

   InnoDB是索引組織表，主鍵索引樹的葉子節點是資料，而普通索引樹的葉子節點是 主鍵值。所以，普通索引樹比主鍵索引樹小很多。對於count(*)這樣的操作，遍歷哪個索引樹得 到的結果邏輯上都是一樣的。因此，MySQL優化器會找到最小的那棵樹來遍歷。在保證邏輯正 確的前提下，儘量減少掃描的資料量，是資料庫系統設計的通用法則之一。

   如果你用過showtable status 命令的話，就會發現這個命令的輸出結果裡面也有一個 TABLE_ROWS用於顯示這個表當前有多少行，這個命令執行挺快的，那這個TABLE_ROWS能 代替count(*)嗎？

   實際上，TABLE_ROWS就是從這個取樣估算得來的，因此它也很不準。 有多不準呢，官方文件說誤差可能達到40%到50%。所以，show table status命令顯示的行 數也不能直接使用。

   小結一下：

   • MyISAM表雖然count(*)很快，但是不支援事務；
   • show table status命令雖然返回很快，但是不準確；
   • InnoDB表直接count(*)會遍歷全表，雖然結果準確，但會導致效能問題。

2. 不同的count用法（基於InnoDB引擎）

   首先你要弄清楚count()的語義。count()是一個聚合函式，對於返回的結果集，一行行地 判斷，如果count函式的引數不是NULL，累計值就加1，否則不加。最後返回累計值。

   所以，count(*)、count(主鍵id)和count(1) 都表示返回滿足條件的結果集的總行數；而count(字 段），則表示返回滿足條件的資料行裡面，引數“欄位”不為NULL的總個數。

   至於分析效能差別的時候，你可以記住這麼幾個原則：

   1. server層要什麼就給什麼；
   2. InnoDB只給必要的值；
   3. 現在的優化器只優化了count(*)的語義為“取行數”，其他“顯而易見”的優化並沒有做。

   對於count(主鍵id)來說，InnoDB引擎會遍歷整張表，把每一行的id值都取出來，返回給server 層。server層拿到id後，判斷是不可能為空的，就按行累加。

   對於count(1)來說，InnoDB引擎遍歷整張表，但不取值。server層對於返回的每一行，放一個 數字“1”進去，判斷是不可能為空的，按行累加。

   單看這兩個用法的差別的話，你能對比出來，count(1)執行得要比count(主鍵id)快。因為從引擎 返回id會涉及到解析資料行，以及拷貝欄位值的操作。

   對於count(欄位)來說：

   1. 如果這個“欄位”是定義為not null的話，一行行地從記錄裡面讀出這個欄位，判斷不能為 null，按行累加；
   2. 如果這個“欄位”定義允許為null，那麼執行的時候，判斷到有可能是null，還要把值取出來再 判斷一下，不是null才累加。

   但是count(*)是例外，並不會把全部欄位取出來，而是專門做了優化，不取值。count(*)肯定不 是null，按行累加。

   看到這裡，你一定會說，優化器就不能自己判斷一下嗎，主鍵id肯定非空啊，為什麼不能按照 count(*)來處理，多麼簡單的優化啊。

   當然，MySQL專門針對這個語句進行優化，也不是不可以。但是這種需要專門優化的情況太多 了，而且MySQL已經優化過count(*)了，你直接使用這種用法就可以了。

   所以結論是：按照效率排序的話，count(欄位)<count(主鍵id)<count(1)≈count(*)，所以建議，儘量使用count(*)。

第十五節: 答疑文章（一）：日誌和索引相關問題

1. 日誌相關問題

   在第二節：日誌系統：一條SQL更新語句是如何執行的？講到binlog（歸檔日 志）和redo log（重做日誌）配合崩潰恢復的時候，用的是反證法，說明了如果沒有兩階段提 交，會導致MySQL出現主備資料不一致等問題。

   有同學問，在兩階段提交的不同瞬間，MySQL如果發生異常重啟，是怎 麼保證資料完整性的？

   再放一次兩階段提交的圖，方便你學習下面的內容。

   

   這個例子裡面，沒有顯式地開啟事務，因此這個update語句自己就是一個事務，在執行 完成後提交事務時，就會用到這個“commit步驟“。

   在兩階段提交的不同時刻，MySQL異常重啟會出現什麼現象？

   如果在圖中時刻A的地方，也就是寫入redo log 處於prepare階段之後、寫binlog之前，發生了崩 潰（crash），由於此時binlog還沒寫，redo log也還沒提交，所以崩潰恢復的時候，這個事務會 回滾。這時候，binlog還沒寫，所以也不會傳到備庫。

   如果在圖中在時刻B，也就是binlog寫完，redo log還沒commit前發生 crash，那崩潰恢復的時候MySQL會怎麼處理？

   我們先來看一下崩潰恢復時的判斷規則。

   1. 如果redo log裡面的事務是完整的，也就是已經有了commit標識，則直接提交；

   2. 如果redo log裡面的事務只有完整的prepare，則判斷對應的事務binlog是否存在並完整：

      a. 如果是，則提交事務；

      b. 否則，回滾事務。

   這裡，時刻B發生crash對應的就是2(a)的情況，崩潰恢復過程中事務會被提交。

   ---

   追問1：MySQL怎麼知道binlog是完整的?

   回答：一個事務的binlog是有完整格式的：

   • statement格式的binlog，最後會有COMMIT；
   • row格式的binlog，最後會有一個XID event。

   另外，在MySQL 5.6.2版本以後，還引入了binlog-checksum引數，用來驗證binlog內容的正確 性。對於binlog日誌由於磁碟原因，可能會在日誌中間出錯的情況，MySQL可以通過校驗 checksum的結果來發現。所以，MySQL還是有辦法驗證事務binlog的完整性的。

   ---

   追問2：redo log 和 binlog是怎麼關聯起來的?

   回答：它們有一個共同的資料欄位，叫XID。崩潰恢復的時候，會按順序掃描redo log：

   • 如果碰到既有prepare、又有commit的redo log，就直接提交；
   • 如果碰到只有parepare、而沒有commit的redo log，就拿著XID去binlog找對應的事務。

   ---

   追問3：處於prepare階段的redo log加上完整binlog，重啟就能恢 復，MySQL為什麼要這麼設計?

   回答：其實，這個問題還是跟我們在反證法中說到的資料與備份的一致性有關。在時刻B，也就 是binlog寫完以後MySQL發生崩潰，這時候binlog已經寫入了，之後就會被從庫（或者用這個 binlog恢復出來的庫）使用。

   所以，在主庫上也要提交這個事務。採用這個策略，主庫和備庫的資料就保證了一致性。

   ---

   追問4：如果這樣的話，為什麼還要兩階段提交呢？乾脆先redo log寫完，再寫 binlog。崩潰恢復的時候，必須得兩個日誌都完整才可以。是不是一樣的邏輯？

   回答：其實，兩階段提交是經典的分散式系統問題，並不是MySQL獨有的。

   如果必須要舉一個場景，來說明這麼做的必要性的話，那就是事務的永續性問題。

   對於InnoDB引擎來說，如果redo log提交完成了，事務就不能回滾（如果這還允許回滾，就可能 覆蓋掉別的事務的更新）。而如果redo log直接提交，然後binlog寫入的時候失敗，InnoDB又回 滾不了，資料和binlog日誌又不一致了。

   追問5：不引入兩個日誌，也就沒有兩階段提交的必要了。只用binlog來支援崩 潰恢復，又能支援歸檔，不就可以了？

   回答：這位同學的意思是，只保留binlog，然後可以把提交流程改成這樣：…-> “資料更新到內 存” -> “寫 binlog” -> “提交事務”，是不是也可以提供崩潰恢復的能力？意思是，只保留binlog，然後可以把提交流程改成這樣：…-> “資料更新到內 存” -> “寫 binlog” -> “提交事務”，是不是也可以提供崩潰恢復的能力？

   答案是不可以。

   如果說歷史原因的話，那就是InnoDB並不是MySQL的原生儲存引擎。MySQL的原生引擎是 MyISAM，設計之初就有沒有支援崩潰恢復。

   InnoDB在作為MySQL的外掛加入MySQL引擎家族之前，就已經是一個提供了崩潰恢復和事務支 持的引擎了。

   InnoDB接入了MySQL後，發現既然binlog沒有崩潰恢復的能力，那就用InnoDB原有的redo log 好了。

   ---

   追問6：那能不能反過來，只用redo log，不要binlog？

   回答：如果只從崩潰恢復的角度來講是可以的。你可以把binlog關掉，這樣就沒有兩階段提交 了，但系統依然是crash-safe的。

   但是，如果你瞭解一下業界各個公司的使用場景的話，就會發現在正式的生產庫上，binlog都是 開著的。因為binlog有著redo log無法替代的功能。

   一個是歸檔。redo log是迴圈寫，寫到末尾是要回到開頭繼續寫的。這樣歷史日誌沒法保 留，redo log也就起不到歸檔的作用。

   一個就是MySQL系統依賴於binlog。binlog作為MySQL一開始就有的功能，被用在了很多地方。 其中，MySQL系統高可用的基礎，就是binlog複製。

   還有很多公司有異構系統（比如一些資料分析系統），這些系統就靠消費MySQL的binlog來更新 自己的資料。關掉binlog的話，這些下游系統就沒法輸入了。

   總之，由於現在包括MySQL高可用在內的很多系統機制都依賴於binlog，所以“鳩佔鵲巢”redo log還做不到。

   ---

   追問7：redo log一般設定多大？

   回答：redo log太小的話，會導致很快就被寫滿，然後不得不強行刷redo log，這樣WAL機制的 能力就發揮不出來了。

   如果磁碟夠大，建議將redo log設定為4個文 件、每個檔案1GB。

   追問8：正常執行中的例項，資料寫入後的最終落盤，是從redo log更新過來的 還是從buffer pool更新過來的呢？

   回答：redo log裡面到底是什麼？

   實際上，redo log並沒有記錄資料頁的完整資料，所以它並沒有能力自己去更新磁碟資料頁，也 就不存在“資料最終落盤，是由redo log更新過去”的情況。

   1. 如果是正常執行的例項的話，資料頁被修改以後，跟磁碟的資料頁不一致，稱為髒頁。最終 資料落盤，就是把記憶體中的資料頁寫盤。這個過程，甚至與redo log毫無關係。
   2. 在崩潰恢復場景中，InnoDB如果判斷到一個資料頁可能在崩潰恢復的時候丟失了更新，就 會將它讀到記憶體，然後讓redo log更新記憶體內容。更新完成後，記憶體頁變成髒頁，就回到了 第一種情況的狀態。

   追問9：redo log buffer是什麼？是先修改記憶體，還是先寫redo log檔案？

   回答：在一個事務的更新過程中，日誌是要寫多次的。比如下面這個事務：

   begin;
   insert into t1 ...
   insert into t2 ...
   commit;
   

   這個事務要往兩個表中插入記錄，插入資料的過程中，生成的日誌都得先儲存起來，但又不能在 還沒commit的時候就直接寫到redo log檔案裡。

   所以，redo log buffer就是一塊記憶體，用來先存redo日誌的。也就是說，在執行第一個insert的時 候，資料的記憶體被修改了，redo log buffer也寫入了日誌。

   但是，真正把日誌寫到redo log檔案（檔名是 ib_logfile+數字），是在執行commit語句的時候 做的。

   這裡說的是事務執行過程中不會“主動去刷盤”，以減少不必要的IO消耗。但是可能會出現“被 動寫入磁碟”，比如記憶體不夠、其他事務提交等情況。

   單獨執行一個更新語句的時候，InnoDB會自己啟動一個事務，在語句執行完成的時候提交。過 程跟上面是一樣的，只不過是“壓縮”到了一個語句裡面完成。