Three types of storage engines common to MySQL (InnoDB, MyISAM, memory)

Check out the "engine" concept first.

Engine is the core component of a development program or system on an electronic platform. Using the engine, developers can quickly build, lay out the functions required by the program, or take advantage of the operation of their auxiliary programs. In general, the engine is a program or a set of system support parts. The common program engine has the game engine, the search engine, the antivirus engine and so on.

Ok, we know that the engine is the core component of a program.

Simply put, the storage engine refers to the type of table and how the table is stored on the computer .

The concept of the storage engine is the characteristics of MySQL, there is no dedicated storage engine concept in Oracle, Oracle has OLTP and OLAP mode distinction. Different storage engines determine that the tables in the MySQL database can be stored in different ways. We can choose different storage engines based on the characteristics of the data.

There are many kinds of storage engines in MySQL that can be viewed through the "show ENGINES" statement. The following focus on InnoDB, MyISAM, memory these three kinds.

I. INNODB storage Engine

InnoDB to MySQL provides transactional, rollback , crash- repair , and Multi-version concurrency control transaction security. In MySQL starting from 3.23.34a contains Innnodb. It is the first table engine on MySQL to provide a foreign key constraint. and InnoDB's ability to handle transactions is unmatched by other storage engines. The later version of MySQL's default storage engine is InnoDB.

The InnoDB storage engine always supports auto_increment. 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 the automatically growing value, and if a certain value is inserted and the value is not previously present, it can be inserted directly.

The InnoDB also supports foreign keys (FOREIGN key). The table where the foreign key resides is called the child table, and the table that the foreign key depends on (REFERENCES) is called the parent table. The field in the parent table that is associated with the foreign key of the Word table must be the primary key. When you delete or update a piece of information in the parent table, the child table must also have a corresponding change, which is the referential integrity rule for the database.

In InnoDB, the table structure of the created table is stored in the . frm file (I think it's a frame abbreviation). Data and indexes are stored in table spaces defined by Innodb_data_home_dir and Innodb_data_file_path.

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.

Two. MyISAM Storage engine

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

The MyISAM table is stored as 3 files. The name of the file is the same as the table name. Expand the name of frm,MYD,MYI. In fact, the FRM file storage table structure, myd file storage data, is mydata abbreviation; myi file storage index, is the abbreviation of Myindex.

Tables based on the MyISAM storage engine support 3 different storage formats. Includes static, dynamic and compression types. The static type is the default storage format of MyISAM, its fields are fixed length, the dynamic type contains variable length fields, the length of the record is not fixed, and the compression type needs to use the Myisampack tool, which takes up less disk space.

The advantage of MyISAM is that it takes up little space and fast processing speed. The disadvantage is that the integrity and concurrency of transactions are not supported.

Three. Memory Storage Engine

Memory is a special kind of storage engine in MySQL. It uses the content stored in memory to create the table, and the data is all in memory . These features are very different from the previous two.

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 the frm type. Only the structure of the table is stored in the file. The data files are stored in memory, which facilitates the fast processing of data and improves the efficiency of the whole table. 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 free up memory and even delete unwanted tables.

Memory uses a hash index by default. Faster than using the B-Tree index. Of course, if you want to use the B-tree index, you can specify it when you create the index.

Note that memory is rarely used because it is stored in memory, which can affect the data if an exception occurs. If you restart or shut down the computer, all data will disappear. Therefore, memory-based tables have a short life cycle and are generally disposable.

Four. How to select the storage engine

In practical work, choosing a suitable storage engine is a complicated problem. Each storage engine has its own pros and cons, and cannot generally say who is better than who.

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.

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.

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.

Reprint Address: http://www.cnblogs.com/yuxiuyan/p/6511837.html

Three types of storage engines common to MySQL (InnoDB, MyISAM, memory)