Introduction to MySQL Database storage engine

MySQL provides multiple storage engines — both for transaction secure tables and non-transaction secure tables. In MySQL, there is no need to use the same storage engine for the entire server. You can use a different storage engine for each table based on specific requirements.

Introduction to Storage Engines

Data in MySQL is stored in files (or memory) using various techniques.

Each of these technologies uses different storage mechanisms, indexing techniques, locking levels, and ultimately offers a wide range of different capabilities and capabilities.

By choosing different technologies, you can gain additional speed or functionality that can improve the overall functionality of your application.

And storage engines are simply that

• How to store data
• How do I index stored data
• How to update, query data and other technical implementation methods.

For example, if you are working on a large amount of temporary data, you may need to use a memory storage engine, which can store all tabular data in memory. Or, if you need a transaction-enabled database to ensure that data can be rolled back if the transaction fails, you may choose a transaction-enabled storage engine.

This section describes storage engine classification

Here is a brief introduction to MySQL’s storage engine.

InnoDB

InnoDB is a robust transactional storage engine that is already used by many Internet companies and provides a powerful solution for users to operate very large data stores.

InnoDB also introduces row-level locking and foreign key constraints, making InnoDB ideal for the following situations:

• Update intensive tables. The InnoDB storage engine is particularly well suited for handling multiple concurrent update requests.

• The transaction. InnoDB storage engine is the standard MySQL storage engine that supports transactions.

• Automatic disaster recovery. Unlike other storage engines, InnoDB tables automatically recover from disasters.

• Foreign key constraints. InnoDB is the only storage engine in MySQL that supports foreign keys.

• Support for automatically adding the column AUTO_INCREMENT attribute.

• Innodb storage engine is the default storage engine since 5.7.

In general, InnoDB is a good choice if you need transaction support and have a high rate of concurrent reads.

MyISAM

MyISAM tables are operating system independent, which means they can be easily ported from a Windows server to a Linux server.

Whenever we create a table for the MyISAM engine, three files are created on the local disk, with the filename being the table name.

For example, if I create a MyISAM engine tb_Demo table, the following three files will be generated:

• Tb_demo.frm, which stores table definitions.
• Tb_demo. MYD, store data.
• Tb_demo. MYI, which stores indexes.

MyISAM tables cannot handle transactions, which means that tables with transaction requirements cannot use the MyISAM storage engine. MyISAM storage engine is particularly suitable for use in the following situations:

1. Select intensive tables. The MyISAM storage engine is very fast at sifting through large amounts of data, which is its most prominent advantage.
2. Insert intensive tables. MyISAM’s concurrent insert feature allows data to be selected and inserted simultaneously.

From this point of view, the MyISAM storage engine is well suited for managing server log data.

MRG_MYISAM

The MRG_MyISAM storage engine is a combination of MyISAM tables. The old version, MERGE, was the same thing.

These MyISAM table structures must be exactly the same, and while their use is not as prominent as other engines, it can be very useful in some cases.

In plain English, a Merge table is an aggregator of several identical MyISAM tables; Merge tables do not contain data. Merge tables can be queried, updated, or deleted. These operations actually operate on internal MyISAM tables.

Merge Storage engine usage scenarios

A common storage strategy for information such as server logs is to divide the data into many tables, with each name associated with a specific time period.

For example, you can store server log data in 12 identical tables, each named for each month. When it is necessary to generate a report based on data from all 12 log tables, this means that multi-table queries need to be written and updated to reflect the information in those tables. Instead of writing these potentially erroneous queries, Merge the tables into a single query and then delete the Merge table without affecting the original data. Deleting the Merge table only deletes the definition of the Merge table and has no impact on the internal tables.

Merge Usage of the storage engine

• ENGINE=MERGE, which is the same as MRG_MyISAM, is not available in MySQL 5.7.

• UNION=(t1, t2), which indicates which tables are attached to the MERGE table. You can alter table by changing the UNION value to add or delete subtables of the MERGE table. Such as:

alter table tb_merge engine=merge union(tb_log1) insert_method=last;
Copy the code

• INSERT_METHOD=LAST, INSERT_METHOD specifies the insertion method. The value can be: 0. FIRST is inserted into the FIRST table in the UNION; LAST is inserted into the LAST table in the UNION.

• MERGE tables and the member tables that make up the MERGE data table structure must have exactly the same structure. The data columns of each member data 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 manner.

MEMORY

The starting point of the MySQL Memory storage engine is speed. To get the fastest response time, the logical storage medium is system Memory.

While storing table data in Memory does provide high performance, when the mysqld daemon crashes, all Memory data is lost.

The gain in speed comes with some drawbacks.

It requires that data stored in Memory tables be in a constant length format, which means that variable length data types such as BLOB and TEXT cannot be used. VARCHAR is a variable length type, but because it is used as a fixed length CHAR inside MySQL, it can be used.

Memory storage engines are generally used in the following situations:

• The target data is small and accessed very frequently. The max_heap_table_size parameter controls the size of the Memory table. Setting this parameter limits the maximum size of the Memory table.
• If the data is temporary and must be immediately available, it can be stored in an in-memory table.
• Sudden loss of data stored in Memory tables does not have a substantial negative impact on application services.
• Memory supports both hash and B-tree indexes.

B-tree indexes have an advantage over hash indexes in that they can use partial and wildcard queries, as well as operators such as <, >, and >= to facilitate data mining.

Hash indexes are very fast for equality comparison, but much slower for range comparison, so hash index values are suitable for the = and <> operators, not for the < or > operators, and equally inappropriate for the order BY clause.

CSV

The CSV storage engine stores data based on CSV files.

• Because of the CSV storage engine’s own file format, NOT NULL must be mandatory for all columns.
• The CSV engine also does not support indexing and does not support partitioning.
• The CSV storage engine also contains a.frm file that stores the table structure, a.csv file that stores the data, and a meta-information file with the same name and extension.csm that stores the state of the table and the amount of data stored in the table.
• Each data line takes one text line.

CSV files can be directly edited by software such as Office, which may cause irregularities. If the content in the CSV file is damaged, you can use the CHECK TABLE or REPAIR TABLE command to CHECK and REPAIR

ARCHIVE

After archiving, many advanced functions are no longer supported, only the most basic insert and query functions are supported.

Before MySQL 5.5, Archive did not support indexes, but since MySQL 5.5, indexes have been supported.

Archive has a good compression mechanism. It uses the Zlib compression library to compress records in real time when they are requested, so it is often used as a repository.

BLACKHOLE

In the 5.x series, MySQL provided a Blackhole engine, the “Blackhole”, which does exactly what its name suggests: any data written to the engine is thrown away, not actually stored; The contents of the Select statement are always empty.

This is exactly what the /dev/null file in Linux does.

So what’s the use of an engine that can’t store data?

Blackhole does not store data, but MySQL will normally record binlogs and these binlogs will be synchronized to the Slave for subsequent processing.

This makes balckhole useful for situations where only the Binlog is required on the Master and no data is required.

BlackHole can also be used in the following scenarios

• Verify the dump file syntax
• Detect the load to use the Blackhole engine to detect the additional load required by the binlog function
• Because blackhole has minimal performance loss, it can be used to detect the performance of MySQL function points other than the storage engine.

PERFORMANCE_SCHEMA

Collect database server performance parameters.

MySQL users cannot create PERFORMANCE_SCHEMA tables, which are used to record binlog as a replication relay.

MySQL Performance Schema MySQL Performance Schema

FEDERATED

A proxy is mainly used to access other remote MySQL servers. It creates a client connection to the remote MySQL server, and transfers queries to the remote server for execution, and then completes data access. The implementation on MariaDB is FederatedX

other

Here are some storage engines provided by other databases: OQGraph, SphinxSE, TokuDB, Cassandra, CONNECT, SQUENCE.

The name provided is for reference only. If you want to know more details, you can consult the information by yourself.

Comparison of Common Engines

mysql> SHOW VARIABLES LIKE '%storage_engine%'; +----------------------------------+---------+ | Variable_name | Value | |----------------------------------+---------| | default_storage_engine | InnoDB | | default_tmp_storage_engine | InnoDB | | disabled_storage_engines | | | Internal_tmp_disk_storage_engine | InnoDB | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- - + 4 rows in the set Time: 0.005 sCopy the code

mysql> SHOW ENGINES; +--------------------+---------+--------------------------------------+-------------+--------+-----------+ | Engine | Support | Comment | Transactions| XA | Savepoints| |--------------------+---------+--------------------------------------+-------------+--------+-----------| | InnoDB | DEFAULT | Supports transactions, | YES | YES | YES | | | | row-level locking, and foreign keys | | | | | MRG_MYISAM | YES | Collection of identical MyISAM tables| NO | NO | NO | | MEMORY | YES | Hash based, stored in memory, useful | NO | NO | NO | | | | for temporary tables | | | | | BLACKHOLE | YES | /dev/null storage engine (anything | NO |  NO | NO | | | | you write to it disappears) | | | | | MyISAM | YES | MyISAM storage engine | NO | NO | NO | | CSV | YES  | CSV storage engine | NO | NO | NO | | ARCHIVE | YES | Archive storage engine | NO | NO | NO | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | | FEDERATED | NO | Federated MySQL storage engine | <null> | <null> | <null> |  +--------------------+---------+--------------------------------------+-------------+--------+-----------+Copy the code

[mysqld]
default_storage_engine=CSV
Copy the code

CREATE TABLE `user` (
  `id`     int(100) unsigned NOT NULL AUTO_INCREMENT,
  `name`   varchar(32) NOT NULL DEFAULT ' ' COMMENT 'name'.`mobile` varchar(20) NOT NULL DEFAULT ' ' COMMENT 'mobile phone',
  PRIMARY KEY (`id`))ENGINE=InnoDB;
Copy the code