Let's talk about the storage engine in MySQL

Basics

In a relational database, each data table is equivalent to a file, and different storage engines will construct different table types.

The role of the storage engine is to specify how the data table stores data, how to create indexes for the stored data, and how to support the implementation of technologies such as update and query.

In databases such as Oracle and SqlServer, only one storage engine is supported, so their data storage management mechanisms are the same. MySQL provides multiple storage engines. Users can choose different storage engines for data tables according to different needs. Users can also write their own storage engines according to their needs.

For example, you can use the txt type when processing text files, and the png type when processing images.

Storage Engine

MySQL supports multiple storage engines. Use show engines command to view the supported storage engines.

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| 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 |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

mysql>

InnoDB

The InnoDB storage engine is the default storage engine of MySQL. It supports transaction operations and is designed mainly for online transaction processing (OLTP) applications.

Its features include row lock design, foreign key support, and Oracle-like non-locking read support, that is, the default read operation will not generate locks. The InnoDB storage engine places data in a logical tablespace, which is managed by the InnoDB storage engine itself like a black box.

Starting from MySQL 4.1 (including 4.1), each InnoDB storage engine table can be stored in a separate ibd file. In addition, the InnoDB storage engine supports the use of raw devices (row disks) to create its tablespace. InnoDB achieves high concurrency by using multi-version concurrency control (MVCC) and implements four SQL standard isolation levels, with the default being the REPEATABLE level. It also uses a strategy called netx-key locking to avoid phantom reading.

In addition, the InnoDB storage engine also provides high-performance and high-availability features such as insert buffer, double write, adaptive hash index, and read ahead. For the storage of data in the table, the InnoDB storage engine adopts a clustered approach. Each table is stored in the order of the primary key. If the primary key is not explicitly specified when defining the table, the InnoDB storage engine will generate a 6-byte row ID (ROWID) for each row and use it as the primary key. InnoDB storage engine is the most commonly used engine in MySQL database. The successful application of Facebook, Google, Yahoo and other companies has proved that InnoDB storage engine has high availability, high performance and high scalability. Mastering and understanding its underlying implementation also requires time and technical accumulation.

If you want to learn more about the working principle, implementation, and application of the InnoDB storage engine, you can refer to the book "MySQL Technology Insider: InnoDB Storage Engine".

MyISAM

It does not support transactions or table lock design, but supports full-text indexing. It is mainly used for some OLAP database applications and was the default storage engine before MySQL 5.5.8 (except the Windows version). A big difference between database systems and file systems is the support for transactions. The MyISAM storage engine does not support transactions.

At its core, this is not difficult to understand. Do users need transactions in all applications? In a data warehouse, if there are no ETL operations, is transaction support still needed for simple report queries? In addition, another unique feature of the MyISAM storage engine is that its buffer pool only caches index files, not data files, which is different from most databases.

NDB

In 2003, MySQL AB acquired the NDB storage engine from Sony Ericsson.

The NDB storage engine is a cluster storage engine, similar to Oracle's RAC cluster. However, unlike Oracle RAC's share everything structure, its structure is a share nothing cluster architecture, so it can provide a higher level of high availability.

The characteristic of the NDB storage engine is that all data is stored in memory (starting from version 5.1, non-index data can be stored on disk), so primary key lookups are extremely fast, and NDB data storage nodes can be added online to linearly improve database performance.

It can be seen that the NDB storage engine is a highly available, high-performance, and highly scalable database cluster system, which is also aimed at OLTP database application types.

Memory

As the name suggests, data in the Memory storage engine is stored in memory.

If the database is restarted or crashes, the data in the table will disappear. It is very suitable for storing temporary tables of temporary data in OLTP database applications, and can also be used as a dimension table of the data warehouse in OLAP database applications.

The Memory storage engine uses hash indexes by default instead of the familiar B+ tree indexes.

Infobright

  Third-party storage engines.

Its characteristic is that the storage is based on columns rather than rows, so it is very suitable for OLAP database applications.

Its official website is http://www.infobright.org/, which contains many successful data warehouse cases for analysis.

NTSE

A storage engine developed by NetEase for internal use.

The current version does not support transactions, but provides features such as compression and row-level caching. Memory-oriented transaction support will be implemented in the near future.

BLACKHOLE

The hole storage engine can be applied to the distribution master library in master-slave replication.

Configuration Engine

Create table and specify

Use engine keyword after the table creation statement to specify the storage engine.

create table table name (id int, name char) engine = storage engine (default innodb);

The following will create a temporary table named temp using the memory storage engine.

mysql> create table temp(id int) engine=memory;
Query OK, 0 rows affected (0.01 sec)

mysql> show create table temp; # View creation information+-------+------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------+
| temp | CREATE TABLE `temp` (
 `id` int(11) DEFAULT NULL
) ENGINE=MEMORY DEFAULT CHARSET=latin1 |
+-------+------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

The data in memory will be cleared when the MySQL service is shut down.

The characteristic of the blackhole storage engine is that no matter how many records are inserted, they will never be stored in the table.

Configuration Assignment

In the configuration file, you can also specify the storage engine when creating a table.

[mysqld]
#The default storage engine that will be used when creating a new table default-storage-engine=INNODB

File Structure

Here we take InnoDB as an example. We first create a student table and then view its file structure.

mysql> create table student(id int) engine=innodb;
Query OK, 0 rows affected (0.02 sec) 

student.frm stores the table structure, such as fields and other information

student.ibd stores table data, such as records and other information

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