mysql查询优化中缓存与索引的结合应用_网络技术

mysql查询优化中缓存与索引的结合应用

发布时间 - 2026-01-30 00:00:00 点击率：次

MySQL 8.0 已彻底移除查询缓存（query_cache），仅保留 innodb_buffer_pool 缓存数据页和索引页；联合索引遵循“连续等值+最多一个范围”原则；覆盖索引可避免回表但需避开 SELECT * 和大字段陷阱；buffer pool 大小需根据命中率与物理读频率合理配置。

MySQL 查询缓存（query_cache）在 8.0 中已彻底移除，别再配置它

MySQL 5.7 是最后一个支持 query_cache_type 和 query_cache_size 的正式版本；8.0 起这些变量已被删除，强行设置会报错 Unknown system variable 'query_cache_type'。如果你还在查文档配 query_cache，说明你用的是过时资料或降级环境。

真实场景中，靠查询缓存提升性能的思路早已失效：它只对完全相同的 SQL 文本（含空格、大小写、库名）生效，且只要表有任意 DML 操作，整个表相关缓存全失效。高并发更新下，命中率极低，反而增加锁争用。

替代方案是应用层缓存（如 Redis）或代理层缓存（如 ProxySQL 的 query cache）
如果必须用 MySQL 内置缓存机制，唯一可用的是 innodb_buffer_pool —— 它缓存的是数据页和索引页，不是 SQL 结果
检查是否误启用了旧配置：运行 SHOW VARIABLES LIKE 'query_cache%';，8.0 返回空结果即正常

索引不是越多越好，联合索引顺序决定能否走索引

WHERE 条件里写了 a = 1 AND b > 10 AND c = 5，但只有 (a, c, b) 索引，b > 10 就无法做索引范围扫描，因为 c = 5 是等值，但位置在 b 前面，导致 b 之后的字段无法参与范围查找。

联合索引遵循“最左前缀原则”，但更准确的理解是“连续等值 + 最多一个范围”：

索引 (a, b, c) 可用于：a = 1 AND b = 2 AND c > 3（c 是最后一个，可范围）
不可用于：a = 1 AND c = 3（跳过了 b，中断最左连续）
可部分用于：a IN (1,2) AND b = 3（MySQL 5.7+ 支持多值等号后的字段继续走索引）
用 EXPLAIN 看 key_len 值，能反推出实际用了索引的前几列

覆盖索引减少回表，但要注意 SELECT * 和大字段陷阱

当 SELECT 的所有字段都在索引中（比如索引是 (user_id, status, created_at)，而语句是 SELECT user_id, status FROM orders WHERE user_id = 123），InnoDB 就不必回主键聚簇索引取整行，直接从二级索引叶子节点返回结果 —— 这就是覆盖索引。

但两个常见坑容易被忽略：

SELECT * 几乎不可能走覆盖索引，除非你建的索引包含所有列（不现实，且极大拖慢写入）
哪怕只选几个字段，如果其中一个是 TEXT 或 VARCHAR(2000)，InnoDB 可能放弃使用该索引，因为单条索引记录过大，B+ 树效率下降
用 EXPLAIN 看 Extra 列：出现 Using index 表示走了覆盖索引；出现 Using where; Using index 是覆盖索引 + 索引条件下过滤；出现 Using filesort 或 Using temporary 通常意味着优化空间很大

Buffer Pool 大小与脏页刷盘节奏直接影响查询延迟

innodb_buffer_pool_size 不是越大越好，也不是设成物理内存 70% 就万事大吉。它影响三件事：热数据驻留能力、脏页堆积量、刷脏频率。

典型问题场景：

Buff
er Pool 太小 → 频繁物理读 → SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_reads'; 持续上升
Buffer Pool 太大（尤其在小内存机器上）→ 操作系统开始 swap → 查询偶发卡顿几十秒，vmstat 1 可见 si/so 非零
脏页太多 + 刷盘慢 → innodb_io_capacity 和 innodb_io_capacity_max 设太低 → 日志写满触发同步刷脏，查询突然变慢

建议用如下方式粗估初始值：

SELECT
  ROUND((SELECT VARIABLE_VALUE FROM performance_schema.global_variables WHERE VARIABLE_NAME = 'innodb_buffer_pool_size') / 1024 / 1024 / 1024, 2) AS pool_gb,
  ROUND((SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests') / 
        (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads'), 2) AS hit_ratio;

命中率低于 95%，且 Innodb_buffer_pool_reads 每秒 > 10 次，才值得调大 buffer pool。

索引设计要贴着查询写，缓存得看清楚是哪一层的缓存 —— MySQL 8.0 里已经没有“查询缓存”这个东西了，别被老教程带偏。

# mysql # redis # 操作系统 # ai # proxy # red # sql # select # 堆 # using # 并发 # 的是 # 最多 # 越好 # 移除 # 几个 # 如果你 # 太多 # 走了 # 都在 # 还在

相关栏目：【网站优化151355 】【网络推广146373 】【网络技术251813 】【 AI营销90571 】

上一篇：c++中如何使用std::quoted处理带引号字符串_c+

下一篇：如何通过国家税务总局平台查验发票真伪发票查验结果怎么看【说

相关栏目网站优化
网络推广
网络技术
AI营销

最新文章 Sublime怎么一键压缩JS代码 Su sublime如何在搜索中使用正则表达式 Sublime如何设置透明窗口效果 Su mysql如何设计商品表结构_mysql css属性背景图不显示怎么办_通过检查路如何使用Golang实现排序_Golan 农历闰月是怎么回事_为合回归年加一月调整塑造《刺客信条》艾吉奥传奇的编剧离开育碧 1英里等于多少公里 1mile和km的换 css grid布局中行和列是如何定义的 PS批量旋转和翻转图片，快速校正图片方向 C# Swagger UI自定义方法 C OPPO手机九宫格和全键盘怎么切换_OP Go语言如何实现用户登录注册_Golan 1节飞行速度多少公里每小时 1节是多少公纸嫁衣8千子树第五章庙门怎么开启庙门 Laravel 多行数据编辑表单中实现逐明日之后如何提升钓鱼等级明日之后提升钓支付宝怎样查年度账单_支付宝年度账单查看 C# 多线程UI更新Dispatcher

上一篇：c++中如何使用std::quoted处理带引号字符串_c+

下一篇：如何通过国家税务总局平台查验发票真伪发票查验结果怎么看【说