MySQL 的架構設計使其能夠支援從小型網站到大型企業應用，理解其架構對於高效能維運至關重要。MySQL 邏輯架構包含連線管理、安全、最佳化、執行以及儲存引擎等關鍵元件。連線管理透過執行緒池和 SSL/TLS 加密確保安全。查詢最佳化器根據統計資訊選擇最佳執行計劃。儲存引擎負責資料儲存和檢索，並透過 API 與伺服器互動。此外，MySQL 的並發控制機制，例如讀/寫鎖和鎖粒度，確保資料一致性。交易處理支援 ACID 特性，並提供多種隔離級別以滿足不同應用需求。交易日誌記錄所有變更，用於故障還原和複製。透過 Performance Schema，可以深入監控和分析資料函式庫執行時資訊，進一步提升效能和穩定性。
高效能MySQL：大規模維運的最佳實踐
前言
高效能MySQL第四版是由Silvia Botros和Jeremy Tinley編寫，Jeremy Cole作序。本文針對現代MySQL的維運和管理提供了深入的見解和實用的策略。
MySQL架構概述
MySQL的邏輯架構包括連線管理、安全、最佳化和執行等關鍵元件。並發控制是確保資料一致性的重要機制，主要透過讀/寫鎖和鎖粒度來實作。
連線管理與安全
MySQL的連線管理涉及執行緒池的管理和連線的安全性。透過SSL/TLS加密和使用者許可權管理，MySQL確保了資料傳輸的安全。
最佳化與執行
查詢最佳化是MySQL效能最佳化的核心。最佳化器根據統計資訊選擇最有效的查詢執行計劃。
並發控制
並發控制確保多個事務可以同時執行而不會損壞資料。MySQL使用讀/寫鎖來控制並發存取。
讀/寫鎖
讀鎖允許多個事務同時讀取資料，而寫鎖則確保在寫入資料時不會有其他事務幹擾。
鎖粒度
鎖粒度決定了鎖定的資料範圍。較粗的鎖粒度可能導致更多的鎖爭用，而較細的鎖粒度則可以提高並發度。
交易處理
交易是資料函式庫操作的邏輯單元。MySQL支援ACID特性的交易，確保資料的一致性和可靠性。
隔離級別
隔離級別定義了一個交易對其他交易的隔離程度。MySQL支援多種隔離級別，包括READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ和SERIALIZABLE。
死鎖
死鎖發生在兩個或多個交易互相等待對方釋放資源時。MySQL透過死鎖檢測機制來解決死鎖問題。
交易日誌
交易日誌記錄了所有交易的變更，用於故障還原和複製。
監控與可靠性工程
在可靠性工程的世界中，監控是確保系統穩定執行的關鍵。本章節討論瞭如何定義服務級別目標（SLO）、監控可用性、查詢延遲和錯誤率等關鍵指標。
定義服務級別目標（SLO）
SLO是衡量服務可靠性的標準。透過定義SLO，可以明確服務的品質目標。
監控解決方案
有效的監控解決方案需要能夠收集和分析系統的執行資料。MySQL提供了多種監控工具，如Performance Schema。
Performance Schema詳解
Performance Schema是MySQL用於收集資料函式庫執行時資訊的工具。本章節介紹瞭如何使用Performance Schema來監控和分析MySQL的效能。
簡介
Performance Schema提供了豐富的儀表化資料，幫助使用者瞭解MySQL的內部運作。
使用者組織
使用者可以根據需要組態Performance Schema，以收集特定的效能資料。
資源消耗
Performance Schema本身的資源消耗很低，不會對系統效能造成明顯影響。
使用Performance Schema
本文介紹瞭如何使用Performance Schema來檢查SQL陳述式的效能、讀寫效能、後設資料鎖、記憶體使用情況等。
MySQL 效能最佳化
MySQL 是一種廣泛使用的開源資料函式倉管理系統，其效能對於許多應用程式的運作至關重要。為了確保 MySQL 資料函式庫能夠高效運作，我們需要對其進行最佳化。本文將介紹 MySQL 效能最佳化的多個方面，包括硬體和作業系統的最佳化、伺服器設定的最佳化、資料函式庫設計的最佳化、查詢效能的最佳化、複製和備份策略等。
硬體和作業系統最佳化
MySQL 的效能受到硬體和作業系統的影響。選擇合適的硬體和最佳化作業系統設定可以顯著提高 MySQL 的效能。
CPU 選擇
選擇適合的 CPU 是最佳化 MySQL 效能的第一步。MySQL 的效能與 CPU 的處理能力密切相關，因此選擇高效能的 CPU 可以提高查詢處理速度。
記憶體和磁碟資源平衡
MySQL 的效能取決於記憶體和磁碟資源的平衡。足夠的記憶體可以減少磁碟 I/O 操作，從而提高效能。
固態硬碟儲存
固態硬碟儲存（SSD）比傳統硬碟具有更快的讀寫速度，可以顯著提高 MySQL 的效能。
RAID 組態
RAID（獨立磁碟冗餘陣列）組態可以提高資料的安全性和效能。選擇合適的 RAID 級別對於 MySQL 的效能至關重要。
伺服器設定最佳化
MySQL 的伺服器設定對於其效能有著直接的影響。最佳化伺服器設定可以提高 MySQL 的處理能力和穩定性。
組態檔案
MySQL 的組態檔案（my.cnf 或 my.ini）包含了伺服器的各種設定引數。正確組態這些引數對於最佳化 MySQL 效能至關重要。
記憶體使用組態
組態 MySQL 的記憶體使用對於其效能至關重要。需要根據實際的工作負載調整記憶體相關的引數，如 InnoDB Buffer Pool 的大小。
I/O 行為組態
MySQL 的 I/O 行為對於其效能有著重要的影響。組態 InnoDB 的交易日誌和表空間可以提高 I/O 效率。
資料函式庫設計最佳化
良好的資料函式庫設計是 MySQL 效能最佳化的基礎。合理的資料型別選擇、索引設計和表格結構設計都可以提高查詢效率。
資料型別選擇
選擇合適的資料型別可以減少儲存空間並提高查詢效率。例如，使用整數型別儲存整數資料，使用日期時間型別儲存日期和時間資料。
索引設計
索引是提高查詢效率的重要手段。合理設計索引可以顯著提高查詢速度。
查詢效能最佳化
查詢效能是 MySQL 效能的一個重要方面。最佳化查詢陳述式和使用高效的查詢技術可以顯著提高 MySQL 的效能。
查詢最佳化
分析查詢陳述式並進行最佳化可以提高查詢效率。使用 EXPLAIN 陳述式分析查詢計畫是查詢最佳化的重要步驟。
索引最佳化
合理使用索引可以顯著提高查詢效率。避免在不必要的欄位上建立索引，並定期維護索引。
複製和備份策略
MySQL 的複製和備份策略對於資料的安全性和可用性至關重要。
複製組態
組態 MySQL 複製可以提高資料的可用性和容災能力。選擇合適的複製格式和組態引數對於複製的效能和穩定性至關重要。
備份策略
制定合理的備份策略可以確保資料的安全性。使用 mysqldump、Percona XtraBackup 等工具進行備份，並定期檢查備份的有效性。
高效能 MySQL 第四版序言與導讀
前言解析
Jeremy Cole 在序言中提及，《高效能 MySQL》自第一版問世以來，已成為資料函式倉管理員（DBA）、系統工程師和資料函式庫開發者必備的參考書籍。隨著 MySQL 的演進和社群的變化，本文第四版由 Silvia 和 Jeremy 全面更新，以適應現代 MySQL 維運的需求。
本文定位與目標讀者
本文作為 Oracle 官方檔案的補充，旨在幫助讀者深入瞭解如何充分利用 MySQL 作為強大的資料平台。主要針對具備一定 RDBMS 知識背景的工程師，特別是在執行 MySQL 於大規模環境下的專業人士。
本版主要更新與特點
第四版著重於以下幾大變革：
- MySQL 生態系統的重大變化：自第三版以來，MySQL 發布了多個主要版本，工具鏈生態也從簡單的 Perl 和 Bash 指令碼擴充套件至更全面的工具解決方案。
- DBA 角色的演變：傳統 DBA 的角色已經轉變，不再只是「Don’t Bother Asking」的守門人，而是轉向成為業務成長的推動者，負責指導開發人員並管理系統以支援快速且安全的 schema 變更。
- 新的挑戰與需求：現代團隊面臨諸如 schema 變更、合規性問題和分片等挑戰，本文旨在提供全面的，幫助企業在大規模環境下執行 MySQL。
重點內容解析
合規性與安全性
本版擴充套件了對合規性和安全性的討論，涵蓋以下主題：
- Health Insurance Portability and Accountability Act (HIPAA)
- Federal Risk and Authorization Management Program (FedRAMP)
- General Data Protection Regulation (GDPR)
- Schrems II
這些法規要求企業在設計技術架構時必須考慮資料隱私和主權問題，從而引入新的複雜性。
安全控制措施的建設
- Secrets 管理：如何安全地管理和儲存敏感資訊。
- 角色與資料的分離：實施適當的許可權控制，確保資料存取的安全性。
- 變更追蹤：建立完善的變更記錄機制，以監控和管理變更。
- 備份與還原程式：制定可靠的備份和災難還原策略，以保障資料的安全性和可用性。
本文的價值與影響
本文不僅提供了對 MySQL 內部設計和擴充套件策略的深入見解，還幫助讀者培養系統化的方法來設計、維護和故障排除根據 MySQL 的架構。對於希望在大規模環境中高效執行 MySQL 的工程師和技術人員來說，本文是一份寶貴的資源。
結語
《高效能 MySQL》第四版是針對現代 MySQL 維運挑戰的全面。透過本文，讀者可以獲得所需的知識和技能，以應對日益複雜的技術環境和不斷變化的法規要求。無論是對於經驗豐富的 DBA 還是新入門的工程師，本文都具有重要的參考價值。
本文所使用的排版慣例
本文採用以下排版慣例：
排版樣式說明
- 斜體字：用於表示新術語、網址、電子郵件地址、檔案名稱及副檔名。
等寬字型：用於程式清單，以及段落中指涉的程式元素，如變數或函式名稱、資料函式庫、資料型別、環境變數、陳述式和關鍵字。等寬粗體字：用於表示使用者應當逐字輸入的命令或其他文字。等寬斜體字：用於表示應當被使用者提供的值或由上下文決定的值所替換的文字。
特殊符號與注意事項
本文使用以下特殊符號來標示不同型別的資訊： 此圖示表示提示或建議。 此圖示表示一般性註解。 此圖示表示警告或注意事項。
如何聯絡我們
我們為本文設立了一個網頁，列出了勘誤、範例及任何額外的資訊。您可以透過以下網址存取該網頁：https://oreil.ly/hiperfmysql_2e。
您也可以透過以下管道與我們聯絡：
第四版致謝
Silvia 的致謝
首先，我要感謝我的家人。我的父母為了讓我和弟弟來到美國，放棄了穩定的工作和生活。我的丈夫 Armea，在我職業生涯中面臨一個又一個挑戰時給予了我支援，最終促成了這本文的完成。
感謝與啟發
我剛進入科技行業時，還是個剛從中東來到美國的移民。在加州的一所州立大學獲得學位後，我在紐約市找到了一份工作。我記得這本文的第二版是我用自己賺的錢買的第一本科技書籍，而不是大學教材。前幾版的作者教會了我很多基礎知識，這些知識為我在職業生涯中管理資料函式庫做好了準備。
我非常感謝在我的職業生涯中遇到過的許多人的支援。他們的鼓勵促使我寫下了這本文的最新版本，這本文在我的職業生涯早期教會了我很多東西。我要感謝 Tim Jenkins，前 SendGrid 的 CTO，他在我面試時告訴他我認為他使用 MySQL 複製的方式是錯誤的，卻仍然錄用了我，並信任我處理後來被證明是非常成功的專案。
特別感謝
我要感謝所有在科技領域支援我的女性朋友們。特別感謝 Camille Fournier 和 Nicole Forsgren 博士，他們的書籍影響了我過去幾年的職業生涯，並改變了我對日常工作的看法。
職場感謝
感謝我在 Twilio 的團隊。感謝 Sean Kilgore，讓我成為一個更好的工程師，不僅僅關心資料函式庫。感謝 John Martin，是你給予我無限的樂觀。感謝 Laine Campbell 和她的 PalominoDB 團隊（後來被 Pythian 收購），在最艱難的時期支援我並教會我很多。感謝 Baron Schwartz，鼓勵我寫下我的經驗。
最後，感謝 Virginia Wilson，她是一位優秀的編輯，幫助我把我的想法變成有意義的句子，並在整個過程中給予我很多支援和關懷。
Jeremy 的致謝
當 Silvia 邀請我協助這本文的時候，正值全球疫情爆發的時期（2020 年）。那時我很猶豫是否要增加更多的壓力給自己。我的妻子 Selena 告訴我，如果我不接受這個邀請，我一定會後悔。我深知不該與她爭論。她一直支援並鼓勵我成為最好的自己。我將永遠感激她為我做的一切。
感謝與啟發
我要感謝我的家人、同事和社群朋友們。如果沒有你們，我不可能走到今天。你們教會了我如何成為今天的自己。我的職業生涯是我與你們所有人共同經歷的總和。你們教會了我如何接受批評，如何以身作則長官，如何失敗並重新站起來，以及最重要的，整體大於個體之和。
最後，我要感謝 Silvia，她信任我為這本文帶來了共同的理解和不同的視角。我希望我達到了您的期望。
對技術審閱者的感謝
作者還要感謝幫助本文成形的技術審閱者們：Aisha Imran、Andrew Regner、Baron Schwartz、Daniel Nichter、Hayley Anderson、Ivan Mora Perez、Jam Leoni、Jaryd Remillard、Jennifer Davis、Jeremy Cole、Keith Wells、Kris Hamoud、Nick Vyzas、Shubheksha Jalan、Tom Krouper 和 Will Gunty。感謝你們的時間和努力。
MySQL 架構概述
MySQL 的架構特性使其能夠適用於廣泛的應用場景。雖然它並非完美，但其靈活性足以在小型和大型環境中良好運作。這些環境包括從個人網站到大型企業級應用程式。要充分利用 MySQL，需要了解其設計，以便與其協同工作，而不是與其對抗。
本章節提供了 MySQL 伺服器架構的高層次概述、主要儲存引擎之間的差異，以及這些差異的重要性。我們試圖透過簡化細節和展示範例來解釋 MySQL。這種討論對於那些新接觸資料函式庫伺服器的人以及熟悉其他資料函式庫伺服器的讀者都將有所幫助。
MySQL 的邏輯架構
瞭解 MySQL 各元件如何協同工作的良好思維圖景將有助於理解伺服器。圖 1-1 顯示了 MySQL 架構的邏輯檢視。
最上層是客戶端，包含了 MySQL 獨有的服務。它們是大多數根據網路的客戶端/伺服器工具或伺服器所需的服務：連線處理、身份驗證、安全等。
第二層是事情變得有趣的地方。MySQL 的大部分核心程式碼都在這裡，包括查詢解析、分析、最佳化以及所有內建函式（例如日期、時間、數學和加密）的程式碼。跨儲存引擎提供的任何功能都位於此層級：儲存過程、觸發器和檢視等。
第三層包含了儲存引擎。它們負責儲存和檢索儲存在 MySQL 中的所有資料。就像 GNU/Linux 上可用的各種檔案系統一樣，每個儲存引擎都有其優缺點。伺服器透過儲存引擎 API 與它們進行通訊。此 API 隱藏了不同儲存引擎之間的差異，並使其在查詢層面上基本透明。它還包含幾十個低階函式，用於執行諸如“開始事務”或“根據主鍵提取行”等操作。儲存引擎不會解析 SQL 或相互通訊；它們只是回應來自伺服器的請求。
圖 1-1. MySQL 伺服器架構的邏輯檢視
連線管理與安全性
預設情況下，每個客戶端連線在伺服器程式中獲得自己的執行緒。該連線的查詢在該單一執行緒中執行，而該執行緒又駐留在某個核心或 CPU 上。伺服器維護了一個可重複使用的執行緒快取，因此不需要為每個新連線建立和銷毀執行緒。
當客戶端（應用程式）連線到 MySQL 伺服器時，伺服器需要對其進行身份驗證。身份驗證根據使用者名稱、來源主機和密碼。X.509 證書也可以用於透過傳輸層安全性（TLS）連線進行身份驗證。
一旦客戶端連線成功，伺服器會驗證客戶端是否具有執行每個查詢的許可權（例如，客戶端是否有權對 world 資料函式庫中的 Country 表執行 SELECT 陳述式）。
最佳化與執行
MySQL 解析查詢以建立內部結構（解析樹），然後應用各種最佳化技術。這可以包括重寫查詢、決定讀取表的順序、選擇要使用的索引等。你可以透過查詢中的特殊關鍵字向最佳化器傳遞提示，影響其決策過程。你還可以要求伺服器解釋各種最佳化方面的內容。這樣你就能知道伺服器正在做出什麼樣的決策，並為重新調整查詢、架構和設定提供參考，以便一切執行得盡可能高效。第 8 章將對此進行詳細介紹。
最佳化器並不真正關心特定表使用的儲存引擎，但儲存引擎確實會影響伺服器如何最佳化查詢。最佳化器會詢問儲存引擎關於其某些功能和某些操作的成本，以及有關表資料的統計資訊。例如，一些儲存引擎支援對某些查詢有幫助的索引型別。你可以在第 6 章和第 7 章中閱讀更多關於架構最佳化和索引的內容。
在舊版本中，MySQL 使用內部查詢快取來檢視是否可以從中提供結果。然而，隨著並發性的增加，查詢快取成為了一個著名的瓶頸。從 MySQL 5.7.20 開始，查詢快取被正式廢棄，而在 8.0 版本中，查詢快取被完全移除。儘管查詢快取不再是 MySQL 伺服器的核心部分，但快取頻繁提供的結果集是一種好的做法。雖然這超出了本文的範圍，但一種流行的設計模式是將資料快取在 memcached 或 Redis 中。
並發控制
任何時候有多個查詢需要同時更改資料時，就會出現並發控制問題。在本章中，MySQL 需要在兩個層級上進行並發控制：伺服器層級和儲存引擎層級。我們將簡要概述 MySQL 如何處理並發讀者和寫入者，以便為本章的其餘部分提供背景。
為了說明 MySQL 如何處理同一組資料上的並發操作，我們將使用一個傳統的試算表檔案作為範例。試算表由行和列組成，就像資料函式庫表一樣。假設該檔案位於你的筆記型電腦上，只有你可以存取它，因此沒有潛在的衝突；只有你可以對檔案進行更改。
現在，假設你需要與同事協同處理該試算表。它現在位於一個分享伺服器上，你們兩人都可以存取。當你們兩人都需要同時對該檔案進行更改時會發生什麼？如果我們有整個團隊的人都在積極地編輯、新增和刪除試算表中的單元格怎麼辦？
我們可以說他們應該輪流進行更改，但這效率不高。我們需要一種允許對高容量試算表進行並發存取的方法。
@startuml
skinparam backgroundColor #FEFEFE
skinparam defaultTextAlignment center
skinparam rectangleBackgroundColor #F5F5F5
skinparam rectangleBorderColor #333333
skinparam arrowColor #333333
title 並發控制
rectangle "連線處理" as node1
rectangle "身份驗證" as node2
rectangle "查詢解析" as node3
rectangle "儲存引擎 API" as node4
rectangle "資料儲存" as node5
node1 --> node2
node2 --> node3
node3 --> node4
node4 --> node5
@enduml
此圖示說明瞭 MySQL 的基本架構及處理客戶端請求的流程
內容解密：
- 客戶端請求：客戶端向 MySQL 伺服器發起連線請求。
- 連線處理：MySQL 伺服器處理連線請求，並分配執行緒。
- 身份驗證：MySQL 對客戶端進行身份驗證，確認其合法性。
- 安全性檢查：檢查客戶端的許可權，確保其有權執行特定操作。
- 查詢解析：MySQL 解析客戶端的 SQL 查詢。
- 最佳化與執行：最佳化查詢計劃並執行查詢。
- 儲存引擎 API：與儲存引擎進行互動，將資料讀寫操作委託給儲存引擎。
- 資料儲存：最終將資料儲存在磁碟上。
此架構展示了 MySQL 如何有效地處理客戶端請求，並透過不同的元件協同工作來提供高效的資料儲存和檢索服務。