为何ENUM字段索引未生效？添加ENUM条件后查询性能骤降

初始查询（性能优异） #

执行以下查询：

EXPLAIN SELECT *
   FROM transactions
  WHERE retry < 5
  ORDER BY id ASC
  LIMIT 100

EXPLAIN输出： #

id | select_type | table        | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra
1  | SIMPLE      | transactions | NULL       | index | status,retry  | PRIMARY | 8       | NULL | 200  | 50.00    | Using where

该查询已充分优化，仅扫描200行，执行耗时0.004秒。

添加ENUM条件后的查询（性能骤降） #

在WHERE子句中新增status = 'failure'条件（status为ENUM类型）后，查询速度大幅变慢：

EXPLAIN SELECT *
   FROM transactions
  WHERE retry < 5
    AND status = 'failure'    # 新增条件
  ORDER BY id ASC
  LIMIT 100

EXPLAIN输出： #

id | select_type | table        | partitions | type  | possible_keys          | key      | key_len | ref   | rows   | filtered | Extra
1  | SIMPLE      | transactions | NULL       | ref   | status,status_2,retry  | status_2 | 1       | const | 255260 | 50.00    | Using where

仅添加该条件后，查询耗时超过4秒。

已创建的索引 #

当前已为transactions表创建以下索引：

transactions: INDEX(retry, status)
transactions: INDEX(status)
transactions: INDEX(retry)

优化需求 #

如何优化该查询以提升性能？

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