MyISAM and InnoDB differences in MySQL storage engine

MyISAM is the default database engine for MySQL (prior to version 5.5), which was improved by the early ISAM (Indexed sequential access method: Indexed sequential access methods). Although performance is excellent, there is one drawback: Transaction processing (transaction) is not supported. However, over the years, MySQL has also introduced InnoDB (another database engine) to enhance referential integrity and concurrency violation handling mechanisms, and later gradually replaced MyISAM.

InnoDB, one of MySQL's database engines, publishes one of the binary standards for MySQL AB. InnoDB was developed by Innobase Oy Company and was acquired by Oracle Corporation in May 2006. Compared with the traditional ISAM and MyISAM, InnoDB's greatest feature is the support for acid-compatible transaction (Transaction) functions, similar to PostgreSQL. At present, InnoDB adopts dual-track licensing, one is the GPL license, the other is proprietary software licensing.

What is the difference between MyISAM and InnoDB?

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. Portability, Backup and recovery

MyISAM: Data is stored as a file, so it is convenient for cross-platform data transfer. You can operate on a table separately for backup and recovery.
InnoDB: The free solution can be copying data files, backing up Binlog, or using mysqldump, which is relatively painful when the amount of data reaches dozens of G.

4. 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).

5, Auto_increment

MyISAM: You can establish a federated index with other fields. The auto-grow column of the engine must be an index, and if it is a composite index, autogrow may not be the first column, and he can be incremented based on the preceding columns.
The Innodb:innodb must contain only the index of the field. The auto-grow column of the engine must be an index, and if it is a combined index, it must be the first column of the combined index.

6. Table Lock Difference

MyISAM: Only table-level locks are supported, and when the user operates the MyISAM table, the Select,update,delete,insert statement automatically locks the table, and if the locking table satisfies insert concurrency, new data can be inserted at the end of the table.
InnoDB: Supports transactional and row-level locks, which are the InnoDB of the most important features. Row locks greatly improve the new performance of multiuser concurrency. But the innodb of a row lock, just where the primary key is valid, where the non-primary key is locked in the full table.

7. Full-Text Indexing

MyISAM: Supports full-text indexing of fulltext types
InnoDB: Full-text indexing of fulltext type is not supported, but InnoDB can support full-text indexing using Sphinx plug-in and works better.

8. Table PRIMARY Key

MyISAM: Allows tables without any indexes and primary keys to exist, and the index is the address of the save row.
InnoDB: If no primary key or non-null unique index is set, a 6-byte primary key is automatically generated (not visible to the user), the data is part of the primary index, and the attached index holds the value of the primary index.

9. The exact number of rows in the table

MyISAM: The total number of rows that have a table saved, if select count (*) from table, the value is taken out directly.
InnoDB: The total number of rows in the table is not saved, and if you use SELECT COUNT (*) from table, the entire table is traversed, consuming considerable amounts, but the MyISAM and InnoDB are handled the same way when the wehre condition is added.

10. Curd operation

MyISAM: If performing a large number of select,myisam is a better choice.
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.

11. Foreign key

MyISAM: Not supported
InnoDB: Support
Through the above analysis, can basically consider using InnoDB to replace the MyISAM engine, because InnoDB itself a lot of good features, such as transaction support, stored procedures, views, row-level locking and so on, in many cases of concurrency, I believe InnoDB's performance is certainly much stronger than MyISAM. In addition, any kind of table is not omnipotent, only appropriate for the business type to choose the appropriate table type, to maximize the performance advantage of MySQL. If not a very complex web application, non-critical applications, or can continue to consider MyISAM, this specific situation can be considered at its own discretion.

Original link: http://www.jb51.net/article/62457.htm

MyISAM and InnoDB differences in MySQL storage engine