mysql数据库版本升级后的查询缓存与慢查询优化

mysql 8.0+ 彻底移除查询缓存，启动时若配置 query_cache_type 等参数将报错退出；慢查询日志默认关闭且支持 jsON 格式或写入 mysql.slow_log 表；EXPLaiN 新增 format=TREE 和 cost_info 字段提升执行计划分析精度。

MySQL 8.0+ 已彻底移除查询缓存（query_cache）

MySQL 5.7 及更早版本中启用的 query_cache_type、query_cache_size 等配置，在 MySQL 8.0 开始已被完全删除。启动时若仍保留这些参数，mysqld 将直接报错退出，错误信息类似：

Unknown variable 'query_cache_type=1'

。这不是兼容性警告，而是硬性移除——官方认为在多数 OLTP 场景下，查询缓存的锁争用和失效开销远大于收益。

替代方案不是“开启更高级的缓存”，而是转向更可控的层级：

• 应用层使用 redis 或 memcached 缓存结构化查询结果（例如 select id,name FROM user WHERE status=1 的固定结果集）
• 利用 InnoDB 的 Buffer Pool 缓存数据页（innodb_buffer_pool_size 应设为物理内存的 50%–75%，且必须启用 innodb_buffer_pool_instances 减少争用）
• 对高频只读视图，考虑物化（通过定期写入临时表 + 应用层重定向查询）

慢查询日志（slow_query_log）在 8.0 中默认关闭且格式有变

MySQL 8.0 默认不开启慢查询日志，且日志格式从纯文本变为可被解析的 json 格式（当启用 log_output = 'FILE' 且 slow_query_log_format = 'JSON' 时）。这意味着旧版用 mysqldumpslow 直接分析日志的方式可能失效或漏掉执行计划字段。

正确启用并获取可用慢查数据的方法：

• 确认已设置：slow_query_log = ON、long_query_time = 1.0（注意：支持小数秒，单位是秒，不是毫秒）
• 推荐将日志输出到表而非文件：log_output = 'table'，这样可直接查 mysql.slow_log 表，字段含 sql_text、query_time、lock_time、rows_sent、rows_examined
• 避免用 SET GLOBAL slow_query_log = ON 临时开启——该操作在某些云厂商 RDS 上被禁用，需改配置文件后重启

EXPLAIN 分析必须关注 format=tree 和 cost_info

MySQL 8.0 引入了新的 EXPLAIN FORMAT=TREE 输出，比传统 tabular 更直观展示执行顺序与嵌套关系；同时 EXPLAIN FORMAT=JSON 中的 cost_info 字段提供了估算代价，这对判断是否走索引、是否触发 filesort / temporary 更可靠。

常见误判点：

• type: ALL 不一定等于全表扫描——如果表很小（如
• key: NULL 但 possible_keys 非空，说明有索引可用但未被选中，需结合 rows_examined 和 filtered 判断选择率
• 使用 EXPLAIN ANALYZE（8.0.18+）可获得真实执行耗时与行数，但会真实执行语句，禁止在生产环境对写操作或大结果集使用

升级后最易忽略的性能倒退点：统计信息陈旧与直方图缺失

MySQL 升级不会自动更新表的统计信息（INFORMATION_SCHEMA.STATISTICS 和 INNODB_INDEX_STATS），尤其当原库长期未 ANALYZE，优化器可能沿用过时的基数估算，导致错误选择执行计划（比如该走索引却选了全表）。

必须手动补救：

• 对所有大表执行：

  ANALYZE TABLE orders, users, logs;

• 对区分度高的列（如 status、category_id）创建直方图：

  ANALYZE TABLE products UPDATE HISTOGRAM ON category_id, status WITH 16 BUCKETS;

  （WITH N BUCKETS 建议 16–256，过多反而拖慢优化器）

• 检查 information_schema.COLUMN_STATISTICS 表确认直方图是否生效，注意该表默认不可见，需先设 show_column_statistics = ON

直方图不能替代索引，但它能让优化器在 WHERE status IN ('pending','processing') 这类多值条件中做出更准的选择率预估——这点在升级后常被跳过，却是慢查复现的核心原因之一。