MariaDB查询日期区间超阈值后索引选择异常，如何优化无需FORCE INDEX？

问题背景 #

• 数据库版本：10.11.4-MariaDB-1:10.11.4+maria~ubu2204
• 核心表：res_ech_test_alex（7,984,176行），包含lab_id（关联仅14个不同值的ent_laboratoire表）、date_prelevement（跨度1年的日期字段），已创建lab_id和date_prelevement单列索引
• 查询语句：

select *
      from res_ech_test_alex res
          ,ent_laboratoire el 
     where res.lab_id = el.id 
       and res.date_prelevement between ( now() - interval 90 DAY )  
                                and ( now() - interval 70 DAY );

• 异常现象：日期区间17天时查询耗时1.7秒，扩大至18天时耗时骤增至16秒；强制使用date_prelevement索引时，18天区间耗时仍为1.7秒，但前端ORM不支持FORCE INDEX语法，需通过优化器参数调整解决。

优化方案 #

1. 刷新并优化统计信息 #

优化器依赖准确的统计信息选择执行计划，先手动更新表统计，确保行数估算精准：

ANALYZE TABLE res_ech_test_alex;

同时可调整InnoDB统计采样页数量，提高统计精度：

SET GLOBAL innodb_stats_sample_pages = 64; -- 默认值为20，增大后统计更准确

注：全局参数需写入my.cnf（或my.ini）配置文件，重启数据库后持久生效。

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

通过修改成本权重，引导优化器优先选择日期索引扫描：

• 提高全表扫描的成本权重，让优化器更倾向于使用索引：

SET GLOBAL optimizer_costs.server_cost.full_scan = 10.0; -- 默认值为1.0，大幅提高全表扫描成本

• 增大范围优化器内存上限，确保优化器能正确计算日期范围扫描的成本：

SET GLOBAL range_optimizer_max_mem_size = 16777216; -- 默认8M，调整为16M避免内存不足导致的估算偏差

3. 创建复合索引（最推荐方案） #

创建(date_prelevement, lab_id)复合索引，该索引能同时覆盖日期过滤条件和表关联条件，让优化器无需纠结执行计划选择：

CREATE INDEX idx_date_lab ON res_ech_test_alex(date_prelevement, lab_id);

复合索引的优势在于，优化器能直接通过索引筛选日期范围，同时获取关联所需的lab_id，避免回表或错误选择lab_id索引（因lab_id基数极低，全表扫描成本估算易偏差）。

4. 调整连接顺序与执行计划评估参数 #

由于ent_laboratoire仅14行，优化器应优先扫描小表再关联大表，可通过以下参数优化：

• 关闭执行计划剪枝，让优化器评估所有可能的执行路径：

SET GLOBAL optimizer_prune_level = 0; -- 默认值1，会剪枝部分执行计划，设置为0后优化器会完整评估

• 提升连接缓存级别，帮助优化器选择更优的连接顺序：

SET GLOBAL optimizer_switch = 'join_cache_level=3'; -- 默认值2，开启更激进的连接缓存策略

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