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

MySQL is the database processing system (DBMS) that we often use. Have you noticed the "storage engine" (storage_engine) in it? Sometimes the interview questions will also ask about the differences between several commonly used storage engines in MySQL. Just talk a little bit about the storage engine this time.

Check the concept of "engine" first.

Engine is the core component of a development program or system on an electronic platform. With the engine, developers can quickly build, lay out the functions needed by the program, or use it to assist the operation of the program. Generally speaking, an engine is the supporting part of a program or a system. Common program engines are game engine, search engine, 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 storage engine is the characteristic of MySQL. There is no special concept of storage engine in Oracle. Oracle has the distinction between OLTP and OLAP mode. Different storage engines determine that tables in MySQL databases can be stored in different ways. We can choose different storage engines according to the characteristics of the data.

There are many storage engines in MySQL, which can be viewed through the "SHOW ENGINES" statement. The following focuses on InnoDB, MyISAM, and MEMORY.

I. InnoDB storage engine

InnoDB provides transaction security for MySQL tables with transaction processing, rollback, crash repair, and multi-version concurrency control. InnnoDB is included in MySQL starting from 3.23.34a. It is the first table engine on MySQL to provide foreign key constraints. And InnoDB's ability to handle transactions is unmatched by other storage engines. The default storage engine for later versions of MySQL is InnoDB.

The InnoDB storage engine always supports AUTO_INCREMENT. The value of the auto-growing column cannot be empty and the value must be unique. The MySQL states that the self-incrementing column must be the primary key. When inserting a value, if the auto-growing column does not enter a value, the inserted value is the auto-growing value; if the entered value is 0 or empty (NULL), the inserted value is also the automatically increased value; if a certain value is inserted and the value has not appeared before, it can be inserted directly.

InnoDB also supports foreign keys (FOREIGN KEY). The table on which the foreign key is located is called the child table, and the table on which the foreign key depends (REFERENCES) is called the parent table. The field associated with the foreign key of the word table in the parent table must be the primary key. When deleting or updating a piece of information in the parent table, the child table must also change accordingly, which is the referential integrity rule of the database.

In InnoDB, the table structure of the created table is stored in a .frm file (I think it's an abbreviation for frame). Data and indexes are stored in tablespaces defined by innodb_data_home_dir and innodb_data_file_path.

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

II. MyISAM storage engine

MyISAM is a common storage engine in MySQL and used to be the default storage engine for MySQL. MyISAM is based on the ISAM engine and adds many useful extensions.

The table of MyISAM is saved into 3 files. The name of the file is the same as the table name. The extension name is frm, MYD, MYI. In fact, frm files store the structure of tables; MYD files store data, which is an abbreviation for MYData; and MYI files store indexes, which are abbreviations for MYIndex.

Tables based on the MyISAM storage engine support three different storage formats. Including static type, dynamic type and compression type. Among them, the static type is the default storage format of MyISAM, and its fields are fixed in length; the dynamic type contains variable length fields, and the length of the record is not fixed; and the compressed type needs to use the myisampack tool and takes up less disk space.

The advantage of MyISAM lies in its small footprint and fast processing speed. The disadvantage is that the integrity and concurrency of transactions are not supported.

III. Memory storage engine

MEMORY is a special storage engine in MySQL. It uses the contents 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 based on the MEMORY storage engine actually corresponds to a disk file. The file name of the file is the same as the table name, and the type is frm. Only the structure of the table is stored in this file. The data files are stored in memory, which is conducive to the rapid processing of data and improve the efficiency of the whole table. It is worth noting that the server needs to have enough memory to maintain the use of the tables of the MEMORY storage engine. If you don't need it, you can free up memory or even delete unwanted tables.

MEMORY uses a hash index by default. It is faster than using a B-tree index. Of course, if you want to use a B-tree index, you can specify it when you create the index.

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

four。 How to choose a storage engine

In practical work, choosing a suitable storage engine is a complicated problem. Each storage engine has its own advantages and disadvantages, and it is impossible to say generally who is better than the other.

InnoDB: supports transaction processing, foreign keys, crash repair and concurrency control. If you need to have high transaction integrity requirements (such as banks) and concurrency control (such as ticket sales), then choosing InnoDB has a great advantage. If you need to update and delete databases frequently, you can also choose InnoDB because transaction commit (commit) and rollback (rollback) are supported.

MyISAM: fast data insertion, low space and memory usage. If the table is mainly used to insert new records and read records, then selecting MyISAM can achieve high processing efficiency. It can also be used if the application has low requirements for integrity and concurrency.

MEMORY: all the data is in memory, the processing speed of the data is fast, but the security is not high. If you need fast reading and writing speed and low data security requirements, you can choose MEMOEY. It has requirements on the size of the table and cannot create too large a table. Therefore, such databases are only used in relatively small database tables.

Note that tables from multiple storage engines can also be used in the same database. If a table requires high transaction processing, you can choose InnoDB. In this database, the tables with high query requirements can be selected for MyISAM storage. If the database needs a temporary table for the query, you can choose the MEMORY storage engine.

Author: my Stroke

This article comes from "meditation on Java Architecture", a partner of Yunqi Community.

Original link: https://yq.aliyun.com/articles/630494?spm=a2c4e.11153940.bloghomeflow.18.69d0291aD6UmgH

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.