MySQL 查询缓存简介
MySQL 查询缓存是 MySQL 将查询返回的完整结果保存在缓存中.当查询命中该缓存,MySQL 会立刻返回结果,跳过了解析,优化和执行阶段.
查询缓存系统会跟踪查询中涉及的每个表,如果这些表发生变化,那么和这个表相关的所有的缓存都将失效.
写在最前面
尽管 查询缓存 目的是提升性能,但它具有严重的可扩展性问题,很容易成为严重的瓶颈.
故 MySQL 查询缓存在 5.7 中已被列为不建议使用,将在 MySQL 8.0 中移除
MySQL 产品经理给出了解决方案,可拉至文章最后阅读
MySQL 如何判断缓存命中:
缓存存放在一个引用表中,通过一个哈希值引用,这个哈希值包括了如下因素,即查询本身,当前要查询的数据库,客户端协议的版本等一些可能影响返回结果的信息.
当查询语句中有一些不确定的数据时,则不会被缓存.例如包含函数 NOW() 或者 CURRENT_DATE() 的查询不会被缓存.类似的,包含 CURRENT_USER 或者 CONNECTION_ID() 的查询语句因为会根据不同的用户返回不同的结果,所以也不会被缓存.如果查询中包含任何用户自定义函数,存储函数,用户变量,临时表,MySQL 库中的系统表,或者任何包含级别权限的表,都不会被缓存.
如果希望换成一个带日期的查询,那么最好将日期提前计算好,而不要直接使用函数.例如:
查询缓存失效
... DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY) -- Not cacheable !
... DATE_SUB('2007-01-01', INTERVAL 1 DAY) -- Cacheable !
如果表更改,则使用该表的所有缓存查询将变为无效,并从缓存中删除. 这包括使用映射到已更改表的 MERGE 表的查询.可以通过许多类型的语句来更改表,例如 INSERT,UPDATE,DELETE,TRUNCATE TABLE,ALTER TABLE,DROP TABLE 或 DROP DATABASE.
无法使用查询缓存场景 [1]
查询是外部查询的子查询
查询是 存储过程,触发器,事件 的一个步骤
引用了一些返回值不确定的函数 (截止 5.7,详情可参阅 MySQL 官网 )
函数 | 函数 | 函数 | 函数 |
---|---|---|---|
AES_DECRYPT() (as of 5.7.4) | AES_ENCRYPT() (as of 5.7.4) | BENCHMARK() | CONNECTION_ID() |
CONVERT_TZ() | CURDATE() | CURRENT_DATE() | CURRENT_TIME() |
CURRENT_TIMESTAMP() | CURRENT_USER() | CURTIME() | DATABASE() |
带一个参数的 ENCRYPT() | FOUND_ROWS() | GET_LOCK() | IS_FREE_LOCK() |
IS_USED_LOCK() | LAST_INSERT_ID() | LOAD_FILE() | MASTER_POS_WAIT() |
NOW() | PASSWORD() | RAND() | RANDOM_BYTES() |
RELEASE_ALL_LOCKS() | RELEASE_LOCK() | SLEEP() | SYSDATE() |
不带参数的 UNIX_TIMESTAMP() | USER() | UUID() | UUID_SHORT() |
引用自定义函数 (UDFs) 或存储过程
引用自定义变量或本地存储的程序变量
引用 mysql 系统数据库中的表
引用任何分区表
下面方式中的任何一种
事务中使用 "可序列化" 隔离级别的
. SELECT ...IN SHARE MODE
. SELECT ...FOR UPDATE
. SELECT ...INTO OUTFILE
. SELECT ...INTO DUMPFILE
. SELECT * FROM ...WHERE autoincrement_col IS NULL
使用临时表
没有使用表,如 select NOW()
语句有 warning
对任何涉及到的表,操作用户有行级权限
InnoDB 和查询缓存
因为 InnoDB 有自己的 MVCC 机制,所以相比其他存储引擎,InnoDB 和查询缓存的交互要更加复杂.
事务是否可以访问查询缓存取决于当前事务 ID,以及对应的数据表上是否有锁.每个 InnoDB 表的内存数据字典都保存了一个事务 ID 号,如果当前事务 ID 小于该事务 ID,则无法访问查询缓存.
通用查询缓存优化
库表结构的设计,查询语句,应用程序设计都可能影响到查询缓存的效率.
用多个小表代替一个大表对查询缓存有好处.
批量写入时只需要做一次缓存失效.所以相比单条写入效率更好
因为缓存空间太大,在过期操作的时候可能会导致服务器僵死.
无法在数据库或者表级别控制查询缓存,但是可以通过 SQL_CACHE 和 SQL_NO_CACHE 来控制某个 SELECT 语句是否需要进行缓存.
对于写密集型的应用来说,直接禁用查询缓存可能会提高系统的性能.
因为对互斥信号量的竞争,有时直接关闭查询缓存对读密集型的应用也会有好处.
如果不想所有的查询都进入查询缓存,但是又希望某些查询走查询缓存,那么可以将 query_cache_type 设置成 DEMAND,然后在希望缓存的查询中加上 SQL_CACHE.
查询缓存的限制
MySQL 最初设计查询缓存的初衷是
查询缓存的理想情况往往在很大程度上是只读的,其中有许多非常昂贵的查询只能检查数百万行只返回一些. 一个假设的例子可能是一个复杂的查询来构建总是出现在网页表单上的下拉列表的值列表. 在这种情况下,查询缓存可以掩盖由缺失索引引起的性能问题,这对新手用户有帮助.
自从 MySQL 5.6(2013)以来,查询缓存已被默认禁用,因为已知它不能在多核机器上的高吞吐量工作负载上扩展.
假设可扩展性可以提高,查询缓存的限制因素在于,只有那些命中缓存的查询才能看到改进; 不可能提高性能的可预测性. 对于面向用户的系统,降低性能的可变性通常比提高峰值吞吐量更重要:
MySQL8.0 后查询缓存的替代方案
由于查询缓存的诸多限制,故更建议将缓存离应用更近一点,根据具体应用的实际情况来自定义缓存.
下图是被 MySQL 产品经理采纳并最终决定放弃查询缓存的一个实验结果:
从图中可以看出,采用 "Client + 2x ProxySQL" 可以达到 5.2 倍的性能提升(即将缓存移至 Client 端,跟应用绑定).
MySQL 产品经理 Morgan Tocker 在其个人博客上有这样一句话 [2]:
MySQL 8.0 不支持查询缓存,用户升级后将被鼓励使用服务器端查询重写或 ProxySQL 作为中间缓存.
参考文献
《高性能 MySQL》
Official: How the Query Cache Operates
MySQL 8.0: Retiring Support for the Query Cache
Scaling with ProxySQL Query Cache
Mysql 查询缓存研究
来源: http://www.jianshu.com/p/f2d1fb1e7f93