14.9.6 缓存与统计表维护优化

ProxySQL 的缓存与统计表（stats_*）维护直接影响性能与可观测性。本节给出缓存启用策略、统计表采样与清理方法，并附安装与排错示例。

原理草图：缓存与统计表数据流

1. 安装与基础准备（含管理端连接）#

# 以 Ubuntu 为例安装（需替换为实际仓库）
sudo apt-get update
sudo apt-get install -y proxysql mysql-client

# 连接 ProxySQL 管理端
mysql -u admin -padmin -h 127.0.0.1 -P6032

命令解释：
- -P6032：管理端口（与业务端口 6033 区分）。
- admin/admin：默认管理账号，生产环境需更换。

2. 查询缓存启用与规则示例#

目标：只缓存可重复读的 SELECT 查询，避免写多读少引发抖动。

-- 1) 启用查询缓存并设置 TTL
SET mysql-query_cache_size_MB=256;
SET mysql-query_cache_default_ttl=300;     -- 默认缓存 300 秒
SET mysql-query_cache_max_resultset=1048576; -- 最大 1MB

LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

-- 2) 创建缓存规则（仅缓存特定读查询）
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, cache_ttl, apply)
VALUES
(1000, 1, '^SELECT .* FROM orders WHERE status=1', 120, 1);

LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

命令解释：
- mysql-query_cache_size_MB：缓存总大小。
- mysql-query_cache_default_ttl：未指定规则 TTL 的默认值。
- cache_ttl：规则级 TTL，覆盖默认值。
- match_pattern：正则匹配可缓存 SQL。

预期效果：
- orders 读查询命中缓存，降低后端压力。
- 写入操作不进入缓存，避免数据不一致。

3. 统计表采样与维护（清理/归档）#

推荐：高峰期降低采样，低峰期归档清理。

-- 查看统计表大小与高频查询
SELECT * FROM stats_mysql_query_digest ORDER BY count_star DESC LIMIT 10;

-- 低峰期清理统计表（按需）
TRUNCATE TABLE stats_mysql_query_digest;
TRUNCATE TABLE stats_mysql_query_rules;

-- 归档：导出后清理（示例）
\! mysqldump -u admin -padmin -h127.0.0.1 -P6032 --databases main \
   --tables stats_mysql_query_digest > /tmp/stats_mysql_query_digest.sql

命令解释：
- stats_mysql_query_digest：SQL 统计主表。
- TRUNCATE：快速清理，释放空间。
- mysqldump：归档后清理，便于回溯。

4. 缓存与统计表排错#

常见问题与解决：
1) 缓存命中率低

SELECT * FROM stats_mysql_query_digest WHERE digest_text LIKE 'SELECT%' LIMIT 5;
SELECT * FROM stats_mysql_query_rules;

检查规则是否匹配。
排除含有 NOW()、RAND() 等不稳定函数的查询。

2) 管理端查询慢/超时

SELECT COUNT(*) FROM stats_mysql_query_digest;

若记录过多，建议低峰期 TRUNCATE 或 DELETE 分批清理。

3) 内存占用升高

SELECT variable_name, variable_value FROM global_variables
WHERE variable_name LIKE 'mysql-query_cache%';

适当降低 mysql-query_cache_size_MB 或缩短 TTL。

5. 练习与验证#

练习 1：验证缓存命中

-- 执行两次同样查询
SELECT * FROM orders WHERE status=1 LIMIT 10;

-- 查看命中情况（hits 增长表示命中）
SELECT * FROM stats_mysql_query_digest
WHERE digest_text LIKE 'SELECT%orders%status=1%' LIMIT 1;

练习 2：设置缓存白名单规则

-- 为订单列表设置更短 TTL
UPDATE mysql_query_rules
SET cache_ttl=30
WHERE rule_id=1000;

LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

预期：
- 第二次查询命中缓存；
- 规则 TTL 生效，30 秒后缓存过期。

6. 最佳实践摘要#

仅对白名单查询启用缓存，避免脏读风险。
统计表分场景采样：诊断期提高采样，稳定期降低采样。
统计表定期归档，保持管理端性能稳定。