Introduction to MySQL Storage engine

The following is a description of three storage engines for InnoDB, MyISAM and Memeory.

InnoDB Storage Engine

InnoDB is subject to the CNU general Public License (GPL) release. InnoDB has been adopted by some heavyweight internet companies, such as Yahoo, Slashdot and Google, providing a powerful solution for users to operate very large databases. InnoDB to MySQL table provides transaction security for transactions, rollback, crash repair, and multiple versions of concurrency control . MySQL contains the InnoDB storage engine starting from 3.23.34a. InnoDB is the first table engine on MySQL to provide a foreign key constraint , and InnoDB's ability to transact is unmatched by other MySQL storage engines. The following describes the characteristics and shortcomings of the InnoDB storage engine.

Auto-Grow column auto_increment is supported in the INNODB storage engine. The value of the autogrow column cannot be empty, and the value must be unique. MySQL specifies that the self-increment column must be the primary key. When inserting a value, if the autogrow column does not enter a value, the inserted value is the auto-grow value, and if the value entered is 0 or null (NULL), the inserted value is also the auto-grow value, and if a certain value is inserted and the value is not previously present, it can be inserted directly.

Foreign keys (FOREIGN key) are supported in the INNODB storage engine. The table where the foreign key resides is the child table, and the table that the foreign key depends on is the parent table. The field in the parent table that is associated with the outer table key must be the primary key. When you delete or update a piece of information from a parent table, the child table must also have a corresponding change. The table structure of the tables created in the INNODB storage engine is stored in the. frm file. data and indexes are stored in innodb_data_home_dir and Innodb_data_file_path table spaces.

The advantage of the InnoDB storage engine is that it provides good transaction management, crash-repair capabilities, and concurrency control. The disadvantage is that its reading and writing efficiency is slightly poor, occupy the data space is relatively large.

The InnoDB table is the ideal engine for the following situations:

Updated dense tables: The InnoDB storage engine is particularly well-suited for handling multiple concurrent update requests.

Transactions: The InnoDB storage Engine is the only standard MySQL storage engine that supports transactions, which is a required software for managing sensitive data such as financial information and user registration information.

Automated Disaster recovery: Unlike other storage engines, the InnoDB table can automatically recover from a disaster. Although the MyISAM table can be repaired after a disaster, the process is much longer.

Oracle's INNODB storage engine is widely used in MySQL-based web, e-commerce, financial systems, health care, and retail applications. Because the INNODB provides efficient acid independence (atomicity), consistency (consistency), isolation (isolation), and persistence (durability)-compatible transaction processing power, And unique, high-performance and scalable architectural elements.

In addition, InnoDB is designed for transactional applications that need to handle crash recovery, referential integrity, high-level user concurrency, and response time timeouts. In MySQL 5.5, the most significant enhancement is the INNODB as the default storage engine. With MyISAM and other table types still available, users can build InnoDB-based applications without changing the configuration.

MyISAM Storage Engine

The MyISAM storage engine is a common storage engine in MySQL, which was once the default storage engine for MySQL. The MyISAM storage engine was developed based on the ISAM storage engine, which solves many of the deficiencies of ISAM. MyISAM adds a lot of useful extensions.

Of the MyISAM storage engineFile Type

The table for the MyISAM storage engine is stored as 3 files. The name of the file is the same as the table name. Extensions include frm, myd, and Myi.

FRM: The structure of the storage table;

MYD: Storing data, is the abbreviation of MyData;

Myi: Storage index, is the abbreviation of Myindex.

Of the MyISAM storage enginestorage Format

Tables based on the MyISAM storage engine support 3 different storage formats, including static , dynamic , and compact .

(1) static type. If the size of all the table columns is static (that is, the Xblob, xtext, or varchar data types are not used), MySQL automatically uses the static MyISAM format. Using this type of table performance is very high because of the low overhead required to maintain and access data stored in a predefined format. However, this advantage is at the expense of space, because each column requires the maximum space allocated to the column, regardless of whether the space is actually used.

(2) dynamic type. If a table column (even if only one column) is defined as dynamic (using Xblob, xtext, or varchar data types), MySQL automatically uses the dynamic format. Although MyISAM dynamic tables occupy less space than static tables, the space savings result in degraded performance. If the contents of a field change, the position is likely to need to be moved, which results in fragmentation. As fragmentation increases in the data set, data access performance is reduced accordingly. There are two ways to fix this problem: use static data types whenever possible. The Optimize table statement is often used to defragment the table and restore space lost due to table updates and deletions.

(3) Compression type. Sometimes tables are created that are read-only throughout the application life cycle. If this is the case, you can use the Myisampack tool to convert it to a myisam compression table to reduce space. In a given hardware configuration (for example, fast processors and low-speed hard drives), the performance boost will be significant.

Advantages and disadvantages of MyISAM storage engine

The advantage of the MyISAM storage engine is that it takes up less space and is faster to process. The disadvantage is that the integrity and concurrency of transactions are not supported.

Memory Storage Engine

The memory storage engine is a special kind of storage engine in MySQL. it uses the content stored in memory to create the table, and all the data is put in memory. these features are different from the InnoDB storage engine, MyISAM storage engine. The following describes the file storage format , index type , storage cycle , and pros and cons of the memory storage engine.

Memory storage engine file storage form

each table that is based on the memory storage engine actually corresponds to a disk file . The file has the same filename as the table name, and the type is frm. Only the structure of the table is stored in the file, and its data files are stored in memory . This facilitates the fast processing of the data and improves the efficiency of the whole table processing. It is important to note that the server needs to have enough memory to sustain the use of tables in the memory storage engine. If you don't need it, you can release the content and even delete the tables you don't need.

Index type of memory storage engine

The memory storage engine uses the hash index by default. It is faster than using the B-tree (BTREE) index. If you want to use the B-tree index, you can choose to use it when you create the index.

Storage cycle of memory storage engine

Memory storage engines are often seldom used. Because all the data in the memory table is stored in RAM, it can affect the integrity of the data if an abnormal memory occurs. If you restart the machine or shut down the computer, all data in the table will disappear. Therefore, the life cycle of the table based on memory storage engine is very short, and is generally one-time.

Advantages and disadvantages of memory storage engine

The size of the memory table is limited. The size of the table is mainly determined by two parameters, namely max_rows and max_heap_table_size. Where max_rows can be specified when the table is created, the size of max_heap_table_size defaults to 16MB and can be expanded as needed. This type of table is processed very quickly because it exists in-memory features. However, its data is easily lost and its life cycle is short.

The starting point for creating the MySQL memory storage engine is speed. To get the fastest response time, the logical storage medium used is system memory. While storing table data in memory does improve performance, remember that when the mysqld daemon crashes, all memory data is lost.

The memory table does not support varchar, blob, and text data types because the table type is stored in a fixed-length record format. In addition, automatically adding columns (via the Auto_increment property) is not supported if you are using a MySQL version prior to 4.1.0. Remember that the memory meter is only used for a specific range and will not be used for long-term storage. Based on this flaw, special care should be taken when choosing memory storage engine.

Consider using the memory table when the data has the following conditions:

Temporary: The target data is only temporary and must be available immediately in its life cycle.

Relatively unrelated: data stored in memory tables, if suddenly lost, does not have a substantial negative impact on application services and has no long-term impact on data integrity.

If you use MySQL4.1 and previous versions, memory searches are less effective than the MyISAM table because the memory table only supports hash indexes , which requires searching using the entire key. However, the version after 4.1 supports both the hash index and the B-Tree index . B-Tree indexes are better than hash indexes, you can use partial queries and wildcard queries, or you can use operators such as,, and >= to facilitate data mining.

How to select a storage engine

Each storage engine has its own advantages, and can not generally say who is better than who, only suitable for unsuitable. The following suggestions for choosing a storage engine are given based on their different characteristics.

InnoDB Storage Engine: For transactional applications, with many features, including acid transaction support, foreign key support. It also supports crash repair and concurrency control. If the integrity of the transaction requires a high level of concurrency control, then choosing the InnoDB storage engine has great advantages. A database that requires frequent updates, deletions, or a InnoDB storage engine can be selected. Because this class of storage engine can implement transaction commit and rollback (Rollback).

MyISAM Storage Engine: Manages non-transactional tables that provide high-speed storage and retrieval, as well as full-text search capabilities. The MyISAM storage engine inserts data fast, with less space and memory usage. If the table is primarily used to insert new records and read out records , then choosing the MyISAM storage engine can achieve high efficiency in processing. The MyISAM storage engine can also be selected if the integrity and concurrency requirements of the application are low.

Memory storage Engine: Memories Storage Engine provides an "in-the-box" table, the memory storage engine all the data is in RAM, the data processing speed, but the security is not high. If you need fast read and write speed, the data security requirements are low, you can choose memory storage engine. The memory storage engine has a requirement for the size of the table and cannot build too large a table. Therefore, this type of database only uses relatively small database tables.

Introduction to MySQL Storage engine