Differences between mysql database engine MyISAM and InnoDB

1. Storage structure

MyISAM: Each MyISAM is stored as three files on disk. The name of the first file starts with the name of the table and the extension indicates the file type. Frm files store table definitions. Data files have the extension.MYD (MYData). The index file extension is.MYI (MYIndex).

InnoDB: All tables are stored in the same data file (or multiple files, or separate table space files), and the size of the InnoDB table is limited only to the size of the operating system file, which is generally 2 GB.

2. Storage space

MyISAM: Can be compressed, storage space is small. Support three different storage formats: static table (default, but note that there can be no spaces at the end of the data, it will be removed), dynamic table, compressed table.

InnoDB: Requires more memory and storage, it builds its own dedicated buffer pool in main memory for caching data and indexes.

3. Business support

MyISAM: The emphasis is on performance, atomicity per query, which executes several times faster than the InnoDB type, but does not provide transaction support.

InnoDB: Provides advanced database functions such as transaction support transactions, external keys, etc. A transaction-safe (ACID compliant) type table with commit, rollback, and crash recovery capabilities.

4. CURD operation

MyISAM: MyISAM is the better choice if you perform a lot of SELECT. (Because there is no support for row-level locking), the entire table needs to be locked when adding or deleting, and the efficiency will be lower. Related is innodb support row level lock, delete insert only need to lock the line, high efficiency

InnoDB: If your data does a lot of INSERT or UPDATE, you should use InnoDB tables for performance reasons. Delete is better than InnoDB in terms of performance, but when Deleting FROM table, InnoDB does not rebuild the table, but deletes it row by row. If you want to empty the table with a large amount of data on innodb, it is best to use the truncate table command.

5. Foreign key

MyISAM: Not supported

InnoDB: Support