MySQL Storage Engine

Objective

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

1. MySQL Storage Engine Overview

• Plug-in storage engine is one of the most important features of MySQL database, the user can choose how to store and index data, use transaction, etc. according to the needs of the application. MySQL supports a variety of storage engines by default to suit the needs of different domains of database applications, users can use different storage engines to improve the efficiency of the application, provide flexible storage, users can even customize and use their own storage engine to achieve the maximum degree of customization.

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

• If you do not specify a storage engine when you create a new table, the default storage engine is used, and the default storage engine before MySQL 5.5 is myisam,5.5 and changed to InnoDB.

1.1 Common storage Engine comparisons

• Common storage Engine Comparison

  features	MyISAM	InnoDB	MEMORY	MERGE	NDB
  Storage limits	Yes	TB	Yes	No	Yes
  Things safe		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
  Speed of BULK Insert	High	Low	High	High	High
  Support for foreign keys		Support

1.2 MySQL Storage Engine settings

• 1) Querying the storage engine supported by the current database

  > Show engines; +--------------------+---------+----------------------------------------------------------------+-------------- +------+------------+    | Engine | Support | Comment | Transactions | XA |    savepoints | +--------------------+---------+----------------------------------------------------------------+-------------- +------+------------+    | ARCHIVE | YES | Archive Storage Engine | NO | NO |    NO | | Blackhole | YES | /dev/null Storage Engine (anything you write to it disappears) | NO | NO |    NO | | Mrg_myisam | YES | Collection of identical MyISAM tables | NO | NO |    NO | | Federated | NO | Federated MySQL Storage Engine | NULL | NULL |    NULL | | MyISAM | YES | MyISAM Storage Engine | NO | NO |    NO | | Performance_schema | YES | Performance Schema | NO | NO |    NO | | InnoDB | DEFAULT | Supports transactions, Row-level locking, and foreign keys | YES | YES |    YES | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO |    NO | | CSV | YES | CSV Storage Engine | NO | NO |    NO | +--------------------+---------+----------------------------------------------------------------+-------------- +------+------------+ 9 rows in Set (0.00 sec)

  > show variables like 'have%';    +------------------------+----------+    | Variable_name          | Value    |    +------------------------+----------+    | have_compress          | YES      |    | have_dynamic_loading   | YES      |    | have_geometry          | YES      |    | have_openssl           | YES      |    | have_profiling         | YES      |    | have_query_cache       | NO       |    | have_rtree_keys        | YES      |    | have_ssl               | YES      |    | have_statement_timeout | YES      |    | have_symlink           | DISABLED |    +------------------------+----------+    10 rows in set (0.00 sec)

• 2) View current default storage engine

  > show variables like 'table_type';    +---------------+--------+    | Variable_name | Value  |    +---------------+--------+    | table_type    | InnoDB |    +---------------+--------+    1 row in set (0.00 sec)

• 3) set up the storage engine for the new table

  # create table 表名 (      列名称字段 数据类型(长度) 约束条件,      列名称字段 数据类型(长度) 约束条件  ) engine = 存储引擎名;> create table ai (   i bigint(20) not null auto_increment,   primary key(i)  ) engine = MyISAM;    Query OK, 0 rows affected (0.08 sec)

• 4) Modify the storage engine for existing tables

  # 修改已有表的存储引擎# alter table 表名 engine = 存储引擎名;> alter table ai engine = InnoDB;    Query OK, 0 rows affected (0.09 sec)    Records: 0  Duplicates: 0  Warnings: 0

• 5) Display the table's storage engine

  # 显示表的存储引擎# show create table 表名;> show create table ai;    +-------+-----------------------------------------------------------------------------------------+    | Table | Create Table                                                                            |    +-------+-----------------------------------------------------------------------------------------+    | ai    | CREATE TABLE `ai` (                                  `i` bigint(20) NOT NULL AUTO_INCREMENT,                                  PRIMARY KEY (`i`)                                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci    |    +-------+-----------------------------------------------------------------------------------------+    1 row in set (0.01 sec)

2. MyISAM Storage Engine

• MyISAM is the default storage engine for MySQL. MyISAM does not support transactions or foreign keys, its advantage is that access is fast, there is no requirement for transactional integrity, or a SELECT, INSERT-based application can basically use this engine to create tables.

• Selecting this storage engine is ideal if the application is based on read and insert operations with few updates and deletions, and the integrity and concurrency requirements for transactions are not high. MyISAM is one of the most commonly used storage engines in the WEB, data warehousing, and other application environments.

2.1 Storage Mode

• 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.

• To specify the path to the index file and data file, you need to specify it by using the Data directory and index directory statements when creating the table, which means that the index files and data files for different MyISAM tables can be placed under different paths. The file path needs to be an absolute path and has access rights.

• Tables of type MyISAM may be corrupted for a variety of reasons, the damaged table may not be accessible, and will prompt for repair or return the result of an error after access.

• A table of type MyISAM provides a repair tool that can check the health of the MyISAM table with a check table statement and fix a corrupted MyISAM table with the REPAIR table statement.

• Table corruption can cause database exceptions to restart, need to be repaired as soon as possible and identify the cause of the corruption.

2.2 Storage formats

• The MyISAM table also supports 3 different storage formats, namely:

  • static (fixed-length) table
  • Dynamic tables
  • Compression table

• Where the static table is the default storage format. The fields in the static table are non-variable length fields, so each record is fixed-length, the advantage of this storage method is that the storage is very fast, easy to cache, the fault is easy to recover, the disadvantage is that the space occupied is usually more than the dynamic table. The static table's data is stored with the column's width defined to fill in the blanks, but these spaces are not available when the app is accessed, and the spaces are removed before they are returned to the app.

  • However, there are some issues that require special attention, and if the content that needs to be saved is preceded by a space, it will be removed when the result is returned, and developers need to pay special attention when writing the program because the static table is the default storage format, and the developer may not be aware of it, thus losing the trailing space.

• Dynamic tables contain variable-length fields, records are not fixed lengths, so the advantage of storage is that they occupy relatively little space, but frequent updates and deletions of records are fragmented and require periodic execution of OPTIMIZE TABLE statements or MYISAMCHK-R commands to improve performance. and recovery is relatively difficult in the event of a failure.

• Compressed tables are created by the Myisampack tool and occupy very small disk space. Because each record is individually compressed, there is very little access expense.

3. InnoDB Storage Engine

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

• Used for transaction processing applications, support for foreign keys. The InnoDB storage engine should be a more appropriate choice if the application has a high level of transactional integrity requirements, requires consistency of data under concurrency conditions, and includes many updates and deletions in addition to insertions and queries. In addition to effectively reducing locks caused by deletions and updates, the InnoDB storage engine also ensures complete commit and rollback (Rollback) of transactions, and InnoDB is the right choice for systems such as billing systems or financial systems that require high data accuracy.

3.1 Auto-Grow column

• 1) The Auto-grow column of the InnoDB table can be inserted manually, but if the inserted value is empty or 0, the actual insertion will be the auto-grow value.

  > create table autoincre_demo (    i smallint not null auto_increment,     name varchar(10), primary key(i)  ) engine = innodb;    Query OK, 0 rows affected (0.04 sec)> insert into autoincre_demo values (1, '1'), (0, '2'), (null, '3');    Query OK, 3 rows affected (0.09 sec)    Records: 3  Duplicates: 0  Warnings: 0> select * from autoincre_demo;    +---+------+    | i | name |    +---+------+    | 1 | 1    |    | 2 | 2    |    | 3 | 3    |    +---+------+    3 rows in set (0.00 sec)

• 2) You can alter table *** auto_increment = n; force the initial value of the autogrow column to be set through the statement.

  • The default starts at 1.
  • The default value for this coercion is kept in memory, and if the value is restarted before it is used, the mandatory default value is lost and needs to be reset after the database is started.

• 3) You can use last_insert_id() the value used to query the last inserted record of the current thread. If more than one record is inserted at a time, the auto-grow value used by the first record is returned.

  > insert into autoincre_demo values(4, '4');    Query OK, 1 row affected (0.05 sec)> select last_insert_id();    +------------------+    | last_insert_id() |    +------------------+    |                2 |    +------------------+    1 row in set (0.01 sec)> insert into autoincre_demo (name) values ('5'), ('6'), ('7');    Query OK, 3 rows affected (0.01 sec)    Records: 3  Duplicates: 0  Warnings: 0> select last_insert_id();    +------------------+    | last_insert_id() |    +------------------+    |                5 |    +------------------+    1 row in set (0.00 sec)

• 4) for InnoDB tables, the auto-grow 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 preceding columns of the combined index.

  > create table autoincre_demo (    d1 smallint not null auto_increment,     d2 smallint not null,     name varchar(10),     index(d2, d1)  ) engine = myisam;     Query OK, 0 rows affected (0.07 sec)> insert into     autoincre_demo     (d2, name)   values     (2, '2'), (3, '3'), (4, '4'), (2, '2'), (3, '3'), (4, '4');    Query OK, 6 rows affected (0.01 sec)    Records: 6  Duplicates: 0  Warnings: 0> select * from autoincre_demo;    +----+----+------+    | d1 | d2 | name |    +----+----+------+    |  1 |  2 | 2    |    |  1 |  3 | 3    |    |  1 |  4 | 4    |    |  2 |  2 | 2    |    |  2 |  3 | 3    |    |  2 |  4 | 4    |    +----+----+------+    6 rows in set (0.00 sec)

  • Create a new table of type MyISAM autoincre_demo , autogrow column D1 as the second column of the combined index, after inserting some records into the table, you can find that the autogrow column is incremented after the first column of the combined index D2 sorted by.

3.2 FOREIGN KEY constraints

• 1) 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.

    > CREATE TABLE Country (country_id smallint unsigned NOT NULL auto_increment, country varchar () No T null, last_update timestamp NOT NULL default current_timestamp on update Current_timestamp, primary key (country    _id) engine = InnoDB default CharSet = UTF8; Query OK, 0 rows affected, 1 warning (0.05 sec) > CREATE TABLE City (city_id smallint unsigned NOT null Auto_increme NT, City varchar (NO) NULL, country_id smallint unsigned not NULL, last_update timestamp NOT NULL default C Urrent_timestamp on update current_timestamp, primary key (city_id), key idx_fk_country_id (country_id), # Constr Aint constraint name foreign key (foreign key name) References source table (primary key Name) constraint Fk_city_country foreign key (country_id) references Country (c    OUNTRY_ID) on the delete restrict on UPDATE cascade) engine = InnoDB default CharSet = UTF8; Query OK, 0 rows affected, 1 warning (0.07 sec)  

  • The Country table is the parent table, COUNTRY_ID is the primary key index, the city table is a child table, and the country_id field is the foreign key, corresponding to the country table's primary key country_id.

• 2) When you create an index, you can specify the corresponding actions on the child table when the parent table is deleted, updated, including restrict, Cascade, set NULL, and no action. Be cautious when selecting the latter two ways, which may result in loss of data due to incorrect operation.

  • Restrict and no action are the same, which means that the parent table cannot be updated if the child table is associated with a record;
  • Cascade indicates that the parent table updates or deletes the corresponding record of the child table when it is updated or deleted;
  • Set NULL indicates that the corresponding field of the child table is set NULLL when the parent table is updated or deleted.

  > select * FROM country where country_id = 1; +------------+-------------+---------------------+    | country_id | Country |    last_update |          +------------+-------------+---------------------+    | 1 | Afghanistan |    2006-02-15 04:44:00 |     +------------+-------------+---------------------+ 1 row in Set (0.00 sec) > select * from city where country_id = 1; +---------+-------+------------+---------------------+    | city_id | City | country_id |    last_update |     +---------+-------+------------+---------------------+    | 251 |          Kabul | 1 |    2006-02-15 04:45:25 | +---------+-------+------------+---------------------+ 1 row in Set (0.00 sec) > Delete from country where country_id    = 1; ERROR 1451 (23000): Cannot delete or update a parent ROW:A FOREIGN KEY constraint fails (' sakila/city ', constraint ' F K_city_country ' FOREIGN KEY (' country_id ') REFERENCES ' country ' (' country_id ') on update CASCADE) > update Country S ET country_id = 10000 where country_id = 1; Query OK, 1 row affected (0.04 sec) Rows matched:1 changed:1 warnings:0> select * from country where country = ' A    Fghanistan '; +------------+-------------+---------------------+    | country_id | Country |    last_update |      +------------+-------------+---------------------+    | 10000 | Afghanistan |    2007-07-17 09:45:23 |    +------------+-------------+---------------------+ 1 row in Set (0.00 sec) > select * from city where city_id = 251; +---------+-------+------------+---------------------+    | city_id | City | country_id |    last_update |     +---------+-------+------------+---------------------+    | 251 |      Kabul | 10000 |    2006-02-15 04:45:25 | +---------+-------+------------+---------------------+ 1 row in Set (0.00 sec)

  • 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.

• 3) 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 the command set foreign_key_checks = 0; to close after execution is complete , and change back to the original state by executing the set foreign_key_checks = 1; statement.

• 4) for InnoDB types of tables, the foreign key information can be displayed by using the Show create table or the Show Table Status command.

  # Show CREATE table name;> show create TABLE city; +-------+------------------------------------------------------------------------------------------------------ --------------------------------+    | Table |                                                                                                                         Create Table    | +-------+------------------------------------------------------------------------------------------------------ --------------------------------+    | City |                                    CREATE TABLE ' city ' (' city_id ' smallint (5) unsigned not NULL auto_increment, ' City ' varchar (+) not NULL, ' country_id ' smallint (5) Unsigne D NOT null, ' last_update ' timestamp not null DEFAULT current_timestamp on update curren T_timestamp, PRIMARY key (' city_id '), key ' Idx_fk_co Untry_id ' (' country_id '), CONSTRAINT ' Fk_city_country ' FOREIGN KEY (' country_id ') referenc                                                                             ES ' Country ' (' country_id ') on UPDATE CASCADE) Engine=innodb DEFAULT Charset=utf8    | +-------+------------------------------------------------------------------------------------------------------ --------------------------------+ 1 row in Set (0.00 sec) # Show table status like table name;> Show table status like ' City '    ; +------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+ -----------+----------------+    | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free |    auto_increment | +------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+ -----------+----------------+    | City | InchNodb | 10 |    Dynamic |              0 |       0 |               16384 |        0 |         16384 |              0 |    1 | +------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+ -----------+----------------+     ---------------------+-------------+------------+-----------------+----------+---- ------------+---------+ create_time | Update_time | Check_time | Collation | Checksum | create_options |     Comment |  ---------------------+-------------+------------+-----------------+----------+----------------+---------+ 2018-06-21 18:58:55 | NULL | NULL |     Utf8_general_ci |                NULL |         |     | ---------------------+-------------+------------+-----------------+----------+----------------+---------+ 1 row In Set (0.06 sec)

3.3 Storage Mode

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

  • Using shared tablespace storage, the table structure of tables created in this way is saved in the. frm file, and the data and indexes are saved in innodb_data_home_dir and innodb_data_file_path defined in the tablespace, which can be multiple files.
  • With multi-tablespace storage, the table structure of the tables created in this way is still 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 location of the data file for each partition when creating the partition, which distributes the table's IO evenly across multiple disks.

• To use multiple table spaces for storage, you need to set parameters and innodb_file_per_table Restart the service to take effect, and for new tables to be created in a multi-table space, existing tables still use shared tablespace storage. If you modify an existing multi-table space way back to the shared tablespace, the new table is created in the shared tablespace, but the table with the existing multi-table space still retains the original access method. Therefore, after the parameters of the multi-table space are in effect, only the newly created tables take effect.

• 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-tablespace attribute, it is easier 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 the shared tablespace, and the directly copied. ibd files and. frm files are not correctly recognized when they are restored. However, you can restore the backup to the database with the following command, but such a single-table backup can only be restored to the database where the table was originally located, and not to the other database. If you want to restore a single table to the target database, you need to implement it through mysqldump and Mysqlimport.

  alter table tbl_name discard tablespace;alter table tbl_name import tablespace;

• Even with multi-table space storage, shared tablespace is still necessary, InnoDB the internal data dictionary and the online redo log in this file.

MySQL Storage Engine