MySQL Database engine MyISAM and InnoDB differences

1. Storage structure

MyISAM: Each MyISAM is stored on disk as three files. The first file name begins with the name of the table, and the extension indicates the file type. frm file stores the table definition. The data file has an extension of. MYD (MYData). The extension of the index file is. MYI (Myindex).
InnoDB: All tables are stored in the same data file (possibly multiple files, or stand-alone tablespace files), and the size of the InnoDB table is limited only by the size of the operating system file, typically 2GB.

2. Storage space

MyISAM: Can be compressed, storage space is small. supports three different storage formats: Static table (default, but note that there can be no space at the end of the data, it will be removed), dynamic tables, compressed tables.
InnoDB: Requires more memory and storage, it establishes its dedicated buffer pool in main memory for caching data and indexes.

3. Business Support

MyISAM: The emphasis is on performance, where each query is atomic and executes several times faster than the InnoDB type, but does not provide transactional support.
InnoDB: Provides advanced database features such as transaction support transactions, foreign keys, and so on. Transaction Security (Transaction-safe (ACID compliant)) Table with transaction (commit), rollback (rollback), and crash-repair capability (crash recovery capabilities).

4. Curd operation

MyISAM: If performing a large number of select,myisam is a better choice. (because there is no support for row-level locks), you need to lock the entire table when adding and deleting, the efficiency is lower. The related is that InnoDB supports row-level locks, deleting inserts only need to lock the row, the efficiency is high
InnoDB: If your data performs a large number of inserts or update, you should use the InnoDB table for performance reasons. Delete is InnoDB better on performance, but when you delete from table, InnoDB does not re-establish the table, but deletes one row at a time, and if you want to empty a table that holds a large amount of data on InnoDB, it is best to use the TRUNCATE TABLE command.

5. Foreign key

MyISAM: Not supported
InnoDB: Support

This article is from the "Little Fart Babe" blog, please make sure to keep this source http://865516915.blog.51cto.com/9037379/1904642

MySQL Database engine MyISAM and InnoDB differences