Summary of the differences between MySQL storage engines MyISAM and InnoDB

1. Changes in MySQL's default storage engine

In versions prior to MySQL 5.1, the default search engine is MyISAM. From MySQL 5.5 and later, the default search engine is changed to InnoDB.

2. Main features of MyISAM and InnoDB storage engines

The MyISAM storage engine features: table-level locks, no support for transactions and full-text indexes, which makes it suitable for some CMS content management systems as backend databases. However, the table lock structure is not sufficient for high-concurrency, heavy-load production systems.

The following are the features of the MySQL 5.7 MyISAM storage engine:

The features of the InnoDB storage engine are: row-level locking, transaction security (ACID compatibility), support for foreign keys, and support for FULLTEXT type indexes (FULLTEXT type indexes are supported starting from version 5.6.4). The InnoDB storage engine provides a transaction-safe storage engine with commit, rollback, and crash recovery capabilities. InnoDB is designed for maximum performance when handling huge volumes. Its CPU efficiency is probably unmatched by any other disk-based relational database engine.

The following are the features of the MySQL 5.7 InnoDB storage engine:

Notice:

The row lock of the InnoDB table is not absolute. If MySQL cannot determine the range to be scanned when executing a SQL statement, the InnoDB table will also lock the entire table, for example, update table set num=1 where name like “a%” .

The main difference between the two types is that InnoDB supports transactions and foreign keys and row-level locks. MyISAM does not support this. Therefore, MyISAM is often easily considered to be only suitable for use in small projects.

3. MyISAM and InnoDB performance test

The following two pictures are the official stress test results of MyISAM and InnoDB

It can be seen that as the number of CPU cores increases, the throughput of InnoDB becomes better, while the throughput of MyISAM remains almost unchanged. Obviously, the table locking mechanism of MyISAM reduces the read and write throughput.

4. Whether transactions are supported

MyISAM is a non-transactional engine, which enables MySQL with the MyISAM engine to provide high-speed storage and retrieval, as well as full-text search capabilities, making it suitable for applications with frequent queries such as data warehouses;

InnoDB is transaction-safe;

Transactions are an advanced processing method. For example, if any error occurs during the addition, deletion, or modification of columns, the transaction can be rolled back and restored, but this is not possible with MyISAM.

5. The difference between MyISAM and InnoDB in structure

(1) Each MyISAM is stored in three files on disk:

The name of the first file begins with the name of the table, and the extension indicates the file type. The .frm file stores the table definition.
The second file is a data file with the extension .MYD (MYData).
The third file is the index file, which has the extension .MYI (MYIndex).

(2) Disk-based resources are the InnoDB tablespace data file and its log file. The size of the InnoDB table is only limited by the size of the operating system file, which is generally 2GB.

6. Explanation of MyISAM and InnoDB table locks and row locks

MySQL table-level locks have two modes: table shared read lock (Table Read Lock) and table exclusive write lock (Table Write Lock). What does this mean? It means that when a MyISAM table is read, it will not block other users' read requests for the same table, but it will block write operations on the same table; and write operations on the MyISAM table will block other users' read and write operations on the same table.

InnoDB row locks are implemented by locking index items, that is, InnoDB uses row-level locks only when data is retrieved through index conditions, otherwise it uses table locks! Row-level locks consume more resources than table locks for each lock acquisition and release operation. When two InnoDB transactions deadlock, the number of rows affected by each transaction is calculated, and then the transaction with fewer rows is rolled back. When InnoDB is not involved in the locking scenario, InnoDB cannot detect it. It can only be solved by locking timeout.

7. Whether to save the specific number of rows in the database table

InnoDB does not save the specific number of rows in the table. That is to say, when executing select count(*) from table , InnoDB has to scan the entire table to calculate the number of rows, but MyISAM simply reads the saved number of rows.

Note that when count(*) statement includes where condition, the operation is the same for both tables. This is the case where InnoDB uses table locks as introduced in "6" above.

8. How to choose

MyISAM is suitable for:

• (1) Do a lot of count calculations;
• (2) Insertions are infrequent, but queries are frequent. If a large number of SELECTs are executed, MyISAM is a better choice.
• (3) No transactions.

InnoDB is suitable for:

• (1) High reliability requirements or transactions are required;
• (2) When table updates and queries are quite frequent and there is a high chance of table locking, specify the creation of a data engine;
• (3) If your data performs a large number of INSERTs or UPDATEs, you should use InnoDB tables for performance reasons;
• (4) When DELETE FROM table is used, InnoDB does not re-create the table but deletes rows one by one.
• (5) The LOAD TABLE FROM MASTER operation does not work for InnoDB. The solution is to first change the InnoDB table to a MyISAM table, then change it back to an InnoDB table after importing the data. However, this does not apply to tables that use additional InnoDB features (such as foreign keys).

Note that the code to create each table is identical, except for the TYPE parameter at the end, which specifies the data engine.

Other differences:

1. For AUTO_INCREMENT type fields, InnoDB must contain an index that only has this field, but in a MyISAM table, a joint index can be created with other fields.

2. When DELETE FROM table is used, InnoDB does not re-create the table, but deletes rows one by one.

3. The LOAD TABLE FROMMASTER operation does not work for InnoDB. The solution is to first change the InnoDB table to a MyISAM table, then change it back to an InnoDB table after importing the data. However, this does not apply to tables that use additional InnoDB features (such as foreign keys).

4. The InnoDB storage engine is fully integrated with the MySQL server. The InnoDB storage engine maintains its own buffer pool for caching data and indexes in main memory.

5. For self-incrementing fields, InnoDB must contain an index for only this field, but a joint index can be created with other fields in a MyISAM table.

6. When clearing the entire table, InnoDB deletes rows one by one, which is very slow. MyISAM will rebuild the table.

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM. If you want to learn more about this, please check out the following links