怎样在MySQL中诊断并消除因二级索引过多引发的Buffer Pool抖动-mysql教程-PHP中文网

Index_length接近或超过Data_length表明索引体积过大，严重污染Buffer Pool；若比值＞0.7，基本可断定二级索引挤占数据页缓存，导致命中率骤降。

怎样在mysql中诊断并消除因二级索引过多引发的buffer pool抖动

查Index_length是否快吃掉Data_length

Buffer Pool抖动常被误认为是内存不够，其实是二级索引页把大量低频数据页塞进了缓存。最直接的信号是Index_length接近甚至超过Data_length——说明索引体积和数据本身差不多大，每读一个索引页，就可能挤掉一个数据页。

执行：

SHOW TABLE STATUS LIKE 'your_table';

重点关注这两列。若Index_length / Data_length > 0.7，基本可以断定索引在污染Buffer Pool。

别只看单表：用information_schema.STATISTICS查全库二级索引列数总和，5列以上宽索引超过3个就要警惕
注意前缀索引陷阱：INDEX (content(255))在VARCHAR(1000)字段上，实际只用前10字符查询，但索引页仍按255字节建，浪费严重
sys.schema_unused_indexes（MySQL 8.0+）能直接标出90天内没被任何EXPLAIN命中的索引，这类可优先删

用EXPLAIN FORMAT=JSON验证key_parts和used_columns错配

宽索引不报错，但会让InnoDB加载整页索引数据，而查询真正用到的只有前几列。这种“加载多、用得少”的行为就是Buffer Pool命中率骤降的根源。

对高频慢查询跑：

EXPLAIN FORMAT=JSON SELECT * FROM t WHERE status = ? AND created_at > ? ORDER BY id;

然后看输出里的key_parts和used_columns字段。

若key_parts显示["status","created_at","user_id","order_id"]，但used_columns只有["status","created_at"]，说明后两列纯属冗余
如果used_columns为空或只含主键，那这个索引大概率是为ORDER BY或GROUP BY建的，但业务根本没走排序逻辑——删掉它
避免WHERE YEAR(created_at) = 2024这类写法：函数导致索引失效，InnoDB被迫全扫聚簇索引，Buffer Pool瞬间被脏页占满

删索引前先看Innodb_buffer_pool_pages_misc是否超10%

Innodb_buffer_pool_pages_misc持续高于10%，往往不是AHI（自适应哈希索引）分裂问题，而是宽索引触发了大量adaptive hash index segment重建——每个重建动作都会占用misc页，且这些页几乎不参与查询，纯属内存黑洞。

kashen-mysql DDLtoExcel

解析 MySQL 8.0 的 DDL 脚本，并将表结构导出为 Excel 格式。适用于用户需将 MySQL 的 `CREATE TABLE` 语句转换为结构化的 Excel 文件场景，导出内容包含字段名、数据类型、长度及精度等信息。支持多张表的批量处理，每张表对应 Excel 中的一个独立工作表。适用于数据库文档编写、数据字典生成及模式分析等任务。

下载

查命令：

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_misc';

再结合SHOW ENGINE INNODB STATUS\G里BUF_POOL段的pages made young和pages not made young比值失衡情况交叉判断。

如果pages not made young占比长期超60%，说明LRU old区里堆满了冷索引页，它们既不热也不被淘汰，卡在中间耗内存
删索引别一刀切：先建新索引INDEX (status, created_at)，等监控确认查询走新索引后，再删旧的(status, created_at, user_id, order_id)
状态字段优先用ENUM或TINYINT，比如status ENUM('pending','done','failed')，索引体积小、比较快、Buffer Pool更干净

覆盖索引要精不要滥，尤其防TEXT/BLOB隐式退化

覆盖索引本意是绕过回表，减少Buffer Pool污染，但加多了反而加重负担。关键是要让索引页真正“够用”，而不是“全字段塞进去”。

例如：SELECT id, name FROM orders WHERE status = ? ORDER BY created_at，理想覆盖索引是INDEX (status, created_at, id, name)。

别把user_id、remark等非SELECT/WHERE/ORDER BY字段加进来——它们不会提升性能，只会增大索引页，降低Buffer Pool利用率
TEXT或BLOB字段不能建在索引里，一旦出现在覆盖索引定义中，MySQL会悄悄退化为回表，你还以为生效了
用performance_schema.table_io_waits_summary_by_table查COUNT_READ异常高的表，再反查该表索引使用率，比凭经验猜更可靠

真正难的不是删索引，而是判断哪一列该留、哪一列该砍——这必须结合EXPLAIN FORMAT=JSON的used_columns、慢查日志里的真实谓词、以及sys.schema_unused_indexes的零查询记录，三者交叉验证。否则删错一个，可能让原本毫秒级的查询变成秒级全表扫描。