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

This article focuses on "what is MySQL query caching". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn "what is MySQL query cache"!

QueryCache introduction

MySQL query cache (QC:QueryCache) was introduced in MySQL 4.0.1. The query cache stores the text of SELECT statements and the result set sent to the client. If the same SQL is executed again, the server will retrieve the results from the query cache to the client, instead of parsing and executing the SQL again, and the query cache is shared between session. Therefore, the cached result set generated by one client can execute the same SQL in response to another client.

Going back to the beginning of the question, how to determine whether the SQL is shared?

Judging by whether the SQL text is completely consistent, all characters, including case and spaces, can only be shared exactly the same. The advantage of sharing is that hard parsing can be avoided and the results obtained directly from QC are returned to the client. The following two SQL are not shared, because one is from and the other is From.

-SQL 1 select id, balance from account where id = 121;-SQL 2 select id, balance From account where id = 121

The following is the algorithm for the Oracle database to generate sql_id through SQL_TEXT. If the sql_id is not the same SQL, it will not be shared, and hard parsing will occur.

#! / usr/bin/perl-w use Digest::MD5 qw (md5 md5_hex md5_base64); use Math::BigInt; my $stmt = "select id, balance from account where id = 12l\ 0"; my $hash = md5 $stmt; my ($a * *, $hash) = unpack ("V*", $hash); my $sqln = $msb* ($sqln) + $lsb; my $stop = log ($sqln) / log (32) + 1; my $sqlid =''; my $charbase32 = '0123456789abcdfghjkmnpqrstuvwxyzbread; my @ chars = split', $charbase32 For ($iTun0; $I

New ($sqln); my $seq = $x-> bdiv (32 seq)-> bmod (32); $sqlid = $chars [$seq]. $sqlid;} print "SQL is:\ n $stmt\ nSQL_ID is\ n $sqlid\ n"

You can find that SQL 1 and SQL 2 generate different sql_ id values through code, so they are not shared.

SQL is: select id, balance from account where id = 121SQL_ID is dm5c6ck1g7bds SQL is: select id, balance From account where id = 121SQL_ID is 6xb8gvs5cmc9b

If you compare the differences in the contents of the two Java code files, you only need to understand this code thoroughly and you can transform and implement your own business logic.

QueryCache configuration

Mysql > show variables like'% query_cache%' +-- +-+ | Variable_name | Value | +-+-+ | have_query_cache | YES | | query _ cache_limit | 1048576 | | query_cache_min_res_unit | 4096 | | query_cache_size | 16777216 | | query_cache_type | OFF | | query_cache_wlock_invalidate | OFF |

Query_cache_min_res_unit description

The default size is 4KB, and if many query results are small, the default block size may result in memory fragmentation, which may force the query cache to delete queries from the cache due to insufficient memory.

In this case, the value of query_cache_min_res_unit can be reduced, and the number of free blocks and queries deleted due to pruning is given by the values of Qcache_free_blocks and Qcache_lowmem_prunes state variables. If a large number of queries have large result sets, you can increase the value of this parameter to improve performance.

QueryCache mode is usually enabled.

# modify the MySQL configuration file / etc/my.cnf, add the following configuration, and restart MySQL server. [mysqld] query_cache_size = 32m query_cache_type = 1

QueryCache usage

First, do some test data to test the scenarios with QueryCache disabled and enabled respectively.

Create a user table users and insert 100w data. CREATE TABLE `users` (`id`users`, `name` varchar (20) NOT NULL DEFAULT''COMMENT' name', `age`tinyint NOT NULL DEFAULT'0' COMMENT 'age', `gender` char (1) NOT NULL DEFAULT' M' COMMENT 'gender', `phone`varchar (16) NOT NULL DEFAULT''COMMENT' mobile phone number', `create_ time`datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'creation time, `update_ time`datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT' modification time' PRIMARY KEY (`id`) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT=' user information table' Select count (*) from users; +-+ | count (*) | +-+ | 1000000 |

Disable queryCache scen

When not using QueryCache, every time the same query statement is executed, a hard parsing occurs, which consumes a lot of resources.

# disable the configuration of QueryCache query_cache_size = 0 query_cache_type = 0

Repeat the following query to observe the execution time.

-- execute query statement mysql > select * from users order by create_time desc limit 10 for the first time +-+ | id | name | age | gender | phone | create_time | | update_time | +-+ | 997855 | User997854 | 54 | M | 15240540354 | 2020-12-15 14:34:50 | 2020-12-15 14:34:50 |. 10 rows in set (0.89 sec)-execute the same query statement mysql > select * from users order by create_time desc limit 10 for the second time +-+ | id | name | age | gender | phone | create_time | | update_time | +-+ | 997855 | User997854 | 54 | M | 15240540354 | 2020-12-15 14:34:50 | 2020-12-15 14:34:50 |. 10 rows in set (0.90 sec)-profile tracking mysql > show profile cpu,block io for query 1 +-- +-+ | Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops _ out | +-+-+ | preparing | 0.000022 | 0.000017 | 0.000004 | 0 | | 0 | | Sorting result | 0.000014 | 0.000009 | 0.000005 | 0 | 0 | executing | 0.000011 | 0.000007 | 0.000004 | 0 | 0 | Sending data | 0.000021 | 0.000016 | 0.000004 | 0 | 0 | Creating sort index | | | 0.906290 | 0.826584 | 0.000000 | 0 | 0 |

As you can see, when the same SQL query statement is executed many times, the execution time is about 0.89s, almost no difference, and the time is mainly consumed in the Creating sort index phase.

Open the queryCache scene

When query caching is enabled, the SQL text and query results will be cached in QC when the query statement is executed for the first time, and the next time you execute the same SQL execution, you can get the data from QC and return it to the client.

# disable the configuration of QueryCache

Query_cache_size = 32m

Query_cache_type = 1

-- execute query statement mysql > select * from users order by create_time desc limit 10 for the first time +-+ | id | name | age | gender | phone | create_time | | update_time | +-+ | 997855 | User997854 | 54 | M | 15240540354 | 2020-12-15 14:34:50 | 2020-12-15 14:34:50 |. 10 rows in set (0.89 sec)-execute the query statement mysql > select * from users order by create_time desc limit 10 for the second time +-+ | id | name | age | gender | phone | create_time | | update_time | +-+ | 997855 | User997854 | 54 | M | 15240540354 | 2020-12-15 14:34:50 | 2020-12-15 14:34:50 |. 10 rows in set (0.00 sec)-profile tracking data mysql > show profile cpu,block io for query 3 +-- +-+ | Status | Duration | CPU_user | | CPU_system | Block_ops_in | Block_ops_out | +-+-+ | Waiting for | Query cache lock | 0.000016 | 0.000015 | 0.000001 | 0 | 0 | checking query cache for query | 0.000007 | 0.000007 | 0.000000 | 0 | 0 | checking privileges on cached | 0.000004 | 0.000003 | 0.000000 | 0 | 0 | checking permissions | 0.000034 | 0.000033 | 0.000001 | 0 | 0 | sending cached result to clien | 0.000018 | 0.000017 | 0.000001 | 0 | 0 |

You can see that the first execution of QueryCache does not cache SQL text and data, and the execution time is 0.89s. Because the QC,SQL text is enabled and the execution result is cached in QC, the second execution executes the same SQL query statement, which directly hits QC and returns data, so the execution time is reduced to 0s. From profile, you can see that sending cached result to client directly sends the data in QC back to the client.

Query cache hit ratio

Query cache-related status variables

Mysql > SHOW GLOBAL STATUS LIKE 'QCache\ _%'; +-+-+ | Variable_name | Value | +-+-+ | Qcache_free_blocks | 1 |-- query the number of available memory blocks in the cache. | Qcache_free_memory | 33268592 |-- query the amount of memory available in the cache. | Qcache_hits | 121 |-- the number of times to get the result set from QC. | Qcache_inserts | 91 |-the number of times the query result set is added to the QC, which means that the query is no longer in the QC. | Qcache_lowmem_prunes | 0 |-the number of queries deleted from the query cache due to insufficient memory. | Qcache_not_cached | 0 |-- number of uncached queries. | Qcache_queries_in_cache | 106 |-- the number of queries registered in the query cache. | Qcache_total_blocks | 256 |-- query the total number of blocks in the cache.

Query cache hit rate and average size

Qcache_hits Query cache hit rate =-x 100% Qcache_hits + Qcache_inserts + Qcache_not_cached query_cache_size = Qcache_free _ memory Query Cache Avg Query Size =-- Qcache_queries_in_cache

Impact of update operation on QC

For example, in the internal transfer logic of the payment system, you need to lock the account and then modify the balance. The main steps are as follows:

In this case, QC is not suitable, because the first execution of the query SQL misses and returns the result to the client. After adding SQL text and result set to QC, the next time the same SQL returns the result directly from QC without hard parsing, but each Update updates the data first, then locks the QC and then updates the cached result, which will invalidate the previous cached result. The query SQL of the execution phase again still missed, and some have to be re-added to QC, so frequently locking QC- > checking QC- > adding QC- > updating QC is very resource-consuming and reduces the concurrent processing capacity of the database.

Why give up QueryCache

General business scenario

According to the operation type of business system, it can be divided into OLTP (OnLine Transaction Processing online transaction processing system) and OLAP (OnLine Analysis Processing online Analytical processing system). For government and enterprise business, it can also be divided into BOSS (Business Operation Support System- Business Operation support system) and BASS (Business Analysis Support System- Business Analysis support system). The characteristics of these two systems are summarized.

Scenarios suitable for QueryCache

First of all, the size of the query cache QC is only a few MB, which is not suitable for setting the cache too large, because threads need to lock the QueryCache during the update process, so for very large caches, you may see lock contention problems. So what helps to benefit from query caching? Here are the ideal conditions:

Hongmeng official Strategic Cooperation to build HarmonyOS Technology Community

The same query is issued repeatedly by the same or more clients.

The underlying data being accessed is static or semi-static in nature.

Queries can be resource-intensive and / or build short but computationally complex result sets, while the result sets are relatively small.

Both concurrency and query QPS are not high.

These four cases are only ideal, the actual business systems are operated by CRUD, the data is updated frequently, and the QPS of the query interface is relatively high, so there are very few business scenarios that can meet the above ideal situation. I can think of configuration tables, data dictionary tables, which are basically static or semi-static, and QC can be used to improve query efficiency when possible.

Scenarios that are not suitable for QueryCache

If the table data changes quickly, the query cache becomes invalid, and as queries are constantly deleted from the cache, the server load increases and processing becomes slower, and if the data is updated every few seconds or more frequently, query caching is unlikely to be appropriate.

At the same time, the query cache uses a single mutex to control access to the cache, which actually imposes a single-threaded gateway on the server SQL processing engine. When the query QPS is relatively high, it may become a performance bottleneck and seriously reduce the query processing speed. Therefore, query caching is disabled by default in MySQL 5.6.

Delete QueryCache

The query cache is deprecated as of MySQL 5.7.20, and is removed in MySQL 8.0. Deprecation includes query_cache_type, you can see that from the default disable of MySQL 5.6, the obsolescence of 5.7and the complete deletion of 8.0, Oracle also made such a choice with a combination of various considerations.

We talked about the suitable and unsuitable QueryCache business scenarios above, and found that this feature is too demanding for business scenarios, which is difficult to match with the actual business, and after enabling, the database concurrency and processing capacity will be greatly reduced. The following summarizes the main reasons why MySQL changes from Disabled- > Deprecated- > Removed QueryCache.

At the same time, the fragmentation of query cache will also increase the load of the server and affect the stability of the database. In the official search of QueryCache in Oracle, we can find that there are many Bug, which determines the direct and decisive Remove of MySQL 8.0.

At this point, I believe you have a deeper understanding of "what is MySQL query cache". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.