MariaDB不同时间范围查询的索引行为差异原因排查

问题场景与表结构 #

在MariaDB 10.6.12中执行聚合查询时，出现了索引选择的差异：相同查询逻辑下，1个月时间范围的查询会使用联合索引，而3个月范围的查询却走全表扫描，且分三次查询单月数据的总耗时远低于一次查询3个月数据。

表结构如下：

CREATE TABLE `mytable` 
(
    `meter_id` bigint(20) unsigned NOT NULL,
    `value` double NOT NULL,
    `datetime` datetime NOT NULL,
    KEY `mytable_meter_id_datetime_index` (`meter_id`,`datetime`),
    CONSTRAINT `mytable_meter_id_foreign` 
        FOREIGN KEY (`meter_id`) REFERENCES `meters` (`id`) 
             ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

两种查询的EXPLAIN结果对比 #

3个月时间范围查询的EXPLAIN #

explain select sum(`value`) as aggregate from `mytable` where `meter_id` in (...) and `datetime` >= '2023-06-05 00:00:00' and `datetime` < '2023-09-05 00:00:00';
+------+-------------+---------+------+---------------------------------+------+---------+------+---------+-------------+
| id   | select_type | table   | type | possible_keys                   | key  | key_len | ref  | rows    | Extra       |
+------+-------------+---------+------+---------------------------------+------+---------+------+---------+-------------+
|    1 | SIMPLE      | mytable | ALL  | mytable_meter_id_datetime_index | NULL | NULL    | NULL | 3061656 | Using where |
+------+-------------+---------+------+---------------------------------+------+---------+------+---------+-------------+

1个月时间范围查询的EXPLAIN #

explain select sum(`value`) as aggregate from `mytable` where `meter_id` in (...) and `datetime` >= '2023-06-05 00:00:00' and `datetime` < '2023-07-05 00:00:00';
+------+-------------+---------+-------+---------------------------------+---------------------------------+---------+------+--------+-----------------------+
| id   | select_type | table   | type  | possible_keys                   | key                             | key_len | ref  | rows   | Extra                 |
+------+-------------+---------+-------+---------------------------------+---------------------------------+---------+------+--------+-----------------------+
|    1 | SIMPLE      | mytable | range | mytable_meter_id_datetime_index | mytable_meter_id_datetime_index | 13      | NULL | 384057 | Using index condition |
+------+-------------+---------+-------+---------------------------------+---------------------------------+---------+------+--------+-----------------------+

原因分析 #

1. 优化器的成本选择逻辑 #

MariaDB的查询优化器基于成本估算选择执行计划，核心对比的是IO、CPU等资源开销：

• 1个月范围查询：优化器预估仅需扫描约38万行数据，远低于全表规模。此时(meter_id, datetime)联合索引可先匹配meter_id in (...)的条件，再在每个meter_id分组下筛选datetime范围，加上InnoDB的**索引条件下推（ICP）**特性，能大幅减少回表次数，整体成本远低于全表扫描，因此选择走索引。
• 3个月范围查询：优化器预估需扫描约306万行数据，已接近全表数据量。此时走索引需要先遍历索引树定位数据，再回表读取value字段做聚合，这种“索引遍历+多次回表”的IO开销，反而高于直接全表扫描的顺序IO，因此优化器选择了全表扫描。

2. 分三次查询更快的原因 #

每次单月查询都能高效利用索引，仅扫描小范围数据，IO开销极低；而一次查询3个月走全表扫描时，需要读取大量数据页，聚合计算的内存压力也更大，整体效率自然不如三次小索引查询的总和。

额外建议 #

如果想让3个月范围的查询尝试走索引，可以使用FORCE INDEX强制指定索引：

select sum(`value`) as aggregate from `mytable` FORCE INDEX(mytable_meter_id_datetime_index) where `meter_id` in (...) and `datetime` >= '2023-06-05 00:00:00' and `datetime` < '2023-09-05 00:00:00';

但需实际测试性能——如果扫描行数确实接近全表，强制索引可能反而会增加开销。

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