The difference between MyISAM and InnoDB

MySQL has multiple storage engines, MyISAM and InnoDB are two commonly used ones. Here are some basic concepts about these two engines (not an in-depth introduction).

MyISAM is the default storage engine of MySQL, based on the traditional ISAM type, supports full-text search, but is not transaction-safe and does not support foreign keys. Each MyISAM table is stored in three files: the frm file stores the table definition; the data file is MYD (MYData); the index file is MYI (MYIndex).

InnoDB is a transactional engine that supports rollback, crash recovery, multi-version concurrency control, ACID transactions, and row-level locking (the row lock of the InnoDB table is not absolute. If MySQL cannot determine to scan when executing a SQL statement Within the scope, the InnoDB table will also lock the entire table, such as the SQL statement during the like operation). InnoDB stores its tables and indexes in a tablespace, which can contain several files.

Main difference:

MyISAM is non-transactionally safe, while InnoDB is transactionally safe.

MyISAM lock granularity is table level, while InnoDB supports row-level locking.

MyISAM supports full-text type index, but InnoDB does not support full-text index.

MyISAM is relatively simple, so it is better than InnoDB in terms of efficiency. Small applications can consider using MyISAM.

MyISAM tables are saved in the form of files. Using MyISAM storage in cross-platform data transfer will save a lot of trouble.

InnoDB tables are more secure than MyISAM tables. You can switch non-transactional tables to transactional tables (alter table tablename type=innodb) while ensuring that data will not be lost.

Application scenario:

MyISAM manages non-transaction tables. It provides high-speed storage and retrieval, as well as full-text search capabilities. If your application needs to perform a large number of SELECT queries, MyISAM is a better choice.

InnoDB is used for transaction processing applications and has numerous features, including ACID transaction support. If your application needs to perform a large number of INSERT or UPDATE operations, you should use InnoDB, which can improve the performance of multi-user concurrent operations.