MySQL的體系結(jié)構(gòu)

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

存儲引擎

基本概念

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

show engines;

• 使用命令查詢當前數(shù)據(jù)庫中存儲引擎相關(guān)的參數(shù):

show variables like '%storage_engine%';

存儲引擎特性

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

InnoDB

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

-- 開啟事務(wù) : 開啟事務(wù)后,可以進行數(shù)據(jù)庫的相關(guān)操作
start transaction;

-- 提交事務(wù) : 進行數(shù)據(jù)庫的相關(guān)操作后,可以提交事務(wù)
commit;

• 外鍵約束: MySQL數(shù)據(jù)庫服務(wù)器中只有InnoDB存儲引擎支持外鍵 創(chuàng)建外鍵時,要求外鍵表對應的外鍵字段必須要有索引 使用外鍵的表在創(chuàng)建外鍵時,會自動創(chuàng)建對應的索引 示例: -- 外鍵表 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; 復制代碼 更新外鍵表信息時,使用外鍵的表的外鍵相關(guān)信息也會自動更新
• 存儲方式: InnoDB存儲表和索引有兩種方式: 共享表空間存儲: 創(chuàng)建的表的表結(jié)構(gòu)存儲在 .frm文件中 數(shù)據(jù)存儲在innodb_data_home_dir定義的表空間中,可以是多個文件 索引存儲在innodb_data_file_path定義的表空間中,可以是多個文件 多表空間存儲: 創(chuàng)建的表的表結(jié)構(gòu)存儲在 .frm文件中 每個表的數(shù)據(jù)單獨存儲在 .ibd文件中 每個表的索引單獨存儲在 .ibd文件中

MyISAM

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

MEMORY

• MEMORY存儲引擎將表的數(shù)據(jù)存儲在內(nèi)存中
• 每個MEMORY存儲引擎的表對應一個 .frm文件: .frm文件中只存儲創(chuàng)建的表的表結(jié)構(gòu) 表的數(shù)據(jù)都是存儲在內(nèi)存中
• MEMORY的數(shù)據(jù)都是存儲在內(nèi)存中,并且默認使用HASH索引
• 優(yōu)點: 數(shù)據(jù)存儲在內(nèi)存中,有利于數(shù)據(jù)的快速處理,提高整個表的效率 MEMORY存儲引擎的表訪問的速度非常快
• 缺點: 不支持事務(wù),不支持外鍵 如果服務(wù)關(guān)閉 ,MEMORY存儲引擎的表的表中的數(shù)據(jù)就會丟失

MERGE

• MERGE存儲引擎的表是一組MyISAM存儲引擎的表的組合 MyISAM存儲引擎的表的結(jié)構(gòu)必須完全相同 MERGE存儲引擎的表不存儲數(shù)據(jù) MERGE存儲引擎的表的查詢,更新和刪除操作都是對內(nèi)部的MyISAM存儲引擎的表的操作
• MERGE存儲引擎的表的插入操作: 通過INSERT_METHOD子句定義插入的表,可以有3個不同的值 FIRST: 插入操作作用在第一個MyISAM存儲引擎的表上 LAST: 插入操作作用在最后一個MyISAM存儲引擎的表上 NO: 不定義子句或者將子句定義為NO, 表明不能對MEGER存儲引擎的表的內(nèi)部的MyISAM存儲引擎的表進行插入操作
• DROP: 對MERGE存儲引擎的表執(zhí)行DROP操作,只是刪除MERGE存儲引擎的表的定義,對MERGE存儲引擎的表的內(nèi)部的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

存儲引擎使用場景

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

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

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

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