explain 的 type=all 不一定触发磁盘 io，取决于数据是否在 buffer pool 中；buffer pool hit rate 低于 99% 表明缓存压力大，需结合 handler_read_rnd_next 与 innodb_data_reads 分析读性能瓶颈。
为什么 EXPLAIN 显示 type=ALL 却不一定是全表扫描？
mysql 的 type=ALL 表示“全表扫描”，但实际是否触发大量磁盘 IO，取决于数据是否已在 InnoDB Buffer Pool 中。如果表小、访问频繁，ALL 可能只走内存页，CPU 消耗高（遍历行、判断 WHERE），IO 几乎为零；反之，若 Buffer Pool 不足、数据冷，就会引发大量 read() + lseek() 系统调用，IO 成瓶颈。
实操建议：
- 用
SHOW ENGINE INNODB STATUSG查看Buffer pool hit rate，低于 99% 就说明缓存压力大 - 对比
Handler_read_rnd_next（随机读行数）和Innodb_data_reads（物理读次数），比值远大于 1 表示大量逻辑读未命中缓存 - 避免在
WHERE中对索引列做函数操作（如WHERE YEAR(created_at) = 2024），否则即使有索引也退化为ALL
什么时候该加 Covering Index 而不是普通索引？
Covering Index 能同时解决 IO 和 CPU 争用：它让查询只通过 B+ 树叶子节点完成（无需回表），既减少磁盘随机读（IO↓），又省去聚簇索引二次查找和行解析开销（CPU↓）。但代价是索引体积变大、写入变慢。
适用场景：
- 查询字段固定且数量少（如
select id, status, updated_at FROM orders WHERE user_id = ?） - WHERE 条件字段 + SELECT 字段合起来不超过 16KB（InnoDB 单页上限），否则可能触发页分裂
- 表写入频率不高，或业务可接受
INSERT/UPDATE延迟增加 5–10%
建索引示例：
ALTER TABLE orders ADD INDEX idx_user_cover (user_id, status, updated_at, id);注意：字段顺序必须满足最左前缀原则，且 id 放最后——因为 user_id 是过滤条件，其余是覆盖字段。
innodb_buffer_pool_size 设多大才不浪费内存又不拖慢查询？
这个参数不是越大越好。设太大（如占物理内存 90%）会导致 OS 缺页、swap 频繁，反而放大 IO 延迟；设太小（如
推荐计算方式：
- 查当前活跃数据量：
SELECT FLOOR(SUM(data_length + index_length) / 1024 / 1024) AS mb FROM information_schema.tables WHERE table_schema = 'your_db'; - 再乘以 1.2–1.5 倍（留出 MVCC 版本、自适应哈希等开销）
- 上限不要超过物理内存的 75%，且确保 OS 至少保留 2GB 给文件系统缓存和后台进程
动态调整命令（无需重启）：
SET GLOBAL innodb_buffer_pool_size = 4294967296; -- 4GB调整后观察 Innodb_buffer_pool_wait_free 是否持续 > 0，若是，说明分配过快导致内部碎片，需分批调大（每次 512MB）。
为什么加了索引，ORDER BY 还会触发 using filesort？
MySQL 只有在索引顺序与 ORDER BY 完全一致、且 WHERE 条件能利用该索引最左前缀时，才能避免 filesort。一旦出现类型转换、NULL 值比较、或 ORDER BY 字段不在索引中，优化器就放弃索引排序能力。
典型陷阱：
-
WHERE status IN ('paid','shipped') ORDER BY created_at DESC→ 若索引是(status)，不包含created_at，必触发filesort -
ORDER BY created_at DESC NULLS LAST→ MySQL 8.0.13+ 才支持NULLS LAST语义，旧版本直接忽略，仍按默认规则排，可能无法复用索引 -
JOIN后ORDER BY用的是被驱动表字段 → 优化器通常无法下推排序，只能归并后排序
验证方法：执行 EXPLAIN FORMAT=tree，看输出里是否有 "using_filesort": true 字段。
真正难平衡的从来不是配置数值，而是理解每一行 Handler_read_* 计数背后，到底是磁盘在转，还是 CPU 在空转。多数人调参只盯着 innodb_buffer_pool_size，却漏看了 innodb_random_read_ahead 在 SSD 上反而拖慢随机点查——硬件特性变了，老经验就得重验。