MySQLheap usage Summary

Source: Internet
Author: User
The following articles mainly describe the usage of MySQLheap. We all know that memory tables use Hash hash indexes to store data of actual applications in the memory, therefore, it is extremely fast and suitable for caching small and medium-sized databases, but it is subject to some restrictions. The following are some of the feelings about the use of Blue Grass. 1. MySQLheap

The following articles mainly describe the usage of MySQL heap. We all know that memory tables use Hash hash indexes to store the actual application data in the memory, therefore, it is extremely fast and suitable for caching small and medium-sized databases, but it is subject to some restrictions. The following are some of the feelings about the use of Blue Grass. 1. MySQL heap

The following articles mainly describe the usage of MySQL heap. We all know that memory tables use Hash hash indexes to store the actual application data in the memory, therefore, it is extremely fast and suitable for caching small and medium-sized databases, but it is subject to some restrictions. The following are some of the feelings about the use of Blue Grass.

1. MySQL heap is visible to all users, which makes it very suitable for caching.

2. It is only suitable for use. Heap does not allow xxxTEXT and xxxBLOB data types. Only the = and <=> operators are allowed to search records (not allowed <,>, <= or> =). auto_increment is not supported; only non-empty data columns can be indexed (not null ).

Note: The operator "<=>" description: NULL-safe equal. this operator and the "=" operator perform the same comparison operation. However, when both operation codes are NULL, the obtained value is 1 instead of NULL. When an operation code is NULL, the value is 0 instead of NULL.

3. Once the server is restarted, all heap table data is lost, but the heap table structure still exists because the MySQL heap table structure is stored in the actual database path and will not be automatically deleted. After the restart, the heap will be cleared. At this time, the heap query results will be empty.

4. If heap is a replicated data table, all primary key, index, auto-increment and other formats will no longer exist after the replication. You need to re-Add the primary key and index if necessary.

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

A. Before any query, run a simple query to check whether the heap table has data. If no data exists, write the data again, or DROP the table to copy a table again. 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.

B. For the page on which the heap table is to be queried, the result of the dataset is judged for the first time and only for the first time. If the result is empty, the data needs to be written again. This saves one query.

C. A better way is to automatically write data to MySQLheap every time MySQL restarts. However, you need to configure the server. The process is complicated and the versatility is limited.

Currently, Blue Grass uses the second method.

6. Some SQL statements that may be used as expected

If the table exists, delete it.

 
 
  1. DROP TABLE IF EXISTS `abc`;

Copy the entire table xyz to heap table abc (including all data)

 
 
  1. CREATE TABLE `abc` type=heap select * from `xyz`;

Add primary key id

 
 
  1. ALTER TABLE `abc` ADD PRIMARY KEY (`id`);

Add index username

 
 
  1. ALTER TABLE `abc` ADD INDEX `abc` (`username`);

The above content is a summary of the usage of MySQL heap. I hope you will get some benefits.

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.