MySQL storage engine (Table type) selection, mysql Engine

MySQL storage engine (Table type) selection, mysql Engine

Unlike most databases, MySQL has a storage engine concept. You can select different storage engines based on data storage requirements. This blog introduces the storage engine in MySQL. MySQL version 5.7.19.

 

Overview

MySQL storage engines can be seen as plug-ins. Users can choose different storage engines as needed, such as whether transactions are supported and how data is indexed. Before 5.5, the default storage engine is MyISAM, and after 5.5, it is changed to InnoDB.

View the default storage engine of the current database. The default storage engine is InnoDB:

mysql> show variables like '%storage_engine%';+----------------------------------+--------+| Variable_name                    | Value  |+----------------------------------+--------+| default_storage_engine           | InnoDB || default_tmp_storage_engine       | InnoDB || disabled_storage_engines         |        || internal_tmp_disk_storage_engine | InnoDB |+----------------------------------+--------+4 rows in set, 1 warning (0.00 sec)

View the storage engines supported by the current database:

mysql> show engines;+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+| 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, stored in memory, useful for temporary 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 rows in set (0.00 sec)

When creating a table, you can specify the storage engine. If not specified, the storage engine is the default one:

mysql> create table t1(id int,name varchar(20)) engine=MyISAM;Query OK, 0 rows affected (0.01 sec)mysql> show create table t1 \G;*************************** 1. row ***************************       Table: t1Create Table: CREATE TABLE `t1` (  `id` int(11) DEFAULT NULL,  `name` varchar(20) DEFAULT NULL) ENGINE=MyISAM DEFAULT CHARSET=utf81 row in set (0.00 sec)

You can use the alter table statement to modify the engine:

mysql> alter table t1 engine=innodb;Query OK, 0 rows affected (0.07 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> show create table t1 \G;*************************** 1. row ***************************       Table: t1Create Table: CREATE TABLE `t1` (  `id` int(11) DEFAULT NULL,  `name` varchar(20) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec)

 

Features of various storage engines

1. MyISAM

MyISAM is the default storage engine before 5.5. Its advantages and disadvantages are as follows:

Advantages: 1. the access speed is fast, and there is no requirement on transaction integrity, or the engine can be used for SELECT-based applications based on INSERT. Disadvantages: 1. transactions are not supported. Foreign keys are not supported. 2. myISAM tables may be damaged and cannot be accessed after they are damaged. You can use tools to CHECK and REPAIR the tables ).. Each MyISAM is stored on a disk as three files. The file name is the same as the table name, but the extension is as follows:

Each MyISAM is stored on a disk as three files. The file name is the same as the table name, but the extension is as follows:

. Frm (storage table scheduled). MYD (storage data). MYI (storage index)

MyISAM supports three different storage formats:

Fixed-length table: each field has a fixed length, which is easy to store and cache. The disadvantage is that it occupies space. The insufficient field length will fill up spaces, making it easy to recover when a fault occurs. Dynamic table: it contains variable length fields, which occupy less space, but frequently updates and deletes or generates fragments. optimize table can be used to improve performance and cannot be easily recovered due to faults. Compressed TABLE: created using myisampack, the occupied space is very small.

Note: If the string is followed by a space, it will be removed when the result is returned.

 

Ii. InnoDB

InnoDB is the default storage engine after 5.5. Its advantages and disadvantages are as follows:

Advantage: it has the ability to commit, roll back, and crash recovery transactions; disadvantage: The processing efficiency is poor, it will occupy more disk space to retain data and indexes.

Features:

1. supports auto-increment columns. For example, you can add two columns by id. foreign key constraints: MySQL only supports Innodb. When creating foreign keys, the parent table must have corresponding indexes, when a sub-Table creates a foreign key, the corresponding index is automatically created.

There are two ways to store tables and indexes in InnoDB:

1. shared tablespace is used. The table structure created in this way is saved in. in the frm file, data and indexes are stored in the tablespace defined by innodb_data_home_dir and innodb_data_file_path. It can be multiple files. the table structure created in this mode is still stored in. frm file, but the data and indexes of each table are stored separately in. in ibd, if it is a partitioned table, each partition corresponds to a separate one. the ibd file is named "table name + partition name". You can specify the data location of each partition when creating a partition. In this way, the I/O of the table is evenly distributed across multiple disks.

Note: Even when multiple tablespaces are stored, shared space is still required. InnoDB stores internal data dictionaries and online redo logs in this file.

 

3. MEMORY

The MEMORY storage engine uses the content in the MEMORY to create tables. The advantages and disadvantages are as follows:

Advantage: fast access because the data is stored in the memory and the HASH index is used by default. disadvantage: Once the service is disabled, the data in the table will be lost.

Create a MEMORY table:

Mysql> create table t2 engine = memory select sid, sname from student where sid <6; Query OK, 5 rows affected (0.01 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> select * from t2; + ----- + -------- + | sid | sname | + ----- + -------- + | 1 | Li Jie | 2 | steel egg | 3 | Zhang San | 4 | Zhang Yi | 5 | Zhang 'er | + ----- + -------- + 5 rows in set (0.00 sec) mysql> show table status like 't2' \ G; * *************************** 1. row ************************* Name: t2 Engine: MEMORY Version: 10 Row_format: fixed Rows: 5 Avg_row_length: 101 Data_length: Weight: 16293219 Index_length: 0 Data_free: 0 Auto_increment: NULL Create_time: 23:57:59 Update_time: NULL Check_time: NULL Collation: Fill Checksum: NULL Create_options: comment: 1 row in set (0.00 sec)

When creating an index for MEMORY, you can specify whether it is a HASH index or a BTREE index:

mysql> create index mem_hash using btree on t2(sname);Query OK, 5 rows affected (0.01 sec)Records: 5  Duplicates: 0  Warnings: 0mysql> show index from t2 \G;*************************** 1. row ***************************        Table: t2   Non_unique: 1     Key_name: mem_hash Seq_in_index: 1  Column_name: sname    Collation: A  Cardinality: NULL     Sub_part: NULL       Packed: NULL         Null:   Index_type: BTREE      Comment:Index_comment:1 row in set (0.00 sec)

To release a TABLE, DELETE the TABLE using delete from or truncate table. DELETE the TABLE using drop table.

 

4. MERGE

The MERGE storage engine is a combination of a group of MyISAM tables. These MyISAM tables must have the same structure. The MERGE table retains two files on the disk, starting with the table name. frm file storage table definition, the other is. the MRG file contains information about the combined tables.

Because a MERGE table consists of multiple tables, you must set INSERT_METHOD to define the insert operation for the MERGE table:

INSERT_METHOD = FIRST # when inserted in MERGE, It is inserted only in the FIRST table; INSERT_METHOD = LAST # when inserted in MERGE, It is inserted only in the LAST table; INSERT_METHOD = NO # This MERGE table cannot be inserted.

When you DROP a MERGE table, it does not affect the tables in the combined table.

For example, to create a MERGE table:

(1) create tables m1, m2, and m3 and the MERGE tables of the first two tables:

mysql> create table m1(id int,name varchar(20)) engine=myisam;Query OK, 0 rows affected (0.01 sec)mysql> create table m2(id int,name varchar(20)) engine=myisam;Query OK, 0 rows affected (0.01 sec)mysql> create table m3(id int,name varchar(20)) engine=merge union(m1,m2) insert_method=last;Query OK, 0 rows affected (0.01 sec)

(2) Insert data in m1 and m2 and view:

mysql> insert into m1(id,name) values(1,'frank');Query OK, 1 row affected (0.00 sec)mysql> insert into m2(id,name) values(1,'rose');Query OK, 1 row affected (0.00 sec)mysql> select * from m1;+------+-------+| id   | name  |+------+-------+|    1 | frank |+------+-------+1 row in set (0.00 sec)mysql> select * from m2;+------+------+| id   | name |+------+------+|    1 | rose |+------+------+1 row in set (0.00 sec)mysql> select * from m3;+------+-------+| id   | name  |+------+-------+|    1 | frank ||    1 | rose  |+------+-------+2 rows in set (0.00 sec)

When data is inserted on m1 and m2, The merge table also adds rows. The following Insert rows in m3:

mysql> insert into m3(id,name) values(2,'alex');Query OK, 1 row affected (0.00 sec)mysql> select * from m1;+------+-------+| id   | name  |+------+-------+|    1 | frank |+------+-------+1 row in set (0.00 sec)mysql> select * from m2;+------+------+| id   | name |+------+------+|    1 | rose ||    2 | alex |+------+------+2 rows in set (0.00 sec)mysql> select * from m3;+------+-------+| id   | name  |+------+-------+|    1 | frank ||    1 | rose  ||    2 | alex  |+------+-------+3 rows in set (0.00 sec)

M3 is successfully added, and m1 is not added with a new row. Because insert_method is last, only the last table is updated.

In addition to the storage engine provided by MySQL, there are also some third-party storage engines, such as Infobright and TokuDB.

Compare and select the following table:

Suggestion:

1. myISAM: if the application is dominated by read operations or insert operations, there are only a few update and delete operations, and the transaction integrity and concurrency requirements are not very high, you can choose, such as Web and data warehouse; 2. innoDB: An application used for transaction processing. It supports foreign keys. InnoDB can be used for billing systems with high data accuracy or financial systems. Reference data: recommended for MySQL ~