web123456

What are the differences between MyISAM and InnoDB in Mysql?

Article Directory

  • 1. Definition
    • InnoDB：
    • MyISAM：
  • 2. Differences:
  • 3. Use:

1. Definition

InnoDB：

InnoDB：MySQLThe default transactional engine is also the most important and widely used storage engine.

It is designed to be a large number of short-term transactions, which are mostly committed normally and rarelyrollback. InnoDBperformanceWith automatic crash recovery features, it also makes it very popular in non-transactional storage requirements. Unless there are very special reasons for using other storage engines, the InnoDB engine should be given priority.

MyISAM：

MyISAM: In MySQL 5.5 and previous versions, MyISAM is the default engine.

MyISAM provides a large number of features, including full-text indexing, compression, and spacefunction(GIS) etc., but MyISAM does not support transactional and row-level locks, and one unquestionable flaw is the inability to recover safely after a crash. It is precisely because of the MyISAM engine that even though MySQL has supported transactions for a long time, in many people's concepts, MySQL is still a non-transactional database. Despite this, it is not useless. For read-only data, or the table is relatively small and can tolerate repair operations, you can still use MyISAM (but please do not use MyISAM by default, but InnoDB by default)

2. Differences:

  1. InnoDB supports transactions, MyISAM does not support transactions. This is one of the important reasons why MySQL changes the default storage engine from MyISAM to InnoDB;
  2. InnoDB supports foreign keys, but MyISAM does not. Converting an InnoDB table with a foreign key to MYISAM will fail;
  3. InnoDB is a clustered index, and MyISAM is a non-clustered index. The file of clustered index is stored on the leaf nodes of the primary key index, so InnoDB must have a primary key, and the primary key indexing is very efficient. However, the auxiliary 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 other indexes will be very large. MyISAM is a non-clustered index, the data file is separated, and the index saves a pointer to the data file. Primary key index and secondary index are independent.
  4. InnoDB does not save the specific number of rows in the table. When executing select count(*) from table, the full table scan is required.. MyISAM uses a variable to save the number of rows in the entire table. When executing the above statement, it only needs to read out the variable, which is very fast;
  5. The smallest lock particle size of InnoDB is the row lock, and the smallest lock particle size of MyISAM is the table lock. An update statement will lock the entire table, causing other queries and updates to be blocked, so concurrent access is restricted.
    This is also one of the important reasons why MySQL changes the default storage engine from MyISAM to InnoDB;

For more detailed introduction, please refer to the following web page:/a/1190000008227211

3. Use:

  1. Whether to support transactions, if you want to choose InnoDB, if you don’t need it, you can consider MyISAM;

  2. If most of the tables are just read queries, you can consider MyISAM. If both read and write frequently, please use InnoDB.

  3. After the system crashes, it is more difficult to recover MyISAM. Whether it can be accepted or not, choose InnoDB;

  4. Since the MySQL 5.5 version, Innodb has become the default engine of Mysql (previously MyISAM), which shows that its advantages are obvious to all. If you don't know what storage engine to use, then use InnoDB, at least it won't be bad.