Six major differences between Mysql storage engine InnoDB and Myisam: innodbmyisam

Source: Internet
Author: User
Tags table definition

Six major differences between Mysql storage engine InnoDB and Myisam: innodbmyisam




Differences in composition:


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 extension is. MYD (MYData ).

The extension 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.


Select update, INSERT,DeleteOperation
If you execute a large number of SELECT statements, MyISAM is a better choice.


1.If you execute a large amount of dataINSERTOrUPDATEFor performance considerations, InnoDB tables should be used

2. delete from tableInnoDB does not create a new table, but deletes a row.

3. LOAD TABLE FROM MASTERThe operation does not work for InnoDB. The solution is to first change the InnoDB table to the MyISAM table and then the InnoDB table after the data is imported. However, for the use of additional InnoDB features (such as foreign keys) the table of is not applicable.



Internal processing of an AUTO_INCREMEN column in each table.

MyISAMIsINSERTAndUPDATEThe operation automatically updates this column.. This makes the AUTO_INCREMENT column faster (at least 10% ). After the value at the top of the sequence is deleted, it cannot be reused. (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. However, in the MyISAM table, you can create a joint index with other fields.

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 growth counter is only stored in the primary memory, rather than on the disk.

For more information about the Algorithm Implementation of this calculator, see

AUTO_INCREMENTColumn inInnoDBHow to work


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.


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 executing an SQL statement, the InnoDB table will also lock the entire table, for example, update table set num = 1 where name like "% aaa %"

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.