Detailed explanation of the usage of MySQL memory tables and temporary tables

Usage of MySQL memory tables and temporary tables

Memory table:

session 1
$ mysql -uroot
root@(none) 10:05:06>use test
Database changed
root@test 10:06:06>CREATE TABLE tmp_memory (i INT) ENGINE = MEMORY;
Query OK, 0 rows affected (0.00 sec)
root@test 10:08:46>insert into tmp_memory values ​​(1);
Query OK, 1 row affected (0.00 sec)
root@test 10:08:46>
session2
$ mysql -uroot test
root@test 10:05:12>CREATE TABLE tmp_memory (i INT) ENGINE = MEMORY;
ERROR 1050 (42S01): Table 'tmp_memory' already exists
root@test 10:16:27>select * from tmp_memory;
+------+
| i |
+------+
| 1 |
+------+
1 row in set (0.00 sec)

1. Multiple sessions cannot create tables with the same name

2. After a session is created, it is also visible to other sessions

3. There is only tmp_memory.frm in the data directory. The table structure is stored on the disk and the data is stored in the memory.

4. After MySQL is restarted or shut down, the data in the memory table will be lost, but the table structure still exists

5. You can create indexes, delete indexes, and support unique indexes

6. Does not affect the master and slave databases. Data inserted in the master database can also be found in the slave database.

7. show tables

Temporary table:

session1
$ mysql -uroot test
root@test 10:30:18>CREATE TEMPORARY TABLE tmp_table (name VARCHAR(10) NOT NULL,value INTEGER NOT NULL);
Query OK, 0 rows affected (0.05 sec)
root@test 10:31:54>select * from tmp_table;
+--------+-------+
| name | value |
+--------+-------+
| aaaaaa | 10 |
+--------+-------+
1 row in set (0.00 sec)
session2
root@test 10:20:13> CREATE TEMPORARY TABLE tmp_table (name VARCHAR(10) NOT NULL,value INTEGER NOT NULL);
Query OK, 0 rows affected (0.02 sec)
root@test 10:30:39>insert into tmp_table values ​​('bbbbbbb',10);
Query OK, 1 row affected (0.01 sec)
root@test 10:31:33>select * from tmp_table;
+---------+-------+
| name | value |
+---------+-------+
| bbbbbbb | 10 |
+---------+-------+
1 row in set (0.00 sec)
root@test 10:31:43>exit
Bye
[1 Single:MS-Master db152011.sqa.cm6:mysql ~ ]
$ mysql -uroot test
root@test 10:32:17>select * from tmp_table;
ERROR 1146 (42S02): Table 'test.tmp_table' doesn't exist
root@test 10:32:22>
root@test 10:32:23>

1. The names of the created tables can be the same

2. Table structure and data are stored in memory

3. When the session disappears, the table structure and data disappear

4. You can create and delete indexes

5. The table created by the main database cannot be found in the standby database.

6. show tables cannot see the table

Things to note when using memory tables

1. The memory table needs to delete data or drop the table by itself; the drop permission is required, which is more dangerous

2. The table structure of the memory table is saved on the disk. If multiple sessions use the same table name, there will be conflicts. If the table name does not need to be used, if the table structure needs to be created every time it is used, there will be many small files, which is not conducive to DB maintenance and there are risks when the DBA cleans up the table.

Based on the above, it is not suitable to use memory table

1. Temporary tables are session-level. Even if multiple sessions create tables with the same name, they will not affect each other.

2. The session disappears, and everything disappears, which is not conducive to application troubleshooting

In addition, both of these require additional memory space, which is tolerable on the db side, but not very controllable; the DB side also has this parameter:

max_tmp_tables The maximum number of temporary tables that a client can keep open at the same time. The default value is 32, and you can adjust this value as needed.

mysql creates a temporary table and inserts the query results into an existing table

I encountered a very tricky problem today. I wanted to temporarily store some data and then read it later. I remember the teacher in the database theory class said that temporary tables can be created. I wonder if MySQL has such a function? Temporary tables are in memory and should be read faster than views. Then you also need to store the query results in a temporary table.

The following is an example of creating a temporary table and inserting data for your reference.

A. After the temporary table is disconnected from MySQL, the system will automatically delete the data in the temporary table, but this is limited to the table created with the following statement:

1) Define fields

CREATE TEMPORARY TABLE tmp_table (
 name VARCHAR(10) NOT NULL, 
 time date NOT NULL
 )

A more advanced point is:

create temporary TABLE `temptable` (
 `jws` varchar(100) character set utf8 collate utf8_bin NOT NULL,
 `tzlb` varchar(100) character set utf8 collate utf8_bin NOT NULL,
 `uptime` date NOT NULL
)ENGINE=InnoDB DEFAULT CHARSET=latin1″

Even the encoding method is specified. . Haha, to prevent garbled characters.

2) Import the query results directly into a temporary table

CREATE TEMPORARY TABLE tmp_table SELECT * FROM table_name

B. In addition, MySQL also allows you to create temporary tables directly in memory. Since it is in memory, the speed will be very fast. The syntax is as follows:

 CREATE TEMPORARY TABLE tmp_table (
 name VARCHAR(10) NOT NULL,
 value INTEGER NOT NULL
 ) TYPE = HEAP

So how do you store the query results into an existing table?

1. You can use the second method in A.

2. Use insert into temtable (select a,b,c,d from tablea)”;

MySQL database memory temporary table parameters

1. Maximum size of internal memory temporary table

This variable does not apply to user-created MEMORY tables. Temporary tables are created in some more complex queries. The actual limit is determined by the smaller of tmp_table_size and max_heap_table_size.
tmp-table-size parameterDefault 16777216

1.1. Memory temporary tables and hard disk temporary tables If the temporary table in memory exceeds the limit, MySQL will automatically convert it into a temporary table on disk.
As of MySQL 5.7.5, the internal_tmp_disk_storage_engine option defines the storage engine to use for disk temporary tables.
Prior to MySQL 5.7.5, the MyISAM storage engine was used.
The number of temporary memory tables created: Created_tmp_tables
The number of temporary hard disk tables created: Created_tmp_disk_tables
Added: Created_tmp_files

1.2. Setting reference When setting tmp-table-size, please refer to the values ​​of the hard disk temporary table and the memory temporary table.
The general ratio is: Created_tmp_disk_tables/Created_tmp_tables<5%
If you perform many advanced GROUP BY queries and you have plenty of memory, increase the value of tmp_table_size (and max_heap_table_size if necessary).

2. Maximum size of the MEMORY table

This variable sets the maximum size to which user-created MEMORY tables are allowed to grow. His variable value is used to calculate the MAX_ROWS value of the MEMORY table.
max_heap_table_size parameter default 16777216

2.1. Usage Setting this variable has no effect on any existing MEMORY table unless the table is recreated using a statement such as CREATE TABLE or changed using ALTER TABLE or TRUNCATE TABLE.
A server restart also sets the maximum size of existing MEMORY tables to the global max_heap_table_size value.
This variable is also used together with tmp_table_size to limit the size of the internal memory table.

The above is my personal experience. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM. If there are any mistakes or incomplete considerations, please feel free to correct me.