Temporary tables and memory tables in MySQL

Source: Internet
Author: User

Temporary tables and memory tables in MySQL

MySQL memory table:

1. Use max_heap_table_size in My. cnf to specify dynamically

2. The table is defined as stored on the disk. The extension is. frm. the restart will not be lost, and the data will be stored in the memory. Therefore, data will be lost after the restart.

3. BLOB or TEXT columns are not supported.

4. Memory table initialization. You can use -- init-file to initialize the memory table. This prevents data from being cleared after mysql is restarted. For example, -- init-file = "/data/mysql/init. SQL", the init. SQL format is:

Use db_test;

Select *** into m_table;

5. If the host crashes during database replication, the binLog will be automatically added to the delete from [memory table] at the next temporary table access to the master database. delete the slave data to ensure data consistency between the two sides.

6. Memory tables do not support transactions.

7. The memory table is a table lock. When the modification is frequent, the performance may decrease.

8. There are the following solutions for data loss caused by restart:

1. Execute a simple query before any query to determine whether the heap table has data. If no data exists, re-write the data or DROP the table to copy a new table. This requires more than one query. However, you can write an include file, which can be called at any time on the page that requires the heap table.

2. For the page on which the heap table is required, the results of the dataset are judged for the first time and only when the table is queried for the first time. If the result is blank, the data needs to be written again. This saves one query.

3. A better way is to automatically write data to heap every time mysql restarts. However, the server needs to be configured. The process is complicated and the versatility is limited.

MySQL temporary table:

1. The maximum memory required for a temporary table must be set through tmp_table_size. The actual maximum memory is determined by the minimum values of max_heap_table_size and tmp_table_size. tmp_table_size is allocated to each thread.

2. If the temporary memory table exceeds the limit, MySQL will automatically convert it to a disk-based MyISAM table and store it in the specified tmpdir directory.

3. Created_tmp_disk_tables and Created_tmp_tables show the number of temporary tables and disk creation. Generally, Created_tmp_disk_tables/Created_tmp_tables <5%

4. create temporary table tmp1 (id int not null); you can create a temporary table.

This article permanently updates the link address:

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.