MySQL Storage engine

MySQL Storage engine Storage Engine Introduction

Data in MySQL is stored in files (or memory) in a variety of different technologies. These different technologies and associated functions are called storage engines in MySQL.

MySQL offers a number of different storage engines that can be pre-set or enabled in MySQL server. Depending on your actual needs, you can also select the storage engine that is used for servers, databases, or tables to provide maximum flexibility in subsequent operations such as retrieving this information. Two of the most well-known storage engines, MyISAM and InnoDB, are mainly spoken here.

MyISAM Storage Engine

The MyISAM storage engine is the default storage engine before MySQL relational database system version 5.5. MyISAM mainly has the following characteristics:

1. Transactions are not supported, only non-transactional tables can be managed.

2, table-level locking form, the data is updated to lock the entire table.

3, the database in the process of reading and writing blocking each other.

4, you can set the cache index through key_buffer_size, improve the performance of access, reduce the pressure of disk IO.

5. The MyISAM storage engine is used to write or read data separately, which is faster and consumes less resources.

6. The MyISAM storage engine does not support foreign key constraints and only supports full-text indexing.

7. Each myisam is stored on disk as three files: the. frm file stores the table definition,. MyD store data files,. Myi stores the index file.

Applicable scenarios

1, the company business does not need the support of affairs.

2, generally unilaterally read more than the data of the business, or unilaterally write data more business, and the use of Read and write concurrent access to a relatively low business.

3, to read the main business, such as Ww,blog picture information database, user database, commodity library and other business.

4, the data business consistency requirements are not very high business.

5, the server hardware resources are relatively poor.

InnoDB Storage Engine

Unlike the MyISAM storage engine, the InnoDB storage engine is capable of supporting transactions, has the ability to commit, rollback, and crash recovery, and is also designed for maximum performance at large volumes of data. The main features are as follows:

1, support transaction, support four transaction isolation level.

2, row-level locking, but the full table scan will still be table-level locking (this situation is relatively small).

3. Read and write blocking is related to the transaction isolation level.

4, with very efficient caching features, the ability to cache indexes, can also cache data.

5. Table and primary key are stored in cluster mode.

6, support partition, table space, similar to Oracle database.

7, support foreign KEY constraints, Mysql 5.5 previously did not support full-text indexing, 5.5 later support.

8, suitable for the high requirements of hardware resources occasions.

Applicable scenarios

1, the company business needs the support of affairs.

2, row-level locking for high concurrency has a good adaptability, but need to ensure that the query is completed through the index.

3, the Business data update more frequent scenes, such as forums, Weibo and so on.

4, business data consistency requirements are relatively high, such as banking business.

5, the hardware device memory is large, the use of InnoDB better cache capacity to improve memory utilization, reduce disk IO pressure.

Configure the appropriate storage engine to see the types of storage engines that the database can configure

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) mysql>

Since I am using MySQL version 5.7, the default storage engine is InnoDB.

View the storage engine that the table is using

1. Use Show table status to view the storage engine that the table is using.

Mysql> Show table status from Test where name= ' yx '; +------+--------+---------+------------+------+---------------- +-------------+-----------------+--------------+-----------+----------------+---------------------+------------ ---------+------------+-----------------+----------+----------------+---------+| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | create_options | Comment |+------+--------+---------+------------+------+----------------+-------------+-----------------+------- -------+-----------+----------------+---------------------+---------------------+------------+----------------- +----------+----------------+---------+| YX |      InnoDB | 10 |    Dynamic |           6 |       2730 |               16384 |            0 |         0 |           0 | NULL | 2018-07-03 22:36:46 | 2018-07-03 22:39:35 | NULL |     Utf8_general_ci |                NULL |         | |+------+--------+---------+------------+------+----------------+-------------+-----------------+-------------- +-----------+----------------+---------------------+---------------------+------------+-----------------+------ ----+----------------+---------+1 row in Set (0.00 sec)

You can see that the storage engine used by the YX table is InnoDB.

2. Use show create to view the storage engine in use.

mysql> show create table yx;+-------+------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table                                                                                                                 |+-------+------------------------------------------------------------------------------------------------------------------------------+| yx    | CREATE TABLE "yx" (  "name" char(10) DEFAULT NULL,  "score" decimal(5,2) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8 |+-------+------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)

Configure the storage engine for the selected type

When you need to select a non-default storage engine, there are three main ways to modify it.

1. When the table has been created, modify the table's storage engine, using the ALTER TABLE command.

Mysql> Show CREATE TABLE yx;+-------+-------------------------------------------------------------------------- ----------------------------------------------------+| Table |                                                                                                                 Create Table |+-------+----------------------------------------------------------------------------------------------------- -------------------------+| YX | CREATE TABLE "Yx" ("Name" char (TEN) default NULL, "Score" decimal (5,2) default null) ENGINE=INNODB default Charset=utf8 |+-------+----------------------------------------------------------------------------------------------------- -------------------------+1 Row in Set (0.00 sec) mysql> ALTER TABLE YX Engine=myisam; Query OK, 6 rows affected (0.80 sec) Records:6 duplicates:0 warnings:0mysql> Show CREATE table yx;+-------+-------- ----------------------------------------------------------------------------------------------------------------------+| Table |                                                                                                                 Create Table |+-------+----------------------------------------------------------------------------------------------------- -------------------------+| YX | CREATE TABLE "Yx" ("Name" char (TEN) default NULL, "Score" decimal (5,2) default null) Engine=myisam default Charset=utf8 |+-------+----------------------------------------------------------------------------------------------------- -------------------------+1 Row in Set (0.00 sec)

You can see that the modification was successful.

2, modify the MySQL configuration file my.cnf, you can specify the Default-storage-engine option to set the default storage engine.

mysql> use test;Database changedmysql> create table test01(name varchar(10),score decimal(5,2));Query OK, 0 rows affected (0.00 sec)mysql> show create table test01;+--------+-------------------------------------------------------------------------------------------------------------------------------------+| Table  | Create Table                                                                                                                        |+--------+-------------------------------------------------------------------------------------------------------------------------------------+| test01 | CREATE TABLE "test01" (  "name" varchar(10) DEFAULT NULL,  "score" decimal(5,2) DEFAULT NULL) ENGINE=MyISAM DEFAULT CHARSET=utf8 |+--------+-------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)

After the modifications are made to the database, a new table is created and the storage engine type of the view table has been modified to MyISAM.

3. Use the engine to specify the storage engine when creating tables using CREATE table.

mysql> create table tset02(id int) engine=InnoDB;Query OK, 0 rows affected (0.03 sec)mysql> show create table tset02;+--------+------------------------------------------------------------------------------------------+| Table  | Create Table                                                                             |+--------+------------------------------------------------------------------------------------------+| tset02 | CREATE TABLE "tset02" (  "id" int(11) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8 |+--------+------------------------------------------------------------------------------------------+1 row in set (0.00 sec)

Because the configuration file was previously modified, the default storage engine for creating a new table is MyISAM, so you can see that the modification was successful by specifying InnoDB.

MySQL Storage engine