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

1Ôª 10Ôª 50Ôª





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



  ÇóÖª ÎÄÕ ÎÄ¿â Lib ÊÓÆµ iPerson ¿Î³Ì ÈÏÖ¤ ×Éѯ ¹¤¾ß ½²×ù Modeler   Code  
»áÔ±   
 
   
 
 
     
   
 ¶©ÔÄ
  ¾èÖú
UberΪʲô´ÓPostgresÇ¨ÒÆµ½MySQL£¬¼õÉÙÆµ¿íÕ¼Óá¢ÄÚ´æÕ¼Óã¬Ìá¸ß²Ù×÷ЧÂÊ
 
×÷Õߣº EVAN KLITZKE À´Ô´£ºÍøÂç ·¢²¼ÓÚ 2016-9-30
  2928  次浏览      28
 

µ¼ÂÛ

UberµÄÔçÆÚ¼Ü¹¹ÓÉÒ»¸öµ¥Ìåºó¶ËÓ¦ÓóÌÐò¹¹³É£¬¸ÃÓ¦ÓÃÓÉPython±àд£¬PythonʹÓÃPostgresÒÔʵÏÖÊý¾Ý³Ö¾Ã»¯¡£×ÔÄÇʱÆð£¬Uber¼Ü¹¹ÒÑ·¢Éú¾Þ±ä£¬Öð²½×ª»¯ÎªÎ¢·þÎñģʽºÍеÄÊý¾Ýƽ̨¡£ÌرðÊÇÔÚ֮ǰһЩʹÓÃPostgresµÄ°¸ÀýÖУ¬ÏÖÔÚÔò¸ÄÓÃSchemaless£¨Ò»¸ö»ùÓÚMySQLµÄÈ«ÐÂÊý¾Ý¿â·ÖƬ£©¡£±¾ÎĽ«Ì½Ë÷PostgresµÄȱÏÝ£¬½âÊÍÇ¨ÒÆµ½MySQLµÄ»ù´¡ÉϹ¹½¨SchemalessºÍÆäËüºó¶Ë·þÎñµÄÔ­Òò¡£

PostgresµÄ¼Ü¹¹

PostgresÓкܶà¾ÖÏÞÐÔ£º

дÈë¼Ü¹¹µÍЧ

Êý¾Ý¸´ÖƵÍЧ

±íË𻵵ÄÎÊÌâ

Ôã¸âµÄ´Ó¿âMVCCÖ§³Ö

а汾¸üÐÂÄѶÈÉý¼¶

ÏÂÎĽ«·ÖÎöPostgresµÄ±í±íʾ·¨ºÍ´ÅÅÌÉϵÄË÷ÒýÊý¾Ý£¬Öصã¶Ô±ÈMySQLͨ¹ýÆäInnoDB´æ´¢ÒýÇæ³ÊÏÖÏàͬÊý¾ÝµÄ·½·¨£¬ÒÔ̽Ë÷ÉÏÊöȱÏÝ¡£×¢Ò⣺±¾ÎÄÉæ¼°µÄ·ÖÎöÖ÷Òª»ùÓھɰæPostgres 9.2ϵÁС£ ÖÚËùÖÜÖª£¬±¾ÎÄÂÛÊöµÄÄÚ²¿¼Ü¹¹ÔÚз¢²¼µÄPostgresÖÐûÓÐÌ«´ó±ä¸ü¡£ÊÂʵÉÏ£¬ÖÁÉÙ×ÔPostgres 8.3µÄ·¢²¼¿ªÊ¼£¨¾à½ñ½üÊ®Ä꣩£¬Postgres 9.2ÖдÅÅÌÉϱíʾ·¨µÄ»ù´¡Éè¼Æ¾ÍһֱûÓÐ×ö³öÏÔÖøµ÷Õû¡£

´ÅÅÌÉϵÄÊý¾Ý¸ñʽ

¹ØÏµÊý¾Ý¿â±ØÐëÖ´ÐÐÏÂÁйؼüÈÎÎñ£º

Ö§³Ö²åÈë/¸üÐÂ/ɾ³ý¹¦ÄÜ

Ö§³Öschema±ä¸ü¹¦ÄÜ

ʵÏÖÒ»¸ö¶à°æ±¾²¢·¢¿ØÖÆ£¨MVCC£©»úÖÆ£¬´Ùʹ²»Í¬µÄÁ¬½Ó¶ÔÆäËù´¦ÀíµÄÊý¾ÝÉú³ÉÒ»ÖÂÐÔµÄÊÂÎñÊÓͼ

˼¿¼ÆäËùÓÐÌØÐÔÈçºÎЭͬÔË×÷ÊÇÉè¼ÆÊý¾Ý¿âÔÚ´ÅÅÌÉϳÊÏÖÊý¾ÝµÄ»ù´¡¡£

PostgresµÄÒ»ÏîºËÐÄÉè¼ÆÊÇÐÐÊý¾Ý¹Ì¶¨¡£¸Ã¹Ì¶¨ÐÐÔÚPostgresÓÃÓïÖÐÓÖÃû¡°Ôª×飨tuple£©¡±¡£ÔÚPostgresÖУ¬Ôª×éÓÖͨ¹ýctid»ñµÃΨһ±êʶ¡£´Ó¸ÅÄîÉϽ²£¬ctid´ú±íÔª×éÔÚ´ÅÅÌÉϵÄλÖã¨ÀýÈçÎïÀí´ÅÅÌÆ«ÒÆ£©¡£¶à¸öctid¿ÉÄÜÄܹ»ÃèÊöÒ»¸öµ¥ÐУ¨ÀýÈç¶à¸öÐа汾ΪÁËMVCCµÄÄ¿µÄ¶ø´æÔÚʱ£¬»òÊǾɰ汾ÐÐδ¾­autovacuum½ø³Ì»ØÊÕʱ£©¡£Ò»×éÔª×éµÄ×éÖ¯¼¯ºÏ¹¹³É±í£¬±í±¾Éí°üº¬Ë÷Òý£¬Ë÷Òý¾­×éºÏ¹¹³ÉÊý¾Ý½á¹¹£¨Í¨³£ÊÇB-tree½á¹¹£©£¬´Ó¶ø½«Ë÷Òý×Ö¶ÎÓ³Éäµ½ctidµÄÓÐÐ§ÔØºÉ¡£

ͨ³£Çé¿öÏ£¬ÕâЩctidÊÇÃæÏòÓû§Í¸Ã÷µÄ£¬µ«Á˽âÆäÔËÐз½Ê½ÓÐÖúÓÚÀí½âPostgres±íÔÚ´ÅÅÌÉϵıí¼Ü¹¹¡£ÈôÒª²é¿´Ðеĵ±Ç°ctid£¬Ôò¿ÉÏòWHERE×Ó¾äÖеÄÀ¸Ä¿ÁбíÖÐÌí¼Ó¡°ctid¡±£º

uber@[local] uber=> SELECT ctid, * FROM my_table LIMIT 1;

-[ RECORD 1 ]--------+------------------------------

ctid | (0,1)

...other fields here...

ΪÇó²¼¾Öϸ½Ú£¬ÏÈÒÔÒ»¸ö¼òµ¥µÄÓû§±íΪÀý¡£UberÕë¶Ôÿ¸öÓû§ÉèÖÃÁË×Ô¶¯µÝÔöµÄÓû§IDÖ÷¼ü¡¢Óû§ÐÕÃûºÍ³öÉúÄê·Ý¡£Í¬Ê±Uber»¹ÉèÖÃÁËÒ»¸ö»ùÓÚÓû§È«Ãû£¨°üÀ¨ÃûºÍÐÕ£©µÄ¸´ºÏ¶þ¼¶Ë÷Òý£¬ºÍÁíÒ»¸ö»ùÓÚÓû§³öÉúÄê·ÝµÄ¶þ¼¶Ë÷Òý¡£ÓÃÒÔ´´½¨¸Ã±íµÄDDLÈçÏ£º

CREATE TABLE users (

id SERIAL,

first TEXT,

last TEXT,

birth_year INTEGER,

PRIMARY KEY (id)

);
CREATE INDEX ix_users_first_last ON users (first, last);
CREATE INDEX ix_users_birth_year ON users (birth_year);

×¢Òâ¸Ã¶¨ÒåÖеÄÈý¸öË÷Òý£ºÖ÷¼üË÷ÒýºÍÁ½¸ö¶þ¼¶Ë÷Òý¡£

ΪÇóÀýÖ¤£¬½«ÒÔÏÂÃæµÄ±í¸ñÊý¾ÝÕ¹¿ªÂÛÊö£¬±íÖÐÊý¾Ý¾ùÓÉÀúÊ·ÉÏÆÄ¾ßÓ°ÏìÁ¦µÄÊýѧ¼Ò¹¹³É£º

ÈçǰËùÊö£¬±íÖÐÿһÐÐÒþº¬Ò»¸öΨһÇÒ²»¹«¿ªµÄctid¡£Òò´Ë£¬±íµÄÄÚ²¿±íʾÈçÏ£º

ÉèÖÃÖ÷¼üË÷Òý£¨Ó³ÉäIDµ½ctid£©£º

B-tree½á¹¹µÄÉèÖûùÓÚid×ֶΣ¬ÇÒÆäÿ¸ö½Úµã¶¼±£´æctidÖµ¡£ÔÚÕâ¸ö°¸ÀýÖÐÐèҪעÒâµÄÊÇ£¬ÓÉÓÚʹÓÃ×Ô¶¯µÝÔöid£¬B-treeÖеÄ×Ö¶Î˳ÐòÓÐʱ»áºÍ±íÖÐ˳ÐòÏàͬ£¬µ«ÊÇÒ²²»Ò»¶¨Èç´Ë¡£

¶þ¼¶Ë÷Òý±Ë´Ë¶¼ºÜÏàËÆ£»Ö÷Òª²îÒìÔÚÓÚ×ֶδ洢˳Ðò£¬¶ø×Ö¶ÎÔÚB-treeÖбØÐëÒÔ×Öµä˳ÐòÅŲ¼¡££¨first£¬last£©Ë÷Òý´ÓÃû¿ªÊ¼°´×Öĸ±í˳Ðò×ÔÉ϶øÏÂÅÅÁС£

ͬÑù£¬birth_year£¨³öÉúÄê·Ý£©¾Û¼¯Ë÷ÒýÒÔÉýÐòÅÅÁУº

×ÛÉÏËùÊö£¬²»Í¬ÓÚ×Ô¶¯µÝÔöÖ÷¼üµÄ°¸Àý£¬ÔÚÉÏÃæµÄÇ龳ϣ¬¸÷¸ö¶þ¼¶Ë÷ÒýÖеÄctid×ֶζ¼²»Êǰ´×Öĸ±í˳ÐòÉýÐòÅŲ¼µÄ¡£

¼ÙÉèÐèÒª¸üÐÂÒ»Ìõ±í¼Ç¼£¬±ÈÈ罫al-Khw¨¡rizm¨©µÄ³öÉúÄê·Ý×ֶθüÐÂΪÁíÒ»¸öÔ¤¹ÀÖµ770CE¡£ÈçǰËùÊö£¬ÐÐÔª×éÊǹ̶¨µÄ£¬Òò´Ë£¬ÎªÁ˸üмǼ£¬ÐèÒªÏò±íÖÐÌí¼ÓÒ»¸öÐÂÔª×é¡£¸ÃÐÂÔª×éÓÐÒ»¸öеķǹ«¿ªctid£¬³ÆÖ®ÎªI¡£PostgresÐèÒªÄܹ»Çø·ÖIÉϵÄÐÂÔª×éºÍDÉϵľÉÔª×é¡£ÔÚÄÚ²¿£¬Postgres½«Ò»¸ö°æ±¾×ֶκÍÖ¸Ïòǰһ¸öÔª×飨Èç¹ûÓеϰ£©µÄÖ¸Õë´æÓÚ¸÷¸öÔª×é¡£¾Ý´Ë£¬±íµÄнṹÈçÏ£º

Ö»Òªal-Khw¨¡rizm¨©´æÔÚÁ½¸öÐа汾£¬ÔòË÷Òý±ØÐë°üº¬ÕâÁ½¸öÐеÄÌõÄ¿¡£ÎªÇó¼ò½à£¬Uber´Ë´¦É¾³ýÁËÖ÷¼üË÷Òý£¬Ö»ÏÔʾ¶þ¼¶Ë÷Òý£º

´Ë´¦½«¾É°æÏÔʾΪºìÉ«£¬Ð°æÎªÂÌÉ«¡£ÔÚÄÚ²¿£¬Postgreͨ¹ýÁíÒ»¸ö×ֶα£´æÐа汾£¬ÒÔÅж¨ÄÄÒ»¸öÊÇ×îÐÂÔª×é¡£¸ÃÐÂÔö×ֶΰïÖúÊý¾Ý¿â¾ö¶¨ÈÃÄÄÒ»¸öÐÐÔª×é·þÎñÓÚÒ»¸öÊÂÎñ£¬¸ÃÊÂÎñ¿ÉÄܲ»±»ÔÊÐí²é¿´×îÐÂÐа汾¡£

PostgresÏ£¬Ö÷Ë÷ÒýºÍ¶þ¼¶Ë÷Òý¶¼Ö±Ö¸´ÅÅÌÉϵÄÔª×éÆ«ÒÆ¡£ÈôÒ»¸öÔª×éµÄλÖ÷¢Éú¸Ä±ä£¬Ôò±ØÐë¸üÐÂÈ«²¿Ë÷Òý¡£

¸´ÖÆ

ÔÚ±íÖвåÈëÐÂÐÐʱ£¬ÈôÖ§³ÖÁ÷¸´ÖÆ£¬PostgresÔòÐè¶ÔÆä½øÐи´ÖÆ¡£ÎªÇó¹ÊÕÏÐÞ¸´£¬Êý¾Ý¿âÒÑÆôÓÃÒ»¸öԤдʽÈÕÖ¾£¨WAL£©£¬ÒÔʵÏÖÁ½½×¶ÎÌá½»¡£¼´Ê¹²»Ö§³ÖÁ÷¸´ÖÆ£¬Êý¾Ý¿âÒ²±ØÐëÆôÓÃWAL£¬ÒòΪWALLÖ§³ÖACIDÖеÄÔ­×ÓÐԺͳ־ÃÐÔÁ½·½Ãæ¡£

ÈôÊý¾Ý¿â·¢ÉúÍ»·¢Òì³££¨±ÈÈçͻȻ¶Ïµç£©£¬¿Éͨ¹ý¿¼ÂÇ·¢ÉúµÄÊÂÇéÀ´Àí½âWAL¡£Êý¾Ý¿â¼Æ»®¶Ô±íºÍË÷ÒýÔÚ´ÅÅÌÉϵÄÄÚÈÝ×ö³ö¸ü¸Ä£¬¶øWAL±íʾÕâЩ¸ü¸ÄµÄ·ÖÀà»ã×Ü¡£PostgresµÄºǫ́³ÌÐò³õ´ÎÆô¶¯Ê±£¬½ø³Ì½«¸Ã·ÖÀà»ã×ÜÄÚµÄÊý¾ÝºÍ´ÅÅÌÉϵÄʵ¼ÊÊý¾Ý½øÐжԱȡ£Èô»ã×ÜÖаüº¬´ÅÅÌÉÏδ·´Ó¦µÄÊý¾Ý£¬ÔòÊý¾Ý¿âÐÞÕýÔª×é»òË÷ÒýÊý¾Ý£¬ÒÔ·´Ó¦WALԤдµÄÊý¾Ý¡£Ëæºó¹ö»Ø²¿·ÖÊý¾Ý£¬ÕâЩÊý¾Ý³öÏÖÓÚWAL£¬µ«À´×Ô²¿·ÖÓ¦ÓÃÊÂÎñ£¨Ö¸Î´Ìá½»ÊÂÎñ£©¡£

Postgres½«Ö÷Êý¾Ý¿âÉϵÄWAL·¢ËÍÖÁ´Ó¿â£¬ÒÔʵÏÖÁ÷¸´ÖÆ¡£Ã¿Ò»¸ö´Ó¿â¶¼ÔÚÒÔ¹ÊÕÏÐÞ¸´µÄ·½Ê½¸ßЧÔËÐУ¬ÒÔÓ¦¶Ô¹ÊÕϺóÆô¶¯µÄ·½Ê½£¬²»¶ÏʵʩWAL¸üС£Á÷¸´ÖƺÍʵ¼Ê¹ÊÕÏÐÞ¸´Ö®¼äµÄΨһ²»Í¬ÔÚÓÚ£ºÖ´ÐÐÁ÷WALʱ£¬´¦ÓÚ¡°Èȱ¸·Ý¡±Ä£Ê½µÄ´Ó¿âÖ§³Ö¶ÁÈ¡²éѯ£»¶øÊµ¼Ê´¦ÓÚ¹ÊÕÏÐÞ¸´Ä£Ê½µÄPostgresÊý¾Ý¿âÍùÍù²»Ö§³Ö²éѯ£¬Ö±µ½Êý¾Ý¿âʵÀýÍê³É±ÀÀ£»Ö¸´½ø³Ì¡£

ÓÉÓÚWALÊÇרΪ±ÀÀ£»Ö¸´¶øÉè¼ÆµÄ£¬¹Ê°üº¬´ÅÅÌÉϸüеĵײãÐÅÏ¢¡£¶øWALµÄÄÚÈÝλÓÚÐÐÔª×éºÍÆä´ÅÅÌÆ«ÒÆ£¨ÀýÈçÐÐctid£©ÔÚ´ÅÅÌÉϵÄʵ¼Ê±íʾ²ã¡£ÈôÔÚ´Ó¿âÍêȫͬ²½Ê±ÔÝÍ£PostgresµÄÖ÷Êý¾Ý¿âºÍ´Ó¿â£¬Ôò´Ó¿â´ÅÅÌÉϵÄʵ¼ÊÄÚÈÝÇ¡ºÃºÍÖ÷Êý¾Ý¿âÉϵÄÄÚÈÝÍêȫƥÅä¡£Òò´Ë£¬ÈôÓëÖ÷Êý¾Ý¿âÁ¬½Ó³¬Ê±£¬rsyncÕâÀ๤¾ßÄܹ»ÐÞ¸´Ë𻵸±±¾¡£

PostgresÉè¼ÆµÄ¿Ó

ÔÚUberµÄÓ¦Óùý³ÌÖУ¬PostgresµÄÉè¼Æµ¼ÖµÍЧÎÊÌâµÄͬʱ£¬»¹¸øUberÊý¾ÝÔì³ÉÕϰ­¡£

дÈë·Å´ó

ÔÚÆäËüÇé¿öÏÂPostgresÉè¼ÆµÄÊ×ÒªÎÊÌâÊÇдÈë·Å´ó¡£Ð´Èë·Å´óͨ³£´úÖ¸Êý¾ÝдÈëSSD´ÅÅ̵ÄÎÊÌ⣺һ¸öСµÄÂß¼­¸üУ¨Ð´È뼸¸ö×Ö½Ú£¨byte£©£©±»·Å´óÁË£¬´Ó¶øÔö¼ÓÁ˸üÐÂתÈëÎïÀí²ãµÄ´ú¼Û¡£PostgresÒ²³öÏÖÁËÏàͬµÄÎÊÌâ¡£ÔÚÇ°ÃæµÄÀý×ÓÖУ¬Èô¶Ôal-Khw¨¡rizm¨©µÄ³öÉúÄê·Ý½øÐÐСµÄÂß¼­¸üУ¬ÔòÖÁÉÙÐèÒªÕ¹¿ªËÄÏîÎïÀí¸üУº

Ïò±í¿Õ¼äдÈëеÄÐÐÔª×飻

¸üÐÂÖ÷¼üË÷ÒýÒÔÏòÐÂÔª×éÖмÓÈëÒ»Ìõ¼Ç¼£»

¸üУ¨first£¬last£©Ë÷ÒýÒÔÏòÐÂÔª×éÖмÓÈëÒ»Ìõ¼Ç¼£»

¸üÐÂbirth_yearË÷ÒýÒÔÏòÐÂÔª×éÖмÓÈëÒ»Ìõ¼Ç¼¡£

ʵ¼ÊÉÏ£¬ÕâËÄÏî¸üнö·´Ó³ÁËÖ÷Òª±í¿Õ¼äµÄдÈ룻ÆäÖÐÿһÏîдÈë¶¼ÐèÒª·´Ó³ÓÚWAL£¬Òò´Ë´ÅÅÌÉϵÄ×ÜдÈëÁ¿¸ü´óÁË¡£

´Ë´¦ÖµµÃ×¢ÒâµÄÊǵڶþºÍµÚÈýÏî¸üС£¸üÐÂal-Khw¨¡rizm¨©µÄ³öÉúÄê·Ýʱ£¬Êµ¼ÊÉϲ¢·Ç¸ü¸ÄÖ÷¼ü£¬Ò²²»ÊǸıäÆäÃûºÍÐÕ¡£È»¶ø£¬ÕâЩË÷ÒýÈÔÒªÒÀ¾ÝÊý¾Ý¿âÖÐÐмǼд´½¨µÄÐÐÔª×é½øÐиüС£Õë¶ÔÓµÓдóÁ¿¶þ¼¶Ë÷ÒýµÄ±í£¬ÕâЩ¶àÓàµÄ²½Ö轫µ¼ÖµÍЧ¡£ÀýÈ磬ÏÖÓÐÒ»±í£¬ÆäÉ϶¨ÒåÁËÊ®¶þÌõË÷Òý£¬ÔòÆä¸üÐÂÖÁÒ»¸ö½öº­¸ÇÒ»ÌõË÷ÒýµÄ×Ö¶ÎÒ²±ØÐëҪתÖÁÈ«²¿Ê®¶þÌõË÷ÒýÒÔ·´Ó¦ÐÂÐеÄctid¡£

¸´ÖÆ

´Ë´¦µÄдÈë·Å´óÎÊÌâ×ÔȻתÖÁ¸´ÖƲ㣬ÒòΪ¸´ÖÆ·¢ÉúÔÚ´ÅÅÌÉϵĸü¸Ä²ã¼¶¡£²»¸´ÖÆÐ¡µÄÂß¼­¼Ç¼£¨Èç¡°½«ctid D¾à½ñµÄ³öÉúÄê·Ý¸ü¸ÄΪ770¡±£©£¬Êý¾Ý¿âת¶øÐ´³öÉÏÊöËÄÏî²Ù×÷µÄWAL¼Ç¼£¬¸Ã¼Ç¼ͨ¹ýÍøÂçͬ²½¡£Òò´Ë£¬Ð´ÈëÀ©´óÎÊÌâҲת»¯Îª¸´ÖÆÀ©´óÎÊÌ⣬ÇÒPostgres¸´ÖÆÊý¾ÝÁ÷ѸËÙ±äµÃ¼«Îª·±Ëö£¬´Ó¶ø¿ÉÄÜ»á´óÁ¿Õ¼ÓÃÆµ¿í¡£

ÈôPostgres¸´ÖƽöÔÚÒ»¸öÊý¾ÝÖÐÐÄÄÚ·¢Éú£¬Ôò¸´ÖÆÆµ¿í¿ÉÄܲ»»á¹¹³ÉÎÊÌâ¡£ÏÖ´úÍøÂçÉ豸ºÍ½»»»Æ÷¿ÉÖ§³Ö´óÁ¿Æµ¿í£¬ÇҺܶàÖ÷»úÌṩÉÌÌṩÃâ·Ñ»òÁ®¼ÛµÄÄÚ²¿Êý¾ÝÖÐÐÄÆµ¿í¡£È»¶ø£¬Èô¸´ÖƱØÐë·¢ÉúÓÚÁ½¸öÊý¾ÝÖÐÐÄÖ®¼ä£¬ÎÊÌâÔò»áѸËÙÀ©´ó¡£ÀýÈ磬Uber×î³õʹÓÃÎ÷º£°¶Ö÷»úÍйܿռäµÄÎïÀí·þÎñÆ÷¡£³öÓÚ±ÀÀ£»Ö¸´µÄÄ¿µÄ£¬Ïò¶þ¼¶¶«º£°¶Ö÷»úÍйܿռäÌí¼Ó·þÎñÆ÷¡£ÔÚ¸ÃÉè¼ÆÖУ¬Î÷²¿Êý¾ÝÖÐÐÄÓÐÒ»¸öÖ÷Êý¾Ý¿âPostgresʵÀý£¨ºÍ´ÓÊý¾Ý¿â£©£¬¶«²¿Êý¾ÝÖÐÐÄÓÐÒ»¸ö¸±±¾¼¯¡£

¼¶Áª¸´Öƽ«ÄÚ²¿Êý¾ÝÖÐÐÄÆµ¿íÐèÇóÏÞÖÆÔÚÖ÷Êý¾Ý¿âºÍµ¥Ò»¸±±¾¼äµÄËùÐèµÄÁ¿£¬¼´Ê¹¶þ¼¶Êý¾ÝÖÐÐÄÓкܶั±¾¡£È»¶ø£¬Postgres¸±±¾Ð­ÒéÈßÓàÈÔÈ»»áµ¼ÖÂʹÓôóÁ¿Ë÷ÒýµÄÊý¾Ý¿â²úÉú¾ÞÁ¿Êý¾Ý¡£¹ºÂò¸ßƵ¿í¿ç¹úÁ¬½Ó»°·ÑºÜ¸ß£¬¶øÇÒ¼´Ê¹×ʽð²»ÊÇÎÊÌ⣬ҲºÜÄÑ»ñµÃÒ»¸öºÍ±¾µØÄÚ²¿Á¬½ÓÒ»ÑùµÄ¿ç¹úÍøÂçÁ¬½Ó¡£Í¬Ê±Æµ¿íÎÊÌâÒ²´øÀ´ÁËWALµµ°¸ÎÊÌâ¡£³ýÁËÒª½«ËùÓÐWAL¸üдÓÎ÷º£°¶·¢Ë͵½¶«º£°¶£¬»¹Òª½«ËùÓÐWAL´æÈëÒ»¸öÎļþ´æ´¢Æ÷web·þÎñ£¬Õâ²»½öÊÇΪ¹ÊÕÏ·¢ÉúºóµÄÊý¾Ý¿â»Ö¸´Ìṩ¶îÍâ±£ÕÏ£¬Í¬Ê±´æ´¢WALÒ²´ÓÊý¾Ý¿â¿ìÕÕÖÐÌáȡи±±¾¡£ÔÚÔçÆÚͨП߷å½×¶Î£¬´æ´¢web·þÎñµÄƵ¿íËٶȲ»¹»¿ì£¬ÒÔÖÁÓÚ²»×ãÒÔ¸ÏÉÏWAL±»Ð´ÈëµÄËÙ¶È¡£

Êý¾ÝËð»µ

ÔÚÒ»´Î³£¹æÖ÷Êý¾Ý¿âÀ©Èݹý³ÌÖУ¬UberÔâÓöÁËPostgres 9.2µÄbug¡£´Ó¿â°´Ê±¼ä˳ÐòÇл»³ö´í£¬µ¼Ö²¿·ÖWAL¼Ç¼ÎóÓá£ÓÉÓÚÕâ¸öbug£¬²¿·ÖÓ¦±»°æ±¾¿ØÖÆ»úÖÆ±ê¼ÇΪ·Ç»îÔ¾µÄ¼Ç¼£¬Êµ¼ÊÉÏδ±»±ê¼ÇΪ·Ç»îÔ¾¡£

ÏÂÃæµÄ²éѯ²ûÊÍÁ˸ÃbugÊÇÈçºÎÓ°ÏìÓû§±íµÄ£º

SELECT * FROM users WHERE id = 4;

¸Ã²éѯ·µ»ØÁ½Ìõ¼Ç¼£º³õʼal-Khw¨¡rizm¨©ÐеÄ780 CE³öÉúÄê·Ý£¬¼ÓÉÏÐÂal-Khw¨¡rizm¨©ÐеÄ770 CE³öÉúÄê·Ý¡£ÈôÏëÏòWHEREÁбíÖÐÌí¼Óctid£¬ÔòÁ½¸ö·µ»Ø¼Ç¼½«¾ßÓв»Í¬µÄctidÖµ£¬»á²úÉúÁ½¸ö½ØÈ»²»Í¬µÄÐÐÔª×é¡£

ÊÜһЩԭÒòÓ°Ï죬Õâ¸öÎÊÌ⼫Ϊ¼¬ÊÖ¡£Ê×ÏÈ£¬ºÜÄÑÈ·ÈÏÓжàÉÙÐлáÊܵ½Ó°Ï죻Êý¾Ý¿â·µ»ØµÄÖØ¸´½á¹ûµ¼Ö´óÁ¿Ó¦ÓÃÂß¼­³ö´í¡£Uber×îÖÕÌí¼Ó·ÀÓùÐÔ±à³ÌÓï¾äÒÔ¼ì²âÓÐÕâÀàÎÊÌâµÄ±íµÄÇé¾³¡£ÒòΪ¸ÃbugÓ°ÏìËùÓзþÎñÆ÷£¬Òò´Ë´Ó¿âʵÀý²»Í¬Ôò±ÀÀ£Ðв»Í¬£¬Ò²¾ÍÊÇ˵ÔÚͬһ¸ö¸±±¾ÉÏXÐпÉÄÜÊÇ»µµÄ£¬¶øYÐÐÓÖÊǺõ쬵«ÁíÒ»¸ö¸±±¾µÄXÐпÉÄÜÊǺõ쬶øYÐÐÈ´ÊÇ»µµÄ¡£Êµ¼ÊÉÏ£¬²¢²»ÄÜÈ·¶¨°üº¬±ÀÀ£Êý¾ÝµÄ¸±±¾Êý£¬ÒÔ¼°¸ÃÎÊÌâÊÇ·ñÓ°Ïìµ½ÁËÖ÷Êý¾Ý¿â¡£

×ÛÉÏËùÊö£¬¸ÃÎÊÌâ½öÌåÏÖÔÚÿ¸öÊý¾Ý¿âÖеöÐÐÉÏ£¬µ«ÊÇÈ´ÆÄÁîÈËÓÇÂÇ£¬ÒòΪ¸´ÖÆ·¢ÉúÔÚÎïÀí²ã£¬ËùÒÔ×îÖÕ¿ÉÄܵ¼ÖÂÊý¾Ý¿âË÷ÒýÍêÈ«±ÀÀ£¡£B-treeµÄÒ»¸öÖØÒª·½Ãæ¾ÍÊDZØÐ붨ÆÚ¶ÔÆä½øÐÐÖØÐÂÆ½ºâ£¬ÇҸòÙ×÷»á³¹µ×¸Ä±ätreeµÄ½á¹¹£¬sub-trees±»ÒÆÍùеĴÅÅÌλÖá£Èô´íÎóÊý¾Ý±»×ªÒÆ£¬¿ÉÄܻᵼÖÂtreeµÄ´ó²¿·ÖÍêȫʧЧ¡£

×îÖÕ£¬UberÄܹ»×·²¶Êµ¼Êbug£¬²¢ÓÃËüÀ´È·¶¨ÐÂÉý¼¶µÄÖ÷Êý¾Ý¿â²»°üº¬ÈκÎÊÜËðÐС£Í¨¹ýÖØÐÂͬ²½È«²¿Ö÷Êý¾Ý¿âµÄпìÕÕ£¬ÒÔ½â¾ö¸±±¾±ÀÀ£ÎÊÌ⣬Õâ¸ö½ø³ÌºÜÀ§ÄÑ£»UberÒ»´ÎÖ»ÄÜ´Ó¸ºÔؾùºâ³ØÖÐÌáÈ¡ÓÐÏÞÁ¿µÄ¸±±¾¡£

ÔâÓöµÄÎÊÌâ½öÓ°ÏìÁËPostgres 9.2µÄÌØ¶¨·¢²¼£¬²¢ÇÒÒѾ­±»½â¾öºÜ³¤Ê±¼äÁË¡£µ«ÈÔÈ»»áµ£ÐÄÕâÀàbug»áÔٴη¢Éú¡£Ò»µ©·¢ÏÖÕâÖÖÐÔÖʵÄbug£¬Ò»¸öаæµÄPostgres¿ÉÒÔËæÊ±·¢²¼£¬¶øÇÒÓÉÓÚ¸´ÖƵŤ×÷·½Ê½£¬¸ÃÎÊÌâ¿ÉÄÜ»áͬ²½ÖÁ¸´ÖƲãµÄËùÓÐÊý¾Ý¿â¡£

´Ó¿âMVCC

PostgresûÓÐÕæÕýµÄ¸´ÖÆMVCCÖ§³Ö¡£´Ó¿âÔËÓÃWAL¸üÐÂÕâÒ»µãµ¼ÖÂÆä¾ßÓÐÒ»¸ö´ÅÅÌÉÏÊý¾ÝµÄ¸±±¾£¬ÔÚÈκθø¶¨µÄʱ¼äµã¶¼ºÍÖ÷Êý¾Ý¿âÏàͬ¡£

PostgresÐèÒª±£³ÖMVCC¾ÉÐа汾µÄ¸±±¾¡£ÈôÁ÷¸´Öưüº¬Ò»¸ö¿ª·ÅÊÂÎñ£¬ÔòÔÚÓ°Ï쵽ͨ¹ý¸ÃÊÂÎñ±£³Ö¿ª·ÅµÄÐÐʱ£¬Êý¾Ý¿â¸üÐÂ»á±»Ëø¶¨¡£ÔÚÕâÖÖÇ龳ϣ¬PostgresÖÐÖ¹WALÓ¦ÓÃỊ̈߳¬Ö±µ½ÊÂÎñÖÕÖ¹¡£ÓÉÓÚ´Ó¿â½ÏÖ®Ö÷Êý¾Ý¿â¿ÉÄÜ»áÑÏÖØÖͺó£¬ËùÒÔÈôÊÂÎñºÄʱ¹ý³¤Ôò»á²úÉúÎÊÌâ¡£Òò´Ë£¬PostgresÔÚÕâÀàÇé¾°ÏÂÉèÖó¬Ê±£ºÈôÒ»ÏîÊÂÎñËø¶¨WALÓ¦ÓÃÒ»¶Îʱ¼ä£¬ÔòPostgres½«kill¸ÃÊÂÎñ¡£

Õâ¸öÉè¼ÆÒâζ×Å´Ó¿âÕý³£»á±ÈÖ÷Êý¾Ý¿âÑÓ³Ù¼¸Ã룬Òò´ËºÜÈÝÒ×±àд´úÂ룬´Ó¶øµ¼ÖÂɱËÀÊÂÎñ¡£Ò»Ð©Ó¦Óÿª·¢Õß±àдµÄ´úÂëÖУ¬ÊÂÎñµÄÆðʼµã½ÏΪģºý£¬¶ÔÓÚËûÃǶøÑÔ£¬Õâ¸öÎÊÌâµÄ¿ÉÄܲ»ÄÇôÃ÷ÏÔ¡£ÀýÈ磬¼ÙÉèÒ»¸ö¿ª·¢ÕßÓÐһЩ´úÂ룬¶øÕâЩ´úÂë±ØÐëÏòÓû§Óʼþ·¢ËÍÊվݡ£»ùÓÚÆä±àд·½Ê½£¬¸Ã´úÂë°µº¬Ò»¸öÊý¾Ý¿âÊÂÎñ£¬ÕâÒ»ÊÂÎñ±£³Ö¿ª·Å״̬£¬Ö±ÖÁÓʼþ·¢ËÍÍê³É¡£ËäȻִÐÐÎÞ¹Ø×èÈûI/Oʱ£¬´úÂë°üº¬¿ª·ÅʽÊý¾Ý¿âÊÂÎñ²¢²»Í×µ±£¬µ«ÊÂʵÊÇ´ó¶àÊý¹¤³Ìʦ¶¼²»ÊÇÊý¾Ý¿âר¼Ò£¬ËùÒÔÍùÍù¿ÉÄܲ»ÄÜÀí½âÕâ¸öÎÊÌâ£¬ÌØ±ðÊÇÔÚʹÓÃORMµÄʱºò£¬ÀàËÆÓÚ¿ª·ÅÊÂÎñ£¬Ëü»áÄ£ºýµ×²ãϸ½Ú¡£

Postgres¸üÐÂ

ÒòΪ¸´ÖƼǼÔÚÎïÀí²ãÖ´ÐУ¬ËùÒÔ²»¿ÉÄܸ´ÖÆPostgres²»Í¬Í¨Óð汾¼äµÄÊý¾Ý¡£Ò»¸öÔËÐÐPostgres 9.3µÄÖ÷Êý¾Ý¿â£¬ÎÞ·¨¸´ÖÆÒ»¸öÔËÐÐPostgres 9.2µÄ´Ó¿â£»£¨·´Ö®ÒàÈ»£©Ò»¸öÔËÐÐPostgres 9.2µÄÖ÷Êý¾Ý¿âÒ²ÎÞ·¨¸´ÖÆÒ»¸öÔËÐÐPostgres 9.3µÄ´Ó¿â¡£

ÔÚ´ÓÒ»¸öPostgres GA¸üе½ÁíÒ»¸öµÄ¹ý³ÌÖУ¬×ñÑ­ÒÔϲ½Ö裺

¹Ø±ÕÖ÷Êý¾Ý¿â£»

ÔÚÖ÷Êý¾Ý¿âÉÏÔËÐС¯pg_upgrade¡¯Ö¸ÁËü½«ÔÚÊʵ±Î»ÖøüÐÂÖ÷Êý¾Ý¿âÊý¾Ý¡£Õâ¶ÔÓÚÒ»¸ö´óÐÍÊý¾Ý¿â¶øÑÔ£¬ÍùÍùÒª»¨ºÃ¶à¸öСʱ£¬ÇÒÔڸýø³Ì½øÐеĹý³ÌÖУ¬²»Ö§³ÖÈκÎͨÐÅ£»

ÔÙ´ÎÆô¶¯Ö÷Êý¾Ý¿â£»

´´½¨Ö÷Êý¾Ý¿âпìÕÕ¡£¸Ã²½ÖèÍêÈ«¸´ÖÆÖ÷Êý¾Ý¿âÄÚµÄËùÓÐÊý¾Ý£¬Òò´ËÒ»¸ö´óÐÍÊý¾Ý¿âÔÚ´ËÒ²Òª»¨·ÑÊý¸öСʱ£»

ÇåÀíÿ¸ö´Ó¿â£¬ÐÞ¸´Ö÷Êý¾Ý¿âµ½´Ó¿âµÄпìÕÕ£»

»Ö¸´Ã¿¸ö´Ó¿âÖÁ¸´ÖƲ㣬Óë´Ëͬʱ£¬µÈ´ý´Ó¿âÍêÈ«»ñÈ¡Ö÷Êý¾Ý¿âµÄÈ«²¿¸üС£

UberÍŶӴÓPostgres 9.1¿ªÊ¼£¬³É¹¦ÊµÏÖÁ˸üÐÂÖÁPostgres 9.2µÄ½ø³Ì¡£È»¶ø£¬¸Ã½ø³ÌºÄʱ¹ý³¤£¬Òò¶øÎÞ·¨Ö§³ÖÔÙ´ÎÖ´ÐС£Postgres 9.3ÍÆ³öºó£¬UberµÄ·¢Õ¹´ÙʹÆäÊý¾Ý¼¯´ó·ùÔö£¬Òò´Ë¸üйý³Ì½«ºÄʱ¸ü¾Ã¡£³öÓÚÕâ¸öÔ­Òò£¬Ö®Ç°ÒÅÁôµÄPostgresʵÀýÖ±ÖÁ½ñÈÕÈÔÔÚÔËÐÐPostgres 9.2£¬¾¡¹ÜĿǰPostgres GAÒѾ­·¢²¼µ½9.5°æ±¾ÁË¡£

ÈôÔËÐÐPostgres 9.4»òÊǸüеİ汾£¬Ôò¿ÉÒÔÔËÓÃpglogicalÕâÑùµÄ¹¦ÄÜ£¬¸Ã¹¦ÄÜʵÏÖÁËPostgresµÄÒ»¸öÂß¼­¸´ÖƲ㡣ÔËÓÃpglogical£¬Ôò¿É¸´ÖƲ»Í¬Postgres°æ±¾¼äµÄÊý¾Ý£¬ÕâÒâζ×Å¿ÉÒÔÔÚÎÞÐèÔâÓöÖØ´ó¹ÊÕϵÄǰÌáÏ£¬½øÐиüвÙ×÷£¨È罫Postgres 9.4¸üе½9.5°æ±¾£©¡£Í¬Ê±£¬¸ÃÐÔÄÜÒ²ÈÔÈ»´æÔÚÎÊÌ⣬ÒòΪËü²¢Î´¼¯³Éµ½PostgresµÄÖ÷¸ÉÊ÷£¬ÇÒpglogical±¾ÉíÒ²ÈÔ·ÇÔËÐоɰæPostgresµÄÑ¡Ïî¡£

MySQLµÄ¼Ü¹¹

³ýÁ˽éÉÜPostgresµÄÏÞÖÆ£¬»¹Òª²ûÊöһ϶ÔÓÚ½üÆÚµÄUber Engineering´æ´¢ÏîÄ¿£¨ÈçSchemaless£©¶øÑÔ£¬ÒÔMySQLΪ¹¤¾ßµÄÖØÒªÒâÒå¡£»á·¢ÏÖÔںܶàÇé¿öÏ£¬MySQL¸üΪÊÊÓá£ÎªÁËÁ˽âÆäÖеIJîÒ죬UberÍŶӼì²âÁËMySQLµÄ¼Ü¹¹ÒÔ¼°ÆäÓëPostgres¼Ü¹¹¼äµÄ²î±ð¡£ÍŶÓÌØ±ð·ÖÎöÁËMySQLÊÇÈçºÎÓëInnoDBµÄ´æ´¢ÒýÇæÏàºÏ×÷µÄ¡£Õë¶ÔUber£¬ÆäÍŶӲ»½öʹÓõ½ÁËInnoDB£¬µ«Ëü¿ÉÄÜÊÇ×îÊÜ»¶Ó­µÄMySQL´æ´¢ÒýÇæ¡£

InnoDB´ÅÅÌÉϵıíʾ·¨

ÀàËÆÓÚPostgres£¬InnoDBÖ§³Ö¸ß¼¶¹¦ÄÜ£¬ÈçMVCCºÍ¿É±äÊý¾Ý¡£¹ØÓÚInnoDBÔÚ´ÅÅÌÉϸñʽµÄÏ꾡ÌÖÂÛ£¬²»ÊôÓÚ±¾Îĵķ¶³ë£»±¾ÎĽ«×ÅÑÛÓÚÆäÓëPostgres¶þÕß¼äµÄºËÐIJîÒì¡£

ÆäÖÐ×îÖØÒªµÄ¼Ü¹¹Çø±ðÔÚÓÚ£ºPostgresÖ±½Ó½«Ë÷Òý¼Ç¼ӳÉäµ½´ÅÅÌÉϵÄλÖ㬶øInnoDBÔò±£ÓÐÒ»¸ö¶þ¼¶½á¹¹¡£InnoDB¶þ¼¶Ë÷Òý¼Ç¼ָÕëÖ¸ÏòÖ÷¼üÖµ£¬¶ø²»ÊÇÏñPostgresÖÐctidµÄ²Ù×÷£¬Ö¸ÕëÖ¸Ïò´ÅÅÌÉÏÐеÄλÖá£Èç´Ë£¬MySQLÖеĶþ¼¶Ë÷ÒýÁ¬½ÓË÷Òý¼üºÍÖ÷¼ü¡£

ÈôÒª»ùÓÚ£¨first£¬last£©Ë÷ÒýÖ´ÐÐÒ»ÏîË÷Òý²éÕÒ£¬Êµ¼ÊÉÏÐèÒªÍê³ÉÁ½Ïî²éÕÒ¡£µÚÒ»ÏîËÑË÷±í²¢ÕÒ³öÒ»Ìõ¼Ç¼µÄÖ÷¼ü¡£Ò»µ©ÕÒµ½Ö÷¼ü£¬µÚ¶þÏî²éѯÆô¶¯£¬¿ªÊ¼ËÑË÷Ö÷¼üË÷ÒýÒÔÕÒ³ö´ÅÅÌÉÏÐеÄλÖá£

¸ÃÉè¼Æ±íÃ÷ÔÚÖ´ÐеڶþÏî¼ü²éÕÒµÄʱºò£¬¶ÔPostgresÔì³ÉÇá΢µÄ¸ºÃæÓ°Ï죬ÒòΪInnoDBÒª½øÐÐÁ½´ÎË÷Òý²éÕÒ£¬¶øPostgres½öÐèÒ»´Î¡£È»¶ø£¬ÓÉÓÚÊDZê×¼»¯Êý¾Ý£¬ÐиüнöÐè¸üÐÂÄÇÐ©ÕæÕýÒòΪÐиüжø²úÉú±ä¸üµÄË÷Òý¼Ç¼¡£´ËÍ⣬InnoDBÍùÍùÔÚÊʵ±µÄλÖýøÐÐÐиüС£Èô¾ÉÊÂÎñÐèÒªÒýÓÃÒ»¸öÐÐÒÔʵÏÖMVCC£¬ÔòMySQL½«¾ÉÐи´ÖƵ½Ò»¸öÃûΪ»Ø¹ö¶ÎµÄÌØÊâÇøÓò¡£

ÏÂÃæ¼ÌÐø¸ú½ø¸üÐÂal-Khw¨¡rizm¨©³öÉúÄê·Ýʱ·¢ÉúµÄÇé¿ö¡£ÈôÓпռ䣬idΪ4µÄÐÐÖеijöÉúÄê·Ý×Ö¶ÎÔÚÊʵ±Î»ÖøüУ¨Êµ¼ÊÉÏ£¬¸Ã¸üÐÂ×ÜÊÇÔÚÊʵ±Î»Ö÷¢Éú£¬ÒòΪ³öÉúÄê·Ýʱһ¸öÕ¼¾Ý¶¨¶î¿Õ¼äµÄInteger£©¡£³öÉúÄê·ÝË÷ÒýÒ²ÔÚÊʵ±Î»ÖøüУ¬ÒÔ·´Ó³ÐÂÊý¾Ý¡£¾ÉÐÐÊý¾Ý±»¸´ÖƵ½»Ø¹ö¶Î¡£Ö÷¼üË÷ÒýÎÞÐè¸üУ¬£¨Ãû£¬ÐÕ£©Ë÷ÒýÒ²ÎÞÐè¸üС£Èô±íÉϰüº¬´óÁ¿Ë÷Òý£¬Ò²Ö»ÐèÒª¸üмìË÷Éæ¼°birth_year×ֶεÄÄDz¿·ÖË÷Òý¡£Òò´Ë£¬¼ÙÉèÏÖÓÐÉæ¼°ÀàËÆÓÚsignup_date¡¢last_login_timeµÈ×ֶεÄË÷Òý£¬ÔòÎÞÐè¶ÔÆä½øÐиüУ¬·´Ö®£¬ÔÚPostgresÖÐÔò±ØÐë¶ÔÆä¸üС£

ͬʱ£¬¸ÃÉè¼Æ»¹ÄÜÌá¸ßÊý¾Ýɨ³ý£¨vacuuming£©ºÍѹËõ²Ù×÷µÄЧÂÊ¡£ËùÓа´Ðè´ýɨ³ýµÄÐж¼¿ÉÖ±½ÓÔڻعö¶ÎÖлñÈ¡¡£Ïà½Ï¶øÑÔ£¬PostgresµÄautovacuum£¨×Ô¶¯É¨³ý£©½ø³ÌÔò±ØÐëÒª½øÐÐÈ«±íɨÃèÀ´È·ÈÏɾ³ýÐС£

MySQLÆôÓÃÒ»¸ö¶îÍâµÄ¼ä½Ó²ã£º¶þ¼¶Ë÷Òý¼Ç¼ָÏòÖ÷¼ü¼Ç¼£¬ÇÒÖ÷¼ü±¾Éí±£´æ´ÅÅÌÉϵÄÐÐλÖá£ÈôÐÐÆ«ÒÆ·¢Éú±ä¸ü£¬Ö»Ðè¸üÐÂÖ÷Ë÷Òý¡£

¸´ÖÆ

MySQLÖ§³Ö¶àÖÖ¸´ÖÆÄ£Ê½£º

»ùÓÚÓï¾ä£¨statement£©µÄ¸´ÖÆ£º¸´ÖÆÂß¼­SQLÓï¾ä£¨ÀýÈ磬°´×ÖÃæ¸´ÖÆÓï¾äÎÄ×ÖÈ磺UPDATE users SET birth_year=770 WHERE id = 4£©£»

»ùÓÚÐеĸ´ÖÆ£º¸´ÖƱä¸üµÄÐмǼ£»

»ìºÏ¸´ÖÆÄ£Ê½£º»ìºÏÉÏÊöÁ½ÖÖģʽ¡£

¶ÔÓÚÕâЩģʽ»¹ÓжàÖÖȨºâ¡£»ùÓÚÓï¾äµÄ¸´ÖÆÍùÍù×îΪ¼ò½à£¬µ«¿ÉÄÜ»áÐèÒª´Ó¿âÔËÓø߼۵ÄÓï¾äÀ´¸üÐÂÉÙÁ¿Êý¾Ý¡£´ËÍ⣬»ùÓÚÐеĸ´ÖÆ£¬ÀàËÆÓÚPostgres WAL¸´ÖÆ£¬Ïà¶Ô·±Ëö£¬µ«Æä½á¹ûÔ¤¼ûÐÔ¸üÇ¿£¬ÇÒÓÐÖúÓÚ´Ó¿â¸ßЧ¸üС£

ÔÚMySQLÖУ¬Ö»ÓÐÖ÷Ë÷Òý¾ßÓÐÖ¸ÕëÖ¸Ïò´ÅÅÌÉϵÄÐÐÆ«ÒÆ¡£ÕâÔÚÉæ¼°¸´ÖÆÊ±£¬²úÉúÁËÒ»¸öÖØÒª½á¹û¡£MySQL¸´ÖÆÁ÷Ö»ÐèÒª°üº¬Âß¼­¸üе½ÐеÄÐÅÏ¢¡£¸´ÖƸüÐÂÊôÓÚÕâÒ»Àà¡°½«ÐеÄʱ¼ä´Á´ÓT_1¸Äµ½T_2¡±¡£ÕâЩÓï¾äµÄ½á¹ûΪ´Ó¿â×Ô¶¯ÍƶÏÐèÒª±ä¸üµÄË÷Òý¡£

¶Ô±È֮ϣ¬Postgres¸´ÖÆÁ÷°üº¬ÎïÀí±ä¸ü£¬ÀýÈç¡°´ÅÅÌÉÏÆ«ÒÆ8,382,491£¬Ð´Èë×Ö½ÚXYZ¡±¡£PostgresÏ£¬´ÅÅ̵Äÿһ´ÎÎïÀí±ä¸ü¶¼ÐèÄÉÈëWALÁ÷¡£Ð¡µÄÂß¼­±ä»¯£¨Èç¸üÐÂÒ»¸öʱ¼ä´Á£©ÐèÒª¶à¸ö´ÅÅÌÉϵıä¸ü£ºPostgres±ØÐë²åÈëÐÂÔª×é²¢¸üÐÂËùÓÐÖ¸Ïò¸ÃÔª×éµÄË÷Òý¡£Èç´Ë£¬WALÁ÷Öн«ÊäÈë´óÁ¿±ä¸ü¡£¸ÃÉè¼Æ²îÒì±íÃ÷MySQL¸´ÖƵĶþ½øÖÆÈÕÖ¾±ÈPostgres WALÁ÷Òª¼ò½àºÜ¶à¡£

ÿ¸ö¸´ÖÆÁ÷µÄÔËÐз½Ê½¶ÔÓÚMVCCºÍ´Ó¿âµÄºÏ×÷·½Ê½Ò²ÓÐ×ÅÖØÒªÒâÒå¡£ÓÉÓÚMySQL¸´ÖÆÁ÷ÓÐÂß¼­¸üУ¬´Ó¿â¿É¾ßÓÐÕæÕýµÄMVCCÓïÒ壻Òò´Ë¶ÁÈ¡´Ó¿â²éѯ²»»áËø¶¨¸´ÖÆÁ÷¡£Ïà½Ï¶øÑÔ£¬Postgres WALÁ÷°üº¬´ÅÅÌÉϵÄÎïÀí±ä¸ü£¬Òò´ËPostgres´Ó¿â²»ÄÜÔËÓÃÓë¶ÁÈ¡²éѯÏà³åÍ»µÄ¸´ÖƸüУ¬¹Ê¶øÎÞ·¨ÊµÏÖMVCC¡£

MySQLµÄ¸´ÖƼܹ¹±íÃ÷£¬Èôbugsȷʵµ¼ÖÂÁ˱í±ÀÀ££¬Ôò¸ÃÎÊÌⲻ̫¿ÉÄܵ¼ÖÂÔÖÄÑÐԵĹÊÕÏ¡£¸´ÖÆ·¢ÉúÔÚÂß¼­²ã£¬Òò´ËÔÙÆ½ºâB-treeÕâÀà²Ù×÷²»¿ÉÄÜÒý·¢Ë÷Òý±ÀÀ£¡£Ò»¸öµäÐ͵ÄMySQL¸´ÖÆÎÊÌâÍùÍùÊÇÓï¾ä±»Ìø¹ý£¨»òȱÉÙ´ÎÊý¡¢ÔËÐÐÁ½´Î£©µÄÇé¿ö¡£Õâ¿ÉÄܻᵼÖÂÊý¾Ýȱʧ»òʧЧ£¬µ«²»»áµ¼ÖÂÊý¾Ý¿âå´»ú¡£

×îÖÕ£¬MySQLµÄ¸´ÖƼܹ¹¼ò»¯Á˲»Í¬MySQL°æ±¾¼äµÄ¸´ÖÆ¡£Èô¸´ÖƸñʽ±ä»¯£¬ÔòMySQL½öÔö¼ÓÆä°æ±¾£¬Õâ¶ÔMySQL¶øÑÔ²¢²»³£¼û¡£MySQLµÄÂß¼­¸´ÖƸñʽҲ±íÃ÷´æ´¢ÒýÇæ²ãÄÚ´ÅÅÌÉϵıä¸ü²»»áÓ°Ïì¸´ÖÆ¸ñʽ¡£Ö´ÐÐMySQL¸üеij£Ó÷½Ê½ÊÇÒ»´Î¸üÐÂÒ»¸ö´Ó¿â£¬ÇÒÒ»µ«¸üÐÂËùÓдӿ⣬ÔòÆäÖÐÒ»¸ö´Ó¿â½«±äΪеÄÖ÷Êý¾Ý¿â¡£Õâ¿ÉÒÔÔÚÁã¹ÊÕÏÏÂÍê³É£¬Í¬Ê±Ò²¼ò»¯Á˱£³ÖMySQL×îÐÂ״̬µÄ²Ù×÷¡£

ÆäËüMySQLÉè¼ÆÓÅÊÆ

So far, we¡¯ve focused on the on-disk architecture for Postgres and MySQL. Some other important aspects of MySQL¡¯s architecture cause it to perform significantly better than Postgres, as well.

½ØÖ¹Ä¿Ç°£¬±¾ÎÄÖ÷Òª¹Ø×¢PostgresºÍMySQL´ÅÅÌÉϵļܹ¹¡£¶øMySQL¼Ü¹¹»¹ÓÐÒ»²¿·ÖÒªµãʹ֮ÔÚÔËÐÐÉÏÓÅÓÚPostgres¡£

»º³å³Ø

Ê×ÏÈ£¬»º´æÔÚÕâÁ½¸öÊý¾Ý¿âÖеÄÔËÐз½Ê½²»Í¬¡£PostgresΪÄÚ²¿¸ßËÙ»º´æÅäÖÃÄڴ棬µ«½ÏÖ®»úÆ÷×ÜÄÚ´æÁ¿£¬ÕâЩ¸ßËÙ»º´æÍùÍù½ÏС¡£ÎªÁËÌáÉýÐÔÄÜ£¬PostgresÖ§³ÖÄÚºËͨ¹ýÒ³Ãæ»º´æ×Ô¶¯´æ´¢×î½ü·ÃÎÊ´ÅÅ̵ÄÊý¾Ý¡£ÀýÈ磬Uber×î´óµÄPostgres´Ó¿â¾ßÓÐ768 GB¿ÉÓÃÄڴ棬µ«Êµ¼ÊÉÏÆäÖÐÖ»ÓÐÔ¼25 GB±»PostgresµÄRSSÄÚ´æÕ¼Óá£Õâ¸øLinuxÒ³Ãæ»º´æÒÅÁôÁ˳¬¹ý700 GBµÄ¿ÕÏÐÄÚ´æ¡£

¸ÃÉè¼ÆµÄÎÊÌâÔÚÓÚ£¬ÊÂʵÉÏ£¬½ÏÖ®·ÃÎÊRSSÄڴ棬ͨ¹ýÒ³Ãæ»º´æ·ÃÎÊÊý¾ÝºÄ×ʸü¸ß¡£Õë¶Ô´ÅÅÌÖеÄÊý¾Ý²éѯ£¬Postgres·¢²¼ÁËlseek(2)ºÍread(2)ϵͳµ÷ÓÃÒÔ¶¨Î»Êý¾Ý¡£´Ë¶þÕß¶¼»áÒý·¢ÉÏÏÂÎÄÇл»£¨context switch£©£¬Õâ±È·ÃÎÊÖ÷ÄÚ´æÊý¾ÝµÄ»¨·Ñ»¹Òª¸ü¸ß¡£Êµ¼ÊÉÏ£¬ÔÚÕâÒ»µãÉÏ£¬PostgresÉõÖÁ»¹Ã»ÓÐÍêÈ«ÓÅ»¯£ºÆäδʹÓÃpread(2)ϵͳµ÷Óã¬pread(2)½«seek + read²Ù×÷ºÏ²¢µ½Ò»¸öϵͳµ÷ÓÃÖ®ÖС£

ͨ¹ý±È½Ï£¬InnoDB´æ´¢ÒýÇæÊµÏÖÁË×ÔÉíInnoDB»º³å³ØÖеÄLRU¡£ÕâÔÚÂß¼­ÉϺÍLinuxÒ³Ãæ»º´æÏàËÆ£¬µ«ËüʵÏÖÓÚÓû§¿Õ¼ä¡£ËäÈ»±ÈPostgresµÄÉè¼Æ¸ü¸´ÔÓ£¬µ«InnoDB»º³å³ØÉè¼ÆÒ²ÓµÓÐһЩÄÜÍ»³öµÄÓÅÊÆ£º

¿ÉʵÏÖÒ»¸öÓû§LRUÉè¼Æ¡£ÀýÈ磬Äܹ»¼ì²â³öÓÐËðLRUµÄ¶ñÒâ·ÃÎÊģʽ£¬²¢×èÖ¹ÆäÔì³É¸ü½øÒ»²½µÄΣº¦£»

¼¸ºõ²»»áµ¼ÖÂÉÏÏÂÎÄÇл»¡£Í¨¹ýInnoDB»º³å³Ø·ÃÎʵ½µÄÊý¾Ý²»ÒªÇóÈκÎÓû§/Äں˵ÄÉÏÏÂÎÄÇл»¡£×îÔã¸âµÄÇé¿öιýÓÚ·¢ÉúTLB miss£¨TLBʧ°Ü£©£¬ÕâÏà¶Ô¶øÑÔÒ²½ÏΪ±ãÒË£¬ÇÒ¿Éͨ¹ý´óÄÚ´æÒ³Ê¹Ëðʧ×îС»¯¡£

Á¬½Ó¹ÜÀí

ͨ¹ý¶Ôÿ¸öÁ¬½ÓÉú³ÉÒ»¸öỊ̈߳¨thread-per-connection£©£¬MySQLʵÏÖ²¢·¢Á¬½Ó¡£ÕâÖÖÁ¬½ÓÕ¼ÓÃÏà¶Ô½ÏµÍ£»Ã¿¸öÏ̶߳¼Õ¼ÓÃÕ»¿Õ¼äÄڴ棬¼ÓÉϲ¿·ÖÅäÖõ½¶ÑÄÚ´æÁ¬½ÓÌØ¶¨£¨connection-specific£©»º³åÇøµÄ¡£½«MySQLÀ©Õ¹µ½10,000»òÊÇÕâÑùµÄ²¢ÐÐÁ¬½ÓÊǺܳ£¼ûµÄ£¬Êµ¼ÊÉÏ£¬Èç½ñUberµÄMySQLʵÀý¸ü½Ó½üÕâ¸öÁ¬½ÓÊý¡£

µ«Postgres²ÉÓÃÒ»¸öÿ¸öÁ¬½ÓÒ»¸ö½ø³Ì£¨process-per-connection£©Éè¼Æ¡£³öÓë¶à·½ÃæµÄÔ­Òò£¬¸ÃÉè¼ÆºÄ×ʱÈÒ»¸öthread-per-connectionÉè¼Æ¸ß³öºÜ¶à¡£½ÏÖ®Éú³ÉÒ»¸öÐÂỊ̈߳¬ÅÉÉúÒ»¸öнø³Ì»áÕ¼¾Ý¸ü¶àÄÚ´æ¡£´ËÍ⣬½ø³Ì¼äµÄIPC±ÈÏ̼߳äµÄIPCºÄ×ʸü¶à¡£Postgres 9.2ʹÓÃSystem V IPCΪIPCµÄÔ­Ó¶ø²»ÊÇʹÓÃÏß³ÌʱµÄÇáÁ¿¼¶futexes¡£ÒòΪͨ³£ÔÚ futexÎÞ¾ºÕùµÄÇé¿öÏ£¬ÎÞÐèÉÏÏÂÎÄÇл»£¬ËùÒÔfutexes±ÈSystem V IPCËٶȸü¿ì¡£

PostgresÉè¼ÆµÄÄÚ´æºÍIPCÕ¼ÓÃÖ®Í⣬PostgresËÆºõÔÚÖ§³Ö¸ßÁ¬½ÓÊýÁ¿·½ÃæµÄ´¦ÀíÄÜÁ¦½Ï²î£¬¼´Ê¹ÊÇÔÚ¿ÉÓÃÄÚ´æ³ä×ãµÄÇé¿öÏ¡£¶øÔÚÀ©Õ¹PostgresÖÁÊý°Ù¸ö»îÔ¾Á¬½ÓµÄ¹ý³ÌÖУ¬UberÍŶÓÔâÓöÁËÖØ´óÎÊÌâ¡£ËäÈ»¸Ã¹Ù·½ÎĵµÎ´¸ø³öÃ÷È·Ô­Òò£¬µ«µÄÈ·¸ø³öÁËÇ¿ÁҵĽ¨Ò飺ѡÓýø³ÌÍâÁ¬½Ó³Ø»úÖÆÒÔÀ©Õ¹PostgresÁ¬½ÓÊýÁ¿¡£ÏàÓ¦µØ£¬ÔÚÔËÓÃpgbouncerºÏ²¢Á¬½ÓºÍPostgres·½Ã棬UberÍŶÓ×ÜÌåÉÏ»ñµÃÁ˳ɹ¦¡£È»¶ø£¬Å¼¶ûºó¶Ë·þÎñ»¹ÊÇ»á³öÏÖÓ¦ÓÃbug£¬´Ó¶øµ¼Ö¿ªÆô¶àÓÚÓ¦ÓзþÎñµÄ»îÔ¾Á¬½Ó£¨ÍùÍùÊÇ¡°idle in transaction¡±Á¬½Ó£©£¬ÕâЩbugsÓֻᵼÖ¹ÊÕÏÀ©ÕÅ¡£

½áÂÛ

ÔÚUberÔçÆÚ£¬PostgresÊÊÓ¦·¢Õ¹ÐèÇ󣬵«ÊÇËæ×ÅPostgresµÄÀ©Õ¹ºÍUberµÄ·¢Õ¹£¬UberÍŶÓÖ𽥿ªÊ¼ÔâÓöһЩ¼¬ÊÖµÄÎÊÌâ¡£Èç½ñ£¬Uber»¹±£ÓÐһЩÒÅÁôPostgresʵÀý£¬µ«´óÁ¿Êý¾Ý¿âÊÇ»ùÓÚMySQL£¨ÍùÍùÓõ½Schemaless²ã£©¹¹½¨£¬»òÊÇ£¨ÔÚijЩÇé¿öÏ£©»ùÓÚNoSQLÊý¾Ý¿â£¬±ÈÈçCassandra¡£UberÍŶÓÈç½ñ¶ÔMySQL½ÏΪÂúÒ⣬δÀ´½«»á¸üиü¶à²©ÎÄ£¬ÒÔ½âÊÍÆäÕë¶ÔUberµÄ¸ü¶àÏȽøÓ¦Óá£

 

   
2928 ´Îä¯ÀÀ       28
Ïà¹ØÎÄÕÂ

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

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

Êý¾ÝÖÎÀí¡¢Êý¾Ý¼Ü¹¹¼°Êý¾Ý±ê×¼
MongoDBʵս¿Î³Ì
²¢·¢¡¢´óÈÝÁ¿¡¢¸ßÐÔÄÜÊý¾Ý¿âÉè¼ÆÓëÓÅ»¯
PostgreSQLÊý¾Ý¿âʵսÅàѵ
×îл¼Æ»®
DeepSeekÔÚÈí¼þ²âÊÔÓ¦ÓÃʵ¼ù 4-12[ÔÚÏß]
DeepSeek´óÄ£ÐÍÓ¦Óÿª·¢Êµ¼ù 4-19[ÔÚÏß]
UAF¼Ü¹¹ÌåϵÓëʵ¼ù 4-11[±±¾©]
AIÖÇÄÜ»¯Èí¼þ²âÊÔ·½·¨Óëʵ¼ù 5-23[ÉϺ£]
»ùÓÚ UML ºÍEA½øÐзÖÎöÉè¼Æ 4-26[±±¾©]
ÒµÎñ¼Ü¹¹Éè¼ÆÓ뽨ģ 4-18[±±¾©]

MySQLË÷Òý±³ºóµÄÊý¾Ý½á¹¹
MySQLÐÔÄܵ÷ÓÅÓë¼Ü¹¹Éè¼Æ
SQL ServerÊý¾Ý¿â±¸·ÝÓë»Ö¸´
ÈÃÊý¾Ý¿â·ÉÆðÀ´ 10´óDB2ÓÅ»¯
oracleµÄÁÙʱ±í¿Õ¼äдÂú´ÅÅÌ
Êý¾Ý¿âµÄ¿çƽ̨Éè¼Æ

²¢·¢¡¢´óÈÝÁ¿¡¢¸ßÐÔÄÜÊý¾Ý¿â
¸ß¼¶Êý¾Ý¿â¼Ü¹¹Éè¼ÆÊ¦
HadoopÔ­ÀíÓëʵ¼ù
Oracle Êý¾Ý²Ö¿â
Êý¾Ý²Ö¿âºÍÊý¾ÝÍÚ¾ò
OracleÊý¾Ý¿â¿ª·¢Óë¹ÜÀí

GE Çø¿éÁ´¼¼ÊõÓëʵÏÖÅàѵ
º½Ìì¿Æ¹¤Ä³×Ó¹«Ë¾ Nodejs¸ß¼¶Ó¦Óÿª·¢
ÖÐÊ¢Òæ»ª ׿Խ¹ÜÀíÕß±ØÐë¾ß±¸µÄÎåÏîÄÜÁ¦
ijÐÅÏ¢¼¼Êõ¹«Ë¾ PythonÅàѵ
ij²©²ÊITϵͳ³§ÉÌ Ò×ÓÃÐÔ²âÊÔÓëÆÀ¹À
ÖйúÓÊ´¢ÒøÐÐ ²âÊÔ³ÉÊì¶ÈÄ£Ðͼ¯³É(TMMI)
ÖÐÎïÔº ²úÆ·¾­ÀíÓë²úÆ·¹ÜÀí