Schema與數(shù)據(jù)類型優(yōu)化

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

有幾個(gè)簡(jiǎn)單的原則：

• 更小的通常更好
  一般情況下使用可以正確存儲(chǔ)數(shù)據(jù)的最小數(shù)據(jù)類型。
• 簡(jiǎn)單的更好
  例如整型比字符操作代價(jià)更低。應(yīng)當(dāng)使用Mysql的日期類型而不是字符串，應(yīng)當(dāng)用整型存儲(chǔ)IP地址
• 盡量避免NULL
  查詢中如果包含NULL的列，對(duì)于Mysql來(lái)說(shuō)更難優(yōu)化，這樣使得索引，索引統(tǒng)計(jì)，值都比較復(fù)雜。NULL的列會(huì)使用更多的存儲(chǔ)空間，在Mysql里也需要特殊處理。當(dāng)可為NULL的列被索引時(shí)，每個(gè)索引記錄需要一個(gè)額外的字節(jié)

整數(shù)類型
無(wú)符號(hào)的數(shù)字上限可以提高一倍
為整數(shù)類型指定寬度，如INT（11），不會(huì)限制值的合法范圍，只是規(guī)定了Mysql的一些交互工具（命令行或客戶端）用來(lái)顯示的字符個(gè)數(shù)。對(duì)于存儲(chǔ)和計(jì)算來(lái)講，int(1) 和 int(20)是相同的。

實(shí)數(shù)類型
浮點(diǎn)類型在存儲(chǔ)同樣范圍的值時(shí)，通常比Decimal使用更少的空間，F(xiàn)loat使用4個(gè)字節(jié)，Double使用8個(gè)字節(jié)相比Float有更高的精度和更大的范圍。這里能選擇的是存儲(chǔ)類型，Mysql內(nèi)部使用Double作為內(nèi)部浮點(diǎn)計(jì)算的類型。

字符串類型
CHAR和VARCHAR
VARCHAR節(jié)省了存儲(chǔ)空間，如果行占用存儲(chǔ)空間增長(zhǎng)，并且在頁(yè)內(nèi)沒(méi)有更多的空間存儲(chǔ)，MyISAM拆成不同的片段存儲(chǔ)，InnoDB則需要分裂頁(yè)來(lái)使行可以放進(jìn)頁(yè)內(nèi)。
下列情況使用Varchar是合適的：

• 字符串最大長(zhǎng)度比平均長(zhǎng)度大很多；
• 列的更新很少，所以碎片不是問(wèn)題；
• 使用了UTF-8字符集，每個(gè)字符都使用不同的字節(jié)數(shù)進(jìn)行存儲(chǔ)。

InnoDB把過(guò)長(zhǎng)的VARCHAR存儲(chǔ)為BLOB
CHAR是定長(zhǎng)的，會(huì)刪除末尾的空格。CHAR(1)需要一個(gè)字節(jié)，VARCHAR(1)需要2個(gè)字節(jié)，因?yàn)檫€需要多一個(gè)字節(jié)存儲(chǔ)長(zhǎng)度。
類似的還有BINNARY和VARBINARY，填充使用的\0（0字節(jié)）

BLOB和TEXT
都是為了存儲(chǔ)很大的數(shù)據(jù)設(shè)計(jì)的字符串?dāng)?shù)據(jù)類型，分別采用二進(jìn)制和字符方式存儲(chǔ)。不同在于BLOB存儲(chǔ)的是二進(jìn)制數(shù)據(jù)，沒(méi)有排序規(guī)則或者字符集。
排序也只是對(duì)每個(gè)列的max_sort_length字節(jié)而不是整個(gè)字符串排序。
查詢?nèi)绻婕癇LOB，服務(wù)器不能在內(nèi)存臨時(shí)表中存儲(chǔ)BLOB，必須要使用磁盤(pán)臨時(shí)表，無(wú)論它多小。

日期和時(shí)間類型
DATETIME可以存儲(chǔ)1001到9999年，精度為秒，與時(shí)區(qū)無(wú)關(guān)，使用8個(gè)字節(jié)的存儲(chǔ)空間。TIMESTAMP保存了1970年1月1日以來(lái)的秒數(shù)。只使用4個(gè)字節(jié)的存儲(chǔ)空間。從1970到2038年。

位數(shù)據(jù)類型
這些類型，不管底層存儲(chǔ)格式和處理方式如何，從技術(shù)上來(lái)說(shuō)都是字符串類型。
BIT
5.0之前BIT是TINYINT的同義詞。之后則完全不同。MyISAM會(huì)打包所有的BIT列，InnoDB和Memory使用足夠存儲(chǔ)最小整數(shù)類型來(lái)存放BIT，所以不能節(jié)省存儲(chǔ)空間。Mysql把BIT當(dāng)作字符串類型而不是數(shù)字，會(huì)造成一些混亂。例如 a bit(8)，值為b'00111001'二進(jìn)制等于57（ascii顯示值等于9），a=9，a+0=57。應(yīng)該謹(jǐn)慎使用，如果想存儲(chǔ)true/false，可以使用CHAR(0)

選擇標(biāo)識(shí)符（identifier）
整數(shù)類型是最好的選擇，很快并且可以使用AUTO_INCREMENT。避免使用字符串作為標(biāo)識(shí)列，很耗空間，通常比數(shù)字類型慢，MyISAM默認(rèn)對(duì)字符串使用壓縮索引，會(huì)導(dǎo)致查詢慢很多。

• 隨機(jī)值如MD5，SHA1，UUID會(huì)導(dǎo)致INSERT和一些SELECT語(yǔ)句變慢，因?yàn)榭赡軐?dǎo)致隨機(jī)寫(xiě)入索引不同位置，導(dǎo)致頁(yè)分裂，磁盤(pán)隨機(jī)訪問(wèn)，對(duì)于聚簇存儲(chǔ)引擎產(chǎn)生聚簇索引碎片。
• SELECT語(yǔ)句變慢因?yàn)檫壿嬌舷噜彽男袝?huì)分布在磁盤(pán)和內(nèi)存的不同地方。
• 隨機(jī)值導(dǎo)致緩存對(duì)所有類型的查詢語(yǔ)句效果都很差。

Scheme設(shè)計(jì)中的陷阱

• 太多的列
  Mysql的存儲(chǔ)引擎API工作時(shí)需要在服務(wù)器層和存儲(chǔ)引擎層之間通過(guò)行緩沖格式拷貝數(shù)據(jù)，然后在服務(wù)器層將緩沖內(nèi)容解碼成各個(gè)列。從行緩沖中將編碼過(guò)的列轉(zhuǎn)換成行數(shù)據(jù)結(jié)構(gòu)的操作代價(jià)是非常高的。非常寬的表可能會(huì)使得CPU占用非常高。
• 太多的關(guān)聯(lián)
  “實(shí)體-屬性-值”（EAV）設(shè)計(jì)模式在Mysql下不能靠譜的工作，限制了每個(gè)關(guān)聯(lián)操作最多只能有61張表。單個(gè)查詢最好在12個(gè)表內(nèi)做關(guān)聯(lián)。
• 全能的枚舉
  枚舉列表增加數(shù)據(jù)需要使用到ALTER TABLE，若不是加在最后可能會(huì)有影響
• 變相的枚舉

范式和反范式
在范式化的數(shù)據(jù)庫(kù)中，每個(gè)事實(shí)數(shù)據(jù)會(huì)出現(xiàn)并且只出現(xiàn)一次，相反，在反范式化的數(shù)據(jù)庫(kù)中，信息是冗余的。

1. 第一范式
   確保數(shù)據(jù)表中每列（字段）的原子性。
   如果數(shù)據(jù)表中每個(gè)字段都是不可再分的最小數(shù)據(jù)單元，則滿足第一范式。
   例如：user用戶表，包含字段id,username,password

2. 第二范式
   在第一范式的基礎(chǔ)上更進(jìn)一步，目標(biāo)是確保表中的每列都和主鍵相關(guān)。
   如果一個(gè)關(guān)系滿足第一范式，并且除了主鍵之外的其他列，都依賴于該主鍵，則滿足第二范式。
   例如：一個(gè)用戶只有一種角色，而一個(gè)角色對(duì)應(yīng)多個(gè)用戶。則可以按如下方式建立數(shù)據(jù)表關(guān)系，使其滿足第二范式。
   user用戶表，字段id,username,password,role_id
   role角色表，字段id,name
   用戶表通過(guò)角色id（role_id）來(lái)關(guān)聯(lián)角色表

3. 第三范式
   在第二范式的基礎(chǔ)上更進(jìn)一步，目標(biāo)是確保表中的列都和主鍵直接相關(guān)，而不是間接相關(guān)。
   例如：一個(gè)用戶可以對(duì)應(yīng)多個(gè)角色，一個(gè)角色也可以對(duì)應(yīng)多個(gè)用戶。則可以按如下方式建立數(shù)據(jù)表關(guān)系，使其滿足第三范式。
   user用戶表，字段id,username,password
   role角色表，字段id,name
   user_role用戶-角色中間表，id,user_id,role_id
   像這樣，通過(guò)第三張表（中間表）來(lái)建立用戶表和角色表之間的關(guān)系，同時(shí)又符合范式化的原則，就可以稱為第三范式。

4. 反范式化
   反范式化指的是通過(guò)增加冗余或重復(fù)的數(shù)據(jù)來(lái)提高數(shù)據(jù)庫(kù)的讀性能。
   例如：在上例中的user_role用戶-角色中間表增加字段role_name。
   反范式化可以減少關(guān)聯(lián)查詢時(shí)，join表的次數(shù)。

范式的優(yōu)點(diǎn)

• 范式化的更新操作更快
• 更新需要變更的數(shù)據(jù)更少
• 表比較小，可以更好放在內(nèi)存里

缺點(diǎn)是通常需要關(guān)聯(lián)，代價(jià)相對(duì)昂貴，也可能使得一些索引策略無(wú)效。

反范式的優(yōu)點(diǎn)
避免關(guān)聯(lián)
查詢相對(duì)高效（當(dāng)索引合理）

創(chuàng)建高性能索引

索引可以包含一個(gè)或多個(gè)列，如果索引包含多個(gè)列，那列的順序也十分重要，因?yàn)镸ysql只能最高效的使用索引的最左前綴列。

B-Tree的索引列是順序組織存儲(chǔ)的，很適合查找范圍數(shù)據(jù)。適用于全鍵值、鍵值范圍或鍵前綴查找。

紅黑樹(shù)是一種含有紅黑結(jié)點(diǎn)并能自平衡的二叉查找樹(shù)。它必須滿足下面性質(zhì)：
性質(zhì)1：每個(gè)節(jié)點(diǎn)要么是黑色，要么是紅色。
性質(zhì)2：根節(jié)點(diǎn)是黑色。
性質(zhì)3：每個(gè)葉子節(jié)點(diǎn)（NIL）是黑色。
性質(zhì)4：每個(gè)紅色結(jié)點(diǎn)的兩個(gè)子結(jié)點(diǎn)一定都是黑色。
性質(zhì)5：任意一結(jié)點(diǎn)到每個(gè)葉子結(jié)點(diǎn)的路徑都包含數(shù)量相同的黑結(jié)點(diǎn)。
從性質(zhì)5又可以推出：
性質(zhì)5.1：如果一個(gè)結(jié)點(diǎn)存在黑子結(jié)點(diǎn)，那么該結(jié)點(diǎn)肯定有兩個(gè)子結(jié)點(diǎn)

哈希索引（hash index）只有精確匹配索引所有列的查詢才有效。只包含哈希值和行指針，不存儲(chǔ)字段值，所以不能避免讀取行。
并不是按照索引值順序存儲(chǔ)，所以無(wú)法用于排序。
也不支持部分索引列匹配查找。只支持等值查詢，不支持范圍查詢。

高性能的索引策略

獨(dú)立的列才能使用到索引，列不能使用操作符或者表達(dá)式
多列索引，當(dāng)使用到多個(gè)單列索引時(shí)，會(huì)進(jìn)行多個(gè)索引的聯(lián)合操作（索引合并）

選擇合適的索引列順序
正確的順序依賴于使用該索引的查詢，并且同時(shí)需要考慮如何更好地滿足排序和分組的需要。
在一個(gè)多列B-Tree索引中，索引列的順序意味著索引首先按照最左列進(jìn)行排序，其次是第二列。

聚簇索引
并非一種單獨(dú)的索引類型，而是一種數(shù)據(jù)存儲(chǔ)方式。InnoDB在同一個(gè)結(jié)構(gòu)中保存了B-Tree索引和數(shù)據(jù)行。
InnoDB使用主鍵聚集數(shù)據(jù)，如果沒(méi)有定義主鍵，會(huì)選擇一個(gè)唯一的非空索引代替，如果沒(méi)有這樣的索引，會(huì)隱式定義一個(gè)主鍵作為聚簇索引。InnoDB只聚集同一個(gè)頁(yè)面的記錄。
優(yōu)點(diǎn)：

• 把相關(guān)數(shù)據(jù)保存再一起。
• 數(shù)據(jù)訪問(wèn)更快
• 使用覆蓋索引掃描的查詢可以直接使用節(jié)點(diǎn)中的主鍵值。

缺點(diǎn)：

• 插入速度依賴于插入順序，如果不是按照主鍵加載數(shù)據(jù)，加載完成后最好使用OPTIMIZE TABLE重新組織表
• 更新聚簇索引的代價(jià)很高，因?yàn)闀?huì)將被更新的行移動(dòng)到新位置
• 插入新航或者主鍵更新需要移動(dòng)行時(shí)，可能面臨“頁(yè)分裂（Page Split）”問(wèn)題
• 可能導(dǎo)致全表掃描變慢，尤其是行比較稀疏
• 二級(jí)索引（非聚簇索引）可能比想象的要更大，因?yàn)槿~子節(jié)點(diǎn)包含了引用行的主鍵列。
• 二級(jí)索引需要兩次索引查找，而不是一次

覆蓋索引
如果索引的葉子節(jié)點(diǎn)中已經(jīng)包含要查詢的數(shù)據(jù)，那么還有什么必要再回表查詢呢？所以一個(gè)索引包含（或者覆蓋）所有需要查詢的字段的值，我們就稱之為覆蓋索引。

索引排序
只有索引的列順序和orderby的順序完全一致，并且列的正序，逆序都一樣時(shí)，才能使用索引對(duì)結(jié)果進(jìn)行排序。如果查詢需要關(guān)聯(lián)多張表，則只有當(dāng)orderby的引用字段全部為第一個(gè)表時(shí)，才能使用索引進(jìn)行排序。

索引和數(shù)據(jù)的碎片化
B-Tree索引可能會(huì)碎片化。
表的數(shù)據(jù)存儲(chǔ)也可能碎片化：
行碎片
這種碎片指的時(shí)數(shù)據(jù)行被存儲(chǔ)到多個(gè)地方的多個(gè)片段中。即使只查詢一行記錄，也會(huì)導(dǎo)致性能下降。
行間碎片
邏輯上順序的頁(yè)，或者行再磁盤(pán)上不是順序存儲(chǔ)的。行間碎片對(duì)諸如全表掃描和聚簇索引掃描之類的操作有很大影響。
剩余空間碎片
指數(shù)據(jù)頁(yè)中有大量的空余空間，會(huì)導(dǎo)致服務(wù)器讀取大量不需要的數(shù)據(jù)造成浪費(fèi)。

查詢性能優(yōu)化

查詢的聲明周期大致按照順序：
從客戶端，到服務(wù)器，然后在服務(wù)器上進(jìn)行解析，生成執(zhí)行計(jì)劃，執(zhí)行，并返回結(jié)果給客戶端。執(zhí)行時(shí)最重要的階段，包含了大量為檢索數(shù)據(jù)到存儲(chǔ)引擎的調(diào)用以及調(diào)用后的數(shù)據(jù)處理，包括排序，分組等。

慢查詢基礎(chǔ)：優(yōu)化數(shù)據(jù)訪問(wèn)

是否請(qǐng)求了不需要的數(shù)據(jù)

• 查詢不需要的記錄
• 查詢不需要的列 （多表關(guān)聯(lián) * ）
• 總是取出全部列（select * ）
• 重復(fù)查詢相同的數(shù)據(jù)

是否在掃描額外的記錄

衡量查詢開(kāi)銷的三個(gè)指標(biāo)如下：

• 響應(yīng)時(shí)間
• 掃描的行數(shù)
• 返回的行數(shù)

響應(yīng)時(shí)間是 服務(wù)時(shí)間 和 排隊(duì)時(shí)間 之和。
掃描的行數(shù)和返回的行數(shù)理想情況下應(yīng)該是相同的，一般在1:1到10:1之間
掃描的行數(shù)和訪問(wèn)類型：在EXPAIN語(yǔ)句中的type列反應(yīng)了訪問(wèn)類型。訪問(wèn)類型有很多中，包括全表掃描，索引掃描，范圍掃描，唯一索引查詢，常數(shù)引用等。這些速度是從慢到快，掃描行數(shù)也是從多到少。

重構(gòu)查詢的方式

一個(gè)復(fù)雜查詢還是多個(gè)簡(jiǎn)單查詢

Mysql支持多個(gè)簡(jiǎn)單查詢，一個(gè)通用服務(wù)器上可以支持每秒10萬(wàn)的查詢，一個(gè)千兆網(wǎng)卡滿足每秒2000次的查詢。Mysql內(nèi)部每秒能掃描內(nèi)存中上百萬(wàn)行數(shù)據(jù)，相比之下響應(yīng)數(shù)據(jù)給客戶端就慢得多了

切分查詢

將一個(gè)大查詢分而治之，例如一個(gè)刪除大量數(shù)據(jù)的語(yǔ)句，拆分為多個(gè)小的刪除。

分解關(guān)聯(lián)查詢

有很多好處：

• 讓緩存的效率更高。無(wú)論是應(yīng)用程序的緩存和Mysql的緩存，都會(huì)在單表的情況下更容易命中。
• 查詢分解后減少了鎖的競(jìng)爭(zhēng)
• 應(yīng)用層關(guān)聯(lián)，更容易對(duì)數(shù)據(jù)庫(kù)進(jìn)行拆分，做到高性能和可擴(kuò)展
• 減少冗余記錄的查詢
• 在應(yīng)用中實(shí)現(xiàn)的哈希關(guān)聯(lián)，而不是使用Mysql的嵌套查詢。

執(zhí)行查詢的基礎(chǔ)

執(zhí)行查詢的過(guò)程：

1. 客戶端發(fā)送一條查詢給服務(wù)器
2. 服務(wù)器先檢查緩存，如果命中了緩存，則立刻返回存儲(chǔ)在緩存中的結(jié)果。否則進(jìn)入下一個(gè)階段。
3. 服務(wù)器進(jìn)行SQL解析，預(yù)處理，再由優(yōu)化器生成對(duì)應(yīng)的執(zhí)行計(jì)劃。
4. Mysql根據(jù)優(yōu)化器生成的執(zhí)行計(jì)劃，調(diào)用存儲(chǔ)引擎的API執(zhí)行查詢。
5. 將結(jié)果返回給客戶端

Mysql客戶端/服務(wù)器通信協(xié)議

通信協(xié)議是“半雙工”的，意味著任何一個(gè)時(shí)刻，要么是服務(wù)端向客戶端發(fā)送數(shù)據(jù)，要么是客戶端向服務(wù)端發(fā)送數(shù)據(jù)。這種協(xié)議讓MySQL通信簡(jiǎn)單快速。但是也意味著沒(méi)法進(jìn)行流量控制，一旦一端開(kāi)始發(fā)送消息，另一端要完整接收完整個(gè)消息才能響應(yīng)它?？蛻舳擞靡粋€(gè)單獨(dú)的數(shù)據(jù)包將查詢傳給服務(wù)器，所以查詢語(yǔ)句特別長(zhǎng)的時(shí)候，參數(shù)max_allowed_packet特別重要。

查詢狀態(tài)

最簡(jiǎn)單使用SHOW FULL PROCESSLIST查看當(dāng)前狀態(tài)，狀態(tài)值有如下幾種：

• Sleep：線程正在等待客戶端發(fā)送新的請(qǐng)求。
• Query：線程正在執(zhí)行查詢或者將查詢結(jié)果返回客戶端。
• Locked：服務(wù)器層線程等待表鎖。在存儲(chǔ)引擎基本實(shí)現(xiàn)的鎖，例如InnoDB的行所，不會(huì)體現(xiàn)在線程狀態(tài)中。
• Analyzing and statistics：線程收集存儲(chǔ)引擎的統(tǒng)計(jì)信息，并生成查詢的執(zhí)行計(jì)劃。
• Copying to tmp table [on disk]：線程執(zhí)行查詢，并將其結(jié)果集復(fù)制到一個(gè)臨時(shí)表中，這種狀態(tài)一般要么是做GROUP BY操作，或者文件排序操作，或者UNION操作。如果后面有“on disk”標(biāo)記表示MySQL將內(nèi)存臨時(shí)表放到磁盤(pán)上。
• Sorting result：線程在對(duì)結(jié)果集排序。
• Sending data：線程可能在多個(gè)狀態(tài)之間傳送數(shù)據(jù)，或者在生成結(jié)果集，或者在向客戶端返回?cái)?shù)據(jù)。

查詢緩存

檢查緩存是通過(guò)一個(gè)對(duì)大小寫(xiě)敏感的哈希查找實(shí)現(xiàn)的。查詢和緩存中的查詢即使只有一個(gè)字節(jié)不同頁(yè)不會(huì)匹配，如果命中在返回結(jié)果集之前MySQL會(huì)檢查一次用戶權(quán)限，這是無(wú)需解析SQL的，因?yàn)椴樵兙彺嬷杏斜４娈?dāng)前查詢需要的表信息。

查詢優(yōu)化處理

語(yǔ)法解析器和預(yù)處理

MySQL通過(guò)關(guān)鍵字將SQL語(yǔ)句解析，生成語(yǔ)法解析樹(shù)，使用MySQL語(yǔ)法規(guī)則驗(yàn)證和解析查詢。例如是否使用了錯(cuò)誤的關(guān)鍵字，關(guān)鍵字順序是否正確，引號(hào)前后是否正確匹配。
預(yù)處理根據(jù)MySQL規(guī)則進(jìn)一步檢查解析樹(shù)是否合法。例如數(shù)據(jù)表、列是否存在，名字和別名是否有歧義。
下一步預(yù)處理器會(huì)驗(yàn)證權(quán)限。

查詢優(yōu)化器

語(yǔ)法樹(shù)已經(jīng)合法，優(yōu)化器將其轉(zhuǎn)為了執(zhí)行計(jì)劃。優(yōu)化器作用就是找到最好的執(zhí)行計(jì)劃。
可以通過(guò)查詢當(dāng)前回話的Last_query_cost的值來(lái)得知MySQL計(jì)算當(dāng)前查詢成本。
根據(jù)一系列統(tǒng)計(jì)信息計(jì)算得來(lái)：每個(gè)表或者索引的頁(yè)面?zhèn)€數(shù)，索引的基數(shù)（索引中不同值的數(shù)量），索引和數(shù)據(jù)行的長(zhǎng)度，索引分布的情況。
優(yōu)化器在評(píng)估成本的時(shí)候不考慮任何緩存，假設(shè)讀取任何數(shù)據(jù)都需要一次磁盤(pán)IO

MySQL的查詢優(yōu)化器是一個(gè)復(fù)雜部件，使用了很多優(yōu)化的執(zhí)行策略。優(yōu)化策略簡(jiǎn)單分為兩種：靜態(tài)優(yōu)化和動(dòng)態(tài)優(yōu)化。
靜態(tài)優(yōu)化直接對(duì)解析樹(shù)進(jìn)行優(yōu)化，靜態(tài)優(yōu)化在第一次萬(wàn)能充后就一直有效，使用不同參數(shù)執(zhí)行查詢頁(yè)不會(huì)發(fā)生變化，可以認(rèn)為是一種“編譯時(shí)優(yōu)化”。
動(dòng)態(tài)優(yōu)化和查詢的上下文有關(guān)，例如WHERE條件中的取值、索引中條目對(duì)應(yīng)的數(shù)據(jù)行數(shù)等?？梢哉J(rèn)為時(shí)“運(yùn)行時(shí)優(yōu)化”。
MySQL能夠處理的優(yōu)化類型：

• 重新定義關(guān)聯(lián)表的順序：數(shù)據(jù)表的關(guān)聯(lián)并不總是按照查詢中指定的順序執(zhí)行
• 將外連接轉(zhuǎn)為內(nèi)連接：MySQL識(shí)別并重寫(xiě)查詢，讓其可以調(diào)整關(guān)聯(lián)順序。
• 使用等價(jià)變化規(guī)則：通過(guò)等價(jià)變換來(lái)簡(jiǎn)化并規(guī)范表達(dá)式。合并減少一些比較，一定一些恒等或者恒不等的判斷。
• 優(yōu)化Count() Max() Min()：min和max可以直接查詢b-tree的最左或者最右端。
• 預(yù)估并轉(zhuǎn)化位常數(shù)表達(dá)式：
• 覆蓋索引掃描
• 子查詢優(yōu)化；某些情況下可以將子查詢轉(zhuǎn)換為效率更高的形式
• 提前終止查詢：在發(fā)現(xiàn)已經(jīng)滿足查詢需求的時(shí)候，MySQL總是能夠立刻終止查詢。
• 等值傳播：兩個(gè)列的值通過(guò)等值關(guān)聯(lián)，MySQL能夠傳遞where條件。
• 列表in()的比較：MySQL將in()列表中的數(shù)據(jù)先進(jìn)行排序，然后通過(guò)二分查找的方式來(lái)確定列表中的值是否滿足條件。這是一個(gè)O(log n)的操作。等價(jià)轉(zhuǎn)換為Or的復(fù)雜度時(shí)O(n)。

MySQL執(zhí)行關(guān)聯(lián)查詢

MySQL先從一個(gè)表中循環(huán)取出單條數(shù)據(jù)，在嵌套循環(huán)到下一個(gè)表中尋找匹配的行，依次直到找到所有表中匹配的行，然后根據(jù)各個(gè)表匹配的行返回查詢中需要的各個(gè)列。MySQL會(huì)嘗試在最后一個(gè)關(guān)聯(lián)表中找到所有匹配的行，如果不行就返回上一層次關(guān)聯(lián)表。
MySQL多表關(guān)聯(lián)的指令樹(shù)時(shí)一顆左側(cè)深度優(yōu)先的樹(shù)。

關(guān)聯(lián)查詢優(yōu)化器

MySQL的最優(yōu)執(zhí)行計(jì)劃中的關(guān)聯(lián)表的順序，通過(guò)預(yù)估需要讀取的數(shù)據(jù)頁(yè)來(lái)選擇，讀取的數(shù)據(jù)頁(yè)越少越好。
關(guān)聯(lián)順序的調(diào)整，可能會(huì)讓查詢進(jìn)行更少的嵌套循環(huán)和回溯操作。
可以使用STRAIGHT_JOIN關(guān)鍵字重寫(xiě)查詢，讓優(yōu)化器按照查詢順序執(zhí)行。

排序優(yōu)化

排序時(shí)成本很高的操作，從性能角度考慮，應(yīng)該盡量避免排序，或者避免對(duì)大量數(shù)據(jù)進(jìn)行排序。
當(dāng)不能用索引生成排序結(jié)果時(shí)，MySQL需要字節(jié)進(jìn)行排序，如果數(shù)據(jù)量小使用內(nèi)存，數(shù)據(jù)量大使用磁盤(pán)。不過(guò)統(tǒng)一都稱為文件排序（filesort）。
MySQL有兩種排序算法：

• 兩次傳輸排序（舊版本）：讀取指針和需要排序的字段，排序之后，再根據(jù)排序結(jié)果讀取所需要的數(shù)據(jù)行。第二次讀取數(shù)據(jù)的時(shí)候可能產(chǎn)生大量隨機(jī)IOS，成本很高，不過(guò)在排序時(shí)加載的數(shù)據(jù)較少，所以在內(nèi)存中就可以讀取更多的行數(shù)進(jìn)行排序。
• 單次傳輸排序（新版本）：查詢所有需要列，根據(jù)給定列進(jìn)行排序直接返回結(jié)果。在MySQL4.1之后引入。

查詢執(zhí)行引擎

查詢執(zhí)行階段就根據(jù)執(zhí)行計(jì)劃，調(diào)用存儲(chǔ)引擎的實(shí)現(xiàn)接口來(lái)完成。
查詢結(jié)果返回時(shí)，即使不需要返回結(jié)果集給客戶端，MySQL返回查詢信息，例如影響到的行數(shù)。

查詢優(yōu)化的局限性

關(guān)聯(lián)子查詢（in+子查詢）
使用join，或者使用函數(shù)GROUP_CONCAT()在in中構(gòu)造一個(gè)由分好分隔的列表，有時(shí)候比關(guān)聯(lián)更快，in加子查詢性能糟糕，一般建議使用exists等效改寫(xiě)。

優(yōu)化特定類型的查詢

優(yōu)化count查詢

MyISAM的count函數(shù)非?？?，只有在沒(méi)有條件的前提下。
近似值：某些不需要精確值的情況下，可以使用EXPLAIN出來(lái)的優(yōu)化器估算行數(shù)。

優(yōu)化關(guān)聯(lián)查詢

• 確保on或者using子句中的列上有索引。
• 確保任何的group by和order by中的表達(dá)式只設(shè)計(jì)一個(gè)表中的列，這樣MySQL才有可能使用索引來(lái)優(yōu)化過(guò)程

優(yōu)化子查詢

在5.6之前盡量轉(zhuǎn)換使用join，5.6之后沒(méi)有太多差別

優(yōu)化group by和distinct

groupby 使用主鍵列效率更高。

優(yōu)化limit

“延遲關(guān)聯(lián)”，首先使用索引覆蓋來(lái)選取范圍內(nèi)的主鍵，接下來(lái)根據(jù)這些主鍵獲取對(duì)應(yīng)數(shù)據(jù)。

分區(qū)表

分區(qū)表限制：

• 一個(gè)表最多只能有1024個(gè)分區(qū)
• 5.1中分區(qū)表達(dá)式必須是整數(shù)，或者是返回整數(shù)的表達(dá)式。5.5中某些場(chǎng)景可以直接使用列進(jìn)行分區(qū)。
• 如果分區(qū)字段中有主鍵或者唯一索引列，那么所有的主鍵列和唯一索引列都必須包含進(jìn)來(lái)。
• 分區(qū)表中無(wú)法使用外鍵約束。

在數(shù)據(jù)量超大的時(shí)候B-Tree就無(wú)法起作用了，除非是索引覆蓋查詢，否則數(shù)據(jù)庫(kù)服務(wù)器需要根據(jù)索引掃描的結(jié)果回表，查詢所有符合條件的記錄。如果數(shù)據(jù)量巨大，這將產(chǎn)生大量隨機(jī)IO，數(shù)據(jù)庫(kù)的響應(yīng)時(shí)間將大到不可接受的程度。

MySQL優(yōu)化服務(wù)器配置

MySQL配置的工作原理

MySQL從 命令行參數(shù)和配置文件中獲取配置信息。配置文件一般是在 /etc/my.cnf 或 /etc/mysql/my.cnf。
確認(rèn)配置文件路徑，可以使用下列命令

$ which mysql
/bin/mysql
$/bin/mysql --verbose --help|grep -A 1 'Default options'
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf 

配置文件分為多個(gè)部分，每個(gè)部分的開(kāi)頭是用方括號(hào)括起來(lái)的分段名稱?？蛻舳藭?huì)讀取client部分，服務(wù)器通常讀取mysqld部分。

配置項(xiàng)都使用小鞋，單次之間用下劃線或者橫線隔開(kāi)。

常用變量及其效果

• key_buffer_size
  一次性為鍵緩沖區(qū)（key buffer）分配所有的指定空間。操作系統(tǒng)會(huì)在使用時(shí)才真正分配。
• table_cache_size
  這個(gè)變量會(huì)等到下次有線程打開(kāi)表才有效果，會(huì)變更緩存中表的數(shù)量。
• thread_cache_size
  MySQL只有再關(guān)閉連接時(shí)才在緩存中增加線程，只在創(chuàng)建新連接時(shí)才從緩存中刪除線程。
• query_cache_size
  修改這個(gè)變量會(huì)立刻刪除所有緩存的查詢，重新分配這片緩存到指定大小，并且重新初始化內(nèi)存。
• read_buffer_size
  MySQL只會(huì)在查詢需要使用時(shí)才會(huì)為該緩存分配內(nèi)存，并且一次性分配該參數(shù)指定大小的全部?jī)?nèi)存。
• read_rnd_buffer_size
  MySQL只會(huì)在查詢需要使用時(shí)才會(huì)為該緩存分配內(nèi)存，并且只會(huì)分配該參數(shù)需要大小的內(nèi)存。
• sort_buffer_size
  MySQL只會(huì)在查詢排序需要使用時(shí)才會(huì)為該緩存分配內(nèi)存，并且一次性分配該參數(shù)指定大小的全部?jī)?nèi)存，不管排序是否需要這想·么大的內(nèi)存。

InnoDB事務(wù)日志

InnoDB使用日志來(lái)減少提交事務(wù)時(shí)的開(kāi)銷。因?yàn)槿罩局幸呀?jīng)記錄了事務(wù)，無(wú)需在每個(gè)事務(wù)提交時(shí)把緩沖池的臟塊刷新到磁盤(pán)中。
InnoDB用日志把隨機(jī)IO變成順序IO，一旦日志寫(xiě)入磁盤(pán)，事務(wù)就持久化了，即使變更還沒(méi)有寫(xiě)到數(shù)據(jù)文件。
InnoDB最后是要把變更寫(xiě)入數(shù)據(jù)文件，日志有固定大小。InnoDB的日志是環(huán)形方式寫(xiě)的：當(dāng)寫(xiě)到日志的尾部，會(huì)重新跳轉(zhuǎn)到開(kāi)頭繼續(xù)寫(xiě)，但不會(huì)覆蓋到還沒(méi)應(yīng)用到數(shù)據(jù)文件的日志記錄，因?yàn)檫@樣會(huì)清掉已經(jīng)提交事務(wù)的唯一持久化記錄。
InnoDB使用一個(gè)后臺(tái)線程只能地刷新這些變更到數(shù)據(jù)文件。這個(gè)線程可以批量組合寫(xiě)入，是的數(shù)據(jù)寫(xiě)入更順序，以提高效率。事務(wù)日志把數(shù)據(jù)文件的隨機(jī)IO轉(zhuǎn)換為幾乎順序的日志文件和數(shù)據(jù)文件IO，把刷新操作轉(zhuǎn)移到后臺(tái)使得查詢可以更快完成，并且緩和查詢高峰時(shí)IO的壓力。

InnoDB表空間

InnoDB把數(shù)據(jù)保存在表空間內(nèi)，本質(zhì)上是一個(gè)由一或多個(gè)磁盤(pán)文件組成的虛擬文件系統(tǒng)。InnoDB用表空間實(shí)現(xiàn)很多功能，不只是存儲(chǔ)表和索引。它還保存了回滾日志（舊版本號(hào)），插入緩沖（Insert Buffer）、雙寫(xiě)緩沖（Doublewrite Buffer），以及其他內(nèi)部數(shù)據(jù)結(jié)構(gòu)。
InnoDB使用雙寫(xiě)緩沖來(lái)避免頁(yè)沒(méi)寫(xiě)完整鎖導(dǎo)致的數(shù)據(jù)損壞。這是一個(gè)特殊的保留區(qū)域，再一些連續(xù)的塊中足夠保存100個(gè)頁(yè)。本質(zhì)上是一個(gè)最近寫(xiě)回的頁(yè)面的備份拷貝。當(dāng)InnoDB從緩沖池刷新頁(yè)面到磁盤(pán)時(shí)，首先把他們寫(xiě)到雙寫(xiě)緩沖，然后再把他們寫(xiě)到其所屬的數(shù)據(jù)區(qū)域中，可以保證每個(gè)頁(yè)面的寫(xiě)入都是原子并且持久化的。頁(yè)面在末尾都有校驗(yàn)值（Checksum）來(lái)確認(rèn)是否損壞。

InnoDB的多線程

• Master Thread
  非常核心的后臺(tái)線程，主要負(fù)責(zé)將緩沖池中的數(shù)據(jù)異步刷新到磁盤(pán)，保證數(shù)據(jù)的一致性，包括臟頁(yè)的刷新、合并插入緩沖（INSERT BUFFER）、UNDO頁(yè)的回收等。
• IO Thread
  InnoDB中大量使用了AIO（Async IO）來(lái)處理IO請(qǐng)求，可以極大提高數(shù)據(jù)庫(kù)性能，IO Thread主要是負(fù)責(zé)這些IO請(qǐng)求的回調(diào)（call back）處理。InnoDB1.0之前工有4個(gè)IO Thread，分別是write、read、insert buffer、log IO thread。
• Purge Thread
  事務(wù)提交后，其使用的undolog可能不再需要，因此需要PurgeThread來(lái)回收已經(jīng)使用并分配的undo頁(yè)。

InnoDB的內(nèi)存

• 緩沖池
  InnoDB基于磁盤(pán)存儲(chǔ)，記錄按照頁(yè)的方式進(jìn)行管理。在數(shù)據(jù)庫(kù)中進(jìn)行讀取頁(yè)的操作，首先將磁盤(pán)讀到的頁(yè)存放在緩沖池中，下次讀取先判斷頁(yè)是否在緩沖池則直接讀取，否則讀取磁盤(pán)上的頁(yè)。對(duì)頁(yè)的修改首先修改緩沖池，然后再以一定的頻率刷新到磁盤(pán)（通過(guò)checkpoint機(jī)制）。緩沖池配置通過(guò)innodb_buffer_pool_size來(lái)設(shè)置。
  緩沖池中緩存的數(shù)據(jù)頁(yè)類型有：索引頁(yè)、數(shù)據(jù)頁(yè)、undo頁(yè)、插入緩沖（insert buffer）、自適應(yīng)哈希索引（adaptive hash index）、InnoDB存儲(chǔ)的鎖信息（lock info）、數(shù)據(jù)字典信息（data dictionary）等

  
  
  內(nèi)存數(shù)據(jù)對(duì)象

• LRU List、Free List和Flush List
  InnoDB的LRU添加了midpoint位置，新讀取的頁(yè)不是放到首部，而是放到midpoint位置。默認(rèn)是放在LRU列表長(zhǎng)度的5/8處。有些操作可能會(huì)全表掃描加載大量的頁(yè)，如果直接加載到首部則可能刷出有效頁(yè)。數(shù)據(jù)庫(kù)開(kāi)始時(shí)，LRU是空的，頁(yè)都在FreeList中，查找時(shí)從Free列表中查找是否有可用空閑頁(yè)，若有則從Free列表中刪除放入LRU。當(dāng)頁(yè)從LRU的old部分假如到new時(shí)，稱之為page made young，因?yàn)閕nnodb_old_blocks_time設(shè)置導(dǎo)致頁(yè)沒(méi)有從old部分移動(dòng)到new部分稱為page not made young。

• 重做日志緩沖（redo log buffer）
  三種情況會(huì)講redo log buffer中的內(nèi)容刷新到日志文件

1. Master Thread每秒刷新一次
2. 每個(gè)事務(wù)提交時(shí)會(huì)刷新
3. redo log buffer剩余空間小于1/2時(shí)

• 額外的內(nèi)存池
  在對(duì)數(shù)據(jù)庫(kù)結(jié)構(gòu)本身的內(nèi)存進(jìn)行分配的時(shí)候，需要從額外的內(nèi)存池進(jìn)行申請(qǐng)。

Checkpoint技術(shù)

InnoDB存儲(chǔ)引擎內(nèi)部有兩種：

• Sharp Checkpoint
  數(shù)據(jù)庫(kù)關(guān)閉時(shí)將所有臟頁(yè)刷回磁盤(pán)，默認(rèn)工作方式，參數(shù)innodb_fast_shuthown=1
• Fuzzy Checkpoint
  刷新一部分臟頁(yè)。（Master Thread Checkpoint，F(xiàn)LUSH_LRU_LIST Checkpoint，Async/Sync Flush Checkpoint，Dirty Page too much Checkpoint）

InnoDB關(guān)鍵特性

插入緩沖

• Insert Buffer
  對(duì)于非聚集索引的插入或者更新操作，不是每一次直接插入到索引頁(yè)，而是先判斷插入的非聚集索引是否在緩沖池中，若在則插入，若不在則放入到一個(gè)Insert Buffer中。以一定的頻率進(jìn)行Insert Buffer和非聚集索引子節(jié)點(diǎn)的合并操作。需要滿足兩個(gè)條件：1.索引是輔助索引。2.索引不是唯一的。
• Change Buffer
  InnoDB 1.0.x開(kāi)始可以對(duì)DML操作進(jìn)行緩沖 （Insert，Delete，Update）分別是：Insert Buffer，Delete Buffer，Purge Buffer。

Insert Buffer是一顆B+樹(shù)，全局唯一，負(fù)責(zé)對(duì)所有表的輔助索引進(jìn)行Insert Buffer。
Merge Insert Buffer是合并到真正的輔助索引中的操作，在下面幾種情況時(shí)發(fā)生：

• 輔助索引頁(yè)被讀取到緩沖池中
• Insert Buffer Bitmap 頁(yè)追蹤到該輔助索引頁(yè)已經(jīng)沒(méi)有空間可用
• Master Thread 觸發(fā)

自適應(yīng)Hash索引（Adaptive Hash Index）

InnoDB 會(huì)監(jiān)控各種索引列的查詢，如果判斷建立哈希索引可以提高訪問(wèn)速度，則會(huì)自動(dòng)建立。AHI是通過(guò)緩沖池的B+樹(shù)構(gòu)建而來(lái)，不需要對(duì)整張表結(jié)構(gòu)建立哈希索引。有如下要求：

• 以相同模式訪問(wèn)了100次
• 頁(yè)通過(guò)該模式訪問(wèn)了N次：N=頁(yè)中記錄*1/16

異步IO

異步IO（Asychronous IO，AIO）

文件

• 參數(shù)文件：
  初始化參數(shù)文件
• 日志文件：
  例如錯(cuò)誤日志文件（error log），二進(jìn)制日志文件（binlog），慢查詢?nèi)罩疚募╯low query log），查詢?nèi)罩疚募╨og）
• socket文件：
  UNIX域套接字方式進(jìn)行連接是需要的文件。
• pid文件：
  MySQL實(shí)例的進(jìn)程ID文件
• MySQL表結(jié)構(gòu)文件：
  用來(lái)存放MySQL表結(jié)構(gòu)定義的文件
• 存儲(chǔ)引擎文件：

二進(jìn)制日志（binlog）

記錄了對(duì)MySQL數(shù)據(jù)庫(kù)執(zhí)行更改的所有操作，不包括SELECT和SHOW。

mysql> mysqlmaster status;

mysql> show binlog events in 'binlog.001663' limit 5;

MySQL5.1引入了binlog_format參數(shù)，參數(shù)有STATEMENT、ROW、MIXED三種。

• STATEMENT
  和之前的MySQL版本一樣，二進(jìn)制日志文件記錄的是日志的邏輯SQL語(yǔ)句。
• ROW
  記錄的是表的行更改情況。如果設(shè)置為ROW，可以將InnoDB事務(wù)隔離設(shè)置為READ COMMITTED獲取更好的并發(fā)性。
• MIXED
  默認(rèn)使用STATEMENT，某些情況下使用MIXED。

1. 表的存儲(chǔ)引擎為NDB，對(duì)表的DML操作以ROW格式記錄。
2. 使用了UUID() USER() CURRENT_USER() FOUND_ROWS() ROW_COUNT()
3. 使用了INSERT DELAY語(yǔ)句
4. 使用了用戶自定義函數(shù)
5. 使用了臨時(shí)表

要查看binlog日志文件的內(nèi)容，必須使用MySQL提供的工具mysqlbinlog。

表結(jié)構(gòu)定義文件

MySQL定義了frm為后綴名的文件，記錄了表結(jié)構(gòu)（視圖）定義。

InnoDB存儲(chǔ)引擎文件

表空間文件（tablespace file）

默認(rèn)有一個(gè)初始大小為10MB，名為ibdata1的文件

重做日志文件（redo log file）

默認(rèn)情況下會(huì)有 ib_logfile0和ib_logfile1作為 redo log file 。每個(gè)InnoDB至少有一個(gè)重做日志文件組（group），文件組下有兩個(gè)重做日志文件，用戶可以設(shè)置多個(gè)鏡像日志組（mirrored log groups）

表

索引組織表（index organized table）

MySQL默認(rèn)創(chuàng)建一個(gè)6字節(jié)大小的指針（_rowid）

InnoDB邏輯存儲(chǔ)結(jié)構(gòu)

所有的數(shù)據(jù)都被邏輯地存放在一個(gè)空間內(nèi)，稱之為表空間（tablespace），表空間又由段（segment），區(qū)（extent）、頁(yè)（page）組成，頁(yè)在某些文檔中也成為塊（block）

InnoDB邏輯存儲(chǔ)結(jié)構(gòu)

表空間

如果啟用了 innodb_file_per_table的參數(shù)，每張表的數(shù)據(jù)可以單獨(dú)放到一個(gè)表空間內(nèi) ，其中存放的是數(shù)據(jù)、索引、和插入緩沖Bitmap頁(yè)，其他類的數(shù)據(jù)如回滾（undo）信息、插入緩沖索引頁(yè)、系統(tǒng)事務(wù)信息、二次寫(xiě)緩沖還是放在原本的共享表空間。

段

表空間是由各個(gè)段組成的，包括數(shù)據(jù)段、索引段、回滾段等。數(shù)據(jù)段就是B+樹(shù)的葉子節(jié)點(diǎn)（Leaf node segment），索引段即B+樹(shù)的非索引節(jié)點(diǎn)（Non-leaf node segment）。

區(qū)

區(qū)是連續(xù)頁(yè)組成的空間，任何情況下每個(gè)區(qū)的大小都為1MB，為了保證區(qū)中頁(yè)的連續(xù)性，InnoDB一次從磁盤(pán)申請(qǐng)4-5個(gè)區(qū)，默認(rèn)情況頁(yè)大小為16KB，一個(gè)區(qū)中一共有64個(gè)連續(xù)的頁(yè)。InnoDB1.0.x引入壓縮頁(yè)，每個(gè)頁(yè)的大小可以通過(guò)key_block_size設(shè)置為2k、4k、8k。1.2.x版本新增了參數(shù)innodb_page_size，通過(guò)該參數(shù)可以將默認(rèn)頁(yè)的大小設(shè)置為4k、8k。

頁(yè)

InnoDB中常見(jiàn)的頁(yè)類型有：

• 數(shù)據(jù)頁(yè)（B-tree Node）
• Undo頁(yè)（Undo Log Page）
• 系統(tǒng)頁(yè)（System Page）
• 事務(wù)數(shù)據(jù)頁(yè)（Transaction system Page）
• 插入緩沖頁(yè)位圖（Insert Buffer Bitmap）
• 插入緩沖空閑列表頁(yè)（Insert Buffer Free List）
• 未壓縮的二進(jìn)制大對(duì)象頁(yè)（Uncompressed BLOB Page）

行

MySQL的存儲(chǔ)是面向列的（row-oriented）,數(shù)據(jù)是按行存儲(chǔ)的。頁(yè)存放的記錄有硬性定義最多存放16KB/2 - 200行，即7992行。

InnoDB數(shù)據(jù)頁(yè)結(jié)構(gòu)

數(shù)據(jù)頁(yè)由下面7個(gè)部分組成：

• File Header（文件頭）固定
• Page Header（頁(yè)頭）固定
• Infimun 和 Supremun Record 固定
  頁(yè)中兩個(gè)虛擬的行記錄，Infimun是指比頁(yè)中任何主鍵更小的值，Supremun指比任何值都大的值，這兩個(gè)值在頁(yè)創(chuàng)建的時(shí)候創(chuàng)建，在任何時(shí)候情況下都不會(huì)刪除。
• User Record（用戶記錄，即行記錄）
  存儲(chǔ)實(shí)際記錄，B+樹(shù)索引組織。
• Free Space（空閑空間）
  空閑空間，鏈表數(shù)據(jù)結(jié)構(gòu)。一條記錄被刪除后會(huì)放到空閑空間。
• Page Directory（頁(yè)目錄）
  存放了記錄的相對(duì)位置，這些記錄指針?lè)Q之為槽（slots）或者目錄槽（dictionary slots），稀疏目錄，可能包含多條記錄。
  B+樹(shù)索引不能找到實(shí)際的記錄，而是找到記錄的頁(yè)。

• File Trailer（文件結(jié)尾信息）
  檢測(cè)頁(yè)是否完整寫(xiě)入了磁盤(pán)，checksum值。

  
  
  InnoDB數(shù)據(jù)頁(yè)結(jié)構(gòu)

行溢出數(shù)據(jù)
InnoDB會(huì)將一條記錄中的某些列存儲(chǔ)在真正的數(shù)據(jù)列之外，BLOB，LOB字段可能不一定會(huì)將字段放在溢出頁(yè)面，VARCHAR也有可能會(huì)放進(jìn)溢出頁(yè)面。
Oracle VarCHAR2最多存放4000字節(jié)，MSSQL最多8000字節(jié)，MySQL最多65535（存在其他開(kāi)銷，最長(zhǎng)65532）。當(dāng)發(fā)生行溢出時(shí)，數(shù)據(jù)存放在頁(yè)類型Uncompress BLOB頁(yè)面。數(shù)據(jù)頁(yè)只保存數(shù)據(jù)的前768字節(jié)。

鎖

lock與latch
latch一般稱為閂鎖，輕量級(jí)，要求鎖定的時(shí)間非常短。在InnoDB中，分為mutex（互斥量）與rwlock（讀寫(xiě)鎖）。用來(lái)保證并發(fā)線程操作臨界資源的正確性，并且通常沒(méi)有死鎖檢測(cè)的機(jī)制。
lock的對(duì)象是事務(wù)，用來(lái)鎖定的是數(shù)據(jù)庫(kù)中的對(duì)象，如表、頁(yè)、行。在commit或者rollback之后釋放，有死鎖檢測(cè)機(jī)制。

鎖的類型

• 共享鎖（S Lock）：允許事務(wù)讀一行數(shù)據(jù)
• 排他鎖（X Lock）：允許事務(wù)更新或刪除一行數(shù)據(jù)

上述兩種都是悲觀鎖，樂(lè)觀鎖就是CAS（Compare and Swap）

一致性非鎖定讀（consistent nonlocking read）
是指InnoDB通過(guò)MVCC（Multi Version Concurrency Control）讀取數(shù)據(jù)庫(kù)當(dāng)前行的方式。如果讀取的行正在進(jìn)行update或者delete操作，則讀取一個(gè)快照。在Read Committed和Repeatedable Read中使用。前者讀取最新的快照，后者使用事務(wù)開(kāi)始時(shí)的快照。
一致性鎖定讀（locking read）
也可以顯式的對(duì)讀取加鎖，有兩種操作：

• select ... for update（加一個(gè)X鎖）
• select ... lock in share mode（加一個(gè)S鎖）

行鎖的3種算法

• Record Lock：?jiǎn)蝹€(gè)行記錄的鎖
• Gap Lock：鎖定一個(gè)范圍，不包括記錄本身
• Next-Key Lock：Gap+Record，鎖定范圍以及記錄本身。用來(lái)解決幻影相關(guān)問(wèn)題（Phantom）
  針對(duì)的是索引的區(qū)間，但是當(dāng)查詢條件指定唯一索引值（只針對(duì)主鍵索引/聚集索引）時(shí)，會(huì)降級(jí)為Record Lock，若是二級(jí)索引則不會(huì)。而且InnoDB還會(huì)對(duì)二級(jí)索引的下一個(gè)鍵值加上Gap Lock。
  例如，二級(jí)索引b列有1，3，6，9。當(dāng)使用X鎖鎖定3時(shí)（where b<=3 for update），會(huì)NKL鎖定了范圍（1-3），同時(shí)會(huì)使用GL鎖定下一個(gè)鍵值（3-6）。
  利用這個(gè)機(jī)制可以用一個(gè)事務(wù)，首先select id from t where col=xxx lock in share mode，接下來(lái)insert t (col) values (xxx)，能夠保證一定插入不存在的值。

死鎖

兩個(gè)事務(wù)執(zhí)行時(shí)，因爭(zhēng)奪鎖資源互相等待的場(chǎng)景。
解決死鎖最簡(jiǎn)單的就是超時(shí)，通過(guò)innodb_lock_wait_timeout控制超時(shí)時(shí)間。
當(dāng)前普遍使用的是wait-for graph（主動(dòng)檢測(cè)的方式），這要求數(shù)據(jù)庫(kù)保存兩種信息：

• 鎖的信息鏈表
• 事務(wù)的等待列表

通過(guò)上述信息，可以在事務(wù)請(qǐng)求鎖并發(fā)生等待時(shí)都進(jìn)行判斷，在上述兩個(gè)信息構(gòu)造的圖中是否存在回路，如果存在就表示存在死鎖。
采用深度優(yōu)先算法實(shí)現(xiàn)，InnoDB1.2之前采用遞歸方式，之后采用非遞歸提高了性能。

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

事務(wù)的隔離性由鎖來(lái)實(shí)現(xiàn)，redo log（重做日志）保證事務(wù)的原子性和持久性，undo log（）保證事務(wù)的一致性。
redo恢復(fù)提交事務(wù)修改的頁(yè)操作，是物理日志，記錄的是頁(yè)的物理修改操作。
undo回滾某個(gè)行記錄到特定版本，是邏輯日志，記錄的是行的修改記錄。

redo

存在 redo log buffer和redo log file，buffer寫(xiě)入file時(shí)需要調(diào)用fsync操作，此操作取決于磁盤(pán)性能，決定了事務(wù)提交的性能也就是數(shù)據(jù)庫(kù)的性能。

UNIX的寫(xiě)操作
一般情況下，對(duì)硬盤(pán)（或者其他持久存儲(chǔ)設(shè)備）文件的write操作，更新的只是內(nèi)存中的頁(yè)緩存（page cache），而臟頁(yè)面不會(huì)立即更新到硬盤(pán)中，而是由操作系統(tǒng)統(tǒng)一調(diào)度，如由專門(mén)的flusher內(nèi)核線程在滿足一定條件時(shí)（如一定時(shí)間間隔、內(nèi)存中的臟頁(yè)達(dá)到一定比例）內(nèi)將臟頁(yè)面同步到硬盤(pán)上（放入設(shè)備的IO請(qǐng)求隊(duì)列）。
因?yàn)閣rite調(diào)用不會(huì)等到硬盤(pán)IO完成之后才返回，因此如果OS在write調(diào)用之后、硬盤(pán)同步之前崩潰，則數(shù)據(jù)可能丟失。雖然這樣的時(shí)間窗口很小，但是對(duì)于需要保證事務(wù)的持久化（durability）和一致性（consistency）的數(shù)據(jù)庫(kù)程序來(lái)說(shuō)，write()所提供的“松散的異步語(yǔ)義”是不夠的，通常需要OS提供的同步IO（synchronized-IO）原語(yǔ)來(lái)保證
fsync的功能是確保文件fd所有已修改的內(nèi)容已經(jīng)正確同步到硬盤(pán)上，該調(diào)用會(huì)阻塞等待直到設(shè)備報(bào)告IO完成。除了同步文件的修改內(nèi)容（臟頁(yè)），fsync還會(huì)同步文件的描述信息（metadata，包括size、訪問(wèn)時(shí)間st_atime & st_mtime等等），因?yàn)槲募臄?shù)據(jù)和metadata通常存在硬盤(pán)的不同地方，因此fsync至少需要兩次IO寫(xiě)操作

undo

delete和update操作產(chǎn)生的刪除語(yǔ)句并不是馬上執(zhí)行，而是將delete_flag標(biāo)記為1，最后有purge操作來(lái)統(tǒng)一完成。用undo log來(lái)執(zhí)行，執(zhí)行之后的空間不會(huì)回收，只會(huì)用于重用。