MySQL database - storage engine (MyISAM and InnoDB)


Database storage engine is the underlying software organization of database. Database management system (DBMS) uses data engine to create, query, update and delete data. Different storage engines provide different storage mechanisms, indexing skills, locking levels and other functions. Using different storage engines, you can also obtain specific functions. Nowadays, many different database management systems support a variety of different data engines. The core of MySQL is the storage engine.

1, Storage engine concept introduction

  • The data in MySQL is stored in files with various technologies. Each technology uses different storage mechanisms, indexing skills, locking levels, and finally provides different functions and capabilities. These different technologies and supporting functions are called storage engines in MySQL

  • The storage engine is the storage method or format in which MySQL stores data in the file system

  • Common MySQL storage engines:

  • Components in MySQL database are responsible for performing actual data I/O operations

  • In MySQL system, the storage engine is on top of the file system. The data will be transferred to the storage engine before being saved to the data file, and then stored according to the storage format of each storage engine


1. Introduction to MyISAM

  • MyISAM does not support transactions or foreign key constraints. It only supports full-text indexing. Data files and index files are saved separately

  • Fast access speed, no requirement for transaction integrity
    MyISAM is suitable for query and insert based application scenarios

  • MyISAM is stored in three files on disk. The file name and table name are the same, but the extensions are:
    The. frm file stores the definition of the table structure
    The extension of the data file is. MYD (MYData)
    The extension of the index file is. MYI (MYIndex)

  • In the form of table level locking, the entire table is locked when data is updated
    The database blocks each other in the process of reading and writing: --- serial operation, which operates in sequence. The whole table will be locked each time it is read or written
    It will block the reading of user data during data writing
    It will also block the user's data writing during data reading
    Features: the data is written or read separately, with fast process speed and relatively less resources

  • MyIsam is a table level lock and cannot be read or written at the same time
    The advantages are: when executed separately, the speed is fast and the resource occupation is relatively small (relatively small)

2.MyISAM table supports three different storage formats:

(1) Static (fixed length) table

  • Static tables are the default storage format. The fields in the static table are non variable fields, so each record is of fixed length. The advantages of this storage method are that it is stored very quickly, easy to cache, and easy to recover in case of failure; The disadvantage is that it usually occupies more space than dynamic tables.
    Fixed length 10
  • Storage is very fast, container cache, and easy to recover after failure
    id(5) char(10)

(2) Dynamic table

  • Dynamic tables contain variable fields (varchar) and records are not of fixed length. The advantage of this storage is that it takes less space, but frequent updating and deleting records will produce fragments. It is necessary to regularly execute the OPTIMIZE TABLE statement or myisamchk -r command to improve performance, and it is relatively difficult to recover in case of failure.

(3) Compression table

  • The compressed table is created by the myisamchk tool and occupies a very small space. Because each record is compressed separately, there is only a very small access expense.

3. Production scenarios applicable to MyISAM

  • The company's business does not need transaction support

  • Businesses that unilaterally read or write more data

  • The MyISAM storage engine reads and writes data frequently, which is not suitable for the scenario

  • Use read-write concurrency to access relatively low services

  • Businesses with relatively few data modifications

  • Businesses that do not require very high data business consistency

  • The server hardware resources are relatively poor

  • MyIsam: it is suitable for one-way task scenarios with low concurrency and low transaction requirements

3, InnoDB

In addition to MyISAM storage engine, another commonly used storage engine in MySQL is InnoD

1. Introduction to InnoDB

  • InnoDB storage engine is different from MyISAM storage engine. It can support transaction security (ACID compatible) and has the capabilities of commit, rollback and crash recovery
  • InnoDB supports row level locking, and can provide a non locked read similar to Oracle style in the SELECT statement. It also supports FOREIGN KEY enforcement
  • Various features improve the multi-user deployment and performance of InnoDB storage engine
  • The InnoDB storage engine also supports mixing InnoDB tables with other MySQL table types in SQL queries, even in the same query
  • InnoDB is designed for maximum performance when handling large amounts of data
  • Its CPU efficiency may be unmatched by any other disk based relational database engine
  • The InnoDB storage engine is fully integrated with the MySQL server to maintain its own buffer pool for caching data and indexes in memory
  • InnoDB stores its tables and indexes in a table space, which can contain several files (or raw disk partitions)
  • This is different from MyISAM. For example, in MyISAM tables, each table is stored in a separate file
  • The InnoDB table can be of any size, even on an operating system where the file size is limited to 2GB

2. Features of InnoDB

  • Supports transactions and four transaction isolation levels
  • Row level locking, but full table scanning will still be mark locking
  • Read / write blocking is related to the transaction isolation level
  • It has very efficient caching characteristics, and can cache indexes and data
  • Tables and primary keys are stored in clusters
  • Support partition and tablespace, similar to Oracle database
  • Supports foreign key constraints. MySQL does not support full-text indexing before 5.5, but after 5.5
  • It is suitable for occasions with high requirements for hardware resources

3. Production scenarios applicable to InnoDB

According to the characteristics of InnoDB, you need to select an appropriate InnoDB storage engine in the following scenarios

  • Business needs transaction support
  • Row level locking has good adaptability to high concurrency, but it needs to ensure that the query is completed through index
  • Scenarios where business data is updated frequently, such as forums, microblogs, etc
  • Business data requires high consistency, such as banking
  • The hardware device has a large memory. The better cache capacity of InnoDB is used to improve the memory utilization and reduce the pressure of disk IO

4. Basis for enterprises to select storage engine

When selecting a storage engine, you should consider the different core functions and common applications provided by each storage engine according to the actual needs of the enterprise system
It is mainly determined according to the following aspects:

  1. Fields and data types supported by the storage engine
    ① All engines support common data types, but not all engines support other field types
    ② Such as binary objects
  2. Lock type
    ① Different storage engines support different levels of locking
    ② Table locking: MyISAM support
    ③ Row locking: InnoDB support
  3. Index support
    ① Indexing can significantly improve performance when searching and recovering data in a database
    ② Different storage engines provide different indexing techniques
    ③ Some storage engines do not support indexing at all
  4. Transaction support
    ① The transaction processing function provides reliability during updating and inserting information into tables
    ② The storage engine can be selected according to whether the enterprise business needs to support transactions

5. Summary

  • The number of rows in the table is not saved in InnoDB. You need to scan the whole table to calculate the number of rows, but MyISAM can simply read the saved rows
  • It should be noted that when the "count()" statement contains the where condition, MyISAM also needs to scan the entire table
  • For self growing fields, InnoDB must contain an index only for this field, but combined indexes can be established with other fields in MyISAM table
  • When emptying the entire table, InnoDB is deleted row by row, which is very slow; MyISAM will rebuild the table

4, Configure the appropriate storage engine

  • After selecting the appropriate storage engine, you can modify it to the corresponding storage engine type. The modification steps are as follows:
    1. View the types of storage engines that can be configured in the database
    2. View the type of storage engine the table is using
    3. Configure the storage engine to the selected type

1. View the types of storage engines that can be configured in the 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       |

EngineEngine types supported by the current version of MySQL
SupportWhether the corresponding engine can be used. DEFAULT refers to the DEFAULT value
CommentBrief description of the storage engine
TransactionsDoes the corresponding engine support transactions
XADoes the storage engine support XA transactions
SavepointsDoes the storage engine support savepoints

2. View the type of storage engine the table is using

2.1 mode I

show table status from Library name where name = 'Table name'\G;
#Add \ G to view vertically, and do not add it horizontally

mysql> show table status from zone where name='dsj'\G;
*************************** 1. row ***************************
           Name: dsj
         Engine: InnoDB		//The engine is innodb
        Version: 10
     Row_format: Dynamic
           Rows: 6
 Avg_row_length: 2730
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2021-10-24 13:37:02
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
1 row in set (0.01 sec)

No query specified

2.2 mode II

use Library name;
show create table Table name;

mysql> use zone;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show create table dsj\G;
*************************** 1. row ***************************
       Table: dsj
Create Table: CREATE TABLE "dsj" (
  "id" int(11) NOT NULL,
  "name" char(10) NOT NULL,
  "score" decimal(5,0) DEFAULT NULL,
  "passwd" char(48) DEFAULT '',
  PRIMARY KEY ("id")
) ENGINE=InnoDB DEFAULT CHARSET=utf8		//The engine is InnoDB
1 row in set (0.00 sec)

No query specified

3. Configure the storage engine to the selected type

3.1 method I

  • Modify by using the alter table command
use Library name;
alter table Table name engine=MyISAM;

mysql> use zone;	
Database changed
mysql> alter table dsj engine=MyISAM;	//Set the engine to MyISAM

mysql> show create table dsj\G;		//View table structure
*************************** 1. row ***************************
       Table: dsj
Create Table: CREATE TABLE "dsj" (
  "id" int(11) NOT NULL,
  "name" char(10) NOT NULL,
  "score" decimal(5,0) DEFAULT NULL,
  "passwd" char(48) DEFAULT '',
  PRIMARY KEY ("id")
) ENGINE=MyISAM DEFAULT CHARSET=utf8	//The engine has been modified to
1 row in set (0.00 sec)

No query specified

3.2 method II

  • By modifying the MySQL configuration file my.cnf, you can specify the default storage engine option to set the default storage engine
vim /etc/my.cnf

systemctl restart mysql.service
#Be sure to restart the service for the modified configuration to take effect

#Note: this method is only for newly created tables and has no impact on previously existing tables, that is, the storage engine category of the previous table will not be modified

3.3 method III

By specifying the storage engine with engine when creating a table using create table, the specified storage engine will be used

use Library name;
create table Table name(Field 1 data type,...) engine=MyISAM;

mysql> use zone;	//Switch Library

mysql> create table lion (name varchar(10),age char(4))engine=myisam;	//Create table assignment engine
Query OK, 0 rows affected (0.00 sec)

mysql> show create table lion\G;	//View table structure
*************************** 1. row ***************************
       Table: lion
Create Table: CREATE TABLE "lion" (
  "name" varchar(10) DEFAULT NULL,
  "age" char(4) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8		//The engine is myisam
1 row in set (0.00 sec)

No query specified


  • MyISAM
    No transaction support required (not supported)
    Relatively low concurrency (locking mechanism problem)
    Data modification is relatively few (blocking problem), mainly reading
    Data consistency requirements are not very high

  • InnoDB
    Transaction support is required (with good transaction characteristics)
    Row level locking has good adaptability to high concurrency, but it needs to ensure that the query is completed through index
    Scenarios with frequent data updates
    High data consistency requirements
    The hardware device has a large memory. The better cache capacity of InnoDB can be used to improve memory utilization and reduce disk IO as much as possible

  • The biggest difference between the two is that Innodb supports transaction processing, foreign keys and row level locks, while MyISAM does not

Tags: Database MySQL innodb

Posted on Mon, 25 Oct 2021 04:00:34 -0400 by Foser