Mysql optimization: Memory tables and temporary tables

Mysql optimization: Since memory tables and temporary tables are directly used to create intermediate tables, the speed is not satisfactory. Therefore, the idea of creating a temporary table as a memory table is introduced. But the difference between a memory table and a temporary table is not familiar with it. You need to find the information .? The temporary table is created at the beginning. When the connection is disconnected, the temporary table is deleted, that is, the temporary table exists in

Mysql optimization: Since memory tables and temporary tables are directly used to create intermediate tables, the speed is not satisfactory. Therefore, the idea of creating a temporary table as a memory table is introduced. However, the difference between a memory table and a temporary table is not familiar with it. You need to find the information. ? The temporary table is created at the beginning. When the connection is disconnected, the temporary table is deleted, that is, the temporary table exists in

Mysql optimization: Memory tables and temporary tables

Because temporary tables are used directly to create intermediate tables, the speed is not satisfactory. Therefore, the idea of creating a temporary table as a memory table exists. But the difference between a memory table and a temporary table is not familiar with it. You need to find the information .?
Initially, the temporary table exists after it is created. When the connection is disconnected, the temporary table is deleted, that is, the temporary table exists on the disk. In practice, the temporary table is found to be created and then viewed in the directory. No temporary table file is found (the link is not closed ). therefore, we guess that the data and structure of the temporary table are stored in the memory instead of in the disk .?
In this case, isn't the memory table also in the memory? What is the difference between it and the temporary table? What is their speed ??

I found some explanations in the official manual :?
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 .?

It can be seen that the memory table stores the table structure on the disk and stores the data in the memory .?
And did the following experiments :?
Temporary table?

Java code ??

1. Mysql>? Create? Temporary? Table? Tmp1 (id? Int? Not? Null );??
2. Query? OK ,? 0? Rows? Affected? (0.00? Sec )??

Java code ??

1. Mysql>? Show? Create? Table? Tmp1 ;??
2. + ------- + Response ++ ??
3. |? Table? |? Create? Table ??????????????????????????????????????? ???????????????????????????????????????? | ??
4. + ------- + Response ++ ??
5. |? Tmp1 ??? |? CREATE? TEMPORARY? TABLE? 'Tmp1 '? (? 'Id '? Int (11 )? NOT? NULL )? ENGINE = MyISAM? DEFAULT? CHARSET = utf8 ???? | ??
6. + ------- + Response ++ ??
7. 1? Row? In? Set? (0.00? Sec )??

Memory table?

Java code ??

1. Mysql>? Create? Table? Tmp2 (id? Int? Not? Null )? TYPE = HEAP ;??
2. Query? OK ,? 0? Rows? Affected? (0.00? Sec )??

Java code ??

1. Mysql>? Show? Create? Table? Tmp2 ;??
2. + ------- + Response ++ ??
3. |? Table? |? Create? Table ??????????????????????????????????????? ???????????????????????????????? | ??
4. + ------- + Response ++ ??
5. |? Tmp2 ??? |? CREATE? TABLE? 'Tmp2 '? (??
6. ??? 'Id '? Int (11 )? NOT? NULL ??
7. )? ENGINE = MEMORY? DEFAULT? CHARSET = utf8? | ??
8. + ------- + Response ++ ??
9. 1? Row? In? Set? (0.00? Sec )??

It can be seen that the temporary table is different from the MEMORY table ENGINE. The default temporary table is MyISAM, while the MEMORY table is MEMORY. You can view the temporary table in the database directory and find that tmp2.frm does not have any files in the tmp1 table. It seems that the actual situation is in line with the official explanation .?

So what about speed (the difference between MyISAM and MEMORY )??
Lab started :?
Implementation Method: Perform OLAP segmentation on two tens of millions of tables. Two different methods are used to create intermediate tables. Finally, the data in the intermediate table is taken out as required and inserted into the result table?
Objective: To test the speed of the temporary memory table and temporary table?
1. Use Create temporary table type = heap to Create a temporary memory table for creating an intermediate table?
2. Use Create temporary table to Create an intermediate table directly?

Experiment results :?
Temporary memory table: 1 hour?
1 11:03:48?
1 12:03:39?
Temporary table: 1 hour and 17 minutes?
2 12:25:28?
2 13:42:37?

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

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. putting a temporary table and a memory table together is indeed much faster: create table tmp2 (id int not null) engine memory ;?

There are some restrictions for creating a memory table :?
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 .?
Enough memory is required at the same time .?
To free memory used by a MEMORY table when ?? You no longer require its contents, you shocould execute DELETE or truncate table, or remove the table altogether using DROP ????????
TABLE. To release the memory, you should execute delete from heap_table or drop table heap_table.