1. Storage engine Overview

Unlike most databases, MySQL has a storage engine concept. You can choose the optimal storage engine for different storage requirements.

A storage engine is a way to store data, build indexes, update query data, and so on. Storage engines are table based, not library based. So storage engines can also be called table types.

Databases such as Oracle and SqlServer have only one storage engine. MySQL provides a plug-in storage engine architecture. Therefore, MySQL has various storage engines. You can use them or write storage engines as required.

Storage engines supported by MySQL5.0 include: InnoDB, MyISAM, BDB, MEMORY, MERGE, EXAMPLE, NDB Cluster, ARCHIVE, CSV, BLACKHOLE, FEDERATED, etc. InnoDB and BDB provide transaction security table, Other storage engines are non-transactional security tables.

To query the storage engines supported by the current database, specify Show Engines:

If you do not specify a storage engine when creating a new table, the system will use the default storage engine. The default storage engine was MyISAM before MySQL5.5 and changed to InnoDB after 5.5.

Mysql database default storage engine

show variables like '%storage_engine%' ；
Copy the code

2. Storage engine features

The following describes several commonly used storage engines and compares their differences.

Next we will focus on the two most commonly used storage engines: InnoDB, MyISAM, and the other two MEMORY engines, MERGE.

2.1 the InnoDB

InnoDB storage engine is the default storage engine for Mysql. InnoDB storage engine provides transaction security with commit, rollback, crash recovery capabilities. But Compared to MyISAM’s storage engine, InnoDB writes less efficiently and takes up more disk space to retain data and indexes.

InnoDB storage engine differs from other storage engines by:

1. Transaction control

create table goods_innodb
(
    id   int         NOT NULL AUTO_INCREMENT,
    name varchar(20) NOT NULL.primary key (id)
) ENGINE = innodb
  DEFAULT CHARSET = utf8; 
Copy the code

start transaction; 
insert into goods_innodb(id,name)values(null.'Meta20'); 
commit; 
Copy the code

Test, found in InnoDB is the existence of transactions;

2. Foreign key constraints

InnoDB is the only storage engine in MySQL that supports foreign keys. When creating foreign keys, the parent table must have corresponding indexes, and the child table will automatically create corresponding indexes when creating foreign keys.

In the following two tables, COUNTRY_innodb is the parent table, country_id is the primary key index, city_innoDB is the child table, and country_ID field is the foreign key corresponding to country_ID, the primary key of country_INNODB.

create table country_innodb
(
    country_id   int          NOT NULL AUTO_INCREMENT,
    country_name varchar(100) NOT NULL.primary key (country_id)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8;

create table city_innodb
(
    city_id    int         NOT NULL AUTO_INCREMENT,
    city_name  varchar(50) NOT NULL,
    country_id int         NOT NULL.primary key (city_id),
    key idx_fk_country_id (country_id),
    CONSTRAINT `fk_city_country` FOREIGN KEY (country_id) REFERENCES
        country_innodb (country_id) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8;

insert into country_innodb
values (null.'China'),
       (null.'America'),
       (null.'Japan');

insert into city_innodb
values (null.'Xian'.1),
       (null.'NewYork'.2),
       (null.'BeiJing'.1); 
Copy the code

When creating indexes, you can specify RESTRICT, CASCADE, SET NULL, and NO ACTION that can be performed on child tables when the parent table is deleted or updated.

RESTRICT is the same as NO ACTION to RESTRICT that the parent table cannot be updated if the child table has associated records.

CASCADE: When the parent table is updated or deleted, the records corresponding to the child table are updated or deleted.

SET NULL: When the parent table is updated or deleted, the corresponding fields of the child table are SET NULL.

For the two tables created above, the foreign keys of the subtables are set to ON DELETE RESTRICT ON UPDATE CASCADE. Therefore, when the primary table deletes records, the subtables cannot be deleted if they have corresponding records. When the primary table updates records, the subtables cannot be deleted if they have corresponding records. The sub-table is updated.

The data in the table is as follows:

You can view foreign key information in either of the following ways:

show create table city_innodb ; 
Copy the code

Delete country_id 1 from country_id where country_id = 1:

delete from country_innodb where country_id = 1;
Copy the code

Alter table country select country_id from country_id where country_id = country_id;

update country_innodb set country_id = 100 where country_id = 1;
Copy the code

After the update, the data information of the sub-table is:

3. Storage mode

InnoDB stores tables and indexes in two ways:

The table structure of a table created in this way is stored in a. FRM file. Data and indexes are stored in the innodb_datA_home_DIR and innodb_datA_fiFILe_PATH tablespaces, which can be multiple files.

(2) Use multi-tablespace storage. The table structure of the table created in this way still exists in the.frm file, but the data and index of each table are stored separately in the.ibD.

2.2 MyISAM

MyISAM does not support transactions, nor does it support foreign keys. MyISAM has the advantage of fast access, no requirement for transaction integrity, or SELECT, INSERT based applications can basically use this engine to create tables. There are two more important features:

1. Transactions are not supported

create table goods_myisam
(
    id   int         NOT NULL AUTO_INCREMENT,
    name varchar(20) NOT NULL.primary key (id)
) ENGINE = myisam
  DEFAULT CHARSET = utf8;
Copy the code

Through testing, we found that in MyISAM storage engine, there is no transaction control;

2. File storage mode

Each MyISAM is stored on disk as 3 files with the same file name as the table name, but with the extension name:

.frm (store table definitions); .myd (MYData, store data); .myi (MYIndex, store index);

2.3 the MEMORY

The Memory storage engine stores the table’s data in Memory. Each MEMORY table actually corresponds to a disk file in the format of. FRM. This file stores only the structure of the table, while its data files are stored in MEMORY, which facilitates fast data processing and improves the efficiency of the entire table. Memory-type tables are very fast to access because their data is stored in MEMORY and HASH indexes are used by default, but when the service is shut down, the data in the table is lost.

2.4 the MERGE

MERGE a storage engine is a group of MyISAM tables. The MERGE tables must have the same structure. The MERGE tables do not store data themselves.

For MERGE table INSERT_METHOD, the INSERT_METHOD clause defines the table to be inserted. The INSERT_METHOD clause can have three different values. FIRST or LAST values are used so that the insert is applied to the FIRST or LAST table. Indicates that an insert cannot be performed on the MERGE table.

You can DROP a MERGE table, but this operation simply drops the MERGE table definition and has no impact on the internal tables.

Here is an example of creating and using a MERGE table:

1). To create 3 test table order_1990 order_1991, order_all, including order_all is two tables before the MERGE table:

create table order_1990
(
    order_id      int,
    order_money   double(10.2),
    order_address varchar(50),
    primary key (order_id)
) engine = myisam
  default charset = utf8;

create table order_1991
(
    order_id      int,
    order_money   double(10.2),
    order_address varchar(50),
    primary key (order_id)
) engine = myisam
  default charset = utf8;

create table order_all
(
    order_id      int,
    order_money   double(10.2),
    order_address varchar(50),
    primary key (order_id)
) engine = merge
  union = (order_1990,order_1991)
  INSERT_METHOD = LAST
  default
      charset = utf8;                       
                       
                       
Copy the code

2). Insert records into two tables

insert into order_1990 values(1.100.0.'Beijing');
insert into order_1990 values(2.100.0.'Shanghai');

insert into order_1991 values(10.200.0.'Beijing');
insert into order_1991 values(11.200.0.'Shanghai');
Copy the code

SQL > select * from table where id = 1;

Data from order_1990:

Data from order_1991:

Data in order_all:

Insert a record into order_all. INSERT_METHOD is set to LAST when the MERGE table is defined.

insert into order_all values(100.10000.0.'xi 'an');Copy the code

3. Select a storage engine

When selecting a storage engine, select an appropriate one based on the characteristics of the application system. For complex application systems, you can combine multiple storage engines based on actual conditions. The following are some common storage engine environments.

• InnoDB: is the default storage engine for MysqlThe transactionHandles applications with support for foreign keys. If the application has high requirements on the integrity of the transaction, theData consistency is required under concurrent conditionsIn addition to insert and query, data operation also includes many update and delete operations, so InnoDB storage engine is a suitable choice. InnoDB storage engine not only reduces locking due to deletion and update, but also ensures complete commit and rollback of transactions. InnoDB is the best choice for systems that require high data accuracy, such as billing or accounting systems.
• MyISAM: If the application is mainly read and insert operations, with few updates and deletes, and the transaction integrity and concurrency requirements are not very high, then this storage engine is very suitable.
• MEMORY: Saves all data in RAM, inYou need to quickly locate records and other similar data environmentsCan provide access to several blocks. MEMORY has limitations on the size of tables that are too large to be cached in MEMORY, and the need to ensure that table data can be recovered after a database abort. MEMORY tables are often used to update small tables that are less frequent to get access results quickly.
• MERGE: Used to combine a series of equivalent MyISAM tables in a logical manner and reference them as an object. MERGE tables have the advantage of overcoming the size limitation of a single MyISAM table and improving the access efficiency of MERGE tables by distributing different tables on multiple disks. This is ideal for VLDB environments such as data warehousing.