第一章 MySQL架構(gòu)與歷史
1.1 MySQL邏輯機(jī)構(gòu)
第一層，即最上一層，所包含的服務(wù)并不是MySQL所獨(dú)有的技術(shù)。它們都是服務(wù)于C/S程序或者是這些程序所需要的 ：連接處理，身份驗(yàn)證，安全性等等。
第二層值得關(guān)注。這是MySQL的核心部分。通常叫做 SQL Layer。在 MySQL據(jù)庫(kù)系統(tǒng)處理底層數(shù)據(jù)之前的所有工作都是在這一層完成的，包括權(quán)限判斷， sql解析，行計(jì)劃優(yōu)化， query cache 的處理以及所有內(nèi)置的函數(shù)(如日期,時(shí)間,數(shù)學(xué)運(yùn)算,加密)等等。各個(gè)存儲(chǔ)引擎提供的功能都集中在這一層，如存儲(chǔ)過(guò)程，觸發(fā)器，視 圖等。
第三層包括了存儲(chǔ)引擎。通常叫做StorEngine Layer ，也就是底層數(shù)據(jù)存取操作實(shí)現(xiàn)部分，由多種存儲(chǔ)引擎共同組成。它們負(fù)責(zé)存儲(chǔ)和獲取所有存儲(chǔ)在MySQL中的數(shù)據(jù)。就像Linux眾多的文件系統(tǒng) 一樣。每個(gè)存儲(chǔ)引擎都有自己的優(yōu)點(diǎn)和缺陷。服務(wù)器是通過(guò)存儲(chǔ)引擎API來(lái)與它們交互的。這個(gè)接口隱藏 了各個(gè)存儲(chǔ)引擎不同的地方。對(duì)于查詢層盡可能的透明。這個(gè)API包含了很多底層的操作。如開始一個(gè)事 物，或者取出有特定主鍵的行。存儲(chǔ)引擎不能解析SQL，互相之間也不能通信。僅僅是簡(jiǎn)單的響應(yīng)服務(wù)器 的請(qǐng)求。
1.1.1連接管理和安全
在服務(wù)器內(nèi)部，每個(gè)client連接都有自己的線程。這個(gè)連接的查詢都在一個(gè)單獨(dú)的線程中執(zhí)行。這些線程輪流運(yùn)行在某一個(gè)CPU內(nèi)核(多核CPU)或者CPU中。服務(wù)器緩存了線程，因此不需要為每個(gè)client連接單獨(dú)創(chuàng)建和銷毀線程 。
當(dāng)clients（也就是應(yīng)用程序）連接到了MySQL服務(wù)器。服務(wù)器需要對(duì)它進(jìn)行認(rèn)證(Authenticate)。認(rèn)證是基于用戶名，主機(jī)，以及密碼。對(duì)于使用了SSL（安全套接字層）的連接，還使用了X.509證書。clients一連接上，服務(wù)器就驗(yàn)證它的權(quán)限 （如是否允許客戶端可以查詢world數(shù)據(jù)庫(kù)下的Country表的數(shù)據(jù)）。
1.1.2優(yōu)化和執(zhí)行
MySQL會(huì)解析查詢，并創(chuàng)建了一個(gè)內(nèi)部數(shù)據(jù)結(jié)構(gòu)（解析樹）。然后對(duì)其進(jìn)行各種優(yōu)化。這些優(yōu)化包括了，查詢語(yǔ)句的重寫，讀表的順序，索引的選擇等等。用戶可以通過(guò)查詢語(yǔ)句的關(guān)鍵詞傳遞給優(yōu)化器以便提示使用哪種優(yōu)化方式，這樣即影響了優(yōu)化器的優(yōu)化方式。另外，用戶也可以請(qǐng)求服務(wù)器給出優(yōu)化過(guò)程的各種說(shuō)明，以獲知服務(wù)器的優(yōu)化策略，為用戶提供了參數(shù)基準(zhǔn)，以便用戶可以重寫查詢，架構(gòu)和修改相關(guān)服務(wù)器配置，便于mysql更高效的運(yùn)行。
優(yōu)化器并是不關(guān)心表使用了哪種存儲(chǔ)引擎，但是存儲(chǔ)引擎對(duì)服務(wù)器優(yōu)化查詢的方式是有影響的。優(yōu)化器需要知道存儲(chǔ)引擎的一些特性：具體操作的性能和開銷方面的信息，以及表內(nèi)數(shù)據(jù)的統(tǒng)計(jì)信息。例如，存儲(chǔ)引擎支持哪些索引類型，這對(duì)于查詢是非常有用的。
在解析查詢之前，要查詢緩存，這個(gè)緩存只能保存查詢信息以及結(jié)果數(shù)據(jù)。如果請(qǐng)求一個(gè)查詢?cè)诰彺?中存在，就不需要解析，優(yōu)化和執(zhí)行查詢了。直接返回緩存中所存放的這個(gè)查詢的結(jié)果。
1.2 并發(fā)控制
1.2.1讀寫鎖
1.共享鎖（shared lock，讀鎖）：共享的，相互不阻塞的。
2.排他鎖（exclusive lock，寫鎖）：排他的，一個(gè)寫鎖會(huì)阻塞其他的寫鎖和讀鎖。
1.2.2鎖粒度
表鎖：MySQL中最基本的鎖策略，并且是開銷最小的策略。
行級(jí)鎖：可以最大程度地支持并發(fā)處理，但是會(huì)帶來(lái)極大的開銷。
1.3事務(wù)
ACID特性
- 原子性（atomicity）
一個(gè)事務(wù)必須被視為一個(gè)不可分割的最小工作單元，整個(gè)事務(wù)中所有操作要么全部提交成功，要么全部失敗回滾，對(duì)于一個(gè)事務(wù)來(lái)說(shuō)，不可能只執(zhí)行其中的一部分操作 - 一致性（consistency）
數(shù)據(jù)庫(kù)總是從一個(gè)一致性的狀態(tài)轉(zhuǎn)換到另外一個(gè)一致性的狀態(tài) - 隔離性（isolation）
一個(gè)事務(wù)所做的修改在最終提交以前，對(duì)其他事務(wù)是不可見的 - 持久性（durability）
一旦事務(wù)提交，則其所做的修改就會(huì)永久保存到數(shù)據(jù)庫(kù)中
1.3.1隔離級(jí)別
在SQL標(biāo)準(zhǔn)中定義四種隔離級(jí)別，每一種隔離級(jí)別都規(guī)定了一個(gè)事務(wù)中所做的修改，哪些是事務(wù)內(nèi)部和事務(wù)之間可見的，哪些是不可見的。較低級(jí)別的隔離通常可以執(zhí)行更高的并發(fā)，系統(tǒng)的開銷也更低。
- READ UNCOMMITTED（未提交讀）
事務(wù)中的修改，即使沒(méi)有提交，對(duì)其他事務(wù)也都是可見的，事務(wù)可以讀取未提交的數(shù)據(jù)，也被稱為臟讀（Dirty Read），這個(gè)級(jí)別會(huì)導(dǎo)致很多問(wèn)題，實(shí)際中很少使用 - READ COMMITTED（提交讀）
大多數(shù)數(shù)據(jù)庫(kù)系統(tǒng)的默認(rèn)隔離級(jí)別，一個(gè)事務(wù)開始時(shí)，只能“看見”已經(jīng)提交的事務(wù)所做的修改，一個(gè)事務(wù)從開始直到提交之前，所做的任何修改對(duì)其他事務(wù)都是不可見的，也叫不可重復(fù)讀（nonrepeatable read），因?yàn)閮纱螆?zhí)行同樣的查詢，得到的結(jié)果是不同的 - REPEATABLE READ（可重復(fù)讀）
這個(gè)級(jí)別可以解決臟讀問(wèn)題，保證了在同一個(gè)事務(wù)中多次讀取同樣記錄的結(jié)果是一致的。但是有可能無(wú)法解決幻讀的問(wèn)題。所謂的幻讀（Phantom Read）指的是當(dāng)某個(gè)事務(wù)在讀取某個(gè)范圍內(nèi)的記錄時(shí)，另外一個(gè)事務(wù)又在該范圍內(nèi)插入了新的記錄，當(dāng)之前的事務(wù)再次讀取該范圍的記錄時(shí)，會(huì)產(chǎn)生幻行（Phantom Row）。
通過(guò)InnoDB和XtraDB存儲(chǔ)引擎，是MySQL的默認(rèn)事務(wù)隔離級(jí)別 - SERIALIZABLE（可串行化）
最高級(jí)別，通過(guò)強(qiáng)制事務(wù)串行執(zhí)行，避免了幻讀問(wèn)題，會(huì)在讀取的每一行數(shù)據(jù)上都加鎖，可能導(dǎo)致大量的超時(shí)和鎖爭(zhēng)用的問(wèn)題，適用于非常需要確保數(shù)據(jù)的一致性而且可以接受沒(méi)有并發(fā)的情況
1.3.2死鎖
死鎖：指兩個(gè)或多個(gè)事務(wù)在同一資源上相互占用，并請(qǐng)求鎖定對(duì)方占用的資源，從而導(dǎo)致惡性循環(huán)的現(xiàn)象。
需要注意的是，鎖的行為和順序是和存儲(chǔ)引擎相關(guān)聯(lián)的。以同樣的書序執(zhí)行的語(yǔ)句，在某些引擎中會(huì)產(chǎn)生死鎖，但是在某些引擎中是不會(huì)產(chǎn)生死鎖的。
1.3.3事務(wù)日志
儲(chǔ)引擎在修改表的數(shù)據(jù)時(shí)只需要修改其內(nèi)存拷貝，再把該修改行為記錄到持久在硬盤上的事務(wù)日志中，而不用每次都將修改的數(shù)據(jù)本身持久到磁盤。事務(wù)日志持久以后，內(nèi)存中被修改的數(shù)據(jù)在后臺(tái)可以慢慢地刷回到磁盤，稱為預(yù)寫式日志（Write-Ahead Logging）
1.3.4MySQL中的事務(wù)
MySQL提供兩種事務(wù)型存儲(chǔ)引擎：InnoDB和NDB Cluster。
自動(dòng)提交模式：MySQL的默認(rèn)模式，將每一條查詢都看作一個(gè)事務(wù)執(zhí)行提交操作。
由于MySQL服務(wù)器層面不進(jìn)行事務(wù)的管理，而是交過(guò)下層的存儲(chǔ)引擎實(shí)現(xiàn)，所以在同一個(gè)事務(wù)中，使用多種存儲(chǔ)引擎是不可靠的。
1.4多版本并發(fā)控制
- 多版本并發(fā)控制（MVCC）是行級(jí)鎖的一個(gè)變種，但是它在很多情況下避免了加鎖操作，因此開銷更低。雖然實(shí)現(xiàn)機(jī)制有所不同，但大都實(shí)現(xiàn)了非阻塞的讀操作，寫操作也只鎖定必要的行
- MVCC的實(shí)現(xiàn)，是通過(guò)保存數(shù)據(jù)在某個(gè)時(shí)間點(diǎn)的快照來(lái)實(shí)現(xiàn)的，有樂(lè)觀和悲觀兩種，只在REPEATABLE READ和READ COMMITTED兩個(gè)隔離級(jí)別下工作
特別說(shuō)明：InnoDB實(shí)現(xiàn)MVCC的方法
InnoDB實(shí)現(xiàn)MVCC的方法是，它存儲(chǔ)了每一行的兩個(gè)(1)額外的隱藏字段，這兩個(gè)隱藏字段分別記錄了行的創(chuàng)建的時(shí)間和刪除的時(shí)間。在每個(gè)事件發(fā)生的時(shí)候，每行存儲(chǔ)版本號(hào)，而不是存儲(chǔ)事件實(shí)際發(fā)生的時(shí)間。每次事物的開始這個(gè)版本號(hào)都會(huì)增加。自記錄時(shí)間開始，每個(gè)事物都會(huì)保存記錄的系統(tǒng)版本號(hào)。依照事物的 版本來(lái)檢查每行的版本號(hào)。在事物隔離級(jí)別為可重復(fù)讀的情況下，來(lái)看看怎樣應(yīng)用它。
- SELECT
Innodb檢查沒(méi)行數(shù)據(jù)，確保他們符合兩個(gè)標(biāo)準(zhǔn)：
1、InnoDB只查找版本早于當(dāng)前事務(wù)版本的數(shù)據(jù)行(也就是數(shù)據(jù)行的版本必須小于等于事務(wù)的版本)，這確保當(dāng)前事務(wù)讀取的行都是事務(wù)之前已經(jīng)存在的，或者是由當(dāng)前事務(wù)創(chuàng)建或修改的行
2、行的刪除操作的版本一定是未定義的或者大于當(dāng)前事務(wù)的版本號(hào)。確定了當(dāng)前事務(wù)開始之前，行沒(méi)有被刪除(2)
符合了以上兩點(diǎn)則返回查詢結(jié)果。 - INSERT
InnoDB為每個(gè)新增行記錄當(dāng)前系統(tǒng)版本號(hào)作為創(chuàng)建ID。 - DELETE
InnoDB為每個(gè)刪除行的記錄當(dāng)前系統(tǒng)版本號(hào)作為行的刪除ID。 - UPDATE
InnoDB復(fù)制了一行。這個(gè)新行的版本號(hào)使用了系統(tǒng)版本號(hào)。它也把系統(tǒng)版本號(hào)作為了刪除行的版本。
1.5MySQL的存儲(chǔ)引擎
MyISAM
它不支持事務(wù)，也不支持外鍵，尤其是訪問(wèn)速度快，對(duì)事務(wù)完整性沒(méi)有要求或者以SELECT、INSERT為主的應(yīng)用基本都可以使用這個(gè)引擎來(lái)創(chuàng)建表。
每個(gè)MyISAM在磁盤上存儲(chǔ)成3個(gè)文件，其中文件名和表名都相同，但是擴(kuò)展名分別為：
.frm(存儲(chǔ)表定義)
MYD(MYData，存儲(chǔ)數(shù)據(jù))
MYI(MYIndex，存儲(chǔ)索引)
數(shù)據(jù)文件和索引文件可以放置在不同的目錄，平均分配IO，獲取更快的速度。要指定數(shù)據(jù)文件和索引文件的路徑，需要在創(chuàng)建表的時(shí)候通過(guò)DATA DIRECTORY和INDEX DIRECTORY語(yǔ)句指定，文件路徑需要使用絕對(duì)路徑。
每個(gè)MyISAM表都有一個(gè)標(biāo)志，服務(wù)器或myisamchk程序在檢查MyISAM數(shù)據(jù)表時(shí)會(huì)對(duì)這個(gè)標(biāo)志進(jìn)行設(shè)置。MyISAM表還有一個(gè)標(biāo)志用來(lái)表明該數(shù)據(jù)表在上次使用后是不是被正常的關(guān)閉了。如果服務(wù)器以為當(dāng)機(jī)或崩潰，這個(gè)標(biāo)志可以用來(lái)判斷數(shù)據(jù)表是否需要檢查和修復(fù)。如果想讓這種檢查自動(dòng)進(jìn)行，可以在啟動(dòng)服務(wù)器時(shí)使用--myisam-recover現(xiàn)象。這會(huì)讓服務(wù)器在每次打開一個(gè)MyISAM數(shù)據(jù)表是自動(dòng)檢查數(shù)據(jù)表的標(biāo)志并進(jìn)行必要的修復(fù)處理。MyISAM類型的表可能會(huì)損壞，可以使用CHECK TABLE語(yǔ)句來(lái)檢查MyISAM表的健康，并用REPAIR TABLE語(yǔ)句修復(fù)一個(gè)損壞到MyISAM表。
MyISAM的表還支持3種不同的存儲(chǔ)格式：靜態(tài)(固定長(zhǎng)度)表、動(dòng)態(tài)表、壓縮表
其中靜態(tài)表是默認(rèn)的存儲(chǔ)格式。靜態(tài)表中的字段都是非變長(zhǎng)字段，這樣每個(gè)記錄都是固定長(zhǎng)度的，這種存儲(chǔ)方式的優(yōu)點(diǎn)是存儲(chǔ)非常迅速，容易緩存，出現(xiàn)故障容易恢復(fù)；缺點(diǎn)是占用的空間通常比動(dòng)態(tài)表多。靜態(tài)表在數(shù)據(jù)存儲(chǔ)時(shí)會(huì)根據(jù)列定義的寬度定義補(bǔ)足空格，但是在訪問(wèn)的時(shí)候并不會(huì)得到這些空格，這些空格在返回給應(yīng)用之前已經(jīng)去掉。同時(shí)需要注意：在某些情況下可能需要返回字段后的空格，而使用這種格式時(shí)后面到空格會(huì)被自動(dòng)處理掉。
動(dòng)態(tài)表包含變長(zhǎng)字段，記錄不是固定長(zhǎng)度的，這樣存儲(chǔ)的優(yōu)點(diǎn)是占用空間較少，但是頻繁到更新刪除記錄會(huì)產(chǎn)生碎片，需要定期執(zhí)行OPTIMIZE TABLE語(yǔ)句或myisamchk -r命令來(lái)改善性能，并且出現(xiàn)故障的時(shí)候恢復(fù)相對(duì)比較困難。
壓縮表由myisamchk工具創(chuàng)建，占據(jù)非常小的空間，因?yàn)槊織l記錄都是被單獨(dú)壓縮的，所以只有非常小的訪問(wèn)開支。InnoDB
InnoDB是一個(gè)健壯的事務(wù)型存儲(chǔ)引擎，這種存儲(chǔ)引擎已經(jīng)被很多互聯(lián)網(wǎng)公司使用，為用戶操作非常大的數(shù)據(jù)存儲(chǔ)提供了一個(gè)強(qiáng)大的解決方案。我的電腦上安裝的MySQL 5.6.13版，InnoDB就是作為默認(rèn)的存儲(chǔ)引擎。InnoDB還引入了行級(jí)鎖定和外鍵約束，在以下場(chǎng)合下，使用InnoDB是最理想的選擇：
1.更新密集的表。InnoDB存儲(chǔ)引擎特別適合處理多重并發(fā)的更新請(qǐng)求。
2.事務(wù)。InnoDB存儲(chǔ)引擎是支持事務(wù)的標(biāo)準(zhǔn)MySQL存儲(chǔ)引擎。
3.自動(dòng)災(zāi)難恢復(fù)。與其它存儲(chǔ)引擎不同，InnoDB表能夠自動(dòng)從災(zāi)難中恢復(fù)。
4.外鍵約束。MySQL支持外鍵的存儲(chǔ)引擎只有InnoDB。
5.支持自動(dòng)增加列AUTO_INCREMENT屬性。
一般來(lái)說(shuō)，如果需要事務(wù)支持，并且有較高的并發(fā)讀取頻率，InnoDB是不錯(cuò)的選擇。MEMORY
使用MySQL Memory存儲(chǔ)引擎的出發(fā)點(diǎn)是速度。為得到最快的響應(yīng)時(shí)間，采用的邏輯存儲(chǔ)介質(zhì)是系統(tǒng)內(nèi)存。雖然在內(nèi)存中存儲(chǔ)表數(shù)據(jù)確實(shí)會(huì)提供很高的性能，但當(dāng)mysqld守護(hù)進(jìn)程崩潰時(shí)，所有的Memory數(shù)據(jù)都會(huì)丟失。獲得速度的同時(shí)也帶來(lái)了一些缺陷。它要求存儲(chǔ)在Memory數(shù)據(jù)表里的數(shù)據(jù)使用的是長(zhǎng)度不變的格式，這意味著不能使用BLOB和TEXT這樣的長(zhǎng)度可變的數(shù)據(jù)類型，VARCHAR是一種長(zhǎng)度可變的類型，但因?yàn)樗贛ySQL內(nèi)部當(dāng)做長(zhǎng)度固定不變的CHAR類型，所以可以使用。
一般在以下幾種情況下使用Memory存儲(chǔ)引擎：
1.目標(biāo)數(shù)據(jù)較小，而且被非常頻繁地訪問(wèn)。在內(nèi)存中存放數(shù)據(jù)，所以會(huì)造成內(nèi)存的使用，可以通過(guò)參數(shù)max_heap_table_size控制Memory表的大小，設(shè)置此參數(shù)，就可以限制Memory表的最大大小。
2.如果數(shù)據(jù)是臨時(shí)的，而且要求必須立即可用，那么就可以存放在內(nèi)存表中。
3.存儲(chǔ)在Memory表中的數(shù)據(jù)如果突然丟失，不會(huì)對(duì)應(yīng)用服務(wù)產(chǎn)生實(shí)質(zhì)的負(fù)面影響。
Memory同時(shí)支持散列索引和B樹索引。
B樹索引的優(yōu)于散列索引的是，可以使用部分查詢和通配查詢，也可以使用<、>和>=等操作符方便數(shù)據(jù)挖掘。散列索引進(jìn)行“相等比較”非?？?，但是對(duì)“范圍比較”的速度就慢多了，因此散列索引值適合使用在=和<>的操作符中，不適合在<或>操作符中，也同樣不適合用在order by子句中。
MERGE
MERGE存儲(chǔ)引擎是一組MyISAM表的組合，這些MyISAM表結(jié)構(gòu)必須完全相同，盡管其使用不如其它引擎突出，但是在某些情況下非常有用。說(shuō)白了，Merge表就是幾個(gè)相同MyISAM表的聚合器；Merge表中并沒(méi)有數(shù)據(jù)，對(duì)Merge類型的表可以進(jìn)行查詢、更新、刪除操作，這些操作實(shí)際上是對(duì)內(nèi)部的MyISAM表進(jìn)行操作。Merge存儲(chǔ)引擎的使用場(chǎng)景。
對(duì)于服務(wù)器日志這種信息，一般常用的存儲(chǔ)策略是將數(shù)據(jù)分成很多表，每個(gè)名稱與特定的時(shí)間端相關(guān)。例如：可以用12個(gè)相同的表來(lái)存儲(chǔ)服務(wù)器日志數(shù)據(jù)，每個(gè)表用對(duì)應(yīng)各個(gè)月份的名字來(lái)命名。當(dāng)有必要基于所有12個(gè)日志表的數(shù)據(jù)來(lái)生成報(bào)表，這意味著需要編寫并更新多表查詢，以反映這些表中的信息。與其編寫這些可能出現(xiàn)錯(cuò)誤的查詢，不如將這些表合并起來(lái)使用一條查詢，之后再刪除Merge表，而不影響原來(lái)的數(shù)據(jù)，刪除Merge表只是刪除Merge表的定義，對(duì)內(nèi)部的表沒(méi)有任何影響。ARCHIVE
Archive是歸檔的意思，在歸檔之后很多的高級(jí)功能就不再支持了，僅僅支持最基本的插入和查詢兩種功能。在MySQL 5.5版以前，Archive是不支持索引，但是在MySQL 5.5以后的版本中就開始支持索引了。Archive擁有很好的壓縮機(jī)制，它使用zlib壓縮庫(kù)，在記錄被請(qǐng)求時(shí)會(huì)實(shí)時(shí)壓縮，所以它經(jīng)常被用來(lái)當(dāng)做倉(cāng)庫(kù)使用。
存儲(chǔ)引擎的一些問(wèn)題
1.如何查看服務(wù)器有哪些存儲(chǔ)引擎可以使用？
為確定你的MySQL服務(wù)器可以用哪些存儲(chǔ)引擎，執(zhí)行如下命令：show engines就能搞定了。
2.如何選擇合適的存儲(chǔ)引擎？
（1）選擇標(biāo)準(zhǔn)可以分為：
（2）是否需要支持事務(wù)；
（3）是否需要使用熱備；
（4）崩潰恢復(fù)：能否接受崩潰；
（5）是否需要外鍵支持；
然后按照標(biāo)準(zhǔn)，選擇對(duì)應(yīng)的存儲(chǔ)引擎即可。
