The difference between MyISAM and InnoDB

MyISAM and InnoDB explanation

InnoDB and MyISAM are the two most common table types used by many people when using MySQL, both of which have pros and cons, depending on the application. The basic difference is that the MyISAM type does not support advanced processing such as transaction processing, and InnoDB type support. The MyISAM type of table emphasizes performance, which is performed more quickly than the InnoDB type, but does not provide transactional support, while InnoDB provides advanced database functionality such as transactional support and external keys.

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

1. InnoDB does not support indexes of type Fulltext.

2. 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 reads the saved rows. Note that when the COUNT (*) statement contains a where condition, the operation of the two tables is the same.

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

4. Delete from table, InnoDB does not reestablish the table, but deletes one row at a time.

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

In addition, the row lock of the InnoDB table is not absolute, 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%"

　　The main difference between the two types is that InnoDB supports transactional and foreign key and row level locks. and MyISAM does not support it. So MyISAM tend to be considered only suitable for use in small projects.

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

The reasons are as follows:

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

2, MyISAM index and data are separate, and the index is compressed, the memory usage of the corresponding improved a lot. Can load more indexes, and InnoDB is the index and the data is tightly bound, do not use compression which will cause innodb than MyISAM volume is large.

3, often 1, 2 months will occur application developers accidentally update a table where the scope of the wrong, resulting in this table can not be normal use, this time the superiority of the MyISAM is reflected, casually from the day of the copy of the compressed package out of the corresponding table file, casually put into a database directory, Then dump into SQL and then back to the main library, and the corresponding binlog complement. If it's InnoDB, I'm afraid it can't be so fast, don't tell me to let InnoDB regularly back up with an export xxx.sql mechanism, because the minimum amount of data for a database instance is roughly dozens of g in size.

4, from the application logic of contact, select COUNT (*) and order BY is the most frequent, probably can account for the entire SQL total statement of more than 60% of the operation, and this operation InnoDB actually will lock the table, many people think InnoDB is a row-level lock, the only Where the primary key is valid, the non-primary key will lock the full table.

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

6, if and myisam than insert write operation, InnoDB also not up to MyISAM write performance, if is for index-based update operation, although MyISAM may be inferior innodb, but so high concurrency of write, from the library can chase is also a problem, It might as well be solved by a multi-instance sub-Library table architecture.

7, if it is used MyISAM, the merge engine can greatly speed up the development of the application department, they just do some select count (*) operation on this merge table, it is very suitable for a large project total of about hundreds of millions of rows of a type (such as log, survey statistics) business table.

Of course InnoDB is not absolutely not used, the project with the business is used InnoDB. In addition, someone might say that you myisam not be able to write too much, but you can make up for it through architecture.

The difference between MyISAM and InnoDB