The experience of MySQL use (v) InnoDB and MyISAM

This article mainly organized the MySQL two commonly used storage engine MYISAM,INNODB six major common differences:

The difference in composition

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

A disk-based resource is a innodb tablespace data file and its log files.

. user.frm storage Table structure.

USER.IBD stores data files.

Transaction processing aspects

MyISAM

Tables of the MyISAM type emphasize performance, which is performed more than the InnoDB type, but does not provide transactional support

InnoDB

InnoDB provides transaction support transactions, external key and other advanced database functions

SELECT Update,insert,delete Action

MyISAM

If performing a lot of Select,myisam is a better choice

InnoDB

1. If your data performs a large number of inserts or updates, for performance reasons, you should use the InnoDB table

2.DELETE from table, InnoDB does not re-establish the table, but deletes one row at a time.

3.LOAD table from master (one way to copy the data from the primary server to the server is to make) the operation does not work for InnoDB, the solution is to first change the InnoDB table to MyISAM table, import the data and then change to InnoDB table, but for the use of The table of additional InnoDB attributes (such as foreign keys) does not apply

Operations on Auto_increment

MyISAM

Internal processing of one auto_incremen column per table.

MyISAM automatically updates this column for insert and update operations. This makes the Auto_increment column faster (at least 10%). The value at the top of the sequence cannot be used again after it has been deleted. (When the Auto_increment column is defined as the last column of a multiple-column index, you can have a situation where the value deleted from the top of the sequence is reused).

Auto_increment value can be reset by ALTER TABLE or MYISAMCH

For a field of type auto_increment, InnoDB must contain an index with only that field, but in the MyISAM table, you can establish a federated index with the other fields.

InnoDB

If you specify a auto_increment column for a table, the InnoDB table handle in the data dictionary contains a counter called the automatic growth counter, which is used to assign a new value to the column.

The automatic growth counter is stored only in main memory, not on disk

Reference: http://kb.cnblogs.com/page/82976/

The exact number of rows in the table

MyISAM

The Select COUNT (*) from Table,myisam simply reads out the number of saved rows, noting that the operations of the two tables are the same when the count (*) statement contains the WHERE condition.

InnoDB

The exact number of rows for the table is not saved in the InnoDB, that is, when the select count (*) from table is executed, InnoDB scans the entire table to calculate how many rows

Lock

MyISAM

Table lock

InnoDB

Provides row locks (locking on row level), providing no lock reads consistent with Oracle type (non-locking read in

Selects), in addition, row locks on innodb tables are not absolute, and if MySQL cannot determine the range to scan when executing an SQL statement, the InnoDB table also locks the entire table, such as Update table set num=1 where name like "% aaa% "

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/MySQL/