Two types of storage engines for MySQL

MySQL has a variety of storage engines, now commonly used are the two engines, MyISAM and InnoDB, in addition to the two engines think there are many other engines, there are official, some companies have developed their own. This article is a simple overview of commonly used MySQL engine, one is often asked in the interview questions, and two this is the database design can not be ignored, with the right engine to better adapt to business scenarios, improve business efficiency.

InnoDB engine
InnoDB is a transactional storage engine that supports rollback and is designed to provide high-performance services when processing large amounts of data, which, at run time, creates buffer pools in memory for buffering data and indexes.

InnoDB Engine Benefits

1. Support transaction processing, acid transaction characteristics;

2. The implementation of the SQL standard four isolation levels;

3. Support row-level lock and foreign KEY constraint;

4. You can use transaction logs for data recovery.

5. Lock level is row lock, row lock advantage is suitable for high concurrency frequent table modification, high concurrency is better performance than MyISAM. The disadvantage is that the system consumes large.

6. The index not only caches itself, but also caches the data, which requires much more memory than the MyISAM.

InnoDB Engine Disadvantages

An index of type Fulltext is not supported because it does not save the number of rows in the table, and the full table is scanned when count statistics are used.

InnoDB Engine Application Scenario

1. Operations that require transactions;

2. Row-level locks are required to update data;

3. Large data volume reading and writing;

4. Large-scale Internet applications.

MyISAM engine

MyISAM is the default engine for MySQL 5.5.5 before it is designed to be read quickly.

MyISAM Engine Benefits

1. High-performance reading;

2. Because it saves the number of rows in the table, the full table is not scanned when the count statistic is used;

MyISAM Engine Disadvantages

1. Lock level is table lock, table lock Advantage is small cost, lock fast, the drawback is large lock size, the probability of locking impulse is higher, accommodating low concurrency ability, this engine is suitable for querying the main business.

2. This engine does not support transactions, nor does it support foreign keys.

3.INSERT and update operations need to lock the entire table;

4. It stores the number of rows in the table, so the Select COUNT (*) from table only needs to read the saved values directly and does not require a full table scan.

MyISAM Engine Application Scenario

1. Operations that do not require transactions;

2. Insert, update less, read frequently;

3. Frequent statistical calculations.

Contrast

Two types of storage engines for MySQL