MySQL存儲引擎與架構

1.1詳細了解數據庫類型

1.1.1關系型數據庫

常見產品：MySQL（免費）、Oracle
關系型數據庫模型是把復雜的數據結構歸結為簡單二維表格形式。通常該表第一行為字段名稱，描述該字段的作用，下面是具體的數據。在定義該表時需要指定字段的名稱及類型。
在關系型數據庫中，對數據的操作幾乎全部建立在一個或多個關系表格上。在大型系統中通常有多個表，且表之間有各種關系。實際使用就是通過對這些關聯的表格分類、合并、連接或選取等運算來實現數據庫的管理

1.1.2非關系型數據庫

常見產品：Redis、MongoDB
鍵值數據庫是一種非關系數據庫，它使用簡單的鍵值方法來存儲數據。鍵值數據庫將數據存儲為鍵值對集合，其中鍵作為唯一標識符

1.1.3列存儲數據庫

常見產品：HBase
對于行存儲數據庫，表中的數據是以行為單位逐行存儲在磁盤上的；而對于列存儲數據庫，表中的數據則是以列為單位逐列存儲在磁盤中。
平時的查詢大部分都是條件查詢，通常是返回某些字段（列）的數據。對于行存儲數據，數據讀取時通常將一行數據完全讀出，如果只需要其中幾列數據的情況，就會存在冗余列，出于縮短處理時間的考量，消除冗余列的過程通常是在內存中進行的。而列存儲，每次讀取的數據是集合的一段或者全部，不存在冗余性問題。這樣，通過這種存儲方式的調整，使得查詢性能得到極大的提升。

1.1.4搜索引擎存儲

常見產品：ElasticSearch
搜索引擎數據庫是應用在搜索引擎領域的數據存儲形式，由于搜索引擎會爬取大量的數據，并以特定的格式進行存儲，這樣在檢索的時候才能保證性能最優
原理：用ik分詞器將文檔分為詞條，對詞條創建索引，記錄詞條所在id，查詢時先根據詞條查詢文檔id，再根據文檔id查詢文檔

1.2圖解MySQL 內部架構及作用

Mysql架構主要分為連接層、server層和存儲引擎層，每一層 中都含有各自的很多小模塊，尤其是第二層，結構相當復雜的。下面我們就分別 針對 這三層做一個簡單的分析。我們看下圖體系結構：

1.2.1.Connectors

指的是不同語言中與SQL的交互，如php、java等。
1.2.2 系統管理和控制工具
系統管理和控制工具、內置工具和服務，專為數據庫的全生命周期管理設計，涵蓋 運維監控、數據維護、性能優化、安全管控 四大核心場景，旨在降低人工運維成本，提升系統穩定性與效率。

1.2.3 連接層

• 在連接建立時，MySQL通常會優先加載全局權限和數據庫級權限，因為這些權限最常用。
• 對于更細粒度的權限（如表級、列級權限），只有在執行具體的SQL操作時才會被加載。

• 用來客戶端連接器請求過來以后，Connection Handler驗證賬戶和密碼，不正確，立馬返回Access denied for user，此外還會驗證ip，若都驗證通過，則連接成功，緩存全局權限和數據庫級權限。
• 連接過程其實就是一個TCP連接的過程，此連接是一個長連接，注意，MySQL服務器與客戶端之間的通信是“半雙工”的，意味著任意時刻，要么是服務器向客戶端發送數據，要什么是客戶端向服務器發送數據（請求），不能同時進行。MySQL也不會讓你啥也不干一直連著，所以就有了超時時間，由wait_timeout控制(斷開是服務器端斷開，客戶端再請求過來就報Lost
  connection to MySQL server during query)
• 連接器采用池化技術，節省了創建和銷毀的成本；
• 默認只能連接151個客戶端，一個客戶端請求服務端分配一個線程（從線程里取），把線程池占滿了，再連就報連接滿了；
• 客戶端一般也采用池化技術，優化請求，防止每次執行SQL都需要建立連接，減少開銷；
• 長連接帶來一個問題，有些SQL在執行的過程中創建臨時表占用內存，連接不釋放，內存不回收，會導致MySQL占用內存漲的特別快，可以通過以下方式解決：
  • 定期斷開長連接（需要重連和權限驗證）
  • Druid會定期檢測空閑連接，默認的timeBetweenEvictionRunsMillis是60秒，minEvictableIdleTimeMillis是30分鐘，所以默認情況下，空閑超過30分鐘的連接會被釋放。

1.2.4 SQL接口

接受用戶的SQL命令，并且返回用戶需要查詢的結果。

1.2.5解析器

• MySQL在真正執行語句之前，會去parser你的查詢語言，了解你要做什么
• 在這個過程會判斷你的語法是否正確，不正確會報錯：You have an error in your SQL syntax;
• 將查詢字段，表，條件封裝到內部的數據結構上形成解析樹

1.2.6.查詢緩存

MySQL 8 將這塊刪除了，因為很雞肋：

• 緩存匹配條件嚴格：需 SQL 完全一致（包括空格、注釋），解析后即可確定是否匹配。
• 任何對表的寫操作（INSERT/UPDATE/DELETE）會標記該表關聯的所有緩存為無效。

• MySQL接收到請求后，會先看緩存中有沒有（之前查詢的結果會以k-v的方式存儲在內存里，k是語句，v是結果）
• 一般情況下查詢緩存的命中率是非常低的（除非你的數據是靜態的）
• 可以通過在select 后加 SQL_CACHE 來顯示指定使用查詢緩存

1.2.7 Optimizer: 查詢優化器。

• 通過語法解析,MySQL知道你的真實意圖了，但你寫的SQL不一定是高效的
• 這個時候MySQL會給我們的SQL做些優化調整（基于成本去優化），比如：使用哪個索引，外連接轉換為內連接，多表連接的時候，表的連接順序從而確定最終的執行計劃
• 例如：等價變換策略：比如 x<y and x=5 優化為y>5 and x=5
  聯合索引的位置調整：比如a，c，b聯合索引，b=3and a=5調整為a=5andb=3
  函數查詢優化：比如：min 直接從索引的左側開始查，max從右；

1.2.8.執行

• 執行器
  • 解析完了，也優化完了，那就該執行了
  • 別急，還沒完，你有權限嗎？沒有，直接拒絕執行，有才可以執行
  • 執行器操作的是下一層的存儲引擎

1.2.9 存儲引擎接口

• MySQL將操作封裝成了接口，屏蔽了不同存儲引擎的差異，各種存儲引擎實現了這些操作接口，內部又差異化的做了各種擴展；
• 比如InnoDB鎖的粒度到行級，InnoDB的事務，都是差異性的
• 我們可以通過show engines來查看支持的存儲引擎
• 存儲引擎同一個實例只能啟用一種

1.2.10文件系統層

• 文件系統由各操作系統提供
• MySQL將其持久化的數據物理存儲在磁盤上，持久化保存數據、索引、binlog、redolog、undolog、error日志、慢sql等；

1.3說明一條SQL請求的過程

1.3.1 連接

SQL客戶端與與服務器建立連接，該請求被發送到連接器，連接器鑒權，

1.3.2 語法解析

首先通過mysql關鍵字將語句解析，會生成一個內部解析樹，mysql解析器將對其解析，查看是否是有錯誤的關鍵字，關鍵字順序是否正確等；

1.3.3 查詢緩存

如果查詢命中緩存（一個大小寫敏感的哈希查找實現的）則直接返回結果，如果查詢沒有命中緩存，則進行下一步sql解析。

1.3.4 生成執行計劃

mysql是基于成本的優化器，他將預測執行此計劃的成本，并選擇成本最小的那條

1.3.5 調用存儲引擎接口執行

在解析和優化階段，MySQL將生成查詢對應的執行計劃，由執行計劃調用存儲引擎的API來執行查詢MySQL就行，將結果返回給客戶端；即使查詢不需要返回結果，MySQL也會返回影響到的行數。

1.4了解主流存儲引擎特性及選擇策略

1.4.1 InnoDB 引擎（默認）

1. 特性與優勢
   • 支持事務處理，具備 ACID 特性（原子性、一致性、隔離性、持久性），保證數據的完整性和可靠性。
   • 采用行級鎖，在高并發場景下能夠有效減少鎖沖突，提高并發處理能力。
   • 支持外鍵約束，便于維護表之間的關系，確保數據的一致性。
   • 具備良好的崩潰恢復能力，在數據庫發生故障時能夠快速恢復數據。
2. 劣勢
   • 相對 MyISAM 引擎，其讀寫性能在一些簡單查詢場景下可能稍遜一籌。
   • 數據存儲和索引占用空間相對較大。
3. 適用場景
   • 對事務完整性要求較高的應用，如電子商務系統、金融交易系統等。
   • 高并發讀寫的場景，例如在線事務處理（OLTP）系統。

1.4.2 MyISAM 引擎

1. 特性與優勢
   • 讀取速度快，特別適合執行大量的 SELECT 查詢操作，其表結構簡單，數據存儲緊湊。
   • 不支持事務和行級鎖，但支持表級鎖，在對數據一致性要求不高的讀多寫少場景下性能較好。
   • 支持全文索引，可用于高效的文本搜索，如博客系統、新聞網站等。
2. 劣勢
   • 不支持事務，無法保證數據的原子性和一致性，在數據更新頻繁的場景下可能導致數據問題。
   • 不支持外鍵約束，不利于表間關系的維護。
3. 適用場景
   • 以讀為主的應用，如 Web 應用中的靜態數據查詢，如用戶信息查詢、文章內容查詢等。
   • 對全文搜索有需求的場景，如文檔管理系統、論壇等。

1.4.3 Memory 引擎

1. 特性與優勢
   • 數據存儲在內存中，讀寫速度極快，適用于對讀寫性能要求極高的臨時數據存儲場景。
   • 支持哈希索引，能夠快速定位數據，提高查詢效率。
2. 劣勢
   • 數據存儲在內存中，一旦服務器關閉或重啟，數據將丟失，因此不適合存儲重要的持久化數據。
   • 內存資源有限，對數據量有一定限制，不適合存儲大量數據。
3. 適用場景
   • 用于存儲臨時數據或緩存數據，如會話數據、臨時計算結果等。
   • 對讀寫性能要求極高且數據量不大的場景，如一些實時統計系統中的中間計算結果存儲。

1.4.4 選擇策略

（一）根據應用需求選擇

1. 如果應用需要處理大量的事務操作，確保數據的完整性和一致性至關重要，那么 InnoDB 引擎是首選。例如，銀行系統中的賬戶交易處理，必須保證每一筆交易的原子性和數據的持久可靠，InnoDB 的事務支持和崩潰恢復能力能夠滿足這一需求。
2. 對于主要以讀取數據為主，對數據一致性要求不高，且數據更新較少的應用，如新聞網站的文章瀏覽功能，MyISAM 引擎可以提供快速的查詢性能，并且其全文索引功能可以方便用戶進行文章搜索。
3. 在需要臨時存儲和快速處理數據的場景，如在一個大型電子商務網站的購物車功能中，存儲用戶購物車中的商品信息（在用戶未結算前），Memory 引擎能夠快速讀寫數據，提升用戶體驗，不過需要注意數據的備份和在合適時機將數據持久化到其他存儲引擎中。

（二）考慮數據量和硬件資源

1. 如果數據量較大且硬件資源有限，InnoDB 引擎的高效存儲和良好的空間管理能力可能更適合。例如，一個大型企業的客戶關系管理系統，數據量隨著業務增長不斷增加，InnoDB 能夠在有限的硬件條件下較好地處理數據存儲和查詢。
2. 對于內存資源充足且數據量較小的場景，Memory 引擎可以充分利用內存的高速讀寫特性。比如，在一個小型實時監控系統中，用于存儲最近幾分鐘的監控數據，Memory 引擎可以快速處理這些數據，及時提供監控結果。

（三）權衡性能與功能

1. 如果對并發性能要求較高，InnoDB 的行級鎖可以有效減少鎖沖突，提高系統的并發處理能力。例如，在一個高并發的在線票務系統中，多個用戶同時查詢和預訂票務，InnoDB 能夠確保系統的高效運行。
2. 若應用對全文搜索功能有強烈需求，MyISAM 的全文索引支持可以提供高效的文本搜索能力。如在一個內容豐富的知識管理系統中，用戶需要快速搜索文檔內容，MyISAM 引擎可以滿足這一功能需求。