Differences between InnoDB and MyISAM storage engines in MySQL

MySQLA very important feature that distinguishes the database from other databases is its plug-in table storage engine, which is based on tables rather than databases. Since each storage engine has its own characteristics, we can choose the most suitable storage engine for each table.

As DBA, we should have a deep understanding of storage engines. Today we introduce the two most common storage engines and their differences: InnoDB and MyISAM.

InnoDB Storage engine

InnoDBThe storage engine supports transactions, and its main design goal is Applications for OLTP (On Line Transaction Processing). Features include row lock design, support for foreign keys, and support for non-locking reads. Starting from version 5.5.8, InnoDB has become the default storage engine of MySQL.

InnoDBThe storage engine uses a clustered index to store data, so each table is stored in the order of the primary key. If the primary key is not specified, InnoDB will automatically generate a 6 byte ROWID for each row as the primary key.

MyISAMStorage engine

MyISAMThe storage engine does not support transaction and table lock designs. Supports full-text indexing, mainly for OLAP (On Line Analytical Processing) applications, suitable for scenarios with frequent queries such as data warehouses. Before the 5.5.8 version, MyISAM was the default storage engine for MySQL. This engine represents the need for querying and analyzing massive amounts of data. It emphasizes performance, so the query execution speed is faster than InnoDB. The difference between

InnoDB and MyISAM

Transaction

For the atomicity of database operations, we need transactions. Ensure that a set of operations either all succeed or fail, such as the function of transferring money. We usually place multiple SQL statements between begin and commit to form a transaction.

InnoDB is supported, MyISAM is not supported.

Primary key

Due to the clustered index of InnoDB, if the primary key is not specified, the primary key will be automatically generated.
MyISAM Supports the existence of tables without primary keys.

Foreign keys

In order to solve the dependencies of complex logic, we need foreign keys. For example, the entry of college entrance examination scores must belong to a certain student, so we need a foreign key to the college entrance examination score database with the admission ticket number.

InnoDB is supported, MyISAM is not supported.

Index

In order to optimize the speed of queries, sort and match searches, we need indexes. For example, everyone's names are stored sequentially from the first letters of a-z. When we search for zhangsan or the 44 position, we can quickly locate the name we want. Find the desired location.

InnoDB is a clustered index. The data is bound to the clustered index of the primary key. Indexing through the primary key is very efficient. If you search through the auxiliary index of other columns, you need to find the clustered index first, and then query all the data, which requires two queries.

MyISAM is a non-clustered index, the data files are separated, and the index saves the pointer of the data.

From InnoDB 1.2.x version, MySQL5.6 version, both support full-text indexing.

auto_incrementAuto-increment

For auto-increment fields, InnoDB requires that the column must be an index and must It is the first column of the index, otherwise an error will be reported:

mysql> create table test(
    -> a int auto_increment,
    -> b int,
    -> key(b,a)
    -> ) engine=InnoDB;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

Just replace the order of (b,a) with (a,b).

And MyISAM can combine this field with other fields in any order to form a joint index.

Number of table rows

A very common requirement is to see how many pieces of data there are in the table. At this time we need select count(*) from table_name.

InnoDBDoes not save the number of table rows and requires a full table scan. MyISAMSave it in a variable and read the value directly, which is faster. At that time, when querying with where, the two were the same.

Storage

The files of the database need to be stored on the disk, and then read into the memory when the application needs it. Generally includes data files and index files.

InnoDB is divided into:

• .frmTable structure file
• .ibdata1 Shared table space
• .ibdExclusive table space
• .redoLog file

##MyISAMDivided into three files:

• .frmStorage table definition
• .MYDStorage table data
• .MYIStorage table index

Execution speed

If your operation is a large number of query operations, such as SELECT, the performance will be better using MyISAM.
If most of the operations are deletions and changes, use InnoDB. The indexes of

InnoDB and MyISAM are both B tree indexes. The primary key of the data can be queried through the index. If you are not familiar with B Tree can view MySQL InnoDB index principles and algorithms. The performance difference between the two mainly lies in the different processing methods after querying the primary key of the data.

InnoDB will cache index and data files. Generally, 16KB is used as a minimum unit (data page size) to interact with the disk, InnoDB After querying the index data, what you actually get is the ID of the primary key. It needs to find all the data of the row in the data page in the memory. However, if the data is not loaded hot data, you still need to perform The search and replacement of data pages may involve multiple I/O operations and data searches in memory, resulting in high time consumption.

AndMyISAMThe storage engine only caches index files and does not cache data files. The cache of its data files directly uses the cache of the operating system, which is very unique. At this time, the same space can load more indexes, so when the cache space is limited, MyISAM's index data page replacement times will be less. According to what we know earlier, the files of MyISAM are divided into MYI and MYD. When we find the primary key ID## through MYI #, in fact, the offset offset of the MYD data file is obtained, and searching for data is much faster than InnoDB addressing mapping.

But because

MyISAM is a table lock, and InnoDB supports row locks, when a large number of write operations are involved, the concurrency performance of InnoDB Much better than MyISAM. At the same time, InnoDB also uses MVVC multi-version control to improve concurrent read and write performance.

deleteDelete data

When calling

delete from table, MyISAM will directly rebuild the table , InnoDB will be deleted row by row, but it can be replaced by truncate table. Reference: Two ways and differences in clearing table data in mysql.

Lock

##MyISAM

Only supports table locks, and the entire table is locked for each operation. InnoDB
Supports row locks, locking a minimum number of rows of data for each operation. Compared with row locks, table locks consume fewer resources and will not cause deadlock, but at the same time, the concurrency performance is poor. Row lock consumes more resources, is slower, and may cause deadlock, but because the locking granularity is small and the data is small, the concurrency performance is good. If a statement in

InnoDB

cannot determine the range to be scanned, the entire table will also be locked. When a row lock deadlock occurs, the number of rows affected by each transaction will be calculated, and then the transaction with a smaller number of rows will be rolled back.

Data Recovery

MyISAM

There is no fast and safe recovery after a crash. InnoDB has a complete recovery mechanism.

Data cache

MyISAM

Only caches index data and queries data through the index. InnoDB Not only caches index data, but also caches data information. The data is read into the cache pool by page and updated according to the LRU (Latest Rare Use) algorithm.

How to choose a storage engineThe statements to create a table are all the same, only the last

type

specifies the storage engine.

MyISAM1. A large number of queries total

count

2. Frequent queries and insufficient insertion Frequent

3. No transaction operation

InnoDB##1. High availability is required, or transactions are required

2. Frequent table updates

Recommended learning:

MySQL tutorial

The above is the detailed content of Differences between InnoDB and MyISAM storage engines in MySQL. For more information, please follow other related articles on the PHP Chinese website!