MYSQL5.7.24 query cache测试
5.7.24的官方mysql文档说明：The query cache is deprecated as of MySQL 5.7.20, and is removed in MySQL 8.0.
生产上建议关闭qc，这个功能官方都不看好，很多场景估计在生产也不会用到；并且开启之后，会影响性能；并且限制条件很多
If a table changes, all cached queries that use the table become invalid and are removed from the cache. This includes queries that use MERGE tables that map to the changed table. A table can be changed by many types of statements, such as INSERT, UPDATE, DELETE, TRUNCATE TABLE, ALTER TABLE, DROP TABLE, or DROP DATABASE.
A query cannot be cached if it uses any of the following functions:
AES_DECRYPT()
AES_ENCRYPT()
BENCHMARK()
CONNECTION_ID()
CONVERT_TZ()
CURDATE()
CURRENT_DATE()
CURRENT_TIME()
CURRENT_TIMESTAMP()
CURRENT_USER()
CURTIME()
DATABASE()
ENCRYPT() with one parameter
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() with no parameters
USER()
UUID()
UUID_SHORT()
mysql> SHOW VARIABLES LIKE ‘have_query_cache’;
+------------------+-------+ | Variable_name | Value | +------------------+-------+ | have_query_cache | YES | +------------------+-------+
The have_query_cache server system variable indicates whether the query cache is available:
When using a standard MySQL binary, this value is always YES, even if query caching is disabled.
mysql> SHOW VARIABLES LIKE ‘have_query_cache’;
+------------------+-------+ | Variable_name | Value | +------------------+-------+ | have_query_cache | YES
在5.7.24版本中 打开query_cache_type这个参数之后，QC生效
mysql> show variables like 'query_cache_%'; +------------------------------+---------+ | Variable_name | Value | +------------------------------+---------+ | query_cache_limit | 1048576 | | query_cache_min_res_unit | 4096 | | query_cache_size | 1048576 | | query_cache_type | ON //此参数需要重启mysqld，在5.7.24版本qc功能是关闭的
通过sysbench模拟并发测试
从如下测试中，可以看出qc命中率极低，1616609次的插入，只有122次被命中；而且从线程信息来看，qc过程会伴随着等待Waiting for query cache lock；说明出现严重的资源竞争，至于发生在什么地方，可能要从源码着手看看
建议：真的需要使用cache，建议使用redis之类的缓存库，作为前置库或者在应用层面来缓存，而不要打算使用db的qc来做快速查询缓存方式，不然死的很难看
mysql> SHOW STATUS LIKE 'Qcache%'; +-------------------------+---------+ | Variable_name | Value | +-------------------------+---------+ | Qcache_free_blocks | 1 | | Qcache_free_memory | 1031832 | | Qcache_hits | 122 | | Qcache_inserts | 1616609 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 1027352 | | Qcache_queries_in_cache | 0 | | Qcache_total_blocks | 1 | +-------------------------+---------+ 8 rows in set (0.00 sec) mysql> show processlist; +----+-------+-----------+-------+---------+------+--------------------------------+-------------------------------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-------+-----------+-------+---------+------+--------------------------------+-------------------------------------------------------------------------------+ | 2 | root | localhost | NULL | Query | 0 | starting | show processlist | | 3 | trsen | localhost | trsen | Query | 0 | statistics | SELECT DISTINCT c FROM sbtest4 WHERE id BETWEEN 64241 AND 64241+99 ORDER BY c | | 4 | trsen | localhost | trsen | Query | 0 | checking query cache for query | SELECT c FROM sbtest2 WHERE id BETWEEN 3565 AND 3565+99 | | 5 | trsen | localhost | trsen | Query | 0 | Sending data | SELECT DISTINCT c FROM sbtest2 WHERE id BETWEEN 7066 AND 7066+99 ORDER BY c | | 6 | trsen | localhost | trsen | Query | 0 | Sending data | SELECT DISTINCT c FROM sbtest4 WHERE id BETWEEN 92252 AND 92252+99 ORDER BY c | | 7 | trsen | localhost | trsen | Query | 0 | Sending to client | SELECT DISTINCT c FROM sbtest7 WHERE id BETWEEN 2681 AND 2681+99 ORDER BY c | | 8 | trsen | localhost | trsen | Query | 0 | Creating sort index | SELECT c FROM sbtest8 WHERE id BETWEEN 75432 AND 75432+99 ORDER BY c | | 9 | trsen | localhost | trsen | Query | 0 | Sending to client | SELECT c FROM sbtest6 WHERE id BETWEEN 94057 AND 94057+99 ORDER BY c | | 10 | trsen | localhost | trsen | Query | 0 | freeing items | SELECT c FROM sbtest1 WHERE id=13224 | | 11 | trsen | localhost | trsen | Query | 0 | Sending data | SELECT DISTINCT c FROM sbtest2 WHERE id BETWEEN 18472 AND 18472+99 ORDER BY c | | 12 | trsen | localhost | trsen | Query | 0 | Sending to client | SELECT c FROM sbtest6 WHERE id BETWEEN 26497 AND 26497+99 ORDER BY c | | 13 | trsen | localhost | trsen | Query | 0 | Sending data | SELECT DISTINCT c FROM sbtest6 WHERE id BETWEEN 59582 AND 59582+99 ORDER BY c | | 14 | trsen | localhost | trsen | Query | 0 | Sending data | SELECT DISTINCT c FROM sbtest9 WHERE id BETWEEN 25069 AND 25069+99 ORDER BY c | | 15 | trsen | localhost | trsen | Query | 0 | Sending to client | SELECT c FROM sbtest10 WHERE id BETWEEN 65172 AND 65172+99 ORDER BY c | | 16 | trsen | localhost | trsen | Query | 0 | Sending to client | SELECT c FROM sbtest3 WHERE id BETWEEN 7585 AND 7585+99 ORDER BY c | | 17 | trsen | localhost | trsen | Query | 0 | starting | COMMIT | | 18 | trsen | localhost | trsen | Query | 0 | Sending data | SELECT DISTINCT c FROM sbtest8 WHERE id BETWEEN 11634 AND 11634+99 ORDER BY c | +----+-------+-----------+-------+---------+------+--------------------------------+-------------------------------------------------------------------------------+ 17 rows in set (0.00 sec) mysql> show processlist; +----+-------+-----------+-------+---------+------+------------------------------+------------------------------------------------------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-------+-----------+-------+---------+------+------------------------------+------------------------------------------------------------------------------------------------------+ | 2 | root | localhost | NULL | Query | 0 | starting | show processlist | | 3 | trsen | localhost | trsen | Query | 0 | Waiting for query cache lock | UPDATE sbtest2 SET c='92666139225-37618265056-14994285440-34146521669-03452300777-95624262219-040401 | | 4 | trsen | localhost | trsen | Query | 0 | Waiting for query cache lock | SELECT DISTINCT c FROM sbtest1 WHERE id BETWEEN 89962 AND 89962+99 ORDER BY c | | 5 | trsen | localhost | trsen | Query | 0 | Waiting for query cache lock | SELECT SUM(K) FROM sbtest8 WHERE id BETWEEN 85347 AND 85347+99 | | 6 | trsen | localhost | trsen | Query | 0 | Waiting for query cache lock | UPDATE sbtest7 SET k=k+1 WHERE id=29441 | | 7 | trsen | localhost | trsen | Query | 0 | Waiting for query cache lock | UPDATE sbtest10 SET k=k+1 WHERE id=88050 | | 8 | trsen | localhost | trsen | Query | 0 | starting | COMMIT | | 9 | trsen | localhost | trsen | Query | 0 | Waiting for query cache lock | SELECT DISTINCT c FROM sbtest3 WHERE id BETWEEN 87306 AND 87306+99 ORDER BY c | | 10 | trsen | localhost | trsen | Query | 0 | Waiting for query cache lock | SELECT DISTINCT c FROM sbtest1 WHERE id BETWEEN 68973 AND 68973+99 ORDER BY c | | 11 | trsen | localhost | trsen | Query | 0 | Waiting for query cache lock | SELECT DISTINCT c FROM sbtest9 WHERE id BETWEEN 25876 AND 25876+99 ORDER BY c | | 12 | trsen | localhost | trsen | Query | 0 | end | UPDATE sbtest10 SET k=k+1 WHERE id=68274 | | 13 | trsen | localhost | trsen | Query | 0 | Sending to client | SELECT c FROM sbtest6 WHERE id BETWEEN 57977 AND 57977+99 ORDER BY c | | 14 | trsen | localhost | trsen | Query | 0 | Waiting for query cache lock | SELECT DISTINCT c FROM sbtest8 WHERE id BETWEEN 50340 AND 50340+99 ORDER BY c | | 15 | trsen | localhost | trsen | Query | 0 | Waiting for query cache lock | SELECT DISTINCT c FROM sbtest5 WHERE id BETWEEN 7558 AND 7558+99 ORDER BY c | | 16 | trsen | localhost | trsen | Query | 0 | Waiting for query cache lock | SELECT DISTINCT c FROM sbtest1 WHERE id BETWEEN 1790 AND 1790+99 ORDER BY c | | 17 | trsen | localhost | trsen | Query | 0 | Waiting for query cache lock | SELECT DISTINCT c FROM sbtest1 WHERE id BETWEEN 95693 AND 95693+99 ORDER BY c | | 18 | trsen | localhost | trsen | Query | 0 | Waiting for query cache lock | SELECT DISTINCT c FROM sbtest5 WHERE id BETWEEN 32327 AND 32327+99 ORDER BY c | +----+-------+-----------+-------+---------+------+------------------------------+------------------------------------------------------------------------------------------------------+