概括
MySQL 是一個基於 C 和 C++ 程式語言開發的關聯式資料庫管理系統，由 MySQL AB 創建，該公司由 David Axmark、Allan Larsson 和 Michael Widenius 創立。最新版本是MySQL 9.0。
MySQL 是一個具有 GNU 授權的開源專案。
MySQL是現在最受歡迎、最實用的資料庫管理系統，因為它開源、功能齊全、效能高。
建築學
客戶端層
- MySQL系統架構第一層。
- 這一層的重要服務是連線處理、驗證和安全性。
- 連線處理：管理來自客戶端的連線。當客戶端想要連接到 MySQL 伺服器時，會為該連線建立一個新執行緒。建立新連線時，伺服器會快取執行緒。
- 身份驗證：當客戶端連接到伺服器時，它必須提供身份驗證資訊才能建立連線。通常，客戶端使用使用者名稱和密碼進行身份驗證。
- 安全性：MySQL 提供了很多功能來保護資料庫並在身份驗證後驗證使用者權限。
伺服器層
- MySQL 的下一層負責處理查詢語句並提供許多實用程式。
- 主要特點：
- 解析器：當客戶端請求查詢語句時，伺服器會對其進行分析以建立查詢樹，重寫查詢，並定義執行查詢語句的順序和索引。
- 最佳化器：它在執行之前會最佳化查詢語句。優化器並不關心使用什麼引擎，但儲存引擎會影響特定查詢的最佳化。
- 查詢快取：查詢快取儲存查詢語句的結果。如果客戶端再次要求相同的查詢，伺服器將傳回快取的結果，繞過解析和最佳化步驟。由於瓶頸問題，此功能在 8.0 或更高版本中已被棄用。
- 服務與實用程式：
- 備份與復原
- 安全性：提供者使用者和權限系統
- 複製：這是一個將資料從主伺服器複製並同步到許多子伺服器的過程。
- 集群
- 分割區：使用特定邏輯將表格分成許多部分。
- Workbench：它是一個視覺化工具，旨在與 MySQL 互動和使用。
儲存層
- 這是我們在資料庫中儲存資料的方式的一層責任。
- 預設情況下，MySQL 使用 InnoDB 儲存引擎。
- MySQL支援許多不同的儲存引擎：
- InnoDB（預設）
- MyISAM
- 記憶
- CSV
- 存檔
- 黑洞
- 合併
- 聯邦
優化 MySQL 效能
最佳化SQL語句
解釋聲明
explain sql_statememnt
登入後複製
explain analyze sql_statement
登入後複製
索引技術
- 它提升了查詢和搜尋時的效能。
- 為多列建立索引時，第一列非常重要。它決定查詢語句的效能。例如，如果您在（姓名，生日）上建立索引，則使用「姓名」列的查詢將利用該索引，但使用「生日」列的查詢則不會。
- 建立新索引的語法：
create index idx_birthday on customers(birthday);
登入後複製
- 該命令將使查詢最佳化器無法存取指定的索引，從而阻止查詢執行計劃考慮它：
alter table customers alter index idx_birthday invisible;
登入後複製
分割
- 當資料庫大小大於2GB（或超過1000萬筆記錄）時，應該對該資料庫使用分割區。
- 使用分割區時，效能將會提高。
- 分區計畫應該是基於WHERE子句中常用的欄位。
優化資料庫參數
緩衝區快取命中
- 它是系統從緩衝區快取而不是從實體儲存載入的資料的百分比。
- 如果百分比低於90%，那麼你必須對其進行最佳化。
- 取決於2個參數：
-
Innodb_buffer_pool_read_requests：傳送到緩衝池的請求總數。指令：顯示全域狀態，如 '%Innodb_buffer_pool_read_requests%
-
Innodb_buffer_pool_reads：記憶體中緩衝池中找不到的必須從磁碟讀取的請求總數。指令：顯示全域狀態，如 '%Innodb_buffer_pool_reads%
- 計算公式：
(Innodb_buffer_pool_read_requestss - Innodb_buffer_pool_reads) x100 / Innodb_buffer_pool_read_requests
登入後複製
表緩存命中
- MySQL 會快取表以便在記憶體中快速查詢。
- 如果百分比低於80%，那麼你必須對其進行最佳化。
- 取決於2個參數：
-
Open_tables：在快取中開啟的表總數。指令：顯示全域狀態，如“Open_tables”
-
Opened_tables：開啟的表格總數。指令：顯示全域狀態，如“Opened_tables”
- 計算公式：Open_tables/Opened_tables
表定義緩存命中
- 執行查詢時，MySQL 需要有關表的信息，例如表名、列數和行數。此資訊稱為表定義。
- 如果百分比低於80%，那麼你必須對其進行最佳化。
- 取決於2個參數：
-
Open_table_definitions：快取中的總定義表。指令：顯示全域狀態，如“Open_table_definitions”
-
Opened_tables：請求的定義表總數。指令：顯示全域狀態，如“Opened_tables”
- 計算公式：Open_table_definitions/Opened_table_definitions
內存中的臨時表
- 許多 SQL 語句（例如 Order、Group By...）必須使用臨時表。
- 如果百分比低於80%，那麼你必須對其進行最佳化。
- 取決於2個參數：
-
Created_tmp_disk_tables：在磁碟上建立的臨時表總數。指令：顯示全域狀態，如“%Created_tmp_disk_tables%”
-
Created_tmp_tables：建立的臨時表總數。指令：顯示全域狀態，如“%Created_tmp_tables%”
- 計算公式：(Created_tmp_tables - Created_tmp_disk_tables)/ Created_tmp_tables
結論
該部落格提供有關 MySQL 的基於研究的知識。 MySQL 是一種關聯式資料庫管理系統，以其低成本、廣泛的實用程式和高效能而聞名，使其適用於廣泛的專案。雖然本部落格涵蓋了 MySQL 的許多功能和方面，但還有更多內容值得探索。我將在未來的部落格中更深入地研究其他功能和知識。
感謝您的閱讀，我們下一篇部落格見。
參考文件
- MySQL 文件
- MySQL 課程 - Tran Quoc Huy
- 維基百科 - MySQL
以上是MySQL架構研究及效能最佳化的詳細內容。更多資訊請關注PHP中文網其他相關文章！
