Shulou(Shulou.com)06/01 Report--

We talked about MySQL transactions in the previous article, and now everyone should know about MySQL transactions. Do you remember the ACID principle of transactions? Children's shoes that you don't remember can review "the first knowledge of MySQL transactions". In fact, more strictly, it should be the MySQL InnoDB storage engine, because in MySQL, only the InnoDB storage engine supports transactions. Seeing here, some friends may have the following questions:

What is the storage engine?

What storage engines are there in MySQL?

What are the characteristics and differences of each storage engine?

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

What is the storage engine?

To put it more colloquially, MySQL is used to save data, right? We can regard the storage engine as a way to store files and a set of tools attached to this way, in which the characteristic of each file storage mode is the characteristic of the storage engine.

For example: Memory storage engine saves data to memory, its advantages are: read and write fast, but the data is not persistent to disk, very easy to lose, and so on.

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 engines, other unintroduced storage engines, interested children's shoes, you can search on your own.

InnoDB: supports transactional operations (such as begin, commit,rollback commands) and row-level locks. Row-level locks have finer granularity and larger allowed concurrency than table locks. There are many details. Next time, we will write a separate article) support foreign key referential integrity constraints. The InnoDB storage engine is also the default storage engine in MySQL version 5.7. The disadvantage is that the storage space will be relatively large.

MyISAM: this storage engine takes up much less space than the InnoDB storage engine, but it supports table locks, its concurrency performance is much lower, and it does not support transactions, so it is usually only used in read-only mode 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, which was previously called "Heap".

Application scenario: it mainly stores some non-critical data that requires quick access, so why not critical data? Just because all its data is kept in memory, it can also be understood as insecure.

CSV: first of all, let's recognize that CSV,CSV files are actually text files separated by commas, which are often used for data conversion. This type is usually used less 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 yourself, which is generally not used in a production environment.

How to choose a storage engine

As you can see from the above comparison, 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 particular reason for choosing a storage engine, my suggestion is to choose InnoDB as the storage engine.

1. When creating a table, we can specify the storage engine. If not, the default storage engine will be 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: 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\ Gbomber * 1. Row * * Table: t_base_userCreate Table: CREATE TABLE `troombaseuser` (`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)

It is also important to note that it is not recommended to modify the storage engine of the table, and you need to consider which storage engine to use when creating the table.

Today's order

Command: show engines

Standard syntax: show stroage engines

Where stroage is optional.

Purpose: displays the storage engines supported by the current version of MySQL.

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 | | CSV | YES | CSV storage engine | NO | | MyISAM | YES | MyISAM storage engine | NO | | BLACKHOLE | YES | / dev/null storage engine (anything you write to it disappears) | NO | | MEMORY | YES | Hash based Stored in memory, useful for temporary tables | NO | | InnoDB | DEFAULT | Supports transactions, row-level locking And foreign keys | YES | | ARCHIVE | YES | Archive storage engine | NO | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | FEDERATED | NO | Federated MySQL storage engine | 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 the default storage engine.

Engine: storage engine name.

Support: indicates whether the storage engine is supported by the current server version of MySQL. If YES supports the storage engine, NO does not.

Comment: features of the storage engine, such as Innodb

Support transactions, row-level locks, etc.

Transactions: whether transactions are supported or not is supported by YES and not by No.

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.