Ii. MySQL Storage engine InnoDB

Introduction of Storage Engine

MySQL uses a separation of business logic and data storage architecture, the underlying storage engine for the upper layer of the SQL support; MySQL uses plug-in way to load the storage engine directly into the running MySQL, which is an important feature of MySQL;

The following command can query the current MySQL-supported storage engine:

Show engines;

This article environment:

1) The MySQL version is: 5.7.18

2) System: Mac OS

9 Types of storage engines:

In addition to federated 8 kinds are supported, the default is InnoDB;

Ii. Characteristics of InnoDB

1) storage limit: 64TB

2) Transaction security: Support

3) Lock mechanism: Row level lock

4) B-tree Index: Support

5) hash index: not supported

6) Full-text index: not supported

7) Cluster Index: Support

8) Data cache: Support

9) Index cache: Support

10) data can be compressed: not supported

11) Space use: High

12) Memory Usage: high

13) Bulk Insertion speed: Low

14) Support FOREIGN key: support

Three, InnoDB main characteristics of the introduction

InnoDB Storage Engine is a third-party company developed, currently the most widely used database storage engine In addition to MyISAM is InnoDB, InnoDB write processing speed with less than myisam efficiency, sacrificing the efficiency of storage and query, support transaction security, support auto-growth column, Support for transactional security is an important reason why InnoDB becomes one of MySQL's most popular storage engines.

1) Support transactions

MySQL supports innodb storage transaction Control and implements the 4 levels defined by the SQL92 standard :

1. Read uncommintted

2. Repeatable READ

3. Read Committed

4, Serializable

MySQL supports local transactions through syntax such as commit, rollback, set autocommit, start transaction, etc.

2) Auto-Grow column

automatic growth of the InnoDB table requires the Auto Increment property to be added after the column, and null values can be inserted in the process of adding table data, which automatically increases the data;

3) FOREIGN KEY constraint

InnoDB realizes the important function of foreign key database, from the database performance, the database foreign key reduces the efficiency of database query, the coupling degree between database tables is more tight , but for many users, the use of FOREIGN key constraints may be the lowest cost Choice of ways. MySQL supports foreign key engines with only InnoDB. When creating a foreign key, it is required that the parent table must have a corresponding index, and the child table will add the corresponding index when the foreign key is created.

On the physical storage, the INNODB has its own storage, and the table structure information is stored in the. frm file, but the table data and index data are stored together. There are two ways to store table data and index data in InnoDB:

1) shared table Space Storage

That is, all table data and index data are stored in the same table space , and the data and indexes are in the table spaces defined by Innodb_data_home_dir and Innodb_data_file_path. One or more data files (. ibdata files) can be used;

2) Multi-table space storage (exclusive table space )

This storage method creates a table structure that is stored in a. frm file, but each table data and index data is stored in a separate. ibd file . In the case of partitioned tables, each partition corresponds to a separate. ibd file, with the file name "table name + partition name", which allows you to specify the path of the data file for each partition when creating the partition , and the benefit is that you can distribute the table's reads evenly across several disk partition files, improving the efficiency of data access.

To use multi-tablespace storage, you need to set: innodb_file_per_table parameter. This parameter modifies the InnoDB to be a separate tablespace pattern, and each database table generates a data space.

You can use the following command to see if the multi-tablespace mode is turned on:

' %per_table% '

Before you set up innodb_file_per_table, you need to close the database and then set or add parameters in the My.cnf file: innodb_file_per_table=1, and restart the database to take effect.

Tables that use the multi-tablespace feature make it easier to perform table backups and restores, but it is not possible to copy. IDB files directly, using the following command:

ALTER TABLE table_name DISCARD tablespace; ALTER TABLE table_name IMPORT tablespace;

InnoDB in the function with MyISAM storage engine is very different, in the parameter configuration, InnoDB is also handled separately, InnoDB all the parameters are basically prefixed with "innodb_".

If you want to block the InnoDB storage engine, in the my.cnf file, remove the # from the Skip-innodb parameter before you can create a table of the InnoDB type.

Reference: "MySQL Technology insider"

Ii. MySQL Storage engine InnoDB