《MySQL實戰45講》學習小結（應用篇）

丁奇老師《MySQL實戰45講》的學習小結

第一篇：基礎概念

第二篇：運維管理

第三篇：合理使用MySQL

題目很大，寫得出來的很少 -_-!

把自己的了解梳理出一個提綱，作為一個速查手冊吧。

第一部分：sql優化

1. 了解一個sql如何被執行

最重要的是搞清楚MySQL是如何執行一個sql指令的。

explain指令

最簡單，也是最常用的：explain指令。

看看執行次序，看看是否用了索引、哪個索引、要掃描多少行記錄（估算）。

OPTIMIZER_TRACE（第16講）

可以打開optimizer_trace，隻對本線程有效。

執行sql指令後，查詢information_schema.OPTIMIZER_TRACE，可看到更多資訊，例如一個排序語句是否使用了臨時檔案。

執行sql指令前後分别查詢 performance_schema.session_status 表的 Innodb_rows_read 值，可以計算這個sql指令掃描了多少行記錄。

慢SQL日志（第19講）

檢視慢日志sql，可檢視sql的掃描行數 Rows_examined。

掃描行數可用于印證sql的執行過程，是一個比較有用的資訊。

select @@slow_query_log; -- 是否開啟慢日志

select @@slow_query_log_file; -- 慢日志檔案路徑

select @@long_query_time; -- 門檻值，機關為秒，超過這個時間視為慢sql，才記錄日志

2. 跟sql執行過程相關的一些概念

索引

主鍵索引為聚簇索引。非主鍵索引需回表，除非包含了sql用到的所有字段，即覆寫索引。

一般情況下，建議使用自增主鍵，這樣非主鍵索引占用的空間小，存儲效率和查詢效率高。

建議盡量使用普通索引，少用唯一索引（第9講）

索引的資料結構（第4講，第5講）

索引的基數（cardinality）（第10講）

字首索引、倒序存儲、hash字段（第11講）

函數索引（第37講，5.7版本以上）

change buffer（第9講）

如果一個業務的更新模式是寫入之後馬上會做查詢，建議關閉 change buffer。

join（34講，35講）

MySQL優化器根據兩個表的字段、行數（根據sql中每個表各自的條件篩選），統計資料量。以小表為驅動表，大表為被驅動表。

如果大表在join字段上有索引，适用 Index Nested-Loop Join（NLJ）算法，對小表中的每一行，從大表中查找比對的記錄，形成結果集。

如果大表在join字段上沒有索引，适用 Block Nested-Loop Join 算法，先把小表讀入 join_buffer，然後掃描大表，對于大表的每一行，跟join_buffer中的資料進行比對，形成結果集。如果小表的資料不能一次性全部讀入 join_buffer，需要分批讀入，則需要多次掃描大表。

sort_buffer（16講，17講）

mysql提供了兩種排序方法：全字段排序 和 rowid排序。

全字段排序：将結果集及排序所需的所有字段載入 sort_buffer，進行排序。如果資料量超出buffer size，需要用到臨時檔案做歸并排序。

rowid排序：隻把滿足條件的排序字段和主鍵字段載入 sort_buffer，排序後，根據主鍵，逐行回表擷取資料，組成結果集。如果sort_buffer裝不下，也要用臨時檔案輔助排序。但rowid排序需載入内容的數量小，能載入更多行，需要檔案輔助排序的機率小。

MySQL優先選擇全字段排序。

内部臨時表（第37講）

執行union語句、group by語句時，MySQL會自動建立内部臨時表，存儲中間資料，并進行排序、計數、sum、去重等操作。

記憶體臨時表預設大小為16M，如果超出這個大小，就會轉成磁盤臨時表，預設使用InnoDB引擎。

group by 語句如果能用上索引，就可以免去排序這個過程。

外部臨時表（第35講，36講）

create temporary table，隻對目前連接配接有效。read-only，也可以使用。

多用于優化join，減少對大表的掃描次數，應用 NLJ 或 BKA 算法。

如果binlog格式為statement/mixed，記錄臨時表相關操作。如果格式為row，不會記錄。

3. sql優化

sql優化，沒啥固定的招式。自己的體會，比較重要的是3條：

1. 了解sql執行原理，了解導緻sql慢的原因和常見的應對方法，了解每種方法的代價
2. 不要踩坑，比如：條件字段函數操作、隐式類型轉換、隐式字元編碼轉換（第18講）。這個有切膚之痛。有個sql，要查某一天的訂單，小夥伴寫了 date(...)=xxx，沒做好code review，就這樣上線了，第二天早上一起量，整個系統就挂了。。。
3. 做好code review，做好慢sql監控，多用explain等方式檢查執行計劃，不要偷懶

第二部分：資料庫配置和運維

作為資料庫的使用者，我希望運維來管理和處理哪些問題？

如果我負責資料庫的運維，會怎樣設定優先級，哪些能獨立完成，哪些需要其他崗位的配合？

對于資料庫來說，正常情況下 “資料可靠性” > “系統可用性” > 優先，運維要做的是在資料可靠的基礎上，盡量提高可用性。

為了達成這個目的，監控必不可少。做好應對問題的預案、定期演練，很重要。

1. 資料可靠性

日志的“雙1”設定（第23講）

innodb_flush_log_at_trx_commit = 1，每次事務的 redo log 都直接持久化。

sync_binlog = 1，每次事務的 binlog 都直接持久化。

關聯的還有2個參數：

binlog_group_commit_sync_delay

binlog_group_commit_sync_no_delay_count

這兩個參數跟資料可靠性無關。在磁盤IO成為瓶頸的情況下，可結合syn_binlog來提升可用性。

binlog格式（第24講）

三種格式：statement, row, mixed。

row格式，能更好地保障主庫和從庫之間（特别注意主備切換過程）的資料一緻性。

代價是磁盤空間

參考設定：

binlog_format = row

binlog_row_image = FULL

GTID（第27講）

用于解決“一主多從”結構下，主備切換時，新的主庫和從庫之間的資料同步。

備份

這個無需多說

版本

盡量升到高版本。

5.6版本引入GTID、并行複制，5.7版本引入SQL動态重寫。

semi-sync（第28講）

主庫在結束一個事務之前，等待至少一個從庫收到binlog的response。

可避免主庫在完成事務，但尚未向從庫發送binlog時損毀，導緻的資料丢失。

對可用性的影響較大，不建議打開。

2. 可用性：正常使用

磁盤IO（第12講）

innodb_io_capacity，建議設為磁盤的IOPS，可用fio工具來測試

redo log檔案，4個1G的檔案（記憶體128G、innodb_io_capacity為20000的情況）

緩存相關

join_buffer, sort_buffer：根據應用場景，調整其的大小

MRR可優化join，但從教程中資訊來看，MySQL似乎并不推薦，謹慎使用。（第35講）

change buffer：根據應用場景，判斷是否需要啟動。

事務隔離級别（第20講）

把binlog格式設為row，應對可以保證主從一緻。

InnoDB的預設事務隔離級别為RR。

RR的隔離性比RC好，解決了不可重複度問題和幻讀問題。因為增加了gap lock，鎖的數量大大增加，死鎖機率升高，死鎖檢測也要消耗資源。一緻性讀也要消耗資源。

在業務允許的情況下，調整為RC，對性能有幫助。

讀寫分離，負載均衡（28講）

一主多從，讀寫分離，這已是正常配備。

兩個問題：1）如何做負載均衡，2）過期讀

這裡隻說第一個問題，如何負載均衡。綜合來看，proxy轉發是比較好的選項。

在此基礎上，我傾向再拿一個從庫出來，讓某些查詢固定通過這個從庫來操作，例如：資料維護操作、某些運作時間長及時性要求低的統計查詢。

限制并發線程數（第29講）

innodb_thread_concurrency，預設為0，不限制。建議設定為 64~128 之間的值（或CPU核數的2倍）。

分庫

劃分業務領域，拆分為多個庫，分開存儲。

這是個系統工程，需要業務系統開發、資料庫運維合作。

3. 可用性：故障恢複

伺服器故障的情況下，主要的可用性名額，是多久可以恢複可用。

全量備份 + 增量備份

全量備份越頻繁，恢複時間越短，存儲空間代價也越大。

主備雙M配置

這個現在是常見手段，一旦主庫故障，備庫可以馬上頂上來。

從庫延遲備份

為了應對誤删資料問題，可考慮采用從庫延遲備份政策。

檢測資料庫是否出問題（29講）

有了這些備份，還需要做好監控，盡早判斷伺服器是否故障。

推薦用update 系統表 + performance_schema 這種方式（打開performace_schema有10%性能損失，19講）。

4. 監控

監控，必須單獨拿出來寫一下。

• 正常要監控的：
• 長事務
• 慢sql
• 主從延遲
• 鎖等待
• 執行次數
• update系統表 + performance_schema

做好監控和報警，才能心中不慌。

以上幾條，都是按“資料可靠性”>“系統可用性”來考慮的。

實際中也會遇到伺服器故障、短時間壓力峰值等情況，需要優先保證系統的可用，可在一段時間内，容忍少量資料錯誤。這種情況下，需要調整政策。

以後如果有了實戰經驗，再總結。

在課程中，有幾講實戰性特别強，遇到相關問題，可以先看下：

- 避免長事務，第3講，思考題

- 降低鎖沖突，第7講，内容及思考題

- 字元串字段索引，第11講，思考題及網友留言

- 業務高峰，MySQL壓力太大的應對，第22講，内容及網友留言

- 有損設定及其應對，第23講，思考題及網友留言

- 誤删資料後的恢複，第31講，内容及網友留言，各種血淚

系統運維要做好，首選是預防問題，其次是監控，争取在使用者感覺之前發現問題解決問題，最後沒辦法了才是問題的暴露和應對。

平時下功夫，才能少出問題，遇到問題才能快速應對：

• sql review（包括建索引）
• 監控：長事務、慢sql、主從延遲......
• 設立和推行規範：删資料、删字段、加字段、删表
• 問題處理工具化，定期演練

再推而廣之，系統的設計、開發，都要把工作做在前面，多問幾個為什麼，把原理搞清楚，把邏輯搞清楚。不斷學習，不斷總結，才能不斷提高。

本文内容為丁奇老師《MySQL實戰45講》的學習筆記，隻是一個提綱。

這門課程的内容群組織方式，每一講的思考題、大家的留言、老師的點評，都非常棒。

強烈推薦！

黃鶴

2019-12-11