大家好捌省，我是彬彬醬福澡，目前在騰訊從事Web后端開(kāi)發(fā)。
由于在工作中經(jīng)常需要使用到MySQL流妻，僅僅知道基本指令是不夠的界斜，所以就開(kāi)了這個(gè)專題整理關(guān)于MySQL 的進(jìn)階知識(shí)，也適合大家進(jìn)行學(xué)習(xí)合冀。

本篇開(kāi)始會(huì)對(duì)MySQL進(jìn)行原理介紹各薇，篇1介紹了MySQL的基礎(chǔ)知識(shí)，包括邏輯架構(gòu)、并發(fā)控制峭判、鎖开缎、事務(wù)性以及存儲(chǔ)引擎，為接下來(lái)的MySQL性能優(yōu)化進(jìn)行鋪墊林螃。

1 - MySQL 邏輯架構(gòu)

最上面的服務(wù)并不是MySQL所獨(dú)有的奕删，大多數(shù)基于網(wǎng)絡(luò)的客戶端/服務(wù)器的工具或者服務(wù)都有類似的架構(gòu)。比如連接處理疗认、授權(quán)認(rèn)證完残、安全等等。

第二層架構(gòu)是MySQL比較有意思的部分横漏。大多數(shù)MySQL的核心服務(wù)功能都在這一層谨设，包括查詢解析、分析缎浇、優(yōu)化扎拣、緩存以及所有的內(nèi)置函數(shù)，所有跨存儲(chǔ)引擎的功能都在這一層實(shí)現(xiàn)：存儲(chǔ)過(guò)程素跺、觸發(fā)器二蓝、視圖等。

第三層包含了存儲(chǔ)引擎指厌，存儲(chǔ)引擎負(fù)責(zé)MySQL中數(shù)據(jù)的存儲(chǔ)和提取刊愚，最常見(jiàn)的引擎包括InnoDB和MyISAM。每一種存儲(chǔ)引擎都有自身的優(yōu)勢(shì)和劣勢(shì)踩验。

MySQL服務(wù)器邏輯架構(gòu)

2 - 并發(fā)控制

當(dāng)多個(gè)查詢?cè)谕粫r(shí)間對(duì)數(shù)據(jù)進(jìn)行修改百拓，就會(huì)產(chǎn)生并發(fā)控制的問(wèn)題。MySQL主要有兩個(gè)層面進(jìn)行并發(fā)控制： 服務(wù)器層與存儲(chǔ)引擎層 晰甚。

2.1 讀寫鎖

在處理并發(fā)讀或者寫時(shí)，可以通過(guò)實(shí)現(xiàn)一個(gè)由兩種類型的鎖組成的鎖系統(tǒng)來(lái)解決問(wèn)題决帖。這兩種類型的鎖通常被稱為 共享鎖 和 排它鎖厕九，也叫 讀鎖 和 寫鎖 。

讀鎖是共享的地回，或者說(shuō)是相互不阻塞的扁远。多個(gè)客戶在同一時(shí)刻可以同時(shí)讀取同一個(gè)資源，而互不干擾刻像。寫鎖則是排他的畅买，也就是說(shuō)一個(gè)寫鎖會(huì)阻塞其他的寫鎖和讀鎖。

2.2 鎖粒度

一種提高共享資源并發(fā)性的方式是讓鎖定對(duì)象更有選擇性细睡。盡量只鎖定需要修改的部分?jǐn)?shù)據(jù)谷羞，而不是全部的資源。更理想的方式是，只會(huì)對(duì)修改的數(shù)據(jù)片進(jìn)行精確的鎖定湃缎。任何時(shí)候犀填，在給定的資源上，鎖定的數(shù)據(jù)量越少嗓违，則系統(tǒng)的并發(fā)程度越高九巡，只要相互之間不發(fā)生沖突即可。

問(wèn)題是加鎖也需要消耗資源蹂季。鎖的各種操作冕广，包括獲得鎖，檢查鎖是否已經(jīng)解除偿洁、釋放鎖等撒汉，都會(huì)增加系統(tǒng)的開(kāi)銷，如果系統(tǒng)花費(fèi)大量的時(shí)間來(lái)管理鎖父能，而不是存取數(shù)據(jù)神凑，那么系統(tǒng)的性能可能會(huì)因此受到影響。下面將介紹兩種最重要的鎖策略何吝。

表鎖（table lock）

表鎖是MySQL中最基本的鎖策略溉委，并且是開(kāi)銷最小的策略。表鎖會(huì)鎖定整張表爱榕，一個(gè)用戶在對(duì)表進(jìn)行寫操作（插入瓣喊、刪除、更新等）前黔酥，需要先獲得寫鎖藻三，這會(huì)阻塞其他用戶對(duì)該表的所有讀寫操作。只有沒(méi)有寫鎖是跪者，其他讀取的用戶才能獲得讀鎖棵帽，讀鎖之間是不相互阻塞的。

盡管存儲(chǔ)引擎可以管理自己的鎖渣玲，MySQL本身還是會(huì)使用各種有效的表鎖來(lái)實(shí)現(xiàn)不同的目的逗概。例如，服務(wù)器會(huì)為諸如ALTER TABLE之類的語(yǔ)句使用表鎖忘衍，而忽略存儲(chǔ)引擎的鎖機(jī)制逾苫。

行級(jí)鎖（row lock）

行級(jí)鎖可以最大程度地支持并發(fā)處理（同時(shí)也帶來(lái)了最大的鎖開(kāi)銷）。行級(jí)鎖只在存儲(chǔ)引擎層實(shí)現(xiàn)枚钓，而MySQL服務(wù)器層沒(méi)有實(shí)現(xiàn)铅搓，例如InnoDB和XtraDB。

3 - 數(shù)據(jù)庫(kù)的事務(wù)性

事務(wù)最典型的應(yīng)用場(chǎng)景是銀行轉(zhuǎn)賬搀捷，當(dāng)A要向B轉(zhuǎn)賬100元時(shí)星掰，需要經(jīng)歷三個(gè)步驟：

1. A的賬戶余額要大于100元

2. 從A的賬戶中減去100元
3. 給B的賬戶中加上100元

上述三個(gè)步驟的操作必須打包在一個(gè)事務(wù)中，任何一個(gè)步驟失敗，則必須回滾所有的步驟蹋偏。而事務(wù)的實(shí)現(xiàn)需要經(jīng)過(guò)嚴(yán)格的ACID測(cè)試便斥，否則空談事務(wù)的概念是不夠的。

3.1 ACID

• 原子性（atomicity）
  一個(gè)事務(wù)必須被視為一個(gè)不可分割的最小工作單元威始，整個(gè)事務(wù)中的所有操作要么全部提交成功枢纠，要么全部失敗回滾。

• 一致性 （consistency）
  數(shù)據(jù)庫(kù)總是從一個(gè)一致性的狀態(tài)轉(zhuǎn)換到另一個(gè)一致性的狀態(tài)黎棠。

• 隔離性（isolation）
  一個(gè)事務(wù)所做的修改在最終提交以前晋渺，對(duì)其他事物是不可見(jiàn)的。

• 持久性（durability）
  一旦事務(wù)提交脓斩，則其所做的修改就會(huì)永久保存到數(shù)據(jù)庫(kù)中木西。

3.2 隔離級(jí)別

• READ UNCOMMITTED（未提交讀）
  　這個(gè)級(jí)別，事務(wù)中的修改随静，即使沒(méi)有提交八千，對(duì)其他事務(wù)也都是可見(jiàn)的。事務(wù)可以讀取未提交的數(shù)據(jù)燎猛，被稱為臟讀（Dirty Read）恋捆，這個(gè)級(jí)別性能不會(huì)比其他級(jí)別好太多，但缺乏其他級(jí)別的很多好處重绷，一般很少使用沸停。

• READ COMMITTED（提交讀）
  　這個(gè)級(jí)別是大多數(shù)數(shù)據(jù)庫(kù)系統(tǒng)的默認(rèn)隔離級(jí)別（但MySQL不是）。一個(gè)事務(wù)從開(kāi)始直到提交之前昭卓，所做的任何修改對(duì)其他事務(wù)都是不可見(jiàn)的愤钾。這個(gè)級(jí)別也叫作不可重復(fù)讀（nonrepeatable read），因?yàn)閮纱螆?zhí)行同樣的查詢候醒，可能會(huì)得到不一樣的結(jié)果能颁。

• REPEATABLE READ（可重復(fù)讀）
  　該級(jí)別保證了在同一個(gè)事務(wù)中多次讀取同樣記錄的結(jié)果是一致的，但依然無(wú)法解決另外一個(gè)幻讀（Phantom Read）的問(wèn)題倒淫』锞眨幻讀，指的是當(dāng)某個(gè)事物在讀取某個(gè)范圍內(nèi)的記錄時(shí)昌简，另外一個(gè)事務(wù)又在該范圍內(nèi)插入了新的記錄，當(dāng)之前的事務(wù)再次讀取該范圍的記錄時(shí)绒怨，會(huì)產(chǎn)生幻行（Phantom Row）纯赎。InnoDB 和 XtraDB 存儲(chǔ)引擎通過(guò)多版本并發(fā)控制（MVCC）解決了幻讀的問(wèn)題∧硝澹可重復(fù)讀是MySQL的默認(rèn)事務(wù)隔離級(jí)別犬金。

• SERIALIZABLE（可串行化）
  　最高的隔離級(jí)別，強(qiáng)制事務(wù)串行執(zhí)行，避免了前面說(shuō)的幻讀的問(wèn)題晚顷。會(huì)在讀取的每一行數(shù)據(jù)上都加鎖峰伙，可能會(huì)導(dǎo)致大量的超時(shí)和鎖征用的問(wèn)題，實(shí)際應(yīng)用中也很少用到這個(gè)級(jí)別该默。

3.3 死鎖

死鎖是指兩個(gè)或者多個(gè)事務(wù)在同一資源上相互占用瞳氓，并請(qǐng)求鎖定對(duì)方占用的資源，從而導(dǎo)致惡性循環(huán)的現(xiàn)象栓袖。當(dāng)多個(gè)事務(wù)試圖以不同的順序鎖定資源時(shí)匣摘，就可能會(huì)產(chǎn)生死鎖。多個(gè)事務(wù)同時(shí)鎖定同一個(gè)資源時(shí)裹刮，也會(huì)產(chǎn)生死鎖音榜。InnoDB目前處理死鎖的方法是，將持有最少行級(jí)排他鎖的事務(wù)進(jìn)行回滾（這是相對(duì)比較簡(jiǎn)單的死鎖回滾算法）捧弃。

4 - 多版本并發(fā)控制（MVCC）

4.1 樂(lè)觀并發(fā)控制

顧名思義赠叼，就是很樂(lè)觀，每次去拿數(shù)據(jù)的時(shí)候都認(rèn)為別人不會(huì)修改违霞，所以不會(huì)上鎖嘴办，但是在更新的時(shí)候會(huì)判斷一下在此期間別人有沒(méi)有去更新這個(gè)數(shù)據(jù)，可以使用版本號(hào)等機(jī)制葛家。樂(lè)觀鎖適用于多讀的應(yīng)用類型户辞，這樣可以提高吞吐量，像數(shù)據(jù)庫(kù)如果提供類似于write_condition機(jī)制的其實(shí)都是提供的樂(lè)觀鎖癞谒。

版本號(hào)機(jī)制
　每行記錄后面保存兩個(gè)隱藏的列來(lái)實(shí)現(xiàn)底燎，這兩個(gè)列，分別保存了行的創(chuàng)建時(shí)間和行的刪除時(shí)間（系統(tǒng)版本號(hào)）弹砚。每開(kāi)始一個(gè)新的事務(wù)双仍，系統(tǒng)版本號(hào)都會(huì)自動(dòng)遞增。

SELECT
　a.查找版本早于當(dāng)前事務(wù)版本的數(shù)據(jù)行
　b.行的刪除版本要么未定義桌吃，要么大于當(dāng)前事務(wù)版本號(hào)

INSERT
　新插入的每一行保存當(dāng)前系統(tǒng)版本號(hào)作為行版本號(hào)

DELETE
　刪除的每一行保存當(dāng)前系統(tǒng)版本號(hào)作為行刪除標(biāo)識(shí)

UPDATE
　插入的新紀(jì)錄保存為行版本號(hào)朱沃，原來(lái)的行保存行刪除標(biāo)識(shí)

4.2 悲觀并發(fā)控制

就是很悲觀，每次去拿數(shù)據(jù)的時(shí)候都認(rèn)為別人會(huì)修改茅诱，所以每次在拿數(shù)據(jù)的時(shí)候都會(huì)上鎖逗物，這樣別人想拿這個(gè)數(shù)據(jù)就會(huì)block直到它拿到鎖。傳統(tǒng)的關(guān)系型數(shù)據(jù)庫(kù)里邊就用到了很多這種鎖機(jī)制瑟俭，比如行鎖翎卓，表鎖等，讀鎖摆寄，寫鎖等失暴，都是在做操作之前先上鎖

5 - MySQL的存儲(chǔ)引擎

通過(guò) show table status 指令來(lái)查看表的信息

mysql>show table status like 'esf_seller_history'\G;

show table status

5.1 InnoDB存儲(chǔ)引擎

InnoDB是MySQL的默認(rèn)事務(wù)型引擎乔夯，也是最重要砖织、使用最廣泛的存儲(chǔ)引擎。它被設(shè)計(jì)用來(lái)處理大量的短期事務(wù)末荐，短期事務(wù)大部分情況是正常提交的侧纯，很少會(huì)被回滾。InnoDB的性能和自動(dòng)崩潰恢復(fù)特性甲脏，使得它在非事務(wù)型存儲(chǔ)的需求中也很流行眶熬。

1. InnoDB的數(shù)據(jù)存儲(chǔ)在表空間中；

2. InnoDB采用MVCC來(lái)支持高并發(fā)块请，并且實(shí)現(xiàn)了四個(gè)標(biāo)準(zhǔn)的隔離級(jí)別娜氏，默認(rèn)級(jí)別是REPEATABLE READ（可重復(fù)讀），并且通過(guò)間隙鎖（next-key locking）策略防止幻讀的出現(xiàn)墩新。間隙鎖使得InnoDB不僅僅鎖定查詢涉及的行贸弥，還會(huì)對(duì)索引中的間隙進(jìn)行鎖定，以防止幻影行的插入抖棘。

3. InnoDB表是基于聚簇索引建立的茂腥，InnoDB的索引結(jié)構(gòu)和MySQL的其他存儲(chǔ)引擎有很大的不同，聚簇索引對(duì)主鍵查詢有很高的性能切省。不過(guò)它的二級(jí)索引中必須包含主鍵列最岗，所以如果主鍵列很大的話，其他的索引都會(huì)很大朝捆。因此般渡，若表上的索引較多的話，主鍵應(yīng)當(dāng)盡可能的小芙盘。

4. InnoDB內(nèi)部做了很多優(yōu)化驯用，包括從磁盤讀取數(shù)據(jù)時(shí)采用的可預(yù)測(cè)性預(yù)讀，能夠自動(dòng)在內(nèi)存中創(chuàng)建hash索引以加速讀操作的自適應(yīng)哈希索引儒老，以及能夠加速插入操作的插入緩沖區(qū)蝴乔。

5.2 MyISAM存儲(chǔ)引擎

MyISAM提供了大量的特性，包括全文索引驮樊、壓縮薇正、空間函數(shù)等，但MyISAM不支持事務(wù)和行級(jí)鎖囚衔，而且有一個(gè)毫無(wú)疑問(wèn)的缺陷就是崩潰后無(wú)法安全恢復(fù)挖腰。對(duì)于只讀的數(shù)據(jù)，或者表比較小练湿，可以忍受修復(fù)操作猴仑，則依然可以繼續(xù)使用MyISAM。

存儲(chǔ)
　MyISAM會(huì)將表存儲(chǔ)在兩個(gè)文件中：數(shù)據(jù)文件和索引文件肥哎，分別以.MYD和.MYI為擴(kuò)展名辽俗。MyISAM表可以包含動(dòng)態(tài)或者靜態(tài)行。MySQL會(huì)根據(jù)表的定義來(lái)決定采用何種行格式贤姆。MyISAM表可以存儲(chǔ)的行記錄數(shù)榆苞，一般受限于可用的磁盤空間，或者操作系統(tǒng)中單個(gè)文件的最大尺寸霞捡。

MyISAM特性

• 加鎖與并發(fā)
  　MyISAM對(duì)整張表加鎖坐漏，而不是針對(duì)行。讀取時(shí)會(huì)對(duì)需要讀到的所有表加共享鎖碧信，寫入時(shí)則對(duì)表加排它鎖赊琳。但是在表有讀取查詢的同時(shí)，也可以往表中插入新的記錄砰碴。

• 修復(fù)
  　對(duì)于MyISAM表躏筏，MySQL可以手工或者自動(dòng)執(zhí)行檢查和修復(fù)操作，但這里說(shuō)的修復(fù)和事務(wù)恢復(fù)以及崩潰恢復(fù)是不同的概念呈枉。執(zhí)行表的修復(fù)可能導(dǎo)致一些數(shù)據(jù)丟失趁尼，而且修復(fù)操作是非常慢的埃碱。

• 索引特性
  　對(duì)于MyISAM表，即使是BLOB和TEXT等長(zhǎng)字段酥泞，也可以基于其前500個(gè)字符創(chuàng)建索引砚殿。MyISAM也支持全文索引，這是一種基于分詞創(chuàng)建的索引芝囤，可以支持復(fù)雜的查詢似炎。

• 延遲更新索引鍵
  　創(chuàng)建MyISAM表的時(shí)候，如果指定了DELAY_KEY_WRITE選項(xiàng)悯姊，在每次修改執(zhí)行完成時(shí)羡藐，不會(huì)立刻將修改的索引數(shù)據(jù)寫入磁盤，而是會(huì)寫到內(nèi)存中的鍵緩沖區(qū)悯许，只有在清理鍵緩沖區(qū)或者關(guān)閉表的時(shí)候才會(huì)將對(duì)應(yīng)的索引塊寫入到磁盤仆嗦。這種方式可以極大地提升寫入性能，但是在數(shù)據(jù)庫(kù)或者主機(jī)崩潰時(shí)會(huì)造成索引損壞先壕，需要執(zhí)行修復(fù)操作欧啤。

大家好，我是彬彬醬启上，目前在騰訊從事Web后端開(kāi)發(fā)邢隧。
菜鳥必知的 MySQL 知識(shí)專題整理了關(guān)于 MySQL 的基礎(chǔ)知識(shí)，適合大家進(jìn)行入門級(jí)學(xué)習(xí)冈在，這個(gè)專題現(xiàn)包含下列文章：
菜鳥必知的 MySQL 知識(shí)（一）—— 基礎(chǔ)知識(shí)
菜鳥必知的 MySQL 知識(shí)（二）—— 數(shù)據(jù)類型優(yōu)化
菜鳥必知的 MySQL 知識(shí)（三）—— 索引優(yōu)化