《高性能MySQL》&《MySQL技術(shù)內(nèi)幕 InnoDB存儲(chǔ)引擎》筆記

第一章 MySQL架構(gòu)與歷史

MySQL的架構(gòu)

從上圖可以看出，MySQL數(shù)據(jù)庫(kù)區(qū)別于其他數(shù)據(jù)庫(kù)的最重要的一個(gè)特點(diǎn)就是其插件式的表存儲(chǔ)引擎畔勤。需要注意的是妆艘，存儲(chǔ)引擎是基于表的，而不是數(shù)據(jù)庫(kù)的（即同一個(gè)數(shù)據(jù)庫(kù)中的不同表可以有不同的存儲(chǔ)引擎）康吵。

MySQL是一個(gè)單進(jìn)程多線程架構(gòu)的數(shù)據(jù)庫(kù)实夹。

連接MySQL

連接MySQL是一個(gè)連接進(jìn)程和MySQL數(shù)據(jù)庫(kù)實(shí)例進(jìn)行通信橄浓。從程序設(shè)計(jì)的角度來(lái)說(shuō)，本質(zhì)上是進(jìn)程通信亮航。

連接MySQL的方式有：TCP/IP套接字荸实、命名管道和共享內(nèi)存、UNIX域套接字缴淋。

InnoDB與MyISAM存儲(chǔ)引擎對(duì)比

InnoDB支持事務(wù)准给、外鍵泄朴、行鎖；支持非鎖定讀圆存，即默認(rèn)讀取操作不會(huì)產(chǎn)生鎖叼旋。
InnoDB通過(guò)使用多版本并發(fā)控制（MVCC）來(lái)獲得高并發(fā)性，并且實(shí)現(xiàn)了SQL標(biāo)準(zhǔn)的4種隔離級(jí)別沦辙，默認(rèn)為REPEATABLE級(jí)別。
提供了插入緩沖讹剔，二次寫(xiě)油讯，自適應(yīng)哈希索引，預(yù)讀等高性能和高可用的功能延欠。
對(duì)于表中數(shù)據(jù)的存儲(chǔ)陌兑，InnoDB存儲(chǔ)引擎采用了聚集的方式，因此每張表數(shù)據(jù)的存儲(chǔ)都是按照主鍵的順序進(jìn)行存放（這種表稱為“索引組織表”）由捎。

MyISAM（發(fā)音：my-z[ei]m）不支持事務(wù)兔综、表鎖設(shè)計(jì)，支持全文索引（InnoDB已經(jīng)支持）狞玛。

MyISAM相對(duì)簡(jiǎn)單软驰，所以在效率上要優(yōu)于InnoDB，小型應(yīng)用可以考慮使用MyISAM心肪。當(dāng)你的數(shù)據(jù)庫(kù)有大量的寫(xiě)入锭亏、更新操作而查詢比較少或者數(shù)據(jù)完整性要求比較高的時(shí)候就選擇InnoDB表。當(dāng)你的數(shù)據(jù)庫(kù)主要以查詢?yōu)橹饔舶埃啾容^而言更新和寫(xiě) 入比較少慧瘤，并且業(yè)務(wù)方面數(shù)據(jù)完整性要求不那么嚴(yán)格，就選擇MyISAM表固该。

第二章 InnoDB存儲(chǔ)引擎概述

內(nèi)存

緩沖池

在數(shù)據(jù)庫(kù)系統(tǒng)中锅减，由于CPU速度與磁盤(pán)速度之間的鴻溝，基于磁盤(pán)的數(shù)據(jù)庫(kù)系統(tǒng)通常使用緩沖池技術(shù)來(lái)提高數(shù)據(jù)庫(kù)的整體性能伐坏。

（注：上圖中左上角的日志緩沖應(yīng)該為重做日志緩沖）
需要注意的是怔匣，頁(yè)從緩沖池刷新回磁盤(pán)的操作并不是在每次頁(yè)發(fā)生更新時(shí)觸發(fā)，而是通過(guò)一種稱為Checkpoint的機(jī)制刷新回磁盤(pán)著淆。

重做日志緩沖

重做日志緩沖一般不需要設(shè)置的很大劫狠，因?yàn)橐话闱闆r下每一秒鐘會(huì)將重做日志緩沖刷新到日志文件，因此用戶只需要保證每秒產(chǎn)生的事務(wù)量在這個(gè)緩沖大小之內(nèi)即可永部。默認(rèn)為8MB.

系統(tǒng)在以下三種情況下會(huì)將重做日志緩沖中的內(nèi)容刷新到外部磁盤(pán)的重做日志文件中：

• Master Thread每一秒將重做日志緩沖刷新到重做日志文件独泞；
• 每個(gè)事務(wù)提交時(shí)會(huì)將重做日志緩沖刷新到重做日志文件；
• 當(dāng)重做日志緩沖池剩余空間小與1/2時(shí)苔埋，重做日志緩沖刷新到重做日志文件懦砂。

Checkpoint技術(shù)

為了避免發(fā)生數(shù)據(jù)丟失的問(wèn)題，當(dāng)前事務(wù)數(shù)據(jù)庫(kù)系統(tǒng)普遍都采用了Write Ahead Log策略，即當(dāng)事務(wù)提交時(shí)荞膘，先寫(xiě)重做日志罚随，再修改頁(yè)。當(dāng)由于宕機(jī)而導(dǎo)致數(shù)據(jù)丟失時(shí)羽资，通過(guò)重做日志來(lái)完成數(shù)據(jù)的恢復(fù)淘菩。這也是事務(wù)ACID中D（Durability 持久性）的要求。

Checkpoint技術(shù)是用來(lái)解決以下幾個(gè)問(wèn)題：

• 縮短數(shù)據(jù)庫(kù)的恢復(fù)時(shí)間屠升；
• 緩沖池不夠用時(shí)潮改，將臟頁(yè)刷新到磁盤(pán)；
• 重做日志不可用時(shí)腹暖，刷新臟頁(yè)汇在。

當(dāng)數(shù)據(jù)庫(kù)發(fā)生宕機(jī)時(shí)，數(shù)據(jù)庫(kù)不需要重做所有的日志脏答，因?yàn)镃heckpoint之前的頁(yè)都已經(jīng)刷新回磁盤(pán)糕殉。故數(shù)據(jù)庫(kù)只需對(duì)Checkpoint后的重做日志進(jìn)行恢復(fù)。這樣就大大縮短了恢復(fù)的時(shí)間殖告。

此外阿蝶，當(dāng)緩沖池不夠用時(shí)，根據(jù)LRU算法會(huì)溢出最近最少使用的頁(yè)丛肮，若此頁(yè)為臟頁(yè)赡磅，那么需要強(qiáng)制執(zhí)行Checkpoint，將臟頁(yè)也就是頁(yè)的新版本刷新回磁盤(pán)宝与。

重做日志出現(xiàn)不可用的情況是因?yàn)楫?dāng)前事務(wù)數(shù)據(jù)庫(kù)系統(tǒng)對(duì)重做日志的設(shè)計(jì)都是循環(huán)使用的焚廊，并不是讓其無(wú)限增大的。重做日志可以被重用的部分是指這部分重做日志已經(jīng)不再需要习劫，即當(dāng)數(shù)據(jù)庫(kù)發(fā)生宕機(jī)時(shí)咆瘟，數(shù)據(jù)庫(kù)恢復(fù)操作不需要這部分的重做日志，因此這部分就可以被覆蓋重用诽里。若此時(shí)這部分重做日志還需要使用袒餐，那么必須強(qiáng)制產(chǎn)生Checkpoint，將緩沖池中的頁(yè)至少刷新到當(dāng)前重做日志的位置谤狡。

對(duì)于InnoDB存儲(chǔ)引擎而言灸眼，其是通過(guò)LSN（Log Sequence Number）來(lái)標(biāo)記版本的。而LSN是8字節(jié)的數(shù)字墓懂，其單位是字節(jié)焰宣。每個(gè)頁(yè)有LSN，重做日志中也有LSN捕仔，Checkpoint也有LSN匕积。

第三章 文件

日志文件

錯(cuò)誤日志

錯(cuò)誤日志文件對(duì)MySQL的啟動(dòng)盈罐、運(yùn)行、關(guān)閉過(guò)程進(jìn)行了記錄闪唆。MySQL DBA 在遇到問(wèn)題時(shí)應(yīng)該首先查看該文件以便定位問(wèn)題盅粪。該文件不僅記錄了所有的錯(cuò)誤信息，也記錄了一些警告信息或正確的信息悄蕾。

慢查詢?nèi)罩?/h5>

可以在MySQL啟動(dòng)時(shí)設(shè)置一個(gè)閾值票顾，將運(yùn)行時(shí)間超過(guò)該值的所有SQL語(yǔ)句都記錄到慢查詢?nèi)罩疚募小Ｔ撝的J(rèn)為10秒帆调。

查詢?nèi)罩?/h5>

查詢?nèi)罩居涗浟怂袑?duì)MySQL數(shù)據(jù)庫(kù)請(qǐng)求的信息库物，無(wú)論這些請(qǐng)求是否得到了正確的執(zhí)行。

二進(jìn)制日志

二進(jìn)制日志（binary log）記錄了對(duì)MySQL數(shù)據(jù)庫(kù)執(zhí)行更改的所有操作贷帮。

二進(jìn)制日志文件默認(rèn)未開(kāi)啟。手動(dòng)開(kāi)啟后會(huì)使系統(tǒng)性能下降大概1%.

但考慮到可以使用復(fù)制（replication）和point-in-time的恢復(fù)诱告，這些性能損失絕對(duì)是可以且應(yīng)該被接受的撵枢。

重做日志文件

在默認(rèn)情況下，在InnoDB存儲(chǔ)引擎的數(shù)據(jù)目錄下會(huì)有兩個(gè)名為ib_logfile0和ib_logfile1的文件精居。這兩個(gè)文件就是重做日志文件锄禽，或者事務(wù)日志。

重做日志的目的：萬(wàn)一實(shí)例或者介質(zhì)失敗靴姿，重做日志文件就能派上用場(chǎng)沃但。例如，數(shù)據(jù)庫(kù)由于所在主機(jī)掉電導(dǎo)致實(shí)例失敗佛吓，InnoDB存儲(chǔ)引擎會(huì)使用重做日志恢復(fù)到掉電前的時(shí)刻宵晚，以此來(lái)保證數(shù)據(jù)的完整性。

每個(gè)InnoDB存儲(chǔ)引擎至少有一個(gè)重做日志文件組维雇，每個(gè)文件組下至少有2個(gè)重做日志文件淤刃，如默認(rèn)的ib_logfile0、ib_logfile1吱型。InnoDB存儲(chǔ)引擎先寫(xiě)重做日志文件1逸贾，當(dāng)達(dá)到文件的最后時(shí)，會(huì)切換至重做日志文件2津滞，當(dāng)重做日志文件2也被寫(xiě)滿時(shí)铝侵，會(huì)再被切換到重做日志文件1中。

重做日志與二進(jìn)制日志的區(qū)別：

1. 二進(jìn)制日志會(huì)記錄所有與mysql數(shù)據(jù)庫(kù)有關(guān)的日志記錄触徐，包括InnoDB咪鲜、MyISAM、Heap等其他存儲(chǔ)引擎的日志锌介，而InnoDB存儲(chǔ)引擎的重做日志只記錄有關(guān)其本身的事務(wù)日志嗜诀，

2. 記錄的內(nèi)容不同猾警，不管你將二進(jìn)制日志文件記錄的格式設(shè)為哪一種，其記錄的都是關(guān)于一個(gè)事務(wù)的具體操作內(nèi)容隆敢，即該日志是邏輯日志发皿；而InnoDB存儲(chǔ)引擎的重做日志文件記錄的關(guān)于每個(gè)頁(yè)的更改的物理情況；

3. 寫(xiě)入的時(shí)間也不同拂蝎，二進(jìn)制日志文件是在事務(wù)提交前進(jìn)行提交穴墅，即只寫(xiě)磁盤(pán)一次，不論這時(shí)該事務(wù)多大温自；而在事務(wù)進(jìn)行的過(guò)程中玄货，不斷有重做日志條目被寫(xiě)入重做日志文件中。

第四章 之一 表

索引組織表

在InnoDB存儲(chǔ)引擎中悼泌，表都是根據(jù)主鍵順序組織存放的松捉，這種存儲(chǔ)方式的表稱為索引組織表（index organized table）。在InnoDB存儲(chǔ)引擎表中馆里，每張表都有個(gè)主鍵隘世，如果在創(chuàng)建表時(shí)沒(méi)有顯式定義主鍵，則InnoDB存儲(chǔ)引擎會(huì)按如下方式選擇或創(chuàng)建主鍵：

• 首先判斷表中是否存在非空的唯一索引（Unique NOT NULL）鸠踪，如果有丙者，則該列即為主鍵；
• 如果不符合上述條件营密，InnoDB存儲(chǔ)引擎會(huì)自動(dòng)創(chuàng)建一個(gè)6字節(jié)大小的指針械媒；

對(duì)于其他的一些數(shù)據(jù)庫(kù)，如Microsoft SQL Server數(shù)據(jù)庫(kù)评汰，其中一種稱為堆表的表類型纷捞，即行數(shù)據(jù)的存儲(chǔ)按照插入的順序存放。堆表的特性決定了堆表上的索引都是非聚集的键俱。

需要牢記的是兰绣，B+樹(shù)索引本身并不能找到具體的一條記錄，能找到的只是該記錄所在的頁(yè)编振。數(shù)據(jù)庫(kù)把頁(yè)載入到內(nèi)存缀辩，然后通過(guò)Page Directory再進(jìn)行二叉查找。只不過(guò)二叉查找的時(shí)間復(fù)雜度很低踪央，同時(shí)在內(nèi)存中的查找很快臀玄，因此通常忽略這部分查找所用的時(shí)間。

從InnoDB存儲(chǔ)引擎的邏輯存儲(chǔ)結(jié)構(gòu)看畅蹂，所有數(shù)據(jù)都被邏輯地存放在一個(gè)空間中健无，稱之為表空間(tablespace)。表空間又由段（segment）液斜、區(qū)（extent）累贤、頁(yè)（page）組成叠穆。頁(yè)在一些文檔中有時(shí)也稱為（block），InnoDB存儲(chǔ)引擎的邏輯存儲(chǔ)結(jié)構(gòu)大致如圖：

VARCHAR

• MySQL數(shù)據(jù)庫(kù)的VARCHAR類型可以存放65535字節(jié)數(shù)據(jù)（除去別的開(kāi)銷(xiāo)臼膏，實(shí)際最大可以存放65532字節(jié)）硼被；
• VARCHAR(N)中的N是指字符數(shù)；
• 此外渗磅，此處65535長(zhǎng)度是指所有VARCHAR列的長(zhǎng)度總和嚷硫，如果列的長(zhǎng)度總和超出這個(gè)長(zhǎng)度，依然無(wú)法創(chuàng)建始鱼，如：

CREATE TABLE test (
    a VARCHAR(22000),
    b VARCHAR(22000),
    c VARCHAR(22000)
) CHARSET = latin1

分區(qū)表

分區(qū)的過(guò)程是將一個(gè)表或索引分解為多個(gè)更小仔掸、更可管理的部分。就訪問(wèn)數(shù)據(jù)庫(kù)的應(yīng)用而言医清，從邏輯上講起暮，只有一個(gè)表或一個(gè)索引，但是在物理上這個(gè)表或索引可能由數(shù)十個(gè)物理分區(qū)組成会烙。每個(gè)分區(qū)都是獨(dú)立的對(duì)象鞋怀，都可獨(dú)自處理，也可以作為一個(gè)更大對(duì)象的一部分進(jìn)行處理持搜。

當(dāng)前MySQL數(shù)據(jù)庫(kù)支持以下幾種類型的分區(qū)：

• RANGE分區(qū)：行數(shù)據(jù)基于屬于一個(gè)給定連續(xù)區(qū)間的列值放入分區(qū)；
• LIST分區(qū)：和RANGE類似焙矛，只是LIST分區(qū)里面是離散的值葫盼；
• HASH分區(qū)：根據(jù)用戶自定義的表達(dá)式的返回值來(lái)進(jìn)行分區(qū)，返回值不能為負(fù)數(shù)村斟；
• KEY分區(qū)：根據(jù)MySQL數(shù)據(jù)庫(kù)提供的（即內(nèi)置的）哈希函數(shù)進(jìn)行分區(qū)贫导。

分區(qū)和性能

數(shù)據(jù)庫(kù)應(yīng)用分為兩類：一類是OLTP（在線事務(wù)處理），如Blog蟆盹，電子商務(wù)孩灯，網(wǎng)絡(luò)游戲等；另一類是OLAP（在線分析處理）逾滥，如數(shù)據(jù)倉(cāng)庫(kù)峰档，數(shù)據(jù)集市。在一個(gè)實(shí)際的應(yīng)用環(huán)境中寨昙，可能既有OLTP的應(yīng)用讥巡，也有OLAP的應(yīng)用。如網(wǎng)絡(luò)游戲中舔哪，玩家的操作的游戲數(shù)據(jù)庫(kù)應(yīng)用就是OLTP的欢顷，但是游戲廠商可能需要對(duì)游戲產(chǎn)生的日志進(jìn)行分析，通過(guò)分析得到的結(jié)果來(lái)更好地服務(wù)于游戲捉蚤，預(yù)測(cè)玩家的行為等抬驴，而這卻是OLAP的應(yīng)用炼七。

對(duì)于OLAP的應(yīng)用，分區(qū)的確可以很好地提高查詢的性能布持，因?yàn)镺LAP應(yīng)用大多數(shù)查詢需要頻繁地掃描一張很大的表豌拙。假設(shè)有一張1億行的表，其中有一個(gè)時(shí)間戳屬性列鳖链。用戶的查詢需要從這張表中獲取一年的數(shù)據(jù)姆蘸。如果按時(shí)間戳進(jìn)行分區(qū)，則只需要掃描相應(yīng)的分區(qū)即可芙委。

然而對(duì)于OLTP的應(yīng)用逞敷，分區(qū)應(yīng)該非常小心。在這種應(yīng)用下灌侣，通常不可能會(huì)獲取一張大表中10%的數(shù)據(jù)推捐，大部分都是通過(guò)索引返回幾條記錄即可。而根據(jù)B+樹(shù)索引的原理可知侧啼，對(duì)于一張大表牛柒，一般的B+樹(shù)需要2~3次的磁盤(pán)IO。因此B+樹(shù)可以很好地完成操作痊乾，不需要分區(qū)的幫助皮壁，并且設(shè)計(jì)不好的分區(qū)會(huì)帶來(lái)嚴(yán)重的性能問(wèn)題。

例如：很多開(kāi)發(fā)團(tuán)隊(duì)會(huì)認(rèn)為含有1000W行的表是一張非常大的表哪审，所以他們往往會(huì)采用分區(qū)蛾魄，如對(duì)主鍵做10個(gè)HASH的分區(qū)，這樣每個(gè)分區(qū)就只有100W的數(shù)據(jù)了湿滓，因此查詢應(yīng)該變快了滴须，如SELECT * FROM TABLE WHERE PK=@pk。但是有沒(méi)有考慮過(guò)這樣一種情況：100W和1000W行的數(shù)據(jù)本身構(gòu)成的B+樹(shù)的層次都是一樣的可能都是2層叽奥。那么上述走主鍵分區(qū)的索引并不會(huì)帶來(lái)性能的提高扔水。如果1000W的B+樹(shù)高度是3,100W的B+樹(shù)的高度是2，那么上述按主鍵分區(qū)的索引可以避免1次IO朝氓，從而提高查詢效率魔市。這沒(méi)問(wèn)題，但是這張表只有主鍵索引赵哲，沒(méi)有任何其他的列需要查詢的嘹狞，如果還有類似如下的SQL語(yǔ)句：SELECT * FROM TABLE WHERE KEY = @key,這時(shí)對(duì)于KEY的查詢需要掃描所有的10個(gè)分區(qū)，即使每個(gè)分區(qū)的查詢開(kāi)銷(xiāo)為2次IO誓竿，則一共需要20次IO磅网。而對(duì)于原來(lái)單表的設(shè)計(jì)，對(duì)于KEY的查詢只需要2~3次IO筷屡。

這里涧偷，MySQL數(shù)據(jù)庫(kù)的分區(qū)是局部分區(qū)索引簸喂，一個(gè)分區(qū)中既存放了數(shù)據(jù)又存放了索引。而全局分區(qū)是指燎潮，數(shù)據(jù)存放在各個(gè)分區(qū)中喻鳄，但是所有數(shù)據(jù)的索引放在一個(gè)對(duì)象中。

——沒(méi)有全局的索引确封，所以才需要遍歷每個(gè)分區(qū)的索引除呵。

第四章 之二 Schema與數(shù)據(jù)類型優(yōu)化

選擇優(yōu)化的數(shù)據(jù)類型

• 更小的通常更好；更小的數(shù)據(jù)類型通常更快爪喘，因?yàn)樗鼈冋加酶俚拇疟P(pán)颜曾、內(nèi)存和CPU緩存，并且處理時(shí)需要的CPU周期也更少秉剑；
• 簡(jiǎn)單就好泛豪；例如，整形比字符串操作代價(jià)更低侦鹏；實(shí)用內(nèi)建類型而不是字符串來(lái)存儲(chǔ)日期和時(shí)間诡曙；用整形存儲(chǔ)IP地址等；
• 盡量避免NULL略水；如果查詢中包含可為NULL的列价卤，對(duì)MySQL來(lái)說(shuō)更難優(yōu)化，因?yàn)榭蔀镹ULL 的列使得索引渊涝、索引統(tǒng)計(jì)和值比較都更復(fù)雜荠雕。盡管把可為NULL的列改為NOT NULL帶來(lái)的性能提升比較小，但如果計(jì)劃在列上創(chuàng)建索引耿戚，就應(yīng)該盡量避免設(shè)計(jì)成可為NULL的列胆剧；

字符串類型

VARCHAR 和 CHAR

VARCHAR是最常見(jiàn)的字符串類型。VARCHAR節(jié)省了存儲(chǔ)空間，所以對(duì)性能也有幫助奸腺。但是，由于行是可變的淋肾，在UPDATE時(shí)可能使行變得比原來(lái)更長(zhǎng)媒抠，這就導(dǎo)致需要做額外的工作。如果一個(gè)行占用的空間增長(zhǎng)洒敏，并且在頁(yè)內(nèi)沒(méi)有更多的空間可以存儲(chǔ)龄恋，MyISAM會(huì)將行拆成不同的片段存儲(chǔ)；InnoDB則需要分裂頁(yè)來(lái)使行可以放進(jìn)頁(yè)內(nèi)凶伙。

下面這些情況使用VARCHAR是合適的：字符串的最大長(zhǎng)度比平均長(zhǎng)度大很多郭毕；列的更新很少，所以碎片不是問(wèn)題函荣；使用了像UTF-8這樣復(fù)雜的字符集显押，每個(gè)字符都使用不同的字節(jié)數(shù)進(jìn)行存儲(chǔ)扳肛。

當(dāng)存儲(chǔ)CHAR值時(shí)，MySQL會(huì)刪除所有的末尾空格乘碑。CHAR值會(huì)根據(jù)需要采用空格進(jìn)行填充以方便比較挖息。

CHAR適合存儲(chǔ)很短的字符串，或者所有值都接近同一個(gè)長(zhǎng)度兽肤，如密碼的MD5值套腹。對(duì)于經(jīng)常變更的數(shù)據(jù)，CHAR也比VARCHAR更好资铡，因?yàn)镃HAR不容易產(chǎn)生碎片（行間碎片电禀？）。

慷慨是不明智的

使用VARCHAR(5)和VARCHAR(200)存儲(chǔ)"hello"的空間開(kāi)銷(xiāo)是一樣的害驹。那么使用更短的列有什么優(yōu)勢(shì)嗎鞭呕？

事實(shí)證明有很大的優(yōu)勢(shì)。更長(zhǎng)的列會(huì)消耗更多的內(nèi)存宛官，因?yàn)镸ySQL通常會(huì)分配固定大小的內(nèi)存塊來(lái)保存內(nèi)部值葫松。尤其是使用內(nèi)存臨時(shí)表進(jìn)行排序或其他操作時(shí)會(huì)特別糟糕。在利用磁盤(pán)臨時(shí)表進(jìn)行排序時(shí)也同樣糟糕底洗。

所以最好的策略是只分配真正需要的空間腋么。

BLOB 和 TEXT

BLOB和TEXT都是為存儲(chǔ)很大的數(shù)據(jù)而設(shè)計(jì)的數(shù)據(jù)類型，分別采用二進(jìn)制和字符方式存儲(chǔ)亥揖。

與其他類型不同珊擂，MySQL把每個(gè)BLOB和TEXT值當(dāng)做一個(gè)獨(dú)立的對(duì)象去處理。當(dāng)BLOB和TEXT值太大時(shí)费变，InnoDB會(huì)使用專門(mén)的“外部”存儲(chǔ)區(qū)域來(lái)進(jìn)行存儲(chǔ)摧扇，此時(shí)每個(gè)值在行內(nèi)需要1~4個(gè)字節(jié)存儲(chǔ)一個(gè)指針，然后在外部存儲(chǔ)區(qū)域存儲(chǔ)實(shí)際的值挚歧。

MySQL對(duì)BLOB和TEXT列進(jìn)行排序與其他類型是不同的：它只對(duì)每個(gè)列的最前max_sort_length個(gè)字節(jié)而不是整個(gè)字符串做排序扛稽。同樣的，MySQL也不能將BLOB或TEXT列全部長(zhǎng)度的字符串進(jìn)行索引滑负。

選擇表示符（identifier）

整數(shù)類型通常是標(biāo)識(shí)列的最佳選擇在张，因?yàn)樗鼈兒芸觳⑶铱梢允褂肁UTO_INCREMENT。
如果可能矮慕，應(yīng)該避免使用字符串類型作為標(biāo)識(shí)列帮匾，因?yàn)樗鼈兒芎目臻g，并且比數(shù)字類型慢痴鳄。
對(duì)于完全隨機(jī)的字符串也需要多加注意瘟斜，例如MD5(),SHA1()或者UUID()產(chǎn)生的字符串。這些函數(shù)生成的新值會(huì)任意分布在很大的空間內(nèi)，這會(huì)導(dǎo)致INSERT以及一些SELECT語(yǔ)句變得很慢：

• 因?yàn)椴迦胫禃?huì)隨機(jī)的寫(xiě)入到索引的不同位置哼转，所以使得INSERT語(yǔ)句更慢明未。這會(huì)導(dǎo)致葉分裂、磁盤(pán)隨機(jī)訪問(wèn)壹蔓。
• SELECT語(yǔ)句會(huì)變的更慢趟妥，因?yàn)檫壿嬌舷噜彽男袝?huì)分布在磁盤(pán)和內(nèi)存的不同地方。
• 隨機(jī)值導(dǎo)致緩存對(duì)所有類型的查詢語(yǔ)句效果都很差佣蓉，因?yàn)闀?huì)使得緩存賴以工作的局部性原理失效披摄。

第五章 創(chuàng)建高性能的索引 & 索引與算法

B+樹(shù)索引在數(shù)據(jù)庫(kù)中有一個(gè)特點(diǎn)是高扇出性，因此在數(shù)據(jù)庫(kù)中勇凭，B+樹(shù)的高度一般都在2~4層疚膊，這也就是說(shuō)查找某一鍵值的行記錄時(shí)最多只需要2到4次IO。

數(shù)據(jù)庫(kù)中的B+樹(shù)索引可以分為聚集索引和輔助索引虾标。聚集索引的葉子結(jié)點(diǎn)存放的是一整行記錄寓盗，而輔助索引葉子結(jié)點(diǎn)存放的是主鍵值。

許多數(shù)據(jù)庫(kù)的文檔這樣告訴讀者：聚集索引按照順序物理地存儲(chǔ)數(shù)據(jù)璧函。但是試想一下傀蚌，如果聚集索引必須按照特定順序存放物理記錄，則維護(hù)成本顯得非常之高蘸吓。所以善炫，聚集索引的存儲(chǔ)并不是物理上連續(xù)的，而是邏輯上連續(xù)的库继。這其中有兩點(diǎn)：一是前面說(shuō)過(guò)的頁(yè)通過(guò)雙向鏈表連接箩艺，頁(yè)按照主鍵的順序排序；另一點(diǎn)是每個(gè)頁(yè)中的記錄也是通過(guò)雙向鏈表進(jìn)行維護(hù)的宪萄，物理存儲(chǔ)上可以同樣不按照主鍵存儲(chǔ)艺谆。（《MySQL技術(shù)內(nèi)幕 InnoDB存儲(chǔ)引擎》）

InnoDB只聚集在同一個(gè)頁(yè)面中數(shù)據(jù)，包含相鄰鍵值的頁(yè)面可能相距甚遠(yuǎn)拜英。（高性能MySQL）

索引可以包含一個(gè)或多個(gè)列的值静汤。如果索引包含多個(gè)列，那么列的順序也十分重要聊记，因?yàn)镸ySQL只能高效地使用索引的最左前綴列。創(chuàng)建一個(gè)包含兩個(gè)列的索引恢暖，和創(chuàng)建兩個(gè)只包含一列的索引是大不相同的排监。

索引的類型

B-Tree索引

B+樹(shù)，所有葉子節(jié)點(diǎn)在同一層杰捂，每一個(gè)葉子節(jié)點(diǎn)包含指向下一個(gè)葉子結(jié)點(diǎn)的指針舆床。

B-Tree對(duì)索引列是順序組織存儲(chǔ)的，所以很適合查找范圍數(shù)據(jù)。

其中挨队，索引對(duì)多個(gè)值進(jìn)行排序的順序是與定義索引時(shí)列的順序一致的谷暮。

B-Tree索引適用于全鍵值、鍵值范圍或鍵前綴查找盛垦。其中鍵前綴查找只適用于根據(jù)最左前綴的查找湿弦。

• 全字匹配：和索引中的所有列進(jìn)行匹配，如查找姓名為Cuba Allen腾夯、出生于1960-01-01的人颊埃；
• 匹配最左前綴：即只使用索引的第一列，如查找所有姓為Allen的人蝶俱；
• 匹配列前綴：匹配某一列的值的開(kāi)頭部分班利，如查找所有以J開(kāi)頭的姓的人。這里只使用了索引的第一列榨呆；
• 匹配范圍值：如查找姓在Allen和Barrymore之間的人罗标。這里也只使用了索引的第一列；
• 精確匹配某一列并范圍匹配另一列：如查找所有姓為Allen积蜻，并且名字是字母K開(kāi)頭的人闯割。即第一列全匹配，第二列范圍匹配浅侨；
• 只訪問(wèn)索引的查詢：覆蓋索引纽谒；

如果不是按照索引的最左列開(kāi)始查找，則無(wú)法使用索引如输。例如上面例子中的索引無(wú)法用于查找名字為Bill的人鼓黔。類似的，也無(wú)法查找姓以某個(gè)字母結(jié)尾的人不见。

不能跳過(guò)索引中的列澳化。也就是說(shuō)，上述索引無(wú)法用于查找姓為Smith并且在某個(gè)特定日期出生的人稳吮。

如果查詢中有某個(gè)列的范圍查詢缎谷，則其右邊所有列都無(wú)法使用索引優(yōu)化查找。例如查詢WHERE 姓='Smith' AND 名 LIKE 'J%' AND 出生日期='1976-12-23'灶似，這個(gè)查詢只能使用索引的前兩列列林，因?yàn)檫@里L(fēng)IKE是一個(gè)范圍條件。（如果范圍查詢列值的數(shù)量有限酪惭，那么可以使用多個(gè)等于條件來(lái)代替范圍條件）

到這里讀者應(yīng)該可以明白希痴，前面提到的索引列的順序是多么重要：這些限制都和索引列的順序有關(guān)。在優(yōu)化性能的時(shí)候春感，可能需要使用相同的列但順序不同的索引來(lái)滿足不同類型的查詢需求砌创。

哈希索引

哈希索引基于哈希表實(shí)現(xiàn)虏缸，只有精確匹配索引所有列的查詢才有效。對(duì)于每一行數(shù)據(jù)嫩实，存儲(chǔ)引擎都會(huì)對(duì)所有的列計(jì)算一個(gè)哈希碼刽辙，哈希索引將所有的哈希碼存儲(chǔ)在索引中，同時(shí)保持指向數(shù)據(jù)行的指針甲献。

在MySQL中宰缤，只有Memory引起顯示支持哈希索引。

• 哈希索引數(shù)據(jù)并不是按照索引數(shù)據(jù)順序存儲(chǔ)的竟纳，所以無(wú)法用于排序撵溃；
• 哈希索引頁(yè)不支持部分索引列匹配查找，因?yàn)楣Ｋ饕冀K是使用索引列的全部?jī)?nèi)容來(lái)計(jì)算哈希值的锥累；
• 哈希索引只支持等值比較查詢缘挑，不支持任何范圍查詢；

InnoDB引擎有個(gè)特殊的功能叫做“自適應(yīng)哈希索引”桶略。當(dāng)InnoDB注意到某些索引值被使用得非常頻繁時(shí)语淘，它會(huì)在內(nèi)存中基于B-Tree索引之上再創(chuàng)建一個(gè)哈希索引。這是一個(gè)完全自動(dòng)的际歼、內(nèi)部的行為惶翻。

索引的優(yōu)點(diǎn)

最常見(jiàn)的B-Tree索引，按照順序存儲(chǔ)數(shù)據(jù)鹅心，所以可以用來(lái)做ORDER BY和GROUP BY操作吕粗。因?yàn)閿?shù)據(jù)是有序的，所以B-Tree也就會(huì)將相關(guān)的列值都存儲(chǔ)在一起旭愧。最后颅筋，因?yàn)樗饕写鎯?chǔ)了實(shí)際的列值，所以某些查詢只使用索引就能夠完成查詢输枯。據(jù)此特性议泵，總結(jié)下來(lái)索引有如下三大優(yōu)點(diǎn)：

• 索引大大減少了服務(wù)器需要掃描的數(shù)據(jù)量；
• 索引可以幫助服務(wù)器避免排序和臨時(shí)表桃熄；
• 索引可以將隨機(jī)IO變?yōu)轫樞騃O先口；

評(píng)價(jià)一個(gè)索引是否適合某個(gè)查詢的“三星系統(tǒng)”：

• 索引將相關(guān)的記錄放到一起則獲得一星；
• 索引中的數(shù)據(jù)順序和查找中的排列順序一致則獲得二星瞳收；
• 索引中的列包含了查詢需要的全部列則獲得三星碉京；

高性能的索引策略

獨(dú)立的列

索引列不能是表達(dá)式的一部分，也不能是函數(shù)的參數(shù)螟深，否則不會(huì)使用索引谐宙。

如：SELECT actor_id FROM actor WHERE actor_id + 1 = 5

SELECT ... WHERE TO_DAYS(CURRENT_DATE) - TO_DAYS(date_col) <= 10;

前綴索引和索引選擇性

有時(shí)候需要索引很長(zhǎng)的字符列，這會(huì)讓索引變得大且慢血崭。一個(gè)策略是前面提到過(guò)的模擬哈希索引卧惜。但有時(shí)候這樣做還不夠，還可以做些什么呢夹纫？
通逞蚀桑可以索引開(kāi)始的部分字符，這樣可以大大節(jié)約空間舰讹，從而提高索引效率茅姜。但這樣也會(huì)降低索引的選擇性。索引的選擇性是指月匣，不重復(fù)的索引值和數(shù)據(jù)表的記錄總數(shù)的比值钻洒。索引的選擇性越高則查詢效率越高，因?yàn)榭梢栽诓樵儠r(shí)過(guò)濾掉更多的行锄开。唯一索引的選擇性是1.

訣竅在與既要選擇足夠長(zhǎng)的前綴以保證較高的選擇性素标，同時(shí)又不能太長(zhǎng)，以便節(jié)約空間萍悴。

多列索引

很多人對(duì)多列索引的理解都不夠头遭。一個(gè)常見(jiàn)的錯(cuò)誤就是，為每個(gè)列創(chuàng)建獨(dú)立的索引癣诱，或者按照錯(cuò)誤的順序建立多列索引计维。

對(duì)于如何選擇索引的列順序有一個(gè)經(jīng)驗(yàn)法則：將選擇性最高的列放到索引最前列（在沒(méi)有ORDER BY 或 GROUP BY的情況下）。
例如撕予，在超市的銷(xiāo)售記錄表中：SELECT * FROM payment WHERE staff_id = 2 AND customer_id = 584鲫惶，很自然的customer_id的選擇性更高些，所以多列索引的順序應(yīng)該是(customer_id, staff_id)实抡。

這樣做有一個(gè)地方需要注意欠母，查詢的結(jié)果非常依賴與選定的具體值。例如澜术，一個(gè)應(yīng)用通常都有一個(gè)特殊的管理員賬號(hào)艺蝴，系統(tǒng)中所有其他用戶都是這個(gè)用戶的好友，所以系統(tǒng)通常通過(guò)它向網(wǎng)站的所有其他用戶發(fā)送狀態(tài)和其他消息鸟废。這個(gè)賬號(hào)巨大的好友列表很容易導(dǎo)致網(wǎng)站出現(xiàn)服務(wù)器性能問(wèn)題猜敢。

這實(shí)際上是一個(gè)非常典型的問(wèn)題。任何的異常用戶盒延，不僅僅是那些用于管理應(yīng)用的設(shè)計(jì)糟糕的賬號(hào)會(huì)有同樣的問(wèn)題缩擂；那些擁有大量好友、圖片添寺、狀態(tài)胯盯、收藏的用戶，也會(huì)有前面提到的系統(tǒng)賬號(hào)同樣的問(wèn)題计露。

從這個(gè)小案例可以看到經(jīng)驗(yàn)法則和推論在多數(shù)情況下是有用的博脑，但要注意不要假設(shè)平均情況下的性能也能代表特殊情況下的性能憎乙，特殊情況可能會(huì)摧毀整個(gè)應(yīng)用的性能。

聚簇索引

聚簇索引并不是一種單獨(dú)的索引類型叉趣，而是一種數(shù)據(jù)存儲(chǔ)方式泞边。InnoDB的聚簇索引在同一個(gè)結(jié)構(gòu)中保存了B-Tree索引和數(shù)據(jù)行。

在InnoDB中疗杉，聚簇索引“就是”表阵谚。

當(dāng)表有聚簇索引時(shí)，它的數(shù)據(jù)行實(shí)際上存放在索引的葉子頁(yè)中烟具。術(shù)語(yǔ)“聚簇”表示數(shù)據(jù)行和相鄰的鍵值緊湊的存儲(chǔ)在一起梢什。因?yàn)闊o(wú)法同時(shí)把數(shù)據(jù)行存放在兩個(gè)不同的地方，所以一個(gè)表只能有一個(gè)聚簇索引朝聋。

InnoDB只能通過(guò)主鍵聚集索引嗡午！
如果沒(méi)有定義主鍵，InnoDB會(huì)選擇一個(gè)唯一的非空索引代替冀痕。如果沒(méi)有這樣的索引翼馆，InnoDB會(huì)隱式定義一個(gè)主鍵來(lái)作為聚簇索引。

MySQL中每個(gè)表都有一個(gè)聚簇索引（clustered index ）金度，除此之外的表上的每個(gè)非聚簇索引都是二級(jí)索引应媚，又叫輔助索引（secondary indexes）。

聚簇索引有一些重要的優(yōu)點(diǎn)：

• 可以把相關(guān)的數(shù)據(jù)保存在一起猜极。例如實(shí)現(xiàn)電子郵箱時(shí)中姜，可以根據(jù)用戶ID來(lái)聚集數(shù)據(jù)，這樣只需要從磁盤(pán)讀取少數(shù)的數(shù)據(jù)頁(yè)就能獲取某個(gè)用戶的全部郵件跟伏。如果沒(méi)有使用聚簇索引丢胚，則每封郵件可能都會(huì)導(dǎo)致一次磁盤(pán)I/O。
• 數(shù)據(jù)訪問(wèn)更快受扳。聚簇索引將索引和數(shù)據(jù)保存在同一個(gè)B-Tree中携龟，因此從聚簇索引中獲取數(shù)據(jù)通常比在非聚簇索引中查找要快。
• 使用覆蓋索引掃描的查詢可以直接使用葉節(jié)點(diǎn)中的主鍵值勘高。

同時(shí)峡蟋，聚簇索引也有一些缺點(diǎn)：

• 聚簇索引最大限度提高了I/O密集型應(yīng)用的性能，但如果數(shù)據(jù)全部都放在內(nèi)存中华望，則訪問(wèn)的順序就沒(méi)有那么重要了蕊蝗，聚簇索引也就沒(méi)什么優(yōu)勢(shì)了。
• 插入速度嚴(yán)重依賴插入順序赖舟。按照主鍵的順序插入式加載數(shù)據(jù)到InnoDB表中速度最快的方式蓬戚。但如果不是按照主鍵順序加載數(shù)據(jù)，那么在加載完成后最好使用OPTIMIZE TABLE命令重新組織一下表宾抓。
• 更新聚簇索引列的代價(jià)很高子漩，因?yàn)闀?huì)強(qiáng)制InnoDB將每個(gè)被更新的行移動(dòng)到新的位置豫喧。
• 基于聚簇索引的表在插入新行，或者主鍵被更新導(dǎo)致移動(dòng)行的時(shí)候幢泼，可能面臨葉分裂的問(wèn)題嘿棘。
• 二級(jí)索引訪問(wèn)需要兩次索引查找，而不是一次旭绒。

MyISAM/InnoDB的主鍵索引和二級(jí)索引

MyISAM的主鍵索引和（所有其他的）二級(jí)索引的葉子節(jié)點(diǎn)中保存的都是指向行的物理位置的指針。

InnoDB的主鍵索引的葉子結(jié)點(diǎn)是數(shù)據(jù)行焦人；（所有其他的）二級(jí)索引的葉子節(jié)點(diǎn)中保存的是主鍵值挥吵。
這樣的策略減少了當(dāng)出現(xiàn)行移動(dòng)或數(shù)據(jù)頁(yè)分裂時(shí)二級(jí)索引的維護(hù)工作。使用主鍵值當(dāng)做指針會(huì)讓二級(jí)索引占用更多的空間花椭，換來(lái)的好處是忽匈，InnoDB在移動(dòng)行時(shí)無(wú)需更新二級(jí)索引中的這個(gè)“指針”。

如果正在使用的InnoDB表沒(méi)有什么數(shù)據(jù)需要聚集矿辽，那么可以定義一個(gè)代理鍵作為主鍵丹允，這種主鍵的數(shù)據(jù)應(yīng)該和應(yīng)用無(wú)關(guān)，最簡(jiǎn)單的方法是使用AUTO_INCREMENT自增列袋倔。這樣可以保證數(shù)據(jù)行是按順序?qū)懭氲癖危瑢?duì)于根據(jù)主鍵做關(guān)聯(lián)操作的性能也會(huì)更好。

最好避免隨機(jī)的（不連續(xù)且值的分布范圍非常大）聚簇索引宾娜，特別是對(duì)于I/O密集型的應(yīng)用批狐。例如，從性能的角度考慮前塔，使用UUID來(lái)作為聚簇索引則會(huì)很糟糕：它使得聚簇索引的插入變得完全隨機(jī)嚣艇，這是最壞的情況，使得數(shù)據(jù)沒(méi)有任何聚集特性华弓。

覆蓋索引

如果一個(gè)索引包含（或者說(shuō)覆蓋）所有需要查詢的字段的值食零，就可以使用索引來(lái)直接獲取列的數(shù)據(jù)，這樣就不再需要讀取數(shù)據(jù)行寂屏。我們稱這樣的索引為覆蓋索引贰谣。

例如，表inventory有一個(gè)多列索引（store_id, film_id）迁霎，MySQL如果只需要訪問(wèn)這兩列冈爹，就可以使用這個(gè)索引做覆蓋索引，如SELECT store_id, film_id FROM inventory.

利用索引掃描來(lái)做排序

只有當(dāng)索引的列順序和ORDER BY子句的列順序完全一致欧引，并且所有列的排序方向（倒序或正序）都一樣時(shí)频伤，MySQL才能使用索引來(lái)對(duì)結(jié)果做排序。如果查詢需要關(guān)聯(lián)多張表芝此，則只有當(dāng)ORDER BY子句引用的字段全部為第一個(gè)表時(shí)憋肖，才能使用索引做排序因痛。ORDER BY子句和查找型查詢的限制是一樣的：需要滿足索引的最左前綴的要求。

有一種情況下ORDER BY子句可以不滿足索引的最左前綴的要求岸更，就是前導(dǎo)列為常量的時(shí)候鸵膏。如果WHERE子句或JOIN子句中對(duì)這些列指定了常量虏杰，就可以“彌補(bǔ)”索引的不足陋葡。

例如，索引：UNIQUE KEY idx（rental_date, inventory_id, customer_id）

下面這個(gè)查詢?yōu)樗饕牡谝涣刑峁┝顺Ａ織l件埃仪，而使用第二列進(jìn)行排序评肆，將兩列組合在一起债查，就形成了索引的最左前綴：

WHERE rental_date = '2005-05-25' ORDER BY inventory_id, customer_id DESC;

下面這個(gè)查詢也沒(méi)問(wèn)題，因?yàn)镺RDER BY使用的兩列就是索引的最左前綴：

WHERE rental_date > '2005-05-25' ORDER BY rental_date , inventory_id;

下面是一些不能使用索引做排序的查詢：

• 下面這個(gè)查詢使用了兩種不同的排序方向瓜挽，但是索引列都是正序排序的：
  • WHERE rental_date = '2005-05-25' ORDER BY inventory_id DESC, customer_id ASC;
• 下面這個(gè)查詢的ORDER BY子句中引用了一個(gè)不在索引中的列：
  • WHERE rental_date = '2005-05-25' ORDER BY inventory_id, staff_id;
• 下面這個(gè)查詢的WHERE和ORDER BY中的列無(wú)法組合成索引的最左前綴：
  • WHERE rental_date = '2005-05-25' ORDER BY customer_id ;
• 下面這個(gè)查詢?cè)谒饕械牡谝涣猩鲜欠秶鷹l件盹廷，所以MySQL無(wú)法使用索引的其余列：
  • WHERE rental_date > '2005-05-25' ORDER BY inventory_id, customer_id;

冗余和重復(fù)索引

重復(fù)索引是指在相同的列上按相同的順序創(chuàng)建相同類型的索引。如：

CREATE TABLE test (
    ID INT NOT NULL PRIMARY KEY,
    A INT NOT NULL,
    B INT NOT NULL,
    UNIQUE(ID),
    INDEX(ID)
) ENGINE=InnoDB;

事實(shí)上久橙，MySQL的主鍵約束和唯一約束都是通過(guò)索引實(shí)現(xiàn)的俄占，因此，上面的寫(xiě)法實(shí)際上在相同的列上創(chuàng)建了三個(gè)重復(fù)的索引淆衷。

冗余索引和重復(fù)索引有一些不同缸榄。如果創(chuàng)建了索引（A, B），再創(chuàng)建索引（A）就是冗余索引祝拯，因?yàn)檫@只是前一個(gè)索引的前綴索引碰凶，索引（A, B）也可以當(dāng)做索引（A）來(lái)使用。

大多數(shù)情況下都不需要冗余索引鹿驼，應(yīng)該盡量擴(kuò)展已有的索引而不是創(chuàng)建新索引（如擴(kuò)展索引（A）為（A欲低，B））。但也有時(shí)候出于性能方面的考慮需要冗余索引畜晰，因?yàn)閿U(kuò)展已有的索引會(huì)導(dǎo)致其變大太大砾莱，從而影響其他使用該索引的查詢的性能。

一般來(lái)說(shuō)凄鼻，增加新索引會(huì)導(dǎo)致INSERT腊瑟、UPDATE、DELETE等操作的速度變慢块蚌，特別是當(dāng)新增索引后導(dǎo)致達(dá)到了內(nèi)存瓶頸的時(shí)候闰非。

第六章 鎖

開(kāi)發(fā)多用戶、數(shù)據(jù)庫(kù)驅(qū)動(dòng)的應(yīng)用時(shí)峭范，最大的一個(gè)難點(diǎn)是：一方面要最大程度地利用數(shù)據(jù)庫(kù)的并發(fā)訪問(wèn)财松，另一方面還要確保每個(gè)用戶能以一致的方式讀取和修改數(shù)據(jù)。為此就有了鎖的機(jī)制，同時(shí)這也是數(shù)據(jù)庫(kù)系統(tǒng)區(qū)別于文件系統(tǒng)的一個(gè)關(guān)鍵特性辆毡。

鎖機(jī)制用于管理對(duì)共享資源的并發(fā)訪問(wèn)菜秦。

InnoDB存儲(chǔ)引擎中的鎖

鎖的類型

InnoDB存儲(chǔ)引擎實(shí)現(xiàn)了如下兩種標(biāo)準(zhǔn)的行級(jí)鎖：

• 共享鎖（S Lock），允許事務(wù)讀一行數(shù)據(jù)舶掖；
• 排它鎖（X Lock）球昨，允許事務(wù)刪除或更新一行數(shù)據(jù)。

此外眨攘，InnoDB存儲(chǔ)引擎支持多粒度鎖定主慰，這種鎖允許事務(wù)在行級(jí)上的鎖和表級(jí)上的鎖同時(shí)存在。為了支持在不同粒度上進(jìn)行加鎖操作鲫售，InnoDB存儲(chǔ)引擎支持一種額外的鎖方式共螺，稱之為意向鎖（Intention Lock）。意向鎖是將鎖定的對(duì)象分為多個(gè)層次龟虎，意向鎖意味著事務(wù)希望在更細(xì)粒度上進(jìn)行加鎖。

例如沙庐，如果需要對(duì)記錄r上X鎖鲤妥，那么分別需要對(duì)數(shù)據(jù)庫(kù)A、表拱雏、頁(yè)上意向鎖IX棉安，最后對(duì)記錄r上X鎖。若其中任何一個(gè)步驟導(dǎo)致等待铸抑，那么該操作需要等待粗粒度鎖的完成贡耽。舉例來(lái)說(shuō)，在對(duì)記錄r加X(jué)鎖之前鹊汛，已經(jīng)有事務(wù)對(duì)表1進(jìn)行了S鎖蒲赂，而當(dāng)前事務(wù)需要對(duì)表1上IX鎖，由于不兼容刁憋，所以該事務(wù)需要等待表鎖操作的完成滥嘴。

一致性非鎖定讀

一致性的非鎖定讀（consistent nonlocking read）是指InnoDB存儲(chǔ)引擎通過(guò)行多版本控制的方式來(lái)讀取當(dāng)前時(shí)間數(shù)據(jù)庫(kù)中行的數(shù)據(jù)。如果讀取的行正在執(zhí)行DELETE或UPDATE操作至耻，這是讀取操作不會(huì)因此去等待行上鎖的釋放若皱。相反的，InnoDB存儲(chǔ)引擎會(huì)去讀取行的一個(gè)快照數(shù)據(jù)尘颓。

快照數(shù)據(jù)是指該行的之前版本的數(shù)據(jù)走触，該實(shí)現(xiàn)是通過(guò)undo段來(lái)完成。而undo用來(lái)在事務(wù)中回滾數(shù)據(jù)疤苹，因此快照數(shù)據(jù)本身是沒(méi)有額外的開(kāi)銷(xiāo)互广。此外，讀取快照數(shù)據(jù)是不需要上鎖的卧土，因?yàn)闆](méi)有事務(wù)需要對(duì)歷史的數(shù)據(jù)進(jìn)行修改操作兜辞。

可以看到迎瞧，非鎖定讀機(jī)制極大地提高了數(shù)據(jù)庫(kù)的并發(fā)性。在InnoDB存儲(chǔ)引擎的默認(rèn)設(shè)置下逸吵，這是默認(rèn)的讀取方式凶硅，即讀取不會(huì)占用和等待表上的鎖。

一個(gè)行記錄可能有不止一個(gè)快照數(shù)據(jù)扫皱，一般稱這種技術(shù)為多版本技術(shù)足绅。由此帶來(lái)的并發(fā)控制，稱之為多版本并發(fā)控制（Multi Version Concurrency Control韩脑，MVCC）氢妈。

在READ COMMITTED事務(wù)隔離級(jí)別下，對(duì)于快照數(shù)據(jù)段多，一致性非鎖定讀總是讀取被鎖定行的最新一份快照數(shù)據(jù)（違反了事務(wù)ACID中的I的特性首量，即隔離性）。而在REPEATABLE READ事務(wù)隔離級(jí)別下进苍，一致性非鎖定讀總是讀取事務(wù)開(kāi)始時(shí)的行數(shù)據(jù)版本加缘。

一致性鎖定讀

在某些情況下，用戶需要顯式的對(duì)數(shù)據(jù)庫(kù)讀取操作進(jìn)行加鎖以保證數(shù)據(jù)邏輯的一致性觉啊。而這要求數(shù)據(jù)庫(kù)支持加鎖語(yǔ)句拣宏，即使是對(duì)于SELECT的只讀操作。InnoDB存儲(chǔ)引擎對(duì)于SELECT語(yǔ)句支持兩種一致性的鎖定讀操作：

• SELECT ... FOR UPDATE（X鎖）
• SELECT ... LOCK IN SHARE MODE（S鎖）

鎖的算法

行鎖的3種算法

InnoDB存儲(chǔ)引擎有3種行鎖的算法杠人，分別是：

• Record Lock：?jiǎn)蝹€(gè)行記錄上的鎖勋乾；
• Gap Lock：間隙鎖，鎖定一個(gè)范圍嗡善，但不包含記錄本身辑莫；
• Next-Key Lock：Gap Lock + Record Lock，鎖定一個(gè)范圍罩引，并且鎖定記錄本身摆昧；

InnoDB對(duì)于行的查詢都是采用這種Next-Key Lock鎖定算法。

但查詢的索引含有唯一屬性時(shí)蜒程，InnoDB存儲(chǔ)引擎會(huì)對(duì)Next-Key Lock進(jìn)行優(yōu)化绅你，將其降級(jí)為Record Lock，即僅鎖住記錄本身昭躺，而不是范圍忌锯。如SELECT * FROM t WHERE pk = 5 FOR UPDATE；

解決Phantom Problem

在默認(rèn)的事務(wù)隔離級(jí)別下领炫，即REPEATABLE READ下偶垮，InnoDB存儲(chǔ)引擎采用Next-Key Locking機(jī)制來(lái)避免Phantom Problem（幻讀）。

Phantom Problem是指在同一事務(wù)下，連續(xù)兩次執(zhí)行相同的SQL語(yǔ)句可能會(huì)導(dǎo)致不同的結(jié)果似舵，第二次的SQL語(yǔ)句可能會(huì)返回之前不存在的行（重點(diǎn)在記錄數(shù)不一樣）脚猾。

如下SQL語(yǔ)句：SELECT * FROM t WHERE pk > 2 FOR UPDATE，第一次返回a=5這條記錄砚哗；若這時(shí)另一個(gè)事務(wù)插入了4這個(gè)值龙助，那么第二次執(zhí)行時(shí)將返回4和5.這與第一次得到的結(jié)果不同，違反了事務(wù)的隔離性蛛芥，即當(dāng)前事務(wù)能夠看到其他事務(wù)的結(jié)果提鸟。

InnoDB存儲(chǔ)引擎采用Next-Key Locking的算法避免Phantom Problem。對(duì)于上述SQL語(yǔ)句仅淑，其鎖住的不是5這個(gè)單值称勋，而是對(duì)(2, +∞)這個(gè)范圍加了X鎖。因此任何對(duì)于這個(gè)范圍的插入都是不被允許的涯竟，從而避免Phantom Problem赡鲜。

鎖問(wèn)題

臟讀

所謂臟數(shù)據(jù)是指事務(wù)對(duì)緩沖池中行記錄的修改，并且還沒(méi)有被提交庐船。

臟讀指的就是在不同的事務(wù)下银酬，當(dāng)前事務(wù)可以讀到另外事務(wù)未提交的數(shù)據(jù)，簡(jiǎn)單來(lái)說(shuō)就是可以讀到臟數(shù)據(jù)醉鳖。

臟讀在生產(chǎn)環(huán)境中并不常見(jiàn)捡硅。臟讀發(fā)生的條件是需要事務(wù)的隔離級(jí)別為READ UNCOMMITTED哮内，而目前絕大多數(shù)的數(shù)據(jù)庫(kù)都至少設(shè)置成READ COMMITTED盗棵。

不可重復(fù)讀

不可重復(fù)讀是指在一個(gè)事務(wù)內(nèi)多次讀取同一數(shù)據(jù)集合。在這個(gè)事務(wù)還沒(méi)有結(jié)束時(shí)北发，另外一個(gè)事務(wù)也訪問(wèn)該數(shù)據(jù)集合纹因，并做了一些DML操作。這樣琳拨，由于第二個(gè)事務(wù)的修改瞭恰，第一個(gè)事務(wù)兩次讀到的數(shù)據(jù)可能是不一樣的，這種情況稱為不可重復(fù)讀狱庇。

不可重復(fù)讀和臟讀的區(qū)別是：臟讀是讀到未提交的數(shù)據(jù)惊畏，而不可重復(fù)讀讀到的卻是已經(jīng)提交的數(shù)據(jù)。

一般來(lái)說(shuō)密任，不可重復(fù)讀的問(wèn)題是可接受的颜启，因?yàn)槠渥x到的數(shù)據(jù)是已經(jīng)提交的，本身不會(huì)帶來(lái)很大的問(wèn)題浪讳。因此缰盏，很多數(shù)據(jù)庫(kù)廠商，如Oracle、Microsoft SQL Server將其數(shù)據(jù)庫(kù)事務(wù)的默認(rèn)隔離級(jí)別設(shè)置為READ COMMITTED口猜，在這種隔離級(jí)別下允許不可重復(fù)讀的現(xiàn)象负溪。

幻讀

一個(gè)事務(wù)按相同的查詢條件查詢之前檢索過(guò)的數(shù)據(jù)，確發(fā)現(xiàn)檢索出來(lái)的結(jié)果集條數(shù)變多或者減少（由其他事務(wù)插入济炎、刪除的）川抡，類似產(chǎn)生幻覺(jué)。

注意到冻辩，Repeatable Read（可重復(fù)讀）隔離級(jí)別仍然避免不了幻讀猖腕。

By default, InnoDB operates in REPEATABLE READ transaction isolation level and with the innodb_locks_unsafe_for_binlog system variable disabled. In this case, InnoDB uses next-key locks for searches and index scans, which prevents phantom rows (see Section 13.6.8.5, “Avoidingthe Phantom Problem Using Next-Key Locking”).

13.2.8.5. Avoiding the PhantomProblem Using Next-Key Locking
To prevent phantoms, InnoDB uses an algorithm called next-key locking that combines index-row locking with gap locking.
You can use next-key locking to implement a uniqueness check in your application:If you read your data in share mode and do not see a duplicate for a row you are going to insert, then you can safely insert your row and know that the next-key lock set on the success or of your row during the read prevents anyone mean while inserting a duplicate for your row. Thus, the next-key locking enables you to “l(fā)ock” the nonexistence of something in your table.

我的理解是說(shuō)，InnoDB提供了next-key locks恨闪，但需要應(yīng)用程序自己去（手動(dòng)）加鎖倘感。manual里提供一個(gè)例子：

SELECT * FROM child WHERE id> 100 FOR UPDATE;

這樣，InnoDB會(huì)給id大于100的行（假如child表里有一行id為102）咙咽，以及100-102老玛，102+的gap都加上鎖。
可以使用show engine innodb status來(lái)查看是否給表加上了鎖钧敞。

在InnoDB存儲(chǔ)引擎中蜡豹，通過(guò)使用Next-Key Locking算法來(lái)避免不可重復(fù)讀的問(wèn)題。
——避免了不可重復(fù)讀溉苛，即實(shí)現(xiàn)了可重復(fù)讀镜廉，亦即實(shí)現(xiàn)了事務(wù)的隔離性。

——避免了不可重復(fù)讀愚战，即實(shí)現(xiàn)了可重復(fù)讀娇唯，亦即實(shí)現(xiàn)了事務(wù)的隔離性。

——避免了不可重復(fù)讀寂玲，即實(shí)現(xiàn)了可重復(fù)讀塔插，亦即實(shí)現(xiàn)了事務(wù)的隔離性。

第七章 事務(wù)

事務(wù)（Transaction）是數(shù)據(jù)庫(kù)區(qū)別于文件系統(tǒng)的重要特性之一拓哟。

事務(wù)概述

理論上說(shuō)想许，事務(wù)有著極其嚴(yán)格的定義，它必須同時(shí)滿足四個(gè)特性断序，即通常所說(shuō)的事務(wù)的ACID特性流纹。值得注意的是，雖然理論上定義了嚴(yán)格的事務(wù)要求违诗，但是數(shù)據(jù)庫(kù)廠商出于各種目的漱凝，并沒(méi)有嚴(yán)格去滿足事務(wù)的ACID標(biāo)準(zhǔn)。如Oracle數(shù)據(jù)庫(kù)较雕，其默認(rèn)的事務(wù)隔離級(jí)別是READ COMMITTED碉哑，不滿足隔離性的要求挚币。對(duì)于InnoDB存儲(chǔ)引擎而言，其默認(rèn)的事務(wù)隔離級(jí)別是READ REPEATABLE扣典，完全遵循和滿足事務(wù)的ACID特性妆毕。

A（Atomicity），原子性

原子性指整個(gè)數(shù)據(jù)庫(kù)事務(wù)是不可分割的工作單位贮尖。事務(wù)中的所有數(shù)據(jù)庫(kù)操作要么全部成功笛粘，要么全部撤銷(xiāo)。

C（Consistency）一致性

一致性指事務(wù)將數(shù)據(jù)庫(kù)從一種一致的狀態(tài)轉(zhuǎn)變?yōu)橄乱环N一致的狀態(tài)湿硝。在事務(wù)開(kāi)始之前和結(jié)束之后薪前，數(shù)據(jù)庫(kù)的完整性約束沒(méi)有破壞。

I（Isolation）隔離性

隔離性還有其他的稱呼关斜，如并發(fā)控制示括、可串行化、鎖等痢畜。

事務(wù)的隔離性要求垛膝，事務(wù)提交前對(duì)其他事務(wù)不可見(jiàn)。通常這使用鎖來(lái)實(shí)現(xiàn)丁稀。

D（Durability）吼拥，持久性

事務(wù)一旦提交，其結(jié)果就是永久性的线衫。即使發(fā)生宕機(jī)等事故凿可，數(shù)據(jù)庫(kù)也能將數(shù)據(jù)恢復(fù)。

事務(wù)的實(shí)現(xiàn)

事務(wù)隔離性由鎖來(lái)實(shí)現(xiàn)授账。原子性枯跑、一致性、持久性通過(guò)數(shù)據(jù)庫(kù)的redo log和undo log來(lái)完成矗积。redo log稱為重做日志全肮，用來(lái)保證事務(wù)的原子性和持久性敞咧。undo log用來(lái)保證事務(wù)的一致性棘捣。

redo和undo都可以視為一種恢復(fù)操作，redo恢復(fù)提交事務(wù)修改的頁(yè)操作休建，而undo回滾行記錄到某個(gè)特定版本乍恐。因此兩者記錄的內(nèi)容不同，redo通常是物理日志测砂，記錄的是頁(yè)的物理修改操作茵烈。undo是邏輯日志，根據(jù)每行記錄進(jìn)行記錄砌些。

redo

重做日志用來(lái)實(shí)現(xiàn)事務(wù)的持久性（所以關(guān)于上面原子性的實(shí)現(xiàn)呜投，有待商榷）加匈。其由兩部分組成：一是內(nèi)存中的重做日志緩沖，其是易失的仑荐；二是重做日志文件雕拼，其是持久的。

InnoDB是事務(wù)的存儲(chǔ)引擎粘招，其通過(guò)Force Log at Commit機(jī)制實(shí)現(xiàn)事務(wù)的持久性啥寇，即當(dāng)事務(wù)提交時(shí)，必須先將該事務(wù)的所有日志寫(xiě)入到重做日志文件進(jìn)行持久化洒扎。

為了確保每次日志都寫(xiě)入重做日志文件辑甜，在每次將重做日志緩沖寫(xiě)入重做日志文件后，為了確保日志寫(xiě)入磁盤(pán)（因?yàn)檫@里有一個(gè)文件系統(tǒng)緩存）袍冷，必須進(jìn)行一次fsync操作磷醋。由于fsync的效率取決于磁盤(pán)的性能，因此磁盤(pán)的性能決定了事務(wù)提交的性能胡诗，也就是數(shù)據(jù)庫(kù)的性能子檀。

InnoDB存儲(chǔ)引擎允許用戶手工設(shè)置非持久性的情況發(fā)生，以此提高數(shù)據(jù)庫(kù)的性能乃戈。即當(dāng)事務(wù)提交時(shí)褂痰，日志不寫(xiě)入重做日志文件，而是等待一個(gè)時(shí)間周期后再執(zhí)行fsync操作症虑。這可以顯著提高數(shù)據(jù)庫(kù)的性能缩歪，但是當(dāng)數(shù)據(jù)庫(kù)發(fā)生宕機(jī)時(shí)，由于部分日志文件未寫(xiě)入磁盤(pán)谍憔，因此會(huì)丟失最后一段時(shí)間的事務(wù)匪蝙。

LSN

LSN是Log Sequence Number的縮寫(xiě)，其代表的是日志序列號(hào)习贫。在InnoDB存儲(chǔ)引擎中逛球，LSN占用8字節(jié)，并且單調(diào)遞增苫昌。LSN代表的含義有：

• 重做日志寫(xiě)入的總量
• checkpoint的位置
• 頁(yè)的版本

LSN表示事務(wù)寫(xiě)入重做日志的字節(jié)總量颤绕。例如，當(dāng)前重做日志的LSN是1000祟身，事務(wù)T1寫(xiě)入了100字節(jié)的重做日志奥务，LSN就變成1100，又有事務(wù)T2寫(xiě)入200字節(jié)的重做日志袜硫，那么LSN變成：1300氯葬。可見(jiàn)LSN記錄的是重做日志的總量婉陷，其單位是字節(jié)帚称。

每個(gè)頁(yè)的頭部也有一個(gè)LSN官研，記錄的是該頁(yè)最后刷新時(shí)LSN的大小。重做日志記錄的是每個(gè)頁(yè)的物理更改日志闯睹，因此頁(yè)中的LSN用來(lái)判斷是否需要進(jìn)行恢復(fù)操作阀参。例如：頁(yè)的LSN為10000，數(shù)據(jù)庫(kù)啟動(dòng)時(shí)瞻坝，寫(xiě)入重做日志的LSN為13000蛛壳，表明該事務(wù)已經(jīng)提交，數(shù)據(jù)庫(kù)需要恢復(fù)所刀；重做日志中的LSN小于頁(yè)中的LSN衙荐，不需要進(jìn)行重做，因?yàn)轫?yè)中的LSN表示已經(jīng)刷新到該位置浮创。

恢復(fù)

InnoDB存儲(chǔ)引擎在啟動(dòng)時(shí)不管上次數(shù)據(jù)庫(kù)運(yùn)行時(shí)是否正常關(guān)閉忧吟，都會(huì)嘗試進(jìn)行恢復(fù)操作。

undo

重做日志記錄了事務(wù)的行為斩披，可以很好的通過(guò)其對(duì)頁(yè)進(jìn)行“重做”操作溜族。但是事務(wù)有時(shí)還需要進(jìn)行回滾操作，這時(shí)就需要undo垦沉。如果用戶執(zhí)行的事務(wù)或語(yǔ)句由于某種原因失敗了煌抒，又或者用戶用一條ROLLBACK語(yǔ)句請(qǐng)求回滾，就可以利用這些undo信息將數(shù)據(jù)回滾到修改之前的樣子厕倍。

除了回滾操作寡壮，undo的另一個(gè)作用是MVCC，即在InnoDB存儲(chǔ)引擎中MVCC的實(shí)現(xiàn)是通過(guò)undo來(lái)完成讹弯。當(dāng)用戶讀取一行記錄時(shí)况既，若該記錄已經(jīng)被其他事務(wù)占用，當(dāng)前事務(wù)可以通過(guò)讀取之前的行版本信息组民，以此實(shí)現(xiàn)非鎖定讀棒仍。**

最后也是最為重要的一點(diǎn)是，undo log也會(huì)產(chǎn)生redo log臭胜，也就是undo log的產(chǎn)生會(huì)伴隨著redo log的產(chǎn)生莫其，這是因?yàn)閡ndo log也需要持久性的保護(hù)。

purge

purge用于最終完成delete和update操作庇楞。這樣設(shè)計(jì)是因?yàn)镮nnoDB存儲(chǔ)引擎支持MVCC榜配，所以記錄不能在事務(wù)提交時(shí)立即進(jìn)行處理否纬。這時(shí)其他事務(wù)可能正在引用該行吕晌，故InnoDB存儲(chǔ)引擎需要保存記錄之前的版本。而是否可以刪除該條記錄通過(guò)purge來(lái)進(jìn)行判斷临燃。若該行記錄已不被任何其他事務(wù)引用睛驳，那么就可以進(jìn)行真正的delete操作烙心。

group commit

若事務(wù)為非只讀事務(wù)，則每次事務(wù)提交時(shí)需要進(jìn)行一次fsync操作乏沸，以此保證重做日志都已經(jīng)寫(xiě)入了磁盤(pán)淫茵。然而磁盤(pán)的fsync性能是有限的，為了提高效率蹬跃，當(dāng)前數(shù)據(jù)庫(kù)都提供了group commit功能匙瘪，即一次fsync可以刷新確保多個(gè)事務(wù)日志被寫(xiě)入文件。

（實(shí)現(xiàn)用了隊(duì)列和流水線）

備份與恢復(fù)

備份類型

根據(jù)備份的方法不同：

• Hot Backup（熱備）：在數(shù)據(jù)庫(kù)運(yùn)行中直接備份
• Cold Backup（冷備）：在數(shù)據(jù)庫(kù)停止的情況下備份
• Warm Backup（溫備）：同樣是在數(shù)據(jù)庫(kù)運(yùn)行中進(jìn)行蝶缀，但會(huì)對(duì)當(dāng)前數(shù)據(jù)庫(kù)的操作有所影響丹喻，如加一個(gè)全局讀鎖以保證備份數(shù)據(jù)的一致性

根據(jù)備份后文件的內(nèi)容：

• 邏輯備份：備份出的文件內(nèi)容是可讀的，一般是文本文件翁都，內(nèi)容一般是由一條條SQL語(yǔ)句碍论，或者是表內(nèi)實(shí)際數(shù)據(jù)組成。這類方法的好處是可以觀察導(dǎo)出的文件的內(nèi)容柄慰，一般適用于數(shù)據(jù)庫(kù)的升級(jí)、遷移等工作。但其缺點(diǎn)是恢復(fù)所需要的時(shí)間往往較長(zhǎng)件甥。
• 裸文件備份：復(fù)制數(shù)據(jù)庫(kù)的物理文件漠嵌。這類備份的恢復(fù)時(shí)間往往較邏輯備份短很多。

按照備份數(shù)據(jù)庫(kù)的內(nèi)容來(lái)分：

• 完全備份：對(duì)數(shù)據(jù)庫(kù)進(jìn)行一個(gè)完整的備份
• 增量備份：在上次完全備份的基礎(chǔ)上概行，對(duì)于更改的數(shù)據(jù)進(jìn)行備份
• 日志備份：對(duì)MySQL數(shù)據(jù)庫(kù)二進(jìn)制日志的備份遥倦，通過(guò)對(duì)一個(gè)完全備份進(jìn)行二進(jìn)制日志的重做來(lái)完成數(shù)據(jù)庫(kù)的point-in-time的恢復(fù)工作。

MySQL數(shù)據(jù)庫(kù)復(fù)制（replication）的原理就是異步實(shí)時(shí)地將二進(jìn)制日志傳送并應(yīng)用到從（slave/standby）數(shù)據(jù)庫(kù)占锯。

但是對(duì)于真正的增量備份來(lái)說(shuō)袒哥，只需要記錄當(dāng)前每頁(yè)最后的檢查點(diǎn)的LSN，如果大于之前全備時(shí)的LSN消略，則備份該頁(yè)堡称，否則不用備份，這大大加快了備份的速度和恢復(fù)的時(shí)間艺演。

快照備份

通過(guò)寫(xiě)時(shí)復(fù)制技術(shù)來(lái)創(chuàng)建快照却紧。當(dāng)創(chuàng)建一個(gè)快照時(shí)，僅復(fù)制原始卷中數(shù)據(jù)的元數(shù)據(jù)胎撤，并不會(huì)有數(shù)據(jù)的物理操作晓殊，因此快照的創(chuàng)建過(guò)程是非常快的伤提。當(dāng)快照創(chuàng)建完成巫俺，原始卷上有寫(xiě)操作時(shí)，快照會(huì)跟蹤原始卷塊的改變肿男，將要改變的數(shù)據(jù)在改變之前復(fù)制到快照預(yù)留的空間里介汹，因此這個(gè)原理的實(shí)現(xiàn)叫做寫(xiě)時(shí)復(fù)制却嗡。而對(duì)于快照的讀取操作，如果讀取的數(shù)據(jù)塊是創(chuàng)建快照后沒(méi)有修改過(guò)的嘹承，那么會(huì)將讀操作直接重定向到原始卷上窗价；如果要讀取的是已經(jīng)修改過(guò)的塊，則將讀取保存在快照中該塊在修改之前的數(shù)據(jù)叹卷。因此撼港，采用寫(xiě)時(shí)復(fù)制機(jī)制保證了讀取快照時(shí)得到的數(shù)據(jù)與快照創(chuàng)建時(shí)一致。

B區(qū)塊被修改了骤竹，因此歷史數(shù)據(jù)放入了快照區(qū)域餐胀。讀取快照數(shù)據(jù)時(shí)，A瘤载、C否灾、D塊還是從原有卷中讀取，而B(niǎo)塊就需要從快照讀取了鸣奔。

復(fù)制（replication）

復(fù)制解決的基本問(wèn)題是讓一臺(tái)服務(wù)器的數(shù)據(jù)與其他服務(wù)器保持同步墨技。

MySQL內(nèi)建的復(fù)制功能是構(gòu)建基于MySQL的大規(guī)模、高性能應(yīng)用的基礎(chǔ)挎狸，這類應(yīng)用使用所謂的“水平擴(kuò)展”的架構(gòu)扣汪。我們可以通過(guò)為服務(wù)器配置一個(gè)或多個(gè)備庫(kù)的方式來(lái)進(jìn)行數(shù)據(jù)同步。復(fù)制功能不僅有利于構(gòu)建高性能的應(yīng)用锨匆，同時(shí)也是高可用性崭别、可擴(kuò)展性、災(zāi)難恢復(fù)恐锣、備份以及數(shù)據(jù)倉(cāng)庫(kù)等工作的基礎(chǔ)茅主。事實(shí)上，可擴(kuò)展性和高可用性通常是相關(guān)聯(lián)的話題土榴。

復(fù)制（replication）是MySQL數(shù)據(jù)庫(kù)提供的一種高可用高性能的解決方案诀姚，一般用來(lái)建立大型的應(yīng)用＄枨荩總體來(lái)說(shuō)赫段，replication的工作分為以下3個(gè)步驟：

1. 主服務(wù)器（master）將數(shù)據(jù)更改記錄到二進(jìn)制日志中
2. 從服務(wù)器（slave）把主服務(wù)器的二進(jìn)制日志復(fù)制到自己的中繼日志（relay log）中。
3. 從服務(wù)器重做中繼日志的日志矢赁，把更改應(yīng)用到自己的數(shù)據(jù)庫(kù)上糯笙，以達(dá)到數(shù)據(jù)的最終一致性。

復(fù)制+快照的備份架構(gòu)

復(fù)制可以用來(lái)作為備份撩银，但其功能不僅限于備份给涕，其主要功能如下：

• 數(shù)據(jù)分布。由于MySQL數(shù)據(jù)庫(kù)提供的復(fù)制并不需要很大的帶寬，因此可以在不同的數(shù)據(jù)中心之間實(shí)現(xiàn)數(shù)據(jù)的拷貝稠炬。
• 讀取的負(fù)載均衡焕阿。通過(guò)建立多個(gè)從服務(wù)器咪啡，可將讀取平均地分布到這些從服務(wù)器中首启，從而減少主服務(wù)器的壓力。一般可以通過(guò)DNS的Round-Robin和Linux的LVS功能實(shí)現(xiàn)負(fù)載平衡撤摸。
• 數(shù)據(jù)庫(kù)備份毅桃。復(fù)制對(duì)備份很有幫助，但是從服務(wù)器不是備份准夷，不能完全代替?zhèn)浞荨?/li>
• 高可用性和故障轉(zhuǎn)移钥飞。通過(guò)復(fù)制建立的從服務(wù)器有助于故障轉(zhuǎn)移，減少故障的停機(jī)時(shí)間和恢復(fù)時(shí)間衫嵌。

可見(jiàn)读宙，復(fù)制的設(shè)計(jì)目的不是簡(jiǎn)簡(jiǎn)單單用來(lái)備份的，并且只用復(fù)制來(lái)進(jìn)行備份是遠(yuǎn)遠(yuǎn)不夠的楔绞。

假設(shè)當(dāng)前應(yīng)用采用了主從式的復(fù)制架構(gòu)结闸，從服務(wù)器用來(lái)作為備份，一個(gè)不太有經(jīng)驗(yàn)的DBA執(zhí)行了誤操作酒朵，如DROP DATABASE或者DROP TABLE桦锄，這時(shí)從服務(wù)器也跟著運(yùn)行了，那這時(shí)如何從服務(wù)器進(jìn)行恢復(fù)呢蔫耽？一種比較好的方法是通過(guò)對(duì)從服務(wù)器上的數(shù)據(jù)庫(kù)所在的分區(qū)做快照结耀，以此來(lái)避免復(fù)制對(duì)誤操作的處理能力。當(dāng)主服務(wù)器上發(fā)生誤操作時(shí)匙铡，只需要恢復(fù)從服務(wù)器上的快照图甜，然后再根據(jù)二進(jìn)制日志執(zhí)行point-in-time的恢復(fù)即可。因此鳖眼，快照+復(fù)制的備份架構(gòu)如下圖所示：

發(fā)送復(fù)制事件到其他備庫(kù)

log_slave_updates選項(xiàng)可以讓備庫(kù)變成其他服務(wù)器的主庫(kù)具则。在設(shè)置該選項(xiàng)后，MySQL會(huì)將其執(zhí)行過(guò)的事件記錄到它自己的二進(jìn)制日志中具帮。

為什么要指定服務(wù)器ID博肋，難道MySQL在不知道復(fù)制命令來(lái)源的情況下不能執(zhí)行嗎？為什么MySQL要在意服務(wù)器ID是全局唯一的蜂厅。問(wèn)題的答案在于MySQL在復(fù)制過(guò)程中如何防止無(wú)限循環(huán)匪凡。當(dāng)復(fù)制SQL線程讀中繼日志時(shí)，會(huì)丟棄事件中記錄的服務(wù)器ID和該服務(wù)器ID相同的事件掘猿，從而打破了復(fù)制過(guò)程中的無(wú)限循環(huán)病游。在某些復(fù)制拓?fù)浣Y(jié)構(gòu)下打破無(wú)限循環(huán)非常重要，例如主-主復(fù)制結(jié)構(gòu)。

復(fù)制拓?fù)?/h4>

幾個(gè)基本原則：

1. 一個(gè)備庫(kù)只能有一個(gè)主庫(kù)
2. 每個(gè)備庫(kù)必須有一個(gè)唯一的服務(wù)器ID
3. 一個(gè)主庫(kù)可以有多個(gè)備庫(kù)
4. 如果打開(kāi)了log_slave_updates選項(xiàng)衬衬，一個(gè)備庫(kù)可以把其主庫(kù)上的數(shù)據(jù)變化傳播到其他備庫(kù)

一主庫(kù)多備庫(kù)

這是最簡(jiǎn)單的拓?fù)浣Y(jié)構(gòu)买猖。在有少量寫(xiě)和大量讀時(shí)，這種配置是非常有用的滋尉∮窨兀可以把讀分?jǐn)偟蕉鄠€(gè)備庫(kù)上，直到備庫(kù)給主庫(kù)造成了太大的負(fù)擔(dān)狮惜，或者主備之間的帶寬成為瓶頸為止高诺。

盡管這是非常簡(jiǎn)單的拓?fù)浣Y(jié)構(gòu)，但它非常靈活碾篡，能滿足多種需求：

• 為不同的角色使用不同的備庫(kù)（如添加不同的索引或使用不同的存儲(chǔ)引擎）
• 把一臺(tái)備庫(kù)當(dāng)做待用的主庫(kù)
• 將一臺(tái)備庫(kù)放到遠(yuǎn)程數(shù)據(jù)中心虱而，用作災(zāi)難恢復(fù)
• 使用其中一個(gè)備庫(kù)，作為備份开泽、培訓(xùn)牡拇、開(kāi)發(fā)或者測(cè)試使用服務(wù)器

這種結(jié)構(gòu)流行的原因是它避免了很多其他拓?fù)浣Y(jié)構(gòu)的復(fù)雜性。例如在同一個(gè)邏輯點(diǎn)停止所有備庫(kù)的復(fù)制穆律，它們正在讀取的是主庫(kù)上同一個(gè)日志文件的相同物理位置惠呼。這是個(gè)很好的物理特性，可以減輕管理員的許多工作众旗，例如把備庫(kù)提升為主庫(kù)罢杉。

主動(dòng)-主動(dòng)模式下的主-主復(fù)制

主-主復(fù)制（也叫雙主復(fù)制或雙向復(fù)制）包含兩臺(tái)服務(wù)器，每一個(gè)都被配置成對(duì)方的主庫(kù)和備庫(kù)贡歧，換句話說(shuō)滩租，它們是一對(duì)主庫(kù)。這樣利朵，任何一方所做的變更律想，都會(huì)通過(guò)復(fù)制應(yīng)用到另外一方的數(shù)據(jù)庫(kù)中。

主動(dòng)-主動(dòng)模式下的主-主復(fù)制有一些應(yīng)用場(chǎng)景绍弟，但通常用于特殊的目的技即。一個(gè)可能的應(yīng)用場(chǎng)景是兩個(gè)處于不同地理位置的辦公室，并且都需要一份可寫(xiě)的數(shù)據(jù)拷貝樟遣。

這種配置最大的問(wèn)題是如何解決沖突而叼，兩個(gè)可寫(xiě)的互主服務(wù)器導(dǎo)致的問(wèn)題非常多。例如兩臺(tái)服務(wù)器同時(shí)修改一行記錄豹悬，或同時(shí)在兩臺(tái)服務(wù)器上向一個(gè)包含AUTO_INCREMENT列的表里插入數(shù)據(jù)葵陵。

總的來(lái)說(shuō)，允許向兩臺(tái)服務(wù)器上寫(xiě)入所帶來(lái)的麻煩遠(yuǎn)遠(yuǎn)大于其帶來(lái)的好處瞻佛，但下面描述的主動(dòng)-被動(dòng)模式則會(huì)非常有用脱篙。

主動(dòng)-被動(dòng)模式下的主-主復(fù)制

這和上面的主要區(qū)別在于其中一臺(tái)服務(wù)器是只讀的被動(dòng)服務(wù)器。

這種方式使得反復(fù)切換主動(dòng)和被動(dòng)服務(wù)器非常方便,因?yàn)榉?wù)器是配置是對(duì)稱的。這使得故障轉(zhuǎn)移和故障恢復(fù)很容易绊困。它也可以讓你在不關(guān)閉服務(wù)器的情況下執(zhí)行維護(hù),優(yōu)化表,升級(jí)操作系統(tǒng)（或者應(yīng)用程序文搂、硬件等）或其他任務(wù)。

例如秤朗，執(zhí)行ALTER TABLE操作可能會(huì)鎖住整個(gè)表煤蹭，阻塞對(duì)表的讀和寫(xiě)，則可能會(huì)花費(fèi)很長(zhǎng)時(shí)間并導(dǎo)致服務(wù)中斷川梅。然而在主-主配置下疯兼，可以先停止主動(dòng)服務(wù)器上的備庫(kù)復(fù)制線程（這樣就不會(huì)在被動(dòng)服務(wù)器上執(zhí)行任何更新）然遏，然后在被動(dòng)服務(wù)器上執(zhí)行ALTER操作贫途，交換角色，最后在先前的主動(dòng)服務(wù)器上啟動(dòng)復(fù)制線程待侵。這個(gè)服務(wù)器將會(huì)讀取中繼日志并執(zhí)行相同的ALTER語(yǔ)句丢早。這可能花費(fèi)很長(zhǎng)時(shí)間，但不要緊秧倾，因?yàn)樵摲?wù)器沒(méi)有為任何活躍查詢提供服務(wù)怨酝。

擁有備庫(kù)的主-主結(jié)構(gòu)

這種配置的優(yōu)點(diǎn)是增加了冗余，對(duì)于不同地理位置的復(fù)制拓?fù)淠窍龋軌蛳军c(diǎn)單點(diǎn)失效的問(wèn)題农猬。你也可以像平常一樣，將讀查詢分配到備庫(kù)上售淡。

主庫(kù)斤葱、分發(fā)主庫(kù)以及備庫(kù)

當(dāng)備庫(kù)足夠多時(shí)，會(huì)對(duì)主庫(kù)造成很大的負(fù)擔(dān)揖闸。因此揍堕，如果需要多個(gè)備庫(kù)，一個(gè)好辦法是從主庫(kù)移除負(fù)載并使用分發(fā)主庫(kù)汤纸。分發(fā)主庫(kù)也是一個(gè)備庫(kù)衩茸，它的唯一目的就是提取和提供主庫(kù)的二進(jìn)制日志。多個(gè)備庫(kù)連接到分發(fā)主庫(kù)贮泞，這使原來(lái)的主庫(kù)擺脫了負(fù)擔(dān)楞慈。為了避免在分發(fā)主庫(kù)上做實(shí)際的查詢，可以將它的表修改為blackhole存儲(chǔ)引擎啃擦。

樹(shù)或金字塔形（或稱級(jí)聯(lián)復(fù)制架構(gòu)）

如果正在將主庫(kù)復(fù)制到大量的備庫(kù)中囊蓝，不管是把數(shù)據(jù)分發(fā)到不同的地方，還是提供更高的讀性能议惰，使用金字塔結(jié)構(gòu)都能夠更好地管理慎颗。

這樣設(shè)計(jì)的好處是減輕了主庫(kù)的負(fù)擔(dān)，就像前面提到的分發(fā)主庫(kù)一樣。它的缺點(diǎn)是中間層出現(xiàn)任何錯(cuò)誤都會(huì)影響到多個(gè)服務(wù)器俯萎。如果每個(gè)備庫(kù)和主庫(kù)直接相連就不會(huì)出現(xiàn)這樣的問(wèn)題傲宜。同樣，中間層次越多夫啊，處理故障會(huì)更困難函卒、更復(fù)雜。

定制的復(fù)制方案

選擇性復(fù)制

為了利用訪問(wèn)局部性原理撇眯，并將需要讀的工作集駐留在內(nèi)存中报嵌，可以復(fù)制少量數(shù)據(jù)到備庫(kù)中。如果每個(gè)備庫(kù)只擁有主庫(kù)的一部分?jǐn)?shù)據(jù)熊榛，并且將讀分配給備庫(kù)锚国，就可以更好的利用備庫(kù)的內(nèi)存。并且每個(gè)備庫(kù)也只有主庫(kù)一部分的寫(xiě)入負(fù)載玄坦，這樣主庫(kù)的能力更強(qiáng)并能保證備庫(kù)延遲血筑。

這個(gè)方案有點(diǎn)類似于下面我們會(huì)討論到的水平數(shù)據(jù)劃分，但它的優(yōu)勢(shì)在于主庫(kù)包含了所有的數(shù)據(jù)集煎楣，這意味著無(wú)須為了一條查詢?nèi)ピL問(wèn)多個(gè)服務(wù)器豺总。如果讀操作無(wú)法在備庫(kù)上找到數(shù)據(jù)，還可以通過(guò)主庫(kù)來(lái)查詢择懂。即使不能從備庫(kù)上讀取所有數(shù)據(jù)喻喳，也可以移除大量的主庫(kù)讀負(fù)擔(dān)。

最簡(jiǎn)單的方法是在主庫(kù)上將數(shù)據(jù)劃分到不同的數(shù)據(jù)庫(kù)里困曙，然后將每個(gè)數(shù)據(jù)庫(kù)復(fù)制到不同的備庫(kù)上表伦。

分離功能

許多應(yīng)用都混合了在線事務(wù)處理（OLTP）和在線分析處理（OLAP），OLTP查詢比較短并且是事務(wù)型的赂弓，OLAP查詢則通常很大绑榴，也很慢，并且不要求絕對(duì)最新的數(shù)據(jù)盈魁。這兩種查詢給服務(wù)器帶來(lái)的負(fù)擔(dān)完全不同翔怎，因此它們需要不同的配置，甚至使用不同的存儲(chǔ)引擎或者硬件杨耙。

一個(gè)常見(jiàn)的辦法是將OLTP的數(shù)據(jù)復(fù)制到專門(mén)為OLAP工作準(zhǔn)備的備庫(kù)上赤套。這些備庫(kù)可以有不同的硬件、配置珊膜、索引或者不同的存儲(chǔ)引擎容握。

復(fù)制和容量規(guī)劃

寫(xiě)操作通常是復(fù)制的瓶頸，并且很難通過(guò)復(fù)制來(lái)擴(kuò)展寫(xiě)操作车柠。當(dāng)計(jì)劃為系統(tǒng)增加復(fù)制容量時(shí)剔氏，需要確保進(jìn)行了正確的計(jì)算塑猖，否則很容易犯一些復(fù)制相關(guān)的錯(cuò)誤。

例如谈跛，假設(shè)工作負(fù)載為20%的寫(xiě)以及80%的讀羊苟，服務(wù)器支持每秒1000次查詢，那么應(yīng)該增加多少備庫(kù)才能處理當(dāng)前兩倍的負(fù)載感憾，并將所有的讀查詢分配給備庫(kù)蜡励？四倍呢？

看上去應(yīng)該增加兩個(gè)備庫(kù)并將1600次讀操作平分給它們阻桅，但不要忘記凉倚，主庫(kù)的寫(xiě)操作同樣會(huì)在備庫(kù)上執(zhí)行。400次寫(xiě)操作嫂沉，只剩600次讀操作稽寒，所以需要三臺(tái)備庫(kù)。

四倍負(fù)載時(shí)输瓜，將有800次寫(xiě)入瓦胎，這時(shí)備庫(kù)只有200次讀每秒芬萍，就需要16臺(tái)備庫(kù)來(lái)處理3200次讀查詢尤揣。

這遠(yuǎn)遠(yuǎn)不是線性擴(kuò)展，查詢數(shù)量增加4倍柬祠，卻需要增加17倍的服務(wù)器北戏。這說(shuō)明當(dāng)為單臺(tái)主庫(kù)增加備庫(kù)時(shí)，將很快達(dá)到投入遠(yuǎn)高于回報(bào)的地步漫蛔。

復(fù)制只能擴(kuò)展讀操作嗜愈，無(wú)法擴(kuò)展寫(xiě)操作。對(duì)數(shù)據(jù)進(jìn)行分片是唯一可以擴(kuò)展寫(xiě)操作的方法莽龟。

測(cè)量備庫(kù)延遲

一個(gè)比較普遍的問(wèn)題是如何監(jiān)控備庫(kù)落后主庫(kù)的延遲有多大（SHOW SLAVE STATUS輸出的Seconds_behind_master由于各種原因/缺陷幾乎不可用）蠕嫁。

最好的解決方案是使用heartbeat record（心跳記錄），這是一個(gè)在主庫(kù)上會(huì)每秒更新一次的時(shí)間戳毯盈。為了計(jì)算延時(shí)剃毒，可以直接用備庫(kù)當(dāng)前的時(shí)間戳減去心跳記錄的值。

MySQL二進(jìn)制日志轉(zhuǎn)儲(chǔ)線程并沒(méi)有通過(guò)輪詢的方式從主庫(kù)請(qǐng)求事件搂赋，而是由主庫(kù)來(lái)通知備庫(kù)新的事件赘阀，因?yàn)榍罢叩托揖徛闹鲙?kù)讀取一個(gè)二進(jìn)制日志事件是一個(gè)阻塞型網(wǎng)絡(luò)調(diào)用脑奠，當(dāng)主庫(kù)記錄事件后基公，馬上就開(kāi)始發(fā)送。因此可以說(shuō)宋欺，只要復(fù)制線程被喚醒并且能夠通過(guò)網(wǎng)絡(luò)傳輸數(shù)據(jù)轰豆，事件就會(huì)很快到達(dá)備庫(kù)胰伍。

可擴(kuò)展的MySQL

什么是可擴(kuò)展性

人們常常把諸如“可擴(kuò)展性”、“高可用性”以及性能等術(shù)語(yǔ)在一些非正式場(chǎng)合用作同義詞酸休，但事實(shí)上它們是完全不同的喇辽。

性能：響應(yīng)時(shí)間
可擴(kuò)展性：當(dāng)增加資源以處理負(fù)載和增加容量時(shí)系統(tǒng)能夠獲得的投資產(chǎn)出率

Scale Up：向上擴(kuò)展/垂直擴(kuò)展，購(gòu)買(mǎi)更多強(qiáng)悍的硬件雨席，以增加已有服務(wù)器的性能菩咨；
Scale Out：向外擴(kuò)展/水平擴(kuò)展，將任務(wù)分配到多臺(tái)計(jì)算機(jī)上陡厘；

向外擴(kuò)展/水平擴(kuò)展

最簡(jiǎn)單也最常見(jiàn)的向外擴(kuò)展的方法是通過(guò)復(fù)制將數(shù)據(jù)分發(fā)到多個(gè)服務(wù)器上抽米，然后將備庫(kù)用于讀查詢。這種技術(shù)對(duì)于以讀為主的應(yīng)用很有效糙置。它也有一些缺點(diǎn)云茸，例如重復(fù)緩存。

另外一個(gè)比較常見(jiàn)的向外擴(kuò)展方法是將工作負(fù)載分布到多個(gè)“節(jié)點(diǎn)”谤饭。

在MySQL架構(gòu)中标捺，一個(gè)節(jié)點(diǎn)（node）就是一個(gè)功能部件。如果沒(méi)有規(guī)劃冗余和高可用性揉抵，那么一個(gè)節(jié)點(diǎn)可能就是一臺(tái)服務(wù)器亡容。如果設(shè)計(jì)的是能夠故障轉(zhuǎn)移的冗余系統(tǒng)，那么一個(gè)節(jié)點(diǎn)通吃┙瘢可能是下面的某一種：

• 一個(gè)主-被模式下的主-主復(fù)制雙機(jī)結(jié)構(gòu)
• 一個(gè)組庫(kù)和多個(gè)備庫(kù)
• 一個(gè)主動(dòng)服務(wù)器闺兢，并使用分布式復(fù)制塊設(shè)備（DRBD）作為備用服務(wù)器
• 一個(gè)基于存儲(chǔ)區(qū)域網(wǎng)絡(luò)（SAN）的“集群”

1，按功能拆分

按功能拆分戏罢，或者說(shuō)按職責(zé)拆分屋谭，意味著不同的節(jié)點(diǎn)執(zhí)行不同的任務(wù)。將獨(dú)立的服務(wù)器或節(jié)點(diǎn)分配給不同的應(yīng)用龟糕，這樣每個(gè)節(jié)點(diǎn)只包含它的特定應(yīng)用所需要的數(shù)據(jù)桐磁。

例如，在門(mén)戶網(wǎng)站讲岁，可以瀏覽網(wǎng)站新聞我擂、論壇，尋求支持和訪問(wèn)知識(shí)庫(kù)等催首，這些不同功能區(qū)域的數(shù)據(jù)可以放到專用的MySQL服務(wù)器中扶踊。

歸根結(jié)底，還是不能通過(guò)功能劃分來(lái)無(wú)限地進(jìn)行擴(kuò)展郎任，因?yàn)槿绻粋€(gè)功能區(qū)域被捆綁到單個(gè)MySQL節(jié)點(diǎn)秧耗，就只能進(jìn)行垂直擴(kuò)展。其中的一個(gè)應(yīng)用或者功能區(qū)域最終增長(zhǎng)到非常龐大時(shí)舶治，都會(huì)迫使你去尋求一個(gè)不同的策略分井。如果進(jìn)行了太多的功能劃分车猬，以后就很難采用更具擴(kuò)展性的設(shè)計(jì)了。

2尺锚，數(shù)據(jù)分片

在目前用于擴(kuò)展大型MySQL應(yīng)用的方案中珠闰，數(shù)據(jù)分片是最通用且最成功的的方法。它把數(shù)據(jù)分割成一小片瘫辩，或者說(shuō)一小塊伏嗜，然后存儲(chǔ)到不同的節(jié)點(diǎn)中。

數(shù)據(jù)分片在和某些類型的按功能劃分聯(lián)合使用時(shí)非常有用伐厌。大多數(shù)分片系統(tǒng)也有一些“全局的”數(shù)據(jù)不會(huì)被分片（例如城市列表或者登錄數(shù)據(jù)）承绸。全局?jǐn)?shù)據(jù)一般存儲(chǔ)在單個(gè)節(jié)點(diǎn)上，并且通常保存在類似memcached這樣的緩存里挣轨。

事實(shí)上军熏，大多數(shù)應(yīng)用只會(huì)對(duì)需要的數(shù)據(jù)做分片——通常是那些將會(huì)增長(zhǎng)得非常龐大的數(shù)據(jù)。假設(shè)正在構(gòu)建的博客服務(wù)卷扮，預(yù)計(jì)會(huì)有1000萬(wàn)用戶荡澎，這時(shí)候就無(wú)需對(duì)注冊(cè)用戶進(jìn)行分片，因?yàn)橥耆梢詫⑺械挠脩簦ɑ蛘咂渲械幕钴S用戶）放到內(nèi)存中晤锹。假如用戶數(shù)達(dá)到5億摩幔，那么就可能需要對(duì)用戶數(shù)據(jù)分片。用戶產(chǎn)生的內(nèi)容抖甘、例如發(fā)表的文章和評(píng)論热鞍，幾乎肯定需要進(jìn)行數(shù)據(jù)分片，因?yàn)檫@些數(shù)據(jù)非常龐大衔彻，而且會(huì)越來(lái)越多。

分片技術(shù)和大多數(shù)應(yīng)用的最初設(shè)計(jì)有著顯著的差異偷办，并且很難將應(yīng)用從單一數(shù)據(jù)存儲(chǔ)轉(zhuǎn)換為分片架構(gòu)艰额。如果在應(yīng)用設(shè)計(jì)初期就已經(jīng)預(yù)計(jì)到分片，那實(shí)現(xiàn)起來(lái)就容易得多椒涯。

許多一開(kāi)始沒(méi)有建立分片架構(gòu)的應(yīng)用都會(huì)碰到規(guī)模擴(kuò)大的情形柄沮。例如，可以使用復(fù)制來(lái)擴(kuò)展博客服務(wù)的讀查詢废岂，直到它不再奏效祖搓。然后可以把服務(wù)器劃分為三個(gè)部分：用戶信息、文章湖苞，以及評(píng)論拯欧。可以將這些數(shù)據(jù)放到不同的服務(wù)器上（按功能劃分）财骨。

最后镐作，可以通過(guò)用戶ID來(lái)對(duì)文章和評(píng)論進(jìn)行分片藏姐，而將用戶信息保留在單個(gè)節(jié)點(diǎn)上。

如果事先知道應(yīng)用會(huì)擴(kuò)大到很大的規(guī)模该贾，并且清楚按功能劃分的局限性羔杨，就可以跳過(guò)中間步驟，直接從單個(gè)節(jié)點(diǎn)升級(jí)為分片數(shù)據(jù)存儲(chǔ)杨蛋。

為什么選擇數(shù)據(jù)分片存儲(chǔ)兜材？

因?yàn)槿绻霐U(kuò)展寫(xiě)容量，就必須切分?jǐn)?shù)據(jù)逞力。如果只有單臺(tái)主庫(kù)护姆，那么不管有多少備庫(kù)，寫(xiě)容量都是無(wú)法擴(kuò)展的掏击。對(duì)于上述缺點(diǎn)而言卵皂，數(shù)據(jù)分片是我們的首選解決方案。

3砚亭，選擇分區(qū)鍵

一個(gè)好的分區(qū)鍵常常是數(shù)據(jù)庫(kù)中一個(gè)非常重要的實(shí)體的主鍵灯变。這些鍵值決定了分片單元。例如捅膘，如果通過(guò)用戶ID或客戶端ID來(lái)分割數(shù)據(jù)添祸，分片單元就是用戶或者客戶端。

選擇分區(qū)鍵的時(shí)候寻仗，盡量選擇那些能夠避免跨分片查詢的刃泌，但同時(shí)也要讓分片足夠小，以免過(guò)大的數(shù)據(jù)片導(dǎo)致問(wèn)題署尤。

4耙替，多個(gè)分區(qū)鍵

許多應(yīng)用擁有多個(gè)分區(qū)鍵，換句話說(shuō)，應(yīng)用需要從不同的角度看到有效且連貫的數(shù)據(jù)視圖。這意味著某些數(shù)據(jù)在系統(tǒng)內(nèi)至少需要存儲(chǔ)兩份阴颖。

例如纯衍，需要將博客應(yīng)用的數(shù)據(jù)按照用戶ID和文章ID進(jìn)行分片，因?yàn)檫@兩者都是應(yīng)用查詢數(shù)據(jù)時(shí)使用比較普遍的方式。試想一下這種情形：頻繁的讀取某個(gè)用戶的所有文章，以及某個(gè)文章的所有評(píng)論。如果按照用戶分片就無(wú)法找到某篇文章的所有評(píng)論（需要遍歷所有分片查詢）除抛，而按文章分片則無(wú)法找到某個(gè)用戶的所有文章。

需要多個(gè)分區(qū)鍵并不意味著需要去設(shè)計(jì)兩個(gè)完全冗余的數(shù)據(jù)存儲(chǔ)母截。
例如到忽，假設(shè)為用戶數(shù)據(jù)和書(shū)籍?dāng)?shù)據(jù)都設(shè)計(jì)了分片數(shù)據(jù)存儲(chǔ)。而評(píng)論同時(shí)擁有用戶ID和評(píng)論ID微酬，這樣就跨越了兩個(gè)分片的邊界绘趋。實(shí)際上卻無(wú)需冗余存儲(chǔ)兩份評(píng)論數(shù)據(jù)颤陶。替代方案是，將評(píng)論和用戶數(shù)據(jù)一起存儲(chǔ)陷遮，然后把每個(gè)評(píng)論的標(biāo)題和ID與書(shū)籍?dāng)?shù)據(jù)存儲(chǔ)在一起滓走。這樣在渲染大多數(shù)關(guān)于某本書(shū)的評(píng)論的視圖時(shí)無(wú)須同時(shí)訪問(wèn)用戶和書(shū)籍?dāng)?shù)據(jù)存儲(chǔ)，如果需要顯示完整的評(píng)論內(nèi)容帽馋，可以再?gòu)挠脩魯?shù)據(jù)存儲(chǔ)中獲得搅方。

5，跨分片查詢

大多數(shù)分片應(yīng)用多少都有一些查詢需要對(duì)多個(gè)分片的數(shù)據(jù)進(jìn)行聚合或關(guān)聯(lián)操作绽族。例如姨涡，一個(gè)讀書(shū)俱樂(lè)部網(wǎng)站要顯示最受歡迎或最活躍的用戶，就必須訪問(wèn)每一個(gè)分片吧慢。如何讓這類查詢很好的執(zhí)行涛漂，是實(shí)現(xiàn)數(shù)據(jù)分片的架構(gòu)中最困難的部分。雖然從應(yīng)用的角度來(lái)看检诗，這是一條查詢匈仗，但實(shí)際上需要拆分成多條并行執(zhí)行的查詢，每個(gè)分片上執(zhí)行一條逢慌。

普遍的做法是使用C或Java編寫(xiě)一個(gè)輔助應(yīng)用來(lái)執(zhí)行查詢并聚合結(jié)果集悠轩。也可以借助匯總表來(lái)實(shí)現(xiàn)。

跨分片查詢并不是分片面臨的唯一難題攻泼。維護(hù)數(shù)據(jù)一致性同樣困難火架。外鍵無(wú)法在分片間工作，因此需要由應(yīng)用來(lái)檢查參照一致性忙菠，或者只在分片內(nèi)使用外鍵何鸡，因?yàn)榉制膬?nèi)部一致性可能是最重要的。還可以使用XA事務(wù)只搁，但由于開(kāi)銷(xiāo)太大音比，現(xiàn)實(shí)中使用很少。

6氢惋，分配數(shù)據(jù)、分片和節(jié)點(diǎn)

應(yīng)盡可能的讓分片的大小比節(jié)點(diǎn)容量小很多稽犁，這樣就可以在單個(gè)節(jié)點(diǎn)上存儲(chǔ)多個(gè)分片焰望。

保持分片足夠小更容易管理。這將使數(shù)據(jù)的備份和恢復(fù)更加容易已亥，如果表很小熊赖，那么像更改表結(jié)構(gòu)這樣的操作會(huì)更加容易。例如虑椎，有一個(gè)100GB的表震鹉，你可以直接存儲(chǔ)俱笛，也可以將其劃分成100個(gè)1GB的分片，并存儲(chǔ)在單個(gè)節(jié)點(diǎn)上〈海現(xiàn)在假如要向表上增加一個(gè)索引迎膜，在單個(gè)100GB的表上的執(zhí)行時(shí)間會(huì)比100個(gè)1GB分片上執(zhí)行的總時(shí)間更長(zhǎng)，因?yàn)?GB的分片更容易全部加載到內(nèi)存中浆兰。并且在執(zhí)行ALTER TABLE時(shí)還會(huì)導(dǎo)致數(shù)據(jù)不可用磕仅，阻塞1GB的數(shù)據(jù)比阻塞100GB的數(shù)據(jù)要好得多。

小一點(diǎn)的分片也便于轉(zhuǎn)移簸呈。這有助于重新分配容量榕订，平衡各個(gè)節(jié)點(diǎn)的分片。

8蜕便，固定分配

將數(shù)據(jù)分配到分片中有兩種主要的方法：固定分配和動(dòng)態(tài)分配劫恒。兩種方法都需要一個(gè)分區(qū)函數(shù)，使用行的分區(qū)鍵作為輸入轿腺，返回存儲(chǔ)該行的分片两嘴。

固定分配使用的分區(qū)函數(shù)僅僅依賴于分區(qū)鍵的值。哈希函數(shù)和取模運(yùn)算就是很好的例子吃溅。

9溶诞，動(dòng)態(tài)分配

假設(shè)有一個(gè)表，包括用戶ID和分片ID：

CREATE TABLE user_to_shard (
    user_id INT NOT NULL,
    shard_id INT NOT NULL,
    PRIMARY KEY (user_id)
);

這個(gè)表本身就是分區(qū)函數(shù)决侈。

負(fù)載均衡

負(fù)載均衡的基本思路很簡(jiǎn)單：在一個(gè)服務(wù)器集群中盡可能地平均負(fù)載量螺垢。通常的做法是在服務(wù)器前端設(shè)置一個(gè)負(fù)載均衡器。然后負(fù)載均衡器負(fù)責(zé)將請(qǐng)求路由到最空閑的服務(wù)器赖歌。

在與MySQL相關(guān)的領(lǐng)域里枉圃，負(fù)載均衡架構(gòu)通常和數(shù)據(jù)分片及復(fù)制緊密相關(guān)。例如庐冯，可以在MySQL Cluster集群的多個(gè)SQL節(jié)點(diǎn)上做負(fù)載均衡孽亲，也可以在多個(gè)數(shù)據(jù)中心間做負(fù)載均衡，其中每個(gè)數(shù)據(jù)中心又可以使用數(shù)據(jù)分片架構(gòu)展父，每個(gè)節(jié)點(diǎn)實(shí)際上是擁有多個(gè)備庫(kù)的主-主復(fù)制對(duì)結(jié)構(gòu)返劲，這里又可以做負(fù)載均衡。

復(fù)制上的讀寫(xiě)分離

MySQL復(fù)制產(chǎn)生了多個(gè)數(shù)據(jù)副本栖茉，你可以選擇在備庫(kù)還是主庫(kù)上執(zhí)行查詢篮绿。由于備庫(kù)復(fù)制是異步的，因此主要的難點(diǎn)是如何處理備庫(kù)上的臟數(shù)據(jù)（主庫(kù)已修改吕漂，而備庫(kù)沒(méi)有來(lái)得及同步更新的數(shù)據(jù)）亲配。

如果不太關(guān)心數(shù)據(jù)是否是臟的，可以使用備庫(kù)，而對(duì)需要即時(shí)數(shù)據(jù)的請(qǐng)求則使用主庫(kù)吼虎。我們將這稱為讀寫(xiě)分離犬钢。

常見(jiàn)的讀寫(xiě)分離方法如下：

• 基于查詢分離
  • 將所有不能容忍臟數(shù)據(jù)的讀和寫(xiě)查詢分配到主庫(kù)或主動(dòng)服務(wù)器上，其他的讀查詢分配到備庫(kù)或被動(dòng)服務(wù)器上思灰。（然而只有很少的查詢能夠容忍臟數(shù)據(jù)）
• 基于臟數(shù)據(jù)分離
  • 讓?xiě)?yīng)用檢查復(fù)制延遲玷犹，以確定備庫(kù)數(shù)據(jù)是否太舊
• 基于會(huì)話分離
  • 判斷用戶是否修改了數(shù)據(jù)。用戶不需要看到其他用戶的最新數(shù)據(jù)官辈，但需要看到自己的更新箱舞。可以在會(huì)話層設(shè)置一個(gè)標(biāo)志位拳亿，標(biāo)記是否做了更新晴股，若是則將該用戶的查詢?cè)谝欢螘r(shí)間內(nèi)總是指向主庫(kù)。這是我們通常推薦的策略肺魁，因?yàn)樗窃诤?jiǎn)單和有效之間的一種很好的妥協(xié)电湘。如果有足夠的想象力，可以把基于會(huì)話的分離方法和復(fù)制延遲監(jiān)控結(jié)合起來(lái)鹅经。如果用戶在10秒前更新了數(shù)據(jù)寂呛，而所有備庫(kù)的延遲在5秒內(nèi)，就可以安全地從備庫(kù)中讀取數(shù)據(jù)瘾晃。
• 基于版本分離
  • 這和基于會(huì)話的分離方法相似：你可以跟蹤對(duì)象的版本號(hào)或時(shí)間戳贷痪，通過(guò)比較從備庫(kù)讀取的對(duì)象版本或時(shí)間戳來(lái)判斷數(shù)據(jù)是否足夠新。例如蹦误，用戶發(fā)表了一篇文章后劫拢，可以更新用戶的版本，這樣就會(huì)從主庫(kù)去讀取數(shù)據(jù)了强胰。

大多數(shù)讀寫(xiě)分離解決方案都需要監(jiān)控復(fù)制延遲來(lái)決策讀查詢的分配舱沧。