How to choose a storage engine? What about the MySQL storage engine?

MySQL is a database processing system (DBMS) that we often use. Have you noticed the "storage engine" (storage_engine)? Sometimes the interview questions will ask about the difference between several commonly used storage engines in MySQL . This time, I will briefly talk about the storage engine.

Let's check the concept of "engine" first.

Engine is the core component of developing programs or systems on electronic platforms. Using the engine, developers can quickly establish and lay the functions required by the program, or use it to assist the operation of the program. Generally speaking, an engine is a supporting part of a program or a set of systems. Common program engines include game engines, search engines, antivirus engines, etc.

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

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

The concept of storage engine is a characteristic of MySQL. Oracle has no concept of special storage engine. Oracle has a distinction between OLTP and OLAP modes. Different storage engines determine the tables in the MySQL database can be stored in different ways. We can choose different storage engines according to the characteristics of the data.

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

One, InnoDB storage engine

InnoDB provides transaction security for MySQL tables with transaction processing, rollback, crash repair capabilities 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. Moreover, 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-growth column cannot be empty, and the value must be unique. MySQL stipulates that auto-incrementing columns must be primary keys. When inserting a value, if the auto-growth column does not enter a value, the inserted value is the value after the auto-increment; if the value entered is 0 or NULL, the inserted value is also the value after the auto-increment; if you insert If a certain value has not appeared before, it can be inserted directly.

InnoDB also supports foreign keys (FOREIGN KEY). The table where the foreign key is located is called the child table, and the table that the foreign key depends on (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 be changed accordingly. This is the referential integrity rule of the database.

In InnoDB, the table structure of the created table is stored in the .frm file (I think it is an abbreviation of frame). Data and indexes are stored in the table space 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 that the read and write efficiency is poor, and the data space occupied is relatively large.

Two, MyISAM storage engine

MyISAM is a common storage engine in MySQL and used to be the default storage engine of MySQL. MyISAM is developed based on the ISAM engine, adding many useful extensions.

MyISAM tables are stored in 3 files. The file name is the same as the table name. The extension names are frm, MYD, MYI. In fact, the structure of the frm file storage table; MYD file storage data, is the abbreviation of MYData; MYI file storage index, is the abbreviation of MYIndex.

Tables based on the MyISAM storage engine support 3 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-length; the dynamic type contains variable-length fields, and the length of the record is not fixed; the compressed type requires 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 it does not support transaction integrity and concurrency.

Three, MEMORY storage engine

MEMORY is a special kind of storage engine in MySQL. It uses the content stored in memory to create a table, and the data is all stored in memory. These characteristics 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 all stored in memory, which is conducive to the rapid processing of data and improves the efficiency of the entire table. It is worth noting that the server needs to have enough memory to maintain the use of the MEMORY storage engine table. If you don't need it, you can free up memory or even delete unnecessary tables.

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

Note that MEMORY is rarely used, because it stores data in the memory, and if the memory is abnormal, it will affect the data. If you restart or shut down, all data will disappear. Therefore, the life cycle of a MEMORY-based table is very short, generally one-time.

Fourth, how to choose a storage engine

In actual work, choosing a suitable storage engine is a more complicated issue. Each storage engine has its own advantages and disadvantages, and we cannot say in general terms who is better than others.

InnoDB: supports transaction processing, foreign keys, crash repair capabilities and concurrency control. If you need to have relatively high requirements for transaction integrity (such as banks) and concurrency control (such as ticket sales), then choosing InnoDB has great advantages. If you need to update and delete the database frequently, you can also choose InnoDB, because it supports the commit and rollback of transactions.

MyISAM: Insert data fast, with low space and memory usage. If the table is mainly used to insert new records and read out records, then choosing MyISAM can achieve high processing efficiency. If the application integrity and concurrency requirements are relatively low, it can also be used.

MEMORY: All the data is in the memory, the data processing speed is fast, but the security is not high. If you need a fast read and write speed and lower 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 in relatively small database tables.

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 high query requirements can be stored in MyISAM. If the database needs a temporary table for query, you can choose the MEMORY storage engine.