文档章节

8.10.3 The MySQL Query Cache

道无涯1934
 道无涯1934
发布于 2018/06/08 15:10
字数 638
阅读 1
收藏 0

原文地址

8.10.3.1 How the Query Cache Operates

8.10.3.2 Query Cache SELECT Options

8.10.3.3 Query Cache Configuration

8.10.3.4 Query Cache Status and Maintenance

Note

The query cache is deprecated as of MySQL 5.7.20, and is removed in MySQL 8.0.

查询缓存在5.7.20版本中已经过时,在8.0版本中会移除这个特性

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 query cache is shared among sessions, so a result set generated by one client can be sent in response to the same query issued by another client.

The query cache can be useful in an environment where you have tables that do not change very often and for which the server receives many identical queries. This is a typical situation for many Web servers that generate many dynamic pages based on database content.

The query cache does not return stale data. When tables are modified, any relevant entries in the query cache are flushed.

Note

The query cache does not work in an environment where you have multiple mysqld servers updating the same MyISAM tables.

The query cache is used for prepared statements under the conditions described in Section 8.10.3.1, “How the Query Cache Operates”.

Note

The query cache is not supported for partitioned tables, and is automatically disabled for queries involving partitioned tables. The query cache cannot be enabled for such queries.

Some performance data for the query cache follows. These results were generated by running the MySQL benchmark suite on a Linux Alpha 2×500MHz system with 2GB RAM and a 64MB query cache.

  • If all the queries you are performing are simple (such as selecting a row from a table with one row), but still differ so that the queries cannot be cached, the overhead for having the query cache active is 13%. This could be regarded as the worst case scenario. In real life, queries tend to be much more complicated, so the overhead normally is significantly lower.

  • Searches for a single row in a single-row table are 238% faster with the query cache than without it. This can be regarded as close to the minimum speedup to be expected for a query that is cached.

To disable the query cache at server startup, set the query_cache_size system variable to 0. By disabling the query cache code, there is no noticeable overhead.

The query cache offers the potential for substantial performance improvement, but do not assume that it will do so under all circumstances. With some query cache configurations or server workloads, you might actually see a performance decrease:

  • Be cautious about sizing the query cache excessively large, which increases the overhead required to maintain the cache, possibly beyond the benefit of enabling it. Sizes in tens of megabytes are usually beneficial. Sizes in the hundreds of megabytes might not be.

  • Server workload has a significant effect on query cache efficiency. A query mix consisting almost entirely of a fixed set of SELECT statements is much more likely to benefit from enabling the cache than a mix in which frequent INSERTstatements cause continual invalidation of results in the cache. In some cases, a workaround is to use theSQL_NO_CACHE option to prevent results from even entering the cache for SELECT statements that use frequently modified tables. (See Section 8.10.3.2, “Query Cache SELECT Options”.)

To verify that enabling the query cache is beneficial, test the operation of your MySQL server with the cache enabled and disabled. Then retest periodically because query cache efficiency may change as server workload changes.

© 著作权归作者所有

道无涯1934
粉丝 0
博文 9
码字总数 3624
作品 0
程序员
私信 提问
关于 MySQL Query Cache

最近经常有人问我 MySQL Query Cache 相关的问题,就整理一点 MySQL Query Cache 的内容,以供参考。 顾名思义,MySQL Query Cache 就是用来缓存和 Query 相关的数据的。具体来说,Query Cac...

范堡
2010/12/21
561
3
【转】MySQL Query Cache 小结

原文出处:http://isky000.com/database/mysql-query-cache-summary 最近经常有人问我 MySQL Query Cache 相关的问题,就整理一点 MySQL Query Cache 的内容,以供参考。 顾名思义,MySQL Q...

吞吞吐吐的
2017/10/18
0
0
mysql查询缓存打开、设置、参数查询、性能变量意思

第一: querycachetype 使用查询缓存的方式 一般,我们会把 querycachetype 设置为 ON,默认情况下应该是ON mysql> select @@querycachetype; +--------------------+ | @@querycachetype | ......

kisshua
2016/03/10
46
0
[玩转MySQL之四]MySQL缓存机制

一、前言 在当今的各种系统中,缓存是对系统性能优化的重要手段。MySQL Query Cache(MySQL查询缓存)在MySQL Server中是默认打开的,但是网上各种资料以及有经验的DBA都建议生产环境中把MySQL...

沈欧邦
2018/09/30
0
0
mysql优化之query_cache_size

MySQL查询缓存保存查询返回的完整结果。当查询命中该缓存,会立刻返回结果,跳过了解析,优化和执行阶段。 查询缓存会跟踪查询中涉及的每个表,如果这写表发生变化,那么和这个表相关的所有缓...

拎壶冲冲冲
2018/06/26
0
0

没有更多内容

加载失败,请刷新页面

加载更多

浅谈Command命令模式

一、前言 命令也是类,将命令作为一个类来保存,当要使用的时候可以直接拿来使用,比如脚本语言写出的脚本,只需要一个命令就能执行得到我们想要的需要操作很长时间才能得到的结果。这是一个...

青衣霓裳
5分钟前
1
0
Less导入指令

在标准CSS中,@import 规则必须位于所有其他类型的规则之前。但是Less.js不在乎我们将 @import 语句放在什么位置。 @import 伪指令常用于在代码中导入文件,它将Less 代码分布在不同的文件上...

凌兮洛
6分钟前
1
0
【apk】空包签名

命令语法 jarsigner -verbose -keystore [keystorePath] -signedjar [apkOut] [apkIn] [alias] 例 子: jarsigner -verbose -keystore F:\签名\laidianyi_customer.keystore -signedjar F:\......

Agnes2017
9分钟前
2
0
虚拟化的操作技巧!

从物理基础设施迁移到虚拟基础设施时,虚拟环境的设计和布局应模仿物理做法,企业级虚拟化软件允许创建虚拟交换机,虚拟局域网(VLANS)和私有网络可以协助迁移,分析物理和逻辑网络图,复制...

青果云小潘
13分钟前
2
0
SEO网站运营助手

SEO网站运营助手有哪些功能? 提交: 百度链接主动提交 + 熊掌号周推(支持500万条提交额,所以合并在一起提交) 更新: 网站内容改动后,对于已收录的链接,可以选择更新 查询: 对百度收录判断...

NoCome
16分钟前
3
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部