This article has participated in the “Digitalstar Project” and won a creative gift package to challenge the creative incentive money.

Preface ❤

What is the difference between MyISAM and InnoDB? At that time, we memorized and forgot again and again, so that many students were often stuck by this problem when looking for internship jobs. So today, I will talk about the difference between MyISAM and InnoDB systematically, so that you can understand it thoroughly!

🧡MySQL default storage engine changes

If you are familiar with MySQL, you know that in MySQL 5.1, the default search engine is MyISAM. In MySQL 5.5, the default search engine has been changed to InnoDB. This also indirectly shows that MySQL official preferred to use InnoDB.

💛MyISAM and InnoDB storage engine key features

💚 MyISAM

MyISAM storage engine is characterized by table-level locking, no transaction support and full-text indexing, which is suitable for some CMS content management systems to use as background database. However, the characteristics of table locking structure are inadequate in large concurrency and heavy load production systems. The following figure shows the MySQL 5.7 MyISAM storage engine version features.

💙 InnoDB

InnoDB storage engine features row-level locking, acid-compatible transaction security, support for foreign keys, and no FULLTEXT index (since 5.6.4, FULLTEXT index is supported). InnoDB Storage Engine provides a transaction-safe storage engine with commit, rollback, and crash recovery capabilities. InnoDB is designed for maximum performance when handling large volumes. Its CPU efficiency is probably unmatched by any other disk-based relational database engine. The following are the release features of the MySQL 5.7 InnoDB storage engine.

💜MyISAM and InnoDB performance testing

MyISAM or InnoDB which performance is higher, actually the official has given the pressure map

In fact, the blind result is: MyISAM is rubbed directly on the ground by InnoDB!

🤎 Whether transactions are supported

MyISAM is a non-transactional engine (it does not support transactions), enabling MyISAM engine MySQL to provide high-speed storage and retrieval, as well as full-text search capabilities, suitable for data warehousing and other query-intensive applications.

InnoDB is transaction safe (supports transaction), transaction is a kind of advanced processing mode, such as in some column add, delete and change as long as any error can be rolled back to restore, but MyISAM does not.

🖤MyISAM with InnoDB table and row locks

MySQL Table level locks have two modes: Table Read Lock and Table Write Lock. MyISAM does not block read requests from other users to the same table, but blocks writes to the same table. Write operations on MyISAM tables will block other users’ read and write operations on the same table.

InnoDB row locks are implemented by locking index entries, i.e. InnoDB uses row locks only if data is retrieved by index criteria, otherwise it uses table locks! Row-level locks consume more resources per lock acquisition and release operation than table locks. When InnoDB deadlocks two transactions, it counts the number of rows affected by each transaction and rolls back the one with fewer rows. Innodb cannot detect locked scenarios that do not involve Innodb. This can only be resolved by a lock timeout.

💔 Whether to save the number of rows in a database table

InnoDB does not store the exact number of rows in a table, that is, when performing select count(*) from table, InnoDB scans the entire table to calculate the number of rows, but MyISAM simply reads the number of rows saved.

💕 How to choose

While InnoDB is good, it is not a brainless choice, in some cases MyISAM is better than InnoDB!

MyISAM is suitable for:

  1. Do a lot of count calculations;
  2. Inserts are infrequent, queries are frequent, and MyISAM is a better choice if you perform a lot of SELECT;
  3. There are no transactions.

InnoDB for:

  1. High reliability requirements, or transaction requirements;
  2. Table updates and queries are fairly frequent, and the chances of locking the table are relatively high to specify the creation of the data engine;
  3. If your data performs a lot of INSERTS or updates, you should use InnoDB tables for performance reasons.
  4. When DELETE FROM table, InnoDB does not re-create the table, but deletes it row by row.
  5. LOAD TABLE FROM MASTER does not work for InnoDB. The solution is to change the InnoDB TABLE to MyISAM first and then to InnoDB after importing data, but it does not work for tables that use additional InnoDB features (such as foreign keys).