Briefly describe the MySQL InnoDB storage engine

Preface:

The storage engine is the core of the database. For MySQL, the storage engine runs in the form of a plug-in. Although MySQL supports a wide variety of storage engines, the most commonly used one is InnoDB. This article will mainly introduce the knowledge related to the InnoDB storage engine.

1. Introduction to InnoDB

After MySQL version 5.5, the default storage engine is InnoDB. InnoDB is a general-purpose storage engine that combines high reliability and high performance. In MySQL 5.7, unless you have configured a different default storage engine, executing a CREATE TABLE statement without specifying an ENGINE will create an InnoDB table.

# View the storage engines supported by MySQLmysql> 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 |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

# View the default storage enginemysql> show variables like 'default_storage_engine';
+------------------------+--------+
| Variable_name | Value |
+------------------------+--------+
| default_storage_engine | InnoDB |
+------------------------+--------+

2. InnoDB Advantages

The reason why InnoDB is so popular is mainly due to its many functional advantages.

1) Support services

The most important point about InnoDB is that it supports transactions. It can be said that this is a very important reason why InnoDB has become the most popular storage engine in MySQL. In addition, InnoDB also implements four isolation levels (READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE), making support for transactions more flexible.

2) Good disaster recovery

InnoDB ensures data security through commit, rollback, and crash-recovery.

Specifically, crash-recovery means that if the server crashes due to hardware or software problems, no matter what the data is like at the time, after restarting MySQL, InnoDB will automatically recover to the state before the crash.

3) Use row-level locks

InnoDB changes the locking mechanism of MyISAM and implements row locking. Although the row lock mechanism of InnoDB is implemented through indexes, most SQL statements in the database use indexes to retrieve data. The row locking mechanism also greatly enhances InnoDB's competitiveness in environments with high concurrency pressure.

4) Implemented buffer processing

InnoDB provides a dedicated buffer pool and implements buffer management. It can not only buffer indexes but also buffer data. Frequently used data can be processed directly from memory, which is faster than obtaining data from disk. On a dedicated database server, it is common to allocate up to 80% of physical memory to the buffer pool.

5) Support foreign keys

InnoDB supports foreign key constraints, checks on foreign keys, inserts, updates, and deletes to ensure data integrity. When storing data in a table, each table is stored in the order of the primary key. If the primary key is not explicitly specified when defining the table, InnoDB generates a 6-byte ROWID for each row and uses it as the primary key.

Summarize:

This article briefly introduces the InnoDB storage engine and its advantages. If you want to learn MySQL in depth, InnoDB is definitely a key knowledge that you cannot avoid. There is a lot more content about InnoDB, and I will write more about it later if I have the chance.

The above is a brief description of the details of the MySQL InnoDB storage engine. For more information about the MySQL InnoDB storage engine, please pay attention to other related articles on 123WORDPRESS.COM!