• notice
  • Congratulations on the launch of the Sought Tech site

mysql optimization: in-memory tables and temporary tables

Because the speed of using the temporary table directly to create the intermediate table is not satisfactory, the idea of building the temporary table into an in-memory table is created. But the difference between the memory table and the temporary table is not familiar, and you need to find the information.
At first, I thought that the temporary table existed after it was created. When the connection is disconnected, the temporary table will be deleted, that is, the temporary table exists on the disk. In actual operation, it is found that after the temporary table is created, go to the directory to check and find that the corresponding temporary table file (unbroken link) is not found. Therefore, it is guessed that the data and structure of the temporary table are stored in memory, not in disk.
    If you think about it this way, doesn't the memory table also exist in memory, so what is the difference between it and the temporary table? What is their speed like?

    Looking up the official manual has some of the following explanations:
The MEMORY storage engine creates tables with contents that are stored in memory. Formerly, these were known as HEAP tables. MEMORY is the preferred term, although HEAP remains supported for backward compatibility.

Each MEMORY table is associated with one disk file. The filename begins with the table name and has an extension of .frm to indicate that it stores the table definition. 

From this, it can be seen that the memory table will store the table structure on disk and store the data in in memory .
And did the following experiment:
Temporary table
mysql> create temporary table tmp1(id int not null);
Query OK, 0 rows affected (0.00 sec)

mysql> show create table tmp1;
+-------+----------- -------------------------------------------------- ---------------------------------+
| Table | Create Table |
+-------+- -------------------------------------------------- -------------------------------------------+
| tmp1 | CREATE TEMPORARY TABLE `tmp1` ( `id` int(11) NOT NULL) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+-------+------------------- -------------------------------------------------- -------------------------+
1 row in set (0.00 sec)

memory table
mysql> create table tmp2(id int not null) TYPE=HEAP;
Query OK, 0 rows affected (0.00 sec)

mysql> show create table tmp2;
+-------+--------- -------------------------------------------------- -------------------------+
| Table | Create Table |
+-------+--------- -------------------------------------------------- -------------------------+
| tmp2 | CREATE TABLE `tmp2` (
   `id` int(11) NOT NULL
) ENGINE=MEMORY DEFAULT CHARSET =utf8 |
+-------+-------------------------------------- ----------------------------------------------+
1 row in set (0.00 sec)

can see that the ENGINE of the temporary table is different from that of the memory table. The default for the temporary table is MyISAM, while the memory table is MEMORY. . Go to the database directory and find tmp2.frm without any files for the tmp1 table. It seems that the actual situation is in line with the official explanation.


What about speed (i.e. the difference between MyISAM and MEMORY)?
Beginning of the experiment:
Implementation means: Do some OLAP segmentation operations based on 2 million-level tables, and use 2 different ways to create the intermediate table. Finally, the data of the intermediate table is taken out as required and inserted into the result table.
Experimental purpose; to test the speed of the temporary memory table and the temporary table
.  .Intermediate
table directly use Create temporary table to establish 

experimental results:
Temporary memory table: 1 hour
1 2008-09-25 11:03:48
1 2008-09-25 12:03:39
Temporary table: 1 hour 17 minutes
2 2008- 09-25 12:25:28
2 2008-09-25 13:42:37 It

is found that MEMORY is about 20% faster than MyISAM.


Then find the official manual:
As indicated by the name, MEMORY tables are stored in memory. They use hash indexes by default, which makes them very fast, and very useful for creating temporary tables. However, when the server shuts down, all rows stored in MEMORY tables are lost . The tables themselves continue to exist because their definitions are stored in .frm files on disk, but they are empty when the server restarts.


It can be seen that MEMORY is indeed very fast, and very useful for creating temporary tables  . It is indeed faster to use tables together : create table tmp2(id int not null) engine memory;

there are some restrictions on the establishment of memory tables:
MEMORY tables cannot contain BLOB or TEXT columns. HEAP does not support BLOB/TEXT columns.    
The server needs sufficient memory to maintain all MEMORY tables that are in use at the same time.
To free memory used by a MEMORY table when you no longer require its contents, you should execute DELETE or TRUNCATE TABLE , or remove the table altogether using DROP TABLE. To free memory, you should do DELETE FROM heap_table or DROP TABLE heap_table.

Tags

Technical otaku

Sought technology together

Related Topic

1 Comments

author

atorvastatin 20mg usa & lt;a href="https://lipiws.top/"& gt;order atorvastatin 40mg generic& lt;/a& gt; order lipitor without preion

Wmvuke

2024-03-11

Leave a Reply

+