MySQL 工作、底層原理，看這一篇就夠了！

mysql原理圖各個組件說明：

1. connectors

? ?與其他編程語言中的sql 語句進(jìn)行交互，如php、java等。

2. Management Serveices & Utilities

? ?系統(tǒng)管理和控制工具

3. Connection Pool (連接池)

管理緩沖用戶連接，線程處理等需要緩存的需求

4. SQL Interface (SQL接口)

接受用戶的SQL命令，并且返回用戶需要查詢的結(jié)果。比如select from就是調(diào)用SQL Interface

5. Parser （解析器）

SQL命令傳遞到解析器的時候會被解析器驗證和解析。

主要功能：

a . 將SQL語句分解成數(shù)據(jù)結(jié)構(gòu)，并將這個結(jié)構(gòu)傳遞到后續(xù)步驟，后面SQL語句的傳遞和處理就是基于這個結(jié)構(gòu)的

b. ?如果在分解構(gòu)成中遇到錯誤，那么就說明這個sql語句是不合理的，語句將不會繼續(xù)執(zhí)行下去

6. Optimizer (查詢優(yōu)化器)

SQL語句在查詢之前會使用查詢優(yōu)化器對查詢進(jìn)行優(yōu)化(產(chǎn)生多種執(zhí)行計劃,最終數(shù)據(jù)庫會選擇最優(yōu)化的方案去執(zhí)行,盡快返會結(jié)果) 他使用的是“選取-投影-聯(lián)接”策略進(jìn)行查詢。

用一個例子就可以理解：select uid,name from user where gender = 1;

這個select 查詢先根據(jù)where 語句進(jìn)行選取，而不是先將表全部查詢出來以后再進(jìn)行g(shù)ender過濾

這個select查詢先根據(jù)uid和name進(jìn)行屬性投影，而不是將屬性全部取出以后再進(jìn)行過濾

將這兩個查詢條件聯(lián)接起來生成最終查詢結(jié)果.

7. Cache和Buffer (查詢緩存)

如果查詢緩存有命中的查詢結(jié)果，查詢語句就可以直接去查詢緩存中取數(shù)據(jù)。

這個緩存機制是由一系列小緩存組成的。比如表緩存，記錄緩存，key緩存，權(quán)限緩存等

8.Engine (存儲引擎)

存儲引擎是MySql中具體的與文件打交道的子系統(tǒng)。也是Mysql最具有特色的一個地方。

Mysql的存儲引擎是插件式的。它根據(jù)MySql AB公司提供的文件訪問層的一個抽象接口來定制一種文件訪問機制（這種訪問機制就叫存儲引擎）

SQL 語句執(zhí)行過程

數(shù)據(jù)庫通常不會被直接使用，而是由其他編程語言通過SQL語句調(diào)用mysql，由mysql處理并返回執(zhí)行結(jié)果。那么Mysql接受到SQL語句后，又是如何處理

? ?首先程序的請求會通過mysql的connectors與其進(jìn)行交互，請求到處后，會暫時存放在連接池（connection pool)中并由處理器（Management Serveices & Utilities）管理。當(dāng)該請求從等待隊列進(jìn)入到處理隊列，管理器會將該請求丟給SQL接口（SQL Interface）。SQL接口接收到請求后，它會將請求進(jìn)行hash處理并與緩存中的結(jié)果進(jìn)行對比，如果完全匹配則通過緩存直接返回處理結(jié)果；否則，需要完整的走一趟流程：

? ?(1)由SQL接口丟給后面的解釋器（Parser），解釋器會判斷SQL語句正確與否，若正確則將其轉(zhuǎn)化為數(shù)據(jù)結(jié)構(gòu)。

? ?(2)解釋器處理完，便來到后面的優(yōu)化器（Optimizer），它會產(chǎn)生多種執(zhí)行計劃,最終數(shù)據(jù)庫會選擇最優(yōu)化的方案去執(zhí)行,盡快返會結(jié)果。

? ?(3)確定最優(yōu)執(zhí)行計劃后，SQL語句此時便可以交由存儲引擎（Engine）處理，存儲引擎將會到后端的存儲設(shè)備中取得相應(yīng)的數(shù)據(jù)，并原路返回給程序。

注意點

(1)如何緩存查詢數(shù)據(jù)

存儲引擎處理完數(shù)據(jù)，并將其返回給程序的同時，它還會將一份數(shù)據(jù)保留在緩存中，以便更快速的處理下一次相同的請求。具體情況是，mysql會將查詢的語句、執(zhí)行結(jié)果等進(jìn)行hash，并保留在cache中，等待下次查詢。

(2)buffer與cache的區(qū)別

從mysql原理圖可以看到，緩存那里實際上有buffer和cache兩個，那它們之間的區(qū)別：簡單的說就是，buffer是寫緩存，cache是讀緩存。

(3)如何判斷緩存中是否已緩存需要的數(shù)據(jù)

這里可能有一個誤區(qū)，覺得處理SQL語句的時候，為了判斷是否已緩存查詢結(jié)果，會將整個流程走一遍，取得執(zhí)行結(jié)果后再與需要的進(jìn)行對比，看看是否命中，并以此說，既然不管緩存中有沒有緩存到查詢內(nèi)容，都要整個流程走一遍，那緩存的優(yōu)勢在哪？

其實并不是這樣，在第一次查詢后，mysql便將查詢語句以及查詢結(jié)果進(jìn)行hash處理并保留在緩存中，SQL查詢到達(dá)之后，對其進(jìn)行同樣的hash處理后，將兩個hash值進(jìn)行對照，如果一樣，則命中，從緩存中返回查詢結(jié)果；否則，需要整個流程走一遍。

當(dāng)數(shù)據(jù)庫中有多個操作需要修改同一數(shù)據(jù)時，不可避免的會產(chǎn)生數(shù)據(jù)的臟讀。這時就需要數(shù)據(jù)庫具有良好的并發(fā)控制能力，這一切在MySQL中都是由服務(wù)器和存儲引擎來實現(xiàn)的。

解決并發(fā)問題最有效的方案是引入了鎖的機制，鎖在功能上分為共享鎖(shared lock)和排它鎖(exclusive lock)即通常說的讀鎖和寫鎖。當(dāng)一個select語句在執(zhí)行時可以施加讀鎖，這樣就可以允許其它的select操作進(jìn)行，因為在這個過程中數(shù)據(jù)信息是不會被改變的這樣就能夠提高數(shù)據(jù)庫的運行效率。當(dāng)需要對數(shù)據(jù)更新時，就需要施加寫鎖了，不在允許其它的操作進(jìn)行，以免產(chǎn)生數(shù)據(jù)的臟讀和幻讀。鎖同樣有粒度大小，有表級鎖(table lock)和行級鎖(row lock)，分別在數(shù)據(jù)操作的過程中完成行的鎖定和表的鎖定。這些根據(jù)不同的存儲引擎所具有的特性也是不一樣的。

MySQL大多數(shù)事務(wù)型的存儲引擎都不是簡單的行級鎖，基于性能的考慮，他們一般都同時實現(xiàn)了多版本并發(fā)控制(MVCC)。這一方案也被Oracle等主流的關(guān)系數(shù)據(jù)庫采用。它是通過保存數(shù)據(jù)中某個時間點的快照來實現(xiàn)的，這樣就保證了每個事務(wù)看到的數(shù)據(jù)都是一致的。詳細(xì)的實現(xiàn)原理可以參考《高性能MySQL》第三版。

4.事務(wù)

1.簡單的說事務(wù)就是一組原子性的SQL語句?？梢詫⑦@組語句理解成一個工作單元，要么全部執(zhí)行要么都不執(zhí)行。在MySQL中可以使用如下命令操作事務(wù)：

start transaction;
select …
update …
insert …
commit;

注意：默認(rèn)MySQL中自動提交是開啟的：

2.事務(wù)具有ACID的特性：

原子性(atomicity):事務(wù)中的所有操作要么全部提交成功，要么全部失敗回滾。
一致性(consistency):數(shù)據(jù)庫總是從一個一致性狀態(tài)轉(zhuǎn)換到另一個一致性狀態(tài)。
隔離性(isolation):一個事務(wù)所做的修改在提交之前對其它事務(wù)是不可見的。
持久性(durability):一旦事務(wù)提交，其所做的修改便會永久保存在數(shù)據(jù)庫中。
3.事務(wù)的隔離級別：在SQL標(biāo)準(zhǔn)中定義了四種隔離級別：
READ UNCOMMITTED(讀未提交)：事務(wù)中的修改即使未提交也是對其它事務(wù)可見
READ COMMITTED(讀提交)：事務(wù)提交后所做的修改才會被另一個事務(wù)看見，可能產(chǎn)生一個事務(wù)中兩次查詢的結(jié)果不同。
REPEATABLE READ(可重讀)：只有當(dāng)前事務(wù)提交才能看見另一個事務(wù)的修改結(jié)果。解決了一個事務(wù)中兩次查詢的結(jié)果不同的問題。
SERIALIZABLE(串行化)：只有一個事務(wù)提交之后才會執(zhí)行另一個事務(wù)。
4.MySQL中可以利用如下語句查詢并臨時修改隔離級別：

5.死鎖：兩個或多個事務(wù)在同一資源上相互占用并請求鎖定對方占用的資源，從而導(dǎo)致惡性循環(huán)的現(xiàn)象。MySQL的部分存儲引擎能夠檢測到死鎖的循環(huán)依賴并產(chǎn)生相應(yīng)的錯誤。InnoDB引擎解決死鎖的方案是將持有最少排它鎖的事務(wù)進(jìn)行回滾。

5.MySQL存儲引擎及應(yīng)用方案

1.MySQL采用插件式的存儲引擎架構(gòu)，可以根據(jù)不同的需求為不同的表設(shè)置不同的存儲引擎?？梢酝ㄟ^如下命令顯示數(shù)據(jù)庫中表的狀態(tài)信息，以user表為例，顯示如下：

Name：顯示的是表名
Engine：顯示存儲引擎，該表存儲引擎為MyISAM
Row_format：顯示行格式，對于MyISAM有Dynamic、Fixed和Compressed三種。非別表示表中有可變的數(shù)據(jù)類型，表中數(shù)據(jù)類型為固定的，以及表是壓縮表的環(huán)境。
Rows：顯示表中行數(shù)
Avg_row_length：平均行長度（字節(jié)）
Data_length：數(shù)據(jù)長度（字節(jié)）
Max_data_length：最大存儲數(shù)據(jù)長度（字節(jié)）
Data_free：已分配但未使用的空間，包括刪除數(shù)據(jù)空余出來的空間
Auto_increment：下一個插入行自動增長字段的值
Create_time：表的創(chuàng)建時間
Update_time：表數(shù)據(jù)的最后修改時間
Collation：表的默認(rèn)字符集及排序規(guī)則
Checksum：如果啟用，表示整個表的實時校驗和
Create_options：創(chuàng)建表示的一些其它選項
Comment：額外的一些注釋信息，根據(jù)存儲引擎的不同表示的內(nèi)容也不脛相同。

2.存儲引擎介紹：

InnoDB引擎：
1.將數(shù)據(jù)存儲在表空間中，表空間由一系列的數(shù)據(jù)文件組成，由InnoDB管理；
2.支持每個表的數(shù)據(jù)和索引存放在單獨文件中(innodb_file_per_table)；
3.支持事務(wù)，采用MVCC來控制并發(fā)，并實現(xiàn)標(biāo)準(zhǔn)的4個事務(wù)隔離級別，支持外鍵；
4.索引基于聚簇索引建立，對于主鍵查詢有較高性能；
5.數(shù)據(jù)文件的平臺無關(guān)性，支持?jǐn)?shù)據(jù)在不同的架構(gòu)平臺移植；
6.能夠通過一些工具支持真正的熱備。如XtraBackup等；
7.內(nèi)部進(jìn)行自身優(yōu)化如采取可預(yù)測性預(yù)讀，能夠自動在內(nèi)存中創(chuàng)建hash索引等。

MyISAM引擎：
1.MySQL5.1中默認(rèn)，不支持事務(wù)和行級鎖；
2.提供大量特性如全文索引、空間函數(shù)、壓縮、延遲更新等；
3.數(shù)據(jù)庫故障后，安全恢復(fù)性差；
4.對于只讀數(shù)據(jù)可以忍受故障恢復(fù)，MyISAM依然非常適用；
5.日志服務(wù)器的場景也比較適用，只需插入和數(shù)據(jù)讀取操作；
6.不支持單表一個文件，會將所有的數(shù)據(jù)和索引內(nèi)容分別存在兩個文件中；
7.MyISAM對整張表加鎖而不是對行，所以不適用寫操作比較多的場景；
8.支持索引緩存不支持?jǐn)?shù)據(jù)緩存。

Archive引擎：
1.只支持insert和select操作；
2.緩存所有的寫數(shù)據(jù)并進(jìn)行壓縮存儲，支持行級鎖但不支持事務(wù)；
3.適合高速插入和數(shù)據(jù)壓縮，減少IO操作，適用于日志記錄和歸檔服務(wù)器。

Blackhole引擎：
1.沒有實現(xiàn)任何存儲機制，會將插入的數(shù)據(jù)進(jìn)行丟棄，但會存儲二進(jìn)制日志；
2.會在一些特殊需要的復(fù)制架構(gòu)的環(huán)境中使用。

CSV引擎：
1.可以打開CSV文件存儲的數(shù)據(jù)，可以將存儲的數(shù)據(jù)導(dǎo)出，并利用excel打開；
2.可以作為一種數(shù)據(jù)交換的機制，同樣經(jīng)常使用。

Memory引擎：
1.將數(shù)據(jù)在內(nèi)存中緩存，不消耗IO；
2.存儲數(shù)據(jù)速度較快但不會被保留，一般作為臨時表的存儲被使用。

Federated引擎：
能夠訪問遠(yuǎn)程服務(wù)器上的數(shù)據(jù)的存儲引擎。能夠建立一個連接連到遠(yuǎn)程服務(wù)器。

關(guān)注公眾號程序員小樂回復(fù)關(guān)鍵字“Java”獲取Java面試題和答案。

Mrg_MyISAM引擎：
將多個MYISAM表合并為一個。本身并不存儲數(shù)據(jù)，數(shù)據(jù)存在MyISAM表中間。

NDB集群引擎：
MySQL Cluster專用。

3.第三方存儲引擎：

1.OLTP類：
XtraDB：InnoDB的改進(jìn)版本。
PBXT：類似InnoDB，但提供引擎級別的復(fù)制和外鍵約束，適當(dāng)支持SSD存儲。
TokuDB(開源)：支持分形樹索引結(jié)構(gòu)，支持海量數(shù)據(jù)的分析。

2.列式存儲引擎：MySQL默認(rèn)是面向行的存儲
Infobright: 支持?jǐn)?shù)十TB的數(shù)據(jù)量，為數(shù)據(jù)分析和數(shù)據(jù)倉庫設(shè)計的。數(shù)據(jù)高度壓縮。
InfiniDB:可以在一組集群間做分布式查詢，有商業(yè)版但沒有典型應(yīng)用案例。

3.社區(qū)存儲引擎：
Aria：解決MyISAM崩潰安全恢復(fù)問題，并能夠進(jìn)行數(shù)據(jù)緩存。
Groona: 全文索引引擎。
QQGraph: 由Open query研發(fā)支持圖操作，比如查找兩點間最短距離。
SphinxSE: 該引擎為Sphinx全文索引搜索服務(wù)器提供SQL接口。
Spider: 支持sharding并能夠基于分片實現(xiàn)并列查詢。
VPForMySQL: 支持垂直分區(qū)。

4.存儲引擎選取參考因素

1.是否有事務(wù)需求
如果需要事務(wù)支持最好選擇InnoDB或者XtraDB，如果主要是select和insert操作MyISAM比較合適，一般使用日志型的應(yīng)用。
2.備份操作需求
如果能夠關(guān)閉服務(wù)器進(jìn)行備份，那么該因素可以忽略，如果需要在線進(jìn)行熱備份，則InnoDB引擎是一個不錯的選擇。
3.故障恢復(fù)需求
在對恢復(fù)要求比較好的場景中推薦使用InnoDB，因為MyISAM數(shù)據(jù)損壞概率比較大而且恢復(fù)速度比較慢。
4.性能上的需求
有些業(yè)務(wù)需求只有某些特定的存儲引擎才能夠滿足，如地理空間索引也只有MyISAM引擎支持。所以在應(yīng)用架構(gòu)需求環(huán)境中也需要管理員折衷考慮，當(dāng)然從各方面比較而言，InnoDB引擎還是默認(rèn)應(yīng)該被推薦使用的。

5.表引擎轉(zhuǎn)換方法

1.直接修改

2.備份修改
利用mysqldump備份工具將數(shù)據(jù)導(dǎo)出，修改create table語句中的存儲引擎選項。注意修改的同時修改表名。
3.創(chuàng)建插入