Detailed explanation of the functions and usage of MySQL common storage engines

This article uses examples to illustrate the functions and usage of common MySQL storage engines. Share with you for your reference, the details are as follows:

There are two main categories of MySQL storage engines:

1. Transaction-safe tables : InnoDB, BDB.

2. Non-transactional safe tables : MyISAM, MEMORY, MERGE, EXAMPLE, NDB Cluster, ARCHIVE, CSV, BLACKHOLE, FEDERATED, etc.

The default storage engine for MySQL is MyISAM (the default in version 5.7 is InnoDB).

Set the default storage engine parameter in the configuration file: default-table-type.

Query the storage engines supported by the current database:

show engines;
show variables like 'have%';

View the current default storage engine:

show variables like '%table_type%';

Specify the storage engine when creating a new table:

create table(...) engine=MyISAM;

The following is a detailed introduction to four commonly used storage engines: MyISAM, InnoDB, MEMORY, and MERGE.

1. MyISAM

1. Data files:

MyISAM data tables are stored on disk as three files, all with the same file name as the table name, and the extensions are:

(1).frm: stores data table structure definition.

(2).MYD: stores table data.

(3).MYI: storage table index.

Among them, data files and index files can be placed in different directories to evenly distribute IO and achieve faster speed. Specify the path of the index file and data file. This needs to be specified using the data directory and index directory statements when creating a table. (The file path must be an absolute path and have access permissions)

MyISAM tables may be damaged for a variety of reasons. A damaged table may not be accessible and may prompt that it needs to be repaired or return an error result after access. You can use the check table statement to check the health of the MyISAM table, and use the repair table statement to repair a damaged MyISAM table.

2. Storage format:

(1) Static table (default): fields are all non-variable length (each record is of fixed length). Storage is very fast, easy to cache, and easy to recover from failures; it usually takes up more space than dynamic tables.

(2) Dynamic table: It occupies relatively less space, but frequent updates and deletions of records will cause fragmentation. It is necessary to regularly execute the optimize table or myisamchk -r command to improve performance. In addition, it is difficult to recover when a failure occurs.

(3) Compressed table: created using the myisampack tool, which takes up very little disk space. Because each record is compressed individually, there is very little access overhead.

The data in the static table will be padded with spaces according to the column width definition when it is stored, and these spaces will be removed before returning the data to the application. If there is a space after the content to be saved, it will be removed when the result is returned. (Actually, this is the behavior of the data type char. If this data type is in the dynamic table, the same problem will occur.)

(Static and dynamic tables are chosen automatically based on the type of columns being used.)

3. Advantages and Disadvantages:

(1) Advantages: fast access speed.

(2) It does not support transactions or foreign keys.

4. Applicable situations:

If the application is mainly based on read and insert operations, with only a few update and delete operations, and the requirements for transaction integrity and concurrency are not very high, then this storage engine is very suitable. MyISAM is one of the most commonly used storage engines in Web, data warehouse and other application environments.

2. InnoDB

1. Storage method:

InnoDB stores tables and indexes in the following two ways:

(1) Use shared tablespace storage: The table structure created in this way is saved in the .frm file, and the data and indexes are saved in the tablespace defined by innodb_data_home_dir and innodb_data_file_path, which can be multiple files.

(2) Use multi-tablespace storage: The table structure created in this way is still saved in the .frm file, but the data and index of each table are saved separately in the .idb file. If it is a partition table, each partition corresponds to a separate .idb file. The file name is "table name + partition name". When creating partitions, you can specify the location of the data file for each partition to evenly distribute the table's IO on multiple disks.

To use the multi-tablespace storage method, you need to set the parameter innodb_file_per_table and restart the server for it to take effect, and it only takes effect on newly created tables. There is no size limit for data files in multiple tablespaces. There is no need to set the initial size, maximum file limit, extended size, and other parameters. Even in the multi-tablespace storage mode, the shared tablespace is still necessary. InnoDB stores the internal data dictionary and work log in this file. Therefore, it is not possible to directly copy the .idb file when backing up a table that uses the multi-tablespace feature. You can use the command to restore the data backup to the database:

ALTER TABLE tbl_name DISCARD TABLESPACE;
ALTER TABLE tbl_name IMPORT TABLESPACE;

However, this can only restore the table to the original database. If you need to restore it to another database, you need to use mysqldump and mysqlimport.

2. Data files:

InnoDB data files are determined by the storage method of the table.

(1) Shared tablespace file: defined by the parameters innodb_data_home_dir and innodb_data_file_path, used to store data dictionaries and logs, etc.

(2).frm: stores table structure definition.

(3) .idb: This file is used to store table data and indexes when using multiple tablespace storage methods. If shared tablespace storage is used, this file will not exist.

3. Foreign key constraints:

InnoDB is the only MySQL engine that supports foreign key constraints. Foreign key constraints allow the database to ensure data integrity and consistency through foreign keys, but the introduction of foreign keys will reduce speed and performance. When creating a foreign key, the parent table must have a corresponding index, and the child table will automatically create a corresponding index when creating a foreign key.

Example of using foreign key constraints:

CREATE TABLE `dep` (
 `id` smallint(6) NOT NULL AUTO_INCREMENT,
 `name` varchar(20) DEFAULT NULL,
 PRIMARY KEY (`id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `emp` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(20) DEFAULT NULL,
 `dep_id` smallint(6) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `idx_fk_dep_id` (`dep_id`),
 CONSTRAINT `fk_emp_dep` FOREIGN KEY (`dep_id`) REFERENCES `dep` (`id`) ON UPDATE CASCADE
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

KEY : Defines the index constraint name.

CONSTRAINT : Defines the foreign key constraint name. (Should be unique in the database. If not specified, the system will automatically generate a constraint name)

ON : Specifies the effect of parent table operations on child tables (restrict is used by default if not defined).

Restrict and no action : The parent table cannot be updated or deleted if the child table has related records.

Cascade : When the parent table is updated or deleted, the corresponding records in the child table are also updated or deleted.

Set null : When the parent table is updated or deleted, the corresponding field of the child table is set to null.

When a table is referenced by a foreign key in another table, the corresponding index or primary key of this table cannot be deleted. When importing data from multiple tables, if you need to ignore the order in which the tables are imported, you can temporarily disable the foreign key check. When performing load data and alter table operations, you can also speed up the processing by temporarily disabling foreign key constraints.

Shutdown command:

set foreign_key_checks=0;

Open command:

set foreign_key_checks=1;

4. Advantages and Disadvantages:

(1) Advantages: Provides transaction security with commit, rollback, and crash recovery capabilities.

(2) Disadvantages: Compared with MyISAM, InnoDB has lower write processing efficiency and takes up more disk space to retain data and indexes.

5. Applicable situations:

If the application has relatively high requirements for transaction integrity and requires data consistency under concurrent conditions, and data operations include many update and delete operations in addition to insert and query, then the InnoDB storage engine should be a more appropriate choice. In addition to effectively reducing locks caused by deletions and updates, the InnoDB storage engine can also ensure complete transaction submission and rollback. For systems such as billing systems or financial systems that have high requirements for data accuracy, InnoDB is a suitable choice.

3. MEMORY

1. Data files:

Each MEMORY table corresponds to only one .frm disk file, which is used to store the table structure definition, and the table data is stored in memory. By default, HASH index is used instead of BTREE index.

2. Advantages and Disadvantages:

(1) Advantages: Access speed is very fast because the data is stored in memory.

(2) Disadvantages: Once the service is shut down, the data in the table will be lost; there is a limit on the size of the table.

3. Applicable situations:

The Memory storage engine is mainly used in code tables whose contents do not change frequently, or as intermediate result tables for statistical operations, so as to efficiently analyze the intermediate results and obtain the final statistical results.

MERGE

1. Engine principle:

The Merge storage engine is a combination of a group of MyISAM tables. These MyISAM tables must have exactly the same structure. The merge table itself does not have data. The merge type table can be queried, updated, and deleted. These operations are actually performed on the actual internal MyISAM table.

Define the insert operation of the merge table through the insert_method clause: using first or last will make the insert operation act on the first or last table respectively. Not defining or defining it as No means that the insert operation cannot be performed on this merge table. The drop operation on the merge table only deletes the definition of the merge and has no effect on the internal tables.

2. Data files:

(1).frm: stores table definitions.

(2) MRG: stores information about the combined table, including which tables the merge table is made up of and the basis for inserting new data. You can modify the merge table by modifying the .mrg file, but you must flush the table after the modification.

3. Usage examples:

CREATE TABLE `m1` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(20) DEFAULT NULL,
 PRIMARY KEY (`id`)
)ENGINE=MyISAM DEFAULT CHARSET=utf8;
CREATE TABLE `m2` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(20) DEFAULT NULL,
 PRIMARY KEY (`id`)
)ENGINE=MyISAM DEFAULT CHARSET=utf8;
CREATE TABLE `m` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(20) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=MRG_MyISAM DEFAULT CHARSET=utf8 INSERT_METHOD=LAST UNION=(`m1`,`m2`);

4. Applicable situations:

Used to logically group a series of identical MyISAM tables together and reference them as a single object. The advantage of the MERGE table is that it can break through the size limit of a single MyISAM table, and by distributing different tables on multiple disks, the access efficiency of the MERGE table can be effectively improved. This is very suitable for VLDB environments such as data warehousing.

Readers who are interested in more MySQL-related content can check out the following topics on this site: "MySQL stored procedure skills", "MySQL common function summary", "MySQL log operation skills", "MySQL transaction operation skills summary" and "MySQL database lock related skills summary"

I hope this article will be helpful to everyone's MySQL database design.