ãInnoDBåMyISAMæ¯å¨ä½¿ç¨MySQLæ常ç¨ç两个表类åï¼åæä¼ç¼ºç¹ï¼è§å
·ä½åºç¨èå®ã
ããä¸é¢æ¯å·²ç¥ç两è
ä¹é´çå·®å«ï¼ä»
ä¾åèã
ãã
ããinnodb
ããInnoDB ç» MySQL æä¾äºå
·æäºå¡(commit)ãåæ»(rollback)åå´©æºä¿®å¤è½å
ãã(crash recovery capabilities)çäºå¡å®å
¨
ãã(transaction-safe (ACID compliant))å表ãInnoDB æä¾äºè¡é
ãã(locking on row level)ï¼æä¾ä¸ Oracle ç±»åä¸è´çä¸å é读å(non-locking
ããread in SELECTs)ãè¿äºç¹æ§åæé«äºå¤ç¨æ·å¹¶åæä½çæ§è½è¡¨ç°ã
ããå¨InnoDB表ä¸ä¸éè¦æ©å¤§éå®(lock escalation)ï¼å 为 InnoDB çåéå®(row level
ããlocks)éå®é常å°ç空é´ãInnoDB æ¯ MySQL ä¸ç¬¬ä¸ä¸ªæä¾å¤é®çº¦æ(FOREIGN KEY
ããconstraints)ç表å¼æã
ãã
ããInnoDB ç设计ç®æ æ¯å¤ç大容éæ°æ®åºç³»ç»ï¼å®ç CPU å©ç¨çæ¯å
¶å®åºäºç£ççå
³ç³»æ°æ®åº
ããå¼ææä¸è½æ¯çãå¨ææ¯ä¸ï¼InnoDB æ¯ä¸å¥æ¾å¨ MySQL åå°çå®æ´æ°æ®åºç³»ç»ï¼
ããInnoDB å¨ä¸»å
åä¸å»ºç«å
¶ä¸ç¨çç¼å²æ± ç¨äºé«éç¼å²æ°æ®åç´¢å¼ã InnoDB ææ°æ®åç´¢å¼å
ããæ¾å¨è¡¨ç©ºé´éï¼å¯è½å
å«å¤ä¸ªæ件ï¼è¿ä¸å
¶å®çä¸ä¸æ ·ï¼ä¸¾ä¾æ¥è¯´ï¼å¨ MyISAM ä¸ï¼è¡¨è¢«åæ¾
ããå¨åç¬çæ件ä¸ãInnoDB 表ç大å°åªåéäºæä½ç³»ç»çæ件大å°ï¼ä¸è¬ä¸º 2 GBã
ããInnoDBææç表é½ä¿åå¨åä¸ä¸ªæ°æ®æ件 ibdata1 ä¸ï¼ä¹å¯è½æ¯å¤ä¸ªæ件ï¼æè
æ¯ç¬ç«ç
ãã表空é´æ件ï¼,ç¸å¯¹æ¥è¯´æ¯è¾ä¸å¥½å¤ä»½ï¼å
è´¹çæ¹æ¡å¯ä»¥æ¯æ·è´æ°æ®æ件ãå¤ä»½ binlogï¼
ããæè
ç¨ mysqldumpã
ãã
ããMyISAM
ããMyISAM æ¯MySQL缺çåè´®å¼æ .
ãã
ããæ¯å¼ MyISAM 表被åæ¾å¨ä¸ä¸ªæ件 ãfrm æ件åæ¾è¡¨æ ¼å®ä¹ã æ°æ®æ件æ¯MYD (MYData) ã
ããç´¢å¼æ件æ¯MYI (MYIndex) å¼ä¼¸ã
ãã
ããå 为MyISAMç¸å¯¹ç®åæ以å¨æçä¸è¦ä¼äºInnoDB..å°ååºç¨ä½¿ç¨MyISAMæ¯ä¸éçéæ©.
ãã
ããMyISAM表æ¯ä¿åææ件çå½¢å¼,å¨è·¨å¹³å°çæ°æ®è½¬ç§»ä¸ä½¿ç¨MyISAMåå¨ä¼çå»ä¸å°ç麻ç¦
ãã
ããMyISAMæ¯ISAM表çæ°çæ¬ï¼æå¦ä¸æ©å±ï¼
ãã
ãã·äºè¿å¶å±æ¬¡çå¯ç§»æ¤æ§ã
ãã·NULLåç´¢å¼ã
ãã·对åé¿è¡æ¯ISAM表ææ´å°çç¢çã
ãã·æ¯æ大æ件ã
ãã·æ´å¥½çç´¢å¼å缩ã
ãã·æ´å¥½çé®åç»è®¡åå¸ã
ãã·æ´å¥½åæ´å¿«çauto_incrementå¤çã
ãã
ãã以ä¸æ¯ä¸äºç»èåå
·ä½å®ç°çå·®å«ï¼
ãã
ããâ1.InnoDBä¸æ¯æFULLTEXTç±»åçç´¢å¼ã
ããâ2.InnoDB ä¸ä¸ä¿å表çå
·ä½è¡æ°ï¼ä¹å°±æ¯è¯´ï¼æ§è¡select count(*) from tableæ¶ï¼
ããInnoDBè¦æ«æä¸éæ´ä¸ªè¡¨æ¥è®¡ç®æå¤å°è¡ï¼ä½æ¯MyISAMåªè¦ç®åç读åºä¿å好çè¡æ°å³å¯ã
ãã注æçæ¯ï¼å½count(*)è¯å¥å
å« whereæ¡ä»¶æ¶ï¼ä¸¤ç§è¡¨çæä½æ¯ä¸æ ·çã
ããâ3.对äºAUTO_INCREMENTç±»åçå段ï¼InnoDBä¸å¿
é¡»å
å«åªæ该å段çç´¢å¼ï¼ä½æ¯å¨MyISAM
ãã表ä¸ï¼å¯ä»¥åå
¶ä»å段ä¸èµ·å»ºç«èåç´¢å¼ã
ããâ4.DELETE FROM tableæ¶ï¼InnoDBä¸ä¼éæ°å»ºç«è¡¨ï¼èæ¯ä¸è¡ä¸è¡çå é¤ã
ããâ5.LOAD TABLE FROM MASTERæä½å¯¹InnoDBæ¯ä¸èµ·ä½ç¨çï¼è§£å³æ¹æ³æ¯é¦å
æInnoDB表æ¹æ
ããMyISAM表ï¼å¯¼å
¥æ°æ®ååæ¹æInnoDB表ï¼ä½æ¯å¯¹äºä½¿ç¨çé¢å¤çInnoDBç¹æ§ï¼ä¾å¦å¤é®ï¼ç
ãã表ä¸éç¨ã
ãã
ããâMyISAMç±»åçäºè¿å¶æ°æ®æ件å¯ä»¥å¨ä¸åæä½ç³»ç»ä¸è¿ç§»ã
ãã
ããå¦å¤ï¼InnoDB表çè¡éä¹ä¸æ¯ç»å¯¹çï¼åå¦å¨æ§è¡ä¸ä¸ªSQLè¯å¥æ¶MySQLä¸è½ç¡®å®è¦æ«æç
ããèå´ï¼InnoDB表åæ ·ä¼éå
¨è¡¨ï¼ä¾å¦update table set num=1 where name like â%aaa%â
ãã
ããåå¦å¤ï¼ä½¿ç¨ä¸¤ç§çéæ©ï¼å¦æä½ çæ°æ®æ§è¡å¤§éçINSERTæUPDATEï¼åºäºæ§è½æ¹é¢çèèï¼
ããåºè¯¥ä½¿ç¨InnoDB表ãå¦ææ§è¡å¤§éçSELECTï¼MyISAMæ¯æ´å¥½çéæ©ãè¥éè¦ä½¿ç¨äºå¡å¤çï¼
ããä½æ¯åæ¥çæ°æ®è¡¨ä½¿ç¨çæ¯myisamï¼å°±éè¦æ¹ä¸ºbdbæè
innodbï¼è¿æ ·åºäºmyisamçç¨åºï¼
ããå°ç±»åæ¹ä¸ºinnodbåï¼å
¶ç¨åºä¸ç¨æ¹å¨â¦â¦
ãã
ãã综ä¸æè¿°ï¼ä»»ä½ä¸ç§è¡¨é½ä¸æ¯ä¸è½çï¼åªææ°å½çé对ä¸å¡ç±»åæ¥éæ©åéç表类åï¼æè½
ããæ大çåæ¥MySQLçæ§è½ä¼å¿ã
ãã
ããMyISAMåInnoDBä¼åï¼
ããkey_buffer_size - è¿å¯¹MyISAM表æ¥è¯´é常éè¦ãå¦æåªæ¯ä½¿ç¨MyISAM表ï¼å¯ä»¥æå®è®¾ç½®
ãã为å¯ç¨å
åç 30-40%ãåççå¼åå³äºç´¢å¼å¤§å°ãæ°æ®é以åè´è½½ -- è®°ä½ï¼MyISAM表ä¼
ãã使ç¨æä½ç³»ç»çç¼åæ¥ç¼åæ°æ®ï¼å æ¤éè¦çåºé¨åå
åç»å®ä»¬ï¼å¾å¤æ
åµä¸æ°æ®æ¯ç´¢å¼å¤§
ããå¤äºã尽管å¦æ¤ï¼éè¦æ»æ¯æ£æ¥æ¯å¦ææç key_buffer é½è¢«å©ç¨äº -- .MYI æ件åªæ 1GB
ããï¼è key_buffer å´è®¾ç½®ä¸º 4GB çæ
åµæ¯é常å°çãè¿ä¹å太浪费äºãå¦æä½ å¾å°ä½¿ç¨
ããMyISAM表ï¼é£ä¹ä¹ä¿çä½äº 16-32MB ç key_buffer_size 以éåºç»äºç£çç临æ¶è¡¨ç´¢å¼
ããæéã
ããinnodb_buffer_pool_size - è¿å¯¹Innodb表æ¥è¯´é常éè¦ãInnodbç¸æ¯MyISAM表对ç¼å²æ´
ãã为ææãMyISAMå¯ä»¥å¨é»è®¤ç key_buffer_size 设置ä¸è¿è¡çå¯ä»¥ï¼ç¶èInnodbå¨é»è®¤ç
ããinnodb_buffer_pool_size 设置ä¸å´è·èçä¼¼çãç±äºInnodbææ°æ®åç´¢å¼é½ç¼åèµ·æ¥ï¼
ããæ éçç»æä½ç³»ç»å¤ªå¤çå
åï¼å æ¤å¦æåªéè¦ç¨Innodbçè¯åå¯ä»¥è®¾ç½®å®é«è¾¾ 70-80% ç
ããå¯ç¨å
åãä¸äºåºç¨äº key_buffer çè§åæ -- å¦æä½ çæ°æ®éä¸å¤§ï¼å¹¶ä¸ä¸ä¼æ´å¢ï¼é£
ããä¹æ éæ
ããinnodb_additional_pool_size - è¿ä¸ªé项对æ§è½å½±å并ä¸å¤ªå¤ï¼è³å°å¨æå·®ä¸å¤è¶³å¤å
å
ããå¯åé
çæä½ç³»ç»ä¸æ¯è¿æ ·ãä¸è¿å¦æä½ ä»ç¶æ³è®¾ç½®ä¸º 20MB(æè
æ´å¤§)ï¼å æ¤å°±éè¦çä¸ä¸
ããInnodbå
¶ä»éè¦åé
çå
åæå¤å°ã
ããinnodb_log_file_size å¨é«åå
¥è´è½½å°¤å
¶æ¯å¤§æ°æ®éçæ
åµä¸å¾éè¦ãè¿ä¸ªå¼è¶å¤§åæ§è½ç¸
ãã对è¶é«ï¼ä½æ¯è¦æ³¨æå°å¯è½ä¼å¢å æ¢å¤æ¶é´ãæç»å¸¸è®¾ç½®ä¸º 64-512MBï¼è·æ®æå¡å¨å¤§å°èå¼ã
ããinnodb_log_buffer_size é»è®¤ç设置å¨ä¸ç强度åå
¥è´è½½ä»¥åè¾çäºå¡çæ
åµä¸ï¼æå¡å¨æ§
ããè½è¿å¯ä»¥ãå¦æåå¨æ´æ°æä½å³°å¼æè
è´è½½è¾å¤§ï¼å°±åºè¯¥èèå 大å®çå¼äºãå¦æå®çå¼è®¾ç½®
ãã太é«äºï¼å¯è½ä¼æµªè´¹å
å -- å®æ¯ç§é½ä¼å·æ°ä¸æ¬¡ï¼å æ¤æ é设置è¶
è¿1ç§æéçå
å空é´ã
ããé常 8-16MB 就足å¤äºãè¶å°çç³»ç»å®çå¼è¶å°ã
ããinnodb_flush_logs_at_trx_commit æ¯å¦ä¸ºInnodbæ¯MyISAMæ
¢1000åè头大ï¼çæ¥ä¹è®¸ä½ å¿
ããäºä¿®æ¹è¿ä¸ªåæ°äºãé»è®¤å¼æ¯ 1ï¼è¿æå³çæ¯æ¬¡æ交çæ´æ°äºå¡ï¼æè
æ¯ä¸ªäºå¡ä¹å¤çè¯å¥ï¼
ããé½ä¼å·æ°å°ç£çä¸ï¼èè¿ç¸å½èè´¹èµæºï¼å°¤å
¶æ¯æ²¡æçµæ± å¤ç¨ç¼åæ¶ãå¾å¤åºç¨ç¨åºï¼å°¤å
¶æ¯
ããä» MyISAM转åè¿æ¥çé£äºï¼æå®çå¼è®¾ç½®ä¸º 2 å°±å¯ä»¥äºï¼ä¹å°±æ¯ä¸ææ¥å¿å·æ°å°ç£çä¸ï¼
ããèåªå·æ°å°æä½ç³»ç»çç¼åä¸ãæ¥å¿ä»ç¶ä¼æ¯ç§å·æ°å°ç£çä¸å»ï¼å æ¤é常ä¸ä¼ä¸¢å¤±æ¯ç§1-
ãã2次æ´æ°çæ¶èãå¦æ设置为 0 就快å¾å¤äºï¼ä¸è¿ä¹ç¸å¯¹ä¸å®å
¨äº -- MySQLæå¡å¨å´©æºæ¶
ããå°±ä¼ä¸¢å¤±ä¸äºäºå¡ã设置为 2 ææ¥ä¸¢å¤±å·æ°å°æä½ç³»ç»ç¼åçé£é¨åäºå¡ã
ããtable_cache -- æå¼ä¸ä¸ªè¡¨çå¼éå¯è½å¾å¤§ãä¾å¦MyISAMæMYIæ件头æ å¿è¯¥è¡¨æ£å¨ä½¿ç¨
ããä¸ãä½ è¯å®ä¸å¸æè¿ç§æä½å¤ªé¢ç¹ï¼æ以é常è¦å 大ç¼åæ°éï¼ä½¿å¾è¶³ä»¥æ大é度å°ç¼åæ
ããå¼ç表ãå®éè¦ç¨å°æä½ç³»ç»çèµæºä»¥åå
åï¼å¯¹å½åç硬件é
ç½®æ¥è¯´å½ç¶ä¸æ¯ä»ä¹é®é¢äºã
ããå¦æä½ æ200å¤ä¸ªè¡¨çè¯ï¼é£ä¹è®¾ç½®ä¸º 1024 ä¹è®¸æ¯è¾åéï¼æ¯ä¸ªçº¿ç¨é½éè¦æå¼è¡¨ï¼ï¼
ããå¦æè¿æ¥æ°æ¯è¾å¤§é£ä¹å°±å 大å®çå¼ãææ¾ç»è§è¿è®¾ç½®ä¸º 100,000 çæ
åµã
ããthread_cache -- 线ç¨çå建åéæ¯çå¼éå¯è½å¾å¤§ï¼å 为æ¯ä¸ªçº¿ç¨çè¿æ¥/æå¼é½éè¦ã
ããæé常è³å°è®¾ç½®ä¸º 16ãå¦æåºç¨ç¨åºä¸æ大éçè·³è·å¹¶åè¿æ¥å¹¶ä¸ Threads_Created çå¼
ããä¹æ¯è¾å¤§ï¼é£ä¹æå°±ä¼å 大å®çå¼ãå®çç®çæ¯å¨é常çæä½ä¸æ éå建æ°çº¿ç¨ã
ããquery_cache -- å¦æä½ çåºç¨ç¨åºæ大é读ï¼èä¸æ²¡æåºç¨ç¨åºçº§å«çç¼åï¼é£ä¹è¿å¾æ
ããç¨ãä¸è¦æå®è®¾ç½®å¤ªå¤§äºï¼å 为æ³è¦ç»´æ¤å®ä¹éè¦ä¸å°å¼éï¼è¿ä¼å¯¼è´MySQLåæ
¢ãé常设置
ãã为 32-512Mbã设置å®ä¹åæ好æ¯è·è¸ªä¸æ®µæ¶é´ï¼æ¥çæ¯å¦è¿è¡è¯å¥½ãå¨ä¸å®çè´è½½ååä¸ï¼
ããå¦æç¼åå½ä¸ç太ä½äºï¼å°±å¯ç¨å®ã
ããsort_buffer_size --å¦æä½ åªæä¸äºç®åçæ¥è¯¢ï¼é£ä¹å°±æ éå¢å å®çå¼äºï¼å°½ç®¡ä½ æ
ãã64GB çå
åãæä¸å¥½ä¹è®¸ä¼éä½æ§è½
温馨提示:答案为网友推荐,仅供参考