Shulou(Shulou.com)06/02 Report--

This article shows you how to analyze the two common MySQL storage engines MyISAM and InnoDB, the content is concise and easy to understand, can definitely make your eyes bright, through the detailed introduction of this article, I hope you can get something.

A brief introduction to MyISAM1.1 MyISAM

MyISAM is the default database engine for MySQL (prior to version 5.5) and is improved by the early ISAM (Indexed Sequential Access Method: indexed sequential access method). Although the performance is excellent and provides a large number of features, including full-text indexing, compression, spatial functions, etc., MyISAM does not support transactions and row-level locks, and the biggest drawback is that it cannot be safely recovered after a crash. However, after version 5.5, MySQL introduced InnoDB (another database engine).

The following figure just means that most of the time we use the InnoDB storage engine, but in some cases it is better to use MyISAM. For example, MyISAM is more suitable for read-intensive tables, while InnoDB is more suitable for write-intensive tables. In the case of the separation of the master and slave of the database, MyISAM is often chosen as the storage engine of the main database.

1.2 MyISAM featur

Row locks (MyISAM only table locks) are not supported. Locks are applied to all tables that need to be read when reading and exclusive locks are added to tables when writing

Transactions are not supported

Foreign keys are not supported

Security recovery after crash is not supported

While the table has a read query, new records can be inserted into the table.

Support for the first 500 characters of BLOB and TEXT, and full-text indexing

Support for delayed updating of indexes, greatly improving write performance

For tables that will not be modified, compressed tables are supported, which greatly reduces the footprint of disk space.

Supplementary concepts:

Mysql row lock and table lock (lock is a mechanism by which a computer coordinates multiple processes or pure threads to access a resource concurrently)

Table-level lock: lock the whole table each time. Low overhead, fast locking, no deadlock; large lock granularity, the highest probability of lock conflict and the lowest concurrency

Row-level lock: locks one row of data at a time. High overhead and slow locking; deadlock occurs; locking granularity is the smallest, the probability of lock conflict is the lowest, and the concurrency is the highest

Brief introduction of two InnoDB2.1 MyISAM

InnoDB is the default database engine for MySQL (after version 5.5) and was acquired by Oracle in May 2006. Compared with traditional ISAM and MyISAM, the most important feature of InnoDB is that it supports ACID-compatible Transaction.

2.2 characteristics of MyISAM

Supports row locks, uses MVCC to support high concurrency, and may deadlock

Support transaction

Foreign keys are supported

Support for security recovery after a crash

Full-text indexing is not supported

The third part is about the comparison and summary of the two. 3.1 the common comparison between the two.

1) differences in count operations: because the MyISAM cache has a table meta-data (number of rows, etc.), it doesn't cost much resources to do COUNT (*) for a well-structured query. For InnoDB, there is no such cache.

2) whether to support transactions and secure recovery after a crash: MyISAM emphasizes performance, each query is atomic, and its execution is faster than the InnoDB type, but does not provide transaction support. However, InnoDB provides advanced database functions such as transaction support and foreign keys. Transaction security (transaction-safe (ACID compliant)) table with transaction (commit), rollback (rollback), and crash repair capability (crash recovery capabilities).

3) whether foreign keys are supported or not: MyISAM does not support foreign keys, but InnoDB does.

3.2 Summary

MyISAM is more suitable for read-intensive tables, while InnoDB is more suitable for write-intensive tables. In the case of the separation of the master and slave of the database, MyISAM is often chosen as the storage engine of the main database.

In general, if transaction support is required and there is a high frequency of concurrent reads (the granularity of MyISAM table locks is too large, so when the table write concurrency is high, there will be a lot of queries to wait for), InnoDB is a good choice. If you have a large amount of data (MyISAM supports compression features to reduce disk space footprint) and does not need to support transactions, MyISAM is the best choice.

The above content is how to analyze the two common storage engines MyISAM and InnoDB in MySQL. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.