MySQL Storage engine overview

MySQL support plug-in storage engine, by default includes a variety of storage engines, you can also customize the engine, the engine is set at the table level.

Second, the characteristics of various storage engines

(A) MyISAM: Transactions are not supported, foreign keys are not supported, and access speed is fast.

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

(A1) frm (storage table definition)

(A2) MYD (MYData, storing data)

(A3) MYI (myindex, storage index)

Data files and index files can be placed in different directories, evenly distributed IO, for faster speeds. You can specify the table's DATA DIRECTORY when you create the table

and the INDEX DIRECTORY statement to specify the path separately.

The MyISAM table may be corrupted, can be detected and repaired by tools, and table corruption can cause database exceptions to restart and need to be repaired as soon as possible. Three storage formats are supported, namely:

static (fixed-length) table; fields are non-variable-length fields: Fast storage, easy to cache, easy to recover, and occupy more space. Note: Trailing spaces are removed from the field

Dynamic tables, which contain variable-length fields, may be fragmented, and need to be regularly optimized for tables. Take up more space and fail to recover easily.

Compressed tables, created by the Myisampack tool, take up very little space, compress each record individually, and have very small access expenses.

(B) InnoDB: Support transactions, support foreign keys, compared to the MyISAM, the write speed is slightly less, occupy a little more space.

Note: Using the last_insert_id () query is only the value that is used when the thread last inserted the record, not the value used in the last inserted record in the database.

Foreign key associations: four types of requests:

RESTRICT: The parent table cannot be updated or deleted in case the child table is associated with a record;

No ACTION: The parent table cannot be updated or deleted in the case of a child table associated with a record;

CASCADE: Indicates that the parent table updates and deletes the records of the child tables as appropriate when updating or deleting them.

Set NULL: Indicates 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.

You can temporarily turn off detection of foreign keys: SET foreign_key_checks=0.

There are two ways to store tables and indexes InnoDB:

Using shared tablespace storage, the table structure in the. frm file, the data and index files can be multiple files in the tablespace defined by Innodb_data_home_dir and Innodb_data_file_path.

Using multi-tablespace storage, table structure in the. frm file, each table's data and indexes are in. IBD alone. If this is a partitioned table, each partition corresponds to a separate. ibd file, the file name is table name + partition name, and you can specify the location of the data file for each partition.

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

(C) Memory: Create a table using the contents of the RAM. Fast access, default is the hash index, the data is not persisted.

Each memory table corresponds to only one disk file, in the format. frm.

The size of the data in each memory table is constrained by the max_heap_table_size system variable, and the initial size is 16MB.

Memory tables are usually used for conversion codes with infrequent content changes, and the intermediate results table for statistical operations. Note the non-persisted attribute of the table data.

(D) Merge: It is actually a combined representation of a MyISAM table with exactly the same number of structures.

　　　　

MySQL Storage engine overview