Comparison of MyISAM and InnoDB

InnoDB with the Myisam The six major differences
	MyISAM	InnoDB
The difference in composition:	Each myisam is stored as three files on disk. 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).	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
transaction Processing Aspects :	Tables of the MyISAM type emphasize performance, which is performed more quickly than the InnoDB type, but does not provide transactional support	InnoDB provides transaction support transactions, external keys and other advanced database functions
Selectupdate,insert , Delete Action	If you do a lot of select,myisam it's better to choose	1. If your data performs a large number of INSERT or UPDATE , you should use the InnoDB table   for performance reasons; 2.DELETE from table , InnoDB does not reestablish the table, but deletes one row at a time. The 3.LOAD table from MASTER operation has no effect on InnoDB, and the workaround is to first change the InnoDB table to a MyISAM table, import the data, and then change to the InnoDB table. However, tables that use additional InnoDB attributes, such as foreign keys, are not available for
pair auto_increment Action for	The internal processing of one auto_incremen column per table.   MyISAM for INSERT and UPDATE The action automatically updates this column . This makes the Auto_increment column faster (at least 10%). The value at the top of the sequence cannot be exploited after it has been deleted. (When the Auto_increment column is defined as the last column of a multicolumn index, the use of values removed from the top of the sequence can occur). The Auto_increment value can be used by ALTER TABLE or MYISAMCH to reset For fields of type auto_increment, InnoDB must contain only the index of that field, but in the MyISAM table, You can establish a federated index with other fields Better and faster auto_increment processing	If you specify a auto_increment column for a table, the InnoDB table handle in the data dictionary contains a counter called the autogrow counter, which is used to assign a new value to the column.   The autogrow counter is stored only in main memory, not on disk For an algorithmic implementation of this calculator, refer to auto_increment column in InnoDB How to work
The exact number of rows in the table	Select COUNT (*) from Table,myisam as long as you simply read the number of rows saved, note that when the COUNT (*) statement contains a where condition, the operation of the two tables is the same	The exact number of rows in a table is not saved in InnoDB, that is, when you execute select COUNT (*) from table, InnoDB scans the entire table to calculate how many rows
Lock	Table lock	Provides row lock (locking on row level), providing no lock read consistent with Oracle type (non-locking read in Selects), in addition, the row lock of the InnoDB table is not absolute, and if MySQL cannot determine the range to scan when executing an SQL statement, the InnoDB table also locks the full table, such as the Update table set num=1 where name like "% aaa% "
The original source of this article is www.dbahacker.com reprint please keep the link

Comparison of MyISAM and InnoDB