《MySQL实战45讲》实践篇 9-15讲 学习笔记
圖片和內容來自于極客時間，如有版權問題，請聯系我刪除。
09 | 普通索引和唯一索引，應該怎么選擇？
查詢
select id from T where k=5
查詢到葉子節點，唯一索引在找到第一個之后就會不再搜索，而普通索引會繼續檢索直到遇到第一個不滿足條件的
二者性能差別微乎其微 ，因為InnoDB是按頁加載數據的，只是多執行一次指針尋找和一次計算,并不耗時
更新
在不影響數據一致性的前提下，InnoDB 會將這些更新操作緩存在 change buffer 中。
將 change buffer 中的操作應用到原數據頁，得到最新結果的過程稱為 merge。除了訪問這個數據頁會觸發 merge 外，系統有后臺線程會定期 merge。在數據庫正常關閉（shutdown）的過程中，也會執行 merge 操作
唯一索引的更新就不能使用 change buffer，實際上也只有普通索引可以使用
change buffer適合于寫多讀少的場景，如果讀多寫少，一個業務在寫入之后馬上會做查詢，那么就不適合使用change buffer
普通索引和唯一索引:建議使用普通索引–區別在于更新的效率上
如果所有的更新后面，都馬上伴隨著對這個記錄的查詢，那么你應該關閉 change buffer。而在其他情況下，change buffer 都能提升更新性能
redo log 主要節省的是隨機寫磁盤的 IO 消耗（轉成順序寫），而 change buffer 主要節省的則是隨機讀磁盤的 IO 消耗。
補充:
首先，業務正確性優先。這里的比較前提是“業務代碼已經保證不會寫入重復數據”的情況下，討論性能問題。如果業務不能保證，或者業務就是要求數據庫來做約束，那么沒得選，必須創建唯一索引。
然后，在一些“歸檔庫”的場景，你是可以考慮使用普通索引的。比如，線上數據只需要保留半年，然后歷史數據保存在歸檔庫。這時候，歸檔數據已經是確保沒有唯一鍵沖突了。要提高歸檔效率，可以考慮把表里面的唯一索引改成普通索引
課后問題: 如果某次寫入使用了 change buffer 機制，之后主機異常重啟，是否會丟失 change buffer 和數據。
是不會丟失.
雖然是只更新內存，但是在事務提交的時候，我們把 change buffer 的操作也記錄到 redo log 里了，所以崩潰恢復的時候，change buffer 也能找回來。
merge 的過程是否會把數據直接寫回磁盤?
merge 的執行流程是這樣的：
到這里 merge 過程就結束了。這時候，數據頁和內存中 change buffer 對應的磁盤位置都還沒有修改，屬于臟頁，之后各自刷回自己的物理數據，就是另外一個過程了。
10 | MySQL為什么有時候會選錯索引？
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();往表t中插入10萬行記錄，取值按整數遞增，即：(1,1,1)，(2,2,2)，(3,3,3) 直到(100000,100000,100000)
選擇索引是優化器的工作
優化器的邏輯
MySQL 在真正開始執行語句之前，并不能精確地知道滿足這個條件的記錄有多少條，而只能根據統計信息來估算記錄數。
這個統計信息就是索引的“區分度”，而一個索引上不同的值的個數，我們稱之為“基數”。
我們可以使用 show index 方法，看到一個索引的基數。雖然這個表的每一行的三個字段值都是一樣的，但是在統計信息中，這三個索引的基數值并不同，而且其實都不準確。
MySQL 是通過采樣統計得到索引的基數
采樣統計的時候，InnoDB 默認會選擇 N 個數據頁，統計這些頁面上的不同值，得到一個平均值，然后乘以這個索引的頁面數，就得到了這個索引的基數
數據表是會持續更新的，索引統計信息也不會固定不變。所以，當變更的數據行數超過 1/M 的時候，會自動觸發重新做一次索引統計
在 MySQL 中，有兩種存儲索引統計的方式，可以通過設置參數 innodb_stats_persistent 的值來選擇：
設置為 on 的時候，表示統計信息會持久化存儲。這時，默認的 N 是 20，M 是 10。
設置為 off 的時候，表示統計信息只存儲在內存中。這時，默認的 N 是 8，M 是 16
如果show index的統計信息不對，那就修正。analyze table t 命令，可以用來重新統計索引信息
除了考慮掃描行數，MySQL還會考慮回表造成的額外開銷
索引選擇異常和處理
一種方法是，像我們第一個例子一樣，采用 force index 強行選擇一個索引。
缺點:使用 force index 最主要的問題還是變更的及時性。
因為選錯索引的情況還是比較少出現的，所以開發的時候通常不會先寫上 force index。
第二種方法就是，我們可以考慮修改語句，引導 MySQL 使用我們期望的索引。
mysql> explain select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;
這樣的一個SQL,分析應該是使用a作為索引掃描行數更小，但是優化器選擇了b(可能是由于這里使用了b進行排序)
改成 order by b,a limit 1;就可以引導MySQL去使用索引a
第三種方法是，在有些場景下，我們可以新建一個更合適的索引，來提供給優化器做選擇，或刪掉誤用的索引。
課后問題:為什么經過這個操作序列，explain的結果就不對了？
delete 語句刪掉了所有的數據，然后再通過 call idata() 插入了 10 萬行數據，看上去是覆蓋了原來的 10 萬行。
但是，session A 開啟了事務并沒有提交，所以之前插入的 10 萬行數據是不能刪除的。這樣，之前的數據每一行數據都有兩個版本，舊版本是 delete 之前的數據，新版本是標記為 deleted 的數據。
這樣，索引 a 上的數據其實就有兩份。然后你會說，不對啊，主鍵上的數據也不能刪，那沒有使用 force index 的語句，使用 explain 命令看到的掃描行數為什么還是 100000 左右？（潛臺詞，如果這個也翻倍，也許優化器還會認為選字段 a 作為索引更合適）
是的，不過這個是主鍵，主鍵是直接按照表的行數來估計的。而表的行數，優化器直接用的是 show table status 的值。
11 | 怎么給字符串字段加索引？
可以針對整個字符串創建索引，也可以使用前綴索引
使用前綴索引，定義好長度，就可以做到既節省空間，又不用額外增加太多的查詢成本。
可以通過區分度找到一個比較合適的長度
前綴索引對覆蓋索引的影響
使用前綴索引就用不上覆蓋索引對查詢性能的優化了，這也是你在選擇是否使用前綴索引時需要考慮的一個因素
如果前綴索引的區分度不高，怎么辦?比方說身份證，一個縣的前6位基本都是一致的
一種方式是使用倒序存儲
二種方式是使用 hash 字段
在表上再創建一個整數字段，來保存身份證的校驗碼(就是對身份證做hash)，同時在這個字段上創建索引
缺點:兩種都不支持范圍查詢
區別:
1.從占用的額外空間來看，倒序存儲方式在主鍵索引上，不會消耗額外的存儲空間，
而 hash 字段方法需要增加一個字段。當然，倒序存儲方式使用 4 個字節的前綴長度應該是不夠的，如果再長一點，這個消耗跟額外這個 hash 字段也差不多抵消了。(倒序存儲后還需要創建前綴索引，需要額外的存儲空間)
2.在 CPU 消耗方面，倒序方式每次寫和讀的時候，都需要額外調用一次 reverse 函數，而 hash 字段的方式需要額外調用一次 crc32() 函數。如果只從這兩個函數的計算復雜度來看的話，reverse 函數額外消耗的 CPU 資源會更小些。
3.從查詢效率上看，使用 hash 字段方式的查詢性能相對更穩定一些。因為 crc32 算出來的值雖然有沖突的概率，但是概率非常小，可以認為每次查詢的平均掃描行數接近 1。而倒序存儲方式畢竟還是用的前綴索引的方式，也就是說還是會增加掃描行數
課后問題: 如何給一個學生學號創建索引
前三位是所在城市編號、第四到第六位是學校編號,接下來是入學年份+排序號+郵箱后綴(@gamil.com)
因為維護的只是一個學校的，因此前面 6 位，可以只存入學年份加順序編號，它們的長度是 9 位。
在此基礎上，可以用數字類型來存這 9 位數字。比如 201100001，這樣只需要占 4 個字節。其實這個就是一種 hash，只是它用了最簡單的轉換規則：字符串轉數字的規則，而剛好我們設定的這個背景，可以保證這個轉換后結果的唯一性。
12 | 為什么我的MySQL會“抖”一下？— 突發性SQL執行變慢
當內存數據頁跟磁盤數據頁內容不一致的時候，我們稱這個內存頁為“臟頁”。內存數據寫入到磁盤后，內存和磁盤上的數據頁的內容就一致了，稱為“干凈頁”
平時執行很快的更新操作，其實就是在寫內存和日志，而 MySQL 偶爾“抖”一下的那個瞬間，可能就是在刷臟頁（flush）
flush時間:
1.redo log滿了 check point向前移動
2.系統內存不足，需要淘汰一些數據頁以加載新的數據頁，如果淘汰的是“臟頁”，就要先將臟頁寫到磁盤。
3.MySQL 認為系統“空閑”的時候
4.MySQL 正常關閉的情況
分析下前兩種對性能的影響:
1.redo log滿了flush 這種情形需要盡可能避免。因為出現這種情況的時候，整個系統就不能再接受更新了，所有的更新都必須堵住。如果你從監控上看，這時候更新數會跌為 0
2.“內存不夠用了，要先將臟頁寫到磁盤”，這種情況其實是常態。InnoDB 用緩沖池（buffer pool）管理內存，緩沖池中的內存頁有三種狀態：
第一種是，還沒有使用的；
第二種是，使用了并且是干凈頁；
第三種是，使用了并且是臟頁
總結下:這兩種情況，都是會明顯影響性能的
1.一個查詢要淘汰的臟頁個數太多，會導致查詢的響應時間明顯變長；
2.日志寫滿，更新全部堵住，寫性能跌為 0，這種情況對敏感業務來說，是不能接受的
InnoDB 需要有控制臟頁比例的機制，來盡量避免上面的這兩種情況
InnoDB 刷臟頁的控制策略
首先需要正確地告訴 InnoDB 所在主機的 IO 能力，這樣 InnoDB 才能知道需要全力刷臟頁的時候，可以刷多快。(innodb_io_capacity)
InnoDB 的刷盤速度就是要參考這兩個因素：一個是臟頁比例，一個是 redo log 寫盤速度。
innodb_max_dirty_pages_pct 是臟頁比例上限，默認值是 75%
InnoDB會根據當前的臟頁比例（假設為M），算出一個范圍在0到100之間的數字
F1(M) { if M>=innodb_max_dirty_pages_pct then return 100; return 100*M/innodb_max_dirty_pages_pct; }InnoDB每次寫入的日志都有一個序號，當前寫入的序號-checkpoint假設為N。根據這個N算出一個范圍在0到100之間的數字，這個計算公式可以記為F2(N),N越大，算出來的值越大。
引擎就可以按照innodb_io_capacity定義的能力乘以R%來控制刷臟頁的速度。
要盡量避免這種情況，你就要合理地設置 innodb_io_capacity 的值，并且平時要多關注臟頁比例，不要讓它經常接近 75%
一旦一個查詢請求需要在執行過程中先 flush 掉一個臟頁時，這個查詢就可能要比平時慢了。
而 MySQL 會在刷臟時去判斷相鄰的數據頁是否是臟頁，是的話就一起flush,并且會不斷蔓延
在 InnoDB 中，innodb_flush_neighbors 參數就是用來控制這個行為的，值為 1 的時候會有上述的“連坐”機制，值為 0 時表示不找鄰居，自己刷自己的。
找“鄰居”這個優化在機械硬盤時代是很有意義的，可以減少很多隨機 IO。機械硬盤的隨機 IOPS 一般只有幾百，相同的邏輯操作減少隨機 IO 就意味著系統性能的大幅度提升。
而如果使用的是 SSD 這類 IOPS 比較高的設備的話，建議把 innodb_flush_neighbors 的值設置成 0。
在 MySQL 8.0 中，innodb_flush_neighbors 參數的默認值已經是 0 了。
課后問題: 如果一個高配的機器，redo log設置太小，會發生什么情況。
每次事務提交都要寫redo log，如果設置太小，很快就會被寫滿，也就是下面這個圖的狀態，這個“環”將很快被寫滿，write pos一直追著checkpoint。
這時候系統不得不停止所有更新，去推進checkpoint。
這時，你看到的現象就是磁盤壓力很小，但是數據庫出現間歇性的性能下跌。
13 | 為什么表數據刪掉一半，表文件大小不變？
一個 InnoDB 表包含兩部分，即：表結構定義和數據。在 MySQL 8.0 版本以前，表結構是存在以.frm 為后綴的文件里。而 MySQL 8.0 版本，則已經允許把表結構定義放在系統數據表中了。
表數據既可以存在共享表空間里，也可以是單獨的文件。這個行為是由參數 innodb_file_per_table 控制的
ON : 每個 InnoDB 表數據存儲在一個以 .ibd 為后綴的文件中。 推薦
OFF : 表的數據放在系統共享表空間，也就是跟數據字典放在一起
一個表單獨存儲為一個文件更容易管理，而且在你不需要這個表的時候，通過 drop table 命令，系統就會直接刪除這個文件。而如果是放在共享表空間中，即使表刪掉了，空間也是不會回收的。
數據刪除流程
假設，我們要刪掉 R4 這個記錄，InnoDB 引擎只會把 R4 這個記錄標記為刪除。如果之后要再插入一個 ID 在 300 和 600 之間的記錄時，可能會復用這個位置。但是，磁盤文件的大小并不會縮小
數據頁的復用跟記錄的復用是不同的。
記錄的復用，只限于符合范圍條件的數據。比如上面的這個例子，R4 這條記錄被刪除后，如果插入一個 ID 是 400 的行，可以直接復用這個空間。但如果插入的是一個 ID 是 800 的行，就不能復用這個位置了
如果我們用 delete 命令把整個表的數據刪除，那么，所有的數據頁都會被標記為可復用。但是磁盤上，文件不會變小。
delete 命令其實只是把記錄的位置，或者數據頁標記為了“可復用”
這些可以復用，而沒有被使用的空間，看起來就像是“空洞”。實際上，插入數據也會造成空洞(發生頁分裂的時候)。
經過大量增刪改的表，都是可能是存在空洞的。(因為頁分裂導致的索引不緊湊)
重建表可以幫助我們減少不必要的空間浪費
新建與表 A 結構相同的表 B，按照主鍵 ID 遞增的順序，把數據一行一行地從表 A 里讀出來再插入到表 B 中
可以使用 alter table A engine=InnoDB 完成重建表
MySQL 5.5之前要求這個DDL期間 表A不能有數據更新 通過創建臨時表來完成
MySQL 5.6 版本引入的 Online DDL，對這個操作流程做了優化
上圖的流程中，alter語句在啟動的時候需要獲取MDL寫鎖，但是這個寫鎖在真正拷貝數據之前就退化成讀鎖了。(為了實現Online，MDL讀鎖不會阻塞增刪改操作)
那為什么不干脆直接解鎖呢？為了保護自己，禁止其他線程對這個表同時做DDL。
而對于一個大表來說，Online DDL最耗時的過程就是拷貝數據到臨時表的過程，這個步驟的執行期間可以接受增刪改操作。所以，相對于整個DDL過程來說，鎖的時間非常短。對業務來說，就可以認為是Online的。
需要補充說明的是，上述的這些重建方法都會掃描原表數據和構建臨時文件。對于很大的表來說，這個操作是很消耗IO和CPU資源的。因此，如果是線上服務，你要很小心地控制操作時間。如果想要比較安全的操作的話，我推薦你使用GitHub開源的gh-ost來做。
Online 和 inplace
在圖4中，根據表A重建出來的數據是放在“tmp_file”里的，這個臨時文件是InnoDB在內部創建出來的。整個DDL過程都在InnoDB內部完成。對于server層來說，沒有把數據挪動到臨時表，是一個“原地”操作，這就是“inplace”名稱的來源。
所以，我現在問你，如果你有一個1TB的表，現在磁盤間是1.2TB，能不能做一個inplace的DDL呢？
答案是不能。因為，tmp_file也是要占用臨時空間的。
我們重建表的這個語句alter table t engine=InnoDB，其實隱含的意思是：
alter table t engine=innodb,ALGORITHM=inplace;
跟inplace對應的就是拷貝表的方式了，用法是：
alter table t engine=innodb,ALGORITHM=copy;
當你使用ALGORITHM=copy的時候，表示的是強制拷貝表，對應的流程就是圖3的操作過程。
聯系：
DDL過程如果是Online的，就一定是inplace的；
反過來未必，也就是說inplace的DDL，有可能不是Online的。截止到MySQL 8.0，添加全文索引（FULLTEXT index）和空間索引(SPATIAL index)就屬于這種情況。
optimize table、analyze table 和 alter table 三種方式區別
從 MySQL 5.6 版本開始，
1.alter table t engine = InnoDB（也就是 recreate）默認的就是上面的流程了；
2.analyze table t 其實不是重建表，只是對表的索引信息做重新統計，沒有修改數據，這個過程中加了 MDL 讀鎖；
3.optimize table t 等于 recreate+analyze。
課后問題: 什么時候使用 alter table t engine=InnoDB 會讓一個表占用的空間反而變大。
如果這個表，本身就已經沒有空洞的了，比如說剛剛做過一次重建表操作。
在 DDL 期間，如果剛好有外部的 DML 在執行，這期間可能會引入一些新的空洞。
在重建表的時候，InnoDB 不會把整張表占滿，每個頁留了 1/16 給后續的更新用。也就是說，其實重建表之后不是“最”緊湊的。
假如是這么一個過程：
將表 t 重建一次；
插入一部分數據，但是插入的這些數據，用掉了一部分的預留空間；(此時的預留空間不足1/16))
這種情況下，再重建一次表 t，就可能會出現問題中的現象(因為需要預留空間1/16，導致所需的空間更大))。
14 | count(*)這么慢，我該怎么辦？
count的實現方式
MyISAM 引擎把一個表的總行數存在了磁盤上，因此執行 count 的時候會直接返回這個數，效率很高；
而 InnoDB 引擎就麻煩了，它執行 count 的時候，需要把數據一行一行地從引擎里面讀出來，然后累積計數
即使是在同一個時刻的多個查詢，由于多版本并發控制（MVCC）的原因，InnoDB 表“應該返回多少行”也是不確定的。 所以InnoDB表沒辦法向MyISAM一樣在磁盤上保存對應的表的行數
對于 count 這樣的操作，遍歷哪個索引樹得到的結果邏輯上都是一樣的。因此，*MySQL 優化器會找到最小的那棵樹來遍歷。在保證邏輯正確的前提下，盡量減少掃描的數據量，是數據庫系統設計的通用法則之一。
*
MyISAM 表雖然 count很快，但是不支持事務；
show table status 命令的輸出結果里面也有一個TABLE_ROWS用于顯示這個表當前有多少行，但是TABLE_ROWS是從索引的統計值估算來的(而索引的統計值是通過采樣來估算的)，因此它也很不準, 官方文檔說誤差可能達到40%到50%。
InnoDB 表直接 count會遍歷全表，雖然結果準確，但會導致性能問題。
所以當遇到count的性能問題時，我們只能自己去實現計數的方式
實現計數的方式
用緩存系統保存計數
問題:
1.緩存系統可能會丟失更新。redis的持久化可能會有數據丟失（取決于你持久化的配置） 可以在重啟后執行一次count拿到最新的行數
2.有可能會和數據庫的結果不完全一致 (緩存一致性問題)
在數據庫保存計數
通過事務可以解決上述的計數不一致問題
不同count()的用法
count() 是一個聚合函數，對于返回的結果集，一行行地判斷，如果 count 函數的參數不是 NULL，累計值就加 1，否則不加。最后返回累計值。
count(星號）、count(主鍵 id) 和 count(1) 都表示返回滿足條件的結果集的總行數；而 count(字段），則表示返回滿足條件的數據行里面，參數“字段”不為 NULL 的總個數
原則:
對于 count(主鍵 id) 來說 InnoDB 引擎會遍歷整張表，把每一行的 id 值都取出來，返回給 server 層。server 層拿到 id 后，判斷是不可能為空的，就按行累加
對于 count(1) 來說，InnoDB 引擎遍歷整張表，但不取值。server 層對于返回的每一行，放一個數字“1”進去，判斷是不可能為空的，按行累加
count(1) 執行得要比 count(主鍵 id) 快。因為從引擎返回 id 會涉及到解析數據行，以及拷貝字段值的操作
對于 count(字段) 來說：
如果這個“字段”是定義為 not null 的話，一行行地從記錄里面讀出這個字段，判斷不能為 null，按行累加；
如果這個“字段”定義允許為 null，那么執行的時候，判斷到有可能是 null，還要把值取出來再判斷一下，不是 null 才累加。
但是 count(星號) 是例外，并不會把全部字段取出來，而是專門做了優化，不取值。count(星號) 肯定不是 null，按行累加。
按照效率排序的話，count(字段)<count(主鍵 id)<count(1)≈count(星號)，所以建議，盡量使用 count(星號)
課后問題: 計數放在Redis里面，不能夠保證計數和MySQL表里的數據精確一致的原因，是這兩個不同的存儲構成的系統，不支持分布式事務，無法拿到精確一致的視圖。而把計數值也放在MySQL中，就解決了一致性視圖的問題。
我們用了事務來確保計數準確。由于事務可以保證中間結果不被別的事務讀到，因此修改計數值和插入新記錄的順序是不影響邏輯結果的。但是，從并發系統性能的角度考慮，你覺得在這個事務序列里，應該先插入操作記錄，還是應該先更新計數表呢？
并發系統性能的角度考慮，應該先插入操作記錄，再更新計數表。
知識點在《行鎖功過：怎么減少行鎖對性能的影響？》
因為更新計數表涉及到行鎖的競爭，先插入再更新能最大程度地減少事務之間的鎖等待，提升并發度。
15 | 答疑文章（一）：日志和索引相關問題
問題1: 兩階段期間發生crash
A時刻發生crash會回滾。在B時刻發生異常:
如果 redo log 里面的事務是完整的，也就是已經有了 commit 標識，則直接提交；
如果 redo log 里面的事務只有完整的 prepare，則判斷對應的事務 binlog 是否存在并完整：
a. 如果是，則提交事務；
b. 否則，回滾事務
問題2：MySQL 怎么知道 binlog 是完整的?
MySQL一個事務的bin log是有完整格式的：
statement 格式的 binlog，最后會有 COMMIT；
row 格式的 binlog，最后會有一個 XID event
另外，在MySQL 5.6.2版本以后，還引入了binlog-checksum參數，用來驗證binlog內容的正確性。對于binlog日志由于磁盤原因，可能會在日志中間出錯的情況，MySQL可以通過校驗checksum的結果來發現。所以，MySQL還是有辦法驗證事務binlog的完整性的。
問題3: redo log 和 binlog 是怎么關聯起來的?
它們有一個共同的數據字段，叫 XID。
崩潰恢復的時候，會按順序掃描 redo log：
如果碰到既有 prepare、又有 commit 的 redo log，就直接提交；
如果碰到只有 parepare、而沒有 commit 的 redo log，就拿著 XID 去 binlog 找對應的事務。
問題4 : 為什么還要兩階段提交呢？干脆先 redo log 寫完，再寫 binlog。崩潰恢復的時候，必須得兩個日志都完整才可以。是不是一樣的邏輯？
對于 InnoDB 引擎來說，如果 redo log 提交完成了，事務就不能回滾（如果這還允許回滾，就可能覆蓋掉別的事務的更新）。而如果 redo log 直接提交，然后 binlog 寫入的時候失敗，InnoDB 又回滾不了，數據和 binlog 日志又不一致了。
兩階段提交就是為了給所有人一個機會，當每個人都說“我 ok”的時候，再一起提交。
問題5: 不引入兩個日志，也就沒有兩階段提交的必要了。只用 binlog 來支持崩潰恢復，又能支持歸檔，不就可以了？
歷史原因:
InnoDB并不是MySQL的原生存儲引擎。MySQL的原生引擎是MyISAM，設計之初就有沒有支持崩潰恢復。
InnoDB在作為MySQL的插件加入MySQL引擎家族之前，就已經是一個提供了崩潰恢復和事務支持的引擎了。InnoDB接入了MySQL后，發現既然binlog沒有崩潰恢復的能力，那就用InnoDB原有的redo log好了。
問題在于binlog 沒有能力恢復“數據頁”。
如果在圖中標的位置，也就是 binlog2 寫完了，但是整個事務還沒有 commit 的時候，MySQL 發生了 crash。
重啟后，引擎內部事務 2 會回滾，然后應用 binlog2 可以補回來；
但是對于事務 1 來說，系統已經認為提交完成了，不會再應用一次 binlog1。
但是，InnoDB 引擎使用的是 WAL 技術，執行事務的時候，寫完內存和日志，事務就算完成了。
如果之后崩潰，要依賴于日志來恢復數據頁。也就是說在圖中這個位置發生崩潰的話，事務 1 也是可能丟失了的，而且是數據頁級的丟失。此時，binlog 里面并沒有記錄數據頁的更新細節，是補不回來的。
如果要說，那我優化一下binlog的內容，讓它來記錄數據頁的更改可以嗎？可以但是這其實就是又做了一個redo log出來。
所以，至少現在的binlog能力，還不能支持崩潰恢復。
我的理解:這跟InnoDB的設計有關，它使用了WAL技術，而redo log里記錄的是已經更新到緩存但是還沒有更新到磁盤的數據修改。但是bin log文件是記錄了所有的操作，如果只使用bin log，那么就沒辦法知道哪些日志是需要用來恢復數據的，如果把之前已經提交的事務的bin log又拿過來執行，就又跟bin log設計的初衷違背了
問題6：那能不能反過來，只用redo log，不要binlog？
只從崩潰恢復的角度來講是可以的，沒有binlog關掉，系統依然是crash-safe的。
但是bin log尤其重要作用
一個是歸檔。redo log是循環寫，起不到歸檔的作用。
一個就是MySQL系統依賴于binlog。MySQL系統高可用的基礎，就是binlog復制。
還有很多公司有異構系統（比如一些數據分析系統），這些系統就靠消費MySQL的binlog來更新自己的數據。關掉binlog的話，這些下游系統就沒法輸入了。
問題7: redo log一般設置多大？
redo log太小的話，會導致很快就被寫滿，然后不得不強行刷redo log。如果是現在常見的幾個TB的磁盤的話，可以直接將redo log設置為4個文件、每個文件1GB吧。
問題8: 正常運行中的實例，數據寫入后的最終落盤，是從 redo log 更新過來的還是從 buffer pool 更新過來的呢？
實際上，redo log 并沒有記錄數據頁的完整數據，所以它并沒有能力自己去更新磁盤數據頁，也就不存在“數據最終落盤，是由 redo log 更新過去”的情況。
如果是正常運行的實例的話，數據頁被修改以后，跟磁盤的數據頁不一致，稱為臟頁。最終數據落盤，就是把內存中的數據頁寫盤。這個過程，甚至與 redo log 毫無關系。
在崩潰恢復場景中，InnoDB 如果判斷到一個數據頁可能在崩潰恢復的時候丟失了更新，就會將它讀到內存，然后讓 redo log 更新內存內容。更新完成后，內存頁變成臟頁，就回到了第一種情況的狀態。
問題9: redo log buffer 是什么？是先修改內存，還是先寫 redo log 文件？
redo log buffer 就是一塊內存，用來先存 redo 日志的。
真正把日志寫到 redo log 文件（文件名是 ib_logfile+ 數字），是在執行 commit 語句的時候做的
在事務執行過程中執行的生成的日志是需要先保存到redo log buffer的
業務設計問題
業務上有這樣的需求，A、B 兩個用戶，如果互相關注，則成為好友。
設計上是有兩張表，一個是 like 表，一個是 friend 表，like 表有 user_id、liker_id 兩個字段，我設置為復合唯一索引即 uk_user_id_liker_id。
語句執行邏輯是這樣的：
以 A 關注 B 為例：
第一步，先查詢對方有沒有關注自己（B 有沒有關注 A）
select * from like where user_id = B and liker_id = A;
如果有，則成為好友insert into friend;沒有，則只是單向關注關系insert into like;
但是如果 A、B 同時關注對方，會出現不會成為好友的情況。因為上面第 1 步，雙方都沒關注對方。第 1 步即使使用了排他鎖也不行，因為記錄不存在，行鎖無法生效。
請問這種情況，在 MySQL 鎖層面有沒有辦法處理？
CREATE TABLE `like` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) NOT NULL, `liker_id` int(11) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uk_user_id_liker_id` (`user_id`,`liker_id`) ) ENGINE=InnoDB;CREATE TABLE `friend` ( id` int(11) NOT NULL AUTO_INCREMENT, `friend_1_id` int(11) NOT NULL, `firned_2_id` int(11) NOT NULL, UNIQUE KEY `uk_friend` (`friend_1_id`,`firned_2_id`) PRIMARY KEY (`id`) ) ENGINE=InnoDB;上述問題就是如下場景:
首先，要給“like”表增加一個字段，比如叫作 relation_ship，并設為整型，取值 1、2、3
值是 1 的時候，表示 user_id 關注 liker_id;
值是 2 的時候，表示 liker_id 關注 user_id;
值是 3 的時候，表示互相關注。
當 A 關注 B 的時候，邏輯改成如下所示的樣子：
應用代碼里面，比較 A 和 B 的大小，如果 A<B，就執行下面的邏輯
(這里的on duplicate key update就是如果插入失敗就更新的意思)
如果A>B,則執行如下:
mysql> begin; /*啟動事務*/ insert into `like`(user_id, liker_id, relation_ship) values(B, A, 2) on duplicate key update relation_ship=relation_ship | 2; select relation_ship from `like` where user_id=B and liker_id=A; /*代碼中判斷返回的 relation_ship，如果是2，事務結束，執行 commit如果是3，則執行下面這兩個語句： */ insert ignore into friend(friend_1_id, friend_2_id) values(B,A); commit;讓“like”表里的數據保證 user_id < liker_id，這樣不論是 A 關注 B，還是 B 關注 A，在操作“like”表的時候，如果反向的關系已經存在，就會出現行鎖沖突
然后，insert … on duplicate 語句，確保了在事務內部，執行了這個 SQL 語句后，就強行占住了這個行鎖，之后的 select 判斷 relation_ship 這個邏輯時就確保了是在行鎖保護下的讀操作。
操作符 “|” 是按位或，連同最后一句 insert 語句里的 ignore，是為了保證重復調用時的冪等性。
這樣，即使在雙方“同時”執行關注操作，最終數據庫里的結果，也是 like 表里面有一條關于 A 和 B 的記錄，而且 relation_ship 的值是 3， 并且 friend 表里面也有了 A 和 B 的這條記錄。
我的理解:
通過添加relation_ship字段，將A,B之間相互關注的操作都集中在同一記錄里(前面因為是在操作不同的記錄導致可以并發操作)，這樣就可以利用InnoDB的行鎖來解決同時關注的問題。
使用了"|" 按位或來簡化代碼操作，如果插入時的relation_ship和自己想要插入的不一致，那么就說明是相互關注，"|"運算后就得到3（相互關注的關系）
之所以要根據A,B的大小判斷也是為了讓A,B之間的關注操作的都是同一記錄
課后問題:
mysql> CREATE TABLE `t` ( `id` int(11) NOT NULL primary key auto_increment, `a` int(11) DEFAULT NULL ) ENGINE=InnoDB; insert into t values(1,2);表t里有唯一的一行數據(1,2)。假設，我現在要執行：
mysql> update t set a=2 where id=1;
你會看到這樣的結果：
僅從現象上看，MySQL內部在處理這個命令的時候，可以有以下三種選擇：
你覺得實際情況會是以上哪種呢？你可否用構造實驗的方式，來證明你的結論？進一步地，可以思考一下，MySQL為什么要選擇這種策略呢？
第一個選項是，MySQL 讀出數據，發現值與原來相同，不更新，直接返回，執行結束。這里我們可以用一個鎖實驗來確認。假設，當前表 t 里的值是 (1,2)。
session B 的 update 語句被 blocked 了，加鎖這個動作是 InnoDB 才能做的，而讀取操作不會鎖記錄，所以排除選項 1。
第二個選項是，MySQL 調用了 InnoDB 引擎提供的接口，但是引擎發現值與原來相同，不更新，直接返回。這里用一個可見性實驗來確認
session A 的第二個 select 語句是一致性讀（快照讀)，讀取的數據是session A快照生成時的數據，session A的快照讀按理是不能看見 session B 的更新的?，F在它返回的是 (1,3)，表示它看見了某個新的版本，那么這個版本只能是 session A 自己的 update 語句做更新的時候生成。
答案應該是選項 3，即：InnoDB 認真執行了“把這個值修改成 (1,2)"這個操作，該加鎖的加鎖，該更新的更新。
然后你會說，MySQL 怎么這么笨，就不會更新前判斷一下值是不是相同嗎？如果判斷一下，不就不用浪費 InnoDB 操作，多去更新一次了？
其實 MySQL 是確認了的。只是在這個語句里面，MySQL 認為讀出來的值，只有一個確定的 (id=1), 而要寫的是 (a=3)，只從這兩個信息是看不出來“不需要修改”的。
我覺得還可以反向思維 : 如果在執行的時候不進行加鎖和更新操作，那么此時其他會話就是有可能去操作這個數據的，這樣一來數據就可能會產生不一致的問題
| begin; | |
| update t set a=2 where id=1; | |
| update t set a=5 where id=1; //blocked | |
| update t set a=a+1 where id=1; | |
| commit; |
實際操作后的數據是(1,3)
說明在session A執行update t set a=2 where id=1;的時候就已經鎖住了對應的記錄，否則就會被session B的操作修改該記錄
總結
以上是生活随笔為你收集整理的《MySQL实战45讲》实践篇 9-15讲 学习笔记的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： ON DUPLICATE KEY UPD
- 下一篇： 《MySQL实战45讲》实践篇 24-2