MySQL Query cache import Query slow resolution

into Mysql:show full processlist;

Command more sleep, the client over the connection is also normal, repeated execution of the above command to find the state of some command "coping to TMP table", decisively open my.cnf found no tmp_table_size settings, MySQL environment is the use of Yum installation, which is why to compile MySQL's sake

Mysql> show VARIABLES like ' tmp_table_size ';
+----------------+----------+
| variable_name | Value |
+----------------+----------+
| Tmp_table_size | 16777216 |
+----------------+----------+
1 row in Set (0.00 sec)

Mysql> show VARIABLES like ' max_heap_table_size ';
+---------------------+----------+
| variable_name | Value |
+---------------------+----------+
| Max_heap_table_size | 16777216 |
+---------------------+----------+
1 row in Set (0.00 sec)

The value of these two settings is not so low that it does not appear to be a problem with TMP table, but in order to ensure that there is no problem, increase in MY.CNF:

Tmpdir=/tmp
tmp_table_size=1073741824

Restart MySQL, the problem remains. It seems to be set up enough, or other reasons. Open the next mysql-log, found that the query table is constantly inserting data, according to the PV number of the site can be judged by someone in the frequent use of the search function, this feature is not configured Sphinx Full-text indexing, but directly read the database, and real-time statistics user input keywords. However, the frequent insertion of new data by people will cause the MySQL query cache to be rebuilt during use, which causes the MYSQLD CPU usage to be high. It seems that such real-time sorting should be given to memcache or Redis to perform, and to verify the group

The misunderstanding of MySQL's Query_cache cognition

Below we through the experiment and the source code concrete analysis. First, let's experiment:

First, let's look at the status of the MySQL Query_cache:

First, we can confirm that MySQL's Query_cache function is open.

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

Because this DB is the new db, so the Hits,inset are all 0, now we execute a SELECT statement:

Status becomes:

As you can see, after executing a SELECT, the current Qcache state is, insert+1, so that we can infer that the SELECT statement has now been added to the Qcache. So let's just add a space to the front of that SQL and see what happens?

Notice that this SQL is a little more than a space before that SQL.

According to the online theory, this SQL should be inserted as another key to another cache, will not reuse the previous cache, but the result?

We can see that the hits has changed to 1 and the inserts hasn't changed at all, which means that query with the space above has hit the result set of query without spaces. From this, we can come to the conclusion that the online version of the previous, is not rigorous.

How does query Cache handle subqueries?
This is one of the most common problems I have encountered. In fact, query Cache is the client request submitted query for the object to handle, as long as the client request is a query, whether this query is a simple single table query or multiple table Join, or a subquery with a complex SQL, are considered as a query, do not will be split into multiple Query for Cache. Therefore, complex query with subqueries will only produce a cache object, the subquery will not produce a separate cache content. The same is true for statements of type Union[all.

Is the Query Cache a block-style chunk of data stored?
No, the cached content in query cache only contains the result data that query requires, and is the result set. Of course, it's not just the result data, but also other information related to the result, such as the character set of the client connection that generated the Cache, the character set of the data, the Default database for the client connection, and so on.

Why is the query cache so efficient that even if all the data can be put into memory, sometimes it's less efficient to use query cache?
Query Cache lookup, after the MySQL received the client request after the permissions of the query to authenticate, before SQL parsing. In other words, when MySQL received the client's SQL, only need to do the appropriate authorization after the Query Cache to find results, and even do not need to go through the Optimizer module to perform the analysis of the planned optimization, not to occur any storage engine interaction, Reduces a lot of disk IO and CPU operations, so it's very efficient.

Does the SQL statement case submitted by the client have an effect on Query Cache?
There is, because the Query cache in memory is a hash structure to map, hash algorithm is the basis for the composition of the SQL statement characters, so must be the entire SQL statement at the character level exactly the same, in query cache can hit, even if more than one space is not.

What is the content of an SQL statement in the Query Cache, and under what circumstances will it be invalidated?
In order to ensure that the contents of Query cache are absolutely consistent with actual data, any changes in the data in the table, including additions, modifications, deletions, and so on, will invalidate all SQL query cache referenced to the table.

Why does my system have a lower overall performance after the Query Cache is turned on?
When the query cache is turned on, especially when our Query_cache_type parameter is set to 1, MySQL will search for query cache for each SELECT statement, although it is simpler to find operations, but it still consumes some CPU Count Resources. Because of the failure mechanism of query cache, because the data on the table is more frequent, a large number of query cache is frequently invalidated, so query cache hit rate may be relatively low. So in some scenarios, Query Cache can not only improve efficiency, but may cause negative effects.

How do I confirm that the Query Cache of a system is running healthy, what is the hit rate, and how much is set?
MySQL provides a series of Global status to record the current state of Query Cache, as follows:

Qcache_free_blocks: The number of memory blocks in the Query Cache that is currently in an idle state
qcache_free_memory: The total amount of Query Cache memory currently in the idle state
qcache_hits:query Cache Hit count
Qcache_inserts: The number of times a new query cache was inserted into query cache, that is, the number of missed hits
qcache_lowmem_prunes: When query cache memory is not enough, the number of times you need to remove the old query cache from the new cache object
qcache_not_cached: No cache SQL number, including cache-unable SQL and SQL not cache due to Query_cache_type settings
Qcache_queries_in_cache: The number of SQL currently in Query cache
Total block number in qcache_total_blocks:query Cache
Can calculate the cache hit rate according to these several states, calculate the query cache size setting is enough, overall, I personally do not recommend the query cache size set more than 256MB, this is the industry more commonly used practice.

Can MySQL Cluster use Query Cache?
In fact, in our production environment also did not use the MySQL Cluster, so I also did not use Query Cache in the MySQL Cluster environment experience, but the MySQL documentation that is true in the MySQL Cluster can use query C Ache From the principle of MySQL Cluster analysis, also feel that should be able to use, after all, SQL nodes and data nodes are relatively independent, their respective roles, but the failure mechanism of the Cache will be slightly more complicated.