MySQL 8运行数小时后为何选择错误索引？队列表查询异常排查

环境与表结构 #

我们运行MySQL 8.0.32服务器，维护一张作为任务队列使用的task表，存储数百万条记录。该表每日新增与删除数百万条记录，整体规模大致稳定。表结构如下：

create table task (
  id bigint not null auto_increment,
  dueTs bigint not null,
  -- other columns
)

表中dueTs字段记录任务待处理时间戳，且已为该字段创建索引task_ix_dueTs以实现快速查询。

查询逻辑 #

应用每次批量获取约100条记录并行处理，查询时会排除正在处理的记录，SQL语句如下：

SELECT * FROM task WHERE dueTs < UNIX_TIMESTAMP() AND id NOT IN (ids) LIMIT 100

另外，由于Java应用使用Hibernate，当无需要排除的记录时，会向IN列表中添加Long.MIN_VALUE以规避Hibernate无法处理空列表的问题。

异常现象 #

该方案多年运行正常，但近期数据库突然不再使用task_ix_dueTs索引，转而使用主键索引。执行OPTIMIZE TABLE task;后，短时间内会恢复使用正确索引，但约6小时后查询因索引选择错误再次变慢。尝试执行ANALYZE TABLE task;未达到预期效果。虽然可以在查询中强制指定索引，但无法解释该现象的触发原因及为何近期出现。

近期变更 #

近期的操作是从数据库另一张表清理了5亿+条历史删除数据，目前不确定该操作是否会影响task表的索引选择。

已做的排查 #

通过以下SQL查询INFORMATION_SCHEMA.STATISTICS获取主键与dueTs索引的基数：

select INDEX_NAME, COLUMN_NAME, CARDINALITY 
FROM INFORMATION_SCHEMA.STATISTICS 
where TABLE_SCHEMA='myschema' and table_name='task';

结果显示查询变慢时索引基数无变化，具体数值如下：

"INDEX_NAME","COLUMN_NAME","CARDINALITY"
task_ix_dueTs,dueTs,131284
PRIMARY,id,47257372

咨询问题 #

• 除EXPLAIN外，是否有其他方式了解MySQL索引选择的依据？
• 为何运行数小时后索引选择会发生变化？

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