MySQL table type storage engine selection

MySQL table type storage engine selection

1. View the storage engine of the current database expenditure

Method 1:

mysql> show engines \G;
*************************** 1. row ***************************
      Engine: InnoDB
     Support: YES
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
*************************** 2. row ***************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 3. row ***************************
      Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 4. row ***************************
      Engine: BLACKHOLE
     Support: YES
     Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 5. row ***************************
      Engine: MyISAM
     Support: DEFAULT
     Comment: MyISAM storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 6. row ***************************
      Engine: CSV
     Support: YES
     Comment: CSV storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 7. row ***************************
      Engine: ARCHIVE
     Support: YES
     Comment: Archive storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 8. row ***************************
      Engine: PERFORMANCE_SCHEMA
     Support: YES
     Comment: Performance Schema
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 9. row ***************************
      Engine: FEDERATED
     Support: NO
     Comment: Federated MySQL storage engine
Transactions: NULL
          XA: NULL
  Savepoints: NULL
9 rows in set (0.00 sec)

ERROR:
No query specified

Method 2:

(Records with a Value of " DISABLED " indicate that the storage engine is supported but is disabled when the database is started.)

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

2. ENGINE={storage engine type} Set the storage engine when creating a table.

mysql> create table a(
    -> i bigint(20) not null auto_increment,
    -> primary key (i)
    ->) engine=myisam default charset=gbk;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 3
Current database: test

Query OK, 0 rows affected (1.33 sec)

3. alter able tablename engine={storage engine type} to modify the table to another storage engine

mysql> alter table a engine=innodb;
Query OK, 0 rows affected (1.70 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> show create table a \G;
*************************** 1. row ***************************
       Table: a
Create Table: CREATE TABLE `a` (
  `i` bigint(20) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`i`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk
1 row in set (0.14 sec)

3.1 Comparison of Common Storage Engines

Features MyISAM InnoDB MEMORY MERGE NDB
Storage Limits have 64TB have No have
Transaction Security support
Lock mechanism Table Lock Row Lock Table Lock Table Lock Table Lock
B-tree index support support support support support
Hash Index support support
Full-text index support
Cluster Index support
Data Cache support support support
Index Cache support support support support support
Data can be compressed support
Space usage Low high N/A Low Low
Memory usage Low high medium Low high
Batch insert speed high Low high high high
Support foreign keys support

3.2 Learning Common Storage Engines (MyISAM, InnoDB, MEMORY, and MERGE)

MyISAM:

The default MySQL storage engine does not support transactions and foreign keys.

Advantages: Fast access speed

Each MyISAM is stored on disk as three files, whose file names and table names are the same. The extensions are:

.frm (stores table definitions)

.MYD (MYData, storage data)

.MYI (MYIndex, storage index)

(Data files and index files can be placed in different directories to evenly distribute IO and achieve faster speed.)

InnoDB:

The processing efficiency is poor and more space is used to store data and indexes.

Advantages: transaction security with commit, rollback, and crash recovery capabilities, the only storage engine that supports foreign keys

Auto-growth column: The auto-growth column of the InnoDB table can be manually inserted, but if the inserted value is empty or 0, the actual inserted value will be the value after the auto-growth.

mysql> create table autoincre_demo(
    -> i smallint not null auto_increment,
    -> name varchar(10),primary key(i)
    ->)engine=innodb;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 5
Current database: test

Query OK, 0 rows affected (1.19 sec)

mysql> insert into autoincre_demo values(1,"121"),(0,"dddf"),(null,"fdf");
Query OK, 3 rows affected (0.59 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from autoincre_demo;
+---+------+
| i | name |
+---+------+
| 1 | 121 |
| 2 | dddf |
| 3 | fdf |
+---+------+
3 rows in set (0.00 sec)

alter table tabename auto_increment=n Set the initial value of the auto-increment column (this value starts at 1 by default)

You can use LAST_INSERT_ID() to query the value used by the current thread to insert the last record. If multiple records are inserted at once, the auto-increment value used by the first record is returned.

The following example demonstrates the use of LAST_INSERT_ID():

mysql> insert into autoincre_demo(name) values('3');
Query OK, 1 row affected (0.36 sec)

mysql> select LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
| 15 |
+------------------+
1 row in set (0.00 sec)

mysql> insert into autoincre_demo(name) values('3'),('6'),('323'),('21');
Query OK, 4 rows affected (0.09 sec)
Records: 4 Duplicates: 0 Warnings: 0

mysql> select LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
| 16 |
+------------------+
1 row in set (0.00 sec)

Foreign key constraints:

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.

Below are two tables in the sample database. country table is the parent table, country_id as the primary key index. city table is the child table, with the country_id field having a foreign key to country_id field in country table.

mysql> create table country(
    -> country_id smallint unsigned not null auto_increment,
    -> country varchar(50) not 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 (0.86 sec)
mysql> CREATE TABLE city (
    -> city_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    -> city VARCHAR(50) NOT NULL,
    -> country_id SMALLINT UNSIGNED NOT NULL,
    -> last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    -> PRIMARY KEY (city_id),
    -> KEY idx_fk_country_id (country_id),
    -> CONSTRAINT `fk_city_country` FOREIGN KEY (country_id) REFERENCES country (country_id) ON
    -> DELETE RESTRICT ON UPDATE CASCADE
    -> )ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (3.22 sec)

When creating an index, you can specify the corresponding operations to be performed on the child table when the parent table is deleted or updated, including RESTRICT , CASCADE, SET NULL, and NO ACTION

  • RESTRICT is the same as NO ACTION , which means that the parent table cannot be updated if there are related records in the child table.
  • CASCADE means that when the parent table is updated or deleted, the corresponding records in the child table are updated or deleted;
  • SET NULL means that when the parent table is updated or deleted, the corresponding fields of the child table are SET NULL .
mysql> select * from country;
+------------+---------+---------------------+
| country_id | country | last_update |
+------------+---------+---------------------+
| 1 | AAA | 2021-06-16 15:09:22 |
+------------+---------+---------------------+
1 row in set (0.00 sec)

mysql> select * from city;
+---------+------+------------+---------------------+
| city_id | city | country_id | last_update |
+---------+------+------------+---------------------+
| 10 | bb | 1 | 2021-06-16 15:11:45 |
+---------+------+------------+---------------------+
1 row in set (0.00 sec)

mysql> delete from country where country_id = 1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`city`, CONSTRAINT `fk_city_country` FOREIGN KEY (`country_id`) REFERENCES `country` (`country_id`) ON UPDATE CASCADE)

mysql> update country set country_id = 10000 where country_id = 1;
Query OK, 1 row affected (0.62 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from country;
+------------+---------+---------------------+
| country_id | country | last_update |
+------------+---------+---------------------+
| 10000 | AAA | 2021-06-16 15:13:35 |
+------------+---------+---------------------+
1 row in set (0.00 sec)

mysql> select * from city
    -> ;
+---------+------+------------+---------------------+
| city_id | city | country_id | last_update |
+---------+------+------------+---------------------+
| 10 | bb | 10000 | 2021-06-16 15:11:45 |
+---------+------+------------+---------------------+
1 row in set (0.00 sec)

When importing data from multiple tables, if you need to ignore the order in which the tables were imported, you can temporarily turn off the foreign key check. Similarly, when performing LOAD DATA and ALTER TABLE operations, you can speed up the processing by temporarily turning off the foreign key constraints. The command to turn them off is " SET FOREIGN_KEY_CHECKS = 0 ;". After the execution is complete, change them back to the original state by executing the " SETFOREIGN_KEY_CHECKS = 1 ;" statement.

View table foreign key information: show create table or show table status command

mysql> show table status like 'city' \G;
*************************** 1. row ***************************
           Name: city
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 1
 Avg_row_length: 16384
    Data_length: 16384
Max_data_length: 0
   Index_length: 16384
      Data_free: 0
 Auto_increment: 11
    Create_time: 2021-06-16 15:02:17
    Update_time: 2021-06-16 15:13:35
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.43 sec)

ERROR:
No query specified

Storage method:

  • (1) Use shared tablespace storage: The table structure is saved in the .frm file, and the data + index is stored 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 is also saved in the .frm file, and the data + index exist separately in ibd . If it is a partitioned table, each partition corresponds to a separate .ibd file. The file name is: "table name + partition name". When creating a partition, you can specify the location of the data file for each partition, so as to evenly distribute the table's IO on multiple disks.

MEMORY:

Create a table using the contents stored in memory

Each MEMORY table actually corresponds to only one disk file, and the format is frm .

Advantages: fast access speed (data is stored in memory), and HASH index is used by default. Data will be lost if the service is shut down.

mysql> CREATE TABLE tab_memory ENGINE=MEMORY
    -> SELECT city_id,city,country_id
    -> FROM city GROUP BY city_id;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 12
Current database: test

Query OK, 1 row affected (0.62 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> select * from tab_memory;
+---------+------+------------+
| city_id | city | country_id |
+---------+------+------------+
| 10 | bb | 10000 |
+---------+------+------------+
1 row in set (0.00 sec)

mysql> show table status like 'tab_memory' \G
*************************** 1. row ***************************
           Name: tab_memory
         Engine: MEMORY
        Version: 10
     Row_format: Fixed
           Rows: 1
 Avg_row_length: 155
    Data_length: 520320
Max_data_length: 65011650
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2021-06-16 15:28:58
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_unicode_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

When creating an index for a table, you can specify the index type as HASH or BTREE

mysql> create index mem_hash using hash on tab_memory(city_id);
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 13
Current database: test

Query OK, 1 row affected (0.63 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> show index from tab_memory \G;
*************************** 1. row ***************************
        Table: tab_memory
   Non_unique: 1
     Key_name: mem_hash
 Seq_in_index: 1
  Column_name: city_id
    Collation: NULL
  Cardinality: 1
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: HASH
      Comment:
Index_comment:
1 row in set (0.32 sec)

ERROR:
No query specified

mysql> drop index mem_hash on tab_memory;
Query OK, 1 row affected (0.31 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> create index mem_hash using btree on tab_memory(city_id);
Query OK, 1 row affected (0.16 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> show index from tab_memory \G;
*************************** 1. row ***************************
        Table: tab_memory
   Non_unique: 1
     Key_name: mem_hash
 Seq_in_index: 1
  Column_name: city_id
    Collation: A
  Cardinality: NULL
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
1 row in set (0.00 sec)

ERROR:
No query specified

MERGE:

This storage service is a combination of a group of MyISAM tables

MERGE type tables can be queried, updated, and deleted. These operations are actually performed on the actual internal MyISAM table.

For insert operations on MERGE type tables, the table to be inserted is defined through the INSERT_METHOD clause, which can have three different values. Using the FIRST or LAST value causes the insert operation to be performed on the first or last table respectively. Not defining this clause or defining it as NO means that insert operations cannot be performed on this MERGE table.

You can perform a DROP operation on a MERGE table. This operation only deletes the definition of MERGE and has no effect on the internal tables.

Storage files: One .frm file stores the table definition, and the other .MRG file contains the information of the combined table, including which tables the MERGE table is composed of and the basis for inserting new data

mysql> create table payment_2020(
    -> country_id smallint,
    -> payment_date datetime,
    -> amount DECIMAL(15,2),
    -> KEY idx_fk_country_id (country_id)
    ->)engine=myisam;
Query OK, 0 rows affected (0.25 sec)

mysql> create table payment_2021(
    -> country_id smallint,
    -> payment_date datetime,
    -> amount DECIMAL(15,2),
    -> KEY idx_fk_country_id (country_id)
    ->)engine=myisam;
Query OK, 0 rows affected (0.54 sec)

mysql> CREATE TABLE payment_all(
    -> country_id smallint,
    -> payment_date datetime,
    -> amount DECIMAL(15,2),
    -> INDEX(country_id)
    -> )engine=merge union=(payment_2020,payment_2021) INSERT_METHOD=LAST;
Query OK, 0 rows affected (0.47 sec)

Insert data into 2020 and 2021 respectively, and query

mysql> insert into payment_2020 values(1,'2020-06-01',100000),(2,'2020-06-15',150000);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> insert into payment_2021 values(1,'2021-04-20',35000),(2,'2021-06-15',220000);
Query OK, 2 rows affected (0.03 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from payment_2020;
+------------+---------------------+-----------+
| country_id | payment_date | amount |
+------------+---------------------+-----------+
| 1 | 2020-06-01 00:00:00 | 100000.00 |
| 2 | 2020-06-15 00:00:00 | 150000.00 |
+------------+---------------------+-----------+
2 rows in set (0.00 sec)

mysql> select * from payment_2021;
+------------+---------------------+-----------+
| country_id | payment_date | amount |
+------------+---------------------+-----------+
| 1 | 2021-04-20 00:00:00 | 35000.00 |
| 2 | 2021-06-15 00:00:00 | 220000.00 |
+------------+---------------------+-----------+
2 rows in set (0.00 sec)

mysql> select * from payment_all;
+------------+---------------------+-----------+
| country_id | payment_date | amount |
+------------+---------------------+-----------+
| 1 | 2020-06-01 00:00:00 | 100000.00 |
| 2 | 2020-06-15 00:00:00 | 150000.00 |
| 1 | 2021-04-20 00:00:00 | 35000.00 |
| 2 | 2021-06-15 00:00:00 | 220000.00 |
+------------+---------------------+-----------+
4 rows in set (0.00 sec)

It can be found that the data in payment_all table is the result set of the records of the payment_2020 and payment_2021 tables merged.

Next, insert a record into the MERGE table. Since the definition of the MERGE table is INSERT_METHOD=LAST , the record will be inserted into the last table. Therefore, although the record inserted here is from 2006, it will still be written to payment_2021 table.

mysql> insert into payment_all values(3,'2020-03-30',12333131);
Query OK, 1 row affected (0.31 sec)

mysql> select * from payment_all;
+------------+---------------------+-------------+
| country_id | payment_date | amount |
+------------+---------------------+-------------+
| 1 | 2020-06-01 00:00:00 | 100000.00 |
| 2 | 2020-06-15 00:00:00 | 150000.00 |
| 1 | 2021-04-20 00:00:00 | 35000.00 |
| 2 | 2021-06-15 00:00:00 | 220000.00 |
| 3 | 2020-03-30 00:00:00 | 12333131.00 |
+------------+---------------------+-------------+
5 rows in set (0.00 sec)

mysql> select * from payment_2021;
+------------+---------------------+-------------+
| country_id | payment_date | amount |
+------------+---------------------+-------------+
| 1 | 2021-04-20 00:00:00 | 35000.00 |
| 2 | 2021-06-15 00:00:00 | 220000.00 |
| 3 | 2020-03-30 00:00:00 | 12333131.00 |
+------------+---------------------+-------------+
3 rows in set (0.00 sec)

mysql> select * from payment_2020;
+------------+---------------------+-----------+
| country_id | payment_date | amount |
+------------+---------------------+-----------+
| 1 | 2020-06-01 00:00:00 | 100000.00 |
| 2 | 2020-06-15 00:00:00 | 150000.00 |
+------------+---------------------+-----------+
2 rows in set (0.00 sec)

This is the end of this article about the selection of MySQL table type storage engine. For more relevant MySQL table type storage engine content, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!

You may also be interested in:
  • MySQL data analysis storage engine example explanation
  • Comparison of storage engines supported by MySQL database
  • Differences and comparisons of storage engines in MySQL
  • Advantages and disadvantages of common MySQL storage engines
  • Let's learn about the MySQL storage engine

<<:  The process of building lamp architecture through docker container

>>:  Detailed explanation of CSS3 text shadow text-shadow property

Recommend

Causes and solutions for MySQL master-slave synchronization delay

For historical reasons, MySQL replication is base...

Complete tutorial on installing Apache, MySQL, PHP, LAMP on Ubuntu 18.04

1. Install Apache $ sudo apt update && su...

Pitfalls based on MySQL default sorting rules

The default varchar type in MySQL is case insensi...

Detailed explanation of how to use Nginx + consul + upsync to achieve dynamic load balancing

Table of contents Prerequisites DNS domain name r...

Detailed explanation of the correct way to configure SSL (https certificate) in Apache on Ubuntu

First, take a look at Alibaba Cloud's officia...

JavaScript object-oriented class inheritance case explanation

1. Object-oriented class inheritance In the above...

Linux uses iftop to monitor network card traffic in real time

Linux uses iftop to monitor the traffic of the ne...

JS cross-domain XML--with AS URLLoader

Recently, I received a requirement for function ex...

A method of making carousel images with CSS3

Slideshows are often seen on web pages. They have...

Use of MySQL trigger

Table of contents 1. Trigger Introduction 1. What...

Install ethereum/Ethereum from scratch under CentOS7

Table of contents Preface Add sudo write permissi...

Tutorial on Installing Nginx-RTMP Streaming Server on Ubuntu 14

1. RTMP RTMP streaming protocol is a real-time au...

Detailed tutorial on building a private Git server on Linux

1. Server setup The remote repository is actually...

Detailed explanation of the use of find_in_set() function in MySQL

First, let’s take an example: There is a type fie...

Vue data responsiveness summary

Before talking about data responsiveness, we need...