The difference between database storage engine MyISAM and InnoDB

InnoDB and MyISAM are the two most commonly used table types used by many people when using MySQL, each with its own pros and cons, depending on the specific application. The basic difference is that the MyISAM type does not support advanced processing, such as transaction processing, but InnoDB type support. The MyISAM type table emphasizes performance, which is faster than the InnoDB type, but does not provide transactional support, and InnoDB provides transactional support and advanced database functionality such as external keys.
MyISAM is the default database engine for MySQL (before version 5.5), 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, in these years of development, MySQL has also imported InnoDB (another database engine) to enhance referential integrity and concurrency violation processing mechanisms.
InnoDB, one of MySQL's database engines, is one of the criteria for publishing binary for MySQL AB. InnoDB was developed by Innobase Oy Corporation and was acquired by Oracle in May 2006. Compared with traditional ISAM and MyISAM, InnoDB's greatest feature is its ability to support acid-compatible things (Transaction), similar to PostgreSQL. At present, the InnoDB adopts dual-track authorization, one is the GPL authorization and the other is proprietary software authorization.
What is the difference between MyISAM and InnoDB.
1, storage structure
MyISAM: Each MyISAM is stored on disk as three files. The first file begins with the name of the table, and the extension indicates the file type.. frm file storage table definition. The data file has an extension of. MyD (MYData). The name of the index file extension is. Myi (Myindex).
InnoDB: All tables are kept in the same data file (possibly multiple files, or separate tablespace files), the size of the InnoDB table is limited to the size of the operating system file, typically 2GB.
2, storage space
MyISAM: Can be compressed, small storage space. supports three different storage formats: Static tables (default, but note that there is no space at the end of the data, will be removed), dynamic table, compressed table.
InnoDB: More memory and storage is needed, and it creates its own dedicated buffer pool in main memory for caching data and indexing.
3, portability, backup and recovery
MyISAM: Data is stored in the form of a file, so it is convenient to cross platform data transfer. You can operate on a table individually for backup and recovery.
InnoDB: The free scheme can be to copy data files, backup Binlog, or mysqldump, when the amount of data reaches dozens of G is relatively painful.
4. Transaction support
MyISAM: Emphasis is on performance, each time the query is atomic, its execution is more than the InnoDB type faster, but does not provide transactional support.
InnoDB: Provides transaction support transactions, foreign key and other advanced database functions. Transaction Security (Transaction-safe (ACID compliant)) Table with transaction (commit), rollback (rollback), and crash repair capability (crash recovery capabilities).
5, Auto_increment
MyISAM: A federated index can be established with other fields. The engine's automatic growth column must be an index, and if it is a combined index, automatic growth can be not the first column, and he can increment it by sorting the previous columns.
The Innodb:innodb must contain only the index of the field. The engine's automatic growth column must be an index, and if it is a combined index, it must be the first column of the composite Index.
6. Table Lock Difference
MyISAM: Only table-level locks are supported, and the Select,update,delete,insert statement automatically locks the table when the user operates the MyISAM table, and can insert new data at the end of the table if the lock table meets the insert concurrency.
InnoDB: Supports transaction and row-level locks, which is the most characteristic of InnoDB. Row lock greatly improves the new energy of multi-user concurrent operation. However, the InnoDB row lock is only valid when the primary key of the where is not the primary key where all the tables are locked.
7. Full-Text Indexing
MyISAM: Support for full-text indexing of fulltext types
InnoDB: Fulltext type Full-text indexing is not supported, but InnoDB can use Sphinx plug-ins to support Full-text indexing and is more effective.
8, table primary key
MyISAM: Allows tables with no indexes and primary keys to exist, and indexes are the addresses that hold rows.
InnoDB: If you do not set a primary key or a Non-empty unique index, a 6-byte primary key is automatically generated (the user is not visible), the data is part of the primary index, and the additional index holds the value of the primary index.
9, the specific number of rows of the table
MyISAM: Saves the total number of rows in the table, if select COUNT (*) from table, and the value is extracted directly.
InnoDB: The total number of rows for the table is not saved, and if you use the Select COUNT (*) from table, the entire table is traversed, consuming quite a lot, but after the where condition is added, MyISAM and InnoDB are treated the same way.
10, curd operation
MyISAM: If performing a lot of Select,myisam is a better choice.
InnoDB: If your data performs a large number of inserts or updates, you should use InnoDB tables for performance reasons. Delete is InnoDB better on performance, but when you delete the From table, InnoDB does not re-establish the table, it is a row of deletes, and it is best to use the TRUNCATE TABLE command if you want to empty the table that holds a large amount of data on InnoDB.
11, FOREIGN key
MyISAM: Not supported
InnoDB: Support

Generally speaking, MyISAM is suitable for:
Do a lot of count calculations; Inserts are infrequent, queries are very frequent; there are no transactions.

InnoDB Suitable for:
Reliability requirements are high, or transactions are required, table updates and queries are quite frequent, and table locking opportunities are relatively large.