MySQL storage engine

Original author, public number [programmer reading], welcome to pay attention to the public number, reprint the article please indicate the source oh.

MySQL storage engine architecture design, compared with other relational database management systems, such as Oracle,SQL Server and other databases, this is the biggest feature of MySQL, MySQL is the most different place, therefore, in this article we will briefly understand.

Storage Engine Architecture

MySQL’s storage engine architecture is designed to be pluggable, meaning that the current storage engine can be replaced at any time to meet storage and performance requirements without affecting any business logic.

In fact, we can see this process through the following MySQL service architecture diagram:

As can be seen from the above figure,MySQL Server layer and storage engine layer provide unified API interface to interact with storage engine through MySQL Server. Server does not care about the implementation details of the underlying storage engine, while different storage engines have their own implementation of data storage, processing and query.

Viewing storage Engines

MySQL supports a variety of storage ENGINES. If you want to see how many storage ENGINES your MySQL server supports, use the SHOW ENGINES command. For example:

mysql> SHOW ENGINES;
Copy the code

The query results are as follows:

+--------------------+---------+----------------------------------------------------------------+--------------+------+- -----------+ | 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, storedin memory, useful fortemporary 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 rowsin set (0.00 sec)
Copy the code

The following table shows the storage engines supported by my server. Of course, different versions of MySQL support different storage engines.

Engine: storage Engine name.

Support: indicates whether the engine is supported and whether the engine is the DEFAULT storage engine. YES indicates that the engine is supported, NO indicates that the engine is not supported, and DEFAULT indicates that the engine is the DEFAULT storage engine.

Comment: A brief introduction to the storage engine.

Transactions: indicates whether the engine supports Transactions.

XA: Indicates whether the storage engine supports distributed transactions.

Savepoints: indicates whether the storage engine supports partial transaction rollback.

Storage Engine Introduction

Among the storage engines listed above, InnoDB and MyISAM are the most commonly used storage engines, so we focus on the introduction of these two storage engines, and comparative study.

InnoDB

InnoDB storage engine is the default storage engine of MySQL. It supports row-level locking, MVCC multi-version control, non-locked read, transaction, external health and other features. The support for transaction makes MySQL become a more complete database management system.

In general, InnoDB storage engine is preferred for creating tables using MySQL if there are no other special requirements.

MyISAM

Before MySQL5.1, MyISAM was the default storage engine. MyISAM supported table-level locking, but did not support transactions. MyISAM was suitable for a large number of SELECT and INSERT operations.

Comparison between InnoDB and MyISAM storage engine

Let’s compare the two storage engines in the following ways.

Transaction support

InnoDB supports ACID transactions. MyISAM does not support transactions. It is important to note that if we need to conduct transactions in our application, all data will use InnoDB storage engine. MyISAM does not roll back to its previous state, so MyISAM does not support transactions.

Index and primary key processing

The InnoDB storage engine uses a clustered index. InnoDB primary key leaves are the data in the row, while other indexes point to the primary key. MyISAM storage engine uses a non-clustered index, where the primary key and other index leaves store Pointers to data.

Another is that MyISAM tables allow no primary key and other indexes, whereas InnoDB tables without primary keys generate a 6-byte primary key that is invisible to the user.

A foreign key

MyISAM does not support foreign keys, while Innodb supports foreign key associations between tables.

Store files differently

Innodb storage file FRM, IBD, and MyISAM is FRM, MYD, MYI. Innodb storage file FRM is the data structure definition file, IBD is the data file,MyISAM FRM is the data structure definition file, MYD is the data file, MYI is the index file.

select count(*)

Mysql > select count(*) from MyISAM; mysql > select count(*) from InnoDB; mysql > select count(*) from MyISAM; mysql > select count(*) from InnoDB;

The level of the lock

InnoDB supports row-level locking, while MyISAM only supports table-level locking, so InnoDB supports high concurrency.

Set the default storage engine

As mentioned above, InnoDB is the default storage engine for MySQL. Of course, we can also set other storage engines as the default storage engine. There are two ways to set InnoDB, such as:

Set when the server starts

When the server starts, run the –default-storage-engine command to set the default storage engine:

mysqld --default-storage-engine=MyISAM
Copy the code

The default storage for temporary tables can also be set using the –default-tmp-storage-engine parameter:

mysqld --default-tmp-storage-engine=MyISAM
Copy the code

Set by using the set command

In addition to setting the default storage engine when starting MySQL server, you can also SET the default storage engine by using the SET command after logging in to the server:

Specify the default storage engine for plain tables:

SET default_storage_engine=MyISAM;
Copy the code

Specify the default storage engine for temporary tables:

SET default_tmp_storage_engine=MyISAM
Copy the code

Set up the storage engine for the data table

You can specify storage engines for tables either when the tables are created using the CREATE TABLE statement or when the tables are already created.

Build table

When creating a table, if no storage engine is specified, the default storage engine set above will be used, as in our table construction statement:

CREATE TABLE users(
    id INT NOT NULL,
    name VARCHAR(20) NOT NULL,
    gender TINYINT NOT NULL,
    email VARCARH(50) NOT NULL,
    PRIMARY KEY(id)
);
Copy the code

If we do not want to use the default storage ENGINE when creating a TABLE, we can set the storage ENGINE by specifying the ENGINE parameter after the CREATE TABLE statement, for example:

CREATE TABLE users(
    id INT NOT NULL,
    name VARCHAR(20) NOT NULL,
    gender TINYINT NOT NULL,
    email VARCARH(50) NOT NULL,
    PRIMARY KEY(id)
)ENGINE=MyISAM;
Copy the code

Modify the data table storage engine

For tables already created, we can use the ALTER TABLE statement to change the storage engine. For example, we can change the users TABLE above to Innodb storage engine.

mysql> ALTER TABLE users engine=InnoDB;
Copy the code

summary

In MySQL, the storage, query, processing and modification of data are performed by the storage engine. With the design of MySQL pluggable storage engine, we can choose a storage engine suitable for ourselves according to our own business needs.

---

If you think the article is good, welcome to scan code attention, your attention is my biggest motivation to write