数据库查询缓存

发表于 2017-10-20 | 分类于 mysql

字数统计 853 | 阅读时长 4

起因

起因是近期，集客中的数据又出现了查询极慢的情况。单表1100W数据,在进行count(*)查询的时候,查询性能极差，按照特定条件的查询(已加索引)，查询出月500W数据的时候，大约耗时18s多。
可能解决方案:

存储引擎修改
查询语句优化
离线预查(此方案结合业务场景)
详见此文：[mysql千万级数据count性能优化]

在解决的过程中顺道看了下查询缓存的问题，线上库竟然没有开启查询缓存。特此做了些记录。

查询缓存原理

查询缓存的实现原理

查询缓存的写入与过期

查询缓存相关参数说明

查询缓存参数

mysql> show status like “%Qcache%”;
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| Qcache_free_blocks      | 1639    |
| Qcache_free_memory      | 5579448 |
| Qcache_hits             | 1464051 |
| Qcache_inserts          | 2442792 |
| Qcache_lowmem_prunes    | 1910628 |
| Qcache_not_cached       | 447381  |
| Qcache_queries_in_cache | 8707    |
| Qcache_total_blocks     | 19402   |
+-------------------------+---------+

变量名称	中文	英文	备注
Qcache_free_blocks	查询缓存中空闲内存块数量	The number of free memory blocks in the query cache
Qcache_free_memory	查询缓存中空闲内存大小	The amount of free memory for the query cache.	单位B
Qcache_hits	查询缓存命中数	The number of query cache hits
Qcache_inserts	加入查询缓存的语句数量	The number of queries added to the query cache.
Qcache_lowmem_prunes	由于内存不足删除的查询语句数	The number of queries that were deleted from the query cache because of low memory	这个数字的持续增长说明内存不足或碎片严重
Qcache_not_cached	没有被缓存的查询语句数	The number of noncached queries
Qcache_queries_in_cache	查询缓存中的语句数	The number of queries registered in the query cache
Qcache_total_blocks	查询缓存的块数	The total number of blocks in the query cache

备注：所有上述参数都已经在mysql5.7.20弃用,在8.0删除,使用时，请确认mysql版本

查询缓存配置参数

mysql> show variables like 'query_cache%';
+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| query_cache_limit            | 1048576  |
| query_cache_min_res_unit     | 4096     |
| query_cache_size             | 16777216 |
| query_cache_type             | ON       |
| query_cache_wlock_invalidate | OFF      |
+------------------------------+----------+
5 rows in set (0.00 sec)

变量名称	中文	英文	备注
query_cache_limit	查询结果最大限制，大于这个值的不缓存	Do not cache results that are larger than this number of bytes. The default value is 1MB.	默认1MB
query_cache_min_res_unit	查询缓存中分配的块的最小大小	The minimum size (in bytes) for blocks allocated by the query cache	字节为单位，默认4096B
query_cache_size	用于缓存查询结果的缓存大小	The amount of memory allocated for caching query results.	默认1M
query_cache_type	查询缓存的类型0/OFF：不进行查询缓存；1：缓存除SELECT SQL_NO_CACHE开头的所有可缓存查询结果；2：值缓存以SELECT SQL_CACHE开头的可缓存查询	Set the query cache type	默认OFF,如果选择OFF，并不会清空查询缓存大小，如果需要，需设置query_cache_size=0
query_cache_wlock_invalidate	针对MyISAM引擎表，对表的请求进行限制	Normally, when one client acquires a WRITE lock on a MyISAM table, other clients are not blocked from issuing statements that read from the table if the query results are present in the query cache. Setting this variable to 1 causes acquisition of a WRITE lock for a table to invalidate any queries in the query cache that refer to the table. This forces other clients that attempt to access the table to wait while the lock is in effect	具体解释参见官方说明

备注：所有上述参数都已经在mysql5.7.20弃用,在8.0删除,使用时，请确认mysql版本

参考文件