查询缓存MySQL(Query Caching MySQL)

查询缓存MySQL(Query Caching MySQL)

我尝试使用查询缓存。 缓存应该将查询与结果一起存储。 所以我启用了my.cnf中的查询缓存。 在持续时间表明，它的工作原理：

RESET QUERY CACHE; SELECT * FROM t_data GROUP BY col2; //duration: 0.034sec SELECT * FROM t_data GROUP BY col2; //duration: 0.015sec SELECT * FROM t_data GROUP BY col2; //duration: 0.014sec

其实一切都很好，但之后我在这个查询中使用了EXPLAIN。 那就是结果：

id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra 1 | SIMPLE | t_data | ALL | | | | | 921 | Usi..

那么为什么我在列行中得到921？ 结果只有15行。 如果认为MySQL将缓存中的此查询连接起来的表。 为什么再次搜索整个表的MySQL？ 是我的第一篇文章。 我希望一切都是正确的！

I try to work with Query Caching. The cache should stores the query together with the result. So i enabled the Query Cache in my.cnf. On the duration shows me that it works:

RESET QUERY CACHE; SELECT * FROM t_data GROUP BY col2; //duration: 0.034sec SELECT * FROM t_data GROUP BY col2; //duration: 0.015sec SELECT * FROM t_data GROUP BY col2; //duration: 0.014sec

Actually everything is fine but after that I used EXPLAIN on this query. Thats the result:

id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra 1 | SIMPLE | t_data | ALL | | | | | 921 | Usi..

So why i get 921 in column rows? The Result has only 15 rows. If thought that MySQL take the table which is connected by this query in the cache. Why searched MySQL again the whole table? Is my first post. I hope everything is correct!

最满意答案

执行计划是解释MySQL如何执行查询 （重点是我的）。 换句话说，这是询问MySQL如何处理给定的SQL查询来获取请求的信息的结果。 因此，查询的速度通常是一个很好的估计，但它并不意味着实际的查询执行。 此外，它也不显示实际的行号，主要是因为它通常不会知道真实的数字，并且如果没有实际运行查询就无法得到它。

另一方面， MySQL查询缓存是一项完全避免查询执行的功能：

查询缓存将SELECT语句的文本与发送给客户端的相应结果一起存储。 如果稍后收到相同的语句，服务器将从查询缓存中检索结果，而不是再次解析并执行该语句 。

您在实际查询执行时获得的度量标准称为分析 ; 这是完全不同的事情。 一些DBMS提供了相当不错的查询性能分析功能，据我所知，MySQL只有一些通用数据。

The execution plan is an explanation of how MySQL would execute a query (emphasis mine). In other words, it's the result of asking MySQL how it would handle a given SQL query to fetch the requested information. As such, it's normally a good estimation on how fast the query will be, but it doesn't imply an actually query execution. Also, it doesn't show actual row numbers either, mainly because it normally won't know the real figure and it'd be impossible to get it without actually running the query.

On the other side, the MySQL Query Cache is a feature to avoid query execution altogether:

The query cache stores the text of a SELECT statement together with the corresponding result that was sent to the client. If an identical statement is received later, the server retrieves the results from the query cache rather than parsing and executing the statement again.

The metrics you would obtain upon an actual query execution are called profiling; it's an entirely different thing. Some DBMSs offer pretty good per-query profiling features, MySQL as far as I know only has some generic data.