Mysql Storage Engine MyISAM and InnoDB differences in the detailed _mysql

In the process of using MySQL, there are some questions about the concepts of MyISAM and InnoDB, and the difference between the two engines has always been a question in my mind. To solve this puzzle, we searched the network and found the following information:

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 mechanism, and then gradually replace MyISAM.

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, the InnoDB feature is the ability to support acid-compliant transactions (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 SELECT COUNT (*) from table, the entire table is traversed, consuming considerable amounts, but after adding wehre conditions, 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
Through the above analysis, we can basically consider using InnoDB to replace the MyISAM engine, because InnoDB itself many good features, such as transaction support, stored procedures, views, row-level locking, and so on, in many cases, I believe InnoDB's performance is certainly much stronger than MyISAM's. In addition, any kind of table is not omnipotent, only appropriate for the business type to choose the appropriate table type, in order to maximize the performance of MySQL advantage. If not very complex web applications, non-critical applications, can continue to consider MyISAM, this specific situation can be considered.