Äú¿ÉÒÔ¾èÖú£¬Ö§³ÖÎÒÃǵĹ«ÒæÊÂÒµ¡£

1Ôª 10Ôª 50Ôª





ÈÏÖ¤Â룺  ÑéÖ¤Âë,¿´²»Çå³þ?Çëµã»÷Ë¢ÐÂÑéÖ¤Âë ±ØÌî



  ÇóÖª ÎÄÕ ÎÄ¿â Lib ÊÓÆµ iPerson ¿Î³Ì ÈÏÖ¤ ×Éѯ ¹¤¾ß ½²×ù Model Center   Code  
»áÔ±   
   
 
     
   
 ¶©ÔÄ
  ¾èÖú
InnoDB´æ´¢ÒýÇæ¼Ü¹¹¼ò½é
 
×÷ÕߣºÀ¶É«Âéȸ
  1541  次浏览      27
2020-8-27 
 
±à¼­ÍƼö:

±¾ÎĽéÉÜÁËInnoDBÊÇÊÂÎñ°²È«µÄMySQL´æ´¢ÒýÇæ£¬Ö§³ÖACIDÊÂÎñ¡£InnoDBÌåϵ¼Ü¹¹ºÍInnoDBÖØÒªÌØÐÔ¡£
±¾ÎÄÀ´×ÔÓÚÖªºõ£¬ÓÉ»ðÁú¹ûÈí¼þAnna±à¼­¡¢ÍƼö¡£

Ò»¡¢ÒýÑÔ

ÔÚMySQL×öÍêÓÅ»¯¹¤×÷ºó£¬ÕæÕýÖ´ÐÐSQLÓï¾äµÄ²¿¼þÊÇ´æ´¢ÒýÇæ¡£ÔÚMySQLÖÚ¶àÒýÇæÖ®ÖУ¬InnoDBÊÇĿǰĬÈϵĴ洢ÒýÇæ£¬Ò²ÊÇʹÓÃ×î¹ã·ºµÄÒýÇæ¡£

InnoDBÊÇÊÂÎñ°²È«µÄMySQL´æ´¢ÒýÇæ£¬Ö§³ÖACIDÊÂÎñ¡£ÆäÉè¼ÆÄ¿±êÖ÷ÒªÃæÏòÔÚÏßÊÂÎñ´¦Àí(OLTP)µÄÓ¦Óá£ÆäÌØµãÊÇÐÐËøÉè¼Æ¡¢Ö§³ÖÍâ¼ü£¬²¢Ö§³Ö·ÇËø¶¨¶Á£¬¼´Ä¬È϶Á²Ù×÷²»»á²úÉúËø¡£InnoDB´æ´¢Êý¾ÝÊÇ»ùÓÚ´ÅÅÌ´æ´¢µÄ,ÇÒÆä¼Ç¼Êǰ´ÕÕÒ³µÄ·½Ê½½øÐйÜÀí¡£ÄÇô½«Òý³öÈçÏÂÒÉÎÊ£º

µ±Ç°µÄ´ÅÅÌËٶȺÍCPUÖ®¼äÓµÓÐÒ»Ìõ¾Þ´óµÄºè¹µ£¬InnoDBÈçºÎ½â¾ö£¿¡£

Êý¾Ý¿â¶ÔÊý¾Ý½øÐвÙ×÷£¨²éѯ£¬Ð޸쬲åÈ룩ʱ£¬Êý¾ÝÔÚ´ÅÅÌÉϵÄλÖÃÊÇËæ»úµÄ£¬½«»á¸ü¼ÓÓ°Ïì²Ù×÷Êý¾ÝµÄÐÔÄÜ£¬InnoDBÓÖÊÇÈçºÎ½â¾öµÄ£¿

Õë¶ÔÎÊÌâ1£¬·Ç³£ÓÐЧµÄ°ì·¨ÊÇÒýÈ뻺´æÀ´½â¾ö£¬µ«ÊÇÒýÈ뻺´æºó£¬»áµ¼Ö»º´æÊý¾ÝºÍ´ÅÅÌÊý¾ÝÒ»ÖÂÐÔºÍMySQL·ÇÕý³£ËÀÍöʱ»º´æÊý¾Ý¶ªÊ§µÄÎÊÌâ¡£

InnoDBÓÐÄÄÐ©ÖØÒªÌØÐÔ£¿

¶þ¡¢InnoDBÌåϵ¼Ü¹¹

ÈçͼËùʾ£¬InnoDB´æ´¢ÒýÇæÓÉÄÚ´æ³ØºÍһЩºǫ́Ïß³Ì×é³É£¬Æä¸÷×ÔÖ÷ÒªµÄ¹¤×÷ÊÇ£º

ÄÚ´æ³ØÖ÷Òª¹¤×÷

ά»¤ËùÓнø³Ì/Ïß³ÌÐèÒª·ÃÎʵĶà¸öÄÚ²¿Êý¾Ý½á¹¹

»º´æ´ÅÅÌÉϵÄÊý¾Ý£¬·½±ã¿ìËÙ¶ÁÈ¡£¬Í¬Ê±ÔÚ¶Ô´ÅÅÌÎļþÐÞ¸Ä֮ǰ½øÐлº´æ

»º´æÖØ×öÈÕÖ¾£¨redo log£©

ºǫ́Ïß³ÌÖ÷Òª¹¤×÷

Ë¢ÐÂÄÚ´æ³ØÖеÄÊý¾Ý£¬±£Ö¤»º³å³ØÖлº´æµÄÊý¾Ý×îÐÂ

½«ÒÑÐÞ¸ÄÊý¾ÝÎļþˢе½´ÅÅÌÎļþ

±£Ö¤Êý¾Ý¿âÒ쳣ʱInnoDBÄָܻ´µ½Õý³£ÔËÐÐ״̬

2.1¡¢ InnoDBÄÚ´æ³Ø

2.1.1. InnoDBÄÚ´æ³Ø¼Ü¹¹Í¼

2.1.2¡¢ »º³å³Ø

InnoDB»º³å³ØÊÇΪÁËͨ¹ýÄÚ´æµÄËÙ¶ÈÀ´ÃÖ²¹´ÅÅÌËÙ¶ÈÂý¶ÔÊý¾Ý¿âÐÔÄÜÔì³ÉµÄÓ°Ïì¡£Æä¹¤×÷·½Ê½×ÜÊǽ«Êý¾Ý¿âÎļþ°´Ò³£¨Ã¿Ò³16K£©¶ÁÈ¡µ½»º³å³Ø£¬È»ºó°´×î½ü×îÉÙʹÓã¨LRU£©µÄËã·¨À´±£ÁôÔÚ»º³å³ØÖеĻº´æÊý¾Ý¡£ÔÚÊý¾Ý¿âÖнøÐжÁ²Ù×÷ʱ£¬Ê×ÏȽ«´Ó´ÅÅ̶Áµ½µÄÒ³´æ·ÅÔÚ»º³å³ØÖУ¬ÏÂÒ»´Î¶ÁÈ¡ÏàͬµÄҳʱ£¬Ê×ÏÈÅж¨ÊÇ·ñ´æÔÚ»º³å³ØÖУ¬Èç¹ûÓоÍÊDZ»ÃüÖÐÖ±½Ó¶ÁÈ¡£¬Ã»Óеϰ¾Í´Ó´ÅÅÌÖжÁÈ¡¡£ÔÚÊý¾Ý¿â½øÐиIJÙ×÷ʱ£¬Ê×ÏÈÐ޸Ļº³å³ØÖеÄÒ³£¨Ð޸ĺ󣬸ÃÒ³¼´ÎªÔàÒ³£©£¬È»ºóÔÚÒÔÒ»¶¨µÄƵÂÊˢе½´ÅÅÌÉÏ¡£ÕâÀïµÄˢлúÖÆ²»ÊÇÿҳÔÚ·¢Éú±ä¸üʱ´¥·¢¡£¶øÊÇͨ¹ýÒ»ÖÖcheckpoint»úÖÆË¢Ðµ½´ÅÅ̵ġ£

ËùÒÔ»º³å³ØµÄ´óСֱ½ÓÓ°Ïì×ÅÊý¾Ý¿âµÄÕûÌåÐÔÄÜ£¬¿ÉÒÔͨ¹ýÅäÖòÎÊýinnodb_buffer_pool_sizeÀ´ÉèÖá£

´Ó¼Ü¹¹Í¼ÖпÉÒÔ¿´³ö»º³å³ØÖлº´æµÄÊý¾ÝÒ³ÀàÐÍÓÐ:Ë÷ÒýÒ³¡¢Êý¾ÝÒ³¡¢ undo Ò³¡¢²åÈ뻺³å¡¢×ÔÊÊÓ¦¹þÏ£Ë÷Òý¡¢ InnoDB µÄËøÐÅÏ¢¡¢Êý¾Ý×ÖµäÐÅÏ¢µÈ¡£Ë÷ÒýÒ³ºÍÊý¾ÝÒ³Õ¼»º³å³ØµÄºÜ´óÒ»²¿·Ö¡£

Êý¾ÝÒ³ºÍË÷ÒýÒ³: PageÊÇInnodb´æ´¢µÄ×î»ù±¾½á¹¹£¬Ò²ÊÇInnodb´ÅÅ̹ÜÀíµÄ×îСµ¥Î»£¬ÓëÊý¾Ý¿âÏà¹ØµÄËùÓÐÄÚÈݶ¼´æ´¢ÔÚPage½á¹¹Àï¡£Page·ÖΪ¼¸ÖÖÀàÐÍ£¬Êý¾ÝÒ³ºÍË÷ÒýÒ³¾ÍÊÇÆäÖÐ×îÎªÖØÒªµÄÁ½ÖÖÀàÐÍ¡£

²åÈ뻺´æ: ÔÚInnoDBÒýÇæÉϽøÐвåÈë²Ù×÷ʱ£¬Ò»°ãÐèÒª°´ÕÕÖ÷¼ü˳Ðò½øÐвåÈ룬ÕâÑù²ÅÄÜ»ñµÃ½Ï¸ßµÄ²åÈëÐÔÄÜ¡£µ±Ò»ÕűíÖдæÔڷǾ۴صÄÇÒ²»Î¨Ò»µÄË÷Òýʱ£¬ÔÚ²åÈëʱ£¬Êý¾ÝÒ³µÄ´æ·Å»¹Êǰ´ÕÕÖ÷¼ü½øÐÐ˳Ðò´æ·Å£¬µ«ÊǶÔÓڷǾ۴ØË÷ÒýÒ¶½ÚµãµÄ²åÈë²»ÔÙÊÇ˳ÐòµÄÁË£¬Õâʱ¾ÍÐèÒªÀëÉ¢µÄ·ÃÎʷǾ۴ØË÷ÒýÒ³£¬ÓÉÓÚËæ»ú¶ÁÈ¡µÄ´æÔÚµ¼Ö²åÈë²Ù×÷ÐÔÄÜϽµ¡£

InnoDBΪ´ËÉè¼ÆÁËInsert BufferÀ´½øÐвåÈëÓÅ»¯¡£¶ÔÓڷǾ۴ØË÷ÒýµÄ²åÈë»òÕ߸üвÙ×÷£¬²»ÊÇÿһ´Î¶¼Ö±½Ó²åÈëµ½Ë÷ÒýÒ³ÖУ¬¶øÊÇÏÈÅжϲåÈëµÄ·Ç¾Û¼¯Ë÷ÒýÊÇ·ñÔÚ»º³å³ØÖУ¬ÈôÔÚ£¬ÔòÖ±½Ó²åÈ룻Èô²»ÔÚ£¬ÔòÏÈ·ÅÈëµ½Ò»¸öInsert BufferÖС£¿´ËÆÊý¾Ý¿âÕâ¸ö·Ç¾Û¼¯µÄË÷ÒýÒѾ­²éµ½Ò¶½Úµã£¬¶øÊµ¼ÊûÓУ¬Õâʱ´æ·ÅÔÚÁíÍâÒ»¸öλÖá£È»ºóÔÙÒÔÒ»¶¨µÄƵÂʺÍÇé¿ö½øÐÐInsert BufferºÍ·Ç¾Û´ØË÷ÒýÒ³×Ó½ÚµãµÄºÏ²¢²Ù×÷¡£Õâʱͨ³£Äܹ»½«¶à¸ö²åÈëºÏ²¢µ½Ò»¸ö²Ù×÷ÖУ¬ÕâÑù¾Í´ó´óÌá¸ßÁ˶ÔÓڷǾ۴ØË÷ÒýµÄ²åÈëÐÔÄÜ¡£

×ÔÊÊÓ¦¹þÏ£Ë÷Òý: InnoDB»á¸ù¾Ý·ÃÎÊµÄÆµÂʺÍģʽ£¬ÎªÈȵãÒ³½¨Á¢¹þÏ£Ë÷Òý£¬À´Ìá¸ß²éѯЧÂÊ¡£InnoDB´æ´¢ÒýÇæ»á¼à¿Ø¶Ô±íÉϸ÷¸öË÷ÒýÒ³µÄ²éѯ£¬Èç¹û¹Û²ìµ½½¨Á¢¹þÏ£Ë÷Òý¿ÉÒÔ´øÀ´ËÙ¶ÈÉϵÄÌáÉý£¬Ôò½¨Á¢¹þÏ£Ë÷Òý£¬ËùÒÔ½Ð×ö×ÔÊÊÓ¦¹þÏ£Ë÷Òý¡£

×ÔÊÊÓ¦¹þÏ£Ë÷ÒýÊÇͨ¹ý»º³å³ØµÄB+Ê÷Ò³¹¹½¨¶øÀ´£¬Òò´Ë½¨Á¢ËٶȺܿ죬¶øÇÒ²»ÐèÒª¶ÔÕûÕÅÊý¾Ý±í½¨Á¢¹þÏ£Ë÷Òý¡£Æä ÓÐÒ»¸öÒªÇ󣬼´¶ÔÕâ¸öÒ³µÄÁ¬Ðø·ÃÎÊģʽ±ØÐëÊÇÒ»ÑùµÄ£¬Ò²¾ÍÊÇ˵Æä²éѯµÄÌõ¼þ(WHERE)±ØÐëÍêȫһÑù£¬¶øÇÒ±ØÐëÊÇÁ¬ÐøµÄ¡£

ËøÐÅÏ¢ : nnoDB´æ´¢ÒýÇæ»áÔÚÐм¶±ðÉ϶ԱíÊý¾Ý½øÐÐÉÏËø¡£²»¹ýInnoDBÒ²»áÔÚÊý¾Ý¿âÄÚ²¿ÆäËûºÜ¶àµØ·½Ê¹ÓÃËø£¬´Ó¶øÔÊÐí¶Ô¶àÖÖ²»Í¬×ÊÔ´Ìṩ²¢·¢·ÃÎÊ¡£Êý¾Ý¿âϵͳʹÓÃËøÊÇΪÁËÖ§³Ö¶Ô¹²Ïí×ÊÔ´½øÐв¢·¢·ÃÎÊ£¬ÌṩÊý¾ÝµÄÍêÕûÐÔºÍÒ»ÖÂÐÔ¡£¹ØÓÚËøµÄ¾ßÌå֪ʶÎÒÃÇÖ®ºóÔÙ½øÐÐÏêϸѧϰ¡£

Êý¾Ý×ÖµäÐÅÏ¢ : InnoDBÓÐ×Ô¼ºµÄ±í»º´æ£¬¿ÉÒÔ³ÆÎª±í¶¨Ò建´æ»òÕßÊý¾Ý×ֵ䡣µ±InnoDB´ò¿ªÒ»ÕÅ±í£¬¾ÍÔö¼ÓÒ»¸ö¶ÔÓ¦µÄ¶ÔÏóµ½Êý¾Ý×ֵ䡣

Êý¾Ý×ÖµäÊǶÔÊý¾Ý¿âÖеÄÊý¾Ý¡¢¿â¶ÔÏó¡¢±í¶ÔÏóµÈµÄÔªÐÅÏ¢µÄ¼¯ºÏ¡£ÔÚMySQLÖУ¬Êý¾Ý×ÖµäÐÅÏ¢ÄÚÈݾͰüÀ¨±í½á¹¹¡¢Êý¾Ý¿âÃû»ò±íÃû¡¢×ֶεÄÊý¾ÝÀàÐÍ¡¢ÊÓͼ¡¢Ë÷Òý¡¢±í×Ö¶ÎÐÅÏ¢¡¢´æ´¢¹ý³Ì¡¢´¥·¢Æ÷µÈÄÚÈÝ¡£MySQL INFORMATION_SCHEMA¿âÌṩÁ˶ÔÊý¾Ý¾ÖÔªÊý¾Ý¡¢Í³¼ÆÐÅÏ¢¡¢ÒÔ¼°ÓйØMySQL serverµÄ·ÃÎÊÐÅÏ¢£¨ÀýÈ磺Êý¾Ý¿âÃû»ò±íÃû£¬×ֶεÄÊý¾ÝÀàÐͺͷÃÎÊȨÏ޵ȣ©¡£¸Ã¿âÖб£´æµÄÐÅÏ¢Ò²¿ÉÒÔ³ÆÎªMySQLµÄÊý¾Ý×ֵ䡣

2.1.3. ÖØ×öÈÕÖ¾³å³Ø

InnoDBÓÐbuffer pool£¨¼ò³Æbp£©¡£bpÊÇÊý¾Ý¿âÒ³ÃæµÄ»º´æ£¬¶ÔInnoDBµÄÈκÎÐ޸IJÙ×÷¶¼»áÊ×ÏÈÔÚbpµÄpageÉϽøÐУ¬È»ºóÕâÑùµÄÒ³Ãæ½«±»±ê¼ÇΪdirty²¢±»·Åµ½×¨ÃŵÄflush listÉÏ£¬ºóÐø½«ÓÉmaster thread»òרÃŵÄË¢ÔàÏ߳̽׶ÎÐԵĽ«ÕâÐ©Ò³ÃæÐ´Èë´ÅÅÌ£¨disk or ssd£©¡£ÕâÑùµÄºÃ´¦ÊDZÜÃâÿ´Îд²Ù×÷¶¼²Ù×÷´ÅÅ̵¼Ö´óÁ¿µÄËæ»úIO£¬½×¶ÎÐÔµÄË¢Ôà¿ÉÒÔ½«¶à´Î¶ÔÒ³ÃæµÄÐÞ¸Ämerge³ÉÒ»´ÎIO²Ù×÷£¬Í¬Ê±Ò첽дÈëÒ²½µµÍÁË·ÃÎʵÄʱÑÓ¡£È»¶ø£¬Èç¹ûÔÚdirty page»¹Î´Ë¢Èë´ÅÅÌʱ£¬server·ÇÕý³£¹Ø±Õ£¬ÕâЩÐ޸IJÙ×÷½«»á¶ªÊ§£¬Èç¹ûдÈë²Ù×÷ÕýÔÚ½øÐУ¬ÉõÖÁ»áÓÉÓÚËð»µÊý¾ÝÎļþµ¼ÖÂÊý¾Ý¿â²»¿ÉÓá£ÎªÁ˱ÜÃâÉÏÊöÎÊÌâµÄ·¢Éú£¬Innodb½«ËùÓжÔÒ³ÃæµÄÐ޸IJÙ×÷дÈëÒ»¸öרÃŵÄÎļþ£¬²¢ÔÚÊý¾Ý¿âÆô¶¯Ê±´Ó´ËÎļþ½øÐлָ´²Ù×÷£¬Õâ¸öÎļþ¾ÍÊÇredo log file¡£ÕâÑùµÄ¼¼ÊõÍÆ³ÙÁËbpÒ³ÃæµÄˢУ¬´Ó¶øÌáÉýÁËÊý¾Ý¿âµÄÍÌÍ£¬ÓÐЧµÄ½µµÍÁË·ÃÎÊʱÑÓ¡£´øÀ´µÄÎÊÌâÊǶîÍâµÄдredo log²Ù×÷µÄ¿ªÏú£¨Ë³ÐòIO£¬µ±È»ºÜ¿ì£©£¬ÒÔ¼°Êý¾Ý¿âÆô¶¯Ê±»Ö¸´²Ù×÷ËùÐèµÄʱ¼ä¡£

redoÈÕÖ¾ÓÉÁ½²¿·Ö¹¹³É£ºredo log buffer¡¢redo log file¡£innodbÊÇÖ§³ÖÊÂÎñµÄ´æ´¢ÒýÇæ£¬ÔÚÊÂÎñÌύʱ£¬±ØÐëÏȽ«¸ÃÊÂÎñµÄËùÓÐÈÕ־дÈëµ½redoÈÕÖ¾ÎļþÖУ¬´ýÊÂÎñµÄcommit²Ù×÷Íê³É²ÅËãÕû¸öÊÂÎñ²Ù×÷Íê³É¡£ÔÚÿ´Î½«redo log bufferдÈëredo log fileºó£¬¶¼ÐèÒªµ÷ÓÃÒ»´Îfsync²Ù×÷£¬ÒòÎªÖØ×öÈÕÖ¾»º³åÖ»ÊǰÑÄÚÈÝÏÈдÈë²Ù×÷ϵͳµÄ»º³åϵͳÖУ¬²¢Ã»ÓÐÈ·±£Ö±½ÓдÈëµ½´ÅÅÌÉÏ£¬ËùÒÔ±ØÐë½øÐÐÒ»´Îfsync²Ù×÷¡£Òò´Ë£¬´ÅÅ̵ÄÐÔÄÜÔÚÒ»¶¨³Ì¶ÈÉÏÒ²¾ö¶¨ÁËÊÂÎñÌá½»µÄÐÔÄÜ¡£

InnoDB ´æ´¢ÒýÇæÏȽ«ÖØ×öÈÕÖ¾ÐÅÏ¢·ÅÈëÕâ¸ö»º³åÇø,È»ºóÒÔÒ»¶¨ÆµÂʽ«Æäˢе½ÖØ×öÈÕÖ¾Îļþ¡£ÖØ×öÈÕÖ¾ÎļþÒ»°ã²»ÐèÒªÉèÖõúܴó,ÒòΪÔÚÏÂÁÐÈýÖÖÇé¿öÏÂÖØ×öÈÕÖ¾»º³åÖеÄÄÚÈÝ»áˢе½´ÅÅ̵ÄÖØ×öÈÕÖ¾ÎļþÖС£

Master Thread ÿһÃë½«ÖØ×öÈÕÖ¾»º³åˢе½ÖØ×öÈÕÖ¾Îļþ

ÿ¸öÊÂÎïÌύʱ»á½«ÖØ×öÈÕÖ¾»º³åˢе½ÖØ×öÈÕÖ¾Îļþ

µ±ÖØ×öÈÕÖ¾»º³åÊ£Óà¿Õ¼äСÓÚ1/2ʱ,ÖØ×öÈÕÖ¾»º³åˢе½ÖØ×öÈÕÖ¾Îļþ

2.1.4. ¶îÍâµÄ»º³å³Ø

ÔÚ InnoDB ´æ´¢ÒýÇæÖУ¬¶ÔһЩÊý¾Ý½á¹¹±¾ÉíµÄÄÚ´æ½øÐзÖÅäʱ£¬ÐèÒª´Ó¶îÍâµÄÄÚ´æ³ØÖнøÐÐÉêÇë¡£ÀýÈç: ·ÖÅäÁË»º³å³Ø,µ«ÊÇÿ¸ö»º³å³ØÖеÄÖ¡»º³å»¹ÓжÔÓ¦µÄ»º³å¿ØÖƶÔÏó£¬ÕâЩ¶ÔÏó¼Ç¼ÒÔһЩÖîÈç LRU, Ëø,µÈ´ýµÈÐÅÏ¢,¶øÕâ¸ö¶ÔÏóµÄÄÚ´æÐèÒª´Ó¶îÍâµÄÄÚ´æ³ØÖÐÉêÇë¡£

2.2 Ö÷Òªºǫ́Ïß³Ì

2.2.1. master thread

ºËÐĵĺǫ́Ị̈߳¬Ö÷Òª¸ºÔ𽫻º³å³ØÖеÄÊý¾ÝÒ첽ˢе½´ÅÅÌ£¬±£Ö¤Êý¾ÝµÄÒ»ÖÂÐÔ£¬°üÀ¨ÔàÒ³µÄˢС¢ºÏ²¢²åÈ뻺³å¡¢undoÒ³µÄ»ØÊյȡ£

Master threadÔÚÖ÷Ñ­»·ÖУ¬·ÖÁ½´ó²¿·Ö²Ù×÷£¬Ã¿ÃëÖӵIJÙ×÷ºÍÿ10ÃëÖӵIJÙ×÷£º

ÿÃëÒ»´ÎµÄ²Ù×÷

ÈÕÖ¾»º³åˢе½´ÅÅÌ: ¼´Ê¹Õâ¸öÊÂÎñ»¹Ã»ÓÐÌá½»£¨×ÜÊÇ£©£¬Õâµã½âÊÍÁËΪʲôÔÙ´óµÄÊÂÎñcommitʱ¶¼ºÜ¿ì£»

ºÏ²¢²åÈ뻺³å£¨¿ÉÄÜ£©: ºÏ²¢²åÈë²¢²»ÊÇÿÃë¶¼·¢Éú£¬InnoDB»áÅжϵ±Ç°Ò»ÃëÄÚ·¢ÉúµÄIO´ÎÊýÊÇ·ñСÓÚ5£¬Èç¹ûÊÇ£¬ÔòϵͳÈÏΪµ±Ç°µÄIOѹÁ¦ºÜС£¬¿ÉÒÔÖ´Ðкϲ¢²åÈ뻺³åµÄ²Ù×÷¡£

ÖÁ¶àË¢ÐÂ100¸öInnoDBµÄ»º³å³ØµÄÔàÒ³µ½´ÅÅÌ£¨¿ÉÄÜ) : Õâ¸öË¢ÐÂ100¸öÔàÒ³Ò²²»ÊÇÿÃë¶¼ÔÚ×ö£¬InnoDBÒýÇæÍ¨¹ýÅжϵ±Ç°»º³å³ØÖÐÔàÒ³µÄ±ÈÀý(buf_get_modified_ratio_pct)ÊÇ·ñ³¬¹ýÁËÅäÖÃÎļþÖÐinnodb_max_drity_pages_pct²ÎÊý(ĬÈÏÊÇ90£¬¼´90%)£¬Èç¹û³¬¹ýÁËÕâ¸öãÐÖµ£¬InnoDBÒýÇæÈÏΪÐèÒª×ö´ÅÅÌͬ²½²Ù×÷£¬½«100¸öÔàҳдÈë´ÅÅÌ¡£

ÿ10ÃëÒ»´ÎµÄ²Ù×÷

Ë¢ÐÂ100¸öÔàÒ³µ½´ÅÅÌ£¨¿ÉÄÜ£©: InnoDBÒýÇæÏÈÅжϹýÈ¥10ÃëÄÚ´ÅÅ̵ÄIO²Ù×÷ÊÇ·ñСÓÚ200´Î£¬Èç¹ûÊÇ£¬ÈÏΪµ±Ç°´ÅÅÌÓÐ×ã¹»µÄIO²Ù×÷ÄÜÁ¦£¬¼´½«100¸öÔàҳˢе½´ÅÅÌ¡£

ºÏ²¢ÖÁ¶à5¸ö²åÈ뻺³å£¨×ÜÊÇ£©: ´Ë´ÎµÄºÏ²¢²åÈ뻺³å²Ù×÷×Ü»áÖ´ÐУ¬²»Í¬ÓÚÿÃë²Ù×÷ʱ¿ÉÄÜ·¢ÉúµÄºÏ²¢²Ù×÷¡£

½«ÈÕÖ¾»º³åˢе½´ÅÅÌ£¨×ÜÊÇ£©: InnoDBÒýÇæ»áÔÙ´ÎÖ´ÐÐÈÕÖ¾»º³åˢе½´ÅÅ̵IJÙ×÷£¬ÓëÿÃë·¢ÉúµÄ²Ù×÷Ò»Ñù¡£

ɾ³ýÎÞÓõÄundoÒ³£¨×ÜÊÇ£©: µ±¶Ô±íÖ´ÐÐupdate£¬delete²Ù×÷ʱ£¬Ô­ÏȵÄÐлᱻ±ê¼ÇΪɾ³ý£¬µ«ÊÇΪÁËÒ»ÖÂÐÔ¶ÁµÄ¹ØÏµ£¬Ðè±£ÁôÕâЩÐа汾µÄÐÅÏ¢£¬ÔÚ½øÐÐ10SÒ»´ÎµÄɾ³ý²Ù×÷ʱ£¬InnoDBÒýÇæ»áÅжϵ±Ç°ÊÂÎñϵͳÖÐÒѱ»É¾³ýµÄÐÐÊÇ·ñ¿ÉÒÔɾ³ý£¬Èç¹û¿ÉÒÔ£¬InnoDB»áÁ¢¼´½«Æäɾ³ý¡£InnoDBÿ´Î×î¶àɾ³ý20¸öUndoÒ³¡£

²úÉúÒ»¸ö¼ì²éµã£¨checkpoing£©£»

2.2.2. IO threads

ÔÚ InnoDB ´æ´¢ÒýÇæÖдóÁ¿Ê¹ÓÃÁËÒì²½ IO À´´¦Àíд IO ÇëÇó£¬IO Thread µÄ¹¤×÷Ö÷ÒªÊǸºÔðÕâЩ IO ÇëÇóµÄ»Øµ÷.¡£·Ö±ðΪwrite¡¢read¡¢insert bufferºÍlog IO thread¡£Ïß³ÌÊýÁ¿¿ÉÒÔͨ¹ý²ÎÊý½øÐе÷Õû¡£5.6ÒÔºóµÄ°æ±¾¿ÉÒÔͨ¹ýinnodb_write_io_threadsºÍinnodb_read_io_threadsÀ´ÏÞÖÆ¶ÁдỊ̈߳¬¶øÔÚ5.6°æ±¾ÒÔǰ£¬Ö»ÓÐÒ»¸ö²ÎÊýinnodb_file_io_threadsÀ´¿ØÖƶÁд×ÜÏß³ÌÊý¡£

2.2.3. purge threads

¸ºÔð»ØÊÕÒѾ­Ê¹Óò¢·ÖÅäµÄundoÒ³£¬purge²Ù×÷ĬÈÏÊÇÓÉmaster threadÖÐÍê³ÉµÄ£¬ÎªÁ˼õÇámaster threadµÄ¹¤×÷£¬Ìá¸ßcpuʹÓÃÂÊÒÔ¼°ÌáÉý´æ´¢ÒýÇæµÄÐÔÄÜ¡£Óû§¿ÉÒÔÔÚ²ÎÊýÎļþÖÐÌí¼ÓÈçÏÂÃüÁîÀ´Æô¶¯¶ÀÁ¢µÄpurge thread¡£

innodb_purge_threads=1

´Óinnodb1.2°æ±¾¿ªÊ¼£¬¿ÉÒÔÖ¸¶¨¶à¸öinnodb_purge_threadsÀ´½øÒ»²½¼Ó¿ìºÍÌá¸ßundo»ØÊÕËÙ¶È¡£

2.2.4. page cleaner threads

Page Cleaner ThreadÊÇÔÚInnoDB1.2.X°æ±¾ÖÐÒýÈëµÄ¡£Æä×÷ÓÃÊǽ«Ö®Ç°°æ±¾ÖÐÔàÒ³µÄˢвÙ×÷¶¼·ÅÈëµ½µ¥¶ÀµÄÏß³ÌÖÐÀ´Íê³É¡£ ÆäÄ¿µÄÊǼõÇámaster threadµÄ¹¤×÷ÒÔ¼°¶ÔÓÚÓû§²éѯÏ̵߳Ä×èÈû£¬½øÒ»²½Ìá¸ßInnoDB´æ´¢ÒýÇæµÄÐÔÄÜ¡£

Èý¡¢InnoDBÖØÒªÌØÐÔ

MySQL InnoDBͨ¹ýÈçÏÂÖØÒªÌØÐÔʵÏÖÁ˸üºÃµÄÐÂÄܺ͸ü¸ßµÄÌØÐÔ

²åÈ뻺³å£¨insert buffer£©

Á½´Îд£¨Double write£©

×ÔÊÊÓ¦¹þÏ£Ë÷Òý£¨adaptive hash index£©

Òì²½io£¨Async IO£©

Ë¢ÐÂÁì½ÓÒ³£¨Flush Neighbor Page£©

3.1 ²åÈ뻺³å

3.1.1. ¾Ù¸öÀõ×Ó

ÎÒÃÇȥͼÊé¹Ý»¹Ê飬¶ÔӦͼÊé¹ÝÀ´Ëµ£¬ËûÊÇ×öÁËinsert(Ôö¼Ó)²Ù×÷£¬¹ÜÀíÔ±ÔÚ1СʱÄÚ½ÓÊÜÁË100±¾Ê飬ÕâʱºòËûÓÐ2ÖÖ×ö·¨°Ñ»¹»ØÀ´µÄÊé¹éλµ½Êé¼ÜÉÏ:

1£©Ã¿»¹»ØÀ´Ò»±¾Ê飬¸ù¾ÝÕâ±¾ÊéµÄ±àÂ루Êé¹ñÇø-ÅÅ-ºÅ£©°ÑÊéËͻؼÜÉÏ

2£©ÔÝʱ²»×ö¹éλ²Ù×÷£¬Ïȷŵ½¹ñÃæÉÏ£¬µÈ²»Ã¦µÄʱºò£¬ÔÙ°ÑÕâЩÊé°´ÕÕÊé¹ñÇø-ÅÅ-ºÅÏÈÅźã¬È»ºóÒ»´ÎÐÔ¹éλ

Ó÷½·¨1£¬¹ÜÀíÔ±ÐèÒª½ø³ö£¨IO£©²ØÊéÇø100´Î£¬²»Í£µÄµÇ¸ßÅÀµÍÍê³ÉͼÊé¹éλ²Ù×÷£¬ÀÛËÀÀۻЧÂʺܲ

Ó÷½·¨2£¬¹ÜÀíÔ±Ö»ÐèÒª½ø³ö£¨IO£©²ØÊéÇø1´Î£¬¶Ôͬһ¸öλÖõÄÊ飬²»¹Ü¶àÉÙ£¬¶¼Ö»ÒªÅÀÒ»´ÎÂ¥ÌÝ£¬´ó´ó¼õÇáÁ˹ÜÀíÔ±µÄ¹¤×÷Á¿¡£

ËùÒÔͼÊé¹Ý¶¼Êǰ´ÕÕ·½·¨2À´×ö»¹Ê鶯×÷µÄ¡£µ«ÊÇÄãҪ˵£¬ÎÒµÄͼÊé¹Ý¾Í20±¾Ê飬1¸ö0.5Ã׵ļÜ×Ó£¬·½·¨2ºÍ1¹ÜÀíÆðÀ´¶¼ºÜ·½±ã£¬ÕâÖÖÇé¿ö²»ÔÚÎÒÃÇÌÖÂ۵ķ¶Î§¡£µ±Êý¾ÝÁ¿·Ç³£Ð¡µÄʱºò£¬¾Í²»´æÔÚЧÂÊÎÊÌâÁË¡£

¹ØÏµÊý¾Ý¿âÔÚ´¦Àí²åÈë²Ù×÷µÄʱºò£¬´¦ÀíµÄ·½·¨ºÍÉÏÃæÀàËÆ£¬Ã¿Ò»´Î²åÈë¶¼Ï൱ÓÚ»¹Ò»±¾Ê飬ËüÒ²ÐèÒªÒ»¸ö¹ñ̨À´±£´æ²åÈëµÄÊý¾Ý£¬È»ºó·ÖÀà¹éµµ£¬ÔÚ²»Ã¦µÄʱºò×öÅúÁ¿µÄ¹éλ¡£Õâ¸ö¹ñ̨¾ÍÊÇinsert buffer.

Õâ¾ÍÊÇΪʲô»áÓÐinsert buffer£¬¸ü¶àµÄÊÇ´¦ÓÚÐÔÄÜÓÅ»¯µÄ¿¼ÂÇ¡£

3.1.2. ʲôÊDzåÈ뻺³å

insert bufferÊÇÒ»ÖÖÌØÊâµÄÊý¾Ý½á¹¹£¨B+ tree£©²¢²»ÊÇ»º´æµÄÒ»²¿·Ö£¬¶øÊÇÎïÀíÒ³¡£¶ÔÓڷǾۼ¯Ë÷ÒýµÄ²åÈë»ò¸üвÙ×÷,²»ÊÇÿһ´ÎÖ±½Ó²åÈëË÷ÒýÒ³.¶øÊÇÏÈÅжϲåÈëµÄ·Ç¾Û¼¯Ë÷ÒýÒ³ÊÇ·ñÔÚ»º³å³ØÖÐ.Èç¹ûÔÚ,ÔòÖ±½Ó²åÈë,Èç¹û²»ÔÙ,ÔòÏÈ·ÅÈëÒ»¸ö²åÈ뻺³åÇøÖÐ.È»ºóÔÙÒÔÒ»¶¨µÄƵÂÊÖ´ÐвåÈ뻺³åºÍ·Ç¾Û¼¯Ë÷ÒýÒ³×Ó½ÚµãµÄºÏ²¢²Ù×÷.ʹÓÃÌõ¼þ:·Ç¾Û¼¯Ë÷Òý,·ÇΨһ£¬Ô­ÒòÈçÏÂ:

primary key Êǰ´ÕÕµÝÔöµÄ˳Ðò½øÐвåÈëµÄ£¬Òì³£²åÈë¾Û×åË÷ÒýÒ»°ãҲ˳ÐòµÄ£¬·ÇËæ»úIO¡£

дΨһË÷ÒýÒª¼ì²é¼Ç¼ÊDz»ÊÇ´æÔÚ£¬ËùÒÔÔÚÐÞ¸ÄΨһË÷Òý֮ǰ,±ØÐë°ÑÐ޸ĵļǼÏà¹ØµÄË÷ÒýÒ³¶Á³öÀ´²ÅÖªµÀÊDz»ÊÇΨһ¡¢ÕâÑùInsert buffer¾ÍûÒâÒåÁË£¬Òª¶Á³öÀ´(Ëæ»úIO)£¬ËùÒÔÖ»¶Ô·ÇΨһË÷ÒýÓÐЧ¡£

3.1.3. insert bufferµÄÔ­Àí

¶ÔÓÚΪ·ÇΨһË÷Òý£¬¸¨ÖúË÷ÒýµÄÐ޸IJÙ×÷²¢·Çʵʱ¸üÐÂË÷ÒýµÄÒ¶×ÓÒ³£¬¶øÊǰÑÈô¸É¶ÔÍ¬Ò»Ò³ÃæµÄ¸üлº´æÆðÀ´×ö£¬ºÏ²¢ÎªÒ»´ÎÐÔ¸üв٠×÷£¬¼õÉÙIO£¬×ªËæ»úIOΪ˳ÐòIO,ÕâÑù¿ÉÒÔ±ÜÃâËæ»úIO´øÀ´ÐÔÄÜËðºÄ£¬Ìá¸ßÊý¾Ý¿âµÄдÐÔÄÜ£¬¾ßÌåÁ÷³Ì:

1) ÏÈÅжÏÒª¸üеÄÕâÒ»Ò³ÔÚ²»ÔÚ»º³å³ØÖÐ

a¡¢ÈôÔÚ£¬ÔòÖ±½Ó²åÈ룻

b¡¢Èô²»ÔÚ£¬Ôò½«index page ´æÈëInsert Buffer£¬°´ÕÕMaster ThreadµÄµ÷¶È¹æÔòÀ´ºÏ²¢·ÇΨһË÷ÒýºÍË÷ÒýÒ³ÖеÄÒ¶×Ó½áµã

2) Master ThreadµÄµ÷¶È¹æÔò

a¡¢Ö÷¶¯merger: innodbÖ÷Ï̶߳¨ÆÚÍê³É£¬Óû§Ïß³ÌÎÞ¸ÐÖª

Ö÷¶¯mergeͨ¹ýinnodbÖ÷Ïß³Ì(svr_master_thread£©ÅжϣºÈô¹ýÈ¥1sÖ®ÄÚ·¢ÉúµÄI/OСÓÚϵͳI/OÄÜÁ¦µÄ5%£¬ÔòÖ÷¶¯½øÐÐÒ»´Îinsert bufferµÄmerge²Ù×÷¡£mergeµÄÒ³ÃæÊýΪϵͳI/OÄÜÁ¦µÄ5%£¬¶ÁÈ¡²ÉÓÃasync ioģʽ¡£Ã¿10s,±Ø¶¨´¥·¢Ò»´Îinsert buffer meger²Ù×÷¡£megerµÄÒ³ÃæÊýÈÔ¾ÉΪϵͳ I/OÄÜÁ¦µÄ5%¡£

Ö÷Ï̷߳¢³öasync ioÇëÇó£¬async¶ÁÈ¡ÐèÒª±»mergeµÄË÷ÒýÒ³Ãæ

I/O handler Ị̈߳¬ÔÚ½ÓÊܵ½Íê³ÉµÄasync I/OÖ®ºó£¬½øÐÐmerge

b ¡¢±»¶¯merge: Óû§Ïß³ÌÍê³É£¬Óû§ÄܸÐÊܵ½meger²Ù×÷´øÀ´µÄÐÔÄÜÓ°Ïì

insert²Ù×÷£¬µ¼ÖÂÒ³Ãæ¿Õ¼ä²»×㣬ÐèÒª·ÖÁÑ(split)¡£ÓÉÓÚinsert bufferÖ»Õë¶Ôµ¥¸öÒ³Ãæ£¬²»ÄÜbuffer page split[Ò³ÒѾ­ÔÚÄÚ´æÀï]£¬Òò´ËÒýÆðÒ³ÃæµÄ±»¶¯meger¡£Í¬Àí£¬update²Ù×÷µ¼ÖÂÒ³Ãæ¿Õ¼ä²» ×㣻purgeµ¼ÖÂÒ³ÃæÎª¿ÕµÈ¡£×ÜÖ®,Èôµ±Ç°²Ù×÷ÒýÆðÒ³Ãæsplit or merge£¬ÄÇô¾Í»áµ¼Ö±»¶¯merge£»

insert²Ù×÷£¬ÓÉÓÚÆäËü¸÷ÖÖÔ­Òò£¬insert bufferÓÅ»¯·µ»Øfalse£¬ÐèÒªÕæÕý¶ÁÈ¡pageʱ£¬Òª½øÐб»¶¯merge¡£ÓëÒ»²»Í¬µÄÊÇ£¬Ò³ÔÚdiskÉÏ£¬ÐèÒª¶ÁÈ¡µ½ÄÚ´æÀ

ÔÚ½øÐÐinsert buffer²Ù×÷£¬·¢ÏÖinsert bufferÌ«´ó£¬ÐèҪѹËõinsert buffer£¬ÕâʱÐèÒªÇ¿ÖÆ±»¶¯merge£¬²»ÔÊÐí insert ²Ù×÷½øÐС£

3.2 Á½´Îд

Insert Buffer´ø¸øInnoDB´æ´¢ÒýÇæµÄÊÇÐÔÄÜÉϵÄÌáÉý£¬doublewrite£¨Á½´Îд£©´ø¸øInnoDB´æ´¢ÒýÇæµÄÊÇÊý¾ÝÒ³µÄ¿É¿¿ÐÔ¡£

µ±·¢ÉúÊý¾Ý¿âå´»úʱ£¬¿ÉÄÜInnoDB´æ´¢ÒýÇæÕýÔÚдÈëij¸öÒ³µ½±íÖУ¬¶øÕâ¸öҳֻдÁËÒ»²¿·Ö£¬±ÈÈç16KBµÄÒ³£¬Ö»Ð´ÁËǰ4KB£¬Ö®ºó¾Í·¢ÉúÁËå´»ú£¬ÕâÖÖÇé¿ö±»³ÆÎª²¿·ÖдʧЧ£¨partial page write)¡£ÔÚInnoDB´æ´¢ÒýÇæÎ´Ê¹ÓÃdoublewrite¼¼Êõǰ£¬Ôø¾­³öÏÖ¹ýÒòΪ²¿·ÖдʧЧ¶øµ¼ÖÂÊý¾Ý¶ªÊ§µÄÇé¿ö¡£

Óо­ÑéµÄDBAÒ²Ðí»áÏ룬Èç¹û·¢ÉúдʧЧ£¬¿ÉÒÔͨ¹ýÖØ×öÈÕÖ¾½øÐлָ´¡£ÕâÊÇÒ»¸ö°ì·¨¡£µ«ÊDZØÐëÇå³þµØÈÏʶµ½£¬ÖØ×öÈÕÖ¾ÖмǼµÄÊǶÔÒ³µÄÎïÀí²Ù×÷£¬ÈçÆ«ÒÆÁ¿800£¬Ð´¡®aaaa¡¯¼Ç¼¡£Èç¹ûÕâ¸öÒ³±¾ÉíÒѾ­·¢ÉúÁËË𻵣¬ÔÙ¶ÔÆä½øÐÐÖØ×öÊÇûÓÐÒâÒåµÄ¡£Õâ¾ÍÊÇ˵£¬ÔÚÓ¦ÓÃÖØ×öÈÕ־ǰ£¬Óû§ÐèÒªÒ»¸öÒ³µÄ¸±±¾£¬µ±Ð´ÈëʧЧ·¢Éúʱ£¬ÏÈͨ¹ýÒ³µÄ¸±±¾À´»¹Ô­¸ÃÒ³£¬ÔÙ½øÐÐÖØ×ö£¬Õâ¾ÍÊÇdoublewrite¡£ÔÚInnoDB´æ´¢ÒýÇæÖÐdoublewriteµÄÌåϵ¼Ü¹¹ÈçͼËùʾ£º

3.3 ×ÔÊÊÓ¦¹þÏ£Ë÷Òý

¹þÏ££¨hash£©ÊÇÒ»Öַdz£¿ìµÄ²éÕÒ·½·¨£¬ÔÚÒ»°ãÇé¿öÏÂÕâÖÖ²éÕÒµÄʱ¼ä¸´ÔÓ¶ÈΪO(1)£¬¼´Ò»°ã½öÐèÒªÒ»´Î²éÕÒ¾ÍÄܶ¨Î»Êý¾Ý¡£ ¶øB+Ê÷µÄ²éÕÒ´ÎÊý£¬È¡¾öÓÚB+Ê÷µÄ¸ß¶È£¬ÔÚÉú²ú»·¾³ÖУ¬B+Ê÷µÄ¸ß¶ÈÒ»°ãΪ3~4²ã£¬ËùÒÔÐèÒª3~4´ÎµÄ²éѯ¡£

InnoDB´æ´¢ÒýÇæ»á¼à¿Ø¶Ô±íÉϸ÷Ë÷ÒýÒ³µÄ²éѯ¡£Èç¹û¹Û²ìµ½½¨Á¢¹þÏ£Ë÷Òý¿ÉÒÔ´øÀ´ËÙ¶ÈÌáÉý£¬Ôò½¨Á¢¹þÏ£Ë÷Òý£¬³ÆÖ®Îª×ÔÊÊÓ¦¹þÏ£Ë÷Òý(Adaptive Hash Index, AHI)¡£AHIÊÇͨ¹ý»º³å³ØµÄB+Ê÷Ò³¹¹Ôì¶øÀ´£¬Òò´Ë½¨Á¢µÄËٶȺܿ죬¶øÇÒ²»ÐèÒª¶ÔÕûÕÅ±í¹¹½¨¹þÏ£Ë÷Òý¡£InnoDB´æ´¢ÒýÇæ»á×Ô¶¯¸ù¾Ý·ÃÎÊµÄÆµÂʺÍģʽÀ´×Ô¶¯µØÎªÄ³Ð©ÈȵãÒ³½¨Á¢¹þÏ£Ë÷Òý¡£

AHIÓÐÒ»¸öÒªÇ󣬶ÔÕâ¸öÒ³µÄÁ¬Ðø·ÃÎÊģʽ±ØÐëÊÇÒ»ÑùµÄ¡£ÀýÈç¶ÔÓÚ(a,b)ÕâÑùµÄÁªºÏË÷ÒýÒ³£¬Æä·ÃÎÊģʽ¿ÉÒÔÊÇÏÂÃæÇé¿ö£º

where a=xxx

where a =xxx and b=xxx

·ÃÎÊģʽһÑùÊÇÖ¸²éѯµÄÌõ¼þÊÇÒ»ÑùµÄ£¬Èô½»Ìæ½øÐÐÉÏÊöÁ½ÖÖ²éѯ£¬ÄÇôInnoDB´æ´¢ÒýÇæ²»»á¶Ô¸ÃÒ³¹¹ÔìAHI¡£

AHI»¹ÓÐÏÂÃæ¼¸¸öÒªÇó£º

ÒÔ¸Ãģʽ·ÃÎÊÁË100´Î

ҳͨ¹ý¸Ãģʽ·ÃÎÊÁËN´Î£¬ÆäÖÐN=Ò³ÖмǼ*1/16

InnoDB´æ´¢ÒýÇæ¹Ù·½ÎĵµÏÔʾ£¬ÆôÓÃAHIºó£¬¶ÁÈ¡ºÍдÈëËÙ¶È¿ÉÒÔÌá¸ß2±¶£¬¸¨ÖúË÷ÒýµÄÁ¬½Ó²Ù×÷ÐÔÄÜ¿ÉÒÔÌá¸ß5±¶¡£AHIµÄÉè¼ÆË¼ÏëÊÇÊý¾Ý¿â×ÔÓÅ»¯£¬²»ÐèÒªDBA¶ÔÊý¾Ý¿â½øÐÐÊÖ¶¯µ÷Õû¡£

3.4 Òì²½IO

sync IO £ºÍ¬²½IO ¼´Ã¿½øÐÐÒ»´ÎIO²Ù×÷£¬´Ë´Î²Ù×÷½áÊø²ÅÄܼÌÐø½ÓÏÂÀ´µÄ²Ù×÷¡£ µ«ÊÇÈç¹ûÓû§·¢ÐèÒªµÈ´ý³öÒ»ÌõË÷ÒýɨÃèµÄ²éѯ£¬ÄÇôÕâÌõSQL²éѯÓï¾ä¿ÉÄÜÐèҪɨÃè¶à¸öË÷ÒýÒ³£¬Ò²¾ÍÊÇÐèÒª½øÐжà´ÎµÄIO²Ù×÷¡£ÔÚÿɨÃèÒ»¸öÒ³²¢µÈ´ýÆÚÍê³ÉÔÙ½øÐÐÏÂÒ»´ÎµÄɨÃèÊÇûÓбØÒªµÄ¡£

Òì²½IO£º Óû§¿ÉÒÔÔÚ·¢³öÒ»¸öIOÇëÇóºóÁ¢¼´ÔÙ·¢³öÁíÒ»¸öIOÇëÇ󣬵±È«²¿IOÇëÇó·¢ËÍÍê±Ïºó£¬µÈ´ýËùÓÐIO²Ù×÷µÄÍê³É£¬Õâ¾ÍÊÇAIO¡£

AIOÁíÒ»¸öÓÅÊÆ¿ÉÒÔ½«¶à¸öIO£¬ºÏ²¢Îª1¸öIO£¬ÒÔÌá¸ßIOЧÂÊ¡£ÀýÈ磺Óû§ÐèÒª·ÃÎÊ3Ò³ÄÚÈÝ£¬µ«Õâ3ҳʱÁ¬ÐøµÄ¡£Í¬²½IOÐèÒª½øÐÐ3´ÎIO,¶øAIOÖ»ÐèÒªÒ»´Î ¾Í¿ÉÒÔÁË¡£

3.5 Ë¢ÐÂÁì½ÓÒ³

µ±Ë¢ÐÂÒ»¸öÔàҳʱ£¬innodb»á¼ì²â¸ÃÒ³ËùÔÚÇø£¨extent£©µÄËùÓÐÒ³£¬Èç¹ûÊÇÔàÒ³£¬ÄÇôһÆð½øÐÐˢС£ÕâÑù×ö£¬Í¨¹ýAIO½«¶à¸öIOдÈë²Ù×÷ºÏ²¢ÎªÒ»¸öIO²Ù×÷¡£ÔÚ´«Í³»úе´ÅÅÌÏÂÓÐ×ÅÏÔÖøÓÅÊÆ

 

   
1541 ´Îä¯ÀÀ       27
Ïà¹ØÎÄÕÂ

»ùÓÚEAµÄÊý¾Ý¿â½¨Ä£
Êý¾ÝÁ÷½¨Ä££¨EAÖ¸ÄÏ£©
¡°Êý¾Ýºþ¡±£º¸ÅÄî¡¢ÌØÕ÷¡¢¼Ü¹¹Óë°¸Àý
ÔÚÏßÉ̳ÇÊý¾Ý¿âϵͳÉè¼Æ ˼·+Ч¹û
 
Ïà¹ØÎĵµ

GreenplumÊý¾Ý¿â»ù´¡Åàѵ
MySQL5.1ÐÔÄÜÓÅ»¯·½°¸
ijµçÉÌÊý¾ÝÖÐ̨¼Ü¹¹Êµ¼ù
MySQL¸ßÀ©Õ¹¼Ü¹¹Éè¼Æ
Ïà¹Ø¿Î³Ì

Êý¾ÝÖÎÀí¡¢Êý¾Ý¼Ü¹¹¼°Êý¾Ý±ê×¼
MongoDBʵս¿Î³Ì
²¢·¢¡¢´óÈÝÁ¿¡¢¸ßÐÔÄÜÊý¾Ý¿âÉè¼ÆÓëÓÅ»¯
PostgreSQLÊý¾Ý¿âʵսÅàѵ
×îл¼Æ»®
DeepSeek´óÄ£ÐÍÓ¦Óÿª·¢ 6-12[ÏÃÃÅ]
È˹¤ÖÇÄÜ.»úÆ÷ѧϰTensorFlow 6-22[Ö±²¥]
»ùÓÚ UML ºÍEA½øÐзÖÎöÉè¼Æ 6-30[±±¾©]
ǶÈëʽÈí¼þ¼Ü¹¹-¸ß¼¶Êµ¼ù 7-9[±±¾©]
Óû§ÌåÑé¡¢Ò×ÓÃÐÔ²âÊÔÓëÆÀ¹À 7-25[Î÷°²]
ͼÊý¾Ý¿âÓë֪ʶͼÆ× 8-23[±±¾©]
 
×îÐÂÎÄÕÂ
InfluxDB¸ÅÄîºÍ»ù±¾²Ù×÷
InfluxDB TSM´æ´¢ÒýÇæÖ®Êý¾ÝдÈë
Éî¶ÈÂþ̸Êý¾Ýϵͳ¼Ü¹¹¡ª¡ªLambda architecture
Lambda¼Ü¹¹Êµ¼ù
InfluxDB TSM´æ´¢ÒýÇæÖ®Êý¾Ý¶ÁÈ¡
×îпγÌ
OracleÊý¾Ý¿âÐÔÄÜÓÅ»¯¡¢¼Ü¹¹Éè¼ÆºÍÔËÐÐά»¤
²¢·¢¡¢´óÈÝÁ¿¡¢¸ßÐÔÄÜÊý¾Ý¿âÉè¼ÆÓëÓÅ»¯
NoSQLÊý¾Ý¿â£¨Ô­Àí¡¢Ó¦Óá¢×î¼Ñʵ¼ù£©
ÆóÒµ¼¶Hadoop´óÊý¾Ý´¦Àí×î¼Ñʵ¼ù
OracleÊý¾Ý¿âÐÔÄÜÓÅ»¯×î¼Ñʵ¼ù
³É¹¦°¸Àý
ij½ðÈÚ¹«Ë¾ Mysql¼¯ÈºÓëÐÔÄÜÓÅ»¯
±±¾© ²¢·¢¡¢´óÈÝÁ¿¡¢¸ßÐÔÄÜÊý¾Ý¿âÉè¼ÆÓëÓÅ»¯
ÖªÃûijÐÅϢͨÐŹ«Ë¾ NoSQL»º´æÊý¾Ý¿â¼¼Êõ
±±¾© oracleÊý¾Ý¿âSQLÓÅ»¯
ÖйúÒÆ¶¯ IaaSÔÆÆ½Ì¨-Ö÷Á÷Êý¾Ý¿â¼°´æ´¢¼¼Êõ