What are the common storage engines in MySQL? How do they differ from each other?

The four storage engines commonly used in MySQL are: MyISAM storage engine, InnoDB storage engine, memory storage engine, archive storage engine. This article focuses on these four storage engines, and finally compares the four storage engines.

MySQL the storage engine in

First, the storage engine

1, storage engine is a kind of database file access mechanism, how to store data, how to index the stored data and how to update, query data and other technology implementation methods.

2. Data in MySQL is stored in files (or memory) in a variety of different technologies, each of which uses different storage mechanisms, indexing techniques, locking levels and ultimately providing a wide range of different functions and capabilities. In MySQL, these different technologies and associated functions are called storage engines.

Second, MySQL in the View engine

1, show engines; Look at the storage engines supported by MySQL and get the default storage engine from MySQL.

2, show variables like '% storage_engine '; View the default storage engine for MySQL

3, show create TABLE tablename; To view the storage engine used by a particular table, this default storage engine has been modified!

4. Show table status from database where Name= "tablename"//accurate view of the storage engine used by a table in a database

Third, MySQL Several storage engines that are commonly used in:

MyISAM Storage Engine:

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

Storage: MyISAM This storage engine does not support transactions, does not support row-level locks, supports only concurrent inserted table locks, and is primarily used for high-load SELECT.

Indexed by: MyISAM also uses the B+tree index but differs somewhat from the InnoDB in the specific implementation.

The engine is based on the ISAM database engine, in addition to providing a number of functions such as index and field management not available in ISAM, MyISAM also uses a table-locking mechanism to optimize multiple concurrent read and write operations, but it is necessary to run the Optimize Table command frequently to restore space wasted by the updated mechanism , the fragmentation will also 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, primarily for high-load SELECT, which may also be the main reason for Web development by MySQL: A large number of data operations in Web development are read operations, Therefore, most virtual hosting providers and Internet Platform providers (Internet presence Provider,ipp) only allow the use of the MyISAM format.
MyISAM types of tables support three different storage structures: static, dynamic, and compact.
Static type: Refers to the defined table column size is fixed (that is, does not contain: Xblob, xtext, varchar and other variable length data types), so that MySQL will automatically use the static MyISAM format. Tables that use a static format are relatively performant because the overhead of maintaining and accessing data in a predetermined format is low, but this high performance is at the cost of space, because it is fixed at the time of definition, so no matter how large the value in the column is, it takes up the entire space, whichever is the maximum value.
Dynamic: If the column (even if only one column) is defined as dynamic (Xblob, xtext, varchar, and other data types), then MyISAM automatically uses the dynamic type, although the dynamic table consumes less space than the static type table, but it brings the performance decrease. Because if the content of a field changes, its position is likely to need to move, which can lead to fragmentation, and as the data changes, the fragmentation increases and data access performance decreases.
There are two solutions to the problem of reducing data access due to the increase of fragmentation:
A. Use static data types whenever possible;
B. Frequently use the OPTIMIZE table TABLE_NAME statement to defragment the table to recover space lost due to update and deletion 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 read-only table in the database for the entire life cycle, you should use a compressed table of MyISAM to reduce space usage.

Advantages and Disadvantages: The advantage of MyISAM is that it occupies little space and fast processing speed. The disadvantage is that the integrity and concurrency of transactions are not supported.

2 . InnoDB Storage Engine

Storage location: MySQL If you use the InnoDB storage engine, the database file type includes. frm, ibdata1,. IBD, storage location two,. frm file default storage location is C:\Documents and Settings\All Users\ The default storage location for Applicationdata\mysql\mysql Server 5.1\data,ibdata1,. ibd files is the Data folder in the MySQL installation directory.

The MySQL table of the InnoDB storage Engine provides the security of transactions, rollbacks, and system crash-repair capabilities and multi-version burst-controlled transactions.

InnoDB supports the self-growth column (auto_increment), the value of the self-growth column cannot be empty, if it is empty when used, how to automatically save the existing value starts to increment, if there is but larger than the present, then save this value.

The InnoDB storage engine supports foreign keys (foreign key), and the table on which the foreign key resides is called a child table and depends on the table called the parent table.

The most important thing about the InnoDB storage engine is the ability to support transactions, as well as transaction-related functionality.

The InnoDB storage engine supports MVCC row-level locks.

InnoDB Storage Engine index is using B+tree

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

3 . memory Storage Engine

The memory storage engine is a bit different than some of the previous storage engines, using the data stored inside it to create the table, and all the data is stored in memory.

Each table based on the memory storage engine actually corresponds to a disk file with the same file name and table name, and the type is. frm. The file only stores the structure of the table, and its data files are stored in memory, which facilitates the fast processing of data and improves the processing capacity of the whole table.

The memory storage engine uses a hash index by default, which is faster than using the B-+tree type, and can be referenced at creation time if the reader wishes to use the B-tree type.

The memory storage engine file data is stored in RAM, and if the mysqld process is abnormal, the data will disappear if the machine is restarted or shut down. So the life cycle of the tables in the memory storage engine is very short and is typically used only once.

4 . ARCHIVE Storage Engine

The storage engine is ideal for storing a large number of independent, historical data. Unlike the two engines, InnoDB and MyISAM, Archive provides compression with efficient insertion speed, but the engine does not support indexing, so query performance is poor.

Four Comparison of four storage engines

InnoDB: Supports transactional processing, supports foreign keys, supports crash-repair capabilities, and concurrency control. If you need to have high integrity requirements for transactions (such as a bank) and require concurrency control (such as ticketing), then choosing InnoDB has a big advantage. If you need frequent updates, delete operations of the database, you can also choose InnoDB, because the transaction support commits (commit) and rollback (rollback).

MyISAM: Fast insert data, low space and memory usage. If the table is primarily used to insert new records and read out records, then choosing MyISAM can achieve high efficiency. It can also be used if the integrity and concurrency requirements of the application are low. If the data table is used primarily for inserting and querying records, the MyISAM engine can provide high processing efficiency

Memory: All of the data is in RAM, the data is processed fast, but the security is not high. If you need fast read and write speed, the data security requirements are low, you can choose Memoey. It has requirements for the size of the table and cannot build too large a table. Therefore, this type of database is only used in relatively small database tables. If the data is only temporarily stored, the amount of data is small, and does not require a high level of data security, you can choose to save the data in memory of the storage engine, MySQL use the engine as a temporary table, the intermediate results of the query stored

If you have only insert and select operations, you can choose Archive,archive to support high concurrency inserts, but it is not transaction-safe by itself. Archive is ideal for storing archived data, such as logging information can be used Archiv

Note that the same database can also use a variety of storage engine tables. If a table requires relatively high transactions, you can choose InnoDB. This database can select MyISAM storage for tables with higher query requirements. If the database requires a temporary table for querying, you can select the memory storage engine.

Related articles:

MySQL storage engine MyISAM and InnoDB 9 point difference

Mysql-mysql Storage Engine Differences

Related videos:

Storage Engine-2017 latest PHP Advanced video tutorial