MySQL performance tuning (vi) Performance comparison of InnoDB engine and MyISAM engine

The choice of the two storage engines depends on the application characteristics of the project, and for the complex application system, it is possible to select a combination of various storage engines according to the actual situation. But this method is not recommended to be taken.

MyISAM supports full-text indexing, which is a segmentation-based index that supports more complex queries, but is not transaction-safe and does not support foreign keys. Each MyISAM table is stored in 3 files: The frm file is stored as a table definition, the data file is MyD, the index file is myi, and for the MyISAM table, you can perform a check or repair operation manually or automatically.

InnoDB is a transactional engine that supports rollback, has crash resilience, multiple versions of concurrency control, supports acid transactions, supports row-level locking (InnoDB row-level locks are not absolute, and if an SQL statement is not used to index, then InnoDB locks the full table).

InnoDB working principle: is to take the data in the memory, read and write by the user, which greatly increases the performance. When the data is all loaded into memory, the performance is the best, its design theory is to make full use of memory, reduce disk I/O utilization.

The difference between MyISAM and InnoDB has the following points:

1. MyISAM is a non-transactional security, and InnoDB is transaction-safe, which is the acid transaction support.

2. MyISAM locks are table-level locks, lock overhead is small, while InnoDB supports row-level locking, lock management overhead, and support for better concurrent write operations.

3. In the latest version, both are already supported for full-text indexing.

4. MyISAM is relatively simple and easy to manage, so it takes precedence over innodb in terms of efficiency, and small applications can be considered.

5. MyISAM tables are saved as files, and using their storage in cross-platform data transfer saves a lot of hassle.

6. The InnoDB table is more secure than the MyISAM table and can be guaranteed to switch non-transactional tables to transaction tables without data loss.

Summarize:

The read and write locks of the MyISAM storage engine are mutually exclusive and read-write operations are serial. When a process requests a read lock on a MyISAM table, and another process requests a write lock on a table, MySQL is the write process that first obtains the lock, not only that, even if the read request is first to the lock waiting queue, the write request is followed, and the write lock is inserted before the read lock request. This is because MySQL considers writing requests to be more important than read requests, which is why the MyISAM table is not suitable for a large number of update operations and query operation applications, because a large number of update operations can make querying operations difficult to suddenly read locks, which can be blocked forever.

InnoDB is used for transactional applications with many features, including support for acid transactions, row locks, and so on. If you need to perform a large number of read and write operations in your application, you should use InnoDB, which can improve the performance of multiuser concurrent operations, so use the InnoDB engine as much as possible.

MySQL performance tuning (vi) Performance comparison of InnoDB engine and MyISAM engine