Mysql's storage engine, MyISAM and InnoDB difference _mysql

A simple expression.
MyISAM is a non transactional storage engine.
InnoDB is a storage engine that supports transactions.

InnoDB engines are more suitable for inserting and updating operations than more applications
and MyISAM is suitable for frequent query application.

MyISAM--table lock.

innodb--design Reasonable words are row lock.
MyISAM does not appear to be deadlocked.

The biggest difference is that MyISAM is suitable for small data, small concurrency, INNODB for large data, and large concurrency. The biggest difference is in the level of the lock.

The MyISAM type does not support advanced processing, such as transaction processing, but InnoDB type support. Tables of the MyISAM type emphasize performance, which executes more than INNODB types faster, but does not provide transactional support, while InnoDB provides transactional support for advanced database features such as foreign keys. Review, you can use different types of storage depending on the data table's different uses. And MyISAM is file stored and can be used directly between different operating systems.

InnoDB:

InnoDB provides MySQL with transaction security (commit), rollback (rollback), and crash repair (crash recovery capabilities) (Transaction-safe (ACID compliant ) Type table. InnoDB provides a row lock (locking on row level) that provides an unlocked read (non-locking read in selects) consistent with the Oracle type. These features improve the performance of multi-user concurrency operations. You do not need to extend the lock (lock escalation) in the InnoDB table because InnoDB column locks (row level locks) are suitable for very small spaces. InnoDB is the first table engine on MySQL that provides a foreign key constraint (FOREIGN key constraints). InnoDB's design goal is to handle a large-capacity database system, which is not comparable to other disk-based relational database engines. Technically, InnoDB is a complete database system in the MySQL background, InnoDB to build its dedicated buffer pool in main memory for caching data and indexing. InnoDB the data and index in a tablespace, possibly containing multiple files, unlike other, for example, in MyISAM, tables are stored in separate files. The size of the InnoDB table is limited to the file size of the operating system, typically 2 GB. InnoDB All tables are stored in the same data file ibdata1 (or multiple files, or separate tablespace files), relatively difficult to backup, can copy files or use Navicat for MySQL.

MyISAM

Each MyISAM table is stored in three files: the frm file is stored in a table definition. The data file is MyD (MYData). Index files are myi (myindex) extensions.
Because the MyISAM is relatively simple, so it is better than innodb in efficiency, small application use MyISAM is a good choice.
MyISAM tables are saved in the form of files, and using MyISAM storage in Cross-platform data transfer saves a lot of trouble

The following are some details and specific implementations of the differences:

The fulltext type index is not supported by the

1.InnoDB.
2.InnoDB does not save the exact number of rows in the table, that is, when the select count (*) from table is executed, InnoDB scans the entire table to calculate the number of rows, but MyISAM simply reads out the saved rows. Note that when the COUNT (*) statement contains the Where condition, the operations of the two tables are the same.
3. For auto_increment type fields, InnoDB must contain an index with only that field, but in the MyISAM table, you can establish a federated index with the other fields.
4.DELETE from table, InnoDB does not re-establish the table, but deletes one row at a time. The
5.LOAD table from master operation does not work for InnoDB, and the solution is to first change the InnoDB table to a MyISAM table, import the data and then change it to a InnoDB table. However, tables that use additional InnoDB attributes, such as foreign keys, do not apply.