为何MariaDB扩大日期范围后弃用索引？如何配置优化？

我有一张含3亿行数据的MESSAGE表，SEND_DATE列已建索引（INDEX_SEND_DATE）。

现象对比 #

窄日期范围查询（快速） #

执行以下查询时速度极快：

SELECT  
    *  
FROM  
    MESSAGE  
WHERE  
    SEND_DATE >= '2024-01-01 00:00:00'  
    AND SEND_DATE < '2024-06-01 00:00:00';  

执行时间：0.044秒 🚀
（加上数据获取时间约150秒）

扩大日期范围后查询骤慢 #

仅将日期范围扩大一个月，查询突然大幅变慢：

SELECT  
    *  
FROM  
    MESSAGE  
WHERE  
    SEND_DATE >= '2024-01-01 00:00:00'  
    AND SEND_DATE < '2024-07-01 00:00:00';  

执行时间：466秒 😱
（加上数据获取时间约150秒）

通过执行计划观察到，此时MariaDB不再使用索引，转而执行全表扫描。

强制使用索引恢复快速查询 #

使用FORCE INDEX强制指定索引后，即使是更大的日期范围，查询也能回到快速状态：

SELECT  
    *  
FROM  
    MESSAGE FORCE INDEX (INDEX_SEND_DATE)  
WHERE  
    SEND_DATE >= '2024-01-01 00:00:00'  
    AND SEND_DATE < '2024-07-01 00:00:00'; 

执行表现符合预期 🚀

已尝试的无效操作 #

• 增大eq_range_index_dive_limit——无效果
• 调整read_buffer_size和tmp_table_size——无改善

核心问题 #

如何配置MariaDB，使其在此类查询中始终优先使用索引，即使查询的日期范围更大？不需要在每个查询中手动添加FORCE INDEX。

补充信息 #

• 数据库版本：10.4.12
• 执行ANALYZE FORMAT=JSON得到的执行计划：

窄日期范围执行计划 #

{
  "query_block": {
    "select_id": 1,
    "r_loops": 1,
    "r_total_time_ms": 68531,
    "table": {
      "table_name": "MESSAGE",
      "access_type": "range",
      "possible_keys": ["INDEX_SEND_DATE"],
      "key": "INDEX_SEND_DATE",
      "key_length": "5",
      "used_key_parts": ["SEND_DATE"],
      "r_loops": 1,
      "rows": 24805704,
      "r_rows": 1.38e7,
      "r_total_time_ms": 65458,
      "filtered": 100,
      "r_filtered": 100,
      "index_condition": "MESSAGE.SEND_DATE >= '2024-01-01 00:00:00' and MESSAGE.SEND_DATE < '2024-05-01 00:00:00'"
    }
  }
}

宽日期范围执行计划 #

{
  "query_block": {
    "select_id": 1,
    "r_loops": 1,
    "r_total_time_ms": 632820,
    "table": {
      "table_name": "MESSAGE",
      "access_type": "ALL",
      "possible_keys": ["INDEX_SEND_DATE"],
      "r_loops": 1,
      "rows": 241889492,
      "r_rows": 2.99e8,
      "r_total_time_ms": 582567,
      "filtered": 24.389,
      "r_filtered": 8.9888,
      "attached_condition": "MESSAGE.SEND_DATE >= '2024-01-01 00:00:00' and MESSAGE.SEND_DATE < '2024-08-01 00:00:00'"
    }
  }
}

解决方案建议 #

1. 调整优化器成本参数 #

MariaDB优化器基于成本估算选择执行策略，当它判定全表扫描成本低于索引范围扫描时，会放弃索引。可通过以下参数降低索引扫描的相对成本：

• 关闭干扰索引选择的合并策略：

  SET GLOBAL optimizer_switch='index_merge=off,index_merge_union=off,index_merge_sort_union=off,index_merge_intersection=off';
  

• 降低随机读成本权重（默认值2.0，可尝试设为1.0）：

  SET GLOBAL read_rnd_cost = 1.0;
  

• 微调顺序读成本权重（谨慎调整，避免影响其他查询）：

  SET GLOBAL read_cost = 1.0;
  

2. 更新表统计信息 #

优化器依赖准确的统计信息判断行数和过滤率，执行以下命令更新统计信息：

ANALYZE TABLE MESSAGE;

注：3亿行大表执行此操作耗时较长，建议在业务低峰期进行。

3. 通过视图封装索引提示 #

创建包含FORCE INDEX的视图，业务查询直接调用视图，无需手动添加索引提示：

CREATE VIEW MESSAGE_VIEW AS SELECT * FROM MESSAGE FORCE INDEX (INDEX_SEND_DATE);

4. 重建索引 #

若索引存在碎片或统计信息异常，重建索引可恢复有效性：

-- 先删除旧索引
ALTER TABLE MESSAGE DROP INDEX INDEX_SEND_DATE;
-- 重建索引，使用INPLACE算法减少锁表时间
ALTER TABLE MESSAGE ADD INDEX INDEX_SEND_DATE(SEND_DATE) ALGORITHM=INPLACE;

5. 升级MariaDB版本 #

当前使用的10.4.12版本较旧，优化器对大表范围查询的成本估算存在偏差。升级到10.5及以上版本，优化器逻辑有针对性改进，可能解决索引选择问题。

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