What are the differences between the two MySQL engines?

Innodb engine

The Innodb engine provides support for database ACID transactions and implements the four isolation levels of the SQL standard. The engine also provides row-level locks and foreign key constraints. Its design goal is to handle large-capacity database systems. It is actually a complete database system based on the MySQL backend. When MySQL is running, Innodb will establish a buffer pool in the memory for Buffer data and indexes. However, the engine does not support FULLTEXT type indexes, and it does not save the number of rows in the table. When SELECT COUNT(*) FROM TABLE, the entire table needs to be scanned. This engine is of course the first choice when working with database transactions is required. Since the lock granularity is smaller, write operations will not lock the entire table, so when concurrency is high, using the Innodb engine will improve efficiency. However, the use of row-level locks 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.

MyIASM engine

MyIASM is the default engine of MySQL, but it does not provide support for database transactions, nor does it support row-level locks and foreign keys. Therefore, when INSERT (insert) or UPDATE ( When updating) data, the write operation needs to lock the entire table, and the efficiency will be lower. However, unlike Innodb, MyIASM stores the number of rows in the table, so when SELECT COUNT(*) FROM TABLE, you only need to directly read the saved values ​​without performing a full table scan. If the table has far more read operations than write operations and does not require database transaction support, MyIASM is also a good choice.

Main differences:

1. MyIASM is non-transactionally safe, while InnoDB is transactionally safe

2. The granularity of MyIASM locks is table-level, while InnoDB supports rows Level lock

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

4. MyIASM is relatively simple and more efficient than InnoDB. Small applications can consider using MyIASM

5. MyIASM tables are saved in file form, which is more convenient for cross-platform use.

Application scenarios:

1. MyIASM manages non-transaction tables and provides high-speed storage and retrieval as well as full-text search capabilities. If you perform a large number of select operations in your application, you should choose MyIASM

2 and InnoDB for transaction processing, which have features such as ACID transaction support. If you perform a large number of insert and update operations in your application, you should choose InnoDB

The above is the detailed content of What are the differences between the two MySQL engines?. For more information, please follow other related articles on the PHP Chinese website!