The difference between MySQL storage engine InnoDB and MyISAM

InnoDB and MyISAM are the two most commonly used table types used by many people when using MySQL, each with its own pros and cons, depending on the specific application.

Basic differences: MyISAM types do not support advanced processing, such as transaction processing, but InnoDB type support. MyISAM type tables emphasize performance, which is performed more than InnoDB types faster, but does not provide transactional support, while InnoDB provides transactional support and advanced database functionality such as external keys.

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

The fulltext type index is not supported for 1.InnoDB.

The number of rows in the table is not saved in 2.InnoDB, that is, when the select count (*) from table is executed, InnoDB scans the entire table to calculate the number of rows, but MyISAM simply reads out the saved rows. Note that when the COUNT (*) statement contains the Where condition, the operations of the two tables are the same.

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

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

5.LOAD table from Master 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 additional InnoDB features (such as foreign key) of the table does not apply.

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

Main difference: The main difference between the two types is that InnoDB supports transaction processing with foreign keys and row-level locks. And MyISAM is not supported. So MyISAM are often considered only suitable for use in small projects.

As a user of MySQL, InnoDB and MyISAM are more like, if the database platform to achieve the requirements: 99.9% stability, convenient scalability and high availability, MyISAM is absolutely preferred.

The reasons are as follows:

1, most of the projects on the platform are read and write less items, and MyISAM read performance is much stronger than the InnoDB.

2, MyISAM index and data are separate, and the index is compressed, memory usage rate on the corresponding increase a lot. Can load more indexes, while InnoDB is tightly bundled with the index and data, no compression is used to create innodb larger than MyISAM.

3, often separated by 1, it's going to happen in 2 months. Application developers accidentally update a table where the scope is not correct, resulting in this table can not be used normally, this time the superiority of the MyISAM is reflected, casually from the copy of the day of the compressed package out of the corresponding table files, casually put into a database directory, Then dump into SQL and then lead back to the main library, and the corresponding binlog to fill up. If it's InnoDB, I'm afraid it's not going to be that fast. Let InnoDB periodically back up with the export xxx.sql mechanism, because the minimum amount of data for one database instance is dozens of g in size.

4, from the contact of the application logic, select COUNT (*) and order BY is the most frequent, probably accounted for the entire SQL total statement more than 60% of the operation, and this operation InnoDB actually will lock the table, many people think InnoDB is row-level lock, That's just where the primary key is valid and the Non-key key will lock the entire table.

5, there is often a lot of application departments need me to give them regular data on certain tables, MyISAM words is very convenient, as long as they correspond to the table frm. myd,myi files, let themselves in the corresponding version of the database to start on the line, and InnoDB need to export the xxx.sql, because the light to others file, by the dictionary data file, the other side is not available.

6, if and myisam than insert write operation, InnoDB also can not reach the MyISAM write performance, if it is for indexing update operation, although MyISAM may be inferior innodb, but so high concurrent write, from the library can chase is also a problem, It is better to solve this problem by using a multi-instance sub-table architecture.

7, if it is using MyISAM, the merge engine can greatly speed up the development of the application department, they simply do some select count (*) operations on the merge table, very suitable for a large project volume of about hundreds of millions of of a certain type of rows (such as logs, survey statistics) business table.

Of course InnoDB is also not absolutely not, use the project of the affairs to use InnoDB. In addition, some people may say that you myisam not be able to resist too much writing, but can be made up by the architecture.