MariaDB不同日期范围查询执行计划异常的原因排查

使用MariaDB存储大量测量数据，常用查询为指定日期范围筛选数据，耗时10-20秒且逐渐变慢，因此尝试调整索引并通过EXPLAIN验证索引有效性。

但EXPLAIN输出结果令人困惑：仅修改查询的日期范围，7月的查询执行计划合理（使用last_update索引），而6月的查询却执行全表扫描，两类查询的执行时间差异明显。

两次查询的EXPLAIN输出 #

查询7月数据的执行计划 #

EXPLAIN SELECT * FROM eurostat_dump WHERE station_name='Izana' AND last_update BETWEEN '2024-07-01' AND '2024-07-30';
+------+-------------+---------------+-------+--------------------------+-------------+---------+------+---------+-------------+
| id   | select_type | table         | type  | possible_keys            | key         | key_len | ref  | rows    | Extra       |
+------+-------------+---------------+-------+--------------------------+-------------+---------+------+---------+-------------+
|    1 | SIMPLE      | eurostat_dump | range | last_update,station_name | last_update | 3075    | NULL | 1972882 | Using where |
+------+-------------+---------------+-------+--------------------------+-------------+---------+------+---------+-------------+

查询6月数据的执行计划 #

MariaDB [slr_stats]> EXPLAIN SELECT * FROM eurostat_dump WHERE station_name='Izana' AND last_update BETWEEN '2024-06-01' AND '2024-06-30';
+------+-------------+---------------+------+--------------------------+------+---------+------+----------+-------------+
| id   | select_type | table         | type | possible_keys            | key  | key_len | ref  | rows     | Extra       |
+------+-------------+---------------+------+--------------------------+------+---------+------+----------+-------------+
|    1 | SIMPLE      | eurostat_dump | ALL  | last_update,station_name | NULL | NULL    | NULL | 16446501 | Using where |
+------+-------------+---------------+------+--------------------------+------+---------+------+----------+-------------+

表结构信息 #

| eurostat_dump | CREATE TABLE `eurostat_dump` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `station_name` text DEFAULT NULL,
  `last_update` text DEFAULT NULL,
  `target_name` text DEFAULT NULL,
  `status_code` text DEFAULT NULL,
  `returns` int(11) DEFAULT NULL,
  `prediction` text DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY `last_update` (`last_update`(768)),
  KEY `station_name` (`station_name`(768))
) ENGINE=InnoDB AUTO_INCREMENT=17848007 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci |

1. 统计信息过时
   MariaDB的查询优化器靠表的统计信息判断用索引还是全表扫描更划算。如果6月数据的统计信息没更新，优化器可能错估使用last_update索引需要扫描的行数，认为全表扫描成本更低。

2. 数据量占比差异
   Izana站点6月的数据量大概率远大于7月。当查询返回的行数占全表比例过高（比如超过20%-30%），优化器会觉得走索引不如全表扫描——因为索引需要先定位数据再回表读取，数据量太大时，回表的开销反而超过全表扫描的开销。从执行计划看，7月预估扫描197万行，全表共1644万行，显然6月该站点的数据占比高很多，所以优化器选择了全表扫描。

3. 文本索引的先天缺陷
   last_update是text类型，你创建的是前缀索引（last_update(768)）。虽然日期字符串的前缀足够区分，但比起DATE/DATETIME类型，文本索引的排序和范围查询效率本来就低，优化器对这类索引的成本估算更容易出错，尤其是数据分布不均的时候。

• 更新统计信息：执行ANALYZE TABLE eurostat_dump;让优化器获取最新的数据分布统计，可能会修正执行计划。
• 修改字段类型：将last_update改为DATE或DATETIME类型，这样可以创建完整高效的范围索引，避免文本前缀索引的局限性，同时优化器能更准确估算查询成本。
• 创建联合索引：针对查询模式（station_name + last_update）创建联合索引KEY idx_station_lastupdate (station_name(255), last_update)，因为你的查询同时过滤站点和日期，联合索引可以直接定位到符合条件的数据，避免回表或全表扫描。注意station_name用合适的前缀长度即可，不需要768（站点名称长度一般不会太长）。
• 检查数据分布：确认Izana站点6月的数据量是否确实远大于7月，如果是，全表扫描可能在某些场景下合理，但通过联合索引仍能提升查询效率。

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