Location>code7788 >text

An article to thoroughly understand each storage engine of MySQL, InnoDB, MyISAM, Memory, CSV, Archive, Merge, Federated, NDB

Popularity：79 ℃/2024-10-25 10:57:59

The storage engines in MySQL are the core modules of its database management system and are used to handle different types of data storage and retrieval operations. Each storage engine has its own characteristics and is suitable for different types of application scenarios.The most commonly used storage engines in MySQL includeInnoDB、MyISAM、Memory、CSV、Archive、Merge、Federated、NDB etc. The following is a detailed description of the MySQL storage engine.

1. InnoDB Storage Engine

Key Features:

  • Transaction support: InnoDB is the default storage engine in MySQL and supports the full ACID transaction (Atomicity, Consistency, Isolation, Durability) feature. It also provides a transaction isolation level that prevents dirty reads, unrepeatable reads, phantom reads, and other problems.
  • Line Level Lock: InnoDB uses Row-level Locking, which makes it ideal for highly concurrent write operations with a low probability of lock conflicts.
  • Foreign key support: InnoDB supports foreign key constraints, so you can define data dependencies between tables and automatically maintain referential integrity.
  • crash recovery: InnoDB is able to do this by using theRedo Logs cap (a poem)Undo Logs to ensure data recovery in the event of a system crash.
  • clustered index: InnoDB uses Clustered Indexes, where the primary key index is stored with the data. Each table must have a primary key. If no primary key is defined, InnoDB automatically generates a hidden primary key.

Applicable Scenarios:

InnoDB is well suited to handle transactional systems that require high concurrency, for example:

  • Bank trading systems: Involves a large number of insert and update operations, and needs to ensure the integrity and isolation of the transaction.
  • E-commerce platform: Frequent order inserts and inventory updates are required and data consistency is guaranteed.

Working mechanism (underlying implementation):

  • data storage structure: InnoDB stores tables in a logical tablespace, which by default uses a shared tablespaceibdata file, or each table can be stored separately in its own.ibd Documentation.
  • Buffer Pool: InnoDB reduces disk I/O operations by caching data pages. Modification operations are first written to the buffer pool and later flushed to disk asynchronously.
  • Doublewrite Buffer: To prevent corruption caused by data being partially written to disk in the event of a crash or power failure, InnoDB writes the data page twice to ensure data integrity.

2. MyISAM storage engine

Key Features:

  • Transactions are not supported: MyISAM does not support transactions and foreign keys, so data integrity and consistency cannot be guaranteed.
  • Table-level locking: MyISAM uses Table-level Locking, which locks the entire table for reads, as well as writes. This makes MyISAM's concurrency performance poor, especially in frequent write scenarios.
  • full text indexing support: MyISAM supports full-text index, which can be used for efficient full-text search operations.
  • Smaller storage space: The compact storage format of MyISAM is advantageous for storing static or logged data.
  • quick: Because it does not require transaction control, foreign key support, etc., MyISAM performs very well in read-only or read-many-write scenarios.

Applicable Scenarios:

  • read-only database: Used in scenarios where static data is stored and updates are infrequent. For example.data warehouse maybeLog storage system。
  • Full-text search: Scenarios that require fast full-text search of text.

Working mechanism (underlying implementation):

  • Separate storage of data and indexes: MyISAM stores data in the.MYD file, the index is stored in the.MYI Documentation.
  • Table Lock Mechanism: With MyISAM, read operations can be performed concurrently, but once a write operation is performed, the entire table is locked. Writes block all other read operations.
  • Crash recovery is not supported: Unlike InnoDB, MyISAM does not support crash recovery, which can lead to data loss or inconsistency in the event of a system crash.

3. Memory Storage Engine

Key Features:

  • Data is stored in memory: Memory Storage Engine data is stored temporarily in memory and all data is lost after restarting the database or closing the session.
  • Table-level locking: Like MyISAM, the Memory engine uses table-level locking.
  • quick: Since the data is stored entirely in memory, the Memory engine is very fast to read and write.
  • BLOB and TEXT types are not supported.: Due to memory constraints, the Memory Engine does not support large object types such as BLOB and TEXT types.

Applicable Scenarios:

  • Provisional data processing: The Memory engine is suitable for temporary data processing, caching data, etc. For example.temporary table maybeSession data storage。
  • Fast query result storage: Scenarios that require efficient and fast queries but do not require persistence.

Working mechanism (underlying implementation):

  • memory storage structure: The Memory engine stores data in memory, using hash indexes (Hash Index) to provide faster query performance.
  • Risk of data loss: Since the data is stored in memory, once the server is restarted or shut down, all data is lost.

4. CSV Storage Engine

Key Features:

  • text storage: The CSV engine stores data as a text file of comma-separated values, similar to a traditional CSV file.
  • simple structure: Each table's data is stored in a.csv Documentation.
  • Indexing is not supported: The CSV engine does not support indexing, so query performance is poor.
  • Easy to import and export: Because the storage format is a standard CSV file, it is easy to exchange data with external systems.

Applicable Scenarios:

  • Data import and export: The CSV storage engine is a good choice when you need to exchange data with other systems.
  • Temporary data storage: For data storage scenarios that require simple formatting and do not require indexing, CSV can be used.

5. Archive Storage Engine

Key Features:

  • Suitable for large-scale data storage: The Archive storage engine is specifically designed to efficiently store large amounts of historical or archived data.
  • Insertion and query support: Archive only supports insert and query operations, not update and delete operations.
  • data compression: The Archive engine compresses the data for storage, thus reducing the disk footprint.
  • Table-level locking: Archive uses table-level locking.

Applicable Scenarios:

  • log system: The Archive storage engine is ideal for storing log data, historical archived data, and other scenarios where data storage space is limited.

6. Other Storage Engines

  • Merge: Merge Multiple MyISAM Tables allows you to merge multiple MyISAM tables of the same structure into a single logical table, often used for partitioned data management.
  • Federated: Used in distributed database systems to enable cross-server queries between different MySQL instances.
  • NDB Cluster: Used in MySQL Cluster configurations, distributed, fault-tolerant storage engine, commonly used in highly available systems.

summarize

storage engine Main features Applicable Scenarios
InnoDB Support for transactions, row-level locking, foreign key support Highly concurrent transactional systems (e.g., banks, e-commerce platforms)
MyISAM No support for transactions, table-level locking, full-text index support Static data storage, read-many-write-few systems
Memory Data is stored in memory and is fast Temporary data handling, caching, session management
CSV Data is stored in text files for easy data exchange Data import and export scenarios
Archive Insert and query only, data compression Log data storage, large-scale historical data archiving
Merge Merging Multiple MyISAM Tables Partitioned data management
Federated Distributed database system with cross-server queries Systems that need to interact with other MySQL instances
<Previous Next>