MySQL 5.7查询新增列结果不一致及返回无效ID问题排查求助

异常产生原因 #

1. 核心诱因是表上的method_profile_instrument_ref_uniq_idx唯一二级索引物理页损坏：该二级索引的叶子节点存储了对应行的主键ID值，损坏后该ID被篡改为无效值-2147476736。
2. 现象匹配逻辑：
   • 不带deleted_timestamp的查询可触发覆盖索引优化，直接从损坏的二级索引读取数据，因此返回错误ID；
   • 加入deleted_timestamp后，该列不在二级索引中，需要拿索引中存储的错误ID回主键聚簇索引查询对应行，自然无匹配结果。
3. 备份后无法复现的原因：逻辑备份工具默认扫描主键聚簇索引读取正确数据，导出内容不含损坏的二级索引信息，新库重建索引后异常自然消失。
4. 仅单条异常的原因：索引损坏通常仅影响单个或少量连续数据页，仅该条数据对应的索引条目出现位翻转/存储异常。

解决方案 #

1. 先执行索引损坏检测验证问题：

CHECK TABLE method_profile;

执行后返回结果中会明确标注索引损坏的相关提示。
2. 修复方案二选一即可：

• 精准修复仅重建损坏的唯一索引：

ALTER TABLE method_profile DROP INDEX method_profile_instrument_ref_uniq_idx;
ALTER TABLE method_profile ADD UNIQUE KEY `method_profile_instrument_ref_uniq_idx` (`screening_profile_id`,`generated_primary_method_id`,`generated_secondary_method_id`,`generated_instrument_id`,`generated_instrument_ref`,`generated_round_id_valid_from`,`generated_round_id_valid_to`,`generated_kit_code_id`,`generated_temperature_id`,`generated_name`,`generated_slope`,`generated_intercept`,`generated_measurement_unit_id`,`deleted`,`generated_deleted_timestamp`);

• 全表重建修复所有潜在索引损坏（推荐，操作更简单，会自动重建所有索引）：

ALTER TABLE method_profile FORCE;

3. 后续预防措施：
   • 升级到MySQL 5.7最新小版本或8.0版本，修复已知的InnoDB索引损坏类bug
   • 每月定期对核心业务表执行CHECK TABLE检测物理损坏
   • 保持innodb_checksum_algorithm参数为默认的crc32，开启InnoDB页校验功能，实例异常重启后自动检测损坏页

内容的提问来源于stack exchange，提问作者Bill Comer