mysql的索引优化与内存使用的关系分析_技术教程

MySQL索引大小直接影响Buffer Pool命中率：索引越宽越多，B+树越高，读页越多，加剧Buffer Pool压力；低频索引、不当前缀索引、非覆盖索引及UUID主键均会降低内存利用效率。

MySQL 的索引大小直接影响 Buffer Pool 命中率

InnoDB 的数据和索引都存在磁盘上，但真正被频繁访问的页会缓存在 innodb_buffer_pool_size 配置的内存区域里。索引越宽（比如用了 VARCHAR(500) 而非 VARCHAR(50)）、越多（冗余索引、未删除的旧索引），单个 B+ 树节点能存的键值就越少，树的高度就越高，一次查询需要读入的页数就越多——这直接抬高了对 Buffer Pool 的压力。

常见错误现象：SHOW ENGINE INNODB STATUS 中看到 Buffer pool hit rate 长期低于 95%，同时 Innodb_buffer_pool_reads 持续上升；information_schema.INNODB_INDEX_STATS 显示某些索引的 avg_frequency 极低（接近 1），说明几乎没人用。

用 SELECT table_name, index_name, sum_number_of_pages FROM information_schema.INNODB_BUFFER_PAGE WHERE index_name IS NOT NULL GROUP BY table_name, index_name ORDER BY sum_number_of_pages DESC LIMIT 10; 查看哪些索引实际占着 Buffer Pool
删掉 information_schema.STATISTICS 里 INDEX_COMMENT 含 UNUSED 或长期无 rows_examined 的索引（配合慢日志或 performance_schema.table_io_waits_summary_by_index_usage）
联合索引字段顺序要按「过滤性高 → 排序/分组 → 覆盖字段」排，避免把低选择性字段（如 status TINYINT）放在最左

前缀索引不是万能解药，可能让内存更紧张

对长文本字段建前缀索引（如 INDEX idx_title (title(100))）确实能缩小索引体积，但容易引发两个隐性开销：一是前缀长度选得太短，导致大量重复前缀值，B+ 树叶子节点里实际要存更多行指针才能定位到目标记录；二是 MySQL 在执行 ORDER BY 或 GROUP BY 时，如果排序字段只是前缀索引的一部分，无法利用索引完成排序，被迫走 filesort，临时表也可能被挤进内存（tmp_table_size / max_heap_table_size）。

使用场景：仅当字段真实值长度远超常用查询前缀（比如 URL 字段平均 200 字符，但 95% 查询只 match 前 64 字符），且该字段极少参与排序/分组时才考虑前缀索引。

用 SELECT COUNT(DISTINCT LEFT(title, 100)) / COUNT(*) AS selectivity FROM articles; 算前缀选择性，低于 0.1 就别用
执行 EXPLAIN FORMAT=JSON 看 using_filesort 或 using_temporary 是否出现，若出现，说明前缀索引没帮上忙反而拖累内存
替代方案优先考虑生成列 + 函数索引（MySQL 8.0.13+）：ALTER TABLE articles ADD COLUMN title_hash CHAR(16) STORED AS (MD5(title)) INDEX;

覆盖索引减少回表，本质是降低 Buffer Pool 的随机读压力

所谓“回表”，就是通过二级索引找到主键后，再根据主键去聚簇索引里捞整行数据。这个过程需要两次 B+ 树查找，且第二次（查聚簇索引）大概率是随机 IO —— 如果 Buffer Pool 不够大，就会反复淘汰热页、加载冷页，命中率暴跌。

性能影响：开启 innodb_adaptive_hash_index=ON 时，高频回表路径可能被自动加速，但会额外占用 Buffer Pool 内存（adaptive hash index 本身也缓存在 Buffer Pool 里）；关掉它则回表完全依赖磁盘随机读，延迟更不可控。

检查是否覆盖：EXPLAIN 结果中 Extra 字段含 Using index 表示走覆盖索引；含 Using index condition 表示用了 ICP，但未必覆盖
添加覆盖字段时别贪多：比如查询 SELECT id, name FROM users WHERE email=?，索引建为 INDEX idx_email_name (email, name) 即可，不必加 id（主键自动包含）
注意 TEXT/BLOB 字段不能作为索引列，但可以用函数索引提取特征：INDEX idx_content_md5 ((MD5(content)))

自增主键与 UUID 主键对 Buffer Pool 的内存分布影响完全不同

InnoDB 聚簇索引按主键物理排序。自增主键写入是追加模式，新记录总落在 B+ 树最右叶子节点，Buffer Pool 里只需缓存“热点页”（最新几个页）；UUID 主键是随机值，每次插入都可能落到任意位置，导致 Buffer Pool 里分散缓存大量非连续页，碎片化严重，有效利用率下降。

错误现象：SHOW STATUS LIKE 'Innodb_buffer_pool_pages_data'; 数值很高，但 Innodb_buffer_pool_read_requests / Innodb_buffer_pool_reads 比值却很低——说明缓存页很多，但真正命中的少。

线上已用 UUID？至少用 UUID_TO_BIN(UUID(), 1) 把时间戳前置，提升局部性
不要为了“分布式唯一”强行用 UUID：可用 REPLACE INTO + 自增 ID + 业务层重试，或引入雪花 ID 服务
监控 Innodb_buffer_pool_pages_misc：如果它持续高于 pages_data 的 10%，说明 Adaptive Hash Index 或锁信息占了太多 Buffer Pool，需调小 innodb_adaptive_hash_index_parts

索引优化不是只盯着 SELECT 快不快，而是看它如何把内存变成“热数据容器”还是“冷页垃圾场”。一个没想清楚的索引，可能比慢查询本身更耗内存。