由：連接池組件、管理服務(wù)和工具組件、sql接口組件、查詢分析器組件、優(yōu)化器組件、       
緩沖組件、插件式存儲(chǔ)引擎、物理文件組成。
mysql是獨(dú)有的插件式體系結(jié)構(gòu)，各個(gè)存儲(chǔ)引擎有自己的特點(diǎn)。    

   


   
mysql各個(gè)存儲(chǔ)引擎概述：
innodb存儲(chǔ)引擎：[/color][/b] 面向oltp(online transaction processing)、行鎖、支持外鍵、非鎖定讀、默認(rèn)采用repeaable級(jí)別（可重復(fù)讀）通過(guò)next-keylocking策略避免幻讀、插入緩沖、二次寫(xiě)、自適應(yīng)哈希索引、預(yù)讀
myisam存儲(chǔ)引擎：不支持事務(wù)、表鎖、全文索引、適合olap（在線分析處理），其中myd:放數(shù)據(jù)文件，myi:放索引文件
ndb存儲(chǔ)引擎：集群存儲(chǔ)引擎，share nothing，可提高可用性
memory存儲(chǔ)引擎：數(shù)據(jù)存放在內(nèi)存中，表鎖，并發(fā)性能差，默認(rèn)使用哈希索引
archive存儲(chǔ)引擎：只支持insert和select zlib算法壓縮1：10，適合存儲(chǔ)歸檔數(shù)據(jù)如日志等、行鎖
maria存儲(chǔ)引擎：目的取代myisam、緩存數(shù)據(jù)和索引、行鎖、mvcc

    


   
innodb特性：
主體系結(jié)構(gòu)：默認(rèn)7個(gè)后臺(tái)線程，4個(gè)io thread(insert buffer、log、read、write),1個(gè)master thread(優(yōu)先級(jí)最高),1個(gè)鎖(lock)監(jiān)控線程，1個(gè)錯(cuò)誤監(jiān)控線程?？梢酝ㄟ^(guò)show engine innodb status來(lái)查看。新版本已對(duì)默認(rèn)的read thread和write thread分別增大到4個(gè)，可通過(guò)show variables like 'innodb_io_thread%'查看。
存儲(chǔ)引擎組成：緩沖池(buffer pool)、重做日志緩沖池(redo log buffer)以及額外的內(nèi)存池(additional memory pool).具體配置可由show variables like 'innodb_buffer_pool_size'、show variables like
'innodb_log_buffer_size'、show variables like 'innodb_additional_mem_pool_size'來(lái)查看。
緩沖池：占最大塊內(nèi)存，用來(lái)存放各種數(shù)據(jù)的緩存包括有索引頁(yè)、數(shù)據(jù)頁(yè)、undo頁(yè)、插入緩沖、自適應(yīng)哈希索引、innodb存儲(chǔ)的鎖信息、數(shù)據(jù)字典信息等。工作方式總是將數(shù)據(jù)庫(kù)文件按頁(yè)(每頁(yè)16k)讀取到緩沖池，然后按最近最少使用(lru)的算法來(lái)保留在緩沖池中的緩存數(shù)據(jù)。如果數(shù)據(jù)庫(kù)文件需要修改，總是首先修改在緩存池中的頁(yè)(發(fā)生修改后即為臟頁(yè))，然后再按照一定的頻率將緩沖池的臟頁(yè)刷新到文件。通過(guò)命令show engine innodb status;來(lái)查看。
日志緩沖：將重做日志信息先放入這個(gè)緩沖區(qū)，然后按一定頻率將其刷新到重做日志文件。




master thread:
loop主循環(huán)每秒一次的操作：
日志緩沖刷新到磁盤(pán)，即使這個(gè)事務(wù)還沒(méi)有提交。(總是執(zhí)行，所以再大的事務(wù)commit 
的時(shí)間也是很快的)           
合并插入緩沖(innodb當(dāng)前一秒發(fā)生的io次數(shù)小于5次則執(zhí)行)
至多刷新100個(gè)innodb的緩沖池中的臟頁(yè)到磁盤(pán)(超過(guò)配置的臟頁(yè)所占緩沖池比例則執(zhí)
行，在配置文件中由innodb_max_dirty_pages_pac決定，默認(rèn)是90，新版本是75，
google建議是80)
如果當(dāng)前沒(méi)用用戶活動(dòng)，切換到backgroud loop        

loop主循環(huán)每10秒一次的操作：
刷新100個(gè)臟頁(yè)到磁盤(pán)(過(guò)去10秒IO操作小于200次則執(zhí)行)
合并至多5個(gè)插入緩沖(總是)
將日志緩沖到磁盤(pán)(總是)
刪除無(wú)用的Undo頁(yè)(總是)
刷新100個(gè)或者10個(gè)臟頁(yè)到磁盤(pán)(有超過(guò)70%的臟頁(yè)，刷新100個(gè)臟頁(yè);否則刷新10個(gè)臟頁(yè))
產(chǎn)生一個(gè)檢查點(diǎn)

backgroud loop，若當(dāng)前沒(méi)有用戶活動(dòng)(數(shù)據(jù)庫(kù)空閑時(shí))或者數(shù)據(jù)庫(kù)關(guān)閉時(shí)，就會(huì)切換到這個(gè)循環(huán)：
刪除無(wú)用的Undo頁(yè)(總是)
合并20個(gè)插入緩沖(總是)
跳回到主循環(huán)(總是)
不斷刷新100個(gè)頁(yè)，直到符合條件(可能在flush loop中完成)

如果flush loop中也沒(méi)有什么事情可以做了，InnoDB存儲(chǔ)引擎會(huì)切換到suspend_loop，將master thread掛起，等待事件的發(fā)生。若啟用了InnoDB存儲(chǔ)引擎，卻沒(méi)有使用任何InnoDB存儲(chǔ)引擎的表，那么master thread總是處于掛起狀態(tài)

插入緩沖：不是緩沖池的一部分，Insert Buffer是物理頁(yè)的一個(gè)組成部分,它帶來(lái)InnoDB性能的提高。根據(jù)B+算法(下文會(huì)提到)的特點(diǎn)，插入數(shù)據(jù)的時(shí)候會(huì)主鍵索引是順序的，不會(huì)造成數(shù)據(jù)庫(kù)的隨機(jī)讀取，而對(duì)于非聚集索引(即輔助索引)，葉子節(jié)點(diǎn)的插入不再是順序的了，這時(shí)需要離散地訪問(wèn)非聚集索引，插入性能在這里變低了。InnoDB引入插入緩沖，判斷非聚集索引頁(yè)是否在緩沖池中，如果在則直接插入;不在，則先放在 插入緩沖區(qū)中。然后根據(jù)上述master thread中介紹的，會(huì)有一定的頻率將插入緩沖合并。此外，輔助索引不能是唯一的，因?yàn)椴迦氲讲迦刖彌_時(shí)，并不去查找索引頁(yè)的情況，否則仍然會(huì)造成隨機(jī)讀，失去插入緩沖的意義了。插入緩沖可能會(huì)占緩沖池中內(nèi)存，默認(rèn)也能會(huì)占到1/2，所以可以將這個(gè)值調(diào)小點(diǎn)，到1/3。通過(guò)IBUF_POOL_SIZE_PER_MAX_SIZE來(lái)設(shè)置，2表示1/2,3表示1/3。

兩次寫(xiě)：  它帶來(lái)InnoDB數(shù)據(jù)的可靠性。如果寫(xiě)失效，可以通過(guò)重做日志進(jìn)行恢復(fù)，但是重做日志中記錄的是對(duì)頁(yè)的物理操作，如果頁(yè)本身?yè)p壞，再對(duì)其進(jìn)行重做是沒(méi)有意義的。所以，在應(yīng)用重做日志前，需要一個(gè)頁(yè)的副本，當(dāng)寫(xiě)入失效發(fā)生時(shí)，先通過(guò)頁(yè)的副本來(lái)還原該頁(yè)，再進(jìn)行重做，這就是doublewire。
恢復(fù)數(shù)據(jù)=頁(yè)副本+重做日志
             



             
自適應(yīng)哈希索引：InnoDB存儲(chǔ)引擎提出一種自適應(yīng)哈希索引，存儲(chǔ)引擎會(huì)監(jiān)控對(duì)表上索引的查找，如果觀察到建立建立哈希索引會(huì)帶來(lái)速度的提升，則建立哈希索引，所以稱之為自適應(yīng)的。自適應(yīng)哈希索引只能用來(lái)搜索等值的查詢，如select * from table where index_col='***', 此外自適應(yīng)哈希是由InnoDB存儲(chǔ)引擎控制的，我們只能通過(guò)innodb_adaptive_hash_index來(lái)禁用或啟用，默認(rèn)開(kāi)啟。

mysql 文件
參數(shù)文件：告訴Mysql實(shí)例啟動(dòng)時(shí)在哪里可以找到數(shù)據(jù)庫(kù)文件，并且指定某些初始化參數(shù)，這些參數(shù)定義了某種內(nèi)存結(jié)構(gòu)的大小等設(shè)置。用文件存儲(chǔ)，可編輯，若啟動(dòng)時(shí)加載不到則不能成功啟動(dòng)（與其他數(shù)據(jù)庫(kù)不同）。參數(shù)有動(dòng)態(tài)和靜態(tài)之分，靜態(tài)相當(dāng)于只讀，動(dòng)態(tài)是可以set的。如我們通過(guò)show variable like '***'查出來(lái)的key、value值，是可以通過(guò)set key=value直接修改的。同是，修改時(shí)還有作用域之分，即這個(gè)seesion個(gè)有效和全局有效，在對(duì)應(yīng)的key前加上session或global即可，如select @@seesion.read_buffer_size、set @@global.read_buffer_size。
日志文件：用來(lái)記錄Mysql實(shí)例對(duì)某種條件做出響應(yīng)時(shí)寫(xiě)入的文件。如錯(cuò)誤日志文件、二進(jìn)制日志文件、慢查詢?nèi)罩疚募?、查詢?nèi)罩疚募取?BR>錯(cuò)誤日志：通過(guò)show variables like 'log_error'來(lái)查看錯(cuò)誤日志存放地址
慢查詢?nèi)罩荆?/STRONG>通過(guò)show variables like '%long%' 查看慢查詢?nèi)罩居涗浀拈撝?，新版本設(shè)成了0.05；通過(guò)show variables like 'log_slow_queries'查看是否開(kāi)啟了，默認(rèn)為關(guān)閉的；通過(guò)show variabes like 'log_queries_not_using_indexes'查看是將沒(méi)有使用索引的查詢記錄到慢日志中。mysql中可以直接通過(guò)mysqldumpslow命令來(lái)查看慢日志。
二進(jìn)制文件：不記錄查詢，只記錄對(duì)數(shù)據(jù)庫(kù)所有的修改操作。目的是為了恢復(fù)(point-in-time修復(fù))和復(fù)制。通過(guò)show variables like 'datadir'查看存放路徑。二進(jìn)制日志支持STATEMENT、ROW、MIX三種格式，通過(guò)binlog_format參數(shù)設(shè)定，通常設(shè)置為ROW，可以為數(shù)據(jù)庫(kù)的恢復(fù)和復(fù)制帶來(lái)更好的可靠性，但會(huì)帶來(lái)二進(jìn)制文件大小的增加，復(fù)制時(shí)會(huì)增加網(wǎng)絡(luò)開(kāi)銷。mysql中通過(guò)mysqlbinlog查看二進(jìn)制日志文件內(nèi)容。
socket文件：當(dāng)用Unix域套接字方式進(jìn)行連接時(shí)需要的文件。
pid文件：Mysql實(shí)例的進(jìn)程ID文件。
Mysql表結(jié)構(gòu)文件：用來(lái)存放Mysql表結(jié)構(gòu)定義文件。因?yàn)镸ysql插件式存儲(chǔ)引擎的體系結(jié)構(gòu)，每個(gè)表都有一個(gè)對(duì)應(yīng)的文件，以frm后綴結(jié)尾。
存儲(chǔ)引擎文件：存儲(chǔ)自己的文件來(lái)保存各種數(shù)據(jù)，真正存儲(chǔ)了數(shù)據(jù)和索引等數(shù)據(jù)。下面主要介紹InnoDB的存儲(chǔ)引擎下的表空間文件和重做日志文件。
表空間文件：InnoDB默認(rèn)的表空間文件為ibdata1,可通過(guò)show variables like 'innodb_file_per_table'查看每個(gè)表是否產(chǎn)生單獨(dú)的.idb表空間文件。但是，單獨(dú)的表空間文件僅存儲(chǔ)該表的數(shù)據(jù)、索引和插入緩沖等信息，其余信息還是存放在默認(rèn)的表空間中。





重做日志文件：實(shí)例和介質(zhì)失敗，重做日志文件就能派上用場(chǎng)，如數(shù)據(jù)庫(kù)掉電，InnoDB存儲(chǔ)引擎會(huì)使用重做日志恢復(fù)到掉電前的時(shí)刻，以此來(lái)保證數(shù)據(jù)的完整性。參數(shù)innodb_log_file_size指定了重做日志文件的大??；innodb_log_file_in_group指定了日志文件組中重做日志文件的數(shù)量，默認(rèn)為2，innodb_mirrored_log_groups指定了日志鏡像文件組的數(shù)量，默認(rèn)為1，代表只有一個(gè)日志文件組，沒(méi)有鏡像；innodb_log_group_home_dir指定了日志文件組所在路徑，默認(rèn)在數(shù)據(jù)庫(kù)路徑下。

二進(jìn)制日志和重做日志的區(qū)別：首先，二進(jìn)制日志會(huì)記錄所有與Mysql有關(guān)的日志記錄，包括InnoDB、MyISAM、Heap等其他存儲(chǔ)引擎的日志。而InnoDB存儲(chǔ)引擎重做日志只存儲(chǔ)有關(guān)其本身的事務(wù)日志；其次內(nèi)容不同，不管將二進(jìn)制日志文件記錄的格式設(shè)為STATEMENT還是ROW，又或者是MIXED，其記錄的都是關(guān)于一個(gè)事務(wù)的具體操作內(nèi)容。而InnoDB存儲(chǔ)引擎的重做日志文件記錄的關(guān)于每個(gè)頁(yè)的更改的物理情況 。此外，寫(xiě)入時(shí)間不同，二進(jìn)制日志文件是在事務(wù)提交前進(jìn)行記錄的，而在事務(wù)進(jìn)行的過(guò)程中，不斷有重做日志條目被 寫(xiě)入重做日志文件中。

mysql innodb表
表空間：表空間可看做是InnoDB存儲(chǔ)引擎邏輯結(jié)構(gòu)的最高層。
段：表空間由各個(gè)段組成，常見(jiàn)的段有數(shù)據(jù)段、索引段、回滾段等。
區(qū)：由64個(gè)連續(xù)的頁(yè)組成，每個(gè)頁(yè)大小為16kb，即每個(gè)區(qū)大小為1MB。
頁(yè)：每頁(yè)16kb，且不能更改。常見(jiàn)的頁(yè)類型有：數(shù)據(jù)頁(yè)、Undo頁(yè)、系統(tǒng)頁(yè)、事務(wù)數(shù)據(jù)頁(yè)、插入緩沖位圖頁(yè)、插入緩沖空閑列表頁(yè)、未壓縮的二進(jìn)制大對(duì)象頁(yè)、壓縮的二進(jìn)制大對(duì)象頁(yè)。
行：InnoDB存儲(chǔ)引擎是面向行的(row-oriented)，每頁(yè)最多允許存放7992行數(shù)據(jù)。
行記錄格式：常見(jiàn)兩種行記錄格式Compact和Redundant，mysql5.1版本后，主要是Compact行記錄格式。對(duì)于Compact，不管是char型還是varchar型，null型都是不占用存儲(chǔ)空間的；對(duì)于Redudant,varchar的null不占用空間，char的null型是占用存儲(chǔ)空間的。

varchar類型的長(zhǎng)度限制是65535，其實(shí)達(dá)不到，會(huì)有別的開(kāi)銷，一般是65530左右，這還跟選取的字符集有關(guān)。此外這個(gè)長(zhǎng)度限制是一整行的，例如：create table test(a varchar(22000), b varchar(22000), cvarchar(22000)) charset=latin1 engine=innodb也會(huì)報(bào)錯(cuò)。

對(duì)于blob類型的數(shù)據(jù)，在數(shù)據(jù)頁(yè)面中只保存了varchar(65535)的前768個(gè)字節(jié)前綴數(shù)據(jù)，之后跟的是偏移量，指向行溢出頁(yè)，也就是Uncompressed BLOB Page。新的InnoDB Plugin引入了新的文件格式稱為Barracuda，其有兩種新的行記錄格式Compressed和Dynamic，兩者對(duì)于存入Blog字段采用了完全溢出的方式，在數(shù)據(jù)庫(kù)頁(yè)中存放20個(gè)字節(jié)的指針，實(shí)際的數(shù)據(jù)都存入在BLOB Page中。           





數(shù)據(jù)頁(yè)結(jié)構(gòu)：數(shù)據(jù)頁(yè)結(jié)構(gòu)由以下7個(gè)部分組成：
File Header(文件頭):記錄頁(yè)的一些頭信息，如頁(yè)偏移量、上一頁(yè)、下一頁(yè)、頁(yè)類型等，固定長(zhǎng)度為38個(gè)字節(jié)。

Page Header(頁(yè)頭)：記錄頁(yè)的狀態(tài)信息，堆中記錄數(shù)、指向空閑列表的指針、已刪除記錄的字節(jié)數(shù)、最后插入的位置等，固定長(zhǎng)度共56個(gè)字節(jié)。

Infimun+Supremum Records:在InnoDB存儲(chǔ)引擎中，每個(gè)數(shù)據(jù)頁(yè)中有兩個(gè)虛擬的行記錄，用來(lái)限定記錄的邊界。

Infimun記錄是比該頁(yè)中任何主鍵都要小的值，Supermum指比任何可能大的值還要大的值。這兩個(gè)值在頁(yè)創(chuàng)建時(shí)被建立，并且在任何情況下不會(huì)被刪除。在Compact行格式和Redundant行格式下，兩者占用的字節(jié)數(shù)各不相同。



User Records(用戶記錄，即行記錄):實(shí)現(xiàn)記錄的內(nèi)容。再次強(qiáng)調(diào)，InnoDB存儲(chǔ)引擎表總是B+村索引組織的。
Free Space(空閑空間)：指空閑空間，同樣也是個(gè)鏈表數(shù)據(jù)結(jié)構(gòu)。當(dāng)一條記錄被刪除后，該空間會(huì)被加入空閑鏈 表中。

Page Directory(頁(yè)目錄)：頁(yè)目錄存放了記錄的相對(duì)位置，并不是偏移量，有些時(shí)候這些記錄稱為Slots(槽)，InnoDB并不是每個(gè)記錄一個(gè)槽，槽是一個(gè)稀疏目錄，即一個(gè)槽中可能屬于多個(gè)記錄，最少屬于4條記錄，最多屬于8條記錄。需要牢記的是，B+樹(shù)索引本身并不能找到具體的一條記錄，B+樹(shù)索引能找到只是該記錄所在的頁(yè)。數(shù)據(jù)庫(kù)把頁(yè)載入內(nèi)存，然后通過(guò)Page Directory再進(jìn)行二叉查找。只不過(guò)二叉查找的時(shí)間復(fù)雜度低，同時(shí)內(nèi)存中的查找很快，因此通過(guò)忽略了這部分查找所用的時(shí)間。

File Trailer(文件結(jié)尾信息)：為了保證頁(yè)完整地寫(xiě)入磁盤(pán)(如寫(xiě)過(guò)程的磁盤(pán)損壞、機(jī)器宕機(jī)等)，固定長(zhǎng)8個(gè)字節(jié)。                                         

視圖：Mysql中的視圖總是虛擬的表，本身不支持物化視圖。但是通過(guò)一些其他技巧(如觸發(fā)器)，同樣也可以實(shí)現(xiàn)一些簡(jiǎn)單的物化視圖的功能。

分區(qū)：Mysql數(shù)據(jù)庫(kù)支持RANGE、LIST、HASH、KEY、COLUMNS分區(qū)，并且可以使用HASH或KEY來(lái)進(jìn)行子分區(qū)。

mysql innodb常見(jiàn)索引與算法：
B+樹(shù)索引：B+樹(shù)的數(shù)據(jù)結(jié)構(gòu)相對(duì)較復(fù)雜，B代表的是balance最早是從平衡二叉樹(shù)演化而來(lái)，但B+樹(shù)并不是一個(gè)二叉樹(shù)，對(duì)其較詳細(xì)的介紹可以參見(jiàn)這篇文章：http://blog.csdn.net/v_JULY_v/article/details/6530142 由于B+樹(shù)索引的高扇出性，因此在數(shù)據(jù)庫(kù)中，B+樹(shù)的高度一般都在2~3層，也就對(duì)于查找某一鍵值的行記錄，最多只要2到3次IO,現(xiàn)在一般的磁盤(pán)每秒至少可以做100次IO，2~3次的IO意味著查詢時(shí)間只需0.02~0.03秒。

數(shù)據(jù)庫(kù)中的B+索引可以分為聚集索引(clustered index)和輔助聚集索引(secondary index),但其內(nèi)部都是B+樹(shù)的，即高度平衡的，葉子節(jié)點(diǎn)存放數(shù)據(jù)。

聚集索引：由于聚集索引是按照主鍵組織的，所以每一張表只能有一個(gè)聚集索引，每個(gè)數(shù)據(jù)頁(yè)都通過(guò)雙向鏈表進(jìn)行連接，葉子節(jié)點(diǎn)存放一整行的信息，所以查詢優(yōu)化器更傾向走聚集索引。此外，對(duì)于聚集索引的存儲(chǔ)是邏輯上連續(xù)的。所以，聚集索引對(duì)于主鍵的排序查找和范圍查找速度非常快。

輔助索引：也叫非聚集索引，葉子節(jié)點(diǎn)不存全部數(shù)據(jù)，主要存鍵值及一個(gè)boomark(其實(shí)就是聚集索引的鍵)告訴InnoDB哪里可以找到與索引相對(duì)應(yīng)的行數(shù)據(jù)，如一個(gè)高度為3的輔助索引和一個(gè)高度為3的聚集索引，若根據(jù)輔助索引來(lái)查詢行記錄，一共需要6次IO。另外輔助索引可以有多個(gè)。


索引的使用原則：高選擇、取出表中的少部分?jǐn)?shù)據(jù)(也稱為唯一索引)。一般取出的數(shù)據(jù)量超過(guò)表中數(shù)據(jù)的20%，優(yōu)化器不會(huì)使用索引，而進(jìn)行全表掃描。如對(duì)于性別等字段是沒(méi)有意義的。

聯(lián)合索引： 也稱復(fù)合索引，是在多列（>=2）上建立的索引。Innodb中的復(fù)合索引也是b+ tree結(jié)構(gòu)。索引的數(shù)據(jù)包含多列(col1, col2, col3…)，在索引中依次按照col1, col2, col3排序。如(1, 2), (1, 3),(2,0)…使用復(fù)合索引要充分利用最左前綴原則，顧名思義，就是最左優(yōu)先。如創(chuàng)建索引ind_col1_col2(col1, col2)，那么在查詢where col1 = xxx and col2 = xx或者where col1 = xxx都可以走ind_col1_col2索引，但where col2=****是走不到索引的。在創(chuàng)建多列索引時(shí)，要根據(jù)業(yè)務(wù)需求，where子句中使用最頻繁且過(guò)濾效果好的的一列放在最左邊。

哈希索引：哈希算法也是比較常見(jiàn)的算法，mysql innoDB中使用了比較常見(jiàn)的鏈地址法進(jìn)行去重。此外上面已經(jīng)提及，innoDB中的hash是自適應(yīng)的，什么時(shí)候使用hash是系統(tǒng)決定的，無(wú)法進(jìn)行人工設(shè)置。
二分查找法：這個(gè)算法比較常見(jiàn)，這里就不多提及了。在InnoDB中，每頁(yè)P(yáng)age Directory中的槽是按照主鍵的順序存放的，對(duì)于某一條具體記錄的查詢是通過(guò)對(duì)Page Directory進(jìn)行二分查找得到的。

mysql innodb中的鎖
InnoDB存儲(chǔ)引擎鎖的實(shí)現(xiàn)和Oracle非常類似，提供一致性的非鎖定讀、行級(jí)鎖支持、行級(jí)鎖沒(méi)有相關(guān)的開(kāi)銷，可以同時(shí)得到并發(fā)性和一致性。
InnoDB存儲(chǔ)引擎實(shí)現(xiàn)了如下兩種標(biāo)準(zhǔn)的行級(jí)鎖：
共享鎖(S Lock)：允許事務(wù)讀一行數(shù)據(jù)；
排他鎖(X Lock)：允許事務(wù)刪除或者更新一行數(shù)據(jù)。
當(dāng)一個(gè)事務(wù)已經(jīng)獲得了行r的共享鎖，那么另外的事務(wù)可以立即獲得行r的共享鎖，因?yàn)樽x取沒(méi)有改變行r的數(shù)據(jù)，我們稱這種情況為鎖兼容。但如果有事務(wù)想獲得行r的排他鎖，則它必須等待事務(wù)釋放行r上的共享鎖————這種情況稱為鎖不兼容。



在InnoDB Plugin之前，只能通過(guò)SHOW FULL PROCESSLIST，SHOW ENGINE INOODB STATUS等命令來(lái)查看當(dāng)前的數(shù)據(jù)庫(kù)請(qǐng)求，然后再判斷當(dāng)前事務(wù)中的鎖的情況。新版本的InnoDB Plugin中，在INFORMATION_SCHEMA架構(gòu)下添加了INNODB_TRX、INNODB_LOCKS、InnoDB_LOCK_WAITS。通過(guò)這三張表，可以更簡(jiǎn)單地監(jiān)控當(dāng)前的事務(wù)并分析可能存在的鎖的問(wèn)題。
INNODB_TRX由8個(gè)字段組成：
trx_id:InnoDB存儲(chǔ)引擎內(nèi)部唯一的事務(wù)ID
trx_state:當(dāng)前事務(wù)的狀態(tài)。
trx_started:事務(wù)的開(kāi)始時(shí)間。
trx_requested_lock_id:等待事務(wù)的鎖ID。如trx_state的狀態(tài)為L(zhǎng)OCK WAIT,那么該值代表當(dāng)前的等待之前事務(wù)占用鎖資源的ID.
若trx_state不是LOCK WAIT,則該值為NULL。
trx_wait_started:事務(wù)等待開(kāi)始的時(shí)間。
trx_weight:事務(wù)的權(quán)重，反映了一個(gè)事務(wù)修改和鎖住的行數(shù)。在InnoDB存儲(chǔ)引擎中，當(dāng)發(fā)生死鎖需要回滾時(shí)，InnoDB存儲(chǔ)會(huì)選
擇該值最小的進(jìn)行回滾。
trx_mysql_thread_id:Mysql中的線程ID,SHOW PROCESSLIST顯示的結(jié)果。
trx_query:事務(wù)運(yùn)行的sql語(yǔ)句。
通過(guò)select * from infomation_schema.INNODB_TRX;可查看 

INNODB_LOCKS表，該表由如下字段組成：
     lock_id:鎖的ID。
     lock_trx_id:事務(wù)ID。
     lock_mode:鎖的模式。
     lock_type:鎖的類型，表鎖還是行鎖。
     lock_table:要加鎖的表。
     lock_index:鎖的索引。
     lock_space:InnoDB存儲(chǔ)引擎表空間的ID號(hào)。
     lock_page:被鎖住的頁(yè)的數(shù)量。若是表鎖，則該值為NULL。
     lock_rec:被鎖住的行的數(shù)量。若是表鎖，則該值為NULL。
     lock_data:被鎖住的行的主鍵值。當(dāng)是表鎖時(shí)，該值為NULL。
     通過(guò)select * from information_schema.INNODB_LOCK;可查看

     INNODB_LOCK_WAIT由4個(gè)字段組成：
     requesting_trx_id:申請(qǐng)鎖資源的事務(wù)ID。
     requesting_lock_id:申請(qǐng)的鎖的ID。
     blocking_trx_id:阻塞的鎖的ID。
     通過(guò)select * from information_schema.INNODB_LOCK_WAITS;可查看。 

一致性的非鎖定讀：InnoDB存儲(chǔ)引擎通過(guò)行多版本控制的方式來(lái)讀取當(dāng)前執(zhí)行時(shí)間數(shù)據(jù)庫(kù)中行的數(shù)據(jù)。如果讀取的行正在執(zhí)行Delete、update操作，這時(shí)讀取操作不會(huì)因此而會(huì)等待行上鎖的釋放，相反，InnoDB存儲(chǔ)引擎會(huì)去讀取行的一個(gè)快照數(shù)據(jù)?？煺諗?shù)據(jù)是指該行之前版本的數(shù)據(jù)，該實(shí)現(xiàn)是通過(guò)Undo段來(lái)實(shí)現(xiàn)。而Undo用來(lái)事務(wù)中回滾數(shù)據(jù)，因此快照本身是沒(méi)有額外開(kāi)銷的。此外，快照數(shù)據(jù)是不需要上鎖的，因?yàn)闆](méi)有必要對(duì)歷史的數(shù)據(jù)進(jìn)行修改。一個(gè)行可能有不止一個(gè)快照數(shù)據(jù)，所以稱這種技術(shù)為行多版本技術(shù)。由此帶來(lái)并發(fā)控制，稱之為多版本并發(fā)控制(Multi VersionConcurrency Control, MVCC)。

事務(wù)的隔離級(jí)別：Read uncommitted、Read committed、Repeatable read、serializable。在Read Committed和Repeatable Read下，InnoDB存儲(chǔ)引擎使用非鎖定一致性讀。然而，對(duì)于快照的定義卻不同。在Read Committed事務(wù)隔離級(jí)別下，對(duì)于快照數(shù)據(jù)，非一致性讀總是讀取被鎖定行的最新一份快照數(shù)據(jù)。在Repeatable事務(wù)隔離級(jí)別下，對(duì)于快照數(shù)據(jù)，非一致性讀總是讀取事務(wù)開(kāi)始時(shí)的行數(shù)據(jù)版本。                 


鎖的算法：
     Record Lock:單行記錄上的鎖
     Gap Lock:間隙鎖，鎖定一個(gè)范圍，但不包含記錄本身
     Next-Key Lock:Gap Lock + Record Lock，鎖定一個(gè)范圍，并且鎖定記錄本身。更加詳細(xì)的介紹可以參見(jiàn)這篇blog,http://www.db110.com/?p=1848

鎖的問(wèn)題：
丟失更新：經(jīng)典的數(shù)據(jù)庫(kù)問(wèn)題，當(dāng)兩個(gè)或多個(gè)事務(wù)選擇同一行，然后基于最初選定的值更新該行時(shí)，會(huì)發(fā)生丟失更新問(wèn)題。每個(gè)事務(wù)都不知道其它事務(wù)的存在。最后的更新將重寫(xiě)由其它事務(wù)所做的更新，這將導(dǎo)致數(shù)據(jù)丟失。 　　
         例:
             事務(wù)A和事務(wù)B同時(shí)修改某行的值，
              1.事務(wù)A將數(shù)值改為1并提交
              2.事務(wù)B將數(shù)值改為2并提交。
              這時(shí)數(shù)據(jù)的值為2，事務(wù)A所做的更新將會(huì)丟失。
              解決辦法：事務(wù)并行變串行操作，對(duì)更新操作加排他鎖。

        臟讀：一個(gè)事務(wù)讀到另一個(gè)事務(wù)未提交的更新數(shù)據(jù)，即讀到臟數(shù)據(jù)。
          例:
              1.Mary的原工資為1000, 財(cái)務(wù)人員將Mary的工資改為了8000(但未提交事務(wù))       
              2.Mary讀取自己的工資 ,發(fā)現(xiàn)自己的工資變?yōu)榱?000，歡天喜地！
              3.而財(cái)務(wù)發(fā)現(xiàn)操作有誤，回滾了事務(wù),Mary的工資又變?yōu)榱?000, 像這樣,Mary記取的工資數(shù)8000是一個(gè)臟數(shù)據(jù)。
              解決辦法：臟讀只有在事務(wù)隔離級(jí)別是Read Uncommitted的情況下才會(huì)出現(xiàn)，innoDB默認(rèn)隔離級(jí)別是Repeatable Read，所以生產(chǎn)環(huán)境下不會(huì)出現(xiàn)臟讀。

        不可重復(fù)讀：在同一個(gè)事務(wù)中,多次讀取同一數(shù)據(jù),返回的結(jié)果有所不同。換句話說(shuō)就是,后續(xù)讀取可以讀到另一個(gè)事務(wù)已提交的更新數(shù)據(jù)。相反"可重復(fù)讀"在同一事務(wù)多次讀取數(shù)據(jù)時(shí),能夠保證所讀數(shù)據(jù)一樣,也就是后續(xù)讀取不能讀到另一事務(wù)已提交的更新數(shù)據(jù)。臟讀和不可重復(fù)讀的主要區(qū)別在于，臟讀是讀到未提交的數(shù)據(jù)，不可重復(fù)讀是讀到已提交的數(shù)據(jù)。
          例:
              1.在事務(wù)1中，Mary 讀取了自己的工資為1000,操作并沒(méi)有完成
              2.在事務(wù)2中，這時(shí)財(cái)務(wù)人員修改了Mary的工資為2000,并提交了事務(wù).
              3.在事務(wù)1中，Mary 再次讀取自己的工資時(shí)，工資變?yōu)榱?000
              解決辦法：讀到已提交的數(shù)據(jù)，一般數(shù)據(jù)庫(kù)是可接受的，因此事務(wù)隔離級(jí)別一般設(shè)為Read Committed。Mysql InnoDB通過(guò)Next-Key Lock算法避免不可重復(fù)讀，默認(rèn)隔離級(jí)別為Repeatable Read。

mysql innodb中的事務(wù)   
事務(wù)的四個(gè)特性：原子性、一致性、隔離性、持久性
隔離性通過(guò)鎖實(shí)現(xiàn)，原子性、一致性、持久性通過(guò)數(shù)據(jù)庫(kù)的redo和undo來(lái)完成。
重做日志記錄了事務(wù)的行為，通過(guò)redo實(shí)現(xiàn)，保證了事務(wù)的完整性，但事務(wù)有時(shí)還需要撤銷，這時(shí)就需要產(chǎn)生undo。undo和redo正好相反，對(duì)于數(shù)據(jù)庫(kù)進(jìn)行修改時(shí)，數(shù)據(jù)庫(kù)不但會(huì)產(chǎn)生redo，而且還會(huì)產(chǎn)生一定的undo，即使執(zhí)行的事務(wù)或語(yǔ)句由于某種原因失敗了，或者如果用一條rollback語(yǔ)句請(qǐng)求回滾，就可以用這些undo信息將數(shù)據(jù)回滾到修改之前的樣子。與redo不同的是,redo存放在重做日志文件中，undo存放在數(shù)據(jù)庫(kù)內(nèi)部的一個(gè)特殊段(segment)中，這稱為undo段(undo segment)，undo段位于共享表空間內(nèi)。還有一點(diǎn)重要的是，undo記錄的是與事務(wù)操作相反的邏輯操作，如insert undo 記錄一個(gè)delete，所以u(píng)ndo只是邏輯地將數(shù)據(jù)庫(kù)恢復(fù)成事務(wù)開(kāi)始前的樣子。如:insert 10萬(wàn)行的數(shù)據(jù)，可能導(dǎo)致表空間增大，回滾后，表空間不會(huì)減小回去。

您可能感興趣的文章:

• 基于MySQL體系結(jié)構(gòu)的分析
• 詳解mysql中的存儲(chǔ)引擎
• 關(guān)于MySQL Memory存儲(chǔ)引擎的相關(guān)知識(shí)
• 詳解MySQL InnoDB存儲(chǔ)引擎的內(nèi)存管理
• Mysql中存儲(chǔ)引擎的區(qū)別及比較
• MySQL系列之七 MySQL存儲(chǔ)引擎
• 詳解MySql中InnoDB存儲(chǔ)引擎中的各種鎖
• MySQL之MyISAM存儲(chǔ)引擎的非聚簇索引詳解
• MySQL的存儲(chǔ)引擎InnoDB和MyISAM
• mysql?體系結(jié)構(gòu)和存儲(chǔ)引擎介紹