MySql Performance Tuning (6) Performance Comparison Between InnoDB engine and Myisam Engine

MySql Performance Tuning (6) Performance Comparison Between InnoDB engine and Myisam Engine

The choice of these two storage engines should be weighed according to the application characteristics of the project. For complex application systems, you can also choose a combination of multiple storage engines based on the actual situation. However, this method is not recommended.

MyISAM supports full-text indexing. This is an index created based on word segmentation. It supports complex queries, but is not transaction-safe and does not support foreign keys. Each MyISAM table is stored in three files: frm file storage table definition; data file is MYD; index file is MYI; For MyISAM tables, check or repair operations can be performed manually or automatically.

InnoDB is a transaction engine that supports rollback, crash recovery, multi-version concurrency control, ACID transactions, and row-level locking. InnoDB row-level locks are not absolute, if an SQL statement does not use an index, InnoDB locks the entire table ).

Working principle of InnoDB: it is to take data into memory and read and write by users, which greatly improves performance. When all data is loaded into the memory, the performance is the best. Its design theory is to make full use of the memory to reduce disk I/O usage.

The differences between MyISAM and InnoDB are as follows:

1. MyISAM is non-transaction-safe, while InnoDB is transaction-safe, that is, ACID transaction support.

2. MyISAM locks are table-level locks with low lock overhead, while InnoDB supports row-level locks and high lock management overhead, and supports better concurrent write operations.

3. In the latest version, both of them support full-text indexing.

4. MyISAM is relatively simple and easy to manage. Therefore, it takes precedence over InnoDB in terms of efficiency. Small applications can be considered for use.

5. MyISAM tables are saved as files. Using MyISAM tables for cross-platform data transfer saves a lot of trouble.

6. the InnoDB table is safer than the MyISAM table, ensuring that the non-transaction table is switched to the transaction table without data loss.

Summary:

The read and write locks of the MyISAM storage engine are mutually exclusive, and are serialized during read/write operations. When a process requests a read lock from a MyISAM table and another process requests a write lock from a table, MySQL writes the lock first, even if the Read Request first goes to the lock wait queue, after a write request arrives, the write lock is inserted before the read lock request. This is because MySQL considers the write request to be more important than the Read Request, this is also the reason why MyISAM tables are not suitable for a large number of update operations and query operation applications, because a large number of update operations will make query operations difficult to read locks and may be blocked forever.

InnoDB is used for transaction processing applications and has many features, including support for ACID transactions and row locks. If an application requires a large number of read/write operations, InnoDB should be used to improve the performance of multi-user concurrent operations. Therefore, the InnoDB engine should be used as a priority.