±à¼ÍƼö: |
±¾ÎÄÀ´×ÔÓÚCSDN£¬±¾ÎÄÖ÷Òª½éÉÜmysqlÌåϵ½á¹¹¡¢mysql¸÷¸ö´æ´¢ÒýÇæÒÔ¼°innodbµÄÏà¹ØÖªÊ¶µÈ¡£ |
|
mysqlÌåϵ½á¹¹£º
ÓÉ£ºÁ¬½Ó³Ø×é¼þ¡¢¹ÜÀí·þÎñºÍ¹¤¾ß×é¼þ¡¢sql½Ó¿Ú×é¼þ¡¢²éѯ·ÖÎöÆ÷×é¼þ¡¢ÓÅ»¯Æ÷×é¼þ¡¢
»º³å×é¼þ¡¢²å¼þʽ´æ´¢ÒýÇæ¡¢ÎïÀíÎļþ×é³É¡£
mysqlÊǶÀÓеIJå¼þʽÌåϵ½á¹¹£¬¸÷¸ö´æ´¢ÒýÇæÓÐ×Ô¼ºµÄÌØµã¡£
mysql¸÷¸ö´æ´¢ÒýÇæ¸ÅÊö£º
innodb´æ´¢ÒýÇæ£º[/color][/b] ÃæÏòoltp(online transaction
processing)¡¢ÐÐËø¡¢Ö§³ÖÍâ¼ü¡¢·ÇËø¶¨¶Á¡¢Ä¬ÈϲÉÓÃrepeaable¼¶±ð£¨¿ÉÖØ¸´¶Á£©Í¨¹ýnext-keylocking²ßÂÔ±ÜÃâ»Ã¶Á¡¢²åÈ뻺³å¡¢¶þ´Îд¡¢×ÔÊÊÓ¦¹þÏ£Ë÷Òý¡¢Ô¤¶Á
myisam´æ´¢ÒýÇæ: ²»Ö§³ÖÊÂÎñ¡¢±íËø¡¢È«ÎÄË÷Òý¡¢ÊʺÏolap£¨ÔÚÏß·ÖÎö´¦Àí£©£¬ÆäÖÐmyd:·ÅÊý¾ÝÎļþ£¬myi:·ÅË÷ÒýÎļþ
ndb´æ´¢ÒýÇæ£º ¼¯Èº´æ´¢ÒýÇæ£¬share nothing£¬¿ÉÌá¸ß¿ÉÓÃÐÔ
memory´æ´¢ÒýÇæ£º Êý¾Ý´æ·ÅÔÚÄÚ´æÖУ¬±íËø£¬²¢·¢ÐÔÄܲĬÈÏʹÓùþÏ£Ë÷Òý
archive´æ´¢ÒýÇæ£ºÖ»Ö§³ÖinsertºÍselect zlibË㷨ѹËõ1£º10£¬Êʺϴ洢¹éµµÊý¾ÝÈçÈÕÖ¾µÈ¡¢ÐÐËø
maria´æ´¢ÒýÇæ£º Ä¿µÄÈ¡´úmyisam¡¢»º´æÊý¾ÝºÍË÷Òý¡¢ÐÐËø¡¢mvcc

innodbÌØÐÔ£º
Ö÷Ìåϵ½á¹¹£ºÄ¬ÈÏ7¸öºǫ́Ị̈߳¬4¸öio thread(insert buffer¡¢log¡¢read¡¢write),1¸ömaster
thread(ÓÅÏȼ¶×î¸ß),1¸öËø(lock)¼à¿ØỊ̈߳¬1¸ö´íÎó¼à¿ØÏ̡߳£¿ÉÒÔͨ¹ýshow engine
innodb statusÀ´²é¿´¡£Ð°汾ÒѶÔĬÈϵÄread threadºÍwrite thread·Ö±ðÔö´óµ½4¸ö£¬¿Éͨ¹ýshow
variables like 'innodb_io_thread%'²é¿´¡£
´æ´¢ÒýÇæ×é³É£º»º³å³Ø(buffer pool)¡¢ÖØ×öÈÕÖ¾»º³å³Ø(redo log buffer)ÒÔ¼°¶îÍâµÄÄÚ´æ³Ø(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'À´²é¿´¡£
»º³å³Ø£ºÕ¼×î´ó¿éÄڴ棬ÓÃÀ´´æ·Å¸÷ÖÖÊý¾ÝµÄ»º´æ°üÀ¨ÓÐË÷ÒýÒ³¡¢Êý¾ÝÒ³¡¢undoÒ³¡¢²åÈ뻺³å¡¢×ÔÊÊÓ¦¹þÏ£Ë÷Òý¡¢innodb´æ´¢µÄËøÐÅÏ¢¡¢Êý¾Ý×ÖµäÐÅÏ¢µÈ¡£¹¤×÷·½Ê½×ÜÊǽ«Êý¾Ý¿âÎļþ°´Ò³(ÿҳ16k)¶ÁÈ¡µ½»º³å³Ø£¬È»ºó°´×î½ü×îÉÙʹÓÃ(lru)µÄËã·¨À´±£ÁôÔÚ»º³å³ØÖеĻº´æÊý¾Ý¡£Èç¹ûÊý¾Ý¿âÎļþÐèÒªÐ޸ģ¬×ÜÊÇÊ×ÏÈÐÞ¸ÄÔÚ»º´æ³ØÖеÄÒ³(·¢ÉúÐ޸ĺó¼´ÎªÔàÒ³)£¬È»ºóÔÙ°´ÕÕÒ»¶¨µÄƵÂʽ«»º³å³ØµÄÔàҳˢе½Îļþ¡£Í¨¹ýÃüÁîshow
engine innodb status;À´²é¿´¡£
ÈÕÖ¾»º³å£º½«ÖØ×öÈÕÖ¾ÐÅÏ¢ÏÈ·ÅÈëÕâ¸ö»º³åÇø£¬È»ºó°´Ò»¶¨ÆµÂʽ«Æäˢе½ÖØ×öÈÕÖ¾Îļþ¡£

master thread:
loopÖ÷Ñ»·Ã¿ÃëÒ»´ÎµÄ²Ù×÷£º
ÈÕÖ¾»º³åˢе½´ÅÅÌ£¬¼´Ê¹Õâ¸öÊÂÎñ»¹Ã»ÓÐÌá½»¡£(×ÜÊÇÖ´ÐУ¬ËùÒÔÔÙ´óµÄÊÂÎñcommit
µÄʱ¼äÒ²ÊǺܿìµÄ)
ºÏ²¢²åÈ뻺³å(innodbµ±Ç°Ò»Ãë·¢ÉúµÄio´ÎÊýСÓÚ5´ÎÔòÖ´ÐÐ)
ÖÁ¶àË¢ÐÂ100¸öinnodbµÄ»º³å³ØÖеÄÔàÒ³µ½´ÅÅÌ(³¬¹ýÅäÖõÄÔàÒ³ËùÕ¼»º³å³Ø±ÈÀýÔòÖ´
ÐУ¬ÔÚÅäÖÃÎļþÖÐÓÉinnodb_max_dirty_pages_pac¾ö¶¨£¬Ä¬ÈÏÊÇ90£¬Ð°汾ÊÇ75£¬
google½¨ÒéÊÇ80)
Èç¹ûµ±Ç°Ã»ÓÃÓû§»î¶¯£¬Çл»µ½backgroud loop
loopÖ÷Ñ»·Ã¿10ÃëÒ»´ÎµÄ²Ù×÷£º
Ë¢ÐÂ100¸öÔàÒ³µ½´ÅÅÌ(¹ýÈ¥10ÃëIO²Ù×÷СÓÚ200´ÎÔòÖ´ÐÐ)
ºÏ²¢ÖÁ¶à5¸ö²åÈ뻺³å(×ÜÊÇ)
½«ÈÕÖ¾»º³åµ½´ÅÅÌ(×ÜÊÇ)
ɾ³ýÎÞÓõÄUndoÒ³(×ÜÊÇ)
Ë¢ÐÂ100¸ö»òÕß10¸öÔàÒ³µ½´ÅÅÌ(Óг¬¹ý70%µÄÔàÒ³£¬Ë¢ÐÂ100¸öÔàÒ³;·ñÔòË¢ÐÂ10¸öÔàÒ³)
²úÉúÒ»¸ö¼ì²éµã
backgroud loop£¬Èôµ±Ç°Ã»ÓÐÓû§»î¶¯(Êý¾Ý¿â¿ÕÏÐʱ)»òÕßÊý¾Ý¿â¹Ø±Õʱ£¬¾Í»áÇл»µ½Õâ¸öÑ»·£º
ɾ³ýÎÞÓõÄUndoÒ³(×ÜÊÇ)
ºÏ²¢20¸ö²åÈ뻺³å(×ÜÊÇ)
Ìø»Øµ½Ö÷Ñ»·(×ÜÊÇ)
²»¶ÏË¢ÐÂ100¸öÒ³£¬Ö±µ½·ûºÏÌõ¼þ(¿ÉÄÜÔÚflush loopÖÐÍê³É)
Èç¹ûflush loopÖÐҲûÓÐʲôÊÂÇé¿ÉÒÔ×öÁË£¬InnoDB´æ´¢ÒýÇæ»áÇл»µ½suspend_loop£¬½«master
thread¹ÒÆð£¬µÈ´ýʼþµÄ·¢Éú¡£ÈôÆôÓÃÁËInnoDB´æ´¢ÒýÇæ£¬È´Ã»ÓÐʹÓÃÈκÎInnoDB´æ´¢ÒýÇæµÄ±í£¬ÄÇômaster
thread×ÜÊÇ´¦ÓÚ¹ÒÆð״̬
²åÈ뻺³å£º²»ÊÇ»º³å³ØµÄÒ»²¿·Ö£¬Insert BufferÊÇÎïÀíÒ³µÄÒ»¸ö×é³É²¿·Ö,Ëü´øÀ´InnoDBÐÔÄܵÄÌá¸ß¡£¸ù¾ÝB+Ëã·¨(ÏÂÎÄ»áÌáµ½)µÄÌØµã£¬²åÈëÊý¾ÝµÄʱºò»áÖ÷¼üË÷ÒýÊÇ˳ÐòµÄ£¬²»»áÔì³ÉÊý¾Ý¿âµÄËæ»ú¶ÁÈ¡£¬¶ø¶ÔÓڷǾۼ¯Ë÷Òý(¼´¸¨ÖúË÷Òý)£¬Ò¶×Ó½ÚµãµÄ²åÈë²»ÔÙÊÇ˳ÐòµÄÁË£¬ÕâʱÐèÒªÀëÉ¢µØ·ÃÎʷǾۼ¯Ë÷Òý£¬²åÈëÐÔÄÜÔÚÕâÀï±äµÍÁË¡£InnoDBÒýÈë²åÈ뻺³å£¬ÅжϷǾۼ¯Ë÷ÒýÒ³ÊÇ·ñÔÚ»º³å³ØÖУ¬Èç¹ûÔÚÔòÖ±½Ó²åÈë;²»ÔÚ£¬ÔòÏÈ·ÅÔÚ
²åÈ뻺³åÇøÖС£È»ºó¸ù¾ÝÉÏÊömaster threadÖнéÉܵ쬻áÓÐÒ»¶¨µÄƵÂʽ«²åÈ뻺³åºÏ²¢¡£´ËÍ⣬¸¨ÖúË÷Òý²»ÄÜÊÇΨһµÄ£¬ÒòΪ²åÈëµ½²åÈ뻺³åʱ£¬²¢²»È¥²éÕÒË÷ÒýÒ³µÄÇé¿ö£¬·ñÔòÈÔÈ»»áÔì³ÉËæ»ú¶Á£¬Ê§È¥²åÈ뻺³åµÄÒâÒåÁË¡£²åÈ뻺³å¿ÉÄÜ»áÕ¼»º³å³ØÖÐÄڴ棬ĬÈÏÒ²ÄÜ»áÕ¼µ½1/2£¬ËùÒÔ¿ÉÒÔ½«Õâ¸öÖµµ÷Сµã£¬µ½1/3¡£Í¨¹ýIBUF_POOL_SIZE_PER_MAX_SIZEÀ´ÉèÖã¬2±íʾ1/2,3±íʾ1/3¡£
Á½´Îд£º Ëü´øÀ´InnoDBÊý¾ÝµÄ¿É¿¿ÐÔ¡£Èç¹ûдʧЧ£¬¿ÉÒÔͨ¹ýÖØ×öÈÕÖ¾½øÐлָ´£¬µ«ÊÇÖØ×öÈÕÖ¾ÖмǼµÄÊǶÔÒ³µÄÎïÀí²Ù×÷£¬Èç¹ûÒ³±¾ÉíË𻵣¬ÔÙ¶ÔÆä½øÐÐÖØ×öÊÇûÓÐÒâÒåµÄ¡£ËùÒÔ£¬ÔÚÓ¦ÓÃÖØ×öÈÕ־ǰ£¬ÐèÒªÒ»¸öÒ³µÄ¸±±¾£¬µ±Ð´ÈëʧЧ·¢Éúʱ£¬ÏÈͨ¹ýÒ³µÄ¸±±¾À´»¹Ô¸ÃÒ³£¬ÔÙ½øÐÐÖØ×ö£¬Õâ¾ÍÊÇdoublewire¡£
»Ö¸´Êý¾Ý=Ò³¸±±¾+ÖØ×öÈÕÖ¾

×ÔÊÊÓ¦¹þÏ£Ë÷Òý£ºInnoDB´æ´¢ÒýÇæÌá³öÒ»ÖÖ×ÔÊÊÓ¦¹þÏ£Ë÷Òý£¬´æ´¢ÒýÇæ»á¼à¿Ø¶Ô±íÉÏË÷ÒýµÄ²éÕÒ£¬Èç¹û¹Û²ìµ½½¨Á¢½¨Á¢¹þÏ£Ë÷Òý»á´øÀ´ËٶȵÄÌáÉý£¬Ôò½¨Á¢¹þÏ£Ë÷Òý£¬ËùÒÔ³ÆÖ®Îª×ÔÊÊÓ¦µÄ¡£×ÔÊÊÓ¦¹þÏ£Ë÷ÒýÖ»ÄÜÓÃÀ´ËÑË÷µÈÖµµÄ²éѯ£¬Èçselect
* from table where index_col='***', ´ËÍâ×ÔÊÊÓ¦¹þÏ£ÊÇÓÉInnoDB´æ´¢ÒýÇæ¿ØÖƵģ¬ÎÒÃÇÖ»ÄÜͨ¹ýinnodb_adaptive_hash_indexÀ´½ûÓûòÆôÓã¬Ä¬ÈÏ¿ªÆô¡£
mysql Îļþ
²ÎÊýÎļþ£º¸æËßMysqlʵÀýÆô¶¯Ê±ÔÚÄÄÀï¿ÉÒÔÕÒµ½Êý¾Ý¿âÎļþ£¬²¢ÇÒÖ¸¶¨Ä³Ð©³õʼ»¯²ÎÊý£¬ÕâЩ²ÎÊý¶¨ÒåÁËijÖÖÄÚ´æ½á¹¹µÄ´óСµÈÉèÖá£ÓÃÎļþ´æ´¢£¬¿É±à¼£¬ÈôÆô¶¯Ê±¼ÓÔØ²»µ½Ôò²»Äܳɹ¦Æô¶¯£¨ÓëÆäËûÊý¾Ý¿â²»Í¬£©¡£²ÎÊýÓж¯Ì¬ºÍ¾²Ì¬Ö®·Ö£¬¾²Ì¬Ï൱ÓÚÖ»¶Á£¬¶¯Ì¬ÊÇ¿ÉÒÔsetµÄ¡£ÈçÎÒÃÇͨ¹ýshow
variable like '***'²é³öÀ´µÄkey¡¢valueÖµ£¬ÊÇ¿ÉÒÔͨ¹ýset key=valueÖ±½ÓÐ޸ĵġ£Í¬ÊÇ£¬ÐÞ¸Äʱ»¹ÓÐ×÷ÓÃÓòÖ®·Ö£¬¼´Õâ¸öseesion¸öÓÐЧºÍÈ«¾ÖÓÐЧ£¬ÔÚ¶ÔÓ¦µÄkeyǰ¼ÓÉÏsession»òglobal¼´¿É£¬Èçselect
@@seesion.read_buffer_size¡¢set @@global.read_buffer_size¡£
ÈÕÖ¾Îļþ£ºÓÃÀ´¼Ç¼MysqlʵÀý¶ÔijÖÖÌõ¼þ×ö³öÏìӦʱдÈëµÄÎļþ¡£Èç´íÎóÈÕÖ¾Îļþ¡¢¶þ½øÖÆÈÕÖ¾Îļþ¡¢Âý²éѯÈÕÖ¾Îļþ¡¢²éѯÈÕÖ¾ÎļþµÈ¡£
´íÎóÈÕÖ¾£ºÍ¨¹ýshow variables like 'log_error'À´²é¿´´íÎóÈÕÖ¾´æ·ÅµØÖ·
Âý²éѯÈÕÖ¾£ºÍ¨¹ýshow variables like '%long%' ²é¿´Âý²éѯÈÕÖ¾¼Ç¼µÄãÐÖµ£¬Ð°汾Éè³ÉÁË0.05£»Í¨¹ýshow
variables like 'log_slow_queries'²é¿´ÊÇ·ñ¿ªÆôÁË£¬Ä¬ÈÏΪ¹Ø±ÕµÄ£»Í¨¹ýshow
variabes like 'log_queries_not_using_indexes'²é¿´Êǽ«Ã»ÓÐʹÓÃË÷ÒýµÄ²éѯ¼Ç¼µ½ÂýÈÕÖ¾ÖС£mysqlÖпÉÒÔÖ±½Óͨ¹ýmysqldumpslowÃüÁîÀ´²é¿´ÂýÈÕÖ¾¡£
¶þ½øÖÆÎļþ£º²»¼Ç¼²éѯ£¬Ö»¼Ç¼¶ÔÊý¾Ý¿âËùÓеÄÐ޸IJÙ×÷¡£Ä¿µÄÊÇΪÁ˻ָ´(point-in-timeÐÞ¸´)ºÍ¸´ÖÆ¡£Í¨¹ýshow
variables like 'datadir'²é¿´´æ·Å·¾¶¡£¶þ½øÖÆÈÕÖ¾Ö§³ÖSTATEMENT¡¢ROW¡¢MIXÈýÖÖ¸ñʽ£¬Í¨¹ýbinlog_format²ÎÊýÉ趨£¬Í¨³£ÉèÖÃΪROW£¬¿ÉÒÔΪÊý¾Ý¿âµÄ»Ö¸´ºÍ¸´ÖÆ´øÀ´¸üºÃµÄ¿É¿¿ÐÔ£¬µ«»á´øÀ´¶þ½øÖÆÎļþ´óСµÄÔö¼Ó£¬¸´ÖÆÊ±»áÔö¼ÓÍøÂ翪Ïú¡£mysqlÖÐͨ¹ýmysqlbinlog²é¿´¶þ½øÖÆÈÕÖ¾ÎļþÄÚÈÝ¡£
socketÎļþ£ºµ±ÓÃUnixÓòÌ×½Ó×Ö·½Ê½½øÐÐÁ¬½ÓʱÐèÒªµÄÎļþ¡£
pidÎļþ£ºMysqlʵÀýµÄ½ø³ÌIDÎļþ¡£
Mysql±í½á¹¹Îļþ£ºÓÃÀ´´æ·ÅMysql±í½á¹¹¶¨ÒåÎļþ¡£ÒòΪMysql²å¼þʽ´æ´¢ÒýÇæµÄÌåϵ½á¹¹£¬Ã¿¸ö±í¶¼ÓÐÒ»¸ö¶ÔÓ¦µÄÎļþ£¬ÒÔfrmºó׺½áβ¡£
´æ´¢ÒýÇæÎļþ£º´æ´¢×Ô¼ºµÄÎļþÀ´±£´æ¸÷ÖÖÊý¾Ý£¬ÕæÕý´æ´¢ÁËÊý¾ÝºÍË÷ÒýµÈÊý¾Ý¡£ÏÂÃæÖ÷Òª½éÉÜInnoDBµÄ´æ´¢ÒýÇæÏµıí¿Õ¼äÎļþºÍÖØ×öÈÕÖ¾Îļþ¡£
±í¿Õ¼äÎļþ£ºInnoDBĬÈϵıí¿Õ¼äÎļþΪibdata1,¿Éͨ¹ýshow variables like
'innodb_file_per_table'²é¿´Ã¿¸ö±íÊÇ·ñ²úÉúµ¥¶ÀµÄ.idb±í¿Õ¼äÎļþ¡£µ«ÊÇ£¬µ¥¶ÀµÄ±í¿Õ¼äÎļþ½ö´æ´¢¸Ã±íµÄÊý¾Ý¡¢Ë÷ÒýºÍ²åÈ뻺³åµÈÐÅÏ¢£¬ÆäÓàÐÅÏ¢»¹ÊÇ´æ·ÅÔÚĬÈϵıí¿Õ¼äÖС£
ÖØ×öÈÕÖ¾Îļþ£ºÊµÀýºÍ½éÖÊʧ°Ü£¬ÖØ×öÈÕÖ¾Îļþ¾ÍÄÜÅÉÉÏÓ󡣬ÈçÊý¾Ý¿âµôµç£¬InnoDB´æ´¢ÒýÇæ»áʹÓÃÖØ×öÈÕÖ¾»Ö¸´µ½µôµçǰµÄʱ¿Ì£¬ÒÔ´ËÀ´±£Ö¤Êý¾ÝµÄÍêÕûÐÔ¡£²ÎÊýinnodb_log_file_sizeÖ¸¶¨ÁËÖØ×öÈÕÖ¾ÎļþµÄ´óС£»innodb_log_file_in_groupÖ¸¶¨ÁËÈÕÖ¾Îļþ×éÖÐÖØ×öÈÕÖ¾ÎļþµÄÊýÁ¿£¬Ä¬ÈÏΪ2£¬innodb_mirrored_log_groupsÖ¸¶¨ÁËÈÕÖ¾¾µÏñÎļþ×éµÄÊýÁ¿£¬Ä¬ÈÏΪ1£¬´ú±íÖ»ÓÐÒ»¸öÈÕÖ¾Îļþ×飬ûÓоµÏñ£»innodb_log_group_home_dirÖ¸¶¨ÁËÈÕÖ¾Îļþ×éËùÔÚ·¾¶£¬Ä¬ÈÏÔÚÊý¾Ý¿â·¾¶Ï¡£
¶þ½øÖÆÈÕÖ¾ºÍÖØ×öÈÕÖ¾µÄÇø±ð£ºÊ×ÏÈ£¬¶þ½øÖÆÈÕÖ¾»á¼Ç¼ËùÓÐÓëMysqlÓйصÄÈÕÖ¾¼Ç¼£¬°üÀ¨InnoDB¡¢MyISAM¡¢HeapµÈÆäËû´æ´¢ÒýÇæµÄÈÕÖ¾¡£¶øInnoDB´æ´¢ÒýÇæÖØ×öÈÕÖ¾Ö»´æ´¢ÓÐ¹ØÆä±¾ÉíµÄÊÂÎñÈÕÖ¾£»Æä´ÎÄÚÈݲ»Í¬£¬²»¹Ü½«¶þ½øÖÆÈÕÖ¾Îļþ¼Ç¼µÄ¸ñʽÉèΪSTATEMENT»¹ÊÇROW£¬ÓÖ»òÕßÊÇMIXED£¬Æä¼Ç¼µÄ¶¼ÊǹØÓÚÒ»¸öÊÂÎñµÄ¾ßÌå²Ù×÷ÄÚÈÝ¡£¶øInnoDB´æ´¢ÒýÇæµÄÖØ×öÈÕÖ¾Îļþ¼Ç¼µÄ¹ØÓÚÿ¸öÒ³µÄ¸ü¸ÄµÄÎïÀíÇé¿ö
¡£´ËÍ⣬дÈëʱ¼ä²»Í¬£¬¶þ½øÖÆÈÕÖ¾ÎļþÊÇÔÚÊÂÎñÌύǰ½øÐмǼµÄ£¬¶øÔÚÊÂÎñ½øÐеĹý³ÌÖУ¬²»¶ÏÓÐÖØ×öÈÕÖ¾ÌõÄ¿±»
дÈëÖØ×öÈÕÖ¾ÎļþÖС£
mysql innodb±í
±í¿Õ¼ä£º±í¿Õ¼ä¿É¿´×öÊÇInnoDB´æ´¢ÒýÇæÂß¼½á¹¹µÄ×î¸ß²ã¡£
¶Î£º±í¿Õ¼äÓɸ÷¸ö¶Î×é³É£¬³£¼ûµÄ¶ÎÓÐÊý¾Ý¶Î¡¢Ë÷Òý¶Î¡¢»Ø¹ö¶ÎµÈ¡£
Çø£ºÓÉ64¸öÁ¬ÐøµÄÒ³×é³É£¬Ã¿¸öÒ³´óСΪ16kb£¬¼´Ã¿¸öÇø´óСΪ1MB¡£
Ò³£ºÃ¿Ò³16kb£¬ÇÒ²»Äܸü¸Ä¡£³£¼ûµÄÒ³ÀàÐÍÓУºÊý¾ÝÒ³¡¢UndoÒ³¡¢ÏµÍ³Ò³¡¢ÊÂÎñÊý¾ÝÒ³¡¢²åÈ뻺³åλͼҳ¡¢²åÈ뻺³å¿ÕÏÐÁбíÒ³¡¢Î´Ñ¹ËõµÄ¶þ½øÖÆ´ó¶ÔÏóÒ³¡¢Ñ¹ËõµÄ¶þ½øÖÆ´ó¶ÔÏóÒ³¡£
ÐУºInnoDB´æ´¢ÒýÇæÊÇÃæÏòÐеÄ(row-oriented)£¬Ã¿Ò³×î¶àÔÊÐí´æ·Å7992ÐÐÊý¾Ý¡£
ÐмǼ¸ñʽ£º³£¼ûÁ½ÖÖÐмǼ¸ñʽCompactºÍRedundant£¬mysql5.1°æ±¾ºó£¬Ö÷ÒªÊÇCompactÐмǼ¸ñʽ¡£¶ÔÓÚCompact£¬²»¹ÜÊÇcharÐÍ»¹ÊÇvarcharÐÍ£¬nullÐͶ¼ÊDz»Õ¼Óô洢¿Õ¼äµÄ£»¶ÔÓÚRedudant,varcharµÄnull²»Õ¼Óÿռ䣬charµÄnullÐÍÊÇÕ¼Óô洢¿Õ¼äµÄ¡£
varcharÀàÐ͵ij¤¶ÈÏÞÖÆÊÇ65535£¬Æäʵ´ï²»µ½£¬»áÓбðµÄ¿ªÏú£¬Ò»°ãÊÇ65530×óÓÒ£¬Õ⻹¸úѡȡµÄ×Ö·û¼¯Óйء£´ËÍâÕâ¸ö³¤¶ÈÏÞÖÆÊÇÒ»ÕûÐеģ¬ÀýÈ磺create
table test(a varchar(22000), b varchar(22000), cvarchar(22000))
charset=latin1 engine=innodbÒ²»á±¨´í¡£
¶ÔÓÚblobÀàÐ͵ÄÊý¾Ý£¬ÔÚÊý¾ÝÒ³ÃæÖÐÖ»±£´æÁËvarchar(65535)µÄǰ768¸ö×Ö½Úǰ׺Êý¾Ý£¬Ö®ºó¸úµÄÊÇÆ«ÒÆÁ¿£¬Ö¸ÏòÐÐÒç³öÒ³£¬Ò²¾ÍÊÇUncompressed
BLOB Page¡£ÐµÄInnoDB PluginÒýÈëÁËеÄÎļþ¸ñʽ³ÆÎªBarracuda£¬ÆäÓÐÁ½ÖÖеÄÐмǼ¸ñʽCompressedºÍDynamic£¬Á½Õß¶ÔÓÚ´æÈëBlog×ֶβÉÓÃÁËÍêÈ«Òç³öµÄ·½Ê½£¬ÔÚÊý¾Ý¿âÒ³Öдæ·Å20¸ö×Ö½ÚµÄÖ¸Õ룬ʵ¼ÊµÄÊý¾Ý¶¼´æÈëÔÚBLOB
PageÖС£


Êý¾ÝÒ³½á¹¹£ºÊý¾ÝÒ³½á¹¹ÓÉÒÔÏÂ7¸ö²¿·Ö×é³É£º
File Header(ÎļþÍ·):¼Ç¼ҳµÄһЩͷÐÅÏ¢£¬ÈçÒ³Æ«ÒÆÁ¿¡¢ÉÏÒ»Ò³¡¢ÏÂÒ»Ò³¡¢Ò³ÀàÐ͵ȣ¬¹Ì¶¨³¤¶ÈΪ38¸ö×Ö½Ú¡£
Page Header(ҳͷ)£º¼Ç¼ҳµÄ״̬ÐÅÏ¢£¬¶ÑÖмǼÊý¡¢Ö¸Ïò¿ÕÏÐÁбíµÄÖ¸Õë¡¢ÒÑɾ³ý¼Ç¼µÄ×Ö½ÚÊý¡¢×îºó²åÈëµÄλÖõȣ¬¹Ì¶¨³¤¶È¹²56¸ö×Ö½Ú¡£
Infimun+Supremum Records:ÔÚInnoDB´æ´¢ÒýÇæÖУ¬Ã¿¸öÊý¾ÝÒ³ÖÐÓÐÁ½¸öÐéÄâµÄÐмǼ£¬ÓÃÀ´ÏÞ¶¨¼Ç¼µÄ±ß½ç¡£
Infimun¼Ç¼ÊDZȸÃÒ³ÖÐÈκÎÖ÷¼ü¶¼ÒªÐ¡µÄÖµ£¬SupermumÖ¸±ÈÈκοÉÄÜ´óµÄÖµ»¹Òª´óµÄÖµ¡£ÕâÁ½¸öÖµÔÚÒ³´´½¨Ê±±»½¨Á¢£¬²¢ÇÒÔÚÈκÎÇé¿öϲ»»á±»É¾³ý¡£ÔÚCompactÐиñʽºÍRedundantÐиñʽÏ£¬Á½ÕßÕ¼ÓõÄ×Ö½ÚÊý¸÷²»Ïàͬ¡£
User Records(Óû§¼Ç¼£¬¼´ÐмǼ):ʵÏּǼµÄÄÚÈÝ¡£ÔÙ´ÎÇ¿µ÷£¬InnoDB´æ´¢ÒýÇæ±í×ÜÊÇB+´åË÷Òý×éÖ¯µÄ¡£
Free Space(¿ÕÏпռä)£ºÖ¸¿ÕÏпռ䣬ͬÑùÒ²ÊǸöÁ´±íÊý¾Ý½á¹¹¡£µ±Ò»Ìõ¼Ç¼±»É¾³ýºó£¬¸Ã¿Õ¼ä»á±»¼ÓÈë¿ÕÏÐÁ´
±íÖС£
Page Directory(ҳĿ¼)£ºÒ³Ä¿Â¼´æ·ÅÁ˼ǼµÄÏà¶ÔλÖ㬲¢²»ÊÇÆ«ÒÆÁ¿£¬ÓÐЩʱºòÕâЩ¼Ç¼³ÆÎªSlots(²Û)£¬InnoDB²¢²»ÊÇÿ¸ö¼Ç¼һ¸ö²Û£¬²ÛÊÇÒ»¸öÏ¡ÊèĿ¼£¬¼´Ò»¸ö²ÛÖпÉÄÜÊôÓÚ¶à¸ö¼Ç¼£¬×îÉÙÊôÓÚ4Ìõ¼Ç¼£¬×î¶àÊôÓÚ8Ìõ¼Ç¼¡£ÐèÒªÀμǵÄÊÇ£¬B+Ê÷Ë÷Òý±¾Éí²¢²»ÄÜÕÒµ½¾ßÌåµÄÒ»Ìõ¼Ç¼£¬B+Ê÷Ë÷ÒýÄÜÕÒµ½Ö»ÊǸüǼËùÔÚµÄÒ³¡£Êý¾Ý¿â°ÑÒ³ÔØÈëÄڴ棬Ȼºóͨ¹ýPage
DirectoryÔÙ½øÐжþ²æ²éÕÒ¡£Ö»²»¹ý¶þ²æ²éÕÒµÄʱ¼ä¸´ÔӶȵͣ¬Í¬Ê±ÄÚ´æÖеIJéÕҺܿ죬Òò´Ëͨ¹ýºöÂÔÁËÕⲿ·Ö²éÕÒËùÓõÄʱ¼ä¡£
File Trailer(Îļþ½áβÐÅÏ¢)£ºÎªÁ˱£Ö¤Ò³ÍêÕûµØÐ´Èë´ÅÅÌ(Èçд¹ý³ÌµÄ´ÅÅÌË𻵡¢»úÆ÷å´»úµÈ)£¬¹Ì¶¨³¤8¸ö×Ö½Ú¡£
ÊÓͼ£ºMysqlÖеÄÊÓͼ×ÜÊÇÐéÄâµÄ±í£¬±¾Éí²»Ö§³ÖÎﻯÊÓͼ¡£µ«ÊÇͨ¹ýһЩÆäËû¼¼ÇÉ(Èç´¥·¢Æ÷)£¬Í¬ÑùÒ²¿ÉÒÔʵÏÖһЩ¼òµ¥µÄÎﻯÊÓͼµÄ¹¦ÄÜ¡£
·ÖÇø£ºMysqlÊý¾Ý¿âÖ§³ÖRANGE¡¢LIST¡¢HASH¡¢KEY¡¢COLUMNS·ÖÇø£¬²¢ÇÒ¿ÉÒÔʹÓÃHASH»òKEYÀ´½øÐÐ×Ó·ÖÇø¡£
mysql innodb³£¼ûË÷ÒýÓëËã·¨£º
B+Ê÷Ë÷Òý£ºB+Ê÷µÄÊý¾Ý½á¹¹Ïà¶Ô½Ï¸´ÔÓ£¬B´ú±íµÄÊÇbalance×îÔçÊÇ´ÓÆ½ºâ¶þ²æÊ÷ÑÝ»¯¶øÀ´£¬µ«B+Ê÷²¢²»ÊÇÒ»¸ö¶þ²æÊ÷£¬
ÓÉÓÚB+Ê÷Ë÷ÒýµÄ¸ßÉȳöÐÔ£¬Òò´ËÔÚÊý¾Ý¿âÖУ¬B+Ê÷µÄ¸ß¶ÈÒ»°ã¶¼ÔÚ2~3²ã£¬Ò²¾Í¶ÔÓÚ²éÕÒijһ¼üÖµµÄÐмǼ£¬×î¶àÖ»Òª2µ½3´ÎIO,ÏÖÔÚÒ»°ãµÄ´ÅÅÌÿÃëÖÁÉÙ¿ÉÒÔ×ö100´ÎIO£¬2~3´ÎµÄIOÒâζ×Ųéѯʱ¼äÖ»Ðè0.02~0.03Ãë¡£
Êý¾Ý¿âÖеÄB+Ë÷Òý¿ÉÒÔ·ÖΪ¾Û¼¯Ë÷Òý(clustered index)ºÍ¸¨Öú¾Û¼¯Ë÷Òý(secondary
index),µ«ÆäÄÚ²¿¶¼ÊÇB+Ê÷µÄ£¬¼´¸ß¶ÈƽºâµÄ£¬Ò¶×Ó½Úµã´æ·ÅÊý¾Ý¡£
¾Û¼¯Ë÷Òý£ºÓÉÓÚ¾Û¼¯Ë÷ÒýÊǰ´ÕÕÖ÷¼ü×éÖ¯µÄ£¬ËùÒÔÿһÕűíÖ»ÄÜÓÐÒ»¸ö¾Û¼¯Ë÷Òý£¬Ã¿¸öÊý¾ÝÒ³¶¼Í¨¹ýË«ÏòÁ´±í½øÐÐÁ¬½Ó£¬Ò¶×Ó½Úµã´æ·ÅÒ»ÕûÐеÄÐÅÏ¢£¬ËùÒÔ²éѯÓÅ»¯Æ÷¸üÇãÏò×ß¾Û¼¯Ë÷Òý¡£´ËÍ⣬¶ÔÓÚ¾Û¼¯Ë÷ÒýµÄ´æ´¢ÊÇÂß¼ÉÏÁ¬ÐøµÄ¡£ËùÒÔ£¬¾Û¼¯Ë÷Òý¶ÔÓÚÖ÷¼üµÄÅÅÐò²éÕҺͷ¶Î§²éÕÒËٶȷdz£¿ì¡£
¸¨ÖúË÷Òý£ºÒ²½Ð·Ç¾Û¼¯Ë÷Òý£¬Ò¶×ӽڵ㲻´æÈ«²¿Êý¾Ý£¬Ö÷Òª´æ¼üÖµ¼°Ò»¸öboomark(Æäʵ¾ÍÊǾۼ¯Ë÷ÒýµÄ¼ü)¸æËßInnoDBÄÄÀï¿ÉÒÔÕÒµ½ÓëË÷ÒýÏà¶ÔÓ¦µÄÐÐÊý¾Ý£¬ÈçÒ»¸ö¸ß¶ÈΪ3µÄ¸¨ÖúË÷ÒýºÍÒ»¸ö¸ß¶ÈΪ3µÄ¾Û¼¯Ë÷Òý£¬Èô¸ù¾Ý¸¨ÖúË÷ÒýÀ´²éѯÐмǼ£¬Ò»¹²ÐèÒª6´ÎIO¡£ÁíÍ⸨ÖúË÷Òý¿ÉÒÔÓжà¸ö¡£
Ë÷ÒýµÄʹÓÃÔÔò£º¸ßÑ¡Ôñ¡¢È¡³ö±íÖеÄÉÙ²¿·ÖÊý¾Ý(Ò²³ÆÎªÎ¨Ò»Ë÷Òý)¡£Ò»°ãÈ¡³öµÄÊý¾ÝÁ¿³¬¹ý±íÖÐÊý¾ÝµÄ20%£¬ÓÅ»¯Æ÷²»»áʹÓÃË÷Òý£¬¶ø½øÐÐÈ«±íɨÃè¡£Èç¶ÔÓÚÐÔ±ðµÈ×Ö¶ÎÊÇûÓÐÒâÒåµÄ¡£
ÁªºÏË÷Òý£º Ò²³Æ¸´ºÏË÷Òý£¬ÊÇÔÚ¶àÁУ¨>=2£©ÉϽ¨Á¢µÄË÷Òý¡£InnodbÖеĸ´ºÏË÷ÒýÒ²ÊÇb+ tree½á¹¹¡£Ë÷ÒýµÄÊý¾Ý°üº¬¶àÁÐ(col1,
col2, col3¡)£¬ÔÚË÷ÒýÖÐÒÀ´Î°´ÕÕcol1, col2, col3ÅÅÐò¡£Èç(1, 2), (1,
3),(2,0)¡Ê¹Óø´ºÏË÷ÒýÒª³ä·ÖÀûÓÃ×î×óǰ׺ÔÔò£¬¹ËÃû˼Ò壬¾ÍÊÇ×î×óÓÅÏÈ¡£Èç´´½¨Ë÷Òýind_col1_col2(col1,
col2)£¬ÄÇôÔÚ²éѯwhere col1 = xxx and col2 = xx»òÕßwhere col1
= xxx¶¼¿ÉÒÔ×ßind_col1_col2Ë÷Òý£¬µ«where col2=****ÊÇ×ß²»µ½Ë÷ÒýµÄ¡£ÔÚ´´½¨¶àÁÐË÷Òýʱ£¬Òª¸ù¾ÝÒµÎñÐèÇó£¬where×Ó¾äÖÐʹÓÃ×îÆµ·±ÇÒ¹ýÂËЧ¹ûºÃµÄµÄÒ»ÁзÅÔÚ×î×ó±ß¡£
¹þÏ£Ë÷Òý£º¹þÏ£Ëã·¨Ò²ÊDZȽϳ£¼ûµÄËã·¨£¬mysql innoDBÖÐʹÓÃÁ˱Ƚϳ£¼ûµÄÁ´µØÖ··¨½øÐÐÈ¥ÖØ¡£´ËÍâÉÏÃæÒѾÌá¼°£¬innoDBÖеÄhashÊÇ×ÔÊÊÓ¦µÄ£¬Ê²Ã´Ê±ºòʹÓÃhashÊÇϵͳ¾ö¶¨µÄ£¬ÎÞ·¨½øÐÐÈ˹¤ÉèÖá£
¶þ·Ö²éÕÒ·¨£ºÕâ¸öËã·¨±È½Ï³£¼û£¬ÕâÀï¾Í²»¶àÌá¼°ÁË¡£ÔÚInnoDBÖУ¬Ã¿Ò³Page DirectoryÖеIJÛÊǰ´ÕÕÖ÷¼üµÄ˳Ðò´æ·ÅµÄ£¬¶ÔÓÚijһÌõ¾ßÌå¼Ç¼µÄ²éѯÊÇͨ¹ý¶ÔPage
Directory½øÐжþ·Ö²éÕҵõ½µÄ¡£
mysql innodbÖеÄËø
InnoDB´æ´¢ÒýÇæËøµÄʵÏÖºÍOracle·Ç³£ÀàËÆ£¬ÌṩһÖÂÐԵķÇËø¶¨¶Á¡¢Ðм¶ËøÖ§³Ö¡¢Ðм¶ËøÃ»ÓÐÏà¹ØµÄ¿ªÏú£¬¿ÉÒÔͬʱµÃµ½²¢·¢ÐÔºÍÒ»ÖÂÐÔ¡£
InnoDB´æ´¢ÒýÇæÊµÏÖÁËÈçÏÂÁ½ÖÖ±ê×¼µÄÐм¶Ëø£º
¹²ÏíËø(S Lock)£ºÔÊÐíÊÂÎñ¶ÁÒ»ÐÐÊý¾Ý£»
ÅÅËûËø(X Lock)£ºÔÊÐíÊÂÎñɾ³ý»òÕ߸üÐÂÒ»ÐÐÊý¾Ý¡£
µ±Ò»¸öÊÂÎñÒѾ»ñµÃÁËÐÐrµÄ¹²ÏíËø£¬ÄÇôÁíÍâµÄÊÂÎñ¿ÉÒÔÁ¢¼´»ñµÃÐÐrµÄ¹²ÏíËø£¬ÒòΪ¶ÁȡûÓиıäÐÐrµÄÊý¾Ý£¬ÎÒÃdzÆÕâÖÖÇé¿öÎªËø¼æÈÝ¡£µ«Èç¹ûÓÐÊÂÎñÏë»ñµÃÐÐrµÄÅÅËûËø£¬ÔòËü±ØÐëµÈ´ýÊÂÎñÊÍ·ÅÐÐrÉϵĹ²ÏíËø¡ª¡ª¡ª¡ªÕâÖÖÇé¿ö³ÆÎªËø²»¼æÈÝ¡£

ÔÚInnoDB Plugin֮ǰ£¬Ö»ÄÜͨ¹ýSHOW FULL PROCESSLIST£¬SHOW ENGINE
INOODB STATUSµÈÃüÁîÀ´²é¿´µ±Ç°µÄÊý¾Ý¿âÇëÇó£¬È»ºóÔÙÅжϵ±Ç°ÊÂÎñÖеÄËøµÄÇé¿ö¡£Ð°汾µÄInnoDB
PluginÖУ¬ÔÚINFORMATION_SCHEMA¼Ü¹¹ÏÂÌí¼ÓÁËINNODB_TRX¡¢INNODB_LOCKS¡¢InnoDB_LOCK_WAITS¡£Í¨¹ýÕâÈýÕÅ±í£¬¿ÉÒÔ¸ü¼òµ¥µØ¼à¿Øµ±Ç°µÄÊÂÎñ²¢·ÖÎö¿ÉÄÜ´æÔÚµÄËøµÄÎÊÌâ¡£
INNODB_TRXÓÉ8¸ö×Ö¶Î×é³É£º
trx_id:InnoDB´æ´¢ÒýÇæÄÚ²¿Î¨Ò»µÄÊÂÎñID
trx_state:µ±Ç°ÊÂÎñµÄ״̬¡£
trx_started:ÊÂÎñµÄ¿ªÊ¼Ê±¼ä¡£
trx_requested_lock_id:µÈ´ýÊÂÎñµÄËøID¡£Èçtrx_stateµÄ״̬ΪLOCK
WAIT,ÄÇô¸ÃÖµ´ú±íµ±Ç°µÄµÈ´ý֮ǰÊÂÎñÕ¼ÓÃËø×ÊÔ´µÄID.
Èôtrx_state²»ÊÇLOCK WAIT,Ôò¸ÃֵΪNULL¡£
trx_wait_started:ÊÂÎñµÈ´ý¿ªÊ¼µÄʱ¼ä¡£
trx_weight:ÊÂÎñµÄÈ¨ÖØ£¬·´Ó³ÁËÒ»¸öÊÂÎñÐ޸ĺÍËø×¡µÄÐÐÊý¡£ÔÚInnoDB´æ´¢ÒýÇæÖУ¬µ±·¢ÉúËÀËøÐèÒª»Ø¹öʱ£¬InnoDB´æ´¢»áÑ¡
Ôñ¸ÃÖµ×îСµÄ½øÐлعö¡£
trx_mysql_thread_id:MysqlÖеÄÏß³ÌID,SHOW PROCESSLISTÏÔʾµÄ½á¹û¡£
trx_query:ÊÂÎñÔËÐеÄsqlÓï¾ä¡£
ͨ¹ýselect * from infomation_schema.INNODB_TRX;¿É²é¿´
INNODB_LOCKS±í£¬¸Ã±íÓÉÈçÏÂ×Ö¶Î×é³É£º
lock_id:ËøµÄID¡£
lock_trx_id:ÊÂÎñID¡£
lock_mode:ËøµÄģʽ¡£
lock_type:ËøµÄÀàÐÍ£¬±íËø»¹ÊÇÐÐËø¡£
lock_table:Òª¼ÓËøµÄ±í¡£
lock_index:ËøµÄË÷Òý¡£
lock_space:InnoDB´æ´¢ÒýÇæ±í¿Õ¼äµÄIDºÅ¡£
lock_page:±»Ëø×¡µÄÒ³µÄÊýÁ¿¡£ÈôÊDZíËø£¬Ôò¸ÃֵΪNULL¡£
lock_rec:±»Ëø×¡µÄÐеÄÊýÁ¿¡£ÈôÊDZíËø£¬Ôò¸ÃֵΪNULL¡£
lock_data:±»Ëø×¡µÄÐеÄÖ÷¼üÖµ¡£µ±ÊDZíËøÊ±£¬¸ÃֵΪNULL¡£
ͨ¹ýselect * from information_schema.INNODB_LOCK;¿É²é¿´
INNODB_LOCK_WAITÓÉ4¸ö×Ö¶Î×é³É£º
requesting_trx_id:ÉêÇëËø×ÊÔ´µÄÊÂÎñID¡£
requesting_lock_id:ÉêÇëµÄËøµÄID¡£
blocking_trx_id:×èÈûµÄËøµÄID¡£
ͨ¹ýselect * from information_schema.INNODB_LOCK_WAITS;¿É²é¿´¡£
Ò»ÖÂÐԵķÇËø¶¨¶Á£ºInnoDB´æ´¢ÒýÇæÍ¨¹ýÐÐ¶à°æ±¾¿ØÖƵķ½Ê½À´¶ÁÈ¡µ±Ç°Ö´ÐÐʱ¼äÊý¾Ý¿âÖÐÐеÄÊý¾Ý¡£Èç¹û¶ÁÈ¡µÄÐÐÕýÔÚÖ´ÐÐDelete¡¢update²Ù×÷£¬Õâʱ¶ÁÈ¡²Ù×÷²»»áÒò´Ë¶ø»áµÈ´ýÐÐÉÏËøµÄÊÍ·Å£¬Ïà·´£¬InnoDB´æ´¢ÒýÇæ»áÈ¥¶ÁÈ¡ÐеÄÒ»¸ö¿ìÕÕÊý¾Ý¡£¿ìÕÕÊý¾ÝÊÇÖ¸¸ÃÐÐ֮ǰ°æ±¾µÄÊý¾Ý£¬¸ÃʵÏÖÊÇͨ¹ýUndo¶ÎÀ´ÊµÏÖ¡£¶øUndoÓÃÀ´ÊÂÎñÖлعöÊý¾Ý£¬Òò´Ë¿ìÕÕ±¾ÉíÊÇûÓжîÍ⿪ÏúµÄ¡£´ËÍ⣬¿ìÕÕÊý¾ÝÊDz»ÐèÒªÉÏËøµÄ£¬ÒòΪûÓбØÒª¶ÔÀúÊ·µÄÊý¾Ý½øÐÐÐ޸ġ£Ò»¸öÐпÉÄÜÓв»Ö¹Ò»¸ö¿ìÕÕÊý¾Ý£¬ËùÒÔ³ÆÕâÖÖ¼¼ÊõΪÐÐ¶à°æ±¾¼¼Êõ¡£ÓÉ´Ë´øÀ´²¢·¢¿ØÖÆ£¬³ÆÖ®Îª¶à°æ±¾²¢·¢¿ØÖÆ(Multi
VersionConcurrency Control, MVCC)¡£
ÊÂÎñµÄ¸ôÀë¼¶±ð£ºRead uncommitted¡¢Read committed¡¢Repeatable
read¡¢serializable¡£ÔÚRead CommittedºÍRepeatable ReadÏ£¬InnoDB´æ´¢ÒýÇæÊ¹Ó÷ÇËø¶¨Ò»ÖÂÐÔ¶Á¡£È»¶ø£¬¶ÔÓÚ¿ìÕյ͍ÒåÈ´²»Í¬¡£ÔÚRead
CommittedÊÂÎñ¸ôÀë¼¶±ðÏ£¬¶ÔÓÚ¿ìÕÕÊý¾Ý£¬·ÇÒ»ÖÂÐÔ¶Á×ÜÊǶÁÈ¡±»Ëø¶¨ÐеÄ×îÐÂÒ»·Ý¿ìÕÕÊý¾Ý¡£ÔÚRepeatableÊÂÎñ¸ôÀë¼¶±ðÏ£¬¶ÔÓÚ¿ìÕÕÊý¾Ý£¬·ÇÒ»ÖÂÐÔ¶Á×ÜÊǶÁÈ¡ÊÂÎñ¿ªÊ¼Ê±µÄÐÐÊý¾Ý°æ±¾¡£
ËøµÄËã·¨£º
Record Lock:µ¥ÐмǼÉϵÄËø
Gap Lock:¼äÏ¶Ëø£¬Ëø¶¨Ò»¸ö·¶Î§£¬µ«²»°üº¬¼Ç¼±¾Éí
Next-Key Lock:Gap Lock + Record Lock£¬Ëø¶¨Ò»¸ö·¶Î§£¬²¢ÇÒËø¶¨¼Ç¼±¾Éí¡£
ËøµÄÎÊÌ⣺
¶ªÊ§¸üУº¾µäµÄÊý¾Ý¿âÎÊÌ⣬µ±Á½¸ö»ò¶à¸öÊÂÎñÑ¡ÔñͬһÐУ¬È»ºó»ùÓÚ×î³õÑ¡¶¨µÄÖµ¸üиÃÐÐʱ£¬»á·¢Éú¶ªÊ§¸üÐÂÎÊÌ⡣ÿ¸öÊÂÎñ¶¼²»ÖªµÀÆäËüÊÂÎñµÄ´æÔÚ¡£×îºóµÄ¸üн«ÖØÐ´ÓÉÆäËüÊÂÎñËù×öµÄ¸üУ¬Õ⽫µ¼ÖÂÊý¾Ý¶ªÊ§¡£
¡¡¡¡
Àý:
ÊÂÎñAºÍÊÂÎñBͬʱÐÞ¸ÄijÐеÄÖµ£¬
1.ÊÂÎñA½«ÊýÖµ¸ÄΪ1²¢Ìá½»
2.ÊÂÎñB½«ÊýÖµ¸ÄΪ2²¢Ìá½»¡£
ÕâʱÊý¾ÝµÄֵΪ2£¬ÊÂÎñAËù×öµÄ¸üн«»á¶ªÊ§¡£
½â¾ö°ì·¨£ºÊÂÎñ²¢Ðб䴮ÐвÙ×÷£¬¶Ô¸üвÙ×÷¼ÓÅÅËûËø¡£
Ôà¶Á£ºÒ»¸öÊÂÎñ¶Áµ½ÁíÒ»¸öÊÂÎñδÌá½»µÄ¸üÐÂÊý¾Ý£¬¼´¶Áµ½ÔàÊý¾Ý¡£
Àý:
1.MaryµÄÔ¹¤×ÊΪ1000, ²ÆÎñÈËÔ±½«MaryµÄ¹¤×ʸÄΪÁË8000(µ«Î´Ìá½»ÊÂÎñ)
2.Mary¶ÁÈ¡×Ô¼ºµÄ¹¤×Ê ,·¢ÏÖ×Ô¼ºµÄ¹¤×ʱäΪÁË8000£¬»¶ÌìϲµØ£¡
3.¶ø²ÆÎñ·¢ÏÖ²Ù×÷ÓÐÎ󣬻عöÁËÊÂÎñ,MaryµÄ¹¤×ÊÓÖ±äΪÁË1000, ÏñÕâÑù,Mary¼ÇÈ¡µÄ¹¤×ÊÊý8000ÊÇÒ»¸öÔàÊý¾Ý¡£
½â¾ö°ì·¨£ºÔà¶ÁÖ»ÓÐÔÚÊÂÎñ¸ôÀë¼¶±ðÊÇRead UncommittedµÄÇé¿öϲŻá³öÏÖ£¬innoDBĬÈϸôÀë¼¶±ðÊÇRepeatable
Read£¬ËùÒÔÉú²ú»·¾³Ï²»»á³öÏÖÔà¶Á¡£
²»¿ÉÖØ¸´¶Á£ºÔÚͬһ¸öÊÂÎñÖÐ,¶à´Î¶ÁȡͬһÊý¾Ý,·µ»ØµÄ½á¹ûÓÐËù²»Í¬¡£»»¾ä»°Ëµ¾ÍÊÇ,ºóÐø¶ÁÈ¡¿ÉÒÔ¶Áµ½ÁíÒ»¸öÊÂÎñÒÑÌá½»µÄ¸üÐÂÊý¾Ý¡£Ïà·´"¿ÉÖØ¸´¶Á"ÔÚͬһÊÂÎñ¶à´Î¶ÁÈ¡Êý¾Ýʱ,Äܹ»±£Ö¤Ëù¶ÁÊý¾ÝÒ»Ñù,Ò²¾ÍÊǺóÐø¶ÁÈ¡²»ÄܶÁµ½ÁíÒ»ÊÂÎñÒÑÌá½»µÄ¸üÐÂÊý¾Ý¡£Ôà¶ÁºÍ²»¿ÉÖØ¸´¶ÁµÄÖ÷񻂿±ðÔÚÓÚ£¬Ôà¶ÁÊǶÁµ½Î´Ìá½»µÄÊý¾Ý£¬²»¿ÉÖØ¸´¶ÁÊǶÁµ½ÒÑÌá½»µÄÊý¾Ý¡£
Àý:
1.ÔÚÊÂÎñ1ÖУ¬Mary ¶ÁÈ¡ÁË×Ô¼ºµÄ¹¤×ÊΪ1000,²Ù×÷²¢Ã»ÓÐÍê³É
2.ÔÚÊÂÎñ2ÖУ¬Õâʱ²ÆÎñÈËÔ±ÐÞ¸ÄÁËMaryµÄ¹¤×ÊΪ2000,²¢Ìá½»ÁËÊÂÎñ.
3.ÔÚÊÂÎñ1ÖУ¬Mary ÔٴζÁÈ¡×Ô¼ºµÄ¹¤×Êʱ£¬¹¤×ʱäΪÁË2000
½â¾ö°ì·¨£º¶Áµ½ÒÑÌá½»µÄÊý¾Ý£¬Ò»°ãÊý¾Ý¿âÊǿɽÓÊܵģ¬Òò´ËÊÂÎñ¸ôÀë¼¶±ðÒ»°ãÉèΪRead Committed¡£Mysql
InnoDBͨ¹ýNext-Key LockËã·¨±ÜÃâ²»¿ÉÖØ¸´¶Á£¬Ä¬ÈϸôÀë¼¶±ðΪRepeatable Read¡£
mysql innodbÖеÄÊÂÎñ
ÊÂÎñµÄËĸöÌØÐÔ£ºÔ×ÓÐÔ¡¢Ò»ÖÂÐÔ¡¢¸ôÀëÐÔ¡¢³Ö¾ÃÐÔ
¸ôÀëÐÔͨ¹ýËøÊµÏÖ£¬Ô×ÓÐÔ¡¢Ò»ÖÂÐÔ¡¢³Ö¾ÃÐÔͨ¹ýÊý¾Ý¿âµÄredoºÍundoÀ´Íê³É¡£
ÖØ×öÈÕÖ¾¼Ç¼ÁËÊÂÎñµÄÐÐΪ£¬Í¨¹ýredoʵÏÖ£¬±£Ö¤ÁËÊÂÎñµÄÍêÕûÐÔ£¬µ«ÊÂÎñÓÐʱ»¹ÐèÒª³·Ïú£¬Õâʱ¾ÍÐèÒª²úÉúundo¡£undoºÍredoÕýºÃÏà·´£¬¶ÔÓÚÊý¾Ý¿â½øÐÐÐÞ¸Äʱ£¬Êý¾Ý¿â²»µ«»á²úÉúredo£¬¶øÇÒ»¹»á²úÉúÒ»¶¨µÄundo£¬¼´Ê¹Ö´ÐеÄÊÂÎñ»òÓï¾äÓÉÓÚijÖÖÔÒòʧ°ÜÁË£¬»òÕßÈç¹ûÓÃÒ»ÌõrollbackÓï¾äÇëÇ󻨹ö£¬¾Í¿ÉÒÔÓÃÕâЩundoÐÅÏ¢½«Êý¾Ý»Ø¹öµ½ÐÞ¸Ä֮ǰµÄÑù×Ó¡£Óëredo²»Í¬µÄÊÇ,redo´æ·ÅÔÚÖØ×öÈÕÖ¾ÎļþÖУ¬undo´æ·ÅÔÚÊý¾Ý¿âÄÚ²¿µÄÒ»¸öÌØÊâ¶Î(segment)ÖУ¬Õâ³ÆÎªundo¶Î(undo
segment)£¬undo¶ÎλÓÚ¹²Ïí±í¿Õ¼äÄÚ¡£»¹ÓÐÒ»µãÖØÒªµÄÊÇ£¬undo¼Ç¼µÄÊÇÓëÊÂÎñ²Ù×÷Ïà·´µÄÂß¼²Ù×÷£¬Èçinsert
undo ¼Ç¼һ¸ödelete£¬ËùÒÔundoÖ»ÊÇÂß¼µØ½«Êý¾Ý¿â»Ö¸´³ÉÊÂÎñ¿ªÊ¼Ç°µÄÑù×Ó¡£Èç:insert
10ÍòÐеÄÊý¾Ý£¬¿ÉÄܵ¼Ö±í¿Õ¼äÔö´ó£¬»Ø¹öºó£¬±í¿Õ¼ä²»»á¼õС»ØÈ¥¡£ |