MySQL Storage Engine Basics

We talked about MySQL transactions in the previous article. Now we all know about MySQL transactions. Remember the ACID principle of transactions? Children who don't remember can review "First Understanding of MySQL Transactions". In fact, if it is more rigorous, it should be MySQL InnoDB storage engine, because in MySQL, only InnoDB storage engine supports transactions. Seeing this, some friends may have the following questions:

What is a storage engine?

What are the storage engines in MySQL?

What are the characteristics and differences of each storage engine?

Below, with these questions, we look down in turn:

What is a storage engine?

Generally speaking, MySQL is used to save data, right? We can think of the storage engine as a way of storing files and a complete set of tools attached to this way, in which the characteristics of each way of storing files are the characteristics of the storage engine.

For example, the Memory storage engine saves data to memory, which has the advantages of fast reading and writing, but the data is not persisted to disk, and it is easy to lose.

Storage Engine in MySQL

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

InnoDB

MyISAM

Memory

CSV

Archive

Blackhole

Merge:

Federated

Example

The following only introduces the commonly used storage engine, other storage engines that are not introduced, and those who are interested in children's shoes can search by themselves.

InnoDB: Supports transactional operations (such as begin, commit, rollback commands), and supports row-level locks. Compared with table locks, row-level locks have finer granularity and allow larger concurrency, which is quite detailed. Next time, we will write a separate article), which supports foreign key referential integrity constraints. The InnoDB storage engine is also the default storage engine in MySQL 5.7. Its disadvantage is that the storage space will occupy a large amount.

MyISAM: This storage engine takes up much less storage space than InnoDB storage engine, but it supports table locking, its concurrency performance is much lower, and it does not support transactions, which is usually only applied to 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, and there was a name called "Heap" before.
Application scenario: It mainly stores 1 non-critical data that needs quick access. Why not critical data? Just because all its data is stored in memory, it can also be understood as unsafe.

CSV: First of all, I know CSV. CSV files are actually text files separated by commas, which are often used for data conversion. This type is seldom used and does not support indexing.

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 it will not be used as a production environment.

How do I choose a storage engine

As you can see from the above comparison, the InnoDB storage engine supports transactions, foreign keys and row-level locks. Best suited for applications that require online transaction processing, when we choose the 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 table, and if not, use the default storage engine.

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) Displays the table's storage engine

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 that it is not recommended to modify the storage engine of the table. When creating the table, it is necessary to consider what storage engine to use.

Orders of the Day

Command: show engines;

Standard syntax: show stroage engines;

Where stroage is optional.

Function: Displays 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: Very useful when looking at the storage engines supported by the current database version and looking at the default storage engine.

Engine: Storage engine name.

Support: Indicates whether the current server version of MySQL supports the storage engine, while YES supports NO does not.

Comment: Features of this storage engine, such as Innodb

Support transactions, row-level locks, and so on.

Transactions: Whether transactions are supported, YES is supported, No is not supported.

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