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 by
MYD和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.