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

In this article, I want to share with you some information about MySql engine. Xiao Bian thinks it is quite practical, so share it for everyone to make a reference. Let's follow the editor and have a look.

Database storage engine is the underlying software organization of database. Database management system (DBMS) uses data engine to create, query, update and delete data. Different storage engines provide different storage mechanisms, indexing techniques, locking levels, etc., and specific features can be obtained using different storage engines.

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

Storage Engine View

MySQL provides developers with the ability to query storage engines. I use MySQL 5.1 here, which can be used in command line windows:

SHOW ENGINES

Command to view the engine MySQL uses, the output of the command is (I use Navicat Premium):

See how MySQL provides users with so many storage engines, including engines that handle transaction-safe tables and engines that come out with non-transaction-safe tables.

If you want to see which engine your database uses by default, you can do so by using the command:

SHOW VARIABLES LIKE 'storage_engine';

To view, the query results are:

In MySQL, there is no need to use the same storage engine throughout the server; you can use a different storage engine for each table for specific requirements. The value in the Support column indicates whether an engine can be used: YES indicates that it can be used, NO indicates that it cannot be used, and DEFAULT indicates that the engine is the current default storage engine.

Here are some of the most common engines.

I. Innodb

Transaction-safe, provides row-level locks and foreign key constraints, has buffer pools for buffering data and indexes.

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

II. MyISAM

No transaction support, no foreign key constraint support, no row-level lock support, operation needs to lock the entire table, but the number of rows in the table will be saved, so when executing select count(*) from tablename 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

III. MEMORY

Create tables using what exists in memory, with each memory actually corresponding to only one disk file. Because it is stored in memory, memory access speed is very fast, and the engine uses hash index, can be located at one time, do not need to look up from the root node to the branch node like B tree, so accurate query access speed is particularly fast, but non-accurate search, such as like, this range search, hash does not work. In addition, once the service is shut down, the data in the table is lost because it is not saved to disk.

Applicable scenarios: mainly used for tables with infrequent content changes, or as intermediate lookup tables. Be careful about updating tables because data is not written to disk, and consider data storage before shutting down services

IV. MERGE

The MERGE storage engine treats a set of MyISAM data tables as a logical unit, allowing us to query them simultaneously. The member MyISAM data tables that make up a MERGE data table structure must have exactly the same structure. The columns of each member table must be defined in the same order with the same names and types, 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 simultaneously without issuing multiple queries, MERGE data tables provide the following conveniences.

MERGE data tables can be used to create a logical unit with a size that exceeds the maximum length allowed for each MyISAM data table

You see a handful of compressed data tables included in MERGE data tables. For example, at the end of a given year, you shouldn't add records to the log file, so you can compress it with myisampack to save space, and the MERGE table will still work as usual

Thank you for reading! About MySql commonly used engines have what to share here, I hope the above content can be of some help to everyone, so that everyone can learn more knowledge. If you think the article is good, you can share it so that more people can see it!

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.