What are the MySql engines?

The database storage engine is the underlying software organization of the database. The database management system (DBMS) uses the data engine to create, query, update and delete data. Different storage engines provide different storage mechanisms, indexing techniques, locking levels and other functions. Using different storage engines, you can also obtain specific functions.

Many different database management systems now support a variety of different data engines. The core of MySql is the storage engine.

Storage engine view

MySQL provides developers with the function of querying storage engines. I am using MySQL5.1 here. , you can use the

SHOW ENGINES

command in the command line window to view the engine used by MySQL. The output of the command is (I use Navicat Premium):

Seeing that MySQL provides users with so many storage engines, including engines that process transaction-safe tables and engines that have non-transaction-safe tables.

If you want to check which engine is used by the database by default, you can use the command:

SHOW VARIABLES LIKE 'storage_engine';

. The query result is:

In MySQL, there is no need to use the same storage engine in the entire server. According to specific requirements, different storage engines can be used for each table. The value of the Support column indicates whether a certain engine can be used: YES means it can be used, NO means it cannot be used, and DEFAULT means the engine is the current default storage engine.

Let’s take a look at some of the commonly used engines.

1. Innodb

supports transactions, is transaction safe, provides row-level locks and foreign key constraints, and has a buffer pool for buffering data and indexes.

Applicable scenarios: used for transaction processing, with ACID transaction support, and applied to tables that perform a large number of insert and update operations.

2. MyISAM

Does not support transactions, foreign key constraints, or row-level locks. The entire table needs to be locked during operation, but the table will be saved. The number of rows, so when executing select count(*) from tablename, the execution is particularly fast.

Applicable scenarios: used to manage non-transactional tables, providing high-speed retrieval and full-text retrieval capabilities, suitable for tables with a large number of select operations, such as log tables

3. MEMORY

Create a table using the contents that exist in memory. Each memory actually corresponds to only one disk file. Because it exists in the memory, the memory access speed is very fast, and the engine uses a hash index, which can be located at one time. There is no need to search from the root node to the branch node like a B-tree, so the access speed is very fast during precise query, but it is very When searching accurately, such as like, hashing will not work in this kind of range search. In addition, once the service is shut down, the data in the table will be lost because it is not saved to disk.

Applicable scenarios: Mainly used for tables whose content changes infrequently, or as an intermediate lookup table. Be careful when updating the table because the data has not been written to the disk. Before shutting down the service, you must consider the storage of the data

4. MERGE

MERGE storage engine A group of MyISAM data tables are treated as a logical unit, allowing us to query them at the same time. Each member of the MyISAM data table that constitutes a MERGE data table structure must have exactly the same structure. The data columns of each member data table must be defined with the same name and type in the same order, and the indexes must be defined in the same order and in the same way.

In addition to making it easy to reference multiple data tables at the same time without issuing multiple queries, the MERGE data table also provides the following conveniences.

The MERGE data table can be used to create a logical unit whose size exceeds the maximum length allowed by each MyISAM data table.

You can see a compressed data table included in the MERGE data table. For example, after the end of a certain year, you should no longer add records to the corresponding log file, so you can use the myisampack tool to compress it to save space, and the MERGE data table can still work as usual

The above is the detailed content of What are the MySql engines?. For more information, please follow other related articles on the PHP Chinese website!