Shulou(Shulou.com)05/31 Report--

This article introduces what the MySQL database cache application environment has, the content is very detailed, interested friends can refer to, hope to be helpful to you.

What is the application environment of MySQL database caching

Data caching does not work as it should in any case. Data caching works well if the enterprise has a table that does not change often and the server is subject to a large number of the same queries for that table. Usually, for the application of Web, the effect will be more obvious. For example, there is a product information table in the database. Users of enterprises need to query product information through web pages. If the system is designed, the result of the default query is to display the product information that has been traded in the previous month. Then each time the user queries the product information by default, it will get the information from the cache (if the relevant information has not been updated). At this time, the query speed of the system will be faster.

Restrictions on the use of data caching

Not in any case, data caching will improve the query. According to the author's project experience, I think that in the following situations, the effect of the data caching mechanism will not be very great.

First, the tables involved in the query will change frequently. For example, in an inventory management system, there may be two forms of product and sales records. Product tables are generally not updated, while sales records may change every minute. At this point, the caching mechanism will not have much effect on the sales record table. Because according to how caching works, when a table is changed, the relevant entries in its corresponding data cache are emptied.

Second, the query cache does not use statements that are more convenient with the server. The related applications can be divided into two categories: server outage and client according to the architecture of Bhand S or Cramp S. When using data caching, the database administrator should consider that query caching does not apply to query statements written by the server in the MySQL database. When the database administrator is using statements written by the server, be aware that these statements do not apply caching techniques.

The third is the two basic conditions of using cache when querying: the query statement used is exactly the same and the related data table has changed. As for the latter one, it has already been mentioned above. What the author wants to say here is that what is called a query statement tile circle is as simple as the two query statements used before and after (not necessarily connected together) are exactly the same. It includes not only the fields of the query, but also the conditions of the query. We should realize a misunderstanding here. If the user inquires a product information table 5 minutes ago, it does not use any query conditions, and queries all product information records. Five minutes later, there is another user to query the product information table, which uses the query conditions, such as only querying the product information newly built one month ago. It is obvious that the result of the latter query is a subset of the previous query (regardless of whether the product base table has changed during this interval). It should be possible to use data caching in theory. However, it should be noted here that the two query statements before and after this time are different (their query conditions are different). Even if the result is the same or has an inclusion relationship, the database will first reparse the SQL statement and then retrieve the data from the data file on the hard disk.

It should also be noted that if the user uses custom functions, custom variables, or references to tables in the system database in the query statement, the caching mechanism will also fail.

How to improve the effect of MySQL database cache

Through the reasonable design of the database, we can improve the use effect of the cache and expand the use field of the cache. Specifically, database administrators can start from the following aspects.

1. Decompose the table according to the frequency of data changes

For example, there are two parts: basic product information and the latest product inventory. Without considering the cache, you can put the product basic information in the same table as the product inventory, and then update the inventory quantity through other jobs. In this way, the inventory quantity of the product can be directly reflected in the front interface. However, from the point of view of cache design, this operation is not very reasonable. Because the product information is relatively unchangeable, while the inventory quantity is constantly changing. If you put them on the same table, the contents of the data cache will be constantly emptied (the data cache associated with the product information table) as the inventory quantity is constantly updated. At this point, if many users want to query the description and specifications of the product (they may not care about the inventory of the product), then they will not be able to use the data cache. Because there is no relevant data in the cache (it is emptied due to the changing amount of inventory).

In this case, the database administrator can store the inventory quantity and the basic product information on two different tables and then associate them with keywords. The advantage of this is that the inventory update does not affect the data cache corresponding to the product basic information table (they are two tables). So as to improve the cache hit rate when querying product information.

2. Use the default condition query to improve the cache hit rate.

In the above analysis, the author mentioned that it takes two identical SQL statements to use caching. If the conditions are different or the fields used are different, the database system will not use caching for query optimization. In addition, MySQL database is different from other databases, for SQL statement parsing, its size is realistic and sensitive. In other words, the same query statement, if its keyword case is different, then it will be considered to use different SQL statements. This is a bit of a headache. In view of this situation, it is best to pay attention to the following points when designing client applications.

One is to get used to using the default condition of the query to improve the cache hit rate. If you are designing the function of querying product information, you can consider querying all the information by default or specifying certain fixed conditions. This improves the hit rate of the cache. Instead of setting different default values in different user interfaces. Some application systems, in order to improve the friendliness of the interface, will provide users with some personalized parameters to save their personalized content. Although it can improve the humanization of the interface, it will obviously reduce the hit rate of the data cache. In this case, the database administrator needs to balance the humanized design with the query performance of the system.

Different applications aim at the same query of the same table, and the query statement had better be the same. For example, the product information can be queried either through the product information window or according to the report. At this point, the corresponding background table is the same. As long as it executes the same query statement and the database table does not change during this period, the system can get the data from the cache. In practical work, forms and reports are often designed and developed by different people. If the two people now have different SQL writing habits, one person likes to use uppercase and the other prefers lowercase. In the system, SQL query statements are case-sensitive when parsing. If the case is different, it will be considered a different SQL statement and the system will not be able to use caching. Therefore, in this case, different users should unify the writing standards of SQL statements, such as using either uppercase or lowercase. The project manager should make the relevant rules according to the actual situation.

3. Increase the cache space to improve the cache hit rate of the database.

When the data cache is full, the new data overwrites the old data. For example, now the user has inquired about a sum of product information. An hour later, it uses the same statement to query the product information (assuming that the product information table has not changed in the process). Whether the query statement will be cached? the answer is not necessarily. If the cache space of the enterprise server is large enough and the old cache information is not overwritten by the new query content, then the information in the cache will be used. On the contrary, if the cache is small, the old cache information of the system will be overwritten by the new query content. In this case, even if the query statement is the same and the table has not changed, the database system still has to get the data from the data file on the hard disk.

Therefore, in order to improve the efficiency of the query and improve the hit rate of the cache, it is best to increase the cache space on the server. Now that the price of memory is relatively cheap, this investment should not be too large. Especially when implementing different applications on one server, it is necessary to increase the capacity of memory.

On the MySQL database cache which application environment is shared here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.