MySQL使用game_id索引时IN查询丢失记录的问题求助

MySQL 8.0.33（Homebrew，MacOS M1，InnoDB）配合Ruby on Rails 7.0.7、mysql2 0.5.5时，出现使用索引的查询返回异常结果的情况：

1. 单值查询可正常获取目标记录

SELECT id, game_id 
FROM team_game_stats 
WHERE game_id=19768 
AND category="passing" 
AND sub_category="attempts" 
AND team_id=3;

结果：

+---------+---------+
| id      | game_id |
+---------+---------+
| 6366235 |   19768 |
+---------+---------+
1 row in set (0.01 sec)

2. 使用IN查询并启用game_id索引时，目标记录丢失

SELECT id, game_id 
from team_game_stats 
WHERE game_id IN (19357, 19375, 19489, 19531, 19595, 19658, 19720, 19768, 19876, 19940, 20047, 20072, 22379) 
AND category="passing" 
AND sub_category="attempts" 
AND team_id=3;

结果仅返回12条记录，缺失game_id=19768的目标记录。

3. 忽略game_id索引后，目标记录正常返回

select * 
from team_game_stats 
IGNORE INDEX (index_team_game_stats_on_game_id) 
where game_id IN (19357, 19375, 19489, 19531, 19595, 19658, 19720, 19768, 19876, 19940, 20047, 20072, 22379) 
AND category="passing" 
AND sub_category="attempts" 
AND team_id=3;

结果返回13条记录，包含目标记录6366235。

表结构信息 #

SHOW CREATE TABLE team_game_stats;

输出：

CREATE TABLE `team_game_stats` (
      `id` int NOT NULL AUTO_INCREMENT,
      `category` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
      `sub_category` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
      `value` decimal(7,3) DEFAULT NULL,
      `box_score_id` int DEFAULT NULL,
      `game_id` int DEFAULT NULL,
      `team_id` int DEFAULT NULL,
      `created_at` datetime NOT NULL,
      `updated_at` datetime NOT NULL,
      PRIMARY KEY (`id`),
      KEY `index_team_game_stats_on_box_score_id` (`box_score_id`),
      KEY `index_team_game_stats_on_game_id` (`game_id`),
      KEY `index_team_game_stats_on_team_id` (`team_id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=6366968 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci 

1. 索引逻辑损坏：InnoDB索引可能因异常关机、磁盘IO错误或数据库崩溃导致逻辑损坏。单值查询可能走主键索引回表获取数据，而IN查询选择game_id索引时，读取了损坏的索引条目，导致过滤掉有效记录。
2. 优化器执行计划异常：MySQL 8.0.33可能存在特定场景下的优化器bug，处理IN查询时，基于game_id索引的过滤逻辑出现错误，遗漏符合条件的记录。
3. 统计信息过时：MySQL依赖表统计信息选择执行计划，如果统计信息未及时更新，优化器可能做出错误的索引选择，导致查询过滤出现偏差。

1. 重建game_id索引：直接删除并重建该索引，修复可能的损坏：

ALTER TABLE team_game_stats DROP INDEX index_team_game_stats_on_game_id;
ALTER TABLE team_game_stats ADD INDEX index_team_game_stats_on_game_id (game_id);

2. 更新表统计信息：强制更新表的统计数据，帮助优化器选择正确的执行计划：

ANALYZE TABLE team_game_stats;

3. 临时规避异常索引：在问题解决前，可在查询中使用IGNORE INDEX (index_team_game_stats_on_game_id)强制不使用该索引，保证查询结果正确。

1. 创建针对性复合索引：针对查询的过滤条件game_id, team_id, category, sub_category创建复合索引，让查询直接通过索引完成过滤，避免单字段索引的回表操作和潜在异常：

CREATE INDEX idx_tgs_game_team_cat_subcat ON team_game_stats (game_id, team_id, category, sub_category);

2. 定期检查表与索引完整性：定期执行CHECK TABLE team_game_stats检查表和索引的完整性，尤其是在数据库异常重启后。
3. 升级MySQL到稳定版本：后续及时升级到MySQL 8.0系列的最新稳定版本，修复已知的优化器或索引相关bug。
4. 监控核心查询执行计划：对业务核心查询，定期用EXPLAIN查看执行计划，确保索引使用符合预期，及时发现异常。

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