MySQL storage engine basics

In the previous article, we talked about MySQL transactions. Now everyone should know about MySQL transactions. Do you still remember the ACID principle of transactions? Those who don’t remember can review “Introduction to MySQL Transactions”. In fact, to be more precise, it should be the MySQL InnoDB storage engine, because in MySQL, only the InnoDB storage engine supports transactions. Seeing this, some friends may have the following questions:

What is a storage engine?

What storage engines are there in MySQL?

What are the characteristics and differences of each storage engine?

Next, let's take these questions and look at them one by one:

What is a storage engine?

To put it simply, MySQL is used to store data, right? We can think of a storage engine as a way of storing files and a set of tools that come with it, where the characteristics of each file storage method are the characteristics of the storage engine.

For example, the Memory storage engine saves data in memory. Its advantages are: fast reading and writing, but the data is not persisted to the disk and is very easy to lose, etc.

Storage Engines in MySQL

In MySQL 5.7, the storage engines supported by MySQL are:

InnoDB

MyISAM

Memory

CSV

Archive

Blackhole

Merge:

Federated

Example

The following only introduces commonly used storage engines. If you are interested in other storage engines that are not introduced, you can search them yourself.

InnoDB: supports transaction operations (such as begin, commit, rollback commands), supports row-level locks. Compared with table locks, row-level locks have finer granularity and allow greater concurrency. There are many details here. Next time, we will write a separate article) supports foreign key referential integrity constraints. The InnoDB storage engine is also the default storage engine in MySQL version 5.7. The disadvantage is that it takes up a lot of storage space.

MyISAM: This storage engine takes up much less space than the InnoDB storage engine, but it only supports table locks, has much lower concurrency performance, and does not support transactions. It is usually only used in read-only applications. It is the original storage engine of MySQL.

Memory: The biggest feature of this storage engine is that all data is stored in memory. It was previously called "Heap".
Application scenario: Mainly store some non-critical data that needs to be accessed quickly. Why is it not critical data? Just because all its data is stored in memory, it can also be understood as unsafe.

CSV: First, let's get to know CSV. A CSV file is actually a text file separated by commas. It is often used for data conversion. This type is rarely used and does not support indexes.

Archive: Archive files, mainly used to store rarely used reference files.

Example: This storage engine is mainly used to show how to write a storage engine by yourself and is generally not used in a production environment.

How to choose a storage engine

You should have seen from the above comparison that the InnoDB storage engine supports transactions, foreign keys, and row-level locks. It is most suitable for applications that require online transaction processing. When choosing a storage engine, if there is no special reason, my suggestion is to choose InnoDB as the storage engine.

1. We can specify the storage engine when creating a table. If not specified, the default storage engine is used.

create table t_base_user(
oid bigint(20) not null primary key auto_increment comment "",
created_at datetime null comment ''
)engine=innodb

2. (Method 1) Display the storage engine of the table

mysql> show table status like "t_base_user" \G;
*************************** 1. row ***************************
    Name: t_base_user
   Engine: InnoDB
   Version: 10
 Row_format: Dynamic
    Rows: 0
Avg_row_length: 0
 Data_length: 16384
Max_data_length: 0
Index_length: 0
  Data_free: 0
Auto_increment: 1
 Create_time: 2017-12-17 20:10:24
 Update_time: NULL
 Check_time: NULL
  Collation: utf8_unicode_ci
  Checksum: NULL
Create_options: 
   Comment: 
1 row in set (0.01 sec)

3. (Method 2) Display the storage engine information of the table

mysql> show create table t_base_user\G;
*************************** 1. row ***************************
  Table: t_base_user
Create Table: CREATE TABLE `t_base_user` (
`oid` bigint(20) NOT NULL AUTO_INCREMENT,
`created_at` datetime DEFAULT NULL,
PRIMARY KEY (`oid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
1 row in set (0.00 sec)

Another thing to note is: It is not recommended to modify the storage engine of a table. When creating a table, you need to consider which storage engine to use.

Today's Order

Command: show engines;

Standard syntax: show stroage engines;

Where stroage is optional.

Function: Display the storage engines supported by the current MySQL version.

Example (MySQL version: 5.7.20):

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

Usage scenario: This function is very useful for viewing the storage engines supported by the current database version and the default storage engine.

Engine: storage engine name.

Support: Indicates whether the current MySQL server version supports the storage engine. YES means it is supported and NO means it is not supported.

Comment: The characteristics of the storage engine, such as Innodb

Supports transactions, row-level locks, etc.

Transactions: whether to support transactions, YES means support, No means no support.

XA and Savepoints: These two attributes are related to transactions. When Transactions is Yes, these two attributes are meaningful, otherwise they are both NO.