《Mysql應(yīng)用基于mysql體系結(jié)構(gòu)的深入解析》要點(diǎn)：
本文介紹了Mysql應(yīng)用基于mysql體系結(jié)構(gòu)的深入解析，希望對(duì)您有用。如果有疑問(wèn)，可以聯(lián)系我們。

由：連接池組件、管理服務(wù)和工具組件、sql接口組件、查詢(xún)分析器組件、優(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)用用戶(hù)活動(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)有用戶(hù)活動(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)速度的提升,則建立哈希索引,所以稱(chēng)之為自適應(yīng)的.自適應(yīng)哈希索引只能用來(lái)搜索等值的查詢(xún),如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)制日志文件、慢查詢(xún)?nèi)罩疚募?、查?xún)?nèi)罩疚募?
錯(cuò)誤日志：通過(guò)show variables like 'log_error'來(lái)查看錯(cuò)誤日志存放地址
慢查詢(xún)?nèi)罩荆?/strong>通過(guò)show variables like '%long%' 查看慢查詢(xún)?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)有使用索引的查詢(xún)記錄到慢日志中.mysql中可以直接通過(guò)mysqldumpslow命令來(lái)查看慢日志.
二進(jìn)制文件：不記錄查詢(xú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)銷(xiāo).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è)類(lèi)型有：數(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類(lèi)型的長(zhǎng)度限制是65535,其實(shí)達(dá)不到,會(huì)有別的開(kāi)銷(xiāo),一般是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類(lèi)型的數(shù)據(jù),在數(shù)據(jù)頁(yè)面中只保存了varchar(65535)的前768個(gè)字節(jié)前綴數(shù)據(jù),之后跟的是偏移量,指向行溢出頁(yè),也就是Uncompressed BLOB Page.新的InnoDB Plugin引入了新的文件格式稱(chēng)為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è)類(lèi)型等,固定長(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(用戶(hù)記錄,即行記錄):實(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í)候這些記錄稱(chēng)為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意味著查詢(xún)時(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)存放一整行的信息,所以查詢(xú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)查詢(xún)行記錄,一共需要6次IO.另外輔助索引可以有多個(gè).


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

聯(lián)合索引： 也稱(chēng)復(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),那么在查詢(xún)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ì)于某一條具體記錄的查詢(xún)是通過(guò)對(duì)Page Directory進(jìn)行二分查找得到的.

mysql innodb中的鎖
InnoDB存儲(chǔ)引擎鎖的實(shí)現(xiàn)和Oracle非常類(lèi)似,提供一致性的非鎖定讀、行級(jí)鎖支持、行級(jí)鎖沒(méi)有相關(guān)的開(kāi)銷(xiāo),可以同時(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ù),我們稱(chēng)這種情況為鎖兼容.但如果有事務(wù)想獲得行r的排他鎖,則它必須等待事務(wù)釋放行r上的共享鎖――――這種情況稱(chēng)為鎖不兼容.



在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:鎖的類(lèi)型,表鎖還是行鎖.
???? 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)銷(xiāo)的.此外,快照數(shù)據(jù)是不需要上鎖的,因?yàn)闆](méi)有必要對(duì)歷史的數(shù)據(jù)進(jìn)行修改.一個(gè)行可能有不止一個(gè)快照數(shù)據(jù),所以稱(chēng)這種技術(shù)為行多版本技術(shù).由此帶來(lái)并發(fā)控制,稱(chēng)之為多版本并發(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í)還需要撤銷(xiāo),這時(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)中,這稱(chēng)為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ì)減小回去.

轉(zhuǎn)載請(qǐng)注明本頁(yè)網(wǎng)址：
http://www.snjht.com/jiaocheng/2133.html