Compare InnoDB with MyISAM and other storage engines

InnoDB storage engine introduction

InnoDB engine is the default storage engine of Mysql. It has many features of its own, which are listed below.

  • Transaction support, InnoDB storage engine is designed primarily for online transaction processing (OLTP) applications.
  • Line lock design, support for foreign keys, non – locked read.
  • Support multiple versions of concurrency control (MVCC) for high concurrency.
  • It provides high performance and high availability functions such as insert buffer, secondary write, adaptive hash index, and preread.

These are some of the features of the InnoDB storage engine and its advantages. The reason why the InnoDB engine is used so widely is that it can have good performance.

MyISAM Storage engine introduction

  • Transactions are not supported and it is designed for online analytics applications (OLAP).
  • Full text indexing is supported.
  • Table lock design.
  • Its buffer pool only buffersIndex file.Data files are not buffered, so MyISAM storage engine table byMYD 和 MYIThe former stores data files and the latter stores index files.

Comparison between storage engines

This section briefly introduces the differences, functions and features of each storage engine.

Feature comparison

features

 MyISAM InnoDB BDB

 Memory Archive NDB
Storage limits

 There is no

 64TB

 There is no

 There are

 There is no

 There are
The transaction
support

 support


The lock level

 Table locks

 Row locks

 page table

 line

 line
MVCC (Concurrency Control)
support

support

 support
The full text indexing

 support




The cluster index
support



Data cache and index cache
support
support
support
Data compression

 support


support
Batch insertion speed

 high

 low

 high

 high

 high

 high
Cluster Database Support




support
Foreign key support
support



Applicable scenario Operations that do not require transactions; Insert, update less, read frequently; Frequent statistical calculations.

 Operations that require transactions; Updating data requires row-level locking; Large data read and write; Large Internet applications.

 Similar to the InnoDB

 The amount of data is small and needs to be accessed frequently. In addition, data loss does not have serious impact on services.

 The storage engine is basically used for data archiving as a log table The cluster

This section describes the features of the storage engine

The storage engine

 The main features
BDB

 An alternative transaction engine for InnoDB that supports COMMIT, ROLLBACK, and other transaction features
Memory

 Data is stored in memory, restarts or crashes, data disappears, hash indexes are used
Archive

 Supports only Insert and Select operations, supports indexes, ideal for storing archived data, target: high-speed Insert and compression functions
NDB

 Clustered storage engine, all data stored in memory, high availability, high performance cluster system
Federated Does not hold data, but points to a table on a remote MySQL database server
Maria

 New development engine to replace MyISAM storage engine. Supports transactional and non-transactional, caching, index files, row locking, and MVCC functions

If there are any improper articles, please correct them. If you like reading on wechat, you can also follow my wechat official account: Learn Java well and get quality learning resources.