Main differences between MySQL storage engine MyISAM and InnoDB _ MySQL

Comparison between MySQL storage engine MyISAM and InnoDB

This article lists the six common differences between Mysql storage engines MyISAM and InnoDB, which are derived from Mysql manuals and Internet materials.

Six major differences between InnoDB and Myisam
	MyISAM	InnoDB
Difference in construction:	Each MyISAM is stored as three files on the disk. The name of the first file starts with the name of the table. the extension indicates the file type. . Frm file storage table definition. The data file name is. MYD (MYData ). The extension name of the index file is. MYI (MYIndex ).	Disk-based resources are InnoDB tablespace data files and their log files. The InnoDB table size is limited by the operating system file size, generally 2 GB
Transaction processing:	MyISAM tables emphasize performance, and the execution speed is faster than that of InnoDB tables, but transactions are not supported.	InnoDB provides advanced database functions such as transactions and external keys.
SELECTUPDATE, INSERT, and Delete operations	If you execute a large number of SELECT statements, MyISAM is a better choice.	1. if you execute a large number of INSERT or UPDATE operations on your data, you should use the InnoDB table for performance considerations. 2. when deleting FROM table, InnoDB does not create a new table, but deletes a row. 3. the load table from master operation does not work for InnoDB. the solution is to first change the InnoDB TABLE to the MyISAM TABLE, and then change the imported data to the InnoDB TABLE, however, it is not applicable to tables that use additional InnoDB features (such as foreign keys ).
Operation on AUTO_INCREMENT	Internal processing of an AUTO_INCREMEN column in each table. MyISAM performs automatic UPDATE for the INSERT and UPDATE columns. This makes the AUTO_INCREMENT column faster (at least 10% ). It cannot be reused after the value at the top of the sequence is deleted. (When the AUTO_INCREMENT column is defined as the last column of the multi-column index, the deleted value from the top of the sequence can be reused ). The AUTO_INCREMENT value can be reset using alter table or myisamch. For fields of the AUTO_INCREMENT type, InnoDB must contain only the index of this field, but it is in the MyISAM table and can be joined with other fields to create a composite index. Better and faster auto_increment processing	If you specify an AUTO_INCREMENT column for a table, the InnoDB table handle in the data dictionary contains a counter named automatic growth counter, which is used to assign new values to the column. The automatic increase counter is only stored in the primary memory, rather than on the disk. For more information about the algorithm implementation of this calculator, see How does the AUTO_INCREMENT column work in InnoDB?
The number of rows in the table.	Select count (*) from table, MyISAM simply reads the number of rows saved. Note that when the count (*) statement contains the where condition, the operations of the two tables are the same.	InnoDB does not store the specific number of rows in the table. that is to say, when you execute select count (*) from table, InnoDB needs to scan the entire table to calculate the number of rows.
Lock	Table lock	Provides locking on row level and non-locking read in SELECTs). In addition, the row lock of the InnoDB table is not absolute. if MySQL cannot determine the scope to be scanned when an SQL statement is executed, the InnoDB table also locks the entire table, for example, update table set num = 1 where name like "% aaa %"
The original source of this article is www.dbahacker.com.

BitsCN.com