Section 14.1: InnoDB introduction, 14.1innodb Introduction

Section 14.1: InnoDB introduction, 14.1innodb Introduction

 

14.1.1. InnoDB-default MySQL storage engine

14.1.2. Check InnoDB availability

14.1.3. Disable InnoDB

 

InnoDB is a general storage engine that combines high reliability and high performance. In MySQL5.5, InnoDB is the default storage engine. In MySQL5.7

If the engine = statement is not used to specify the storage engine used when executing the Create Table statement, the InnoDB Storage engine is used by default. For example

1. The experiment results show that

mysql> create table innodb(    ->  id int primary key,    ->  name varchar(20)    -> )    -> ;Query OK, 0 rows affected (1.02 sec)mysql> show create table  innodb;+--------+-----------------------------------------------------------------------------------------------------------------------------------------------+| Table  | Create Table                                                     |+--------+-----------------------------------------------------------------------------------------------------------------------------------------------+| innodb | CREATE TABLE `innodb` (  `id` int(11) NOT NULL,  `name` varchar(20) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 |+--------+-----------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.01 sec)

 

From the above experiment results, we can see that the innodb Storage engine is used by default in the create table operation.

 

Advantages of InnoDB:

Tables created using the InnoDB engine have the following advantages:

1. The tables created using the InnoDB engine are DML (delete, update, insert) operations following the ACID model. Transaction operations, such as commit,
Rollback also supports fault recovery. All of this can effectively protect user data.

2. the InnoDB Storage engine provides row-level locks and consistent reads similar to oracle. This feature greatly improves multi-user concurrency and
MySQL performance.

3. InnoDB tables organize data based on primary keys to Optimize Query Efficiency (clustered index)

4. InnoDB supports foreign key constraints to ensure data integrity. When you insert, update, or delete data, the system checks the foreign key constraints.

5. You can freely mix tables created by the InnoDB engine and other engines, or even mix them in the same SQL statement. For example, you can
Perform join operations on an Innodb table and a Memory engine table.

6. InnoDB has greatly optimized the processing of large data volumes. Its performance is rarely comparable to that of other hard disk-based data.

 

Features of the InnoDB Engine

The InnoDB Storage engine has a buffer pool in the primary storage to cache data and indexes. You can set innodb_file_per_table to enable each

A table and its indexes are stored in a separate file. When innodb_file_per_table is disabled, InnoDB stores all the tables and indexes in the system.

Tablespace (may contain several files or disk partitions ). InnoDB can process large volumes of data, although some operating systems have limits on the size of a single file to 2 GB.

 

Enhancements and new features of InnoDB

For new features of InnoDB in MySQL5.7, refer to the following article (not yet written ..)