MySQL架構研究及效能最佳化

概括

MySQL 是一個基於 C 和 C++ 程式語言開發的關聯式資料庫管理系統，由 MySQL AB 創建，該公司由 David Axmark、Allan Larsson 和 Michael Widenius 創立。最新版本是MySQL 9.0。

MySQL 是一個具有 GNU 授權的開源專案。

MySQL是現在最受歡迎、最實用的資料庫管理系統，因為它開源、功能齊全、效能高。

建築學

• 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中文網其他相關文章！