What are the common storage engines in mysql?

Common storage engines in mysql are: 1. InnoDB storage engine; 2. MyISAM storage engine; 3. MEMORY storage engine. MySQL 5.5 and later uses the InnoDB storage engine by default, which has features such as hot backup, foreign key association, row-level locks, and transactions.

Common storage engines in mysql are:

(Recommended tutorial: mysql tutorial)

1. InnoDB engine (used by default after MySQL 5.5)

The default storage engine in MySQL 5.5 and later versions. Its advantages are as follows:

• Good disaster recovery

• Support transactions

• Use row-level locks

• Support Foreign key association

• Support hot backup

• For tables in the InnoDB engine, the physical organization form of the data is a cluster table (Cluster Table) , the primary key index and the data are together, and the data is physically distributed in the order of the primary key to implement buffer management. It can not only buffer the index but also the data, and automatically create a hash index to speed up data acquisition

2. MyISAM engine

Features are as follows:

• Does not support transactions

• Use table-level locks, poor concurrency

• After the host goes down, the MyISAM table is easily damaged and the disaster recovery is poor

• Can be used with locks , realize copy backup and migration under the operating system

• Only caches the index, and the data caching is realized by using the operating system buffer. May cause too many system calls and be inefficient

• Data is stored compactly, resulting in smaller indexes and faster full table scan performance

3. MEMORY storage engine

Features:

• Provides memory tables, does not support transactions and foreign keys

• Use table-level locks. Although memory access is fast, table-level locks will become a bottleneck if frequent reads and writes occur.

• Only supports fixed-size rows. Varchar type fields will be stored as fixed-length Char types, which wastes space

• TEXT and BLOB fields are not supported. When some queries require the use of temporary tables (which also use the MEMORY storage engine), if there are TEXT and BLOB fields in the table, they will be converted to disk-based MyISAM tables, seriously reducing performance

• Due to the expensive cost of memory resources, it is generally not recommended to set up an overly large memory table. If the memory table is full, you can avoid errors by clearing the data or adjusting the memory table parameters.

• Restart the server The data will be lost later, so you need to be careful when copying and maintaining it

The above is the detailed content of What are the common storage engines in mysql?. For more information, please follow other related articles on the PHP Chinese website!