Analysis of Cache Management Ideas in MySQL Databases

InMySQL databaseThe user may execute the same query statement multiple times. In order to improve query efficiency, the database will divide into special regions to store the most recently executed queries.Cache. Because the memory runs much faster than the hard disk. Therefore, the query efficiency can be improved through the cache mechanism. When you execute the same query next time, you can directly obtain data from the cache, instead of reading data from the data file in the hard disk, or you can skip the parsing work.

I. Application Environment of data cache

Data caching does not play a proper effect under any circumstances. If an enterprise has an infrequently changed table and the server receives a large number of identical queries from this table, the data cache can achieve good results. Generally, Web applications have obvious effects. For example, there is a product information table in the database. Enterprise users need to query product information on the webpage. If the system is designed, the default query result shows the product information that has been traded in the last month. Each time a user queries the product information by default, the information will be obtained from the cache (if the related information has not been updated ). In this case, the system query speed is faster.

If an enterprise has an infrequently changed table and the server receives a large number of identical queries from this table, I suggest you enable the data cache mechanism. Before starting, you can use the name (as shown in) to check whether the system cache is enabled. As shown in, if the query result is YES, it indicates that the data cache mechanism has been enabled in the system.

Ii. Restrictions on Data Cache Usage

Data caching does not improve queries in any situation. Based on my project experience, I think that the data cache mechanism will not be very effective in the following situations.

First, the tables involved in the query will be changed frequently. For example, in an inventory management system, there may be two forms of product and sales records. Generally, the product table is not updated, and the sales record table may change every minute. In this case, the cache mechanism is not very effective for the sales record table. According to the working principle of the cache, when a table is changed, its corresponding data cache related entries will be cleared.

Second, the query cache is not used and the server is more convenient. Based on the B/S or C/S architecture, applications can be divided into two types: Server disconnection and client. When using the data cache, the database administrator should consider that in the MySQL database, the query cache does not apply to the query statements compiled by the server. When the database administrator is using statements written by the server, note that these statements do not apply the cache technology.

Third, there are two basic conditions for using the cache for queries: the query statements used are identical and the related data cousin ou has changed. As mentioned above. What I want to say here is, what is the same as the query statement tile circle? Simply put, the two query statements used before and after (not necessarily connected together) are completely consistent. Not only query fields, but also query conditions. A misunderstanding should be realized here. If a user queries a product information table five minutes ago, the user queries all product information records without any query conditions. Five minutes later, another user queries the product information table. In this case, the user uses the query conditions. For example, the user only queries the product information created in the last month. Obviously, the result of the next query is a subset of the previous query result (regardless of whether the product basic table has been changed during this interval ). Data caching should be available. However, note that the two query statements before and after are different (their query conditions are different ). Even if the results are the same or have a inclusion relationship, the database will re-parse the SQL statement and then retrieve data from the data file on the hard disk.

In addition, if you use custom functions, custom variables, or referenced tables in the system database in the query statement, the cache mechanism will also become invalid.

3. Improve Cache Usage

Through rational database design, you can improve the Cache Usage and expand the Cache Usage field. Specifically, the database administrator can start from the following aspects.

1. Break down the table based on the frequency of data changes

For example, there are two parts: basic product information and the latest product inventory. If you do not consider caching, you can place the basic product information and product inventory in the same table, and then use other jobs to update the inventory quantity. In this case, the inventory quantity of the product can be intuitively reflected on the front-end interface. But from the perspective of cache design, this operation is not very reasonable. Because the product information is relatively not changing, but the inventory quantity is often changing. If they are placed on the same table, the content in the data cache will be cleared due to the constant update of the inventory quantity (data cache related to the product information table ). At this time, if many users want to query the product description and specifications (they may not pay attention to the product inventory), they will not be able to use the data cache. Because there is no relevant data in the cache (the inventory is cleared due to the changing quantity ).

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

2. Use queries with default conditions to increase the cache hit rate

In the above analysis, I mentioned that two identical SQL statements can use the cache. If the conditions are different or the fields used are different, the database system will not use the cache for query optimization. In addition, the MySQL database is different from other databases. For SQL statement parsing, its size is realistic and sensitive. That is to say, if the keywords of the same query statement are case-insensitive, different SQL statements are used. This is a headache. In this case, it is best to pay attention to the following points when designing the client application.

First, you must get used to queries with default conditions to increase the cache hit rate. If you are designing the product information query function, you can query all information by default or specify a fixed condition. This improves the cache hit rate. Instead of setting different default values on different user interfaces. In order to improve the friendliness of the interface, some application systems provide users with personalized parameters to save their personalized content. At this time, although the user interface can be improved, it will obviously reduce the data cache hit rate. In this case, the database administrator needs to balance the humanized design with the query performance of the system.

Different applications use the same query statement for the same table. For example, you can query the product information in the product information window or by report. In this case, the corresponding background table is the same. The system can obtain data from the cache as long as the query statement is the same and the database table does not change during this period. In actual work, forms and reports are often designed and developed by different people. If these two SQL statements have different writing habits, one prefers to use uppercase letters, while the other prefers to use lowercase letters. In the system, the time zone for SQL query statement Parsing is case sensitive. If the case is different, different SQL statements are considered to be different, and the system cannot use the cache. In this case, different users must unify the writing specifications of SQL statements. For example, either uppercase or lowercase are used. The Project Administrator should formulate relevant rules based on the actual situation.

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

When the data cache is full, the new data overwrites the old data. For example, a user queries a product information. One hour later, he used the same statement to query the product information (assuming that the product information table has not changed during this process ). Will query statements 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, 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 does not change, the database system still needs to obtain data from the data file on the hard disk.

To improve query efficiency and cache hit rate, it is best to increase the cache space on the server. The current Memory price is relatively low, and the investment should not be too large. Especially when different applications are implemented on a server, it is quite necessary to increase the memory capacity.

I have introduced so much about the resolution of Cache Management in the MySQL database. I hope everyone can learn from it. If you encounter similar problems in your work in the future, you can easily solve them.