Detailed explanation of storage engine in MySQL

MySQL storage engine overview

What is a storage engine?

Data in MySQL is stored in files (or in memory) using a variety of different techniques. Each of these technologies uses different storage mechanisms, indexing techniques, locking levels and ultimately provides a wide range of different functions and capabilities. By choosing different technologies, you can gain additional speed or functionality, thereby improving the overall functionality of your application.

For example, if you are working with large amounts of temporary data, you might want to use an in-memory storage engine. The in-memory storage engine is able to store all table data in memory. Alternatively, you may need a database that supports transactions (to ensure data rollback if transactions are unsuccessful).

These different technologies and their associated functions are called storage engines (also called table types) in MySQL.

MySQL comes with a number of different storage engines configured by default, which can be pre-configured or enabled in the MySQL server. You can choose storage engines for servers, databases, and tables to give you maximum flexibility in choosing how to store your information, how to retrieve it, and what performance and functionality you need for your data.

This flexibility in choosing how to store and retrieve your data is a major reason why MySQL is so popular. Other database systems (including most commercial options) support only one type of data storage.

Unfortunately, the "one size fits all" approach taken by other types of database solutions means that you either sacrifice some performance or spend hours or even days tuning your database in detail. With MySQL, we only need to modify the storage engine we use.

What storage engines does MySQL support?

The storage engines supported by MySQL 5.6 include InnoDB, MyISAM, MEMORY, CSV, BLACKHOLE, FEDERATED, MRG_MYISAM, ARCHIVE, and PERFORMANCE_SCHEMA. NDB and InnoDB provide transaction-safe tables, while other storage engines provide non-transaction-safe tables.

Features of various storage engines

Overview

The MySQL server uses a multi-layer design and independent modules, a plug-in storage engine architecture, which allows the storage engine to be loaded into a new MySQL server being run, as shown in the Pluggable Storage Engines section in the figure. With the MySQL server architecture, application programmers and DBAs no longer have to consider all the underlying implementation details because a consistent and simple application model and API are provided at the storage level (that is, Pluggable Storage Engines). Therefore, although different storage engines have different capabilities, the applications are decoupled from them. The storage engine is responsible for dealing with the file system.

Features of various storage engines

Concurrency: Some applications have more granular locking requirements (such as row-level locking) than others.

Transaction support: Not all applications require transactions, but for those that do, there are well-defined requirements, such as ACID compliance.

Referential integrity: Through DDL-defined foreign keys, the server needs to enforce referential integrity of the associated database.

Physical storage: This includes a variety of things, from the total page size of tables and indexes, to the format required to store data, to the physical disks.

Index support: Different applications tend to adopt different indexing strategies. Each storage engine usually has its own indexing method, but some indexing methods (such as B-tree indexes) are common to almost all storage engines.

Memory Cache: Different applications respond better to certain memory cache strategies than others, so while some memory caches are common to all storage engines (such as the cache for user connections, MySQL's high-speed query cache, and so on), other cache strategies are uniquely defined only when using a particular storage engine.

Performance assistance: including multiple I/O threads for parallel operations, thread concurrency, database checkpoints, batch insert processing, etc.

Other target features: may include support for geospatial operations, security restrictions on specific data processing operations, etc.

The above requirements will be reflected in different needs. It is impossible to achieve them through a single system. Some of the above characteristics are contradictory in themselves, which is a problem of having one’s cake and eating it too. By making some choices about the above, the resulting storage engine is a plug-in engine that can be used for certain specific needs. As shown in the figure below, some existing storage engines and their basic features:

Introduction to various search engines

InnoDB

The default storage engine for MySql 5.6. InnoDB is a transaction-safe storage engine that has commit, rollback, and crash recovery capabilities to protect user data. InnoDB's row-level locking and Oracle-style consistent lock-free reads improve its multi-user concurrency and performance. InnoDB stores user data in a clustered index to reduce the I/O overhead of common queries based on the primary key. To ensure data integrity, InnoDB also supports foreign key constraints.

MyISAM

MyISAM supports neither transactions nor foreign keys. Its advantage is fast access speed, but table-level locking limits its performance in read-write loads. Therefore, it is often used in read-only or read-dominated data scenarios.

Memory

All data is stored in memory and is used for quick search of non-critical data. Memory type tables access data very quickly because their data is stored in memory and uses HASH index by default, but once the service is shut down, the data in the table will be lost.

BLACKHOLE

Black hole storage engine, similar to Unix's /dev/null, Archive only receives but does not save data. Queries on tables in this engine often return an empty set. This table can be used in a master-slave configuration where DML statements need to be sent to the slave server, but the master server does not retain a copy of this data.

CSV

Its tables are really comma-delimited text files. The CSV table allows you to import and export data in CSV format, interacting with scripts and applications in the same read and write format. Since CSV tables have no indexes, you are better off putting the data in an InnoDB table in normal operations and using the CSV table only during the import or export phase.

NDB

(aka NDBCLUSTER) - This clustered database engine is particularly suitable for applications that require the highest degree of uptime and availability. Note: The NDB storage engine is not supported in the standard MySQL 5.6 version. Currently supports

The versions of MySQL Cluster are: MySQL Cluster NDB 7.1 based on MySQL 5.1; MySQL Cluster NDB 7.2 based on MySQL 5.5; MySQL Cluster NDB 7.3 based on MySQL 5.6. MySQL Cluster NDB 7.4, also based on MySql 5.6, is currently under development.

Merge

Allows a MySql DBA or developer to group a series of identical MyISAM tables and reference them as a single object. Applicable to ultra-large-scale data scenarios, such as data warehouses.

Federated

Provides the ability to create a logical database by connecting different MySQL servers from multiple physical machines. Suitable for distributed or data market scenarios.

Example

This storage engine is used to store MySQL source code examples that illustrate how to start writing a new storage engine. It is mainly aimed at interested developers. This storage engine is just a "stub" that does nothing. You can create tables using this engine, but you cannot save any data to them, nor retrieve any indexes from them.

Common storage engines and applicable scenarios

InnoDB

For transaction processing applications, supporting foreign key and row-level locks. If the application has relatively high requirements for the integrity of things, requires data consistency under concurrent conditions, and data operations include many update and delete operations in addition to insertion and query, then the InnoDB storage engine is more appropriate. In addition to effectively reducing locks caused by deletions and updates, InnoDB can also ensure the complete submission and rollback of transactions. It is a suitable choice for systems such as billing systems or financial systems that have high requirements for data accuracy.

Transactions

MyISAM

If the application is mainly based on read and insert operations, with only a few update and delete operations, and does not have high requirements for transaction integrity and concurrency, then you can choose this storage engine.

Memory

Keeping all data in memory provides extremely fast access in environments where records and other similar data need to be located quickly. The disadvantage of Memory is that it limits the size of the table. Although the data can be restored normally if the database is terminated abnormally, once the database is closed, the data stored in the memory will be lost.

The use of storage engine in MySQL

Storage engine related sql statements

--View the current default storage engine:
mysql> show variables like "default_storage_engine";
--Query the storage engines supported by the current databasemysql> show engines \G;

result

Specify the storage engine to create a table

Specify when creating a table

mysql> create table ai(id bigint(12),name varchar(200)) ENGINE=MyISAM;
mysql> create table country(id int(4),cname varchar(50)) ENGINE=InnoDB;

--You can also use the alter table statement to modify the storage engine of an existing table.
mysql> alter table ai engine = innodb;

Specify in the configuration file

#my.ini file [mysqld]
default-storage-engine=INNODB

MySQL workflow

The MySQL architecture has a total of four layers, which are divided by dotted lines in the above figure.

First of all, the top-level service is not unique to MySQL. Most client/server tools or services provided to the network have a similar architecture. For example: connection processing, authorization authentication, security, etc.

The second layer of the architecture includes most of the MySQL core services. Includes: query parsing, analysis, optimization, caching, and all built-in functions (for example: date, time, math, and cryptographic functions). At the same time, all cross-storage engine functions are implemented at this layer: stored procedures, triggers, views, etc.

The third layer contains the storage engine. The storage engine is responsible for storing and retrieving data in MySQL. The server communicates with the storage engine through an API. These interfaces shield the differences between different storage engines, making these differences transparent to the upper-level query process. The storage engine API contains more than a dozen low-level functions for performing operations such as "start a transaction". However, storage engines generally do not parse SQL (InnoDB parses foreign key definitions because it does not implement this function itself), and different storage engines do not communicate with each other, but simply respond to upper-level server requests.

The fourth layer includes the file system. All table structures and data as well as logs of user operations are ultimately stored in the form of files on the hard disk.

The above is a detailed explanation of the storage engine in MySQL. For more information about MySQL storage engine, please pay attention to other related articles on 123WORDPRESS.COM!