The difference between InnoDB and MyISAM in the MySQL storage engine

1. Transaction InnoDB supports transactional functions, MyISAM not supported.
Myisam executes faster and performs better.

2. Select, UPDATE, INSERT, delete 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, for performance reasons, you should use the InnoDB table
3. Different locking mechanisms

InnoDB is a row-level lock and MyISAM is a table-level lock.

Note: When the database is not determined, the row you are looking for is also locked into the entire table.
such as: Update table Set num = ten where username like "%test%";
4. The number of rows in the query table is different myisam:select count (*) from Table,myisam as long as the number of rows saved is simply read, note that when the COUNT (*) statement contains the Where condition, the operation of the two tables is the same

The exact number of rows in a table is not saved in Innodb:innodb, that is, when you execute select COUNT (*) from table, InnoDB scans the entire table to calculate how many rows
5. Physical Structure different 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.
The. 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: A disk-based resource is a InnoDB tablespace data file and its log file, and the size of the InnoDB table is limited only by the size of the operating system file, typically 2GB

6. Anto_increment mechanism different better and faster auto_increment processing
Other: Why MyISAM is faster than InnoDB's query. InnoDB When doing select, to maintain more than the MyISAM engine much more;
1) data block, InnoDB to cache, MyISAM only cache the index block, which also has the reduction of swap-out;
2) InnoDB addressing to map to the block, and then to the line, MYISAM record is directly the offset of the file, positioning is faster than InnoDB
3) InnoDB also need to maintain MVCC consistent; Although your scene is not, he still needs to check and maintain

The difference between InnoDB and MyISAM in the MySQL storage engine