Basic knowledge of MySQL storage engine and basic knowledge of mysql Engine

Basic knowledge of MySQL storage engine and basic knowledge of mysql Engine

We mentioned MySQL transactions in previous articles. Now everyone should know about MySQL transactions. Do you still remember the ACID principles of transactions? If you do not remember, you can review MySQL's first knowledge of transactions. In fact, if you are more rigorous, it should be the MySQL InnoDB Storage engine, because in MySQL, only the InnoDB Storage engine supports transactions. Some friends may have the following questions:

What is the storage engine?

What storage engines does MySQL have?

What are the characteristics and differences of each storage engine?

Next, let's look at these questions in sequence:

What is the storage engine?

In other words, MySQL is used to store data, right? We can regard the storage engine as a method for storing files and a complete set of tools attached to this method. The features of each storage file method are the characteristics of this storage engine.

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

Storage engine in MySQL

MySQL 5.7 supports the following storage engines:

InnoDB

MyISAM

Memory

CSV

Archive

Blackhole

Merge:

Federated

Example

The following describes only common storage engines. If you are interested in other storage engines, you can search for them by yourself.

InnoDB: supports transaction operations (such as begin, commit, and rollback commands) and row-level locks. Compared with table locks, row-level locks have a finer granularity and allow a larger concurrency, there are a lot of details here. Next time, we will write an article separately) support the integrity constraints of foreign key references. The InnoDB Storage engine is also the default storage engine in MySQL 5.7. The disadvantage is that the storage space is large.

MyISAM: This storage engine consumes much less space than the InnoDB Storage engine. However, it supports table locks and has much lower concurrent performance and does not support transactions, it is usually used only for read-only applications. It is the most primitive storage engine of MySQL.

Memory: the largest feature of the storage engine is that all data is stored in the Memory, and the former name is "Heap 」.
Application Scenario: It mainly stores data that requires fast access and is not critical. Why is it not critical data? Because all the data is stored in the memory, it can also be considered unsafe.

CSV: first, let's take a look at CSV. a csv file is actually a text file separated by commas (,). It is often used for data conversion. This type is usually used less often and does not support indexing.

Archive: Archive files, mainly used to store referenced files that are rarely used,

Example: This storage engine is mainly used to show you how to write a storage engine. It is generally not used in a production environment.

How to select a storage engine

We can see 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. If there is no special reason for choosing a storage engine, I suggest using InnoDB as the storage engine.

1. You can specify the storage engine when creating a table. 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) 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: 0Avg_row_length: 0 Data_length: 16384Max_data_length: 0Index_length: 0  Data_free: 0Auto_increment: 1 Create_time: 2017-12-17 20:10:24 Update_time: NULL Check_time: NULL  Collation: utf8_unicode_ci  Checksum: NULLCreate_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_userCreate 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_ci1 row in set (0.00 sec)

Note that we do not recommend you modify the storage engine of a table. When creating a table, you must consider what storage engine to use.

Today's command

Command: show engines;

Standard Syntax: show stroage engines;

Stroage is optional.

Purpose: 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: it is very useful when viewing the storage engine supported by the current database version and viewing the default storage engine.

Engine: name of the storage Engine.

Support: indicates whether the current MySQL Server version supports the storage engine. If YES, NO is not supported.

Comment: For the features of the storage engine, such as Innodb

Supports transactions and row-level locks.

Transactions: Indicates whether Transactions are supported. YES indicates YES, and No indicates No.

XA and Savepoints: These two attributes are related to Transactions. When Transactions is Yes, these two attributes make sense. Otherwise, they are all NO.