The difference between MyISAM and InnoDB in MySQL database

The first thing to understand is that the storage engine in MySQL database loyalty is table-based, not database-based.

Tell them the difference:

The InnoDB storage engine, primarily for OLTP (online Transaction processing, on-line transaction processing) applications, is the first storage engine that fully supports ACID transactions (BDB, the first transaction-enabled storage engine, has stopped developing).

Characteristics:

Row lock design, support foreign key;

Supports consistent, non-lock read similar to Oracle style (read operations do not generate locks by default);

InnoDB the data in a logical tablespace, which is managed by the InnoDB itself. Starting with the MySQL4.1 version, each table of the InnoDB storage engine can be stored separately in a separate IBD file;

InnoDB achieves high concurrency by using MVCC (multiple versioning concurrency control: read does not block write, write, or block read), and implements 4 isolation levels for the SQL standard (default is repeatable level);

InnoDB also provides high performance and high-availability features such as insert buffer, two write (double write), adaptive hash (Adaptive Hash Index), pre-read (read ahead), etc.

InnoDB uses aggregation (clustered) to store the data in the table, each of which is stored in the order of the primary key (if you specify a primary key without an explicit build table, InnoDB generates a 6-byte ROWID for each row, which is used as the primary key);

The InnoDB table will have three hidden fields: In addition to the 6 byte db_row_id mentioned above, there are 6 bytes of b_tx_id (transaction ID) and 7 bytes of db_roll_ptr (pointing to the address of the corresponding rollback segment). This can be seen through InnoDB monitor;

The MyISAM storage Engine is the official MySQL storage engine for Applications in OLAP (online analytical processing, on-line Analytical processing).

Characteristics:

Transactions are not supported, table and full-text indexes are supported. Fast operation speed;

The MyISAM Storage engine table consists of MYD and MYI, MYD used to hold data files and MYI to store index files.

The MySQL database caches only its index files, and the cache of the data files is given to the operating system itself;

Starting with the MySQL5.0 version, MyISAM supports 256T of single-table data by default;

Finally, summarize:

1, InnoDB support things, and myisam do not support things;

2, InnoDB support row-level lock, and MYISAM support table-level lock;

3, InnoDB support MVCC, and MyISAM not support;

4, INNODB support foreign key, and MyISAM not support;

5. InnoDB does not support full-text indexing, and MyISAM supports it.

This article is from the "Hello I am Forest" blog, please make sure to keep this source http://chensenlin.blog.51cto.com/10559465/1873229

The difference between MyISAM and InnoDB in MySQL database