MySQL 5.5 ÍÍ¡áÅéÇ à»ÅÕè¹ÁÒãªé InnoDB á·¹ MyISAM
·ÕèÁÒ: http://www.blognone.com/news/20607
MySQL 5.5 ÍÍ¡áÅéÇ ¡ÒÃà»ÅÕè¹á»Å§·ÕèÊӤѷÕèÊØ´¤×ÍÂéÒÂÁÒãªé InnoDB à»ç¹ default storage engine á·¹ MyISAM àÊÕÂ·Õ ËÅѧ¨Ò¡àµÃÕÂÁ¡ÒÃàÃ×èͧ¹ÕéÁÒ¹Ò¹ËÅÒÂÃØè¹
¼Å¡ç¤×Í MySQL 5.5 ¨Ðä´é¿Õà¨ÍÃìËÅÒÂæ ÍÂèÒ§¨Ò¡ InnoDB â´Â੾ÒÐàÃ×èͧ»ÃÐÊÔ·¸ÔÀÒ¾ áÅФÇÒÁÊÒÁÒö㹡ÒâÂÒµÑÇ (scalability) à¾ÔèÁà¢éÒÁÒ Êèǹ¢Í§ãËÁèÍÂèÒ§Í×蹡ç¤×Í»ÃѺ»ÃاàÃ×èͧ replication áÅФÓÊÑè§ãËÁèÍÂèÒ§ SIGNAL/RESIGNAL ÃÒÂÅÐàÍÕ´ÍèÒ¹¡Ñ¹àͧ㹠What's New in MySQL 5.5
àÇçºä«µì Help Net Security ÃкØÇèÒ㹡Ò÷´Êͺº¹ÇÔ¹â´ÇÊì MySQL 5.5 ÁÕ»ÃÐÊÔ·¸ÔÀÒ¾´Õ¡ÇèÒ 5.1 àÃ×èͧ read/write ੾ÒСóշÕèÊÙ§ÊØ´¤×Í 1,500% Êèǹº¹ÅԹء«ìÊÙ§ÊØ´ 360%
·Ó¤ÇÒÁÃÙé¨Ñ¡¡Ñº INNODB Engine
·ÕèÁÒ Modoeye Articles â´Â Sherokuÿà¢Õ¹àÁ×èÍ 2009.08.09
InnoDB Database Engine à»ç¹ Database Engine ˹Ö觢ͧ MySQL ·Õèà»ç¹·Õè¹ÔÂÁàªè¹à´ÕÂǡѺ MyISAM à˵ؼÅ˹Ö觷ÕèÊèǹãËè¹Ñ¡à¢Õ¹â»Ãá¡ÃÁãªé MyISAM ¤×ͤÇÒÁàÃçǢͧ¡Òà Query áµè InnoDB ¹Ñé¹áÁé¤ÇÒÁàÃçǨÐÊÙéäÁèä´éáµè¡çÂѧà»ç¹·Õè¹ÔÂÁà¾ÃÒÐÍÐäà àÃÒÁÒà»ÃÕºà·Õº Engine ·Ñé§ 2 Ẻ¡Ñ¹ÇèÒµèÒ§¡Ñ¹ÍÂèÒ§äÃ
| MyISAM | InnoDB | MEMORY | NDB | ||
|---|---|---|---|---|---|
| Multi-statement transactions, ROLLBACK | - | X | - | X | |
| Foreign key constraints | - | X | - | - | |
| Locking level | table | row | table | row | |
| BTREE indexes | X | X | - | X | |
| FULLTEXT indexes | X | - | - | - | |
| HASH lookups | - | X | X | X | |
| Other in-memory tree-based index | - | - | 4.1.0 | - | |
| GIS, RTREE indexes | 4.1.0 | - | - | - | |
| Unicode | 4.1.0 | 4.1.2 | - | - | |
| Merge (union views) | X | - | - | - | |
| Compress read-only storage | X | - | - | - | |
| Relative disk use | low | high | - | low | |
| Relative memory use | low | high | low | high |
ÍéÒ§ÍÔ§¨Ò¡ http://dev.mysql.com/tech-resources/articles/storage-engine/part_3.html
¨ÐàËç¹ÇèÒ InnoDB ÁÕ¢éÍ´Õà˹×Í MyISAM ¤×Í ¡ÒÃÃͧÃѺ Transaction, Foreign Key, ÁÕ Hash Lookup, áÅСÒà Lock ä´é¶Ö§ÃдѺ Row
ÿáµè¡çÁÕ¢éÍàÊÕ¤×Í¢Ò´¡Ò÷ӴѪ¹ÕẺ FULLTEXT, GIS áÅÐ RTREE, äÁèÁÕ¡Òà Union Views, äÁèÁÕ¡ÒúպÍÑ´ áÅÐãªé·ÃѾÂÒ¡ÃÁÒ¡¡ÇèÒ
áµèà˵Øã´ InnoDB Âѧ¤§à»ç¹·Õè¹ÔÂÁ?
- ¡ÒÃ·Ó Transaction «Öè§äÁèÁÕã¹ Engine Í×è¹æ ¹Í¡¨Ò¡ InnoDB áÅÐ NDB à·èÒ¹Ñé¹
- ·ÓÊÒÁÒö Lock ä´éÃдѺ Row ·Óà¢Õ¹áÅÐÍèÒ¹ä´éã¹àÇÅÒà´ÕÂǡѹ
- ÁÕ¡ÒÃ·Ó Hashing ´Ñª¹Õ
¹Í¡¨Ò¡¤ÇÒÁÊÒÁÒöÊÓ¤ÑàÃ×èͧ¡ÒÃ·Ó Transaction ä´éáÅéǹÑé¹ ÊÔè§ÊӤѷÕèÁÕ¡ÒÃàÅ×Í¡ãªé InnoDB ¤×Í ¡Òà Lock ÃдѺ Row áÁéÇèÒ ¨ÐÁÕ¡ÒÃãªé·ÃѾÂҡ÷ÕèÁÒ¡¡ÇèÒ áµè´éÇ¡Ò÷ÕèÊÒÁÒö Lock ÃдѺ Row ·ÓãËéÊÒÁÒöà¢Õ¹ (Insert/Update/Delete) ã¹àÇÅÒà´ÕÂǡѺ¡ÒÃÍèÒ¹ (Select) ä´é ·ÓãËéà¡Ô´¤ÇÒÁàÃçÇäÁèµéͧÃÍ¡ÒÃà¢Õ¹àÊÃ稡è͹àªè¹ã¹ MyISAM
ÊèǹÊÓ¤ÑÍÕ¡ÍÂèÒ§¤×Í InnoDB ä´éá¡¡Ò÷ӴѪ¹Õ (index) ÍÍ¡¨Ò¡µÒÃÒ§¢éÍÁÙÅáÅÐ·Ó Hashing ´Ñª¹ÕäÇé´éÇÂà·¤¹Ô¤¹Õé·ÓãËé¡Òäé¹ËÒà¢éÒ¶Ö§ä´éÃÇ´àÃçÇ
¨Ò¡¡Ò÷´Êͺ¤ÇÒÁàÃçǹÑ鹡Òà Query ¢éÍÁÙŨҡµÒÃÒ§¹éÍÂæ 2-3 µÒÃÒ§ MyISAM ÊÒÁÒö·Ó¤ÇÒÁàÃçÇä´é´Õ¡ÇèÒ áµèàÁ×èÍÁÕ¡Ò÷ӧҹ¡ÑºËÅÒµÒÃÒ§ >3 µÒÃÒ§¢Öé¹ä» â´Â੾ÒÐàÁ×èÍÁÕ¢éÍÁÙÅËÅÒÂÅéÒ¹ record ¢Öé¹ä»¹Ñé¹ InnoDB ÊÒÁÒö·Ó¤ÇÒÁàÃçÇä´é´Õ¡ÇèÒ à¾ÃÒÐà·¤¹Ô¤¡ÒÃ·Ó Hashing ´Ñª¹Õá¡äÇé¹Ñè¹àͧ
MyISAM ¹Ñé¹à¹×èͧ¨Ò¡äÁèÁÕ¡Òà Lock ÃдѺ Row ¨Ö§äÁèàËÁÒСѺ§Ò¹·ÕèÁÕ¡ÒÃà¢Õ¹¢éÍÁÙžÃéÍÁ¡Ñº¡ÒÃàÃÕ¡ãªéºèÍÂæ ¶éÒÁÕ¡ÒÃà¢Õ¹áÅСÒÃÍèÒ¹¾ÃéÍÁæ ¡Ñ¹¤ÇÃàÅ×Í¡ãªé InnoDB ´Õ¡ÇèÒ µèÍä»àÁ×èÍÁÕ¤ÇÒÁ¨Óà»ç¹µéͧãªé§Ò¹ MySQL ¤ÇÃàÅ×Í¡ Database Engine ãËéàËÁÒÐÊÁ¤ÃѺ
