MySql Innodb瀛樺偍寮曟搸-閿佸拰浜嬪姟
聽
聽
lock鍜宭atch鐨勬瘮杈?/strong>
latch 涓€鑸О涓洪棭閿?杞婚噺绾х殑閿? 鍥犱负鍏惰姹傞攣瀹氱殑鏃堕棿闈炲父鐭紝鑻ヨ繜鍕嬫椂闂撮暱锛屽垯搴旂敤鎬ц兘闈炲父宸紝鍦↖nnoDB瀛樺偍寮曟搸涓紝latch鏈夊彲浠ュ垎涓簃utex(浜掓枼閿?鍜宺wlock(璇诲啓閿?鍏剁洰鐨勭敤鏉ヤ繚璇佸苟鍙戠嚎绋嬫搷浣滀复鐣岃祫婧愮殑姝ｇ‘鎬э紝骞朵笖娌℃湁姝婚攣妫€娴嬬殑鏈哄埗
聽
lock鐨勫璞℃槸浜嬪姟锛岀敤鏉ラ攣瀹氱殑鏄暟鎹簱涓殑UI鎯筹紝濡傝〃銆侀〉銆佽銆傚苟涓斾竴鑸琹ock瀵硅薄浠呭湪浜嬪姟commit鎴杛ollback鍚庤繘琛岄噴鏀?涓嶅悓浜嬪姟闅旂绾у埆閲婃斁鐨勬椂闂村彲鑳戒笉鍚?锛屾澶杔ock姝ｅ澶у鏁版暟鎹簱涓竴鏍凤紝鏄湁姝婚攣鏈哄埗鐨勩€傝〃鏄剧ず浜唋ock涓巐atch鐨勪笉鍚?/p>
聽
mysql> SHOW ENGINE INNODB MUTEX; +--------+-------------------+-------------+ | Type | Name | Status | +--------+-------------------+-------------+ | InnoDB | dict0dict.cc:1057 | os_waits=2 | | InnoDB | log0log.cc:844 | os_waits=1 | | InnoDB | fil0fil.cc:1690 | os_waits=1 | | InnoDB | dict0dict.cc:1066 | os_waits=3 | | InnoDB | log0log.cc:907 | os_waits=11 | +--------+-------------------+-------------+
鍦―EBUG鐗堟湰涓嬶紝閫氳繃SHOW ENGINE INNODB MUTEX 鍙互鐪嬪埌latch鐨勬洿澶氫俊鎭?/p>
debug涓璼tatus瀛楁涓殑鍙傛暟浠嬬粛
聽
鑻ュ皢涓婇攣鐨勫璞＄湅鎴愪竴妫垫爲锛岄偅涔堝鏈€涓婂眰鐨勫璞′笂閿侊紝涔熷氨鏄鏈€缁嗙矑搴︾殑瀵硅薄杩涜涓婇攣锛岄偅涔堥鍏堥渶瑕佸绮楃矑搴︾殑瀵硅薄涓婇攣锛屽涓婂浘锛屽鏋滈渶瑕佸椤典笂鐨勮褰時杩涜涓奨閿侊紝閭ｄ箞鍒嗗埆闇€瑕佸鏁版嵁A銆佽〃銆侀〉涓婃剰鍚戦攣IX锛屾渶鍚庡璁板綍r涓奨閿?鑻ュ叾涓换浣曚竴閮ㄥ垎瀵艰嚧绛夊緟锛岄偅涔堣鎿嶄綔闇€瑕佺瓑寰呯矖绮掑害閿佺殑瀹屾垚
聽
聽
聽
聽
聽
innodb閿佺浉鍏崇殑琛?/strong>
INNODB_LOCKS琛?/p>
a) 聽 聽 聽 聽lock_id锛氶攣鐨刬d浠ュ強琚攣浣忕殑绌洪棿id缂栧彿銆侀〉鏁伴噺銆佽鏁伴噺
b) 聽 聽 聽 聽lock_trx_id锛氶攣鐨勪簨鍔d銆?/p>
c) 聽 聽 聽 聽lock_mode锛氶攣鐨勬ā寮忋€?/p>
d) 聽 聽 聽 聽lock_type锛氶攣鐨勭被鍨嬶紝琛ㄩ攣杩樻槸琛岄攣
e) 聽 聽 聽 聽lock_table锛氳鍔犻攣鐨勮〃銆?/p>
f) 聽 聽 聽 聽 聽lock_index锛氶攣鐨勭储寮曘€?/p>
g) 聽 聽 聽 聽lock_space锛歩nnodb瀛樺偍寮曟搸琛ㄧ┖闂寸殑id鍙风爜
h) 聽 聽 聽 聽lock_page锛氳閿佷綇鐨勯〉鐨勬暟閲忥紝濡傛灉鏄〃閿侊紝鍒欎负null鍊笺€?/p>
i) 聽 聽 聽 聽 聽lock_rec锛氳閿佷綇鐨勮鐨勬暟閲忥紝濡傛灉琛ㄩ攣锛屽垯涓簄ull鍊笺€?/p>
j) 聽 聽 聽 聽 聽lock_data锛氳閿佷綇鐨勮鐨勪富閿€硷紝濡傛灉琛ㄩ攣锛屽垯涓簄ull鍊笺€?/p>
聽
innodb_lock_waits琛?/p>
1) 聽 聽 聽 聽requesting_trx_id锛氱敵璇烽攣璧勬簮鐨勪簨鍔d銆?/p>
2) 聽 聽 聽 聽requested_lock_id锛氱敵璇风殑閿佺殑id銆?/p>
3) 聽 聽 聽 聽blocking_trx_id锛氶樆濉炵殑浜嬪姟id銆?/p>
4) 聽 聽 聽 聽blocking_lock_id锛氶樆濉炵殑閿佺殑id銆?/p>
聽
innodb_trx琛?/p>
trx_id浜嬪姟ID
trx_state浜嬪姟鐘舵€?/p>
trx_started浜嬪姟寮€濮嬫椂闂?/p>
trx_requested_lock_idinnodb_locks.lock_id
trx_wait_started浜嬪姟寮€濮嬬瓑寰呯殑鏃堕棿
trx_weight
trx_mysql_thread_id浜嬪姟绾跨▼ID
trx_query鍏蜂綋SQL璇彞
trx_operation_state浜嬪姟褰撳墠鎿嶄綔鐘舵€?/p>
trx_tables_in_use浜嬪姟涓湁澶氬皯涓〃琚娇鐢?/p>
trx_tables_locked浜嬪姟鎷ユ湁澶氬皯涓攣
trx_lock_structs
trx_lock_memory_bytes浜嬪姟閿佷綇鐨勫唴瀛樺ぇ灏忥紙B锛?/p>
trx_rows_locked浜嬪姟閿佷綇鐨勮鏁?/p>
trx_rows_modified浜嬪姟鏇存敼鐨勮鏁?/p>
trx_concurrency_tickets浜嬪姟骞跺彂绁ㄦ暟
trx_isolation_level浜嬪姟闅旂绾у埆
trx_unique_checks鏄惁鍞竴鎬ф鏌?/p>
trx_foreign_key_checks鏄惁澶栭敭妫€鏌?/p>
trx_last_foreign_key_error鏈€鍚庣殑澶栭敭閿欒
trx_adaptive_hash_latched
trx_adaptive_hash_timeout
聽
聽
聽
聽
聽
涓€鑷存€ч潪閿佸畾璇?consistent nonlocking read)鏄寚InnoDB瀛樺偍寮曟搸閫氳繃澶氱増鏈帶鍒?multi versionning)鐨勬柟寮忔潵璇诲彇褰撳墠鎵ц鏃堕棿鏁版嵁搴撲腑琛岀殑鏁版嵁锛屽鏋滆鍙栫殑琛屾鍦ㄦ墽琛孌ELETE鎴朥PDATE鎿嶄綔锛岃繖鏄鍙栨搷浣滀笉浼氬洜姝ょ瓑寰呰涓婇攣鐨勯噴鏀俱€傜浉鍙嶇殑锛孖nnoDB浼氬幓璇诲彇琛岀殑涓€涓揩鐓ф暟鎹?/p>
聽
聽
涓€鑷存€ч攣瀹氳鐨凷QL璇硶
-- 鎺掍粬閿? SELECT ...... FOR UPDATE -- 鍏变韩閿? SELECT ...... LOCK IN SHARE MODE
聽
聽
聽
鑷閿?/strong>
-- 鍐呴儴鐢ㄤ笅鍒楁柟寮忓疄鐜扮殑 SELECT max(auto_inc_col) FROM t FOR UPDATE;
MySQL 5.1.22鐗堟湰寮€濮嬶紝InnoDB鎻愪緵浜嗕竴绉嶈交閲忕骇浜掓枼閲忕殑鑷闀垮疄鐜版満鍒讹紝杩欑鏈哄埗澶уぇ鎻愰珮浜嗚嚜澧為暱鎻掑叆鐨勬€ц兘銆?/p>
InnoDB鎻愪緵浜嗕竴涓弬鏁癷nnodb_autoinc_lock_mode鏉ユ帶鍒惰嚜澧為暱鐨勬ā寮忥紝璇ュ弬鏁扮殑榛樿鍊间负1锛屽湪缁х画璁ㄨ鏂扮殑鑷闀垮疄鐜版柟寮忎箣鍓嶏紝闇€瑕佸鑷闀跨殑鎻掑叆杩涜鍒嗙被
聽
鍙傛暟innodb_autoinc_lock_mode浠ュ強鍚勪釜璁剧疆涓嬪鑷鐨勫奖鍝嶏紝鍏跺叡鏈?涓湁鏁堝€煎彲浠ヨ瀹?0 1 2聽
聽
聽
聽
聽
MySql閿佺殑绫诲瀷
InnoDB鏈変笁绉嶈閿佺殑绠楁硶锛?/p>
1锛孯ecord Lock锛氬崟涓璁板綍涓婄殑閿併€?/p>
2锛孏ap Lock锛氶棿闅欓攣锛岄攣瀹氫竴涓寖鍥达紝浣嗕笉鍖呮嫭璁板綍鏈韩銆侴AP閿佺殑鐩殑锛屾槸涓轰簡闃叉鍚屼竴浜嬪姟鐨勪袱娆″綋鍓嶈锛屽嚭鐜板够璇荤殑鎯呭喌銆?/p>
3锛孨ext-Key Lock锛?+2锛岄攣瀹氫竴涓寖鍥达紝骞朵笖閿佸畾璁板綍鏈韩銆傚浜庤鐨勬煡璇紝閮芥槸閲囩敤璇ユ柟娉曪紝涓昏鐩殑鏄В鍐冲够璇荤殑闂銆?/p>
innodb瀵逛簬鍞竴绱㈠紩锛岃繕鏈変富閿储寮曚娇鐢ㄧ殑鏄疪ecord Lock锛屽浜庢櫘閫氱储寮曪紝鑱斿悎绱㈠紩鎵嶄細浣跨敤next key lock绠楁硶锛屽洜涓哄敮涓€绱㈠紩鏄竴涓‘瀹氱殑鍊硷紝涓嶉渶瑕侀攣瀹氫竴涓寖鍥寸殑銆?/p>
next key lock涔熷彲浠ヨВ鍐冲够璇荤殑闂
MySql涓閿佹鏌ユ槸閫氳繃瓒呮椂鏈哄埗锛岃繕鏈?wait-for graph绠楁硶瑙ｅ喅鐨?/p>
聽
聽
聽
聽
浜嬪姟鐨勯殧绂荤骇鍒?/strong>
=================================================================================
聽闅旂绾у埆 聽 聽 聽 聽 聽 聽鑴忚锛圖irty Read锛?聽 聽 聽 涓嶅彲閲嶅璇伙紙NonRepeatable Read锛?聽 聽 骞昏锛圥hantom Read锛壜?/p>
=================================================================================
聽
鏈彁浜よ锛圧ead uncommitted锛?聽 聽 聽 聽鍙兘 聽 聽 聽 聽 聽 聽 聽 聽 聽 聽 聽 聽 聽 聽鍙兘 聽 聽 聽 聽 聽 聽 聽 聽 聽 聽 聽 鍙兘
聽
宸叉彁浜よ锛圧ead committed锛?聽 聽 聽 聽 聽涓嶅彲鑳?聽 聽 聽 聽 聽 聽 聽 聽 聽 聽 聽 聽 聽鍙兘 聽 聽 聽 聽 聽 聽 聽 聽 聽 聽 聽 聽鍙兘
聽
鍙噸澶嶈锛圧epeatable read锛?聽 聽 聽 聽 聽涓嶅彲鑳?聽 聽 聽 聽 聽 聽 聽 聽 聽 聽 聽 聽 聽涓嶅彲鑳?聽 聽 聽 聽 聽 聽 聽 聽 聽 聽 鍙兘
聽
鍙覆琛屽寲锛圫erializable 锛?聽 聽 聽 聽 聽 聽 聽 聽涓嶅彲鑳?聽 聽 聽 聽 聽 聽 聽 聽 聽 聽 聽 聽 聽涓嶅彲鑳?聽 聽 聽 聽 聽 聽 聽 聽 聽 聽 涓嶅彲鑳?/p>
聽
================================================================================
路鏈彁浜よ(Read Uncommitted)锛氬厑璁歌剰璇伙紝涔熷氨鏄彲鑳借鍙栧埌鍏朵粬浼氳瘽涓湭鎻愪氦浜嬪姟淇敼鐨勬暟鎹?/p>
路鎻愪氦璇?Read Committed)锛氬彧鑳借鍙栧埌宸茬粡鎻愪氦鐨勬暟鎹€侽racle绛夊鏁版暟鎹簱榛樿閮芥槸璇ョ骇鍒?(涓嶉噸澶嶈)
路鍙噸澶嶈(Repeated Read)锛氬彲閲嶅璇汇€傚湪鍚屼竴涓簨鍔″唴鐨勬煡璇㈤兘鏄簨鍔″紑濮嬫椂鍒讳竴鑷寸殑锛孖nnoDB榛樿绾у埆銆傚湪SQL鏍囧噯涓紝璇ラ殧绂荤骇鍒秷闄や簡涓嶅彲閲嶅璇伙紝浣嗘槸杩樺瓨鍦ㄥ够璞¤
路涓茶璇?Serializable)锛氬畬鍏ㄤ覆琛屽寲鐨勮锛屾瘡娆¤閮介渶瑕佽幏寰楄〃绾у叡浜攣锛岃鍐欑浉浜掗兘浼氶樆濉?/p>
聽
聽
聽
闃诲鐩稿叧鐨勫弬鏁?/strong>
-- 鎺у埗绛夊緟鐨勬椂闂达紝鍔ㄦ€佸弬鏁? innodb_lock_wait_timeout -- 鐢ㄦ潵璁惧畾鏄惁鍦ㄧ瓑寰呰秴鏃舵椂瀵硅繘琛屼腑鐨勪簨鐗╄繘琛屽洖婊氭搷浣?榛樿鏄疧FF涓嶅洖婊?锛岄潤鎬佸弬鏁? innodb_rollback_on_timeout
聽
聽
閿佸崌绾?/strong>
1.鐢卞崟鐙殑SQL鍦ㄤ竴涓璞′笂鎸佹湁鐨勯攣鏁伴噺瓒呰繃浜嗛榾鍊硷紝涔嬮粯璁ゅ€兼槸5000锛屽鏋滄槸涓嶅悓瀵硅薄鍒欎笉浼氬崌绾?/p>
2.閿佽祫婧愬崰鐢ㄧ殑鍐呭瓨瓒呰繃浜嗘縺娲诲唴瀛樼殑40%灏变細鍙戠敓閿佸崌绾?/p>
innodb鏍规嵁姣忎釜浜嬬墿璁块棶鐨勬瘡涓〉瀵归攣杩涜绠＄悊锛岄噰鐢ㄧ殑鏄綅鍥剧殑鏂瑰紡
innodb涓嶅瓨鍦ㄩ攣鍗囩骇鐨勯棶棰橈紝杩欎簺鏄井杞殑SQL瀛樺湪鐨?/p>
聽
聽
聽
聽
聽
浜嬪姟鐨凙CID
ACID琛ㄧず鍘熷瓙鎬э紙atomicity锛夈€佷竴鑷存€э紙consistency锛夈€侀殧绂绘€э紙isolation锛夊拰鎸佷箙鎬э紙durability锛夈€備竴涓緢濂界殑浜嬪姟澶勭悊绯荤粺锛屽繀椤诲叿澶囪繖浜涙爣鍑嗙壒鎬э細
鍘熷瓙鎬э紙atomicity锛?/p>
銆€銆€涓€涓簨鍔″繀椤昏瑙嗕负涓€涓笉鍙垎鍓茬殑鏈€灏忓伐浣滃崟鍏冿紝鏁翠釜浜嬪姟涓殑鎵€鏈夋搷浣滆涔堝叏閮ㄦ彁浜ゆ垚鍔燂紝瑕佷箞鍏ㄩ儴澶辫触鍥炴粴锛屽浜庝竴涓簨鍔℃潵璇达紝涓嶅彲鑳藉彧鎵ц鍏朵腑鐨勪竴閮ㄥ垎鎿嶄綔锛岃繖灏辨槸浜嬪姟鐨勫師瀛愭€?/p>
涓€鑷存€э紙consistency锛?/p>
聽 聽 聽鏁版嵁搴撴€绘槸浠庝竴涓竴鑷存€х殑鐘舵€佽浆鎹㈠埌鍙︿竴涓竴鑷存€х殑鐘舵€併€傦紙鍦ㄥ墠闈㈢殑渚嬪瓙涓紝涓€鑷存€х‘淇濅簡锛屽嵆浣垮湪鎵ц绗笁銆佸洓鏉¤鍙ヤ箣闂存椂绯荤粺宕╂簝锛屾敮绁ㄨ处鎴蜂腑涔熶笉浼氭崯澶?00缇庡厓锛屽洜涓轰簨鍔℃渶缁堟病鏈夋彁浜わ紝鎵€浠ヤ簨鍔′腑鎵€鍋氱殑淇敼涔熶笉浼氫繚瀛樺埌鏁版嵁搴撲腑銆傦級
闅旂鎬э紙isolation锛?/p>
聽 聽 聽閫氬父鏉ヨ锛屼竴涓簨鍔℃墍鍋氱殑淇敼鍦ㄦ渶缁堟彁浜や互鍓嶏紝瀵瑰叾浠栦簨鍔℃槸涓嶅彲瑙佺殑銆傦紙鍦ㄥ墠闈㈢殑渚嬪瓙涓紝褰撴墽琛屽畬绗笁鏉¤鍙ャ€佺鍥涙潯璇彞杩樻湭寮€濮嬫椂锛屾鏃舵湁鍙﹀鐨勪竴涓处鎴锋眹鎬荤▼搴忓紑濮嬭繍琛岋紝鍒欏叾鐪嬪埌鏀エ甯愭埛鐨勪綑棰濆苟娌℃湁琚噺鍘?00缇庡厓銆傦級
鎸佷箙鎬э紙durability锛?/p>
銆€銆€涓€鏃︿簨鍔℃彁浜わ紝鍒欏叾鎵€鍋氱殑淇敼涓嶄細姘镐箙淇濆瓨鍒版暟鎹簱銆傦紙姝ゆ椂鍗充娇绯荤粺宕╂簝锛屼慨鏀圭殑鏁版嵁涔熶笉浼氫涪澶便€傛寔涔呮€ф槸涓湁鍗犳ā绯婄殑姒傚康锛屽洜涓哄疄闄呬笂鎸佷箙鎬т篃鍒嗗緢澶氫笉鍚岀殑绾у埆銆傛湁浜涙寔涔呮€х瓥鐣ヨ兘澶熸彁渚涢潪甯稿己鐨勫畨鍏ㄤ繚闅滐紝鑰屾湁浜涘垯鏈繀锛岃€屼笖涓嶅彲鑳芥湁鑳藉仛鍒?00%鐨勬寔涔呮€т繚璇佺殑绛栫暐銆傦級
聽
聽
聽
浜嬪姟鐨勫垎绫?/strong>
鎵佸钩浜嬪姟(Flat Transactions)
甯︽湁淇濆瓨鐐圭殑鎵佸钩浜嬪姟(Flat Transactions with Savepoints)
閾句簨鍔?Chained Transactions)
宓屽浜嬪姟(Nested Transactions)
鍒嗗竷寮忎簨鍔?Distributed Transactions)
聽
鎵佸钩浜嬪姟 鏄簨鍔＄被鍨嬩腑鏈€绠€鍗曠殑涓€绉嶏紝浣嗘槸鍦ㄥ疄闄呯敓浜х幆澧冧腑锛岃繖鍙兘鏄娇鐢ㄦ渶棰戠箒鐨勪簨鍔★紝鍦ㄦ墎骞充簨鍔′腑锛屾墍鏈夋搷浣滈兘澶勪簬鍚屼竴灞傛锛屽叾鐢盉EGIN WORK寮€濮嬶紝鐢盋OMMIT WORK鎴朢OLLBACK WORK缁撴潫锛屽叾闂寸殑鎿嶄綔鏄簮鑷殑锛岃涔堥兘鎵ц锛岃涔堥兘鍥炴粴锛屽洜姝ゆ墎骞充簨鍔℃槸搴旂敤绋嬪簭绉颁负鍘熷瓙鎿嶄綔鐨勭殑鍩烘湰缁勬垚妯″潡
聽
甯︽湁淇濆瓨鐐圭殑鎵佸钩浜嬪姟 瀵逛簬鎵佸钩鐨勪簨鍔℃潵璇达紝闅愬紡鐨勮缃簡涓€涓繚瀛樼偣銆傜劧鑰屾暣涓簨鍔′腑锛屽彧鏈夎繖涓€涓繚瀛樼偣锛屽洜姝わ紝鍥炴粴鍙兘浼氭粴鍒颁簨鍔″紑濮嬫椂鐨勭姸鎬侊紝淇濆瓨鐐圭敤SAVE WORK鍑芥暟鏉ュ缓绔嬶紝閫氱煡绯荤粺璁板綍褰撳墠鐨勫鐞嗙姸鎬併€傚綋鍑虹幇闂鏃讹紝淇濆瓨鐐硅兘鐢ㄤ綔鍐呴儴鐨勯噸鍚姩鐐癸紝鏍规嵁搴旂敤閫昏緫锛屽喅瀹氭槸鍥炲埌鏈€杩戜竴涓繚瀛樼偣杩樻槸鍏朵粬鏇存棭鐨勪繚瀛樼偣銆?/p>
聽
閾句簨鍔?鍙涓轰繚瀛樼偣妯″紡鐨勪竴绉嶅彉绉嶏紝甯︽湁淇濆瓨鐐圭殑鎵佸钩浜嬪姟锛屽綋鍙戠敓绯荤粺宕╂簝鏄紝鎵€鏈夌殑鐨勪繚瀛樼偣閮藉皢娑堝け锛屽洜涓哄叾淇濆瓨鐐规槸鏄撳け鐨勶紝杩欐剰鍛崇潃褰撹繘琛屾仮澶嶆椂锛屼簨鍔￠渶瑕佷粠寮€濮嬪閲嶆柊鎵ц锛岃€屼笉鑳戒粠鏈€杩戠殑涓€涓繚瀛樼偣缁х画鎵ц
閾句簨鍔＄殑鎬濇兂鏄細鍦ㄦ彁浜や竴涓簨鍔℃椂锛岄噴鏀句笉闇€瑕佺殑鏁版嵁瀵硅薄锛屽皢蹇呰鐨勫鐞嗕笂涓嬫枃闅愬紡鍦颁紶缁欎笅涓€涓寮€濮嬬殑浜嬪姟锛屾彁浜や簨鍔℃搷浣滃拰寮€濮嬩笅涓€涓簨鍔℃搷浣?灏嗗悎骞朵负涓€涓師瀛愭搷浣滐紝杩欐剰鍛崇潃涓嬩竴涓簨鍔″皢鐪嬪埌涓婁竴涓簨鍔＄殑缁撴灉锛屽氨濂藉儚涓€涓簨鍔′腑杩涜鐨勪竴鏍凤紝濡傚浘鏄剧ず浜嗛摼浜嬪姟鐨勫伐浣滄柟寮?/p>
聽
宓屽浜嬪姟 鏄竴涓眰娆＄粨鏋勬鏋讹紝鐢变竴涓《灞備簨鍔?top-level transaction)鎺у埗鐫€鍚勪釜灞傛鐨勪簨鍔★紝椤跺眰浜嬪姟涔嬩笅宓屽鐨勪簨鍔¤绉颁负瀛愪簨鍔★紝鍏舵帶鍒舵瘡涓€涓眬閮ㄧ殑鍙樻崲
閲囩敤淇濆瓨鐐规妧鏈瘮宓屽鏌ヨ鏈夋洿澶х殑鐏垫椿鎬?/p>
浣嗘槸鐢ㄤ繚瀛樼偣鎶€鏈潵妯℃嫙宓屽浜嬪姟鍦ㄩ攣鐨勬寔鏈夋柟闈㈣繕鏄笌宓屽鏌ヨ鏈変簺鍖哄埆銆傚綋閫氳繃淇濆瓨鐐规妧鏈潵妯℃嫙宓屽浜嬪姟鏃讹紝鐢ㄦ埛鏃犳硶閫夋嫨鍝簺閿侀渶瑕佽瀛愪簨鍔￠泦鎴愶紝鍝簺闇€瑕佽鐖朵簨鍔′繚鐣?/p>
聽
聽
聽
浜嬪姟鎺у埗璇彞
start transction 鏄剧ず鐨勫紑鍚竴涓簨鍔?
begin 鏄剧ず鐨勫紑鍚竴涓簨鍔?
commit 锛坈ommit work锛?
commit work涓巆ompletion_type鐨勫叧绯伙紝commit work鏄敤鏉ユ帶鍒朵簨鍔＄粨鏉熷悗鐨勮涓猴紝鏄痗hain杩樻槸release鐨勶紝鍙互閫氳繃鍙傛暟completion_type鏉ユ帶鍒讹紝榛樿涓?
rollback锛宺ollback work涓巆ommit锛宑ommit work鐨勫伐浣滃師鐞嗕竴鏍枫€?
rollback锛坮ollback work锛?
savepoint [identifier] 鍦ㄤ簨鍔′腑鍒涘缓涓€涓繚瀛樼偣锛屼竴涓簨鍔″厑璁告湁澶氫釜淇濆瓨鐐?
release savepoint [identifier] 鍒犻櫎浜嬪姟涓殑淇濆瓨鐐癸紝褰撴椂涓€涓繚瀛樼偣涔熸病鏈夋椂鎵ц杩欎釜鍛戒护锛屼細鎶ラ敊鎶涘嚭涓€涓紓甯?/pre>
聽
聽
聽
浜嬪姟鎿嶄綔鐨勭粺璁?/strong>
鍥犱负InnoDB瀛樺偍寮曟搸鏄敮鎸佷簨鍔＄殑锛屽洜姝ゅ浜嶪nnoDB瀛樺偍寮曟搸鐨勫簲鐢紝鍦ㄨ€冭檻姣忕璇锋眰鏁帮紙Question Per Second锛孮PS锛夌殑鍚屾椂锛屼篃璁告洿搴旇鍏虫敞姣忕浜嬪姟澶勭悊鐨勮兘鍔涳紙Transaction Per Second锛孴PS锛夈€?/p>
璁＄畻TPS鐨勬柟娉曟槸锛坈om_commit+com_rollback锛?time銆備絾鏄敤杩欑鏂规硶璁＄畻鐨勫墠鎻愭槸锛氭墍鏈夌殑浜嬪姟蹇呴』閮芥槸鏄惧紡鎻愪氦鐨勶紝濡傛灉瀛樺湪闅愬紡鐨勬彁浜ゅ拰鍥炴粴锛堥粯璁utocommit=1锛夛紝涓嶄細璁＄畻鍒癱om_commit鍜宑om_rollback鍙橀噺涓€?/p>
show global status like'com_commit';
show global status like'com_rollback';
聽
聽
聽
鍒嗗竷寮忎簨鍔?/p>
XA 浜嬪姟鐨勫熀纭€鏄袱闃舵鎻愪氦鍗忚銆傞渶瑕佹湁涓€涓簨鍔″崗璋冭€呮潵淇濊瘉鎵€鏈夌殑浜嬪姟鍙備笌鑰呴兘瀹屾垚浜嗗噯澶囧伐浣?绗竴闃舵)銆傚鏋滃崗璋冭€呮敹鍒版墍鏈夊弬涓庤€呴兘鍑嗗濂界殑娑堟伅锛屽氨浼氶€氱煡鎵€鏈夌殑浜嬪姟閮藉彲浠ユ彁浜や簡锛堢浜岄樁娈碉級銆侻ySQL 鍦ㄨ繖涓猉A浜嬪姟涓壆婕旂殑鏄弬涓庤€呯殑瑙掕壊锛岃€屼笉鏄崗璋冭€?浜嬪姟绠＄悊鍣?銆?/p>
聽
Mysql 鐨刋A浜嬪姟鍒嗕负鍐呴儴XA鍜屽閮╔A銆?澶栭儴XA鍙互鍙備笌鍒板閮ㄧ殑鍒嗗竷寮忎簨鍔′腑锛岄渶瑕佸簲鐢ㄥ眰浠嬪叆浣滀负鍗忚皟鑰咃紱鍐呴儴XA浜嬪姟鐢ㄤ簬鍚屼竴瀹炰緥涓嬭法澶氬紩鎿庝簨鍔★紝鐢盉inlog浣滀负鍗忚皟鑰咃紝姣斿鍦ㄤ竴涓瓨鍌ㄥ紩鎿庢彁浜ゆ椂锛岄渶瑕佸皢鎻愪氦淇℃伅鍐欏叆浜岃繘鍒舵棩蹇楋紝杩欏氨鏄竴涓垎甯冨紡鍐呴儴XA浜嬪姟锛屽彧涓嶈繃浜岃繘鍒舵棩蹇楃殑鍙備笌鑰呮槸MySQL鏈韩銆?Mysql 鍦╔A浜嬪姟涓壆婕旂殑鏄竴涓弬涓庤€呯殑瑙掕壊锛岃€屼笉鏄崗璋冭€呫€?/p>
聽
鍩烘湰璇硶
XA {START|BEGIN} xid [JOIN|RESUME] 鍚姩涓€涓猉A浜嬪姟 (xid 蹇呴』鏄竴涓敮涓€鍊? [JOIN|RESUME] 瀛楀彞涓嶈鏀寔)
XA END xid [SUSPEND [FOR MIGRATE]] 缁撴潫涓€涓猉A浜嬪姟 ( [SUSPEND [FOR MIGRATE]] 瀛楀彞涓嶈鏀寔)
XA PREPARE xid 鍑嗗
XA COMMIT xid [ONE PHASE] 鎻愪氦XA浜嬪姟
XA ROLLBACK xid 鍥炴粴XA浜嬪姟
XA RECOVER 鏌ョ湅澶勪簬PREPARE 闃舵鐨勬墍鏈塜A浜嬪姟
鎿嶄綔婕旂ず
mysql> XA START 'xatest';
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO mytable (i) VALUES(10);
Query OK, 1 row affected (0.04 sec)
mysql> XA END 'xatest';
Query OK, 0 rows affected (0.00 sec)
mysql> XA PREPARE 'xatest';
Query OK, 0 rows affected (0.00 sec)
mysql> XA COMMIT 'xatest';
Query OK, 0 rows affected (0.00 sec)
聽
聽
聽
閲嶅仛鏃ュ織
閲嶅仛鏃ュ織淇濊瘉浜嗘寔涔呮€у拰鍘熷瓙鎬э紝閿佺敤鏉ヤ繚鎸侀殧绂绘€э紝undo log鍒欎繚鎸佷簡涓€鑷存€?/p>
閲嶅仛鏃ュ織鍖呭惈閲嶅仛鏃ュ織缂撳啿(redo log buffer)鍜岄噸鍋氭棩蹇楁枃浠?redo log file)
聽
涓轰簡纭繚姣忔鏃ュ織閮借兘鍐欏叆鏃ュ織鏂囦欢锛屽湪姣忔灏嗛噸鍋氭棩蹇楃紦鍐插啓鍏ラ噸鍋氭棩蹇楁枃浠跺悗锛孖nnoDB瀛樺偍寮曟搸閮介渶瑕佽皟鐢ㄤ竴娆sync鎿嶄綔锛岀敱浜庨噸鍋氭棩蹇楁枃浠舵墦寮€骞舵病鏈変娇鐢∣_DIRECT閫夐」锛屽洜姝ら噸鍋氭棩蹇楃紦鍐插厛鍐欏叆鏂囦欢绯荤粺缂撳瓨銆備负浜嗙‘淇濋噸鍋氭棩蹇楀啓鍏ョ鐩橈紝蹇呴』杩涜fsync鎿嶄綔銆傜敱浜巉sync鐨勬晥鐜囧彇鍐充簬纾佺洏鐨勬€ц兘锛屽洜姝ょ鐩樼殑鎬ц兘鍐冲畾浜嗕簨鍔℃彁浜ょ殑鎬ц兘锛屼篃灏辨槸鏁版嵁搴撶殑鎬ц兘
鍙傛暟innodb_flush_log_at_trx_commit鐢ㄦ潵鎺у埗閲嶅仛鏃ュ織鍒锋柊鍒扮鐩樼殑绛栫暐
1 锛岃〃绀轰簨鍔℃彁浜ゆ椂蹇呴』璋冪敤涓€娆sync鎿嶄綔锛?/p>
0琛ㄧず浜嬪姟鎻愪氦鏃朵笉杩涜鍐欏叆閲嶅仛鏃ュ織鎿嶄綔锛岃繖涓搷浣滀粎鍦╩aster thread涓畬鎴愶紝涔熷氨鏄瘡绉掑埛鏂颁竴娆★紝浣嗗彲鑳戒細鍑虹幇鐬棿瀹曟満鏁版嵁涓㈠け鐨勫彲鑳芥€?/p>
2 琛ㄧず浜嬪姟鎻愪氦鏃跺皢閲嶅仛鏃ュ織鍐欏叆閲嶅仛鏃ュ織鏂囦欢锛屼絾浠呭啓鍏ユ枃浠剁郴缁熺殑缂撳瓨涓紝涓嶈繘琛宖sync鎿嶄綔銆傚湪杩欎釜璁剧疆涓嬶紝褰揗ySQL鍙戠敓瀹曟満鑰屾搷浣滅郴缁熶笉鍙戠敓瀹曟満鏃讹紝骞朵笉浼氬鑷翠簨鍔＄殑涓㈠け锛岃€屽綋鎿嶄綔绯荤粺瀹曟満鏃讹紝閲嶅惎鏁版嵁搴撲細涓㈠け鏈粠鏂囦欢绯荤粺缂撳瓨鍒锋柊鍒伴噸鍋氭棩蹇楁枃浠剁殑閭ｉ儴鍒嗕簨鍔?/p>
閲嶅仛鏃ュ織缂撳啿鍖哄拰閲嶅仛鏃ュ織缁勪箣闂寸殑鍏崇郴
聽
鍦↖nnoDB瀛樺偍寮曟搸涓紝閲嶅仛鏃ュ織閮芥槸浠?12瀛楄妭杩涜瀛樺偍鐨勶紝杩欐剰鍛崇潃閲嶅仛鏃ュ織缂撳瓨銆侀噸鍋氭棩蹇楁枃浠跺潡閮芥槸浠ュ潡block鐨勬柟寮忚繘琛屼繚瀛樼殑锛岀О涓洪噸鍋氭棩蹇楀潡(redo log block)姣忓潡鐨勫ぇ灏?12瀛楄妭
聽
聽
鏃ュ織鍧楃敱涓夐儴鍒嗙粍鎴愶紝渚濇涓烘棩蹇楀揩澶?log block header)銆佹棩蹇楀唴瀹?log body)銆佹棩蹇楀潡灏?log block tailer)
聽
LOG_BLOCK_HDR_NO鐢ㄦ潵鏍囪杩欎釜鏁扮粍涓殑浣嶇疆锛屽挨鍏舵槸閫掑骞朵笖寰幆浣跨敤鐨勩€傚崰鐢?涓瓧鑺傘€備絾鏄敱浜庣涓€浣嶇敤鏉ュ垽鏂槸鍚︽槸flush bit锛屾墍浠ユ渶澶у€间负2G
LOG_BLOCK_HDR_DATA_LEN鍗犵敤2涓瓧鑺傦紝琛ㄧずlog block鎵€鍗犵敤鐨勫ぇ灏忥紝褰搇og block琚啓婊℃椂锛岃鍊间负0x200,琛ㄧず浣跨敤鍏ㄩ儴鐨刲og block绌洪棿锛屽嵆鍗犵敤512瀛楄妭
LOG_BLOCK_FIRST_REC_GROUP 鍗犵敤2涓瓧鑺傦紝琛ㄧずlog block涓涓€涓棩蹇楁墍鍦ㄧ殑鍋忕Щ閲忋€傚鏋滆鍊肩殑澶у皬鍜孡OG_BLOCK_HDR_DATA_LEN鐩稿悓锛屽垯琛ㄧず褰撳墠log block涓嶅寘鍚柊鐨勬棩蹇?/p>
LOG_BLOCK_CHECKPOINT_NO鍗犵敤4瀛楄妭锛岃〃绀鸿log block鏈€鍚庤鍐欏叆鏃剁殑妫€鏌ョ偣绗?瀛楄妭鐨勫€?/p>
LOG_BLOCK_TAILER 鍙敱1涓儴鍒嗙粍鎴愶紝涓斿€煎拰LOG_BLOCK_HDR_NO鐩稿悓锛屽苟鍦ㄥ嚱鏁發og_block_init涓鍒濆鍖?LOG_BLOCK_TRL_NO 澶у皬涓?瀛楄妭
聽
聽
閲嶅仛鏃ュ織鏍煎紡
閫氱敤鐨勫ご閮ㄦ牸寮忕敱涓€涓?閮ㄥ垎缁勬垚
redo_log_type 閲嶅仛鏃ュ織绫诲瀷
space: 琛ㄧ┖闂碔D
page_no 椤电殑鍋忕Щ閲?/p>
涔嬪悗鏄痳edo log body 聽
聽
body浣撶殑鍐呭
聽
SHOW ENGINE INNODB STATUS鏌ョ湅LSN鐨勬儏鍐?/p>
---
LOG
---
Log sequence number 18766833801 -- 琛ㄧず褰撳墠鐨凩SN
Log flushed up to 18766832201 -- 琛ㄧず鍒锋柊鍒伴噸鍋氭棩蹇楁枃浠剁殑LSN
Pages flushed up to 18766816420
Last checkpoint at 18766816420 -- 琛ㄧず鍒锋柊鍒扮鐩樼殑LSN
鐢变簬checkpoint琛ㄧず宸茬粡鍒锋柊鍒扮鐩橀〉涓婄殑LSN锛屽洜姝ゅ湪鎭㈠杩囩▼涓粎闇€鎭㈠checkpoint寮€濮嬬殑鏃ュ織閮ㄥ垎
聽
鍐嶆斁涓€寮犲畬鏁寸殑log block鍒嗘瀽鍥?/p>
聽
聽
聽
undo log
涓轰簡婊¤冻浜嬪姟鐨勫師瀛愭€э紝鍦ㄦ搷浣滀换浣曟暟鎹箣鍓嶏紝棣栧厛灏嗘暟鎹浠藉埌Undo Log锛岀劧鍚庤繘琛屾暟鎹殑淇敼銆傚鏋滃嚭鐜颁簡閿欒鎴栬€呯敤鎴锋墽琛屼簡ROLLBACK璇彞锛岀郴缁熷彲浠ュ埄鐢║ndo Log涓殑澶囦唤灏嗘暟鎹仮澶嶅埌浜嬪姟寮€濮嬩箣鍓嶇殑鐘舵€併€備笌redo log涓嶅悓鐨勬槸锛岀鐩樹笂涓嶅瓨鍦ㄥ崟鐙殑undo log鏂囦欢锛屽畠瀛樻斁鍦ㄦ暟鎹簱鍐呴儴鐨勪竴涓壒娈婃(segment)涓紝杩欑О涓簎ndo娈?undo segment)锛寀ndo娈典綅浜庡叡浜〃绌洪棿鍐呫€?/p>
姝ゅundo log杩樼敤鏉ュ仛浜嬪姟鐨凪MVC鍔熻兘锛屽鐗堟湰骞跺彂
undo log涓嶆槸鐗╃悊鎿嶄綔锛屾槸閫昏緫鐨勫弽鍚戞搷浣滐紝姣斿鎻掑叆涓€涓褰曪紝鍦╱ndo log涓氨浼氭湁涓€涓垹闄よ褰曠殑鎿嶄綔锛屾洿鏂颁竴涓褰曪紝undo灏卞仛鍙嶅悜鏇存柊锛屽悓鐞嗗垹闄ゆ搷浣渦ndo log灏辫褰曚竴涓彃鍏ユ搷浣溿€?/p>
鎻掑叆1W鏉¤褰曞悗鍙嶅悜鎿嶄綔锛屽彲鑳戒細浣垮緱椤靛彉寰楁洿澶с€?/p>
聽
鐩稿叧鍙傛暟
聽
innodb_undo_directory 鐢ㄦ潵璁剧疆rollback segment鏂囦欢鎵€鍦ㄧ殑璺緞
innodb_undo_logs 鐢ㄦ潵璁剧疆rollback segment鐨勪釜鏁帮紝榛樿涓?28
innodb_undo_tablespaces 鐢ㄦ潵璁剧疆鏋勬垚rollback segment鏂囦欢鐨勬暟閲?/pre>
浜嬪姟鎻愪氦鍚庡苟涓嶄細椹笂鍒犻櫎undo log锛岃繖鏄洜涓鸿繕鏈夊叾浠栦簨鍔￠渶瑕乽ndo log鏉ュ緱鍒颁箣鍓嶇殑鐗堟湰锛岃兘鍚﹀垹闄ndo log鐢眕urge绾跨▼鍒ゆ柇
聽
聽
涓轰簡淇濊瘉浜嬪姟骞跺彂鎿嶄綔鏃讹紝鍦ㄥ啓鍚勮嚜鐨剈ndo log鏃朵笉浜х敓鍐茬獊锛孖nnoDB閲囩敤鍥炴粴娈电殑鏂瑰紡鏉ョ淮鎶ndo log鐨勫苟鍙戝啓鍏ュ拰鎸佷箙鍖栥€傚洖婊氭瀹為檯涓婃槸涓€绉?Undo 鏂囦欢缁勭粐鏂瑰紡锛屾瘡涓洖婊氭鍙堟湁澶氫釜undo log slot銆?/p>
聽
聽
聽
聽
undo log鐨刬nsert鏍煎紡
聽
undo log鐨剈pdate鍜宒elete鏍煎紡
聽
INNODB_TRX_ROLLBACK_SEGMENT 聽杩欎釜鏁版嵁瀛楀吀琛ㄧ敤鏉ユ煡鐪媟ollback segment锛堝洖婊氭锛夈€?/p>
鍙互鏌ョ湅鏌愪釜璁板綍鎻掑叆鍝釜琛紝鍝釜椤碉紝鎻掑叆鐨勫亸绉婚噺鍜岄暱搴︼紝杩欐牱鍙互鑾峰彇椤典腑鐨剈ndo log鍐呭
INNODB_TRX_UNDO 聽鐢ㄦ潵璁板綍浜嬪姟瀵瑰簲鐨剈ndo log锛屾柟渚緿BA鍜屽紑鍙戜汉鍛樿缁嗕簡瑙ｆ瘡涓簨鍔′骇鐢熺殑undo閲?span style="font-size: 14px;">聽
聽
瀵逛簬delete鎿嶄綔锛屾暟鎹苟涓嶆槸椹笂鍒犻櫎锛岃€屾槸澧炲姞涓€涓爣蹇椾綅锛屾渶鍚庣敱purge绾跨▼寮傛鍒犻櫎杩欎簺鏁版嵁
鎵€鏈夌殑undo log浼氭斁鍒颁竴涓垪琛ㄤ腑锛岀劧鍚巔urge浼氫緷娆℃壂鎻忚繖涓垪琛ㄥ垽鏂摢浜涘彲浠ュ垹闄?/p>
涓嬮潰鎿嶄綔浼氶鍏堟鏌rx1锛屽彂鐜皌rx1寮曠敤鍒颁簡trx5锛岃€宼rx5琚崰鐢ㄤ簬鏄笉鑳藉垹闄わ紝鍐嶆鏌rx2鍙戠幇鍙互鍒犻櫎鍥犱负娌℃湁浜哄紩鐢ㄤ簡
聽
聽
缁勬彁浜?group commit)鏄疢YSQL澶勭悊鏃ュ織鐨勪竴绉嶄紭鍖栨柟寮忥紝涓昏涓轰簡瑙ｅ喅鍐欐棩蹇楁椂棰戠箒鍒风鐩樼殑闂銆傜粍鎻愪氦浼撮殢鐫€MYSQL鐨勫彂灞曚笉鏂紭鍖栵紝浠庢渶鍒濆彧鏀寔redo log 缁勬彁浜わ紝鍒扮洰鍓?.6瀹樻柟鐗堟湰鍚屾椂鏀寔redo log 鍜宐inlog缁勬彁浜ゃ€傜粍鎻愪氦鐨勫疄鐜板ぇ澶ф彁楂樹簡mysql鐨勪簨鍔″鐞嗘€ц兘锛屼笅鏂囧皢浠nnodb 瀛樺偍寮曟搸涓轰緥锛岃缁嗕粙缁嶇粍鎻愪氦鍦ㄥ悇涓樁娈电殑瀹炵幇鍘熺悊銆?/p>
缁勬彁浜ゆ€濇兂鏄紝灏嗗涓簨鍔edo log鐨勫埛鐩樺姩浣滃悎骞讹紝鍑忓皯纾佺洏椤哄簭鍐欍€侷nnodb鐨勬棩蹇楃郴缁熼噷闈紝姣忔潯redo log閮芥湁涓€涓狶SN(Log Sequence Number)锛孡SN鏄崟璋冮€掑鐨勩€傛瘡涓簨鍔℃墽琛屾洿鏂版搷浣滈兘浼氬寘鍚竴鏉℃垨澶氭潯redo log锛屽悇涓簨鍔″皢鏃ュ織鎷疯礉鍒發og_sys_buffer鏃?log_sys_buffer 閫氳繃log_mutex淇濇姢)锛岄兘浼氳幏鍙栧綋鍓嶆渶澶х殑LSN锛屽洜姝ゅ彲浠ヤ繚璇佷笉鍚屼簨鍔＄殑LSN涓嶄細閲嶅銆?/p>
聽
聽
聽
鍙傝€?/strong>
閫氳繃渚嬪瓙鐞嗚В浜嬪姟鐨?绉嶉殧绂荤骇鍒?/a>
MySQL浜嬪姟闅旂绾у埆璇﹁В
Innodb閿佹満鍒讹細Next-Key Lock 娴呰皥
MySQL涓竴鑷存€ч潪閿佸畾璇?/a>
MySql姝婚攣闂鍒嗘瀽
MySQL 路 寮曟搸鐗规€?路 InnoDB redo log婕父
MySQL redo log鍙妑ecover杩囩▼娴呮瀽
MySQL涓璻edo鏃ュ織
MySQL 路 寮曟搸鐗规€?路 InnoDB undo log 婕父
InnoDB undo log瑙ｆ瀽锛堜竴锛?/a>
MySQL 鐮旂┒innodb_max_purge_lag鍒嗕韩
MYSQL-GroupCommit
XA/JTA/MYSQL涓ら樁娈垫彁浜や簨鍔?/a>
閽堝SSD鐨凪ySQL IO浼樺寲