Misunderstandings about query_cache of mysql

In fact, this statement is not completely correct. First, the key value of query_cache in mysql is not a simple query, but a query with databasename and flag. This can be seen from the source code. I will not describe it here, but I will analyze it later. What is important is the second point. Is it a space? Does mysql consider it a different query? In fact, this is case-based. It depends on the space. If the space is added before the query, for example, adding a space at the start of the query, this does not affect the query cache results. mysql considers this as a query, if the space is in the query, the query cache results will be affected. mysql considers the space as a different query.

Next we will analyze the experiment and source code. First, let's test:

First, let's take a look at the status of mysql query_cache:

First, we can confirm that the query_cache function of mysql is enabled.

Next, let's take a look at the status:

Because this db is a new db, the hits and inset values are both 0. Now we execute a select statement:

Status changed:

As you can see, after executing a select statement, the current qcache status is insert + 1, so we can infer that the select statement just now has been added to the qcache. Now let's add a space in front of the SQL statement to see what will happen?

Note that this SQL statement has a space before it.

According to the theory on the Internet, this SQL statement should be inserted into another cache as another key without reusing the previous cache. But what about the result?

We can see that the hits is changed to 1, but the inserts is not changed at all. This shows that the query with spaces added in front hits the query result set without spaces. From this, we can draw a conclusion that the previous online statements are not rigorous.

So what is going on? What should I do? Why does the result set with spaces match the query result set without spaces. In fact, we can get the answer through the source code.

Let's look at the mysql source code. Here we look at 5.1. There is such a section in the send_result_to_client (this function is a function that mysql calls query_cache) function ,,
Copy codeThe Code is as follows:
/*
Test if the query is a SELECT
(Pre-space is removed in dispatch_command ).

First '/'Looks like comment before command it is not
Frequently appeared in real life, consequently we can
Check all such queries, too.
*/
If (my_toupper (system_charset_info, SQL [I])! ='S '|
My_toupper (system_charset_info, SQL [I + 1])! = 'E' |
My_toupper (system_charset_info, SQL [I + 2])! = 'L ')&&
SQL [I]! = '/')
{
DBUG_PRINT ("qcache", ("The statement is not a SELECT; Not cached "));
Goto err;
}

Check whether the statement is a select statement, with emphasis on the above comments. In particular, in the arc, pre-space is removed in dispatch_command. That is to say, the extra spaces before the start of the statement have been processed and removed from the dispache_command function.

Let's take a look at the dispache_command method, which contains the following:
Copy codeThe Code is as follows:
If (alloc_query (thd, packet, packet_length ))
Break; // fatal error is set
Char * packet_end = thd-> query () + thd-> query_length ();
/* 'B' stands for 'buffer' parameter ', special for 'My _ snprintf '*/
Const char * end_of_stmt = NULL;

Here, the alloc_query method is called. Let's look at the content of this method:
Copy codeThe Code is as follows:
Bool alloc_query (THD * thd, const char * packet, uint packet_length)
{
Char * query;
/* Remove garbage at start and end of query */
While (packet_length> 0 & my_isspace (thd-> charset (), packet [0])
{
Packet ++;
Packet_length --;
}
Const char * pos = packet + packet_length; // Point at end null
While (packet_length> 0 &&
(Pos [-1] = ';' | my_isspace (thd-> charset (), pos [-1])
{
Pos --;
Packet_length --;
}
/* We must allocate some extra memory for query cache
The query buffer layout is:
Buffer: =
<Statement> The input statement (s)
'\ 0' Terminating null char (1 byte)
<Length> Length of following current database name (size_t)
<Db_name> Name of current database
<Flags> Flags struct
*/
If (! (Query = (char *) thd-> memdup_w_gap (packet,
Packet_length,
1 + sizeof (size_t) + thd-> db_length +
QUERY_CACHE_FLAGS_SIZE )))
Return TRUE;
Query [packet_length] = '\ 0 ';
/*
Space to hold the name of the current database is allocated. We
Also store this length, in case current database is changed
Execution. We might need to reallocate the 'query' buffer
*/
Char * len_pos = (query + packet_length + 1 );
Memcpy (len_pos, (char *) & thd-> db_length, sizeof (size_t ));
Thd-> set_query (query, packet_length );
/* Reclaim some memory */
Thd-> packet. shrink (thd-> variables.net _ buffer_length );
Thd-> convert_buffer.shrink (thd-> variables.net _ buffer_length );
Return FALSE;
}

This method will process the query at the beginning (line 1 of code) and remove the garbage at the beginning and end.
As we can see here, we basically understand that mysql will pre-process the input query and process the space and other things, so spaces that do not start with will not affect query_cache, because for mysql, is a query.