我在MariaDB（v10.6.12）上执行的一个特定查询上观察到一个奇怪的行为，我无法解释。
该表创建为：
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个月VS 1个月。
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 |
+------+-------------+---------+------+---------------------------------+------+---------+------+---------+-------------+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 |
+------+-------------+---------+-------+---------------------------------+---------------------------------+---------+------+--------+-----------------------+第二个在meter_id和datetime上利用了INDEX，第一个没有。执行三个不同的查询，每个月在所需的范围内，是一个快得多，然后查询一次3个月的范围。
什么原因会导致这种情况（特别是在第二个查询上的索引激活）？
1条答案
按热度按时间5fjcxozz1#
有一个普遍的假设，当使用索引的一部分以上（大约20%，但我还没有找到一个确切的引用），它更有效的不使用索引。
你的第二个一个月查询使用了12.5%的行（384057/3061656），假设是线性数据集，3个月将是37.6%，这就是为什么没有使用它。
选项1：FORCE INDEX，则查询变为：
选项2：将
value追加到索引这允许查询"Using index"，并避免了第二次查找以检索值。
将
meter_id,datetime作为主键将具有与此相同的性能影响。相反的meter_id,datetime也是有效的，但要考虑在这些类型的查询中查询最多的是什么，以及什么更具选择性。选项3：添加
datetime,meter_id,value索引这是基于3个月窗口小于~20%的假设
详细信息可以使用analyze format=json或optimizer trace找到。