When to use MyISAM and InnoDB?

 

This question already has an answer here:

  • MyISAM versus InnoDB 27 answers

    • Read about Storage Engines .

    MyISAM:

    The MyISAM storage engine in MySQL.

  • Simpler to design and create, thus better for beginners. No worries about the foreign relationships between tables.
  • Faster than InnoDB on the whole as a result of the simpler structure thus much less costs of server resources. -- Mostly no longer true.
  • Full-text indexing. -- InnoDB has it now
  • Especially good for read-intensive (select) tables. -- Mostly no longer true.
  • Disk footprint is 2x-3x less than InnoDB's. -- As of Version 5.7, this is perhaps the only real advantage of MyISAM.

    • InnoDB:

    The InnoDB storage engine in MySQL.

  • Support for transactions (giving you support for the ACID property).
  • Row-level locking. Having a more fine grained locking-mechanism gives you higher concurrency compared to, for instance, MyISAM.
  • Foreign key constraints. Allowing you to let the database ensure the integrity of the state of the database, and the relationships between tables.
  • InnoDB is more resistant to table corruption than MyISAM.
  • Support for large buffer pool for both data and indexes. MyISAM key buffer is only for indexes.
  • MyISAM is stagnant; all future enhancements will be in InnoDB. This was made abundantly clear with the roll out of Version 8.0.

    • MyISAM Limitations:

  • No foreign keys and cascading deletes/updates
  • No transactional integrity (ACID compliance)
  • No rollback abilities
  • 4,284,867,296 row limit (2^32) -- This is old default. The configurable limit (for many versions) has been 2**56 bytes.
  • Maximum of 64 indexes per table

    • InnoDB Limitations:

  • No full text indexing (Below-5.6 mysql version)
  • Cannot be compressed for fast, read-only (5.5.14 introduced ROW_FORMAT=COMPRESSED )
  • You cannot repair an InnoDB table

    • For brief understanding read below links:

  • MySQL Engines: InnoDB vs. MyISAM – A Comparison of Pros and Cons
  • MySQL Engines: MyISAM vs. InnoDB
  • What are the main differences between InnoDB and MyISAM?
  • MyISAM versus InnoDB
  • What's the difference between MyISAM and InnoDB?
  • MySql: MyISAM vs. Inno DB!

    • Use MyISAM for very unimportant data or if you really need those minimal performance advantages. The read performance is not better in every case for MyISAM.

    I would personally never use MyISAM at all anymore. Choose InnoDB and throw a bit more hardware if you need more performance. Another idea is to look at database systems with more features like PostgreSQL if applicable.

    EDIT : For the read-performance, this link shows that innoDB often is actually not slower than MyISAM: http://www.mysqlperformanceblog.com/2007/01/08/innodb-vs-myisam-vs-falcon-benchmarks-part-1/

    链接地址: http://www.djcxy.com/p/52234.html

    上一篇: InnoDB或MyISAM会话？

    下一篇: 何时使用MyISAM和InnoDB？