MySQL database - storage engine (MyISAM and InnoDB)

preface

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:
MyISAM
InnoDB
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

2, MyISAM

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

(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:

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
Lock type
① Different storage engines support different levels of locking
② Table locking: MyISAM support
③ Row locking: InnoDB support
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
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       |

parameter	explain
Engine	Engine types supported by the current version of MySQL
Support	Whether the corresponding engine can be used. DEFAULT refers to the DEFAULT value
Comment	Brief description of the storage engine
Transactions	Does the corresponding engine support transactions
XA	Does the storage engine support XA transactions
Savepoints	Does the storage engine support savepoints

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

2.1 mode I

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

#Example:
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
 Create_options: 
        Comment: 
1 row in set (0.01 sec)

ERROR: 
No query specified

2.2 mode II

use Library name;
show create table Table name;


Example:
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)

ERROR: 
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;


Example:
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)

ERROR: 
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
[mysqld]
default-storage-engine=INNODB

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;


#Example:
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)

ERROR: 
No query specified

summary

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

Programmer Help