MySQL Storage Engine

　　There is a concept of storage engine in MySQL and the optimal storage engine can be selected for different storage requirements.

I. Overview

MySQL 5.0 supports storage engines including MyISAM, InnoDB, BDB, Memory, MERGE, EXAMPLE, NDB Cluster, ARCHIVE, CSV, blackhole, federated, etc. where InnoDB and BDB provide transaction security tables, other storage engines are non-transactional security tables.

The default storage before MySQL5.5 is myisam,5.5 is InnoDB. If you want to modify the default storage engine, you can set default-table-type in the parameters file. When you create a table, you can set up a new table's storage engine by adding the engine keyword, for example: Table Auth_type 's storage engine is MyISAM, and the storage engine for table auth_type is InnoDB .

create table ' Auth_type ' (
  ' id ' int (11) Not NULL,
  ' Type_code ' char (3) default NULL COMMENT ' authorization type number ',
  ' type_name ' varchar (255) default NULL COMMENT ' Authorization type name ',
  PRIMARY KEY (' id ')
) engine=myisam DEFAULT CHARSET =UTF8MB4;

CREATE TABLE 'auth_user' (
' id ' int (one) is not NULL,
' user_name ' varchar (+) DEFAULT NULL COMMENT ' authorized user name ',
' token ' char (+), DEFAULT NULL COMMENT ' Access authorization token ',
' Created_at ' timestamp null DEFAULT null COMMENT ' record creation time ',
' Updated_at ' timestamp null DEFAULT null COMMENT ' record Last modified time ',
' Deleted_at ' timestamp null DEFAULT null COMMENT ' record delete time ',
PRIMARY KEY (' id ')
) engine=innodb DEFAULT charset=utf8mb4 comment= ' authorized user table ';

You can also use the Alert table statement to change an existing table to another storage engine. Alert table Auth_type engine = InnoDB

Second, various storage engine features

Let's look at the differences between the different storage engines in the table

Characteristics	MyISAM	InnoDB	Memory	Merge	NDB
Storage limits	Yes	64TB	Yes	No	Yes
Transaction security		Support
Lock mechanism	Table lock	Row lock	Table lock	Table lock	Row lock
B-Tree Index	Support	Support	Support	Support	Support
Hash index			Support		Support
Full-Text Indexing	Support
Cluster index		Support
Data caching		Support	Support		Support
Index cache	Support	Support	Support	Support	Support
Data can be compressed	Support
Space use	Low	High	N/A	Low	Low
Memory usage	Low	High	Medium	Low	High
BULK INSERT Speed	High	Low	High	High	High
Support for foreign keys		Support

The 4 storage engines that are most commonly used are described below: MyISAM, InnoDB, memory, and merge.

1, MyISAM

MyISAM does not support transactions, does not support foreign keys, its advantage is that access is fast, the integrity of the transaction is not required, or Select\insert-based applications can basically use this engine to create tables.

Each myisam is stored as 3 files on disk with the same file name as the table name, but with the following extensions:

. frm (save table definition)

. MYD (MYData, storing data)

. MYI (myindex, storage index)

Data files and index files can be placed in different directories, evenly distributed IO, for faster speeds.

When creating a table, specify the path to the data file and the index file by using the database directory with the index directory statement, which requires an absolute path . and has access rights .

MyISAM types of tables may be corrupted, for a variety of reasons. The damaged table may not be accessible, prompting you to repair or return incorrect results. MyISAM types of tables provide a repair tool,

The check table statement is used to check the health of the tables, and theREPAIR table statement is used to repair a corrupted table. Damage to the table can also cause the database to start abnormally, and need to be repaired as soon as possible to confirm the cause of the corruption.

The MyISAM table also supports 3 different storage formats: static (fixed-length) tables, dynamic tables, and compressed tables.

Static tables: The fields in the table are non-variable length fields, so that each field is a fixed length, such as char (20). The advantages of this kind of storage are: storage is very fast, easy to cache, failure is easy to recover, disadvantage is: Occupy more space than dynamic table more often. When a static table's data is stored, the fill space is defined by the given column width, but these spaces are not available when the app is accessed.

Dynamic tables: The table contains variable-length fields, such as varchar (20), and records are not fixed lengths. Even if only one field is a variable-length field, it is also a dynamic table. The advantage of this storage is that it takes up a relatively small amount of space, but frequently updating and deleting records is fragmented and requires regular execution of OPTIMIZE TABLE or myisamchk-r commands to improve performance. and recovery is relatively difficult in the event of a failure.

Compressed tables: Tables are created by the Myisampack tool and occupy very small disk space. Because the records are compressed by the individual, there is only very little expense.

2, InnoDB

The InnoDB storage Engine provides transactional security with commit, rollback, and crash resiliency. But compared to the MyISAM storage engine, InnoDB writes are nearly as efficient and consumes more disk space to preserve data and indexes.

Features of the InnoDB storage engine:

2.1. Auto-Growth column

Auto-growth of the InnoDB table can be inserted manually, but if the inserted value is empty or 0, the actual insert will be the auto-grow value. For example: In the following table AUTH_USER, the column ID is the autogrow column, specified by auto_increment .

CREATE TABLE ' Auth_User ' (
  ' id ' int (one) not Null auto_increment ,
  ' user_name ' varchar (+) default NULL COMMENT ' authorized user name ',
  ' token ' char (+) ' default null COMMENT ' Access Authorize token ',
  ' created_at ' timestamp null DEFAULT null COMMENT ' record creation time ',
  ' updated_at ' timestamp NU 　　　　LL DEFAULT null COMMENT ' record Last modified time ',
  ' deleted_at ' timestamp null DEFAULT null COMMENT ' record delete time ',
  PRIMARY KEY (' id ')
) engine=innodb DEFAULT charset=utf8mb4 comment= ' authorized user table '; The
is also available through the alter TABLE auth_user auto_increment = n; " Statement forces the initial value of autogrow to start at 1, but the default value of the coercion is kept in memory, and if the value is restarted before using the database, the mandatory default value is lost and needs to be reset after the database is started. The
can use last_insert_id ()

　　For InnoDB tables, the autogrow column must be an index. If it is a composite index, it must also be the first column of the combined index, but for the MyISAM table, the autogrow column can be the other column of the combined index, so that when the record is inserted, the autogrow column is incremented by the rank of the top of the combined index. Such as:

CREATE TABLE ' Autoincre_demo ' (
' Id1 ' int (one) not NULL auto_increment,

' Id2 ' int (one) is not NULL,
' Name ' varchar (+) DEFAULT NULL COMMENT ' names '
Index (' id1 ', ' Id2 ')
) Engine=myisam;

INSERT INTO ' Autoincre_demo ' (' Id2 ', ' name ') VALUES (2, ' 2 '), (3, ' 3 '), (4, ' 4 ');

The result is: SELECT * from ' Autoincre_demo ';

　　

Id1	Id2	Name
1	2	2
1	3	3
1	4	4
2	2	2
2	3	3
2	4	4

2.2, FOREIGN KEY constraints

MySQL supports foreign key storage engine only InnoDB, when creating foreign keys, requires the parent table must have a corresponding index, the child table when creating foreign keys will also automatically create the corresponding index.

When you create an index, you can specify the appropriate actions for the child table when the schedule is deleted, updated, including restrict, Cascade, set NULL, and no action.

Restrict and no action are the same, meaning that the parent table cannot be updated when the child table is associated with a record.

Cascade refers to a parent table that updates or deletes a child table corresponding to a record when it is updated or deleted.

Set NULL means that the corresponding field of the child table is set NULL when the parent table is updated or deleted.

When a table has a foreign key reference created by another table, the corresponding index or primary key of the table is prohibited from being deleted.

When importing data from multiple tables, you can temporarily turn off the foreign key check if you need to ignore the import order before the table, and similarly, when you perform the load data and ALTER TABLE operations, you can speed up processing by temporarily closing the foreign KEY constraint, which is "SET foreing_ key_checks = 0; ", after execution completes, by executing" SET foreing_key_checks = 1; " The statement returns to its original state.

2.3. Storage mode

There are two ways to store tables and indexes in the InnoDB.

With shared tablespace storage, the table structure of the tables created in this way is saved in the. frm file, and the data and indexes are saved in the table space defined by Innod_data_home_dir and Innod_data_file_path, which can be multiple files.

Using multi-tablespace storage, the table structure of tables created in this way is saved in the. frm file, but the data and indexes for each table are saved separately in. ibd. If it is a partitioned table, each partition corresponds to a separate. ibd file, and the file name is "table name + partition name", which allows you to specify the data file location of each partition when creating the partition, which is used to distribute the IO of the table to multiple disks.

To use multi-table space storage, you need to set the parameter innodb_file_per_table and restart the service to take effect, and for the new table to be created in multiple tablespaces, existing tables still use shared tablespace storage. If you modify an existing multi-tablespace method back to the shared tablespace, the new table is created in the shared tablespace, but the existing multi-table space table still preserves the original storage mode.

There is no size limit for a data file in a multi-tablespace, and you do not need to set the initial size or set parameters such as the maximum file limit, the extension size, and so on.

For tables that use the multi-table space attribute, it is convenient to perform single-table backup and restore operations, but it is not possible to copy. ibd files directly, because there is no data dictionary information for shared tables, and the. ibd files and. frm files that are copied directly are not recognized when they are restored, but can be done by using the following command:

ALTER TABLE tbl_name DISCARD tablespace;

ALTER TABLE tbl_name IMPORT tablespace;

Restore the backup to the database, but this can only be restored to the database where the table originally resides and cannot be restored to another database. You can restore a single table to the target database through mysqldump and Mysqlimport.

Note: Even with multi-table space storage, shared tablespace is still necessary, innodb the internal data dictionary and online redo logs in this file.

3. Memory

The memory storage engine creates tables using the content that exists in the RAM. Each memory meter only actually corresponds to a disk file, in the format of. frm. Memory Type table access is very fast because its data is in memory, and the hash index is used by default, but once the service is closed, the data in the table is lost.

When starting the MySQL service, use the --init--file option to INSERT INTO ... A statement such as SELECT or load data INFILE is placed in this file to load the table from a persistent data source when the service starts.

The server needs enough memory to maintain all memory tables that are used at the same time, and when the contents of the storage table are no longer needed, to release the RAM used by the memories, the DELETE from or TRUNCATE table should be executed , or the entire delete table (using the drop table operation).

The amount of data that can be placed in each memory table is constrained by the max_heap_table_size system variable, and the initial value of this system variable is 16MB, which can be increased as needed. In addition, when defining the memory table, you can specify the maximum number of rows in the table by using the Max_Rows clause.

Memory-type storage engines are mainly used in code tables where statements are not frequently changed, or as intermediate results tables for statistical operations, allowing for efficient analysis of intermediate results and final statistical results. It is prudent to update the storage engine's memory tables, since the data is not actually written to disk, so be sure to consider how to obtain the modified data after the next restart of the service.

4. Merge

The merge storage engine is a combination of a set of MyISAM tables that must be structurally identical, that the merge table itself has no data, that tables of the merge type can be queried, updated, and deleted, which are actually performed on the internal MyISAM table. For the insert operation of the Merge type table, the inserted table is defined by the INSERT_METHOD clause, which can have 3 different values, using first or last value so that the insert operation will be used to act on the second or final table, without defining the clause or defining no, Indicates that the merger table cannot be manipulated.

The merge table can be dropped, which simply removes the merge definition and has no effect on the internal table.

Two files are kept on disk, the file name begins with the name of the table, a. frm file stores the table definition, and the other. mrg file contains information about the combined table that contains the tables that comprise the merge table and the basis for inserting new data. The merge table can be modified by modifying the. mrg file, but will be refreshed by flush tables after modification.

The difference between a merger table and a partitioned table is that the merger table does not intelligently write records to the corresponding table, but instead writes to tables specified using first or last values . But the partition table can. Typically, we use the merger table to transparently query and update multiple tables, and this time-logged operation log table transparently inserts them.

Three, how to choose the right storage engine Iv. Summary

MySQL Storage Engine