What are the commonly used storage engines in MySQL? How do they differ from each other?-Mysql Tutorial-php.cn

The four commonly used storage engines in MySQL are: MyISAM storage engine, innoDB storage engine, MEMORY storage engine, and ARCHIVE storage engine. This article will focus on these four storage engines, and finally compare these four storage engines.

Storage engine in MySQL

1. Storage engine

1. The storage engine is actually an access mechanism for database files, how to store data, how to index the stored data, how to update, query data and other technical implementation methods.

2. 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 different functions and capabilities. In MySQL, these different technologies and supporting related functions are called storage engines.

2. View engines in MySQL

1. show engines; // View the storage engines supported by mysql and get the mysql default storage engine.

2. show variables like '% storage_engine'; // View the default storage engine of mysql

3. show create table tablename; // View the storage engine used by a specific table, This default storage engine has been modified!

4. show table status from database where name="tablename" //Accurately view the storage engine used by a table in a database

3. MySQL## Several storage engines commonly used in #:

MyISAM Storage engine:

Storage location: If you use MySQL MyISAM storage engine, database file types include .frm, .MYD, .MYI, the default storage location is C:\Documentsand Settings\All Users\Application Data\MySQL\MySQL Server 5.1\data

storage method : MyISAM is a storage engine that does not support transactions or row-level locks. It only supports concurrently inserted table locks and is mainly used for high-load select.

Index method: MyISAM also uses B tree index but its specific implementation is slightly different from Innodb.

This engine is based on the ISAM database engine. In addition to providing a large number of functions such as indexing and field management that are not available in ISAM, MyISAM also uses a table locking mechanism to optimize multiple concurrent read and write operations, but it requires frequent operation of OPTIMIZE TABLE. command to recover the space wasted by the update mechanism, otherwise fragmentation will increase, ultimately affecting data access performance. MyISAM also has some useful extensions, such as the MyISAMChk tool for repairing database files and the MyISAMPack tool for recovering wasted space. MyISAM emphasizes fast read operations and is mainly used for high-load selects. This may also be the main reason why MySQL is so popular in web development: a large number of data operations performed in web development are read operations, so most virtual host providers and Internet Platform Provider (Internet Presence Provider, IPP) only allow the use of MyISAM format.
MyISAM type tables support three different storage structures: static, dynamic, and compressed.
Static type: means that the size of the defined table column is fixed (that is, it does not contain variable-length data types such as xblob, xtext, varchar, etc.), so MySQL will automatically use the static MyISAM format. The performance of tables using static formats is relatively high because the overhead required to maintain and access data stored in a predetermined format is very low; but this high performance comes at the expense of space because it is fixed at the time of definition. So no matter how big the value in the column is, the maximum value will prevail and occupy the entire space.
Dynamic type: If the column (even if there is only one column) is defined as dynamic (xblob, xtext, varchar and other data types), then MyISAM will automatically use the dynamic type, although the dynamic type table occupies less space than the static type table space, but it brings about performance reduction, because if the content of a field changes, its position is likely to need to be moved, which will lead to the generation of fragments. As the data changes increase, the fragments also increase, and the data Access performance will be reduced accordingly.
For the problem of reducing data access due to the increase in fragments, there are two solutions:
A. Use static data types as much as possible;
B. Optimize table table_name statement sort out the fragments of tables to recover to restore Space loss due to updates and deletions of table data. If the storage engine does not support optimize table table_name, you can dump and reload the data, which can also reduce fragmentation;
Compression type: If you create a table in the database that is read-only throughout its life cycle, you should use MyISAM compression table to reduce space usage.

Advantages and Disadvantages: The advantages of MyISAM are its small footprint and fast processing speed. The disadvantage is that transaction integrity and concurrency are not supported.

2. innoDBStorage engine

Storage location: MySQL If you use the InnoDB storage engine, the database file types include .frm, ibdata1, and .ibd. There are two storage locations, .frm files The default storage location is C:\Documents and Settings\All Users\ApplicationData\MySQL\MySQL Server 5.1\data. The default storage location of ibdata1 and .ibd files is the data folder in the MySQL installation directory.

The mysql table of the innodb storage engine provides transaction, rollback and system crash repair capabilities and transaction security with multi-version burst control.

innodb supports auto-increment columns (auto_increment). The value of an auto-increment column cannot be empty. If it is empty when used, how can the existing value be automatically saved and start to increase in value? If there is, it is larger than the current value. If it is still large, then this value will be saved.

The innodb storage engine supports foreign keys. The table where the foreign key is located is called a child table and the table it depends on is called a parent table.

The most important thing about the innodb storage engine is that it supports transactions and transaction-related functions.

The innodb storage engine supports mvcc row-level locks.

The innodb storage engine index uses B Tree

Advantages and Disadvantages: The advantage of InnoDB is that it provides good transaction processing, crash repair capabilities and concurrency control. The disadvantage is that the reading and writing efficiency is poor and the data space occupied is relatively large.

3． MEMORYStorage Engine

The memory storage engine is a little different from some of the previous storage engines. It uses the data stored in it to create tables, and all The data is also stored in memory.

Each table based on the memory storage engine actually corresponds to a disk file. The file name and table name of the file are the same, and the type is .frm. This file only stores the structure of the table, and its data files are stored in memory, which is conducive to rapid processing of data and improves the processing capabilities of the entire table.

The memory storage engine uses hash (HASH) index by default, which is faster than using B-Tree type. If readers want to use B-Tree type, they can reference it when creating.

Memory storage engine file data is stored in memory. If an exception occurs in the mysqld process, the data will disappear if the machine is restarted or shut down. Therefore, the life cycle of tables in the memory storage engine is very short and is generally only used once.

4． ARCHIVEStorage Engine

This storage engine is very suitable for storing large amounts of independent data as historical records. Different from the two engines InnoDB and MyISAM, ARCHIVE provides compression function and has efficient insertion speed. However, this engine does not support indexes, so the query performance is poor.

Four. Comparison of four storage engines

InnoDB: supports transaction processing, supports foreign keys, supports crash repair capabilities and concurrency control. If you need to have relatively high requirements on transaction integrity (such as banks) and require concurrency control (such as ticket sales), then choosing InnoDB has great advantages. If you need a database that requires frequent updates and deletions, you can also choose InnoDB because it supports transaction commit and rollback.

MyISAM: Inserting data is fast and space and memory usage is relatively low. If the table is mainly used to insert new records and read out records, then choosing MyISAM can achieve high processing efficiency. It can also be used if the application's integrity and concurrency requirements are relatively low. If the data table is mainly used to insert and query records, the MyISAM engine can provide higher processing efficiency

MEMORY: All data is in memory, and the data processing speed is fast, but the security is not high. If you need fast reading and writing speeds and low data security requirements, you can choose MEMOEY. It has requirements on the size of the table and cannot create a table that is too large. Therefore, this type of database is only used for relatively small database tables. If the data is only temporarily stored, the amount of data is not large, and high data security is not required, you can choose the Memory engine to save the data in the memory. This engine is used as a temporary table in MySQL to store the intermediate results of the query

If you only have INSERT and SELECT operations, you can choose Archive. Archive supports high-concurrency insert operations, but it is not transaction-safe. Archive is very suitable for storing archived data. For example, you can use Archive

to record log information. Note that the same database can also use tables from multiple storage engines. If a table requires relatively high transaction processing, you can choose InnoDB. In this database, tables with higher query requirements can be stored in MyISAM. If the database requires a temporary table for querying, you can choose the MEMORY storage engine.

9 differences between MySQL storage engine MyISAM and InnoDB

##mysql-MySQL storage engine difference