Comparison of storage engines supported by MySQL database

Storage Engine

MySQL can store data in files (memory) using different techniques, which are called storage engines.
Each storage engine uses different storage mechanisms, indexing techniques, and locking levels, ultimately providing a wide range of different capabilities.

Storage engines supported by MySQL

MyISAM

InnoDB

Memory

CSV

Archive

Concurrency Control

Ensure data consistency and integrity when multiple connections modify records.

Lock

1. Shared lock (read lock): Multiple users can read the same resource in the same time period, and the data will not change during the reading process.
2. Exclusive lock (write lock): Only one user can write to a resource at any time. When a write lock is performed, other read lock or write lock operations will be blocked.

Lock particles

1. Table lock is a lock strategy with the lowest overhead.
2. Row lock is the most expensive lock strategy.

Transactions

Transactions are used to ensure the integrity of the database

Implementation steps:
(1) Subtract 200 yuan from the current account (account balance is greater than or equal to 200).
(2) Add RMB 200 to the other party’s account.

Foreign Keys

It is a strategy to ensure data consistency.

index

It is a structure for sorting the values ​​of one or more columns in a data table.

Features of various storage engines

Memory: Data is stored in memory, and the storage limit is limited by the memory size.
MyISAM: Suitable for situations where there are not many transactions.
InnoDB: Suitable for situations where there are many transactions and foreign key support is required.
Index: normal index, unique index, full-text index, btree index, hash index.

MyISAM: The storage limit can reach 256TB, and it supports indexing, table-level locking, and data compression.
InnoDB: The storage limit is 64 TB, supports transactions and indexes, and the lock granularity is row lock.

The above is the detailed comparison of storage engines supported by MySQL database. For more information about MySQL storage engine, please pay attention to other related articles on 123WORDPRESS.COM!