第一章 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ù)庫系統(tǒng)處理底層數(shù)據(jù)之前的所有工作都是在這一層完成的凿掂，包括權(quán)限判斷伴榔， sql解析，行計(jì)劃優(yōu)化， query cache 的處理以及所有內(nèi)置的函數(shù)(如日期,時(shí)間,數(shù)學(xué)運(yùn)算,加密)等等踪少。各個(gè)存儲引擎提供的功能都集中在這一層塘安，如存儲過程，觸發(fā)器援奢，視 圖等兼犯。
第三層包括了存儲引擎。通常叫做StorEngine Layer 萝究，也就是底層數(shù)據(jù)存取操作實(shí)現(xiàn)部分免都，由多種存儲引擎共同組成。它們負(fù)責(zé)存儲和獲取所有存儲在MySQL中的數(shù)據(jù)帆竹。就像Linux眾多的文件系統(tǒng) 一樣绕娘。每個(gè)存儲引擎都有自己的優(yōu)點(diǎn)和缺陷。服務(wù)器是通過存儲引擎API來與它們交互的栽连。這個(gè)接口隱藏 了各個(gè)存儲引擎不同的地方险领。對于查詢層盡可能的透明。這個(gè)API包含了很多底層的操作秒紧。如開始一個(gè)事 物绢陌，或者取出有特定主鍵的行。存儲引擎不能解析SQL熔恢，互相之間也不能通信脐湾。僅僅是簡單的響應(yīng)服務(wù)器 的請求。
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ù)器需要對它進(jìn)行認(rèn)證(Authenticate)孟岛。認(rèn)證是基于用戶名，主機(jī)督勺，以及密碼渠羞。對于使用了SSL（安全套接字層）的連接，還使用了X.509證書智哀。clients一連接上堵未，服務(wù)器就驗(yàn)證它的權(quán)限 （如是否允許客戶端可以查詢world數(shù)據(jù)庫下的Country表的數(shù)據(jù)）。
1.1.2優(yōu)化和執(zhí)行
MySQL會解析查詢盏触，并創(chuàng)建了一個(gè)內(nèi)部數(shù)據(jù)結(jié)構(gòu)（解析樹）。然后對其進(jìn)行各種優(yōu)化。這些優(yōu)化包括了赞辩，查詢語句的重寫雌芽，讀表的順序，索引的選擇等等辨嗽。用戶可以通過查詢語句的關(guān)鍵詞傳遞給優(yōu)化器以便提示使用哪種優(yōu)化方式世落，這樣即影響了優(yōu)化器的優(yōu)化方式。另外糟需，用戶也可以請求服務(wù)器給出優(yōu)化過程的各種說明屉佳，以獲知服務(wù)器的優(yōu)化策略，為用戶提供了參數(shù)基準(zhǔn)洲押，以便用戶可以重寫查詢武花，架構(gòu)和修改相關(guān)服務(wù)器配置，便于mysql更高效的運(yùn)行杈帐。
優(yōu)化器并是不關(guān)心表使用了哪種存儲引擎体箕，但是存儲引擎對服務(wù)器優(yōu)化查詢的方式是有影響的。優(yōu)化器需要知道存儲引擎的一些特性：具體操作的性能和開銷方面的信息挑童，以及表內(nèi)數(shù)據(jù)的統(tǒng)計(jì)信息累铅。例如，存儲引擎支持哪些索引類型站叼，這對于查詢是非常有用的娃兽。
在解析查詢之前，要查詢緩存尽楔，這個(gè)緩存只能保存查詢信息以及結(jié)果數(shù)據(jù)投储。如果請求一個(gè)查詢在緩存 中存在，就不需要解析翔试，優(yōu)化和執(zhí)行查詢了轻要。直接返回緩存中所存放的這個(gè)查詢的結(jié)果。
1.2 并發(fā)控制
1.2.1讀寫鎖
1.共享鎖（shared lock垦缅，讀鎖）：共享的冲泥，相互不阻塞的。
2.排他鎖（exclusive lock壁涎，寫鎖）：排他的凡恍，一個(gè)寫鎖會阻塞其他的寫鎖和讀鎖。
1.2.2鎖粒度
表鎖：MySQL中最基本的鎖策略怔球，并且是開銷最小的策略嚼酝。
行級鎖：可以最大程度地支持并發(fā)處理，但是會帶來極大的開銷竟坛。
1.3事務(wù)
ACID特性
- 原子性（atomicity）
一個(gè)事務(wù)必須被視為一個(gè)不可分割的最小工作單元闽巩，整個(gè)事務(wù)中所有操作要么全部提交成功钧舌，要么全部失敗回滾，對于一個(gè)事務(wù)來說涎跨，不可能只執(zhí)行其中的一部分操作 - 一致性（consistency）
數(shù)據(jù)庫總是從一個(gè)一致性的狀態(tài)轉(zhuǎn)換到另外一個(gè)一致性的狀態(tài) - 隔離性（isolation）
一個(gè)事務(wù)所做的修改在最終提交以前洼冻，對其他事務(wù)是不可見的 - 持久性（durability）
一旦事務(wù)提交，則其所做的修改就會永久保存到數(shù)據(jù)庫中
1.3.1隔離級別
在SQL標(biāo)準(zhǔn)中定義四種隔離級別隅很，每一種隔離級別都規(guī)定了一個(gè)事務(wù)中所做的修改撞牢，哪些是事務(wù)內(nèi)部和事務(wù)之間可見的，哪些是不可見的叔营。較低級別的隔離通澄荼耄可以執(zhí)行更高的并發(fā)，系統(tǒng)的開銷也更低绒尊。
- READ UNCOMMITTED（未提交讀）
事務(wù)中的修改畜挥，即使沒有提交，對其他事務(wù)也都是可見的垒酬，事務(wù)可以讀取未提交的數(shù)據(jù)砰嘁，也被稱為臟讀（Dirty Read），這個(gè)級別會導(dǎo)致很多問題勘究，實(shí)際中很少使用 - READ COMMITTED（提交讀）
大多數(shù)數(shù)據(jù)庫系統(tǒng)的默認(rèn)隔離級別矮湘，一個(gè)事務(wù)開始時(shí)，只能“看見”已經(jīng)提交的事務(wù)所做的修改口糕，一個(gè)事務(wù)從開始直到提交之前缅阳，所做的任何修改對其他事務(wù)都是不可見的，也叫不可重復(fù)讀（nonrepeatable read）景描，因?yàn)閮纱螆?zhí)行同樣的查詢十办，得到的結(jié)果是不同的 - REPEATABLE READ（可重復(fù)讀）
這個(gè)級別可以解決臟讀問題，保證了在同一個(gè)事務(wù)中多次讀取同樣記錄的結(jié)果是一致的超棺。但是有可能無法解決幻讀的問題向族。所謂的幻讀（Phantom Read）指的是當(dāng)某個(gè)事務(wù)在讀取某個(gè)范圍內(nèi)的記錄時(shí)，另外一個(gè)事務(wù)又在該范圍內(nèi)插入了新的記錄棠绘，當(dāng)之前的事務(wù)再次讀取該范圍的記錄時(shí)件相，會產(chǎn)生幻行（Phantom Row）。
通過InnoDB和XtraDB存儲引擎氧苍，是MySQL的默認(rèn)事務(wù)隔離級別 - SERIALIZABLE（可串行化）
最高級別夜矗，通過強(qiáng)制事務(wù)串行執(zhí)行，避免了幻讀問題让虐，會在讀取的每一行數(shù)據(jù)上都加鎖紊撕，可能導(dǎo)致大量的超時(shí)和鎖爭用的問題，適用于非常需要確保數(shù)據(jù)的一致性而且可以接受沒有并發(fā)的情況
1.3.2死鎖
死鎖：指兩個(gè)或多個(gè)事務(wù)在同一資源上相互占用赡突，并請求鎖定對方占用的資源对扶，從而導(dǎo)致惡性循環(huán)的現(xiàn)象区赵。
需要注意的是，鎖的行為和順序是和存儲引擎相關(guān)聯(lián)的辩稽。以同樣的書序執(zhí)行的語句惧笛，在某些引擎中會產(chǎn)生死鎖，但是在某些引擎中是不會產(chǎn)生死鎖的逞泄。
1.3.3事務(wù)日志
儲引擎在修改表的數(shù)據(jù)時(shí)只需要修改其內(nèi)存拷貝，再把該修改行為記錄到持久在硬盤上的事務(wù)日志中拜效，而不用每次都將修改的數(shù)據(jù)本身持久到磁盤喷众。事務(wù)日志持久以后，內(nèi)存中被修改的數(shù)據(jù)在后臺可以慢慢地刷回到磁盤紧憾，稱為預(yù)寫式日志（Write-Ahead Logging）
1.3.4MySQL中的事務(wù)
MySQL提供兩種事務(wù)型存儲引擎：InnoDB和NDB Cluster到千。
自動提交模式：MySQL的默認(rèn)模式，將每一條查詢都看作一個(gè)事務(wù)執(zhí)行提交操作赴穗。
由于MySQL服務(wù)器層面不進(jìn)行事務(wù)的管理憔四，而是交過下層的存儲引擎實(shí)現(xiàn)，所以在同一個(gè)事務(wù)中般眉，使用多種存儲引擎是不可靠的了赵。
1.4多版本并發(fā)控制
- 多版本并發(fā)控制（MVCC）是行級鎖的一個(gè)變種，但是它在很多情況下避免了加鎖操作甸赃，因此開銷更低柿汛。雖然實(shí)現(xiàn)機(jī)制有所不同，但大都實(shí)現(xiàn)了非阻塞的讀操作埠对，寫操作也只鎖定必要的行
- MVCC的實(shí)現(xiàn)络断，是通過保存數(shù)據(jù)在某個(gè)時(shí)間點(diǎn)的快照來實(shí)現(xiàn)的，有樂觀和悲觀兩種项玛，只在REPEATABLE READ和READ COMMITTED兩個(gè)隔離級別下工作
特別說明：InnoDB實(shí)現(xiàn)MVCC的方法
InnoDB實(shí)現(xiàn)MVCC的方法是貌笨，它存儲了每一行的兩個(gè)(1)額外的隱藏字段，這兩個(gè)隱藏字段分別記錄了行的創(chuàng)建的時(shí)間和刪除的時(shí)間襟沮。在每個(gè)事件發(fā)生的時(shí)候锥惋，每行存儲版本號，而不是存儲事件實(shí)際發(fā)生的時(shí)間臣嚣。每次事物的開始這個(gè)版本號都會增加净刮。自記錄時(shí)間開始，每個(gè)事物都會保存記錄的系統(tǒng)版本號硅则。依照事物的 版本來檢查每行的版本號淹父。在事物隔離級別為可重復(fù)讀的情況下，來看看怎樣應(yīng)用它怎虫。
- SELECT
Innodb檢查沒行數(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ù)的版本號。確定了當(dāng)前事務(wù)開始之前粮彤，行沒有被刪除(2)
符合了以上兩點(diǎn)則返回查詢結(jié)果根穷。 - INSERT
InnoDB為每個(gè)新增行記錄當(dāng)前系統(tǒng)版本號作為創(chuàng)建ID。 - DELETE
InnoDB為每個(gè)刪除行的記錄當(dāng)前系統(tǒng)版本號作為行的刪除ID导坟。 - UPDATE
InnoDB復(fù)制了一行屿良。這個(gè)新行的版本號使用了系統(tǒng)版本號。它也把系統(tǒng)版本號作為了刪除行的版本惫周。
1.5MySQL的存儲引擎
MyISAM
它不支持事務(wù)尘惧，也不支持外鍵，尤其是訪問速度快递递，對事務(wù)完整性沒有要求或者以SELECT喷橙、INSERT為主的應(yīng)用基本都可以使用這個(gè)引擎來創(chuàng)建表。
每個(gè)MyISAM在磁盤上存儲成3個(gè)文件登舞，其中文件名和表名都相同贰逾，但是擴(kuò)展名分別為：
.frm(存儲表定義)
MYD(MYData，存儲數(shù)據(jù))
MYI(MYIndex逊躁，存儲索引)
數(shù)據(jù)文件和索引文件可以放置在不同的目錄似踱，平均分配IO，獲取更快的速度稽煤。要指定數(shù)據(jù)文件和索引文件的路徑核芽，需要在創(chuàng)建表的時(shí)候通過DATA DIRECTORY和INDEX DIRECTORY語句指定，文件路徑需要使用絕對路徑酵熙。
每個(gè)MyISAM表都有一個(gè)標(biāo)志轧简，服務(wù)器或myisamchk程序在檢查MyISAM數(shù)據(jù)表時(shí)會對這個(gè)標(biāo)志進(jìn)行設(shè)置。MyISAM表還有一個(gè)標(biāo)志用來表明該數(shù)據(jù)表在上次使用后是不是被正常的關(guān)閉了匾二。如果服務(wù)器以為當(dāng)機(jī)或崩潰哮独，這個(gè)標(biāo)志可以用來判斷數(shù)據(jù)表是否需要檢查和修復(fù)。如果想讓這種檢查自動進(jìn)行察藐，可以在啟動服務(wù)器時(shí)使用--myisam-recover現(xiàn)象皮璧。這會讓服務(wù)器在每次打開一個(gè)MyISAM數(shù)據(jù)表是自動檢查數(shù)據(jù)表的標(biāo)志并進(jìn)行必要的修復(fù)處理。MyISAM類型的表可能會損壞分飞，可以使用CHECK TABLE語句來檢查MyISAM表的健康悴务，并用REPAIR TABLE語句修復(fù)一個(gè)損壞到MyISAM表。
MyISAM的表還支持3種不同的存儲格式：靜態(tài)(固定長度)表、動態(tài)表讯檐、壓縮表
其中靜態(tài)表是默認(rèn)的存儲格式羡疗。靜態(tài)表中的字段都是非變長字段，這樣每個(gè)記錄都是固定長度的别洪，這種存儲方式的優(yōu)點(diǎn)是存儲非常迅速叨恨，容易緩存，出現(xiàn)故障容易恢復(fù)挖垛；缺點(diǎn)是占用的空間通常比動態(tài)表多痒钝。靜態(tài)表在數(shù)據(jù)存儲時(shí)會根據(jù)列定義的寬度定義補(bǔ)足空格，但是在訪問的時(shí)候并不會得到這些空格晕换，這些空格在返回給應(yīng)用之前已經(jīng)去掉午乓。同時(shí)需要注意：在某些情況下可能需要返回字段后的空格，而使用這種格式時(shí)后面到空格會被自動處理掉闸准。
動態(tài)表包含變長字段，記錄不是固定長度的梢灭，這樣存儲的優(yōu)點(diǎn)是占用空間較少夷家，但是頻繁到更新刪除記錄會產(chǎn)生碎片，需要定期執(zhí)行OPTIMIZE TABLE語句或myisamchk -r命令來改善性能敏释，并且出現(xiàn)故障的時(shí)候恢復(fù)相對比較困難库快。
壓縮表由myisamchk工具創(chuàng)建，占據(jù)非常小的空間钥顽，因?yàn)槊織l記錄都是被單獨(dú)壓縮的义屏，所以只有非常小的訪問開支。InnoDB
InnoDB是一個(gè)健壯的事務(wù)型存儲引擎蜂大，這種存儲引擎已經(jīng)被很多互聯(lián)網(wǎng)公司使用闽铐，為用戶操作非常大的數(shù)據(jù)存儲提供了一個(gè)強(qiáng)大的解決方案。我的電腦上安裝的MySQL 5.6.13版奶浦，InnoDB就是作為默認(rèn)的存儲引擎兄墅。InnoDB還引入了行級鎖定和外鍵約束，在以下場合下澳叉，使用InnoDB是最理想的選擇：
1.更新密集的表隙咸。InnoDB存儲引擎特別適合處理多重并發(fā)的更新請求。
2.事務(wù)成洗。InnoDB存儲引擎是支持事務(wù)的標(biāo)準(zhǔn)MySQL存儲引擎五督。
3.自動災(zāi)難恢復(fù)。與其它存儲引擎不同瓶殃，InnoDB表能夠自動從災(zāi)難中恢復(fù)充包。
4.外鍵約束。MySQL支持外鍵的存儲引擎只有InnoDB碌燕。
5.支持自動增加列AUTO_INCREMENT屬性误证。
一般來說继薛，如果需要事務(wù)支持，并且有較高的并發(fā)讀取頻率愈捅，InnoDB是不錯(cuò)的選擇遏考。MEMORY
使用MySQL Memory存儲引擎的出發(fā)點(diǎn)是速度。為得到最快的響應(yīng)時(shí)間蓝谨，采用的邏輯存儲介質(zhì)是系統(tǒng)內(nèi)存灌具。雖然在內(nèi)存中存儲表數(shù)據(jù)確實(shí)會提供很高的性能，但當(dāng)mysqld守護(hù)進(jìn)程崩潰時(shí)譬巫，所有的Memory數(shù)據(jù)都會丟失咖楣。獲得速度的同時(shí)也帶來了一些缺陷。它要求存儲在Memory數(shù)據(jù)表里的數(shù)據(jù)使用的是長度不變的格式芦昔，這意味著不能使用BLOB和TEXT這樣的長度可變的數(shù)據(jù)類型诱贿，VARCHAR是一種長度可變的類型，但因?yàn)樗贛ySQL內(nèi)部當(dāng)做長度固定不變的CHAR類型咕缎，所以可以使用珠十。
一般在以下幾種情況下使用Memory存儲引擎：
1.目標(biāo)數(shù)據(jù)較小，而且被非常頻繁地訪問凭豪。在內(nèi)存中存放數(shù)據(jù)焙蹭，所以會造成內(nèi)存的使用，可以通過參數(shù)max_heap_table_size控制Memory表的大小嫂伞，設(shè)置此參數(shù)孔厉，就可以限制Memory表的最大大小。
2.如果數(shù)據(jù)是臨時(shí)的帖努，而且要求必須立即可用撰豺，那么就可以存放在內(nèi)存表中。
3.存儲在Memory表中的數(shù)據(jù)如果突然丟失然磷，不會對應(yīng)用服務(wù)產(chǎn)生實(shí)質(zhì)的負(fù)面影響郑趁。
Memory同時(shí)支持散列索引和B樹索引。
B樹索引的優(yōu)于散列索引的是姿搜，可以使用部分查詢和通配查詢寡润，也可以使用<、>和>=等操作符方便數(shù)據(jù)挖掘舅柜。散列索引進(jìn)行“相等比較”非乘笪疲快，但是對“范圍比較”的速度就慢多了致份，因此散列索引值適合使用在=和<>的操作符中变抽，不適合在<或>操作符中，也同樣不適合用在order by子句中。
MERGE
MERGE存儲引擎是一組MyISAM表的組合绍载，這些MyISAM表結(jié)構(gòu)必須完全相同扶歪，盡管其使用不如其它引擎突出静浴，但是在某些情況下非常有用抠璃。說白了腰奋，Merge表就是幾個(gè)相同MyISAM表的聚合器；Merge表中并沒有數(shù)據(jù)阳谍，對Merge類型的表可以進(jìn)行查詢蛀柴、更新、刪除操作矫夯，這些操作實(shí)際上是對內(nèi)部的MyISAM表進(jìn)行操作鸽疾。Merge存儲引擎的使用場景。
對于服務(wù)器日志這種信息训貌，一般常用的存儲策略是將數(shù)據(jù)分成很多表制肮，每個(gè)名稱與特定的時(shí)間端相關(guān)。例如：可以用12個(gè)相同的表來存儲服務(wù)器日志數(shù)據(jù)递沪，每個(gè)表用對應(yīng)各個(gè)月份的名字來命名弄企。當(dāng)有必要基于所有12個(gè)日志表的數(shù)據(jù)來生成報(bào)表，這意味著需要編寫并更新多表查詢区拳，以反映這些表中的信息。與其編寫這些可能出現(xiàn)錯(cuò)誤的查詢意乓，不如將這些表合并起來使用一條查詢樱调，之后再刪除Merge表，而不影響原來的數(shù)據(jù)届良，刪除Merge表只是刪除Merge表的定義笆凌，對內(nèi)部的表沒有任何影響。ARCHIVE
Archive是歸檔的意思士葫，在歸檔之后很多的高級功能就不再支持了乞而，僅僅支持最基本的插入和查詢兩種功能。在MySQL 5.5版以前慢显，Archive是不支持索引爪模，但是在MySQL 5.5以后的版本中就開始支持索引了。Archive擁有很好的壓縮機(jī)制荚藻，它使用zlib壓縮庫屋灌，在記錄被請求時(shí)會實(shí)時(shí)壓縮，所以它經(jīng)常被用來當(dāng)做倉庫使用应狱。
存儲引擎的一些問題
1.如何查看服務(wù)器有哪些存儲引擎可以使用共郭？
為確定你的MySQL服務(wù)器可以用哪些存儲引擎，執(zhí)行如下命令：show engines就能搞定了。
2.如何選擇合適的存儲引擎除嘹？
（1）選擇標(biāo)準(zhǔn)可以分為：
（2）是否需要支持事務(wù)写半；
（3）是否需要使用熱備；
（4）崩潰恢復(fù)：能否接受崩潰尉咕；
（5）是否需要外鍵支持叠蝇；
然后按照標(biāo)準(zhǔn)，選擇對應(yīng)的存儲引擎即可龙考。
