一條SQL更新語句是如何執行的
1.執行器先找引擎取 ID=2 這一行。ID 是主鍵，引擎直接用樹搜尋找到這一行。如果 ID=2 這一行所在的資料頁本來就在記憶體中（change buffer），就直接返回給執行器；否則，需要先從磁碟讀入記憶體，然後再返回。
2.執行器拿到引擎給的行資料，把這個值加上 1，比如原來是 N，現在就是 N+1，得到新的一行資料，再呼叫引擎介面寫入這行新資料。
3.引擎將這行新資料更新到記憶體中，同時將這個更新操作記錄到 redo log 裡面，此時 redo log 處於 prepare 狀態。然後告知執行器執行完成了，隨時可以提交事務。
4.執行器生成這個操作的 binlog，並把 binlog 寫入磁碟。
5.執行器呼叫引擎的提交事務介面，引擎把剛剛寫入的 redo log 改成提交（commit）狀態，更新完成。
redo log的寫入有兩個步驟，第一次是praper狀態，事務commit後才會更新為commit狀態。
redo log
redo log 是 InnoDB 引擎特有的, redo log 是物理日誌，記錄的是“在某個資料頁上做了什麼修改” ,會有專門的pure執行緒處理redo log
undo log
回滾日誌，在 MySQL 中，實際上每條記錄在更新的時候都會同時記錄一條回滾操作。記錄上的最新值，通過回滾操作，都可以得到前一個狀態的值。MVCC中，一個記錄有多個版本，要得到某個版本的值，就是通過undo log計算到的。
事務與mvcc
Innodb實現事務隔離，是通過mvcc和鎖實現的。一致性讀的時候（就是快照讀），不需要加鎖，直接通過mvcc實現。
快照讀（所有的select語句）
在實現上， InnoDB 為每個事務構造了一個陣列，用來儲存這個事務啟動瞬間（事務啟動是指第一次查詢sql時，不是 start transtion），當前正在“活躍”的所有事務 ID。“活躍”指的就是，啟動了但還沒提交。這個陣列中，最小值稱為低水位，最大值加1稱為高水位。讀取一行資料得到 row trx_id，通過這個陣列來判斷是否可見，如果不可見，通過undo log得到上一個版本的資料。
當前讀
如果是當前讀，就必須是讀最新的版本資訊，就要加上行鎖鎖住這行。一直到當前的事務提交。當前讀的情況有：update t set k=k+1 where id=1 ….
為什麼要重建表
innodb經過大量的增刪改之後，資料頁會留下大量空洞，浪費磁碟空間，重建表可以解決這個問題。
頁合併和頁分裂
innodb刪除某個記錄，其實就是標記這個位置可複用。 當頁中刪除的記錄達到MERGE_THRESHOLD（預設頁體積的50%），InnoDB會開始尋找最靠近的頁（前或後）看看是否可以將兩個頁合併以優化空間使用。 當插入或者更新記錄時，當前頁已經滿了，需要在這個頁中間插入一個記錄，產生頁分裂，同時產生頁空洞。
重建表
你可以使用 alter table A engine=InnoDB 命令來重建表。
聯合索引的使用
index(name, age),先按照name排序，然後是age排序，索引上保留name和age的值
覆蓋索引
查詢的欄位已經在索引上獲取，不用回表查詢。
最左字首原則
索引下推
select * from tuser where name like ‘張%’ and age=10
會從索引找到張開頭的資料，然後過濾age=10後，再去回表查詢。就是說會盡力在索引中過濾完條件再去回表，而不是回表完在過濾其他where條件。
Order By使用索引
innodb的索引有兩大功能，快速定位記錄，還有就是排序功能。盡力保證order by的欄位利用到索引
Change Buffer和redo log
change buffer作用，當需要更新一個資料頁時，如果資料頁在記憶體中就直接更新，而如果這個資料頁還沒有在記憶體中的話，在不影響資料一致性的前提下（沒有唯一索引），InnoDB 會將這些更新操作快取在 change buffer 中，這樣就不需要從磁碟中讀入這個資料頁了。在下次查詢需要訪問這個資料頁的時候，將資料頁讀入記憶體，然後執行 change buffer 中與這個頁有關的操作。通過這種方式就能保證這個資料邏輯的正確性。
寫入或者更新一條資料
對於唯一索引來說，需要將資料頁讀入記憶體，判斷到沒有衝突，插入這個值，語句執行結束；
對於普通索引來說，則是將更新記錄在 change buffer，語句執行就結束了。（如果馬上有讀取這個記錄，還是需要去讀取這行記錄到記憶體，然後merge，所以說如果說change bufer更適合在寫多讀少的情況，並且沒有用到唯一索引）
redo log 主要節省的是隨機寫磁碟的 IO 消耗（轉成順序寫），而 change buffer 主要節省的則是隨機讀磁碟的 IO 消耗。
Mysql與客戶端如何互動
我們查詢一個大資料的時候，需要擔心把mysql記憶體爆掉嗎？
實際上，服務端並不需要儲存一個完整的結果集。取資料和發資料的流程是這樣的：
1.獲取一行，寫到 net_buffer 中。這塊記憶體的大小是由引數 net_buffer_length 定義的，預設是 16k。
2.重複獲取行，直到 net_buffer 寫滿，呼叫網路介面發出去(socket)。如果傳送成功，就清空 net_buffer，然後繼續取下一行，並寫入 net_buffer。
3.如果傳送函式返回 EAGAIN 或 WSAEWOULDBLOCK，就表示本地網路棧（socket send buffer）寫滿了，進入等待。直到網路棧重新可寫，再繼續傳送。
也就是說，MySQL 結果是“邊讀邊發的”，這個概念很重要。這就意味著，如果客戶端接收得慢，會導致 MySQL 服務端由於結果發不出去，這個事務的執行時間變長。
如果net_buffer寫滿了，mysql執行show processlist顯示Sending to client
mysql客戶端接收服務端返回結果的方式有兩種：
一種是本地快取，也就是在本地開一片記憶體，先把結果存起來。如果你用 API 開發，對應的就是 mysql_store_result 方法。
另一種是不快取，讀一個處理一個。如果你用 API 開發，對應的就是 mysql_use_result 方法。
Mysql內部臨時表
union
會有去重操作，所以會生成一個臨時表儲存資料，每次獲取一行，需要判斷是否存在。union all不需要去重，所以不會生成臨時表，直接返回資料即可。
group by
select a,count(1) as num from t group by a
1.建立記憶體臨時表，表裡有兩個欄位 a 和 num，主鍵是 a； //group by 欄位就是主鍵
2.從t中掃描資料（可能會選擇一個索引），寫入到記憶體臨時表中。由於a是主鍵，所以寫入第一條後，後面的只會修改聚合的欄位，比如num+1.
3.遍歷完後，再根據a欄位排序，得到結果集返回客戶端。 如果你的需求並不需要對結果進行排序，那你可以在 SQL 語句末尾增加 order by null
explain結果，extra欄位包含，Using temporary; Using filesort
磁碟臨時表
記憶體臨時表的大小是有限制的，引數 tmp_table_size 就是控制這個記憶體大小的，預設是 16M。 這時候就會把記憶體臨時錶轉成磁碟臨時表，磁碟臨時表預設使用的引擎是 InnoDB
group by 優化方法 – 索引
不論是使用記憶體臨時表還是磁碟臨時表，group by 邏輯都需要構造一個帶唯一索引的表，執行代價都是比較高的。 首先看看臨時表的作用，因為掃描的資料得到的a是無序的，所以需要構造一個主鍵為a的臨時表，好隨時修改對應主鍵的聚合值。如果掃描出來就是a有序的，就不需要臨時表了。
explain 語句，extra顯示，Using index，表示group by 索引欄位，不用臨時表，不用排序
group by 優化方法 – 直接排序
如果沒有辦法避開臨時表和排序，可以估算資料，直接走磁碟臨時表。不然會先放到記憶體臨時表，插入一部分資料後，發現記憶體臨時表不夠用了再轉成磁碟臨時表”，看上去就有點兒傻。
本作品採用《CC 協議》，轉載必須註明作者和本文連結