Shulou(Shulou.com)06/01 Report--

Today, I will talk to you about the common storage engines in mysql, which may not be well understood by many people. in order to make you understand better, the editor has summarized the following for you. I hope you can get something according to this article.

Data in MySQL is stored in files (or memory) using a variety of different technologies. Each of these technologies uses different storage mechanisms, indexing techniques, locking levels, and ultimately provides a wide range of functions and capabilities. By choosing different technologies, you can gain additional speed or functionality to improve the overall functionality of your application. Common storage engines are:

1. InnoDB engine (MySQL5.5 will be used by default later)

The default storage engine in MySQL 5.5 and later, which has the following advantages:

Good disaster recovery

Support transaction

Use row-level locks

Foreign key association is supported

Hot backup is supported

For the tables in the InnoDB engine, the physical organization of the data is Cluster Table, the primary key index and the data are together, and the physical distribution of the data according to the order of the primary key realizes the buffer management, which can buffer not only the index but also the data, and will automatically create a hash index to speed up the data acquisition.

2. MyISAM engine

The characteristics are as follows:

Transactions are not supported

Use table-level locks with poor concurrency

After the mainframe downtime, the MyISAM table is easy to be damaged and the disaster recovery is not good.

It can cooperate with lock to realize replication, backup and migration under operating system.

Only the index is cached, and the data cache is implemented using the operating system buffer. May cause too many system calls and are inefficient

Compact storage of data, resulting in smaller indexes and faster full table scan performance

3. MEMORY storage engine

Features:

Provides memory tables and does not support transactions and foreign keys

Using table-level locks, although memory access is fast, table-level locks will become a bottleneck if you read and write frequently.

Only fixed-size rows are supported. Fields of type Varchar will be stored as fixed-length Char types, wasting space

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

Due to the high cost of memory resources, it is generally not recommended to set a large memory table. If the memory table is full, you can clear the data or adjust the memory table parameters to avoid errors.

Data will be lost after the server is restarted, so care should be taken during replication maintenance.

After reading the above, do you have any further understanding of the common storage engines in mysql? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.