MySQL storage engine (ii)

MySQL storage engine (ii)

• • • MySQL Storage Engine II
      • MEMORY
      • MERGE
      • BerkeleyDB Storage Engine

MEMORY

The memory storage engine creates a table by using the contents of the RAM. Each memory table is actually associated with a disk file, with the file name in the format "table name. frm". The memory type of table access is extremely fast because the data source comes from memory, so when the database shuts down, the data is lost. Hash index is used by default.

Mysql> CREATE TABLE Memory_table (ID int primary key, name varchar ()) engine=memory; Query OK, 0 rows affected (0.02 sec)Mysql> INSERT INTO memory_table (Id,name) VALUES (2, ' Frank '); Query OK, 1 row Affected (0.00 sec)mysql> SELECT * from memory_table;+----+-----------+| id | name |+----+-----------+| 1 | Frankstar || 2 | Frank |+----+-----------+2 rows in Set (0.00 sec) mysql> Show table status like' memory_table '\g;*************************** 1. Row ***************************name:memory_tableengine:memoryversion:10row_format:fixedRows:2avg_row_length:66data_length:127008Max_data_length:12582900index_length:126992data_free:0Auto_increment:nullcreate_time:2016-05-09 22:23:47Update_time:nullCheck_time:nullCollation:utf8_binChecksum:nullcreate_options:Comment:1 row in Set (0.00 sec) error:no Query specifiedmysql> show index from memory_table \g;*************************** 1. Row *************************** Table:memory_Tablenon_unique:0key_name:primaryseq_in_index:1Column_name:idCollation:nullCardinality:2Sub_part:nullPacked:nullNull:Index_type:hashComment:Index_comment:1 Row in Set (0.00 sec)Error:no query specified

Memory tables are stored in memory, and if the table data is large, the server will automatically convert it to a disk table, and the thresholds are determined by the TEMP_TABLE_SIZE system variable. The capacity of each memory meter is controlled by the value of the max_heap_table_size variable. Default 16MB.
Mainly used in the data content changes infrequently the code table and the access speed requirement is high, the data quantity is not large, also need to consider the update operation data does not write back to the disk file.

MERGE

It is actually a combination of a set of MyISAM tables, combining a set of MyISAM tables with the same structure, the merge table itself has no data, and for that type of table the insert operation is done through the Insert_method definition, with the value last or first, First means that the data is added to the MyISAM table in the combined table, and the last meaning is added to the final table. So the merge table has 2 files, one is a. frm file for storing data, and an MRG file that holds the name of the merge table, including its constituent tables.

As follows:

mysql> create table myisam_table1(    -> id int primary key,    -> data datetime    -> )engine=myisam;Query OK, 0 rows affected (0.02 sec)create table myisam_table2( id int primary key, data datetime )engine=myisam;Query OK, 0 rows affected (0.01 sec)mysql> create table table1_merge_table2(    -> id int primary key,    -> data datetime    -> )engine=merge union=(myisam_table1,myisam_table2) insert_method=first;Query OK, 0 rows affected (0.01 sec)

Add data to the 2-Word table, as follows:

mysql> Insert intoMyisam_table1 VALUES (1,' 2016-5-7 '); Query OK,1Row affected (0.00SEC) mysql> Insert intoMyisam_table1 VALUES (2,' 2016-5-6 '); Query OK,1Row affected (0.00SEC) mysql> Insert intoMyisam_table2 VALUES (1,' 2016-5-7 '); Query OK,1Row affected (0.00SEC) mysql> Insert intoMyisam_table2 VALUES (2,' 2016-5-6 '); Query OK,1Row affected (0.00Sec

Query the merge table as follows:

mysql> select * from table1_merge_table2;+----+---------------------+| id | data                |+----+---------------------+|  1 | 2016-05-07 00:00:00 ||  2 | 2016-05-06 00:00:00 ||  1 | 2016-05-07 00:00:00 ||  2 | 2016-05-06 00:00:00 |+----+---------------------+4 rows in set (0.01 sec)

Add a piece of data to the merge table, as follows:

mysql> INSERT INTO Table1_merge_Table2 VALUES (3,' 2016-5-8 '); Query OK, 1 row Affected (0.00 sec)mysql> SELECT * from table1_merge_table2;+----+---------------------+| id | data |+----+---------------------+| 1 |  2016-05-07 00:00:00 | | 2 |  2016-05-06 00:00:00 | | 3 |  2016-05-08 00:00:00 | | 1 | 2016-05-07 00:00:00 || 2 | 2016-05-06 00:00:00 |+----+---------------------+5 rows in Set (0.00 sec)mysql> SELECT * from myisam_table1;+----+---------------------+| id | data |+----+---------------------+| 1 |  2016-05-07 00:00:00 | | 2 | 2016-05-06 00:00:00 || 3 | 2016-05-08 00:00:00 |+----+---------------------+3 Rows in Set (0.00 sec)mysql> SELECT * from myisam_table2;+----+---------------------+| id | data |+----+---------------------+| 1 | 2016-05-07 00:00:00 || 2 | 2016-05-06 00:00:00 |+----+---------------------+2 rows in Set (0.00 sec)

The Insert_method designation works, and if not specified, an error occurs when attempting to insert data into the merge table. It is common to use the merge table to transparently query and update multiple tables.

BerkeleyDB Storage Engine

For short BDB, when you create a table of that type, you have 2 data files, one. frm file stores table metadata, and another. db file stores data and index files, similar to InnoDB. It implements transaction security with redo logs. Every time you start, you will do a check to empty all the redo logs. Like the memory engine, it is page-level locked.

MySQL storage engine (ii)