MySQL的體系結構

• MySQL由以下組件組成: Connection Pool : 連接池組件 Management Services & Utilities : 管理服務和工具組件 SQL Interface : SQL接口組件 Parser : 查詢分析器組件 Optimizer : 優化器組件 Caches & Buffers : 緩沖池組件 Pluggable Storage Engines : 插件式存儲引擎組件 File System : 文件系統 Files & Logs : 文件和日志
• MySQL的各個層級: 連接層: 連接層是最上層的一些客戶端和連接服務,包含本地Socket通信和大多數基于客戶端或者服務端工具實現的類似于TCP/IP的通信 用于完成關于連接處理,授權認證和相關的安全方案 連接層中引入了線程池的概念,可以為通過安全認證接入的客戶端提供處理線程 連接層中可以實現基于SSL的安全連接 服務器會驗證每一個安全接入的客戶端的操作權限 服務層: 服務層主要用來完成數據庫服務器的大部分核心功能.比如SQL接口,緩存的查詢 ,SQL的分析和優化,內置函數的執行功能等. 所有與跨存儲引擎的相關功能也在服務層實現,比如存儲過程和存儲函數等 在服務層中,數據庫服務器會解析查詢并創建對應的內部解析樹,并完成相應的優化.比如確定表的查詢順序,是否利用索引等,最后生成相應的執行操作 如果是SELECT查詢語句,服務器會查詢內部的緩存來獲取數據.因此如果有足夠大的緩存空間,可以在提升大量讀操作環境中的系統性能 引擎層: 存儲引擎 .MySQL中的存儲引擎負責MySQL中數據的存儲和讀取,服務器是通過API和存儲引擎進行通信的 不同的存儲引擎具有不同的功能,可以根據實際需要,選取合適的存儲引擎 存儲層: 數據存儲層 .MySQL中的數據存儲層主要是將數據存儲在文件系統上,并且完成與存儲引擎的交互
• MySQL數據庫服務器與其余的數據庫服務器相比,MySQL數據庫服務器的架構可以在不同的場景下應用并發揮良好的作用.主要體現在存儲引擎上: 插件式的存儲引擎架構. 將查詢處理和其余系統任務以及數據的存儲提取相分離 這樣的業務架構是的可以根據業務的需求和實際需要選擇合適的存儲引擎

存儲引擎

基本概念

• MySQL中存在一個存儲引擎的概念,針對不同的存儲需求可以選擇最優化的存儲引擎
• 存儲引擎: 存儲引擎就是存儲數據,建立索引,更新查詢數據等技術的實現方式 存儲引擎是表類型的.因為存儲引擎是基于表的,而不是基于數據庫的
• MySQL和只有一種存儲引擎的Oracle,SqlServer等數據庫相比較 ,MySQL是一種插件式的存儲引擎架構.在MySQL中存在多種存儲引擎,可以根據需要使用相應的存儲引擎或者自定義編寫存儲引擎
• MySQL中支持的存儲引擎: InnoDB MyISAM BDB MEMORY MERGE EXAMPLE NDB Cluster AERCHIVE CSV BLACKHOLE FEDERATED 這里InnoDB和BDB是事務安全表,其余的存儲引擎是非事務安全表
• MySQL數據庫中創建新表如果不指定存儲引擎,就會使用默認的存儲引擎.在MySQL 5.5版本之前默認的存儲引擎是MyISAM, 在MySQL 5.5版本之后默認的存儲引擎是InnoDB
• 使用命令查詢當前數據庫支持的存儲引擎:

show engines;

• 使用命令查詢當前數據庫中存儲引擎相關的參數:

show variables like '%storage_engine%';

存儲引擎特性

• 幾種常用的存儲引擎的特性比較:

InnoDB

• InnoDB存儲引擎是MySQL數據庫服務器的默認存儲引擎
• InnoDB存儲引擎提供具有提交,回滾,崩潰恢復的事務安全機制
• InnoDB存儲引擎和MyISAM存儲引擎相比較存在的缺點: 處理效率比較低 為了保存數據和索引會占用更多的磁盤空間
• 事務安全:

-- 開啟事務 : 開啟事務后,可以進行數據庫的相關操作
start transaction;

-- 提交事務 : 進行數據庫的相關操作后,可以提交事務
commit;

• 外鍵約束: MySQL數據庫服務器中只有InnoDB存儲引擎支持外鍵 創建外鍵時,要求外鍵表對應的外鍵字段必須要有索引 使用外鍵的表在創建外鍵時,會自動創建對應的索引 示例: -- 外鍵表 CREATE TABLE country( id int NOT NULL AUTO_INCREMENT, country_name varchar(100) NOT NULL, PRIMARY KEY(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- 使用外鍵的表 CREATE TABLE city( id int NOT NULL AUTO_INCREMENT, city_name varchar(50) NOT NULL, country_id int NOT NULL, PRIMARY KEY(id), KEY idx_fk_country_id(country_id), CONSTRAINT 'fk_city_country' FOREIGN KEY(country_id) REFERENCES country(id) ON DELETE RESTRICT ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 復制代碼 更新外鍵表信息時,使用外鍵的表的外鍵相關信息也會自動更新
• 存儲方式: InnoDB存儲表和索引有兩種方式: 共享表空間存儲: 創建的表的表結構存儲在 .frm文件中 數據存儲在innodb_data_home_dir定義的表空間中,可以是多個文件 索引存儲在innodb_data_file_path定義的表空間中,可以是多個文件 多表空間存儲: 創建的表的表結構存儲在 .frm文件中 每個表的數據單獨存儲在 .ibd文件中 每個表的索引單獨存儲在 .ibd文件中

MyISAM

• MyISAM存儲引擎不支持事務,不支持外鍵 適合對事務的完整性沒有要求的應用 適合以SELECT或者INSERT為主要操作的應用
• MyISAM的優點是訪問速度快
• 不支持事務
• 存儲方式: 每個MyISAM存儲引擎的表在磁盤上存儲為三個文件,文件名都和表名相同,拓展名有以下三種: .frm - 創建的表的表結構 .MYD - MYDATA. 創建的表的數據 .MYI - MYIndex. 創建的表的索引

MEMORY

• MEMORY存儲引擎將表的數據存儲在內存中
• 每個MEMORY存儲引擎的表對應一個 .frm文件: .frm文件中只存儲創建的表的表結構 表的數據都是存儲在內存中
• MEMORY的數據都是存儲在內存中,并且默認使用HASH索引
• 優點: 數據存儲在內存中,有利于數據的快速處理,提高整個表的效率 MEMORY存儲引擎的表訪問的速度非?？?/li>
• 缺點: 不支持事務,不支持外鍵 如果服務關閉 ,MEMORY存儲引擎的表的表中的數據就會丟失

MERGE

• MERGE存儲引擎的表是一組MyISAM存儲引擎的表的組合 MyISAM存儲引擎的表的結構必須完全相同 MERGE存儲引擎的表不存儲數據 MERGE存儲引擎的表的查詢,更新和刪除操作都是對內部的MyISAM存儲引擎的表的操作
• MERGE存儲引擎的表的插入操作: 通過INSERT_METHOD子句定義插入的表,可以有3個不同的值 FIRST: 插入操作作用在第一個MyISAM存儲引擎的表上 LAST: 插入操作作用在最后一個MyISAM存儲引擎的表上 NO: 不定義子句或者將子句定義為NO, 表明不能對MEGER存儲引擎的表的內部的MyISAM存儲引擎的表進行插入操作
• DROP: 對MERGE存儲引擎的表執行DROP操作,只是刪除MERGE存儲引擎的表的定義,對MERGE存儲引擎的表的內部的MyISAM存儲引擎的表沒有任何影響
• 示例:

-- MyISAM存儲引擎的表
CREATE TABLE orderA(
	id int AUTO_INCREMENT,
	order_money double(10,2),
	order_address vachar(64),
	PRIMARY KEY(id)
)ENGINE=myisam DEFAULT CHARSET=utf8;

-- MyISAM存儲引擎的表
CREATE TABLE orderB(
	id int AUTO_INCREMENT,
	order_money double(10,2),
	order_address varchar(64),
	PRIMARY KEY(id)
)ENGINE=myisam DEFAULT CHARSET=utf8;

-- MERGE存儲引擎的表
CREATE TABLE order_all(
	id int AUTO_INCREMENT,
	order_money double(10,2),
	order_address varchar(64),
	PRIMARY KEY(id)
)ENGINE=merge 
UNION=(orderA,orderB)
INSERT_METHOD=LAST DEFAULT CHARSET=utf8

存儲引擎使用場景

• 選擇存儲引擎時,需要根據應用系統的特點選擇合適的存儲引擎
• 對于復雜的應用系統,可以根據實際情況選擇多種存儲引擎進行組合
• 常見的幾種存儲引擎的使用場景:

InnoDB: MySQL的默認存儲引擎.支持事務的處理,支持外鍵 適合應用于應用系統對事務的完整性有比較高的要求,在并發的條件下要求數據的一致性,數據操作除了插入和查詢以外,還包含很多的更新,刪除操作 InnoDB存儲引擎可以有效降低刪除和更新導致的鎖定,可以確保事務的完整提交和回滾 InnoDB引擎適合于類似計費系統和財務系統等對數據準確性要求很高的應用系統

• MyISAM: 適合應用于應用系統以讀操作和插入操作為主,只有很少的更新和刪除操作,并且對事務的完整性,并發性要求不高
• MEMORY: 將所有的數據存儲在內存中,在需要快速定位記錄和類似的數據應用場景下,可以非常高效的訪問數據 但是MEMORY引擎對存儲表的大小有限制,無法將數據量很大的表存儲到內存中 需要策略保證MEMORY引擎的表的數據可以恢復,保證數據庫異常中止后 ,MEMORY存儲引擎的表中的數據可以恢復 MEMORY存儲引擎適合應用于不頻繁進行更新的小表,可以非常高效的訪問數據
• MERGE: MERGE存儲引擎是將一系統相同數據結構的MyISAM存儲引擎的表以邏輯方式組合在一起,并作為一個對象來進行引用 MERGE存儲引擎的表突破了對單個MyISAM存儲引擎的表的大小限制,通過將不同的MyISAM存儲引擎的表分布在多個磁盤上,提高MERGE存儲引擎的表的數據的訪問效率 MERGE存儲引擎適合應用于數據倉儲等相關的VLDB環境

原文鏈接：
https://juejin.cn/post/7090909280609665031