MySQL database InnoDB and MyISAM differences and how to choose

MySQL database InnoDB and MyISAM differences and how to choose:

Difference:

1. InnoDB support transactions, MyISAM is not supported, for INNODB each SQL language is encapsulated as a transaction by default, auto-commit, which affects speed, so it is better to put multiple SQL language between Begin and commit, compose a transaction;

2. InnoDB supports foreign keys, while MyISAM is not supported. The conversion of a InnoDB table containing a foreign key to MyISAM will fail;

3. InnoDB is a clustered index, the data file is tied to the index, must have a primary key, through the primary key index efficiency is very high. However, the secondary index requires two queries, first querying the primary key, and then querying the data through the primary key. Therefore, the primary key should not be too large, because the primary key is too large and the other indexes will be large. While MyISAM is a nonclustered index, the data file is detached and the index holds a pointer to the data file. The primary key index and the secondary index are independent.

4. InnoDB does not save the exact number of rows in the table, and a full table scan is required to execute SELECT COUNT (*) from table. and MyISAM with a variable to save the entire table row number, the execution of the above statement only need to read out the variable, fast;

5. InnoDB does not support full-text indexing, while MyISAM supports full-text indexing, and the query efficiency is MyISAM high;

How to choose:

1. Whether to support transactions, if you want to choose InnoDB, if you do not need to consider myisam;

2. If the majority of the table is just read query, you can consider MyISAM, if both read and write are very frequent, please use InnoDB.

3. After the system MyISAM, it is more difficult to recover, can accept;

4. MySQL5.5 version start InnoDB has become the default engine of MySQL (previously MyISAM), stating that its advantages are obvious, if you do not know what to use, then use InnoDB, at least not bad.

Oscarwin
Links: https://www.zhihu.com/question/20596402/answer/211492971
Source: Know
Copyright belongs to the author. Commercial reprint please contact the author for authorization, non-commercial reprint please specify the source.

MySQL database InnoDB and MyISAM differences and how to choose