Section 14.1: InnoDB Introduction

14.1.1. Innodb-mysql Default Storage Engine

14.1.2. Detecting the availability of InnoDB

14.1.3. Close InnoDB

The InnoDB is a universal storage engine that combines high reliability and high performance. InnoDB is the default storage engine in MySQL5.5. In the MySQL5.7

When executing the CREATE TABLE statement, the INNODB storage engine will be used by default if the storage engine used is not specified using the engine= statement. Such as

1 experimental results can prove

Mysql> CREATE TABLE InnoDB (  ID int primary KEY,  name varchar ()    )    ; Query OK, 0 rows affected (1.02 sec) mysql> Show create TABLE  innodb;+--------+----------------------------------  -------------------------------------------------------------------------------------------------------------+| Table  | Create Table                                                     |+--------+----------------------------------------------------------------------------------- ------------------------------------------------------------+| InnoDB | CREATE TABLE ' InnoDB ' (  ' id ' int (one) not null,  ' name ' varchar () DEFAULT NULL,  PRIMARY KEY (' id ')) engine= InnoDB DEFAULT Charset=utf8 |+--------+-------------------------------------------------------------------------- ---------------------------------------------------------------------+1 Row in Set (0.01 sec)

As you can see from the experimental results above, the default for CREATE table is the InnoDB storage engine.

Advantages of InnoDB:

Tables built using the InnoDB engine have the following advantages:

1. Tables built using the InnoDB engine are DML (Delete,update,insert) operations that follow the acid model, and support transactional operations such as commit,
Rollback, also supports failback. All of this is good for protecting your users ' data.

2. The InnoDB storage engine provides row-level locks, as well as consistent reads similar to Oracle. This feature can greatly improve the concurrency of multiple users and improve
MySQL's performance.

3. InnoDB table organizes data based on primary key to optimize query efficiency (clustered index)

4. To ensure data integrity, INNODB supports foreign key constraints. Foreign KEY constraints are checked when inserting, updating, and deleting data

5. You can mix tables with the InnoDB engine and other engines and even mix them in the same SQL statement, for example, you can
A JOIN operation is done on a InnoDB table and a memory engine's table.

6. InnoDB has done a great deal of optimization for big data processing, and his performance is rarely comparable to other hard disk-based data.

Characteristics of the InnoDB engine

The InnoDB storage engine will have buffer pools in main memory to cache data and indexes. You can enable the InnoDB by setting the Innodb_file_per_table status to make each

Table and the index of the table are stored in a separate file. When disabled through Innodb_file_per_table, InnoDB will store all tables and indexes in the system

Tablespace (may contain several files or disk partitions). InnoDB can handle large quantities of data, although some operating systems have a limit of 2G per file size.

Enhancements and new features of InnoDB

The new features of InnoDB in MySQL5.7 can be referenced in the following articles (not yet written). ）