MySQL技術(shù)內(nèi)幕：InnoDB存儲(chǔ)引擎(第2版)

姜承堯

第1章 MySQL體系結(jié)構(gòu)和存儲(chǔ)引擎

>> 在上述例子中使用了mysqld_safe命令來(lái)啟動(dòng)數(shù)據(jù)庫(kù)钢拧，當(dāng)然啟動(dòng)MySQL實(shí)例的方法還有很多堂污，在各種平臺(tái)下的方式可能又會(huì)有所不同抖甘。

>> 當(dāng)啟動(dòng)實(shí)例時(shí)爷绘，MySQL數(shù)據(jù)庫(kù)會(huì)去讀取配置文件鲤屡，根據(jù)配置文件的參數(shù)來(lái)啟動(dòng)數(shù)據(jù)庫(kù)實(shí)例。這與Oracle的參數(shù)文件（spfile）相似列粪，不同的是审磁，Oracle中如果沒有參數(shù)文件，在啟動(dòng)實(shí)例時(shí)會(huì)提示找不到該參數(shù)文件岂座，數(shù)據(jù)庫(kù)啟動(dòng)失敗态蒂。而在MySQL數(shù)據(jù)庫(kù)中，可以沒有配置文件费什，在這種情況下钾恢，MySQL會(huì)按照編譯時(shí)的默認(rèn)參數(shù)設(shè)置啟動(dòng)實(shí)例

>> 從概念上來(lái)說(shuō)，數(shù)據(jù)庫(kù)是文件的集合吕喘，是依照某種數(shù)據(jù)模型組織起來(lái)并存放于二級(jí)存儲(chǔ)器中的數(shù)據(jù)集合赘那；數(shù)據(jù)庫(kù)實(shí)例是程序刑桑，是位于用戶與操作系統(tǒng)之間的一層數(shù)據(jù)管理軟件氯质，用戶對(duì)數(shù)據(jù)庫(kù)數(shù)據(jù)的任何操作，包括數(shù)據(jù)庫(kù)定義祠斧、數(shù)據(jù)查詢闻察、數(shù)據(jù)維護(hù)、數(shù)據(jù)庫(kù)運(yùn)行控制等都是在數(shù)據(jù)庫(kù)實(shí)例下進(jìn)行的琢锋，應(yīng)用程序只有通過(guò)數(shù)據(jù)庫(kù)實(shí)例才能和數(shù)據(jù)庫(kù)打交道辕漂。

>> 需要特別注意的是，存儲(chǔ)引擎是基于表的吴超，而不是數(shù)據(jù)庫(kù)钉嘹。

>> 關(guān)于NDB存儲(chǔ)引擎，有一個(gè)問(wèn)題值得注意鲸阻，那就是NDB存儲(chǔ)引擎的連接操作（JOIN）是在MySQL數(shù)據(jù)庫(kù)層完成的跋涣，而不是在存儲(chǔ)引擎層完成的

>> 相信在任何一本關(guān)于數(shù)據(jù)庫(kù)原理的書中缨睡，可能都會(huì)提到數(shù)據(jù)庫(kù)與傳統(tǒng)文件系統(tǒng)的最大區(qū)別在于數(shù)據(jù)庫(kù)是支持事務(wù)的

>> MySQL提供了一個(gè)非常好的用來(lái)演示MySQL各項(xiàng)功能的示例數(shù)據(jù)庫(kù)，如SQL Server提供的AdventureWorks示例數(shù)據(jù)庫(kù)和Oracle提供的示例數(shù)據(jù)庫(kù)陈辱。據(jù)我所知奖年，知道MySQL示例數(shù)據(jù)庫(kù)的人很少，可能是因?yàn)檫@個(gè)示例數(shù)據(jù)庫(kù)沒有在安裝的時(shí)候提示用戶是否安裝（如Oracle和SQL Server）以及這個(gè)示例數(shù)據(jù)庫(kù)的下載竟然和文檔放在一起

>> 在Linux和UNIX環(huán)境下沛贪，還可以使用UNIX域套接字陋守。UNIX域套接字其實(shí)不是一個(gè)網(wǎng)絡(luò)協(xié)議，所以只能在MySQL客戶端和數(shù)據(jù)庫(kù)實(shí)例在一臺(tái)服務(wù)器上的情況下使用利赋。用戶可以在配置文件中指定套接字文件的路徑水评，如--socket=/tmp/mysql.sock。當(dāng)數(shù)據(jù)庫(kù)實(shí)例啟動(dòng)后媚送，用戶可以通過(guò)下列命令來(lái)進(jìn)行UNIX域套接字文件的查找：

第2章 InnoDB存儲(chǔ)引擎

>> 從MySQL數(shù)據(jù)庫(kù)的官方手冊(cè)可得知之碗，著名的Internet新聞?wù)军c(diǎn)Slashdot.org運(yùn)行在InnoDB上。Mytrix季希、Inc.在InnoDB上存儲(chǔ)超過(guò)1 TB的數(shù)據(jù)褪那，還有一些其他站點(diǎn)在InnoDB上處理插入/更新操作的速度平均為800次/秒。這些都證明了InnoDB是一個(gè)高性能式塌、高可用博敬、高可擴(kuò)展的存儲(chǔ)引擎。

>> 后臺(tái)線程的主要作用是負(fù)責(zé)刷新內(nèi)存池中的數(shù)據(jù)峰尝，保證緩沖池中的內(nèi)存緩存的是最近的數(shù)據(jù)偏窝。此外將已修改的數(shù)據(jù)文件刷新到磁盤文件，同時(shí)保證在數(shù)據(jù)庫(kù)發(fā)生異常的情況下InnoDB能恢復(fù)到正常運(yùn)行狀態(tài)武学。

>> 在InnoDB存儲(chǔ)引擎中大量使用了AIO（Async IO）來(lái)處理寫IO請(qǐng)求祭往，這樣可以極大提高數(shù)據(jù)庫(kù)的性能。而IO Thread的工作主要是負(fù)責(zé)這些IO請(qǐng)求的回調(diào)（call back）處理

>> 可以通過(guò)命令SHOW ENGINE INNODB STATUS來(lái)觀察InnoDB中的IO Thread：

>> 具體來(lái)看火窒，緩沖池中緩存的數(shù)據(jù)頁(yè)類型有：索引頁(yè)硼补、數(shù)據(jù)頁(yè)、undo頁(yè)熏矿、插入緩沖（insert buffer）已骇、自適應(yīng)哈希索引（adaptive hash index）、InnoDB存儲(chǔ)的鎖信息（lock info）票编、數(shù)據(jù)字典信息（data dictionary）等

>> 從InnoDB 1.0.x版本開始褪储，允許有多個(gè)緩沖池實(shí)例。每個(gè)頁(yè)根據(jù)哈希值平均分配到不同緩沖池實(shí)例中慧域。這樣做的好處是減少數(shù)據(jù)庫(kù)內(nèi)部的資源競(jìng)爭(zhēng)鲤竹，增加數(shù)據(jù)庫(kù)的并發(fā)處理能力∥袅瘢可以通過(guò)參數(shù)innodb_buffer_pool_instances來(lái)進(jìn)行配置辛藻，該值默認(rèn)為1瑟啃。

>> 從MySQL 5.6版本開始，還可以通過(guò)information_schema架構(gòu)下的表INNODB_BUFFER_POOL_STATS來(lái)觀察緩沖的狀態(tài)

>> 在InnoDB存儲(chǔ)引擎中揩尸，緩沖池中頁(yè)的大小默認(rèn)為16KB蛹屿，同樣使用LRU算法對(duì)緩沖池進(jìn)行管理

>> 。稍有不同的是InnoDB存儲(chǔ)引擎對(duì)傳統(tǒng)的LRU算法做了一些優(yōu)化岩榆。在InnoDB的存儲(chǔ)引擎中错负，LRU列表中還加入了midpoint位置。新讀取到的頁(yè)勇边，雖然是最新訪問(wèn)的頁(yè)犹撒，但并不是直接放入到LRU列表的首部，而是放入到LRU列表的midpoint位置粒褒。這個(gè)算法在InnoDB存儲(chǔ)引擎下稱為midpoint insertion strategy识颊。

>> 那為什么不采用樸素的LRU算法，直接將讀取的頁(yè)放入到LRU列表的首部呢奕坟？這是因?yàn)槿糁苯訉⒆x取到的頁(yè)放入到LRU的首部祥款，那么某些SQL操作可能會(huì)使緩沖池中的頁(yè)被刷新出，從而影響緩沖池的效率月杉。

>> 常見的這類操作為索引或數(shù)據(jù)的掃描操作刃跛。這類操作需要訪問(wèn)表中的許多頁(yè)，甚至是全部的頁(yè)苛萎，而這些頁(yè)通常來(lái)說(shuō)又僅在這次查詢操作中需要桨昙，并不是活躍的熱點(diǎn)數(shù)據(jù)。如果頁(yè)被放入LRU列表的首部腌歉，那么非惩芾遥可能將所需要的熱點(diǎn)數(shù)據(jù)頁(yè)從LRU列表中移除，而在下一次需要讀取該頁(yè)時(shí)翘盖，InnoDB存儲(chǔ)引擎需要再次訪問(wèn)磁盤桂塞。

>> Buffer pool hit rate，表示緩沖池的命中率最仑，這個(gè)例子中為100%藐俺，說(shuō)明緩沖池運(yùn)行狀態(tài)非常良好炊甲。通常該值不應(yīng)該小于95%泥彤。若發(fā)生Buffer pool hit rate的值小于95%這種情況，用戶需要觀察是否是由于全表掃描引起的LRU列表被污染的問(wèn)題卿啡。

>> 執(zhí)行命令SHOW ENGINE INNODB STATUS顯示的不是當(dāng)前的狀態(tài)吟吝，而是過(guò)去某個(gè)時(shí)間范圍內(nèi)InnoDB存儲(chǔ)引擎的狀態(tài)。從上面的例子可以發(fā)現(xiàn)颈娜，Per second averages calculated from the last 24 seconds代表的信息為過(guò)去24秒內(nèi)的數(shù)據(jù)庫(kù)狀態(tài)剑逃。

>> 在LRU列表中的頁(yè)被修改后浙宜，稱該頁(yè)為臟頁(yè)（dirty page），即緩沖池中的頁(yè)和磁盤上的頁(yè)的數(shù)據(jù)產(chǎn)生了不一致蛹磺。這時(shí)數(shù)據(jù)庫(kù)會(huì)通過(guò)CHECKPOINT機(jī)制將臟頁(yè)刷新回磁盤粟瞬，而Flush列表中的頁(yè)即為臟頁(yè)列表。需要注意的是萤捆，臟頁(yè)既存在于LRU列表中裙品，也存在于Flush列表中

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

>> 當(dāng)前3TB的MySQL數(shù)據(jù)庫(kù)已并不少見市怎，但是3 TB的內(nèi)存卻非常少見。目前Oracle Exadata旗艦數(shù)據(jù)庫(kù)一體機(jī)也就只有2 TB的內(nèi)存辛慰。

>> 因此Checkpoint（檢查點(diǎn)）技術(shù)的目的是解決以下幾個(gè)問(wèn)題：□ 縮短數(shù)據(jù)庫(kù)的恢復(fù)時(shí)間区匠；□ 緩沖池不夠用時(shí)，將臟頁(yè)刷新到磁盤帅腌；□ 重做日志不可用時(shí)驰弄，刷新臟頁(yè)。

>> 對(duì)于InnoDB存儲(chǔ)引擎而言速客，其是通過(guò)LSN（Log Sequence Number）來(lái)標(biāo)記版本的揩懒。而LSN是8字節(jié)的數(shù)字，其單位是字節(jié)挽封。每個(gè)頁(yè)有LSN已球，重做日志中也有LSN，Checkpoint也有LSN辅愿≈橇粒可以通過(guò)命令SHOW ENGINE INNODB STATUS來(lái)觀察：

>> InnoDB存儲(chǔ)引擎的關(guān)鍵特性包括：□ 插入緩沖（Insert Buffer）□ 兩次寫（Double Write）□ 自適應(yīng)哈希索引（Adaptive Hash Index）□ 異步IO（Async IO）□ 刷新鄰接頁(yè)（Flush Neighbor Page）

>> InnoDB存儲(chǔ)引擎開創(chuàng)性地設(shè)計(jì)了Insert Buffer，對(duì)于非聚集索引的插入或更新操作点待，不是每一次直接插入到索引頁(yè)中阔蛉，而是先判斷插入的非聚集索引頁(yè)是否在緩沖池中，若在癞埠，則直接插入状原；若不在涨冀，則先放入到一個(gè)Insert Buffer對(duì)象中驰贷，好似欺騙。數(shù)據(jù)庫(kù)這個(gè)非聚集的索引已經(jīng)插到葉子節(jié)點(diǎn)玷或，而實(shí)際并沒有通铲，只是存放在另一個(gè)位置毕莱。

>> 然后再以一定的頻率和情況進(jìn)行Insert Buffer和輔助索引頁(yè)子節(jié)點(diǎn)的merge（合并）操作，這時(shí)通常能將多個(gè)插入合并到一個(gè)操作中（因?yàn)樵谝粋€(gè)索引頁(yè)中），這就大大提高了對(duì)于非聚集索引插入的性能朋截。

>> 輔助索引不能是唯一的蛹稍，因?yàn)樵诓迦刖彌_時(shí)，數(shù)據(jù)庫(kù)并不去查找索引頁(yè)來(lái)判斷插入的記錄的唯一性部服。如果去查找肯定又會(huì)有離散讀取的情況發(fā)生唆姐，從而導(dǎo)致Insert Buffer失去了意

>> 正如前面所說(shuō)的，目前Insert Buffer存在一個(gè)問(wèn)題是：在寫密集的情況下廓八，插入緩沖會(huì)占用過(guò)多的緩沖池內(nèi)存（innodb_buffer_pool）厦酬，默認(rèn)最大可以占用到1/2的緩沖池內(nèi)存。

>> InnoDB從1.0.x版本開始引入了Change Buffer瘫想，可將其視為Insert Buffer的升級(jí)仗阅。從這個(gè)版本開始，InnoDB存儲(chǔ)引擎可以對(duì)DML操作——INSERT国夜、DELETE减噪、UPDATE都進(jìn)行緩沖，他們分別是：Insert Buffer车吹、Delete Buffer筹裕、Purge buffer。

>> innodb_change_buffer_max_size值默認(rèn)為25窄驹，表示最多使用1/4的緩沖池內(nèi)存空間朝卒。而需要注意的是，該參數(shù)的最大有效值為50乐埠。

>> 可能令絕大部分用戶感到吃驚的是抗斤，Insert Buffer的數(shù)據(jù)結(jié)構(gòu)是一棵B+樹。在MySQL 4.1之前的版本中每張表有一棵Insert Buffer B+樹丈咐。而在現(xiàn)在的版本中瑞眼，全局只有一棵Insert Buffer B+樹，負(fù)責(zé)對(duì)所有的表的輔助索引進(jìn)行Insert Buffer棵逊。

>> 而這棵B+樹存放在共享表空間中伤疙，默認(rèn)也就是ibdata1中。因此辆影，試圖通過(guò)獨(dú)立表空間ibd文件恢復(fù)表中數(shù)據(jù)時(shí)徒像，往往會(huì)導(dǎo)致CHECK TABLE失敗

>> 。這是因?yàn)楸淼妮o助索引中的數(shù)據(jù)可能還在Insert Buffer中蛙讥，也就是共享表空間中锯蛀，所以通過(guò)ibd文件進(jìn)行恢復(fù)后，還需要進(jìn)行REPAIR TABLE操作來(lái)重建表上所有的輔助索引键菱。

>> 如果說(shuō)Insert Buffer帶給InnoDB存儲(chǔ)引擎的是性能上的提升谬墙，那么doublewrite（兩次寫）帶給InnoDB存儲(chǔ)引擎的是數(shù)據(jù)頁(yè)的可靠性今布。

>> 當(dāng)發(fā)生數(shù)據(jù)庫(kù)宕機(jī)時(shí)经备，可能InnoDB存儲(chǔ)引擎正在寫入某個(gè)頁(yè)到表中拭抬，而這個(gè)頁(yè)只寫了一部分，比如16KB的頁(yè)侵蒙，只寫了前4KB造虎，之后就發(fā)生了宕機(jī)，這種情況被稱為部分寫失效（partial page write）纷闺。在InnoDB存儲(chǔ)引擎未使用doublewrite技術(shù)前算凿，曾經(jīng)出現(xiàn)過(guò)因?yàn)椴糠謱懯Ф鴮?dǎo)致數(shù)據(jù)丟失的情況。有經(jīng)驗(yàn)的DBA也許會(huì)想犁功，如果發(fā)生寫失效氓轰，可以通過(guò)重做日志進(jìn)行恢復(fù)。這是一個(gè)辦法浸卦。但是必須清楚地認(rèn)識(shí)到署鸡，重做日志中記錄的是對(duì)頁(yè)的物理操作，如偏移量800限嫌，寫'aaaa'記錄靴庆。如果這個(gè)頁(yè)本身已經(jīng)發(fā)生了損壞，再對(duì)其進(jìn)行重做是沒有意義的怒医。這就是說(shuō)炉抒，在應(yīng)用（apply）重做日志前，用戶需要一個(gè)頁(yè)的副本稚叹，當(dāng)寫入失效發(fā)生時(shí)焰薄，先通過(guò)頁(yè)的副本來(lái)還原該頁(yè)，再進(jìn)行重做扒袖，這就是doublewrite

>> 在對(duì)緩沖池的臟頁(yè)進(jìn)行刷新時(shí)蛤奥，并不直接寫磁盤，而是會(huì)通過(guò)memcpy函數(shù)將臟頁(yè)先復(fù)制到內(nèi)存中的doublewrite buffer僚稿，之后通過(guò)doublewrite buffer再分兩次凡桥，每次1MB順序地寫入共享表空間的物理磁盤上，然后馬上調(diào)用fsync函數(shù)蚀同，同步磁盤缅刽，避免緩沖寫帶來(lái)的問(wèn)題。在這個(gè)過(guò)程中蠢络，因?yàn)閐oublewrite頁(yè)是連續(xù)的衰猛，因此這個(gè)過(guò)程是順序?qū)懙模_銷并不是很大刹孔。在完成doublewrite頁(yè)的寫入后啡省，再將doublewrite buffer中的頁(yè)寫入各個(gè)表空間文件中，此時(shí)的寫入則是離散的。

>> InnoDB存儲(chǔ)引擎會(huì)監(jiān)控對(duì)表上各索引頁(yè)的查詢卦睹。如果觀察到建立哈希索引可以帶來(lái)速度提升畦戒，則建立哈希索引，稱之為自適應(yīng)哈希索引（Adaptive Hash Index结序，AHI）障斋。

>> 值得注意的是，哈希索引只能用來(lái)搜索等值的查詢徐鹤，如SELECT*FROM table WHERE index_col='xxx'垃环。而對(duì)于其他查找類型，如范圍查找返敬，是不能使用哈希索引的遂庄，因此這里出現(xiàn)了non-hash searches/s的情況

>> 用戶可以在發(fā)出一個(gè)IO請(qǐng)求后立即再發(fā)出另一個(gè)IO請(qǐng)求，當(dāng)全部IO請(qǐng)求發(fā)送完畢后劲赠，等待所有IO操作的完成涛目，這就是AIO。AIO的另一個(gè)優(yōu)勢(shì)是可以進(jìn)行IO Merge操作经磅，也就是將多個(gè)IO合并為1個(gè)IO泌绣，這樣可以提高IOPS的性能

>> 需要注意的是，Native AIO需要操作系統(tǒng)提供支持预厌。Windows系統(tǒng)和Linux系統(tǒng)都提供Native AIO支持阿迈，而Mac OSX系統(tǒng)則未提供

>> 參數(shù)innodb_use_native_aio用來(lái)控制是否啟用Native AIO，在Linux操作系統(tǒng)下轧叽，默認(rèn)值為ON

>> 在關(guān)閉時(shí)苗沧，參數(shù)innodb_fast_shutdown影響著表的存儲(chǔ)引擎為InnoDB的行為。該參數(shù)可取值為0炭晒、1待逞、2，默認(rèn)值為1网严。

>> 參數(shù)innodb_force_recovery影響了整個(gè)InnoDB存儲(chǔ)引擎恢復(fù)的狀況识樱。該參數(shù)值默認(rèn)為0，代表當(dāng)發(fā)生需要恢復(fù)時(shí)震束，進(jìn)行所有的恢復(fù)操作怜庸，當(dāng)不能進(jìn)行有效恢復(fù)時(shí)，如數(shù)據(jù)頁(yè)發(fā)生了corruption垢村，MySQL數(shù)據(jù)庫(kù)可能發(fā)生宕機(jī)（crash）割疾，并把錯(cuò)誤寫入錯(cuò)誤日志中去。

>> 參數(shù)innodb_force_recovery還可以設(shè)置為6個(gè)非零值：1～6嘉栓。大的數(shù)字表示包含了前面所有小數(shù)字表示的影響

第3章 文件

>> 默認(rèn)情況下宏榕，MySQL實(shí)例會(huì)按照一定的順序在指定的位置進(jìn)行讀取拓诸，用戶只需通過(guò)命令mysql--help | grep my.cnf來(lái)尋找即可。

>> Oracle數(shù)據(jù)庫(kù)存在所謂的隱藏參數(shù)（undocumented parameter）麻昼，以供Oracle“內(nèi)部人士”使用奠支，SQL Server也有類似的參數(shù)。有些DBA曾問(wèn)我涌献，MySQL中是否也有這類參數(shù)胚宦。我的回答是：沒有首有，也不需要燕垃。即使Oracle和SQL Server中都有些所謂的隱藏參數(shù)，在絕大多數(shù)的情況下井联，這些數(shù)據(jù)庫(kù)廠商也不建議用戶在生產(chǎn)環(huán)境中對(duì)其進(jìn)行很大的調(diào)整卜壕。

>> MySQL數(shù)據(jù)庫(kù)中的參數(shù)可以分為兩類：□ 動(dòng)態(tài)（dynamic）參數(shù)□ 靜態(tài)（static）參數(shù)動(dòng)態(tài)參數(shù)意味著可以在MySQL實(shí)例運(yùn)行中進(jìn)行更改，靜態(tài)參數(shù)說(shuō)明在整個(gè)實(shí)例生命周期內(nèi)都不得進(jìn)行更改烙常，就好像是只讀（read only）的

>> 當(dāng)出現(xiàn)MySQL數(shù)據(jù)庫(kù)不能正常啟動(dòng)時(shí)轴捎，第一個(gè)必須查找的文件應(yīng)該就是錯(cuò)誤日志文件，該文件記錄了錯(cuò)誤信息蚕脏，能很好地指導(dǎo)用戶發(fā)現(xiàn)問(wèn)題侦副。

>> 設(shè)置long_query_time這個(gè)閾值后，MySQL數(shù)據(jù)庫(kù)會(huì)記錄運(yùn)行時(shí)間超過(guò)該值的所有SQL語(yǔ)句驼鞭，但運(yùn)行時(shí)間正好等于long_query_time的情況并不會(huì)被記錄下秦驯。也就是說(shuō)，在源代碼中判斷的是大于long_query_time挣棕，而非大于等于

>> 另一個(gè)和慢查詢?nèi)罩居嘘P(guān)的參數(shù)是log_queries_not_using_indexes译隘，如果運(yùn)行的SQL語(yǔ)句沒有使用索引，則MySQL數(shù)據(jù)庫(kù)同樣會(huì)將這條SQL語(yǔ)句記錄到慢查詢?nèi)罩疚募?br>

>> MySQL 5.6.5版本開始新增了一個(gè)參數(shù)log_throttle_queries_not_using_indexes洛心，用來(lái)表示每分鐘允許記錄到slow log的且未使用索引的SQL語(yǔ)句次數(shù)固耘。該值默認(rèn)為0，表示沒有限制词身。在生產(chǎn)環(huán)境下厅目，若沒有使用索引，此類SQL語(yǔ)句會(huì)頻繁地被記錄到slow log法严，從而導(dǎo)致slow log文件的大小不斷增加损敷，故DBA可通過(guò)此參數(shù)進(jìn)行配置。

>> MySQL 5.1開始可以將慢查詢的日志記錄放入一張表中渐夸，這使得用戶的查詢更加方便和直觀嗤锉。慢查詢表在mysql架構(gòu)下，名為slow_log

>> 查看slow_log表的定義會(huì)發(fā)現(xiàn)該表使用的是CSV引擎墓塌，對(duì)大數(shù)據(jù)量下的查詢效率可能不高瘟忱。用戶可以把slow_log表的引擎轉(zhuǎn)換到MyISAM奥额，并在start_time列上添加索引以進(jìn)一步提高查詢的效率。

>> 不能忽視的是访诱，將slow_log表的存儲(chǔ)引擎更改為MyISAM后垫挨，還是會(huì)對(duì)數(shù)據(jù)庫(kù)造成額外的開銷。

>> 用戶可以通過(guò)額外的參數(shù)long_query_io將超過(guò)指定邏輯IO次數(shù)的SQL語(yǔ)句記錄到slow log中触菜。該值默認(rèn)為100九榔，即表示對(duì)于邏輯讀取次數(shù)大于100的SQL語(yǔ)句，記錄到slow log中涡相。而為了兼容原MySQL數(shù)據(jù)庫(kù)的運(yùn)行方式哲泊，還添加了參數(shù)slow_query_type，用來(lái)表示啟用slow log的方式

>> 查詢?nèi)罩居涗浟怂袑?duì)MySQL數(shù)據(jù)庫(kù)請(qǐng)求的信息催蝗，無(wú)論這些請(qǐng)求是否得到了正確的執(zhí)行切威。默認(rèn)文件名為：主機(jī)名.log

>> 。同樣地丙号，從MySQL 5.1開始先朦，可以將查詢?nèi)罩镜挠涗浄湃雖ysql架構(gòu)下的general_log表中，該表的使用方法和前面小節(jié)提到的slow_log基本一樣

>> 二進(jìn)制日志（binary log）記錄了對(duì)MySQL數(shù)據(jù)庫(kù)執(zhí)行更改的所有操作犬缨，但是不包括SELECT和SHOW這類操作喳魏，因?yàn)檫@類操作對(duì)數(shù)據(jù)本身并沒有修改。

>> 使用事務(wù)的表存儲(chǔ)引擎（如InnoDB存儲(chǔ)引擎）時(shí)怀薛，所有未提交（uncommitted）的二進(jìn)制日志會(huì)被記錄到一個(gè)緩存中去刺彩，等該事務(wù)提交（committed）時(shí)直接將緩沖中的二進(jìn)制日志寫入二進(jìn)制日志文件，而該緩沖的大小由binlog_cache_size決定乾戏，默認(rèn)大小為32K迂苛。

>> 此外，binlog_cache_size是基于會(huì)話（session）的鼓择，也就是說(shuō)三幻，當(dāng)一個(gè)線程開始一個(gè)事務(wù)時(shí)，MySQL會(huì)自動(dòng)分配一個(gè)大小為binlog_cache_size的緩存呐能，因此該值的設(shè)置需要相當(dāng)小心念搬，不能設(shè)置過(guò)大。當(dāng)一個(gè)事務(wù)的記錄大于設(shè)定的binlog_cache_size時(shí)摆出，MySQL會(huì)把緩沖中的日志寫入一個(gè)臨時(shí)文件中朗徊，因此該值又不能設(shè)得太小

>> Binlog_cache_use記錄了使用緩沖寫二進(jìn)制日志的次數(shù)，binlog_cache_disk_use記錄了使用臨時(shí)文件寫二進(jìn)制日志的次數(shù)

>> 默認(rèn)情況下偎漫，二進(jìn)制日志并不是在每次寫的時(shí)候同步到磁盤（用戶可以理解為緩沖寫）爷恳。因此，當(dāng)數(shù)據(jù)庫(kù)所在操作系統(tǒng)發(fā)生宕機(jī)時(shí)象踊，可能會(huì)有最后一部分?jǐn)?shù)據(jù)沒有寫入二進(jìn)制日志文件中温亲，這會(huì)給恢復(fù)和復(fù)制帶來(lái)問(wèn)題

>> 即使將sync_binlog設(shè)為1棚壁，還是會(huì)有一種情況導(dǎo)致問(wèn)題的發(fā)生。當(dāng)使用InnoDB存儲(chǔ)引擎時(shí)栈虚，在一個(gè)事務(wù)發(fā)出COMMIT動(dòng)作之前袖外，由于sync_binlog為1，因此會(huì)將二進(jìn)制日志立即寫入磁盤魂务。如果這時(shí)已經(jīng)寫入了二進(jìn)制日志曼验，但是提交還沒有發(fā)生，并且此時(shí)發(fā)生了宕機(jī)粘姜，那么在MySQL數(shù)據(jù)庫(kù)下次啟動(dòng)時(shí)鬓照，由于COMMIT操作并沒有發(fā)生，這個(gè)事務(wù)會(huì)被回滾掉相艇。但是二進(jìn)制日志已經(jīng)記錄了該事務(wù)信息颖杏，不能被回滾纯陨。

>> 如果當(dāng)前數(shù)據(jù)庫(kù)是復(fù)制中的slave角色坛芽，則它不會(huì)將從master取得并執(zhí)行的二進(jìn)制日志寫入自己的二進(jìn)制日志文件中去。如果需要寫入翼抠，要設(shè)置log-slave-update咙轩。如果需要搭建master=>slave=>slave架構(gòu)的復(fù)制，則必須設(shè)置該參數(shù)阴颖。

>> MySQL 5.1開始引入了binlog_format參數(shù)活喊，該參數(shù)可設(shè)的值有STATEMENT、ROW和MIXED

>> 上面的這個(gè)例子告訴我們量愧，將參數(shù)binlog_format設(shè)置為ROW钾菊，會(huì)對(duì)磁盤空間要求有一定的增加。而由于復(fù)制是采用傳輸二進(jìn)制日志方式實(shí)現(xiàn)的偎肃，因此復(fù)制的網(wǎng)絡(luò)開銷也有所增加煞烫。

>> 要查看二進(jìn)制日志文件的內(nèi)容，必須通過(guò)MySQL提供的工具mysqlbinlog累颂。對(duì)于STATEMENT格式的二進(jìn)制日志文件滞详，在使用mysqlbinlog后，看到的就是執(zhí)行的邏輯SQL語(yǔ)句

>> 但不論表采用何種存儲(chǔ)引擎紊馏，MySQL都有一個(gè)以frm為后綴名的文件料饥，這個(gè)文件記錄了該表的表結(jié)構(gòu)定義。

>> frm還用來(lái)存放視圖的定義朱监，如用戶創(chuàng)建了一個(gè)v_a視圖岸啡，那么對(duì)應(yīng)地會(huì)產(chǎn)生一個(gè)v_a.frm文件，用來(lái)記錄視圖的定義赫编，該文件是文本文件巡蘸，可以直接使用cat命令進(jìn)行查看

>> 設(shè)置innodb_data_file_path參數(shù)后篇裁，所有基于InnoDB存儲(chǔ)引擎的表的數(shù)據(jù)都會(huì)記錄到該共享表空間中。若設(shè)置了參數(shù)innodb_file_per_table赡若，則用戶可以將每個(gè)基于InnoDB存儲(chǔ)引擎的表產(chǎn)生一個(gè)獨(dú)立表空間

>> 达布。獨(dú)立表空間的命名規(guī)則為：表名.ibd。通過(guò)這樣的方式逾冬，用戶不用將所有數(shù)據(jù)都存放于默認(rèn)的表空間

>> 這些單獨(dú)的表空間文件僅存儲(chǔ)該表的數(shù)據(jù)黍聂、索引和插入緩沖BITMAP等信息，其余信息還是存放在默認(rèn)的表空間中

>> 在默認(rèn)情況下身腻，在InnoDB存儲(chǔ)引擎的數(shù)據(jù)目錄下會(huì)有兩個(gè)名為ib_logfile0和ib_logfile1的文件产还。在MySQL官方手冊(cè)中將其稱為InnoDB存儲(chǔ)引擎的日志文件，不過(guò)更準(zhǔn)確的定義應(yīng)該是重做日志文件（redo log file）嘀趟。為什么強(qiáng)調(diào)是重做日志文件呢脐区？因?yàn)橹刈鋈罩疚募?duì)于InnoDB存儲(chǔ)引擎至關(guān)重要，它們記錄了對(duì)于InnoDB存儲(chǔ)引擎的事務(wù)日志她按。

>> 每個(gè)InnoDB存儲(chǔ)引擎至少有1個(gè)重做日志文件組（group）牛隅，每個(gè)文件組下至少有2個(gè)重做日志文件，如默認(rèn)的ib_logfile0和ib_logfile1酌泰。為了得到更高的可靠性媒佣，用戶可以設(shè)置多個(gè)的鏡像日志組（mirrored log groups），將不同的文件組放在不同的磁盤上陵刹，以此提高重做日志的高可用性默伍。在日志組中每個(gè)重做日志文件的大小一致，并以循環(huán)寫入的方式運(yùn)行衰琐。

>> InnoDB存儲(chǔ)引擎先寫重做日志文件1也糊，當(dāng)達(dá)到文件的最后時(shí)，會(huì)切換至重做日志文件2羡宙，再當(dāng)重做日志文件2也被寫滿時(shí)狸剃，會(huì)再切換到重做日志文件1中。

>> 若磁盤本身已經(jīng)做了高可用的方案辛辨，如磁盤陣列捕捂，那么可以不開啟重做日志鏡像的功能

>> 二進(jìn)制日志會(huì)記錄所有與MySQL數(shù)據(jù)庫(kù)有關(guān)的日志記錄，包括InnoDB斗搞、MyISAM指攒、Heap等其他存儲(chǔ)引擎的日志。而InnoDB存儲(chǔ)引擎的重做日志只記錄有關(guān)該存儲(chǔ)引擎本身的事務(wù)日志。

>> 其次，記錄的內(nèi)容不同贝咙，無(wú)論用戶將二進(jìn)制日志文件記錄的格式設(shè)為STATEMENT還是ROW鸳慈，又或者是MIXED隙弛，其記錄的都是關(guān)于一個(gè)事務(wù)的具體操作內(nèi)容架馋，即該日志是邏輯日志。而InnoDB存儲(chǔ)引擎的重做日志文件記錄的是關(guān)于每個(gè)頁(yè)（Page）的更改的物理情況全闷。

>> 此外叉寂，寫入的時(shí)間也不同，二進(jìn)制日志文件僅在事務(wù)提交前進(jìn)行提交总珠，即只寫磁盤一次屏鳍，不論這時(shí)該事務(wù)多大。而在事務(wù)進(jìn)行的過(guò)程中局服，卻不斷有重做日志條目（redo entry）被寫入到重做日志文件中钓瞭。

>> 在InnoDB存儲(chǔ)引擎中，對(duì)于各種不同的操作有著不同的重做日志格式淫奔。到InnoDB 1.2.x版本為止山涡，總共定義了51種重做日志類型。

>> 在第2章中已經(jīng)提到唆迁，寫入重做日志文件的操作不是直接寫鸭丛，而是先寫入一個(gè)重做日志緩沖（redo log buffer）中，然后按照一定的條件順序地寫入日志文件

>> 從重做日志緩沖往磁盤寫入時(shí)媒惕，是按512個(gè)字節(jié)系吩，也就是一個(gè)扇區(qū)的大小進(jìn)行寫入。因?yàn)樯葏^(qū)是寫入的最小單位妒蔚，因此可以保證寫入必定是成功的。因此在重做日志的寫入過(guò)程中不需要有doublewrite月弛。

>> 因此為了保證事務(wù)的ACID中的持久性肴盏，必須將innodb_flush_log_at_trx_commit設(shè)置為1，也就是每當(dāng)有事務(wù)提交時(shí)帽衙，就必須確保事務(wù)都已經(jīng)寫入重做日志文件菜皂。那么當(dāng)數(shù)據(jù)庫(kù)因?yàn)橐馔獍l(fā)生宕機(jī)時(shí)，可以通過(guò)重做日志文件恢復(fù)厉萝，并保證可以恢復(fù)已經(jīng)提交的事務(wù)恍飘。

第4章 表

>> 在InnoDB存儲(chǔ)引擎中，表都是根據(jù)主鍵順序組織存放的谴垫，這種存儲(chǔ)方式的表稱為索引組織表（index organized table）章母。

>> 在InnoDB存儲(chǔ)引擎表中，每張表都有個(gè)主鍵（Primary Key）翩剪，如果在創(chuàng)建表時(shí)沒有顯式地定義主鍵乳怎，則InnoDB存儲(chǔ)引擎會(huì)按如下方式選擇或創(chuàng)建主鍵：□ 首先判斷表中是否有非空的唯一索引（Unique NOT NULL），如果有前弯，則該列即為主鍵蚪缀★牛□ 如果不符合上述條件，InnoDB存儲(chǔ)引擎自動(dòng)創(chuàng)建一個(gè)6字節(jié)大小的指針询枚。

>> 當(dāng)表中有多個(gè)非空唯一索引時(shí)违帆，InnoDB存儲(chǔ)引擎將選擇建表時(shí)第一個(gè)定義的非空唯一索引為主鍵

>> 。這里需要非常注意的是金蜀，主鍵的選擇根據(jù)的是定義索引的順序前方，而不是建表時(shí)列的順序。

>> 可以通過(guò)下面的SQL語(yǔ)句判斷表的主鍵值：

>> _rowid可以顯示表的主鍵廉油，因此通過(guò)上述查詢可以找到表z的主鍵

>> 另外需要注意的是惠险，_rowid只能用于查看單個(gè)列為主鍵的情況，對(duì)于多列組成的主鍵就顯得無(wú)能為力了

>> 從InnoDB存儲(chǔ)引擎的邏輯存儲(chǔ)結(jié)構(gòu)看抒线，所有數(shù)據(jù)都被邏輯地存放在一個(gè)空間中班巩，稱之為表空間（tablespace）。表空間又由段（segment）嘶炭、區(qū)（extent）抱慌、頁(yè)（page）組成。頁(yè)在一些文檔中有時(shí)也稱為塊（block）眨猎，

>> InnoDB存儲(chǔ)引擎的邏輯存儲(chǔ)結(jié)構(gòu)大致如圖4-1所示抑进。

>> 第3章中已經(jīng)介紹了在默認(rèn)情況下InnoDB存儲(chǔ)引擎有一個(gè)共享表空間ibdata1，即所有數(shù)據(jù)都存放在這個(gè)表空間內(nèi)睡陪。如果用戶啟用了參數(shù)innodb_file_per_table寺渗，則每張表內(nèi)的數(shù)據(jù)可以單獨(dú)放到一個(gè)表空間內(nèi)。

>> 如果啟用了innodb_file_per_table的參數(shù)兰迫，需要注意的是每張表的表空間內(nèi)存放的只是數(shù)據(jù)信殊、索引和插入緩沖Bitmap頁(yè)，其他類的數(shù)據(jù)汁果，如回滾（undo）信息涡拘，插入緩沖索引頁(yè)、系統(tǒng)事務(wù)信息据德，二次寫緩沖（Double write buffer）等還是存放在原來(lái)的共享表空間內(nèi)鳄乏。這同時(shí)也說(shuō)明了另一個(gè)問(wèn)題：即使在啟用了參數(shù)innodb_file_per_table之后，共享表空間還是會(huì)不斷地增加其大小棘利。

>> InnoDB存儲(chǔ)引擎不會(huì)在執(zhí)行rollback時(shí)去收縮這個(gè)表空間橱野。雖然InnoDB不會(huì)回收這些空間，但是會(huì)自動(dòng)判斷這些undo信息是否還需要赡译，如果不需要仲吏，則會(huì)將這些空間標(biāo)記為可用空間，供下次undo使用。

>> 我用python寫了一個(gè)py_innodb_page_info小工具裹唆，用來(lái)查看表空間中各頁(yè)的類型和信息誓斥，用戶可以在code.google.com上搜索david-mysql-tools進(jìn)行查找

>> 因?yàn)榍懊嬉呀?jīng)介紹過(guò)了InnoDB存儲(chǔ)引擎表是索引組織的（index organized），因此數(shù)據(jù)即索引许帐，索引即數(shù)據(jù)劳坑。那么數(shù)據(jù)段即為B+樹的葉子節(jié)點(diǎn)（圖4-1的Leaf node segment），索引段即為B+樹的非索引節(jié)點(diǎn)（圖4-1的Non-leaf node segment）成畦。

>> 區(qū)是由連續(xù)頁(yè)組成的空間距芬，在任何情況下每個(gè)區(qū)的大小都為1MB。為了保證區(qū)中頁(yè)的連續(xù)性循帐，InnoDB存儲(chǔ)引擎一次從磁盤申請(qǐng)4～5個(gè)區(qū)框仔。在默認(rèn)情況下，InnoDB存儲(chǔ)引擎頁(yè)的大小為16KB拄养，即一個(gè)區(qū)中一共有64個(gè)連續(xù)的頁(yè)离斩。

>> InnoDB 1.0.x版本開始引入壓縮頁(yè)，即每個(gè)頁(yè)的大小可以通過(guò)參數(shù)KEY_BLOCK_SIZE設(shè)置為2K瘪匿、4K跛梗、8K，因此每個(gè)區(qū)對(duì)應(yīng)頁(yè)的數(shù)量就應(yīng)該為512棋弥、256核偿、128。

>> InnoDB 1.2.x版本新增了參數(shù)innodb_page_size顽染，通過(guò)該參數(shù)可以將默認(rèn)頁(yè)的大小設(shè)置為4K漾岳、8K，但是頁(yè)中的數(shù)據(jù)庫(kù)不是壓縮家乘。這時(shí)區(qū)中頁(yè)的數(shù)量同樣也為256蝗羊、128∪示猓總之，不論頁(yè)的大小怎么變化翔悠，區(qū)的大小總是為1M业崖。

>> 在用戶啟用了參數(shù)innodb_file_per_talbe后，創(chuàng)建的表默認(rèn)大小是96KB蓄愁。區(qū)中是64個(gè)連續(xù)的頁(yè)双炕，創(chuàng)建的表的大小至少是1MB才對(duì)啊撮抓？其實(shí)這是因?yàn)樵诿總€(gè)段開始時(shí)妇斤，先用32個(gè)頁(yè)大小的碎片頁(yè)（fragment page）來(lái)存放數(shù)據(jù)，在使用完這些頁(yè)之后才是64個(gè)連續(xù)頁(yè)的申請(qǐng)。這樣做的目的是站超，對(duì)于一些小表荸恕，或者是undo這類的段，可以在開始時(shí)申請(qǐng)較少的空間死相，節(jié)省磁盤容量的開銷

>> 因?yàn)橐呀?jīng)用完了32個(gè)碎片頁(yè)融求，新的頁(yè)會(huì)采用區(qū)的方式進(jìn)行空間的申請(qǐng)，如果此時(shí)用戶再通過(guò)py_innodb_page_info工具來(lái)看表空間文件t1.ibd算撮，應(yīng)該可以看到很多類型為Freshly Allocated Page的頁(yè)：

>> 從InnoDB 1.2.x版本開始生宛，可以通過(guò)參數(shù)innodb_page_size將頁(yè)的大小設(shè)置為4K、8K肮柜、16K陷舅。若設(shè)置完成，則所有表中頁(yè)的大小都為innodb_page_size审洞，不可以對(duì)其再次進(jìn)行修改莱睁。除非通過(guò)mysqldump導(dǎo)入和導(dǎo)出操作來(lái)產(chǎn)生新的庫(kù)

>> 每個(gè)頁(yè)存放的行記錄也是有硬性定義的，最多允許存放16KB / 2-200行的記錄预明，即7992行記錄

>> InnoDB存儲(chǔ)引擎提供了Compact和Redundant兩種格式來(lái)存放行記錄數(shù)據(jù)缩赛，

>> 在MySQL 5.1版本中，默認(rèn)設(shè)置為Compact行格式撰糠。用戶可以通過(guò)命令SHOW TABLE STATUS LIKE 'table_name'來(lái)查看當(dāng)前表使用的行格式酥馍，其中row_format屬性表示當(dāng)前所使用的行記錄結(jié)構(gòu)類型。

>> 每行數(shù)據(jù)除了用戶定義的列外阅酪，還有兩個(gè)隱藏列旨袒，事務(wù)ID列和回滾指針列，分別為6字節(jié)和7字節(jié)的大小术辐。

>> 若InnoDB表沒有定義主鍵砚尽，每行還會(huì)增加一個(gè)6字節(jié)的rowid列。

>> InnoDB存儲(chǔ)引擎可以將一條記錄中的某些數(shù)據(jù)存儲(chǔ)在真正的數(shù)據(jù)頁(yè)面之外辉词。一般認(rèn)為BLOB必孤、LOB這類的大對(duì)象列類型的存儲(chǔ)會(huì)把數(shù)據(jù)存放在數(shù)據(jù)頁(yè)面之外。但是瑞躺，這個(gè)理解有點(diǎn)偏差敷搪，BLOB可以不將數(shù)據(jù)放在溢出頁(yè)面，而且即便是VARCHAR列數(shù)據(jù)類型幢哨，依然有可能被存放為行溢出數(shù)據(jù)

>> 從錯(cuò)誤消息可以看到InnoDB存儲(chǔ)引擎并不支持65535長(zhǎng)度的VARCHAR赡勘。這是因?yàn)檫€有別的開銷，通過(guò)實(shí)際測(cè)試發(fā)現(xiàn)能存放VARCHAR類型的最大長(zhǎng)度為65532捞镰。

>> 因此從這個(gè)例子中用戶也應(yīng)該理解VARCHAR（N）中的N指的是字符的長(zhǎng)度闸与。而文檔中說(shuō)明VARCHAR類型最大支持65535毙替，單位是字節(jié)。

>> 此外需要注意的是践樱，MySQL官方手冊(cè)中定義的65535長(zhǎng)度是指所有VARCHAR列的長(zhǎng)度總和厂画，如果列的長(zhǎng)度總和超出這個(gè)長(zhǎng)度，依然無(wú)法創(chuàng)建

>> 即使能存放65532個(gè)字節(jié)映胁，但是有沒有想過(guò)木羹，InnoDB存儲(chǔ)引擎的頁(yè)為16KB，即16384字節(jié)解孙，怎么能存放65532字節(jié)呢坑填？因此，在一般情況下弛姜，InnoDB存儲(chǔ)引擎的數(shù)據(jù)都是存放在頁(yè)類型為B-tree node中脐瑰。但是當(dāng)發(fā)生行溢出時(shí)，數(shù)據(jù)存放在頁(yè)類型為Uncompress BLOB頁(yè)中廷臼。

>> InnoDB存儲(chǔ)引擎表是索引組織的苍在，即B+Tree的結(jié)構(gòu)，這樣每個(gè)頁(yè)中至少應(yīng)該有兩條行記錄（否則失去了B+Tree的意義荠商，變成鏈表了）寂恬。因此，如果頁(yè)中只能存放下一條記錄莱没，那么InnoDB存儲(chǔ)引擎會(huì)自動(dòng)將行數(shù)據(jù)存放到溢出頁(yè)中

>> 經(jīng)過(guò)多次試驗(yàn)測(cè)試初肉，發(fā)現(xiàn)這個(gè)閾值的長(zhǎng)度為8098

>> 對(duì)于TEXT或BLOB的數(shù)據(jù)類型，用戶總是以為它們是存放在Uncompressed BLOB Page中的饰躲，其實(shí)這也是不準(zhǔn)確的牙咏。是放在數(shù)據(jù)頁(yè)中還是BLOB頁(yè)中，和前面討論的VARCHAR一樣嘹裂，至少保證一個(gè)頁(yè)能存放兩條記錄

>> InnoDB 1.0.x版本開始引入了新的文件格式（file format妄壶，用戶可以理解為新的頁(yè)格式），以前支持的Compact和Redundant格式稱為Antelope文件格式寄狼，新的文件格式稱為Barracuda文件格式丁寄。Barracuda文件格式下?lián)碛袃煞N新的行記錄格式：Compressed和Dynamic。

>> 從MySQL 4.1版本開始泊愧，CHR（N）中的N指的是字符的長(zhǎng)度狡逢，而不是之前版本的字節(jié)長(zhǎng)度。也就說(shuō)在不同的字符集下拼卵，CHAR類型列內(nèi)部存儲(chǔ)的可能不是定長(zhǎng)的數(shù)據(jù)

>> SELECT a,CHAR_LENGTH(a),LENGTH(a)

>> SELECT a,HEX(a) ? ? ? ?-> FROM j\G;

>> SHOW VARIABLES LIKE 'innodb_file_format'\G;

>> 關(guān)系型數(shù)據(jù)庫(kù)系統(tǒng)和文件系統(tǒng)的一個(gè)不同點(diǎn)是，關(guān)系數(shù)據(jù)庫(kù)本身能保證存儲(chǔ)數(shù)據(jù)的完整性蛮艰，不需要應(yīng)用程序的控制腋腮，而文件系統(tǒng)一般需要在程序端進(jìn)行控制

>> 通過(guò)設(shè)置參數(shù)sql_mode的值為STRICT_TRANS_TABLES雀彼，這次MySQL數(shù)據(jù)庫(kù)對(duì)于輸入值的合法性進(jìn)行了約束，而且針對(duì)不同的錯(cuò)誤即寡，提示的錯(cuò)誤內(nèi)容也都不同徊哑。參數(shù)sql_mode可設(shè)的值有很多，具體可參考MySQL官方手冊(cè)聪富。

>> 最多可以為一個(gè)表建立6個(gè)觸發(fā)器莺丑，即分別為INSERT、UPDATE墩蔓、DELETE的BEFORE和AFTER各定義一個(gè)梢莽。

>> 假設(shè)有張用戶消費(fèi)表，每次用戶購(gòu)買一樣物品后其金額都是減的奸披，若這時(shí)有“不懷好意”的用戶做了類似減去一個(gè)負(fù)值的操作昏名，這樣用戶的錢沒減少反而會(huì)不斷增加

>> 一般來(lái)說(shuō)，稱被引用的表為父表阵面，引用的表稱為子表轻局。外鍵定義時(shí)的ON DELETE和ON UPDATE表示在對(duì)父表進(jìn)行DELETE和UPDATE操作時(shí)，對(duì)子表所做的操作

>> CASCADE表示當(dāng)父表發(fā)生DELETE或UPDATE操作時(shí)样刷，對(duì)相應(yīng)的子表中的數(shù)據(jù)也進(jìn)行DELETE或UPDATE操作仑扑。SET NULL表示當(dāng)父表發(fā)生DELETE或UPDATE操作時(shí)，相應(yīng)的子表中的數(shù)據(jù)被更新為NULL值置鼻，但是子表中相對(duì)應(yīng)的列必須允許為NULL值镇饮。NO ACTION表示當(dāng)父表發(fā)生DELETE或UPDATE操作時(shí)，拋出錯(cuò)誤沃疮，不允許這類操作發(fā)生盒让。RESTRICT表示當(dāng)父表發(fā)生DELETE或UPDATE操作時(shí)，拋出錯(cuò)誤司蔬，不允許這類操作發(fā)生邑茄。

>> 在其他數(shù)據(jù)庫(kù)中，如Oracle數(shù)據(jù)庫(kù)俊啼，有一種稱為延時(shí)檢查（deferred check）的外鍵約束肺缕，即檢查在SQL語(yǔ)句運(yùn)行完成后再進(jìn)行。而目前MySQL數(shù)據(jù)庫(kù)的外鍵約束都是即時(shí)檢查（immediate check）

>> 雖然視圖是基于基表的一個(gè)虛擬表授帕，但是用戶可以對(duì)某些視圖進(jìn)行更新操作同木，其本質(zhì)就是通過(guò)視圖的定義來(lái)更新基本表。一般稱可以進(jìn)行更新操作的視圖為可更新視圖（updatable view）跛十。視圖定義中的WITH CHECK OPTION就是針對(duì)于可更新的視圖的彤路，即更新的值是否需要檢查

>> MySQL數(shù)據(jù)庫(kù)DBA的一個(gè)常用的命令是SHOW TABLES，該命令會(huì)顯示出當(dāng)前數(shù)據(jù)庫(kù)下所有的表芥映。但因?yàn)橐晥D是虛表洲尊，同樣被作為表顯示出來(lái)

>> 用戶只想查看當(dāng)前架構(gòu)下的基表远豺，可以通過(guò)information_schema架構(gòu)下的TABLE表來(lái)查詢，并搜索表類型為BASE TABLE的表

>> Oracle數(shù)據(jù)庫(kù)支持物化視圖——該視圖不是基于基表的虛表坞嘀，而是根據(jù)基表實(shí)際存在的實(shí)表躯护，即物化視圖的數(shù)據(jù)存儲(chǔ)在非易失的存儲(chǔ)設(shè)備上。物化視圖可以用于預(yù)先計(jì)算并保存多表的鏈接（JOIN）或聚集（GROUP BY）等耗時(shí)較多的SQL操作結(jié)果丽涩。這樣棺滞，在執(zhí)行復(fù)雜查詢時(shí)，就可以避免進(jìn)行這些耗時(shí)的操作矢渊，從而快速得到結(jié)果继准。物化視圖的好處是對(duì)于一些復(fù)雜的統(tǒng)計(jì)類查詢能直接查出結(jié)果。在Microsoft SQL Server數(shù)據(jù)庫(kù)中昆淡，稱這種視圖為索引視圖锰瘸。

>> 分區(qū)功能并不是在存儲(chǔ)引擎層完成的，因此不是只有InnoDB存儲(chǔ)引擎支持分區(qū)昂灵，常見的存儲(chǔ)引擎MyISAM避凝、NDB等都支持。但也并不是所有的存儲(chǔ)引擎都支持眨补，如CSV管削、FEDORATED、MERGE等就不支持撑螺。在使用分區(qū)功能前含思，應(yīng)該對(duì)選擇的存儲(chǔ)引擎對(duì)分區(qū)的支持有所了解。

>> MySQL數(shù)據(jù)庫(kù)支持的分區(qū)類型為水平分[插圖]甘晤，并不支持垂直分[插圖]含潘。此外，MySQL數(shù)據(jù)庫(kù)的分區(qū)是局部分區(qū)索引线婚，一個(gè)分區(qū)中既存放了數(shù)據(jù)又存放了索引遏弱。而全局分區(qū)是指，數(shù)據(jù)存放在各個(gè)分區(qū)中塞弊，但是所有數(shù)據(jù)的索引放在一個(gè)對(duì)象中漱逸。目前，MySQL數(shù)據(jù)庫(kù)還不支持全局分區(qū)游沿。

>> 不論創(chuàng)建何種類型的分區(qū)饰抒，如果表中存在主鍵或唯一索引時(shí)，分區(qū)列必須是唯一索引的一個(gè)組成部分

>> 唯一索引可以是允許NULL值的诀黍，并且分區(qū)列只要是唯一索引的一個(gè)組成部分袋坑，不需要整個(gè)唯一索引列都是分區(qū)列

>> 如果建表時(shí)沒有指定主鍵，唯一索引眯勾，可以指定任何一個(gè)列為分區(qū)列

>> 查看表在磁盤上的物理文件咒彤，啟用分區(qū)之后疆柔，表不再由一個(gè)ibd文件組成了，而是由建立分區(qū)時(shí)的各個(gè)分區(qū)ibd文件組成镶柱，如下面的t#P#p0.ibd，t#P#p1.ibd

>> 通過(guò)EXPLAIN PARTITION命令我們可以發(fā)現(xiàn)模叙，在上述語(yǔ)句中歇拆，SQL優(yōu)化器只需要去搜索p2008這個(gè)分區(qū)，而不會(huì)去搜索所有的分區(qū)——稱為Partition Pruning（分區(qū)修剪）范咨，故查詢的速度得到了大幅度的提

>> 在前面介紹的RANGE故觅、LIST、HASH和KEY這四種分區(qū)中渠啊，分區(qū)的條件是：數(shù)據(jù)必須是整型（interger）输吏，如果不是整型，那應(yīng)該需要通過(guò)函數(shù)將其轉(zhuǎn)化為整型替蛉，如YEAR（）贯溅，TO_DAYS（），MONTH（）等函數(shù)躲查。MySQL5.5版本開始支持COLUMNS分區(qū)它浅，可視為RANGE分區(qū)和LIST分區(qū)的一種進(jìn)化。COLUMNS分區(qū)可以直接使用非整型的數(shù)據(jù)進(jìn)行分區(qū)镣煮，分區(qū)根據(jù)類型直接比較而得姐霍，不需要轉(zhuǎn)化為整型枕磁。此外轧拄，RANGE COLUMNS分區(qū)可以對(duì)多個(gè)列的值進(jìn)行分區(qū)侠驯。

>> 子分區(qū)（subpartitioning）是在分區(qū)的基礎(chǔ)上再進(jìn)行分區(qū)亏钩，有時(shí)也稱這種分區(qū)為復(fù)合分區(qū)（composite partitioning）腻暮。MySQL數(shù)據(jù)庫(kù)允許在RANGE和LIST的分區(qū)上再進(jìn)行HASH或KEY的子分區(qū)

>> 子分區(qū)的建立需要注意以下幾個(gè)問(wèn)題：□ 每個(gè)子分區(qū)的數(shù)量必須相同剪勿☆砉眨□ 要在一個(gè)分區(qū)表的任何分區(qū)上使用SUBPARTITION來(lái)明確定義任何子分區(qū)航缀，就必須定義所有的子分區(qū)夜牡。

>> 子分區(qū)可以用于特別大的表与纽，在多個(gè)磁盤間分別分配數(shù)據(jù)和索引

>> MySQL數(shù)據(jù)庫(kù)允許對(duì)NULL值做分區(qū)，但是處理的方法與其他數(shù)據(jù)庫(kù)可能完全不同塘装。MYSQL數(shù)據(jù)庫(kù)的分區(qū)總是視NULL值視小于任何的一個(gè)非NULL值急迂，這和MySQL數(shù)據(jù)庫(kù)中處理NULL值的ORDER BY操作是一樣的

>> 。因此對(duì)于不同的分區(qū)類型蹦肴，MySQL數(shù)據(jù)庫(kù)對(duì)于NULL值的處理也是各不相同僚碎。

>> HASH和KEY分區(qū)對(duì)于NULL的處理方式和RANGE分區(qū)、LIST分區(qū)不一樣阴幌。任何分區(qū)函數(shù)都會(huì)將含有NULL值的記錄返回為0

>> 對(duì)于OLAP的應(yīng)用勺阐，分區(qū)的確是可以很好地提高查詢的性能卷中，因?yàn)镺LAP應(yīng)用大多數(shù)查詢需要頻繁地掃描一張很大的表。假設(shè)有一張1億行的表渊抽，其中有一個(gè)時(shí)間戳屬性列蟆豫。用戶的查詢需要從這張表中獲取一年的數(shù)據(jù)。如果按時(shí)間戳進(jìn)行分區(qū)懒闷，則只需要掃描相應(yīng)的分區(qū)即可十减。這就是前面介紹的Partition Pruning技術(shù)。

>> 然而對(duì)于OLTP的應(yīng)用愤估，分區(qū)應(yīng)該非常小心帮辟。在這種應(yīng)用下，通常不可能會(huì)獲取一張大表中10%的數(shù)據(jù)玩焰，大部分都是通過(guò)索引返回幾條記錄即可由驹。而根據(jù)B+樹索引的原理可知，對(duì)于一張大表昔园，一般的B+樹需要2～3次的磁盤IO蔓榄。因此B+樹可以很好地完成操作，不需要分區(qū)的幫助蒿赢，并且設(shè)計(jì)不好的分區(qū)會(huì)帶來(lái)嚴(yán)重的性能問(wèn)題润樱。

>> 我發(fā)現(xiàn)很多開發(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店展。但是有沒有考慮過(guò)這樣一種情況：100W和1000W行的數(shù)據(jù)本身構(gòu)成的B+樹的層次都是一樣的，可能都是2層秃流。

>> MySQL 5.6開始支持ALTER TABLE … EXCHANGE PARTITION語(yǔ)法赂蕴。該語(yǔ)句允許分區(qū)或子分區(qū)中的數(shù)據(jù)與另一個(gè)非分區(qū)的表中的數(shù)據(jù)進(jìn)行交換。如果非分區(qū)表中的數(shù)據(jù)為空舶胀，那么相當(dāng)于將分區(qū)中的數(shù)據(jù)移動(dòng)到非分區(qū)表中概说。若分區(qū)表中的數(shù)據(jù)為空，則相當(dāng)于將外部表中的數(shù)據(jù)導(dǎo)入到分區(qū)中嚣伐。

第5章 索引與算法

>> ?B+樹中的B不是代表二叉（binary）糖赔，而是代表平衡（balance），因?yàn)锽+樹是從最早的平衡二叉樹演化而來(lái)轩端，但是B+樹不是一個(gè)二叉樹放典。

>> 另一個(gè)常常被DBA忽視的問(wèn)題是：B+樹索引并不能找到一個(gè)給定鍵值的具體行。B+樹索引能找到的只是被查找數(shù)據(jù)行所在的頁(yè)。然后數(shù)據(jù)庫(kù)通過(guò)把頁(yè)讀入到內(nèi)存奋构，再在內(nèi)存中進(jìn)行查找壳影，最后得到要查找的數(shù)據(jù)。

>> 平衡二叉樹的查找性能是比較高的弥臼，但不是最高的宴咧，只是接近最高性能。最好的性能需要建立一棵最優(yōu)二叉樹醋火，但是最優(yōu)二叉樹的建立和維護(hù)需要大量的操作悠汽，因此，用戶一般只需建立一棵平衡二叉樹即可芥驳。

>> 平衡二叉樹的查詢速度的確很快，但是維護(hù)一棵平衡二叉樹的代價(jià)是非常大的茬高。通常來(lái)說(shuō)兆旬，需要1次或多次左旋和右旋來(lái)得到插入或更新后樹的平衡性。

>> B+樹由B樹和索引順序訪問(wèn)方法（ISAM怎栽，是不是很熟悉丽猬？對(duì)，這也是MyISAM引擎最初參考的數(shù)據(jù)結(jié)構(gòu)）演化而來(lái)熏瞄，但是在現(xiàn)實(shí)使用過(guò)程中幾乎已經(jīng)沒有使用B樹的情況了脚祟。

>> B+樹是為磁盤或其他直接存取輔助設(shè)備設(shè)計(jì)的一種平衡查找樹。在B+樹中强饮，所有記錄節(jié)點(diǎn)都是按鍵值的大小順序存放在同一層的葉子節(jié)點(diǎn)上由桌，由各葉子節(jié)點(diǎn)指針進(jìn)行連接。

>> 可以看到邮丰，不管怎么變化行您，B+樹總是會(huì)保持平衡。但是為了保持平衡對(duì)于新插入的鍵值可能需要做大量的拆分頁(yè)（split）操作剪廉。因?yàn)锽+樹結(jié)構(gòu)主要用于磁盤娃循，頁(yè)的拆分意味著磁盤的操作，所以應(yīng)該在可能的情況下盡量減少頁(yè)的拆分操作斗蒋。

>> 因此捌斧，B+樹同樣提供了類似于平衡二叉樹的旋轉(zhuǎn)（Rotation）功能。

>> B+樹索引的本質(zhì)就是B+樹在數(shù)據(jù)庫(kù)中的實(shí)現(xiàn)泉沾。

>> 但是B+索引在數(shù)據(jù)庫(kù)中有一個(gè)特點(diǎn)是高扇出性捞蚂，因此在數(shù)據(jù)庫(kù)中，B+樹的高度一般都在2～4層爆哑，這也就是說(shuō)查找某一鍵值的行記錄時(shí)最多只需要2到4次IO洞难，這倒不錯(cuò)。因?yàn)楫?dāng)前一般的機(jī)械磁盤每秒至少可以做100次IO，2～4次的IO意味著查詢時(shí)間只需0.02～0.04秒队贱。

>> 數(shù)據(jù)庫(kù)中的B+樹索引可以分為聚集索引（clustered inex）和輔助索引（secondary index[插圖]色冀，但是不管是聚集還是輔助的索引，其內(nèi)部都是B+樹的柱嫌，即高度平衡的锋恬，葉子節(jié)點(diǎn)存放著所有的數(shù)據(jù)。聚集索引與輔助索引不同的是编丘，葉子節(jié)點(diǎn)存放的是否是一整行的信息与学。

>> 聚集索引（clustered index）就是按照每張表的主鍵構(gòu)造一棵B+樹，同時(shí)葉子節(jié)點(diǎn)中存放的即為整張表的行記錄數(shù)據(jù)嘉抓，也將聚集索引的葉子節(jié)點(diǎn)稱為數(shù)據(jù)頁(yè)索守。

>> 由于實(shí)際的數(shù)據(jù)頁(yè)只能按照一棵B+樹進(jìn)行排序，因此每張表只能擁有一個(gè)聚集索引

>> 許多數(shù)據(jù)庫(kù)的文檔會(huì)這樣告訴讀者：聚集索引按照順序物理地存儲(chǔ)數(shù)據(jù)抑片。如果看圖5-14卵佛，可能也會(huì)有這樣的感覺。但是試想一下敞斋，如果聚集索引必須按照特定順序存放物理記錄截汪，則維護(hù)成本顯得非常之高。所以植捎，聚集索引的存儲(chǔ)并不是物理上連續(xù)的衙解，而是邏輯上連續(xù)的。

>> 對(duì)于輔助索引（Secondary Index焰枢，也稱非聚集索引）蚓峦，葉子節(jié)點(diǎn)并不包含行記錄的全部數(shù)據(jù)。葉子節(jié)點(diǎn)除了包含鍵值以外医咨，每個(gè)葉子節(jié)點(diǎn)中的索引行中還包含了一個(gè)書簽（bookmark）枫匾。該書簽用來(lái)告訴InnoDB存儲(chǔ)引擎哪里可以找到與索引相對(duì)應(yīng)的行數(shù)據(jù)

>> 。由于InnoDB存儲(chǔ)引擎表是索引組織表拟淮，因此InnoDB存儲(chǔ)引擎的輔助索引的書簽就是相應(yīng)行數(shù)據(jù)的聚集索引鍵干茉。

>> 如果在一棵高度為3的輔助索引樹中查找數(shù)據(jù)，那需要對(duì)這棵輔助索引樹遍歷3次找到指定主鍵很泊，如果聚集索引樹的高度同樣為3角虫，那么還需要對(duì)聚集索引樹進(jìn)行3次查找，最終找到一個(gè)完整的行數(shù)據(jù)所在的頁(yè)委造，因此一共需要6次邏輯IO訪問(wèn)以得到最終的一個(gè)數(shù)據(jù)頁(yè)戳鹅。

>> 用戶可以設(shè)置對(duì)整個(gè)列的數(shù)據(jù)進(jìn)行索引，也可以只索引一個(gè)列的開頭部分?jǐn)?shù)據(jù)昏兆，如前面創(chuàng)建的表t枫虏，列b為varchar（8000），但是用戶可以只索引前100個(gè)字段

>> ?Cardinality：非常關(guān)鍵的值，表示索引中唯一值的數(shù)目的估計(jì)值隶债。Cardinality表的行數(shù)應(yīng)盡可能接近1腾它，如果非常小，那么用戶需要考慮是否可以刪除此索引死讹。

>> Cardinality值非常關(guān)鍵瞒滴，優(yōu)化器會(huì)根據(jù)這個(gè)值來(lái)判斷是否使用這個(gè)索引。但是這個(gè)值并不是實(shí)時(shí)更新的赞警，即并非每次索引的更新都會(huì)更新該值妓忍，因?yàn)檫@樣代價(jià)太大了

>> Cardinality為NULL，在某些情況下可能會(huì)發(fā)生索引建立了卻沒有用到的情況愧旦∈榔剩或者對(duì)兩條基本一樣的語(yǔ)句執(zhí)行EXPLAIN，但是最終出來(lái)的結(jié)果不一樣：一個(gè)使用索引笤虫，另外一個(gè)使用全表掃描搁廓。這時(shí)最好的解決辦法就是做一次ANALYZE TABLE的操作。

>> MySQL 5.5版本之前（不包括5.5）存在的一個(gè)普遍被人詬病的問(wèn)題是MySQL數(shù)據(jù)庫(kù)對(duì)于索引的添加或者刪除的這類DDL操作耕皮，MySQL數(shù)據(jù)庫(kù)的操作過(guò)程為：

>> InnoDB存儲(chǔ)引擎從InnoDB 1.0.x版本開始支持一種稱為Fast Index Creation（快速索引創(chuàng)建）的索引創(chuàng)建方式——簡(jiǎn)稱FIC。

>> 對(duì)于輔助索引的創(chuàng)建蝙场，InnoDB存儲(chǔ)引擎會(huì)對(duì)創(chuàng)建索引的表加上一個(gè)S鎖凌停。在創(chuàng)建的過(guò)程中，不需要重建表售滤，因此速度較之前提高很多罚拟，并且數(shù)據(jù)庫(kù)的可用性也得到了提高。刪除輔助索引操作就更簡(jiǎn)單了完箩，InnoDB存儲(chǔ)引擎只需更新內(nèi)部視圖赐俗，并將輔助索引的空間標(biāo)記為可用，同時(shí)刪除MySQL數(shù)據(jù)庫(kù)內(nèi)部視圖上對(duì)該表的索引定義即可弊知。這里需要特別注意的是阻逮，臨時(shí)表的創(chuàng)建路徑是通過(guò)參數(shù)tmpdir進(jìn)行設(shè)置的。用戶必須保證tmpdir有足夠的空間可以存放臨時(shí)表秩彤，否則會(huì)導(dǎo)致創(chuàng)建索引失敗叔扼。

>> 由于FIC在索引的創(chuàng)建的過(guò)程中對(duì)表加上了S鎖，因此在創(chuàng)建的過(guò)程中只能對(duì)該表進(jìn)行讀操作漫雷，若有大量的事務(wù)需要對(duì)目標(biāo)表進(jìn)行寫操作瓜富，那么數(shù)據(jù)庫(kù)的服務(wù)同樣不可用

>> Facebook采用PHP腳本來(lái)現(xiàn)實(shí)OSC，而并不是通過(guò)修改InnoDB存儲(chǔ)引擎源碼的方式降盹。OSC最初由Facebook的員工Vamsi Ponnekanti開發(fā)与柑。此外，OSC借鑒了開源社區(qū)之前的工具The openarkkit toolkit oak-online-alter-table。實(shí)現(xiàn)OSC步驟如下：

>> MySQL 5.6版本開始支持Online DDL（在線數(shù)據(jù)定義）操作价捧，其允許輔助索引創(chuàng)建的同時(shí)丑念，還允許其他諸如INSERT、UPDATE干旧、DELETE這類DML操作渠欺，這極大地提高了MySQL數(shù)據(jù)庫(kù)在生產(chǎn)環(huán)境中的可用性。

>> LOCK部分為索引創(chuàng)建或刪除時(shí)對(duì)表添加鎖的情況椎眯，可有的選擇為：

>> InnoDB存儲(chǔ)引擎實(shí)現(xiàn)Online DDL的原理是在執(zhí)行創(chuàng)建或者刪除操作的同時(shí)挠将，將INSERT、UPDATE编整、DELETE這類DML操作日志寫入到一個(gè)緩存中舔稀。待完成索引創(chuàng)建后再將重做應(yīng)用到表上，以此達(dá)到數(shù)據(jù)的一致性掌测。這個(gè)緩存的大小由參數(shù)innodb_online_alter_log_max_size控制内贮，默認(rèn)的大小為128MB。若用戶更新的表比較大汞斧，并且在創(chuàng)建過(guò)程中伴有大量的寫事務(wù)夜郁，如遇到innodb_online_alter_log_max_size的空間不能存放日志時(shí)，會(huì)拋出類似如下的錯(cuò)誤：

>> 對(duì)于這個(gè)錯(cuò)誤粘勒，用戶可以調(diào)大參數(shù)innodb_online_alter_log_max_size竞端，以此獲得更大的日志緩存空間。此外庙睡，還可以設(shè)置ALTER TABLE的模式為SHARE事富，這樣在執(zhí)行過(guò)程中不會(huì)有寫事務(wù)發(fā)生，因此不需要進(jìn)行DML日志的記錄乘陪。

>> 如果某個(gè)字段的取值范圍很廣统台，幾乎沒有重復(fù)，即屬于高選擇性啡邑，則此時(shí)使用B+樹索引是最適合的贱勃。例如，對(duì)于姓名字段谣拣，基本上在一個(gè)應(yīng)用中不允許重名的出現(xiàn)募寨。

>> 在InnoDB存儲(chǔ)引擎中，Cardinality統(tǒng)計(jì)信息的更新發(fā)生在兩個(gè)操作中：INSERT和UPDATE森缠。

>> 聯(lián)合索引的第二個(gè)好處是已經(jīng)對(duì)第二個(gè)鍵值進(jìn)行了排序處理拔鹰。例如，在很多情況下應(yīng)用程序都需要查詢某個(gè)用戶的購(gòu)物情況贵涵，并按照時(shí)間進(jìn)行排序列肢，最后取出最近三次的購(gòu)買記錄恰画，這時(shí)使用聯(lián)合索引可以避免多一次的排序操作，因?yàn)樗饕旧碓谌~子節(jié)點(diǎn)已經(jīng)排序了瓷马。

>> 正如前面所介紹的那樣拴还，聯(lián)合索引（a，b）其實(shí)是根據(jù)列a欧聘、b進(jìn)行排序片林，因此下列語(yǔ)句可以直接使用聯(lián)合索引得到結(jié)果： ? ?SELECT ... FROM TABLE WHERE a=xxx ORDER BY b

>> InnoDB存儲(chǔ)引擎支持覆蓋索引（covering index，或稱索引覆蓋）怀骤，即從輔助索引中就可以得到查詢的記錄费封，而不需要查詢聚集索引中的記錄。使用覆蓋索引的一個(gè)好處是輔助索引不包含整行記錄的所有信息蒋伦，故其大小要遠(yuǎn)小于聚集索引弓摘，因此可以減少大量的IO操作。

>> 覆蓋索引的另一個(gè)好處是對(duì)某些統(tǒng)計(jì)問(wèn)題而言的

>> 表buy_log有（userid痕届，buy_date）的聯(lián)合索引韧献，這里只根據(jù)列b進(jìn)行條件查詢，一般情況下是不能進(jìn)行該聯(lián)合索引的研叫，但是這句SQL查詢是統(tǒng)計(jì)操作锤窑，并且可以利用到覆蓋索引的信息，因此優(yōu)化器會(huì)選擇該聯(lián)合索引

>> 這是為什么呢嚷炉？原因在于用戶要選取的數(shù)據(jù)是整行信息果复，而OrderID索引不能覆蓋到我們要查詢的信息，因此在對(duì)OrderID索引查詢到指定數(shù)據(jù)后渤昌，還需要一次書簽訪問(wèn)來(lái)查找整行數(shù)據(jù)的信息。雖然OrderID索引中數(shù)據(jù)是順序存放的走搁，但是再一次進(jìn)行書簽查找的數(shù)據(jù)則是無(wú)序的独柑，因此變?yōu)榱舜疟P上的離散讀操作。如果要求訪問(wèn)的數(shù)據(jù)量很小私植，則優(yōu)化器還是會(huì)選擇輔助索引忌栅，但是當(dāng)訪問(wèn)的數(shù)據(jù)占整個(gè)表中數(shù)據(jù)的蠻大一部分時(shí)（一般是20%左右），優(yōu)化器會(huì)選擇通過(guò)聚集索引來(lái)查找數(shù)據(jù)曲稼。因?yàn)橹耙呀?jīng)提到過(guò)索绪，順序讀要遠(yuǎn)遠(yuǎn)快于離散讀。

>> 因此對(duì)于不能進(jìn)行索引覆蓋的情況贫悄，優(yōu)化器選擇輔助索引的情況是瑞驱，通過(guò)輔助索引查找的數(shù)據(jù)是少量的。這是由當(dāng)前傳統(tǒng)機(jī)械硬盤的特性所決定的窄坦，即利用順序讀來(lái)替換隨機(jī)讀的查找唤反。若用戶使用的磁盤是固態(tài)硬盤凳寺，隨機(jī)讀操作非常快彤侍，同時(shí)有足夠的自信來(lái)確認(rèn)使用輔助索引可以帶來(lái)更好的性能肠缨，那么可以使用關(guān)鍵字FORCE INDEX來(lái)強(qiáng)制使用某個(gè)索引

>> 因此，USE INDEX只是告訴優(yōu)化器可以選擇該索引盏阶，實(shí)際上優(yōu)化器還是會(huì)再根據(jù)自己的判斷進(jìn)行選擇晒奕。而如果使用FORCE INDEX的索引提示，如：

>> MySQL5.6版本開始支持Multi-Range Read（MRR）優(yōu)化名斟。Multi-Range Read優(yōu)化的目的就是為了減少磁盤的隨機(jī)訪問(wèn)脑慧，并且將隨機(jī)訪問(wèn)轉(zhuǎn)化為較為順序的數(shù)據(jù)訪問(wèn)，這對(duì)于IO-bound類型的SQL查詢語(yǔ)句可帶來(lái)性能極大的提升蒸眠。

>> MRR的工作方式如下：□ 將查詢得到的輔助索引鍵值存放于一個(gè)緩存中漾橙，這時(shí)緩存中的數(shù)據(jù)是根據(jù)輔助索引鍵值排序的±憧ǎ□ 將緩存中的鍵值根據(jù)RowID進(jìn)行排序霜运。□ 根據(jù)RowID的排序順序來(lái)訪問(wèn)實(shí)際的數(shù)據(jù)文件蒋腮。

>> 之前的MySQL數(shù)據(jù)庫(kù)版本不支持Index Condition Pushdown淘捡，當(dāng)進(jìn)行索引查詢時(shí)，首先根據(jù)索引來(lái)查找記錄池摧，然后再根據(jù)WHERE條件來(lái)過(guò)濾記錄焦除。在支持Index Condition Pushdown后，MySQL數(shù)據(jù)庫(kù)會(huì)在取出索引的同時(shí)作彤，判斷是否可以進(jìn)行WHERE條件的過(guò)濾膘魄，也就是將WHERE的部分過(guò)濾操作放在了存儲(chǔ)引擎層

>> 當(dāng)優(yōu)化器選擇Index Condition Pushdown優(yōu)化時(shí)，可在執(zhí)行計(jì)劃的列Extra看到Using index condition提示竭讳。

>> 自適應(yīng)哈希索引采用之前討論的哈希表的方式實(shí)現(xiàn)创葡。不同的是，這僅是數(shù)據(jù)庫(kù)自身創(chuàng)建并使用的绢慢，DBA本身并不能對(duì)其進(jìn)行干預(yù)灿渴。自適應(yīng)哈希索引經(jīng)哈希函數(shù)映射到一個(gè)哈希表中，因此對(duì)于字典類型的查找非骋扔撸快速

>> 全文檢索通常使用倒排索引（inverted index）來(lái)實(shí)現(xiàn)骚露。倒排索引同B+樹索引一樣，也是一種索引結(jié)構(gòu)缚窿。它在輔助表（auxiliary table）中存儲(chǔ)了單詞與單詞自身在一個(gè)或多個(gè)文檔中所在位置之間的映射棘幸。這通常利用關(guān)聯(lián)數(shù)組實(shí)現(xiàn)，其擁有兩種表現(xiàn)形式：

>> full inverted index還存儲(chǔ)了單詞所在的位置信息倦零，如code這個(gè)單詞出現(xiàn)在（1∶6）够话，即文檔1的第6個(gè)單詞為code蓝翰。相比之下，full inverted index占用更多的空間女嘲，但是能更好地定位數(shù)據(jù)畜份，并擴(kuò)充一些其他的搜索特性。

>> InnoDB存儲(chǔ)引擎從1.2.x版本開始支持全文檢索的技術(shù)欣尼，其采用full inverted index的方式

>> 爆雹。在InnoDB存儲(chǔ)引擎中，將（DocumentId愕鼓，Position）視為一個(gè)“ilist”钙态。因此在全文檢索的表中，有兩個(gè)列菇晃，一個(gè)是word字段册倒，另一個(gè)是ilist字段，并且在word字段上有設(shè)有索引

>> 正如之前所說(shuō)的那樣磺送，倒排索引需要將word存放到一張表中驻子，這個(gè)表稱為Auxiliary Table（輔助表）。在InnoDB存儲(chǔ)引擎中估灿，為了提高全文檢索的并行性能崇呵，共有6張Auxiliary Table，目前每張表根據(jù)word的Latin編碼進(jìn)行分區(qū)馅袁。

>> Auxiliary Table是持久的表域慷，存放于磁盤上。然而在InnoDB存儲(chǔ)引擎的全文索引中汗销，還有另外一個(gè)重要的概念FTS Index Cache（全文檢索索引緩存）犹褒，其用來(lái)提高全文檢索的性能。FTS Index Cache是一個(gè)紅黑樹結(jié)構(gòu)弛针，其根據(jù)（word化漆，ilist）進(jìn)行排序。這意味著插入的數(shù)據(jù)已經(jīng)更新了對(duì)應(yīng)的表钦奋，但是對(duì)全文索引的更新可能在分詞操作后還在FTS Index Cache中蹬耘，Auxiliary Table可能還沒有更新着撩。InnoDB存儲(chǔ)引擎會(huì)批量對(duì)Auxiliary Table進(jìn)行更新，而不是每次插入后更新一次Auxiliary Table

>> FTS Document ID是另外一個(gè)重要的概念我磁。在InnoDB存儲(chǔ)引擎中圃阳，為了支持全文檢索厌衔，必須有一個(gè)列與word進(jìn)行映射，在InnoDB中這個(gè)列被命名為FTS_DOC_ID捍岳，其類型必須是BIGINT UNSIGNED NOT NULL富寿，并且InnoDB存儲(chǔ)引擎自動(dòng)會(huì)在該列上加入一個(gè)名為FTS_DOC_ID_INDEX的Unique Index

>> 文檔中分詞的插入操作是在事務(wù)提交時(shí)完成睬隶，然而對(duì)于刪除操作，其在事務(wù)提交時(shí)页徐，不刪除磁盤Auxiliary Table中的記錄苏潜，而只是刪除FTS Cache Index中的記錄

>> 由于文檔的DML操作實(shí)際并不刪除索引中的數(shù)據(jù)，相反還會(huì)在對(duì)應(yīng)的DELETED表中插入記錄变勇，因此隨著應(yīng)用程序的允許恤左，索引會(huì)變得非常大，即使索引中的有些數(shù)據(jù)已經(jīng)被刪除搀绣，查詢也不會(huì)選擇這類記錄飞袋。

>> 通過(guò)設(shè)置參數(shù)innodb_ft_aux_table來(lái)查看分詞對(duì)應(yīng)的信息：

>> SELECT * FROM information_schema.INNODB_FT_INDEX_TABLE;

>> 當(dāng)前InnoDB存儲(chǔ)引擎的全文檢索還存在以下的限制：□ 每張表只能有一個(gè)全文檢索的索引×椿迹□ 由多列組合而成的全文檢索的索引列必須使用相同的字符集與排序規(guī)則巧鸭。□ 不支持沒有單詞界定符（delimiter）的語(yǔ)言麻捻，如中文纲仍、日語(yǔ)、韓語(yǔ)等芯肤。

第6章 鎖

>> 鎖是數(shù)據(jù)庫(kù)系統(tǒng)區(qū)別于文件系統(tǒng)的一個(gè)關(guān)鍵特性巷折。鎖機(jī)制用于管理對(duì)共享資源的并發(fā)訪[插圖]

>> 2005版本，Microsoft SQL Server開始支持樂觀并發(fā)和悲觀并發(fā)崖咨，在樂觀并發(fā)下開始支持行級(jí)鎖锻拘，但是其實(shí)現(xiàn)方式與InnoDB存儲(chǔ)引擎的實(shí)現(xiàn)方式完全不同。用戶會(huì)發(fā)現(xiàn)在Microsoft SQL Server下击蹲，鎖是一種稀有的資源署拟，鎖越多開銷就越大，因此它會(huì)有鎖升級(jí)歌豺。在這種情況下推穷，行鎖會(huì)升級(jí)到表鎖，這時(shí)并發(fā)的性能又回到了以前类咧。

>> latch一般稱為閂鎖（輕量級(jí)的鎖）馒铃，因?yàn)槠湟箧i定的時(shí)間必須非常短。若持續(xù)的時(shí)間長(zhǎng)痕惋，則應(yīng)用的性能會(huì)非常差区宇。在InnoDB存儲(chǔ)引擎中，latch又可以分為mutex（互斥量）和rwlock（讀寫鎖）值戳。其目的是用來(lái)保證并發(fā)線程操作臨界資源的正確性议谷，并且通常沒有死鎖檢測(cè)的機(jī)制。

>> 對(duì)于InnoDB存儲(chǔ)引擎中的latch堕虹，可以通過(guò)命令SHOW ENGINE INNODB MUTEX來(lái)進(jìn)行查看

>> 相對(duì)于latch的查看卧晓，lock信息就顯得直觀多了芬首。用戶可以通過(guò)命令SHOW ENGINE INNODB STATUS及information_schema架構(gòu)下的表INNODB_TRX、INNODB_LOCKS逼裆、INNODB_LOCK_WAITS來(lái)觀察鎖的信息

>> 如果一個(gè)事務(wù)T1已經(jīng)獲得了行r的共享鎖郁稍，那么另外的事務(wù)T2可以立即獲得行r的共享鎖，因?yàn)樽x取并沒有改變行r的數(shù)據(jù)波附，稱這種情況為鎖兼容（Lock Compatible）艺晴。但若有其他的事務(wù)T3想獲得行r的排他鎖，則其必須等待事務(wù)T1掸屡、T2釋放行r上的共享鎖——這種情況稱為鎖不兼容封寞。

>> S和X鎖都是行鎖，兼容是指對(duì)同一記錄（row）鎖的兼容性情況仅财。

>> nnoDB存儲(chǔ)引擎支持意向鎖設(shè)計(jì)比較簡(jiǎn)練狈究，其意向鎖即為表級(jí)別的鎖。設(shè)計(jì)目的主要是為了在一個(gè)事務(wù)中揭示下一行將被請(qǐng)求的鎖類型盏求。

>> 在InnoDB 1.0版本之前抖锥，用戶只能通過(guò)命令SHOW FULL PROCESSLIST，SHOW ENGINE INNODB STATUS等來(lái)查看當(dāng)前數(shù)據(jù)庫(kù)中鎖的請(qǐng)求碎罚，然后再判斷事務(wù)鎖的情況磅废。從InnoDB1.0開始，在INFORMATION_SCHEMA架構(gòu)下添加了表INNODB_TRX荆烈、INNODB_LOCKS拯勉、INNODB_LOCK_WAITS。通過(guò)這三張表憔购，用戶可以更簡(jiǎn)單地監(jiān)控當(dāng)前事務(wù)并分析可能存在的鎖問(wèn)題

>> 在通過(guò)表INNODB_LOCKS查看了每張表上鎖的情況后宫峦，用戶就可以來(lái)判斷由此引發(fā)的等待情況了。當(dāng)事務(wù)較小時(shí)玫鸟，用戶就可以人為地导绷、直觀地進(jìn)行判斷了。但是當(dāng)事務(wù)量非常大屎飘，其中鎖和等待也時(shí)常發(fā)生妥曲，這個(gè)時(shí)候就不這么容易判斷。但是通過(guò)表INNODB_LOCK_WAITS钦购，可以很直觀地反映當(dāng)前事務(wù)的等待

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

>> 之所以稱其為非鎖定讀怨规，因?yàn)椴恍枰却L問(wèn)的行上X鎖的釋放陌宿。快照數(shù)據(jù)是指該行的之前版本的數(shù)據(jù)波丰，該實(shí)現(xiàn)是通過(guò)undo段來(lái)完成壳坪。而undo用來(lái)在事務(wù)中回滾數(shù)據(jù)，因此快照數(shù)據(jù)本身是沒有額外的開銷

>> 非鎖定讀機(jī)制極大地提高了數(shù)據(jù)庫(kù)的并發(fā)性掰烟。在InnoDB存儲(chǔ)引擎的默認(rèn)設(shè)置下爽蝴，這是默認(rèn)的讀取方式，即讀取不會(huì)占用和等待表上的鎖纫骑。但是在不同事務(wù)隔離級(jí)別下蝎亚，讀取的方式不同，并不是在每個(gè)事務(wù)隔離級(jí)別下都是采用非鎖定的一致性讀先馆。此外发框，即使都是使用非鎖定的一致性讀，但是對(duì)于快照數(shù)據(jù)的定義也各不相同煤墙。

>> 一個(gè)行記錄可能有不止一個(gè)快照數(shù)據(jù)梅惯，一般稱這種技術(shù)為行多版本技術(shù)。由此帶來(lái)的并發(fā)控制仿野，稱之為多版本并發(fā)控制（Multi Version Concurrency Control铣减，MVCC）。

>> 在事務(wù)隔離級(jí)別READ COMMITTED和REPEATABLE READ（InnoDB存儲(chǔ)引擎的默認(rèn)事務(wù)隔離級(jí)別）下脚作，InnoDB存儲(chǔ)引擎使用非鎖定的一致性讀葫哗。然而，對(duì)于快照數(shù)據(jù)的定義卻不相同鳖枕。在READ COMMITTED事務(wù)隔離級(jí)別下魄梯，對(duì)于快照數(shù)據(jù)，非一致性讀總是讀取被鎖定行的最新一份快照數(shù)據(jù)宾符。而在REPEATABLE READ事務(wù)隔離級(jí)別下酿秸，對(duì)于快照數(shù)據(jù)，非一致性讀總是讀取事務(wù)開始時(shí)的行數(shù)據(jù)版本

>> 需要特別注意的是魏烫，對(duì)于READ COMMITTED的事務(wù)隔離級(jí)別而言辣苏，從數(shù)據(jù)庫(kù)理論的角度來(lái)看，其違反了事務(wù)ACID中的I的特性哄褒，即隔離性

>> 但是在某些情況下稀蟋，用戶需要顯式地對(duì)數(shù)據(jù)庫(kù)讀取操作進(jìn)行加鎖以保證數(shù)據(jù)邏輯的一致性。而這要求數(shù)據(jù)庫(kù)支持加鎖語(yǔ)句呐赡，即使是對(duì)于SELECT的只讀操作退客。InnoDB存儲(chǔ)引擎對(duì)于SELECT語(yǔ)句支持兩種一致性的鎖定讀（locking read）操作：□ SELECT…FOR UPDATE□ SELECT…LOCK IN SHARE MODE

>> SELECT…FOR UPDATE對(duì)讀取的行記錄加一個(gè)X鎖，其他事務(wù)不能對(duì)已鎖定的行加上任何鎖。SELECT…LOCK IN SHARE MODE對(duì)讀取的行記錄加一個(gè)S鎖萌狂，其他事務(wù)可以向被鎖定的行加S鎖档玻，但是如果加X鎖，則會(huì)被阻塞茫藏。

>> 對(duì)于一致性非鎖定讀误趴，即使讀取的行已被執(zhí)行了SELECT…FOR UPDATE，也是可以進(jìn)行讀取的务傲，這和之前討論的情況一樣凉当。此外，SELECT…FOR UPDATE售葡，SELECT…LOCK IN SHARE MODE必須在一個(gè)事務(wù)中看杭，當(dāng)事務(wù)提交了，鎖也就釋放了天通。

>> 插入操作會(huì)依據(jù)這個(gè)自增長(zhǎng)的計(jì)數(shù)器值加1賦予自增長(zhǎng)列泊窘。這個(gè)實(shí)現(xiàn)方式稱做AUTO-INC Locking。這種鎖其實(shí)是采用一種特殊的表鎖機(jī)制像寒，為了提高插入的性能烘豹，鎖不是在一個(gè)事務(wù)完成后才釋放，而是在完成對(duì)自增長(zhǎng)值插入的SQL語(yǔ)句后立即釋放

>> 雖然AUTO-INC Locking從一定程度上提高了并發(fā)插入的效率诺祸，但還是存在一些性能上的問(wèn)題携悯。首先，對(duì)于有自增長(zhǎng)值的列的并發(fā)插入性能較差筷笨，事務(wù)必須等待前一個(gè)插入的完成（雖然不用等待事務(wù)的完成）憔鬼。其次，對(duì)于INSERT…SELECT的大數(shù)據(jù)量的插入會(huì)影響插入的性能胃夏，因?yàn)榱硪粋€(gè)事務(wù)中的插入會(huì)被阻塞轴或。

>> 從MySQL 5.1.22版本開始，InnoDB存儲(chǔ)引擎中提供了一種輕量級(jí)互斥量的自增長(zhǎng)實(shí)現(xiàn)機(jī)制仰禀，這種機(jī)制大大提高了自增長(zhǎng)值插入的性能照雁。

>> 還需要特別注意的是InnoDB存儲(chǔ)引擎中自增長(zhǎng)的實(shí)現(xiàn)和MyISAM不同，MyISAM存儲(chǔ)引擎是表鎖設(shè)計(jì)答恶，自增長(zhǎng)不用考慮并發(fā)插入的問(wèn)題饺蚊。因此在master上用InnoDB存儲(chǔ)引擎，在slave上用MyISAM存儲(chǔ)引擎的replication架構(gòu)下悬嗓，用戶必須考慮這種情況污呼。

>> InnoDB存儲(chǔ)引擎有3種行鎖的算法，其分別是：□ Record Lock：?jiǎn)蝹€(gè)行記錄上的鎖□ Gap Lock：間隙鎖包竹，鎖定一個(gè)范圍燕酷，但不包含記錄本身□ Next-Key Lock∶Gap Lock+Record Lock籍凝，鎖定一個(gè)范圍，并且鎖定記錄本身

>> 當(dāng)查詢的索引含有唯一屬性時(shí)苗缩，InnoDB存儲(chǔ)引擎會(huì)對(duì)Next-Key Lock進(jìn)行優(yōu)化静浴，將其降級(jí)為Record Lock，即僅鎖住索引本身挤渐，而不是范圍。

>> InnoDB存儲(chǔ)引擎默認(rèn)的事務(wù)隔離級(jí)別是REPEATABLE READ双絮，在該隔離級(jí)別下浴麻，其采用Next-Key Locking的方式來(lái)加鎖。而在事務(wù)隔離級(jí)別READ COMMITTED下囤攀，其僅采用Record Lock软免，因此在上述的示例中，會(huì)話A需要將事務(wù)的隔離級(jí)別設(shè)置為READ COMMITTED焚挠。

>> 不可重復(fù)讀是指在一個(gè)事務(wù)內(nèi)多次讀取同一數(shù)據(jù)集合膏萧。在這個(gè)事務(wù)還沒有結(jié)束時(shí)，另外一個(gè)事務(wù)也訪問(wèn)該同一數(shù)據(jù)集合蝌衔，并做了一些DML操作榛泛。因此，在第一個(gè)事務(wù)中的兩次讀數(shù)據(jù)之間噩斟，由于第二個(gè)事務(wù)的修改曹锨，那么第一個(gè)事務(wù)兩次讀到的數(shù)據(jù)可能是不一樣的。這樣就發(fā)生了在一個(gè)事務(wù)內(nèi)兩次讀到的數(shù)據(jù)是不一樣的情況剃允，這種情況稱為不可重復(fù)讀沛简。

>> 不可重復(fù)讀和臟讀的區(qū)別是：臟讀是讀到未提交的數(shù)據(jù)，而不可重復(fù)讀讀到的卻是已經(jīng)提交的數(shù)據(jù)斥废，但是其違反了數(shù)據(jù)庫(kù)事務(wù)一致性的要求椒楣。

>> 在MySQL官方文檔中將不可重復(fù)讀的問(wèn)題定義為Phantom Problem，即幻像問(wèn)題牡肉。

>> 丟失更新是另一個(gè)鎖導(dǎo)致的問(wèn)題捧灰，簡(jiǎn)單來(lái)說(shuō)其就是一個(gè)事務(wù)的更新操作會(huì)被另一個(gè)事務(wù)的更新操作所覆蓋，從而導(dǎo)致數(shù)據(jù)的不一致荚板。

>> 因?yàn)椴煌i之間的兼容性關(guān)系凤壁，在有些時(shí)刻一個(gè)事務(wù)中的鎖需要等待另一個(gè)事務(wù)中的鎖釋放它所占用的資源，這就是阻塞跪另。阻塞并不是一件壞事拧抖，其是為了確保事務(wù)可以并發(fā)且正常地運(yùn)行。

>> 在InnoDB存儲(chǔ)引擎中免绿，參數(shù)innodb_lock_wait_timeout用來(lái)控制等待的時(shí)間（默認(rèn)是50秒）唧席，innodb_rollback_on_timeout用來(lái)設(shè)定是否在等待超時(shí)時(shí)對(duì)進(jìn)行中的事務(wù)進(jìn)行回滾操作（默認(rèn)是OFF，代表不回滾）。

Spring應(yīng)該是會(huì)幫我們處理回滾的淌哟。自己用JDBC寫我們捕獲異常后迹卢，一般也會(huì)手動(dòng)回滾。

>需要牢記的是徒仓，在默認(rèn)情況下InnoDB存儲(chǔ)引擎不會(huì)回滾超時(shí)引發(fā)的錯(cuò)誤異常腐碱。其實(shí)InnoDB存儲(chǔ)引擎在大部分情況下都不會(huì)對(duì)異常進(jìn)行回滾。

>> 解決死鎖問(wèn)題最簡(jiǎn)單的方式是不要有等待掉弛，將任何的等待都轉(zhuǎn)化為回滾症见，并且事務(wù)重新開始。

>> 毫無(wú)疑問(wèn)殃饿，這的確可以避免死鎖問(wèn)題的產(chǎn)生谋作。然而在線上環(huán)境中，這可能導(dǎo)致并發(fā)性能的下降乎芳，甚至任何一個(gè)事務(wù)都不能進(jìn)行遵蚜。而這所帶來(lái)的問(wèn)題遠(yuǎn)比死鎖問(wèn)題更為嚴(yán)重，因?yàn)檫@很難被發(fā)現(xiàn)并且浪費(fèi)資源奈惑。

>> 解決死鎖問(wèn)題最簡(jiǎn)單的一種方法是超時(shí)吭净，即當(dāng)兩個(gè)事務(wù)互相等待時(shí)，當(dāng)一個(gè)等待時(shí)間超過(guò)設(shè)置的某一閾值時(shí)肴甸，其中一個(gè)事務(wù)進(jìn)行回滾攒钳，另一個(gè)等待的事務(wù)就能繼續(xù)進(jìn)行。在InnoDB存儲(chǔ)引擎中雷滋，參數(shù)innodb_lock_wait_timeout用來(lái)設(shè)置超時(shí)的時(shí)間不撑。

>> 因此，除了超時(shí)機(jī)制晤斩，當(dāng)前數(shù)據(jù)庫(kù)還都普遍采用wait-for graph（等待圖）的方式來(lái)進(jìn)行死鎖檢測(cè)焕檬。較之超時(shí)的解決方案，這是一種更為主動(dòng)的死鎖檢測(cè)方式澳泵。InnoDB存儲(chǔ)引擎也采用的這種方式

>> 通過(guò)上述的介紹实愚，可以發(fā)現(xiàn)wait-for graph是一種較為主動(dòng)的死鎖檢測(cè)機(jī)制，在每個(gè)事務(wù)請(qǐng)求鎖并發(fā)生等待時(shí)都會(huì)判斷是否存在回路兔辅，若存在則有死鎖腊敲，通常來(lái)說(shuō)InnoDB存儲(chǔ)引擎選擇回滾undo量最小的事務(wù)。

有并發(fā)就有可能發(fā)生维苔，跟開發(fā)的實(shí)現(xiàn)也有關(guān)系碰辅，但是概率也比較小，出現(xiàn)具體問(wèn)題的時(shí)候再具體分析介时！

>如果程序是串行的没宾，那么不可能發(fā)生死鎖凌彬。死鎖只存在于并發(fā)的情況，而數(shù)據(jù)庫(kù)本身就是一個(gè)并發(fā)運(yùn)行的程序循衰，因此可能會(huì)發(fā)生死鎖

>> 如果程序是串行的铲敛，那么不可能發(fā)生死鎖。死鎖只存在于并發(fā)的情況会钝，而數(shù)據(jù)庫(kù)本身就是一個(gè)并發(fā)運(yùn)行的程序伐蒋，因此可能會(huì)發(fā)生死鎖。

理論上Spring應(yīng)該也是做了這個(gè)判斷的…自己用JDBC實(shí)現(xiàn)的時(shí)候就要注意…

>還記得6.6節(jié)中所說(shuō)的內(nèi)容嗎迁酸？InnoDB存儲(chǔ)引擎并不會(huì)回滾大部分的錯(cuò)誤異常咽弦，但是死鎖除外。發(fā)現(xiàn)死鎖后胁出，InnoDB存儲(chǔ)引擎會(huì)馬上回滾一個(gè)事務(wù)，這點(diǎn)是需要注意的段审。因此如果在應(yīng)用程序中捕獲了1213這個(gè)錯(cuò)誤全蝶，其實(shí)并不需要對(duì)其進(jìn)行回滾。

>> Oracle數(shù)據(jù)庫(kù)中產(chǎn)生死鎖的常見原因是沒有對(duì)外鍵添加索引寺枉，而InnoDB存儲(chǔ)引擎會(huì)自動(dòng)對(duì)其進(jìn)行添加抑淫，因而能夠很好地避免了這種情況的發(fā)生

>> 此外還存在另一種死鎖，即當(dāng)前事務(wù)持有了待插入記錄的下一個(gè)記錄的X鎖姥闪，但是在等待隊(duì)列中存在一個(gè)S鎖的請(qǐng)求始苇，則可能會(huì)發(fā)生死鎖

>> 鎖升級(jí)（Lock Escalation）是指將當(dāng)前鎖的粒度降低。舉例來(lái)說(shuō)筐喳，數(shù)據(jù)庫(kù)可以把一個(gè)表的1000個(gè)行鎖升級(jí)為一個(gè)頁(yè)鎖催式，或者將頁(yè)鎖升級(jí)為表鎖。如果在數(shù)據(jù)庫(kù)的設(shè)計(jì)中認(rèn)為鎖是一種稀有資源避归，而且想避免鎖的開銷荣月，那數(shù)據(jù)庫(kù)中會(huì)頻繁出現(xiàn)鎖升級(jí)現(xiàn)象。Microsoft SQL Server數(shù)據(jù)庫(kù)的設(shè)計(jì)認(rèn)為鎖是一種稀有的資源梳毙，在適合的時(shí)候會(huì)自動(dòng)地將行哺窄、鍵或分頁(yè)鎖升級(jí)為更粗粒度的表級(jí)鎖。這種升級(jí)保護(hù)了系統(tǒng)資源账锹，防止系統(tǒng)使用太多的內(nèi)存來(lái)維護(hù)鎖萌业，在一定程度上提高了效率。

>> InnoDB存儲(chǔ)引擎不存在鎖升級(jí)的問(wèn)題奸柬。因?yàn)槠洳皇歉鶕?jù)每個(gè)記錄來(lái)產(chǎn)生行鎖的生年，相反，其根據(jù)每個(gè)事務(wù)訪問(wèn)的每個(gè)頁(yè)對(duì)鎖進(jìn)行管理的廓奕，采用的是位圖的方式晶框。因此不管一個(gè)事務(wù)鎖住頁(yè)中一個(gè)記錄還是多個(gè)記錄排抬，其開銷通常都是一致的。

>> 假設(shè)一張表有3000 000個(gè)數(shù)據(jù)頁(yè)授段，每個(gè)頁(yè)大約有100條記錄蹲蒲，那么總共有300000 000條記錄。若有一個(gè)事務(wù)執(zhí)行全表更新的SQL語(yǔ)句侵贵，則需要對(duì)所有記錄加X鎖届搁。若根據(jù)每行記錄產(chǎn)生鎖對(duì)象進(jìn)行加鎖，并且每個(gè)鎖占用10字節(jié)窍育，則僅對(duì)鎖管理就需要差不多需要3GB的內(nèi)存卡睦。而InnoDB存儲(chǔ)引擎根據(jù)頁(yè)進(jìn)行加鎖，并采用位圖方式漱抓，假設(shè)每個(gè)頁(yè)存儲(chǔ)的鎖信息占用30個(gè)字節(jié)表锻，則鎖對(duì)象僅需90MB的內(nèi)存。

第7章 事務(wù)

>> 事務(wù)（Transaction）是數(shù)據(jù)庫(kù)區(qū)別于文件系統(tǒng)的重要特性之一乞娄。在文件系統(tǒng)中瞬逊，如果正在寫文件，但是操作系統(tǒng)突然崩潰了仪或，這個(gè)文件就很有可能被破壞确镊。當(dāng)然，有一些機(jī)制可以把文件恢復(fù)到某個(gè)時(shí)間點(diǎn)范删。不過(guò)蕾域，如果需要保證兩個(gè)文件同步，這些文件系統(tǒng)可能就顯得無(wú)能為力了到旦。例如旨巷，在需要更新兩個(gè)文件時(shí)，更新完一個(gè)文件后添忘，在更新完第二個(gè)文件之前系統(tǒng)重啟了契沫，就會(huì)有兩個(gè)不同步的文件。

>> 這正是數(shù)據(jù)庫(kù)系統(tǒng)引入事務(wù)的主要目的：事務(wù)會(huì)把數(shù)據(jù)庫(kù)從一種一致狀態(tài)轉(zhuǎn)換為另一種一致狀態(tài)昔汉。

>> 理論上說(shuō)懈万，事務(wù)有著極其嚴(yán)格的定義，它必須同時(shí)滿足四個(gè)特性靶病，即通常所說(shuō)的事務(wù)的ACID特性会通。值得注意的是，雖然理論上定義了嚴(yán)格的事務(wù)要求娄周，但是數(shù)據(jù)庫(kù)廠商出于各種目的涕侈，并沒有嚴(yán)格去滿足事務(wù)的ACID標(biāo)準(zhǔn)。例如煤辨，對(duì)于MySQL的NDB Cluster引擎來(lái)說(shuō)裳涛，雖然其支持事務(wù)木张，但是不滿足D的要求，即持久性的要求端三。對(duì)于Oracle數(shù)據(jù)庫(kù)來(lái)說(shuō)舷礼，其默認(rèn)的事務(wù)隔離級(jí)別為READ COMMITTED，不滿足I的要求郊闯，即隔離性的要求妻献。雖然在大多數(shù)的情況下，這并不會(huì)導(dǎo)致嚴(yán)重的結(jié)果团赁，甚至可能還會(huì)帶來(lái)性能的提升育拨，但是用戶首先需要知道嚴(yán)謹(jǐn)?shù)氖聞?wù)標(biāo)準(zhǔn)，并在實(shí)際的生產(chǎn)應(yīng)用中避免可能存在的潛在問(wèn)題欢摄。對(duì)于InnoDB存儲(chǔ)引擎而言熬丧，其默認(rèn)的事務(wù)隔離級(jí)別為READ REPEATABLE，完全遵循和滿足事務(wù)的ACID特性怀挠。

原子性就是說(shuō)析蝴，事務(wù)中的兩個(gè)SQL肯定都應(yīng)該是一起執(zhí)行或者一起不執(zhí)行，就是一個(gè)組合體唆香，最小單元。

>A（Atomicity）吨艇，原子性

>> 如果事務(wù)中的操作都是只讀的躬它，要保持原子性是很簡(jiǎn)單的。一旦發(fā)生任何錯(cuò)誤东涡，要么重試冯吓，要么返回錯(cuò)誤代碼。因?yàn)橹蛔x操作不會(huì)改變系統(tǒng)中的任何相關(guān)部分疮跑。

事務(wù)提交以后组贺，要么成功，要么失敗，失敗了就一定要回滾。

>C（consistency）才写，一致性傲茄。一致性指事務(wù)將數(shù)據(jù)庫(kù)從一種狀態(tài)轉(zhuǎn)變?yōu)橄乱环N一致的狀態(tài)

隔離性主要是處理和避免并發(fā)情況下出現(xiàn)的一些異常的問(wèn)題。

>I（isolation）移盆，隔離性。隔離性還有其他的稱呼，如并發(fā)控制（concurrency control）仪吧、可串行化（serializability）、鎖（locking）等鞠眉。

>> D（durability）薯鼠，持久性择诈。事務(wù)一旦提交，其結(jié)果就是永久性的出皇。即使發(fā)生宕機(jī)等故障羞芍，數(shù)據(jù)庫(kù)也能將數(shù)據(jù)恢復(fù)。需要注意的是恶迈，只能從事務(wù)本身的角度來(lái)保證結(jié)果的永久性涩金。例如，在事務(wù)提交后暇仲，所有的變化都是永久的步做。即使當(dāng)數(shù)據(jù)庫(kù)因?yàn)楸罎⒍枰謴?fù)時(shí)，也能保證恢復(fù)后提交的數(shù)據(jù)都不會(huì)丟失奈附。但若不是數(shù)據(jù)庫(kù)本身發(fā)生故障全度，而是一些外部的原因，如RAID卡損壞斥滤、自然災(zāi)害等原因?qū)е聰?shù)據(jù)庫(kù)發(fā)生問(wèn)題将鸵，那么所有提交的數(shù)據(jù)可能都會(huì)丟失。因此持久性保證事務(wù)系統(tǒng)的高可靠性（High Reliability）佑颇，而不是高可用性（High Availability）顶掉。

>> 扁平事務(wù)的主要限制是不能提交或者回滾事務(wù)的某一部分，或分幾個(gè)步驟提交挑胸。

>> 帶有保存點(diǎn)的扁平事務(wù)（Flat Transactions with Savepoint）痒筒，除了支持扁平事務(wù)支持的操作外，允許在事務(wù)執(zhí)行過(guò)程中回滾到同一事務(wù)中較早的一個(gè)狀態(tài)茬贵。這是因?yàn)槟承┦聞?wù)可能在執(zhí)行過(guò)程中出現(xiàn)的錯(cuò)誤并不會(huì)導(dǎo)致所有的操作都無(wú)效簿透，放棄整個(gè)事務(wù)不合乎要求，開銷也太大

>> 鏈?zhǔn)聞?wù)（Chained Transaction）可視為保存點(diǎn)模式的一種變種解藻。帶有保存點(diǎn)的扁平事務(wù)老充，當(dāng)發(fā)生系統(tǒng)崩潰時(shí)，所有的保存點(diǎn)都將消失螟左，因?yàn)槠浔４纥c(diǎn)是易失的（volatile）啡浊，而非持久的（persistent）。這意味著當(dāng)進(jìn)行恢復(fù)時(shí)胶背，事務(wù)需要從開始處重新執(zhí)行虫啥，而不能從最近的一個(gè)保存點(diǎn)繼續(xù)執(zhí)行。

>> 鏈?zhǔn)聞?wù)的思想是：在提交一個(gè)事務(wù)時(shí)奄妨，釋放不需要的數(shù)據(jù)對(duì)象涂籽，將必要的處理上下文隱式地傳給下一個(gè)要開始的事務(wù)

>> 。注意砸抛，提交事務(wù)操作和開始下一個(gè)事務(wù)操作將合并為一個(gè)原子操作

>> 子事務(wù)既可以提交也可以回滾评雌。但是它的提交操作并不馬上生效树枫，除非其父事務(wù)已經(jīng)提交。因此可以推論出景东，任何子事物都在頂層事務(wù)提交后才真正的提交砂轻。

>> 樹中的任意一個(gè)事務(wù)的回滾會(huì)引起它的所有子事務(wù)一同回滾，故子事務(wù)僅保留A斤吐、C搔涝、I特性，不具有D的特性和措。

>> redo log稱為重做日志庄呈，用來(lái)保證事務(wù)的原子性和持久性。undo log用來(lái)保證事務(wù)的一致性派阱。

>> 有的DBA或許會(huì)認(rèn)為undo是redo的逆過(guò)程诬留，其實(shí)不然。redo和undo的作用都可以視為是一種恢復(fù)操作贫母，redo恢復(fù)提交事務(wù)修改的頁(yè)操作文兑，而undo回滾行記錄到某個(gè)特定版本。因此兩者記錄的內(nèi)容不同腺劣，redo通常是物理日志绿贞，記錄的是頁(yè)的物理修改操作。undo是邏輯日志橘原，根據(jù)每行記錄進(jìn)行記錄籍铁。

>> redo log基本上都是順序?qū)懙模跀?shù)據(jù)庫(kù)運(yùn)行時(shí)不需要對(duì)redo log的文件進(jìn)行讀取操作靠柑。而undo log是需要進(jìn)行隨機(jī)讀寫的寨辩。

>> 二進(jìn)制日志只在事務(wù)提交完成后進(jìn)行一次寫入吓懈。而InnoDB存儲(chǔ)引擎的重做日志在事務(wù)進(jìn)行中不斷地被寫入歼冰，這表現(xiàn)為日志并不是隨事務(wù)提交的順序進(jìn)行寫入的。

>> InnoDB存儲(chǔ)引擎在啟動(dòng)時(shí)不管上次數(shù)據(jù)庫(kù)運(yùn)行時(shí)是否正常關(guān)閉耻警，都會(huì)嘗試進(jìn)行恢復(fù)操作隔嫡。因?yàn)橹刈鋈罩居涗浀氖俏锢砣罩荆虼嘶謴?fù)的速度比邏輯日志甘穿，如二進(jìn)制日志腮恩，要快很多。與此同時(shí)温兼，InnoDB存儲(chǔ)引擎自身也對(duì)恢復(fù)進(jìn)行了一定程度的優(yōu)化秸滴，如順序讀取及并行應(yīng)用重做日志，這樣可以進(jìn)一步地提高數(shù)據(jù)庫(kù)恢復(fù)的速度募判。

>> 用戶通常對(duì)undo有這樣的誤解：undo用于將數(shù)據(jù)庫(kù)物理地恢復(fù)到執(zhí)行語(yǔ)句或事務(wù)之前的樣子——但事實(shí)并非如此荡含。undo是邏輯日志咒唆，因此只是將數(shù)據(jù)庫(kù)邏輯地恢復(fù)到原來(lái)的樣子。所有修改都被邏輯地取消了释液，但是數(shù)據(jù)結(jié)構(gòu)和頁(yè)本身在回滾之后可能大不相同全释。這是因?yàn)樵诙嘤脩舨l(fā)系統(tǒng)中，可能會(huì)有數(shù)十误债、數(shù)百甚至數(shù)千個(gè)并發(fā)事務(wù)浸船。數(shù)據(jù)庫(kù)的主要任務(wù)就是協(xié)調(diào)對(duì)數(shù)據(jù)記錄的并發(fā)訪問(wèn)。比如寝蹈，一個(gè)事務(wù)在修改當(dāng)前一個(gè)頁(yè)中某幾條記錄李命，同時(shí)還有別的事務(wù)在對(duì)同一個(gè)頁(yè)中另幾條記錄進(jìn)行修改。因此躺盛，不能將一個(gè)頁(yè)回滾到事務(wù)開始的樣子项戴，因?yàn)檫@樣會(huì)影響其他事務(wù)正在進(jìn)行的工作。

>> 當(dāng)InnoDB存儲(chǔ)引擎回滾時(shí)槽惫，它實(shí)際上做的是與先前相反的工作周叮。對(duì)于每個(gè)INSERT，InnoDB存儲(chǔ)引擎會(huì)完成一個(gè)DELETE界斜；對(duì)于每個(gè)DELETE仿耽，InnoDB存儲(chǔ)引擎會(huì)執(zhí)行一個(gè)INSERT；對(duì)于每個(gè)UPDATE各薇，InnoDB存儲(chǔ)引擎會(huì)執(zhí)行一個(gè)相反的UPDATE项贺，將修改前的行放回去。

>> 除了回滾操作峭判，undo的另一個(gè)作用是MVCC开缎，即在InnoDB存儲(chǔ)引擎中MVCC的實(shí)現(xiàn)是通過(guò)undo來(lái)完成。當(dāng)用戶讀取一行記錄時(shí)林螃，若該記錄已經(jīng)被其他事務(wù)占用奕删，當(dāng)前事務(wù)可以通過(guò)undo讀取之前的行版本信息，以此實(shí)現(xiàn)非鎖定讀取疗认。

>> 事務(wù)提交后并不能馬上刪除undo log及undo log所在的頁(yè)完残。這是因?yàn)榭赡苓€有其他事務(wù)需要通過(guò)undo log來(lái)得到行記錄之前的版本。故事務(wù)提交時(shí)將undo log放入一個(gè)鏈表中横漏，是否可以最終刪除undo log及undo log所在頁(yè)由purge線程來(lái)判斷谨设。

>> 若為每一個(gè)事務(wù)分配一個(gè)單獨(dú)的undo頁(yè)會(huì)非常浪費(fèi)存儲(chǔ)空間，特別是對(duì)于OLTP的應(yīng)用類型

>> 因此缎浇，在InnoDB存儲(chǔ)引擎的設(shè)計(jì)中對(duì)undo頁(yè)可以進(jìn)行重用扎拣。具體來(lái)說(shuō)，當(dāng)事務(wù)提交時(shí)，首先將undo log放入鏈表中二蓝，然后判斷undo頁(yè)的使用空間是否小于3/4尊蚁，若是則表示該undo頁(yè)可以被重用，之后新的undo log記錄在當(dāng)前undo log的后面

>> History list length就代表了undo log的數(shù)量侣夷，這里為12横朋。purge操作會(huì)減少該值。然而由于undo log所在的頁(yè)可以被重用百拓，因此即使操作發(fā)生琴锭，History list length的值也可以不為0。

>> update undo log記錄的是對(duì)delete和update操作產(chǎn)生的undo log衙传。該undo log可能需要提供MVCC機(jī)制决帖，因此不能在事務(wù)提交時(shí)就進(jìn)行刪除

>> InnoSQL對(duì)information_schema進(jìn)行了擴(kuò)展，添加了兩張數(shù)據(jù)字典表蓖捶，這樣用戶可以非常方便和快捷地查看undo的信息地回。首先增加的數(shù)據(jù)字典表為INNODB_TRX_ROLLBACK_SEGMENT。

>> 另一張數(shù)據(jù)字典表為INNODB_TRX_UNDO俊鱼，用來(lái)記錄事務(wù)對(duì)應(yīng)的undo log刻像，方便DBA和開發(fā)人員詳細(xì)了解每個(gè)事務(wù)產(chǎn)生的undo量

>> 通過(guò)上面的例子可以看到，delete操作并不直接刪除記錄并闲，而只是將記錄標(biāo)記為已刪除细睡，也就是將記錄的delete flag設(shè)置為1。而記錄最終的刪除是在purge操作中完成的帝火。

>> 全局動(dòng)態(tài)參數(shù)innodb_purge_batch_size用來(lái)設(shè)置每次purge操作需要清理的undo page數(shù)量溜徙。在InnoDB1.2之前，該參數(shù)的默認(rèn)值為20犀填。而從1.2版本開始蠢壹，該參數(shù)的默認(rèn)值為300。通常來(lái)說(shuō)九巡，該參數(shù)設(shè)置得越大图贸，每次回收的undo page也就越多，這樣可供重用的undo page就越多比庄，減少了磁盤存儲(chǔ)空間與分配的開銷求妹。不過(guò)乏盐，若該參數(shù)設(shè)置得太大佳窑，則每次需要purge處理更多的undo page，從而導(dǎo)致CPU和磁盤IO過(guò)于集中于對(duì)undo log的處理父能，使性能下降神凑。因此對(duì)該參數(shù)的調(diào)整需要由有經(jīng)驗(yàn)的DBA來(lái)操作，并且需要長(zhǎng)期觀察數(shù)據(jù)庫(kù)的運(yùn)行的狀態(tài)。正如官方的MySQL數(shù)據(jù)庫(kù)手冊(cè)所說(shuō)的溉委，普通用戶不需要調(diào)整該參數(shù)鹃唯。

>> 為了提高磁盤fsync的效率，當(dāng)前數(shù)據(jù)庫(kù)都提供了group commit的功能瓣喊，即一次fsync可以刷新確保多個(gè)事務(wù)日志被寫入文件坡慌。對(duì)于InnoDB存儲(chǔ)引擎來(lái)說(shuō)，事務(wù)提交時(shí)會(huì)進(jìn)行兩個(gè)階段的操作：1）修改內(nèi)存中事務(wù)對(duì)應(yīng)的信息藻三，并且將日志寫入重做日志緩沖洪橘。2）調(diào)用fsync將確保日志都從重做日志緩沖寫入磁盤。

>> COMMIT和COMMIT WORK語(yǔ)句基本是一致的棵帽，都是用來(lái)提交事務(wù)熄求。不同之處在于COMMIT WORK用來(lái)控制事務(wù)結(jié)束后的行為是CHAIN還是RELEASE的。如果是CHAIN方式逗概，那么事務(wù)就變成了鏈?zhǔn)聞?wù)弟晚。

>> 用戶可以通過(guò)參數(shù)completion_type來(lái)進(jìn)行控制，該參數(shù)默認(rèn)為0逾苫，表示沒有任何操作卿城。在這種設(shè)置下COMMIT和COMMIT WORK是完全等價(jià)的。當(dāng)參數(shù)completion_type的值為1時(shí)铅搓，COMMIT WORK等同于COMMIT AND CHAIN藻雪，表示馬上自動(dòng)開啟一個(gè)相同隔離級(jí)別的事務(wù)，

>> 參數(shù)completion_type為2時(shí)狸吞，COMMIT WORK等同于COMMIT AND RELEASE勉耀。在事務(wù)提交后會(huì)自動(dòng)斷開與服務(wù)器的連接

>> TRUNCATE TABLE語(yǔ)句是DDL，因此雖然和對(duì)整張表執(zhí)行DELETE的結(jié)果是一樣的蹋偏，但它是不能被回滾的（這又是和Microsoft SQL Server數(shù)據(jù)不同的地方）便斥。

>> 計(jì)算TPS的方法是（com_commit+com_rollback）/time。但是利用這種方法進(jìn)行計(jì)算的前提是：所有的事務(wù)必須都是顯式提交的威始，如果存在隱式地提交和回滾（默認(rèn)autocommit=1）枢纠，不會(huì)計(jì)算到com_commit和com_rollback變量中。

>> 隔離級(jí)別越低黎棠，事務(wù)請(qǐng)求的鎖越少或保持鎖的時(shí)間就越短晋渺。這也是為什么大多數(shù)數(shù)據(jù)庫(kù)系統(tǒng)默認(rèn)的事務(wù)隔離級(jí)別是READ COMMITTED。

>> 據(jù)了解脓斩，大部分的用戶質(zhì)疑SERIALIZABLE隔離級(jí)別帶來(lái)的性能問(wèn)題木西，但是根據(jù)Jim Gray在《Transaction Processing》一書中指出，兩者的開銷幾乎是一樣的随静，甚至SERIALIZABLE可能更優(yōu)!!!因此在InnoDB存儲(chǔ)引擎中選擇REPEATABLE READ的事務(wù)隔離級(jí)別并不會(huì)有任何性能的損失

>> 因?yàn)镮nnoDB存儲(chǔ)引擎在REPEATABLE READ隔離級(jí)別下就可以達(dá)到3°的隔離八千，因此一般不在本地事務(wù)中使用SERIALIABLE的隔離級(jí)別吗讶。SERIALIABLE的事務(wù)隔離級(jí)別主要用于InnoDB存儲(chǔ)引擎的分布式事務(wù)。

>> XA事務(wù)允許不同數(shù)據(jù)庫(kù)之間的分布式事務(wù)恋捆，如一臺(tái)服務(wù)器是MySQL數(shù)據(jù)庫(kù)的照皆，另一臺(tái)是Oracle數(shù)據(jù)庫(kù)的，又可能還有一臺(tái)服務(wù)器是SQL Server數(shù)據(jù)庫(kù)的沸停，只要參與在全局事務(wù)中的每個(gè)節(jié)點(diǎn)都支持XA事務(wù)

>> 在單個(gè)節(jié)點(diǎn)上運(yùn)行分布式事務(wù)沒有太大的實(shí)際意義膜毁，但是要在MySQL數(shù)據(jù)庫(kù)的命令下演示多個(gè)節(jié)點(diǎn)參與的分布式事務(wù)也是行不通的。通常來(lái)說(shuō)愤钾，都是通過(guò)編程語(yǔ)言來(lái)完成分布式事務(wù)的操作的爽茴。當(dāng)前Java的JTA（Java Transaction API）可以很好地支持MySQL的分布式事務(wù)，需要使用分布式事務(wù)應(yīng)該認(rèn)真參考其API

>> 最為常見的內(nèi)部XA事務(wù)存在于binlog與InnoDB存儲(chǔ)引擎之間

>> 對(duì)于不同語(yǔ)言的API绰垂，自動(dòng)提交是不同的室奏。MySQL C API默認(rèn)的提交方式是自動(dòng)提交，而MySQL Python API則會(huì)自動(dòng)執(zhí)行SET AUTOCOMMIT=0劲装，以禁用自動(dòng)提交胧沫。因此在選用不同的語(yǔ)言來(lái)編寫數(shù)據(jù)庫(kù)應(yīng)用程序前，應(yīng)該對(duì)連接MySQL的API做好研究占业。

>> 就像之前小節(jié)中所講到的绒怨，對(duì)事務(wù)的BEGIN、COMMIT和ROLLBACK操作應(yīng)該交給程序端來(lái)完成谦疾，存儲(chǔ)過(guò)程需要完成的只是一個(gè)邏輯的操作南蹂，即對(duì)邏輯進(jìn)行封裝。

>> 長(zhǎng)事務(wù)（Long-Lived Transactions）念恍，顧名思義六剥，就是執(zhí)行時(shí)間較長(zhǎng)的事務(wù)。比如峰伙，對(duì)于銀行系統(tǒng)的數(shù)據(jù)庫(kù)疗疟，每過(guò)一個(gè)階段可能需要更新對(duì)應(yīng)賬戶的利息。如果對(duì)應(yīng)賬號(hào)的數(shù)量非常大瞳氓，例如對(duì)有1億用戶的表account策彤，需要執(zhí)行下列語(yǔ)句

>> 在執(zhí)行過(guò)程中，當(dāng)數(shù)據(jù)庫(kù)或操作系統(tǒng)匣摘、硬件等發(fā)生問(wèn)題時(shí)店诗，重新開始事務(wù)的代價(jià)變得不可接受。數(shù)據(jù)庫(kù)需要回滾所有已經(jīng)發(fā)生的變化音榜，而這個(gè)過(guò)程可能比產(chǎn)生這些變化的時(shí)間還要長(zhǎng)庞瘸。因此，對(duì)于長(zhǎng)事務(wù)的問(wèn)題囊咏，有時(shí)可以通過(guò)轉(zhuǎn)化為小批量（mini batch）的事務(wù)來(lái)進(jìn)行處理恕洲。當(dāng)事務(wù)發(fā)生錯(cuò)誤時(shí)，只需要回滾一部分?jǐn)?shù)據(jù)梅割，然后接著上次已完成的事務(wù)繼續(xù)進(jìn)行

>> 上述代碼將一個(gè)需要處理1億用戶的大事務(wù)分解為每次處理10萬(wàn)用戶的小事務(wù)霜第，通過(guò)批量處理小事務(wù)來(lái)完成大事務(wù)的邏輯。每完成一個(gè)小事務(wù)户辞，將完成的結(jié)果存放在batchcontext表中泌类，表示已完成批量事務(wù)的最大賬號(hào)ID。

第8章 備份與恢復(fù)

>> 按照備份后文件的內(nèi)容底燎，備份又可以分為：□ 邏輯備份□ 裸文件備份在MySQL數(shù)據(jù)庫(kù)中刃榨，邏輯備份是指?jìng)浞莩龅奈募?nèi)容是可讀的，一般是文本文件双仍。內(nèi)容一般是由一條條SQL語(yǔ)句枢希，或者是表內(nèi)實(shí)際數(shù)據(jù)組成。如mysqldump和SELECT*INTO OUTFILE的方法朱沃。這類方法的好處是可以觀察導(dǎo)出文件的內(nèi)容苞轿，一般適用于數(shù)據(jù)庫(kù)的升級(jí)、遷移等工作逗物。但其缺點(diǎn)是恢復(fù)所需要的時(shí)間往往較長(zhǎng)搬卒。裸文件備份是指復(fù)制數(shù)據(jù)庫(kù)的物理文件，既可以是在數(shù)據(jù)庫(kù)運(yùn)行中的復(fù)制（如ibbackup翎卓、xtrabackup這類工具）契邀，也可以是在數(shù)據(jù)庫(kù)停止運(yùn)行時(shí)直接的數(shù)據(jù)文件復(fù)制。這類備份的恢復(fù)時(shí)間往往較邏輯備份短很多失暴。

>> 對(duì)于MySQL數(shù)據(jù)庫(kù)來(lái)說(shuō)坯门，官方?jīng)]有提供真正的增量備份的方法，大部分是通過(guò)二進(jìn)制日志完成增量備份的工作逗扒。這種備份較之真正的增量備份來(lái)說(shuō)田盈，效率還是很低的

>> 最后，任何時(shí)候都需要做好遠(yuǎn)程異地備份缴阎，也就是容災(zāi)的防范允瞧。只是同一機(jī)房的兩臺(tái)服務(wù)器的備份是遠(yuǎn)遠(yuǎn)不夠的。我曾經(jīng)遇到的情況是蛮拔，公司在2008年的汶川地震中發(fā)生一個(gè)機(jī)房可能被淹的的情況述暂，這時(shí)遠(yuǎn)程異地備份顯得就至關(guān)重要了。

>> replication的工作原理分為以下3個(gè)步驟：1）主服務(wù)器（master）把數(shù)據(jù)更改記錄到二進(jìn)制日志（binlog）中建炫。2）從服務(wù)器（slave）把主服務(wù)器的二進(jìn)制日志復(fù)制到自己的中繼日志（relay log）中畦韭。3）從服務(wù)器重做中繼日志中的日志，把更改應(yīng)用到自己的數(shù)據(jù)庫(kù)上肛跌，以達(dá)到數(shù)據(jù)的最終一致性艺配。

>> 之前已經(jīng)說(shuō)過(guò)MySQL的復(fù)制是異步實(shí)時(shí)的察郁，并非完全的主從同步。若用戶要想得知當(dāng)前的延遲转唉，可以通過(guò)命令SHOW SLAVE STATUS和SHOW MASTER STATUS得知皮钠，如：

>> 假設(shè)當(dāng)前應(yīng)用采用了主從的復(fù)制架構(gòu)，從服務(wù)器作為備份赠法。這時(shí)麦轰，一個(gè)初級(jí)DBA執(zhí)行了誤操作，如DROP DATABASE或DROP TABLE砖织，這時(shí)從服務(wù)器也跟著運(yùn)行了款侵。這時(shí)用戶怎樣從服務(wù)器進(jìn)行恢復(fù)呢？因此侧纯，一個(gè)比較好的方法是通過(guò)對(duì)從服務(wù)器上的數(shù)據(jù)庫(kù)所在分區(qū)做快照新锈，以此來(lái)避免誤操作對(duì)復(fù)制造成影響。當(dāng)發(fā)生主服務(wù)器上的誤操作時(shí)眶熬，只需要將從服務(wù)器上的快照進(jìn)行恢復(fù)壕鹉，然后再根據(jù)二進(jìn)制日志進(jìn)行point-in-time的恢復(fù)即可。

>> 還有一些其他的方法來(lái)調(diào)整復(fù)制聋涨，比如采用延時(shí)復(fù)制晾浴，即間歇性地開啟從服務(wù)器上的同步，保證大約一小時(shí)的延時(shí)牍白。這的確也是一個(gè)方法脊凰，只是數(shù)據(jù)庫(kù)在高峰和非高峰期間每小時(shí)產(chǎn)生的二進(jìn)制日志量是不同的，用戶很難精準(zhǔn)地控制茂腥。另外狸涌，這種方法也不能完全起到對(duì)誤操作的防范作用。

>> 建議在從服務(wù)上啟用read-only選項(xiàng)最岗，這樣能保證從服務(wù)器上的數(shù)據(jù)僅與主服務(wù)器進(jìn)行同步帕胆，避免其他線程修改數(shù)據(jù)

第9章 性能調(diào)優(yōu)

>> 另一方面，閃存中的數(shù)據(jù)是不可以更新的般渡，只能通過(guò)扇區(qū)（sector）的覆蓋重寫懒豹，而在覆蓋重寫之前，需要執(zhí)行非常耗時(shí)的擦除（erase）操作驯用。擦除操作不能在所含數(shù)據(jù)的扇區(qū)上完成脸秽，而需要在刪除整個(gè)被稱為擦除塊的基礎(chǔ)上完成，這個(gè)擦除塊的尺寸大于扇區(qū)的大小蝴乔，通常為128KB或者256KB记餐。此外，每個(gè)擦除塊有擦寫次數(shù)的限制薇正。已經(jīng)有一些算法來(lái)解決這個(gè)問(wèn)題

>> 因?yàn)榇嬖谏鲜鰧懭敕矫娴膯?wèn)題片酝，閃存提供的讀寫速度是非對(duì)稱的囚衔。讀取速度要遠(yuǎn)快于寫入的速度，因此對(duì)于固態(tài)硬盤在數(shù)據(jù)庫(kù)中的應(yīng)用雕沿，應(yīng)該好好利用其讀取的性能练湿，避免過(guò)多的寫入操作。

>> 由于將多個(gè)硬盤組合成為一個(gè)邏輯扇區(qū)晦炊，RAID看起來(lái)就像一個(gè)單獨(dú)的硬盤或邏輯存儲(chǔ)單元鞠鲜，因此操作系統(tǒng)只會(huì)把它當(dāng)作一個(gè)硬盤宁脊。

>> RAID 5具有和RAID 0相近似的數(shù)據(jù)讀取速度断国，只是多了一個(gè)奇偶校驗(yàn)信息，寫入數(shù)據(jù)的速度相當(dāng)慢榆苞，若使用Write Back可以讓性能改善不少稳衬。

>> RAID 01比RAID 10有著更快的讀寫速度，不過(guò)也多了一些會(huì)讓整個(gè)硬盤組停止運(yùn)轉(zhuǎn)的幾率坐漏，因?yàn)橹灰唤M的硬盤全部損毀薄疚，RAID 01就會(huì)停止運(yùn)作，而RAID 10可以在犧牲RAID 0的優(yōu)勢(shì)下正常運(yùn)作赊琳。RAID 10巧妙地利用了RAID 0的速度及RAID 1的安全（保護(hù)）兩種特性街夭，它的缺點(diǎn)是需要較多的硬盤，因?yàn)橹辽俦仨殦碛兴膫€(gè)以上的偶數(shù)硬盤才能使用躏筏。

>> RAID Write Back功能是指RAID控制器能夠?qū)懭氲臄?shù)據(jù)放入自身的緩存中板丽，并把它們安排到后面再執(zhí)行。這樣做的好處是趁尼，不用等待物理磁盤實(shí)際寫入的完成埃碱，因此寫入變得更快了

>> 對(duì)RAID卡進(jìn)行配置可以在服務(wù)器啟動(dòng)時(shí)進(jìn)入一個(gè)類似于BIOS的配置界面，然后再對(duì)其進(jìn)行各種設(shè)置酥泞。此外砚殿，很多廠商都開發(fā)了各種操作系統(tǒng)下的軟件對(duì)RAID進(jìn)行配置，如果用戶使用的是LSI公司生產(chǎn)提供的RAID卡芝囤，則可以使用MegaCLI工具來(lái)進(jìn)行配置似炎。

>> 特別需要注意地是，當(dāng)RAID卡的寫入策略從Write Back切換為Write Through時(shí)悯姊，該更改立即生效名党。然而從Write Through切換為Write Back時(shí)，必須重啟服務(wù)器才能使其生效挠轴。

>> 基準(zhǔn)測(cè)試工具可以用來(lái)對(duì)數(shù)據(jù)庫(kù)或操作系統(tǒng)調(diào)優(yōu)后的性能進(jìn)行對(duì)比传睹。MySQL數(shù)據(jù)庫(kù)本身提供了一些比較優(yōu)秀的工具，這里將介紹另外兩款更為優(yōu)秀和常用的基準(zhǔn)測(cè)試工具：sysbench和mysql-tpcc岸晦。

>> 對(duì)于MySQL數(shù)據(jù)庫(kù)的OLTP測(cè)試欧啤，和fileio一樣需要經(jīng)歷prepare睛藻、run和cleanup階段。prepare階段會(huì)根據(jù)選項(xiàng)產(chǎn)生一張指定行數(shù)的表邢隧，默認(rèn)表在sbtest架構(gòu)下店印，表名為sbtest（sysbench默認(rèn)生成表的存儲(chǔ)引擎為InnoDB）。例如創(chuàng)建一張8000W的表：

>> TPC（Transaction Processing Performance Council倒慧，事務(wù)處理性能協(xié)會(huì)）是一個(gè)用來(lái)評(píng)價(jià)大型數(shù)據(jù)庫(kù)系統(tǒng)軟硬件性能的非盈利組織按摘。TPC-C是TPC協(xié)會(huì)制定的，用來(lái)測(cè)試典型的復(fù)雜OLTP（在線事務(wù)處理）系統(tǒng)的性能纫谅。目前在學(xué)術(shù)界和工業(yè)界普遍采用TPC-C來(lái)評(píng)價(jià)OLTP應(yīng)用的性能炫贤。