The difference between MyISAM and InnoDB

1.Mysql charts for each engine

2.MyISAM engine

MyISAM: This is an older version of the default engine type, which is based on the traditional ISAM type,ISAM is an abbreviation for indexed sequential access method (with indexed sequential access methods ), It is the standard way to store records and files. Compared to other storage engines, MyISAM has most of the tools for checking and repairing tables. MyISAM Tables can be compressed , and they support full-text search . They are not transaction-safe and do not support foreign keys . If the transaction rollback will result in incomplete rollback , there is no atomicity . If you perform a large number of select,MyISAM is a better choice .

Myiasm is a new version of the Iasm table, with the following extensions:

• Portability at the binary level.
• NULL column index .
• There is less fragmentation than the ISAM table for variable-length rows.
• Support for large files.
• Better index compression.
• A better key? Statistical distribution.
• Better and faster auto_increment processing.

3.InnoDB engine

InnoDB: This is the new version of the default engine type, which is transaction-safe . It has the same characteristics as the BDB type, and they also support foreign keys . The InnoDB table is fast and has a richer feature than BDB, so it is recommended if a transaction-safe storage engine is required. If your data performs a large number of inserts or UPDATE, for performance reasons, you should use the InnoDB table . For INNODB types of tables that support things, the main reason for the speed is that the autocommit default setting is open, and the program does not explicitly call begin to start a transaction, resulting in an automatic commit for each insert, which seriously affects the speed. You can call begin before you execute SQL , and Multiple SQL forms A transaction (even if the autocommit is open), which will greatly improve performance .

comparison between 4.InnoDB and MyISAM

You can see the following table for details:

	MyISAM	InnoDB
The difference in composition	Each myisam is stored as three files on disk. The file name is the table name and the extension is file type. . frm file storage table definition; . MYD (MYData) data file extension; . MYI (myindex) extension of the index file.	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 executes faster than the InnoDB type, but does not provide transactional support.	INNODB provides advanced database features such as transaction support transactions , external keys, and more.
Lock	Table-Level Locks	Row-level Locks The row lock of the InnoDB table is also not absolute, and if MySQL cannot determine the scope 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%"
Select, insert, UPDATE, delete operations	If executing a lot of select,myisam is a better choice.	1. If your data performs a large number of inserts or update, for performance reasons, you should use the InnoDB table. 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 it to a InnoDB table, but not for tables that use additional InnoDB features, such as foreign keys.
For fields of type auto_increment	Must contain only the index of the field	You can establish a federated index with other fields
		InnoDB does not support indexes of type Fulltext.
	MyISAM-type binary data files can be migrated in different operating systems

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

2. InnoDB fulltext type not supported The index .
4. 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, but MyISAM simply read out the number of saved rows . Note that when the COUNT (*) statement contains a where condition, the operation of the two tables is the same.
6. For a field of type auto_increment , theInnoDB must contain only the index of the field , but in the MyISAM table, You can establish a federated index with other fields.
8. Delete from table ,InnoDB does not reestablish the table , but deletes one row at a time.
10. The LOAD table from MASTER operation has no effect on InnoDB , the workaround is to first change the InnoDB table to MyISAM table, import the data and then change to the InnoDB table, However, tables that use additional InnoDB attributes, such as foreign keys, do not apply.

In summary, any kind of table is not omnipotent, only appropriate for the business type to choose the appropriate table type, to maximize the performance advantage of MySQL.

The difference between MyISAM and InnoDB