1. MySQL的體系結(jié)構(gòu)
1.1 整個MySQL Server由以下組成
- Connection Pool : 連接池組件
- Management Services & Utilities : 管理服務和工具組件
- SQL Interface : SQL接口組件
- Parser : 查詢分析器組件
- Optimizer : 優(yōu)化器組件
- Caches & Buffers : 緩沖池組件
- Pluggable Storage Engines : 存儲引擎
- File System : 文件系統(tǒng)
- 連接層
最上層是一些客戶端和鏈接服務托享，包含本地sock 通信和大多數(shù)基于客戶端/服務端工具實現(xiàn)的類似于 TCP/IP的通信。主要完成一些類似于連接處理籽御、授權(quán)認證肢执、及相關(guān)的安全方案枉阵。在該層上引入了線程池的概念，為通過認證安全接入的客戶端提供線程预茄。同樣在該層上可以實現(xiàn)基于SSL的安全鏈接兴溜。服務器也會為安全接入的每個客戶端驗證它所具有的操作權(quán)限。
- 服務層
第二層架構(gòu)主要完成大多數(shù)的核心服務功能耻陕，如SQL接口拙徽，并完成緩存的查詢，SQL的分析和優(yōu)化诗宣，部分內(nèi)置函數(shù)的執(zhí)行膘怕。所有跨存儲引擎的功能也在這一層實現(xiàn)，如 過程召庞、函數(shù)等岛心。在該層来破，服務器會解析查詢并創(chuàng)建相應的內(nèi)部解析樹，并對其完成相應的優(yōu)化如確定表的查詢的順序忘古，是否利用索引等徘禁， 最后生成相應的執(zhí)行操作。如果是select語句存皂，服務器還會查詢內(nèi)部的緩存晌坤，如果緩存空間足夠大，這樣在解決大量讀操作的環(huán)境中能夠很好的提升系統(tǒng)的性能旦袋。
- 引擎層
存儲引擎層骤菠， 存儲引擎真正的負責了MySQL中數(shù)據(jù)的存儲和提取，服務器通過API和存儲引擎進行通信疤孕。不同的存儲引擎具有不同的功能商乎，這樣我們可以根據(jù)自己的需要，來選取合適的存儲引擎祭阀。
- 存儲層
數(shù)據(jù)存儲層鹉戚， 主要是將數(shù)據(jù)存儲在文件系統(tǒng)之上，并完成與存儲引擎的交互专控。
- 和其他數(shù)據(jù)庫相比抹凳，MySQL有點與眾不同，它的架構(gòu)可以在多種不同場景中應用并發(fā)揮良好作用伦腐。主要體現(xiàn)在存儲引擎上赢底，插件式的存儲引擎架構(gòu)，將查詢處理和其他的系統(tǒng)任務以及數(shù)據(jù)的存儲提取分離柏蘑。這種架構(gòu)可以根據(jù)業(yè)務的需求和實際需要選擇合適的存儲引擎幸冻。
2. 存儲引擎
2.1 存儲引擎概述
和大多數(shù)的數(shù)據(jù)庫不同, MySQL中有一個存儲引擎的概念, 針對不同的存儲需求可以選擇最優(yōu)的存儲引擎。
存儲引擎就是存儲數(shù)據(jù)咳焚，建立索引洽损，更新查詢數(shù)據(jù)等等技術(shù)的實現(xiàn)方式 。存儲引擎是基于表的革半，而不是基于庫的碑定。所以存儲引擎也可被稱為表類型。
Oracle又官，SqlServer等數(shù)據(jù)庫只有一種存儲引擎不傅。MySQL提供了插件式的存儲引擎架構(gòu)。所以MySQL存在多種存儲引擎赏胚，可以根據(jù)需要使用相應引擎访娶，或者編寫存儲引擎。
MySQL5.0支持的存儲引擎包含 ： InnoDB 觉阅、MyISAM 崖疤、BDB秘车、MEMORY、MERGE劫哼、EXAMPLE叮趴、NDB Cluster、ARCHIVE权烧、CSV眯亦、BLACKHOLE、FEDERATED等般码，其中InnoDB和BDB提供事務安全表妻率，其他存儲引擎是非事務安全表。
可以通過指定 show engines 板祝， 來查詢當前數(shù)據(jù)庫支持的存儲引擎 ：
創(chuàng)建新表時如果不指定存儲引擎宫静，那么系統(tǒng)就會使用默認的存儲引擎，MySQL5.5之前的默認存儲引擎是MyISAM券时，5.5之后就改為了InnoDB孤里。
查看Mysql數(shù)據(jù)庫默認的存儲引擎 ， 指令 ：
show variables like '%storage_engine%' ;
2.2 MySQL 中各種存儲引擎的特性
- 下面重點介紹幾種常用的存儲引擎橘洞， 并對比各個存儲引擎之間的區(qū)別捌袜， 如下表所示 ：
| 特點 | InnoDB | MyISAM | MEMORY | MERGE | NDB |
|---|---|---|---|---|---|
| 存儲限制 | 64TB | 有 | 有 | 沒有 | 有 |
| 事務安全 | 支持 | ||||
| 鎖機制 | 行鎖(適合高并發(fā)) | 表鎖 | 表鎖 | 表鎖 | 行鎖 |
| B樹索引 | 支持 | 支持 | 支持 | 支持 | 支持 |
| 哈希索引 | 支持 | ||||
| 全文索引 | 支持(5.6版本之后) | 支持 | |||
| 集群索引 | 支持 | ||||
| 數(shù)據(jù)索引 | 支持 | 支持 | 支持 | ||
| 索引緩存 | 支持 | 支持 | 支持 | 支持 | 支持 |
| 數(shù)據(jù)可壓縮 | 支持 | ||||
| 空間使用 | 高 | 低 | N/A | 低 | 低 |
| 內(nèi)存使用 | 高 | 低 | 中等 | 低 | 高 |
| 批量插入速度 | 低 | 高 | 高 | 高 | 高 |
| 支持外鍵 | 支持 |
- 下面我們將重點介紹最常使用的兩種存儲引擎： InnoDB、MyISAM 炸枣， 另外兩種 MEMORY琢蛤、MERGE ， 了解即可抛虏。
2.2.1 InnoDB
InnoDB存儲引擎是Mysql的默認存儲引擎。InnoDB存儲引擎提供了具有提交套才、回滾迂猴、崩潰恢復能力的事務安全。但是對比MyISAM的存儲引擎背伴，InnoDB寫的處理效率差一些沸毁，并且會占用更多的磁盤空間以保留數(shù)據(jù)和索引。
InnoDB存儲引擎不同于其他存儲引擎的特點 ：
事務控制
- 創(chuàng)建一個新的數(shù)據(jù)庫創(chuàng)建一張新的表 :
create database index_demo_02; -- 創(chuàng)建數(shù)據(jù)庫
create table goods_innodb( -- 創(chuàng)建表
id int NOT NULL AUTO_INCREMENT,
name varchar(20) NOT NULL,
primary key(id)
)ENGINE=innodb DEFAULT CHARSET=utf8;
- 新開兩個窗口并開啟事務 :
start transcation; -- 開啟事務
insert into goods_innodb values(null , 'meta21'); -- 插入數(shù)據(jù)
commit; -- 提交事務
外鍵約束
MySQL支持外鍵的存儲引擎只有InnoDB 傻寂， 在創(chuàng)建外鍵的時候息尺， 要求父表必須有對應的索引 ， 子表在創(chuàng)建外鍵的時候疾掰， 也會自動的創(chuàng)建對應的索引搂誉。
下面兩張表中 ， country_innodb是父表 静檬， country_id為主鍵索引炭懊，city_innodb表是子表并级，country_id字段為外鍵，對應于country_innodb表的主鍵country_id 侮腹。
create table country_innodb(
country_id int NOT NULL AUTO_INCREMENT,
country_name varchar(100) NOT NULL,
primary key(country_id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
create table city_innodb(
city_id int NOT NULL AUTO_INCREMENT,
city_name varchar(50) NOT NULL,
country_id int NOT NULL,
primary key(city_id),
key idx_fk_country_id(country_id),
CONSTRAINT `fk_city_country` FOREIGN KEY(country_id) REFERENCES country_innodb(country_id) ON DELETE RESTRICT ON UPDATE CASCADE
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into country_innodb values(null,'China'),(null,'America'),(null,'Japan');
insert into city_innodb values(null,'Xian',1),(null,'NewYork',2),(null,'BeiJing',1);
在創(chuàng)建索引時嘲碧， 可以指定在刪除、更新父表時父阻，對子表進行的相應操作愈涩，包括 RESTRICT、CASCADE加矛、SET NULL 和 NO ACTION履婉。
RESTRICT 和 NO ACTION 相同， 是指限制在子表有關(guān)聯(lián)記錄的情況下荒椭， 父表不能更新谐鼎；
CASCADE 表示父表在更新或者刪除時，更新或者刪除子表對應的記錄趣惠；
SET NULL 則表示父表在更新或者刪除的時候狸棍，子表的對應字段被SET NULL 。
針對上面創(chuàng)建的兩個表味悄， 子表的外鍵指定是 ON DELETE RESTRICT ON UPDATE CASCADE 方式的草戈， 那么在主表刪除記錄的時候， 如果子表有對應記錄侍瑟， 則不允許刪除唐片， 主表在更新記錄的時候， 如果子表有對應記錄涨颜， 則子表對應更新 费韭。
表中數(shù)據(jù)如下圖所示 ：
- 外鍵信息可以使用如下兩種方式查看 ：
show create table city_innodb;
- 刪除country_id為1的 country_innodb中的數(shù)據(jù) :
delete from country_innodb where id = 1;
- 更新主表， 子表的數(shù)據(jù)信息為 ：
update country_innodb set country_id = 100 where country_id = 1;
存儲方式
- 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 中逻翁。
查看MySQL 數(shù)據(jù)文件存儲：cd /var/lib/mysql
2.2.2 MyISAM
- MyISAM 不支持事務、也不支持外鍵捡鱼，其優(yōu)勢是訪問的速度快八回，對事務的完整性沒有要求或者以SELECT、INSERT為主的應用基本上都可以使用這個引擎來創(chuàng)建表 。有以下兩個比較重要的特點：
不支持事務
create table goods_myisam(
id int NOT NULL AUTO_INCREMENT,
name varchar(20) NOT NULL,
primary key(id)
)ENGINE=myisam DEFAULT CHARSET=utf8;
測試事務
-- 開啟事務
start transaction;
-- 插入數(shù)據(jù)
insert into goods_myisam values(null , 'meta20');
-- 提交事務
commit;
2.2.3 MEMORY
- Memory存儲引擎將表的數(shù)據(jù)存放在內(nèi)存中辽社。每個MEMORY表實際對應一個磁盤文件伟墙，格式是.frm ，該文件中只存儲表的結(jié)構(gòu)滴铅，而其數(shù)據(jù)文件戳葵，都是存儲在內(nèi)存中，這樣有利于數(shù)據(jù)的快速處理汉匙，提高整個表的效率拱烁。MEMORY 類型的表訪問非常地快，因為他的數(shù)據(jù)是存放在內(nèi)存中的噩翠，并且默認使用HASH索引 戏自， 但是服務一旦關(guān)閉，表中的數(shù)據(jù)就會丟失伤锚。
2.2.4 MERGE
MERGE存儲引擎是一組MyISAM表的組合擅笔，這些MyISAM表必須結(jié)構(gòu)完全相同，MERGE表本身并沒有存儲數(shù)據(jù)屯援，對MERGE類型的表可以進行查詢猛们、更新、刪除操作狞洋，這些操作實際上是對內(nèi)部的MyISAM表進行的弯淘。
對于MERGE類型表的插入操作，是通過INSERT_METHOD子句定義插入的表吉懊，可以有3個不同的值庐橙，使用FIRST 或 LAST 值使得插入操作被相應地作用在第一或者最后一個表上，不定義這個子句或者定義為NO借嗽，表示不能對這個MERGE表執(zhí)行插入操作态鳖。
可以對MERGE表進行DROP操作，但是這個操作只是刪除MERGE表的定義恶导，對內(nèi)部的表是沒有任何影響的浆竭。
- 下面是一個創(chuàng)建和使用MERGE表的示例 ：
- 創(chuàng)建3個測試表 order_1990, order_1991, order_all , 其中order_all是前兩個表的MERGE表 ：
create table order_1990(
order_id int ,
order_money double(10,2),
order_address varchar(50),
primary key (order_id)
)engine = myisam default charset=utf8;
create table order_1991(
order_id int ,
order_money double(10,2),
order_address varchar(50),
primary key (order_id)
)engine = myisam default charset=utf8;
create table order_all(
order_id int ,
order_money double(10,2),
order_address varchar(50),
primary key (order_id)
)engine = merge union = (order_1990,order_1991) INSERT_METHOD=LAST default charset=utf8;
- 分別向兩張表中插入數(shù)據(jù) :
insert into order_1990 values(1,100.0,'北京');
insert into order_1990 values(2,100.0,'上海');
insert into order_1991 values(10,200.0,'北京');
insert into order_1991 values(11,200.0,'上海');
- 查詢3張表中的數(shù)據(jù)：
-- 查詢 order_1990 表中數(shù)據(jù)
select * from order_1990;
--- 查詢 order_1991 表中的數(shù)據(jù)
select * from order_1991;
-- 查詢 order_all 表中數(shù)據(jù)
select * from order_all;
- 往order_all中插入一條記錄 ，由于在MERGE表定義時甲锡，INSERT_METHOD 選擇的是LAST，那么插入的數(shù)據(jù)會想最后一張表中插入羽戒。
insert into order_all values(100,10000.0,'西安');
2.3 存儲引擎的選擇
- 在選擇存儲引擎時缤沦，應該根據(jù)應用系統(tǒng)的特點選擇合適的存儲引擎。對于復雜的應用系統(tǒng)易稠，還可以根據(jù)實際情況選擇多種存儲引擎進行組合缸废。以下是幾種常用的存儲引擎的使用環(huán)境。
InnoDB : 是Mysql的默認存儲引擎，用于事務處理應用程序企量，支持外鍵测萎。如果應用對事務的完整性有比較高的要求，在并發(fā)條件下要求數(shù)據(jù)的一致性届巩，數(shù)據(jù)操作除了插入和查詢意外硅瞧，還包含很多的更新、刪除操作恕汇，那么InnoDB存儲引擎是比較合適的選擇腕唧。InnoDB存儲引擎除了有效的降低由于刪除和更新導致的鎖定， 還可以確保事務的完整提交和回滾瘾英，對于類似于計費系統(tǒng)或者財務系統(tǒng)等對數(shù)據(jù)準確性要求比較高的系統(tǒng)枣接，InnoDB是最合適的選擇。
MyISAM ： 如果應用是以讀操作和插入操作為主缺谴，只有很少的更新和刪除操作但惶，并且對事務的完整性、并發(fā)性要求不是很高湿蛔，那么選擇這個存儲引擎是非常合適的膀曾。
MEMORY：將所有數(shù)據(jù)保存在RAM中，在需要快速定位記錄和其他類似數(shù)據(jù)環(huán)境下煌集，可以提供幾塊的訪問妓肢。MEMORY的缺陷就是對表的大小有限制，太大的表無法緩存在內(nèi)存中苫纤，其次是要確保表的數(shù)據(jù)可以恢復碉钠，數(shù)據(jù)庫異常終止后表中的數(shù)據(jù)是可以恢復的。MEMORY表通常用于更新不太頻繁的小表卷拘，用以快速得到訪問結(jié)果喊废。
MERGE：用于將一系列等同的MyISAM表以邏輯方式組合在一起，并作為一個對象引用他們栗弟。MERGE表的優(yōu)點在于可以突破對單個MyISAM表的大小限制污筷，并且通過將不同的表分布在多個磁盤上，可以有效的改善MERGE表的訪問效率乍赫。這對于存儲諸如數(shù)據(jù)倉儲等VLDB環(huán)境十分合適瓣蛀。
