MariaDB查询性能优化求助：关联加油站名称时超时问题

问题背景 #

现有两张业务表：

• stations：约1.7万条记录，存储加油站的post_code、uuid、name等基础信息
• prices：约2.1亿条记录，存储单站单次油价变动的datetime、station_uuid、diesel（柴油价格）等数据

需求：按30分钟时间区间，统计单日指定邮编区域内的柴油最低价，同时获取对应加油站的名称。

遇到的问题：

• 仅统计最低价的查询执行正常，但关联stations表获取名称时，分组逻辑无法保证名称与最低价对应
• 改用子查询关联获取名称时，出现查询超时

已创建的索引：prices.diesel、prices.date、stations.post_code单列BTREE索引。

已尝试的查询方案 #

方案1：仅统计最低价（执行正常） #

SELECT 
  MIN(diesel) AS "Minimal",
  date - interval minute(date)%30 minute AS time
FROM prices 
LEFT JOIN stations
  ON prices.station_uuid = stations.uuid
WHERE `date` BETWEEN (SELECT FROM_UNIXTIME(1684627200)) AND (SELECT FROM_UNIXTIME(1684713599)) 
    AND diesel > 0.0 
    AND post_code = 59929
GROUP BY date_format(time, '%Y-%m-%d %H:%i');

方案2：关联获取名称（分组结果异常） #

SELECT 
  MIN(diesel) AS "Minimal",
  name AS "Tankstelle",
  date - interval minute(date)%30 minute AS time
FROM prices 
LEFT JOIN stations
  ON prices.station_uuid = stations.uuid
WHERE `date` BETWEEN (SELECT FROM_UNIXTIME(1684627200)) AND (SELECT FROM_UNIXTIME(1684713599)) 
    AND diesel > 0.0 
    AND post_code = 33106
GROUP BY date_format(time, '%Y-%m-%d %H:%i');

问题：分组仅按时间区间，但name未加入分组逻辑，导致返回的名称无法对应该区间的最低价加油站。

方案3：子查询匹配最低价（超时） #

SELECT 
  stations.name AS "Tankstelle",
  date - interval minute(date)%30 minute as time
FROM prices 
LEFT JOIN stations
  ON prices.station_uuid = stations.uuid
WHERE `date` BETWEEN (SELECT FROM_UNIXTIME(1684627200)) AND (SELECT FROM_UNIXTIME(1684713599)) 
    AND diesel > 0.0 
    AND post_code = 59929
    AND diesel = (
      SELECT MIN(diesel) as Min 
      FROM prices p
      WHERE p.date BETWEEN (prices.date - interval minute(prices.date)%30 minute) AND (prices.date - interval ((minute(prices.date)%30)+30) minute))

问题：子查询为依赖子查询，会对主查询的每一行执行一次全表扫描（prices表2亿+数据），导致查询超时。

补充信息 #

数据库版本 #

10.11.3-MariaDB-1:10.11.3+maria~ubu2204

字段说明 #

diesel字段存储柴油价格，若记录为e10价格变动则diesel值为0，因此需通过diesel > 0过滤有效柴油价格记录。

EXPLAIN执行计划 #

查询1、2的执行计划

+------+-------------+----------+--------+-------------------+---------+---------+------------------------------+--------+---------------------------------------------------------------------+
| id   | select_type | table    | type   | possible_keys     | key     | key_len | ref                          | rows   | Extra                                                               |
+------+-------------+----------+--------+-------------------+---------+---------+------------------------------+--------+---------------------------------------------------------------------+
|    1 | SIMPLE      | prices   | range  | date,diesel       | date    | 5       | NULL                         | 695600 | Using index condition; Using where; Using temporary; Using filesort |
|    1 | SIMPLE      | stations | eq_ref | PRIMARY,post_code | PRIMARY | 16      | tankonix.prices.station_uuid | 1      | Using where                                                         |
+------+-------------+----------+--------+-------------------+---------+---------+------------------------------+--------+---------------------------------------------------------------------+

查询3的执行计划

+------+--------------------+----------+--------+-------------------+---------+---------+------------------------------+-----------+------------------------------------+
| id   | select_type        | table    | type   | possible_keys     | key     | key_len | ref                          | rows      | Extra                              |
+------+--------------------+----------+--------+-------------------+---------+---------+------------------------------+-----------+------------------------------------+
|    1 | PRIMARY            | prices   | range  | date,diesel       | date    | 5       | NULL                         | 695600    | Using index condition; Using where |
|    1 | PRIMARY            | stations | eq_ref | PRIMARY,post_code | PRIMARY | 16      | tankonix.prices.station_uuid | 1         | Using where                        |
|    4 | DEPENDENT SUBQUERY | p        | ALL    | date              | NULL    | NULL    | NULL                         | 207323157 | Using where                        |
+------+--------------------+----------+--------+-------------------+---------+---------+------------------------------+-----------+------------------------------------+

表结构 #

prices表

CREATE TABLE `prices` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `date` datetime NOT NULL,
  `station_uuid` text NOT NULL,
  `diesel` float NOT NULL,
  `e10` float NOT NULL,
  `e5` float NOT NULL,
  `dieselchange` tinyint(4) NOT NULL,
  `e5change` tinyint(4) NOT NULL,
  `e10change` tinyint(4) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `date` (`date`) USING HASH,
  KEY `e5` (`e5`),
  KEY `e10` (`e10`),
  KEY `diesel` (`diesel`)
) ENGINE=InnoDB AUTO_INCREMENT=381367318 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

stations表

CREATE TABLE `stations` (
  `uuid` uuid NOT NULL,
  `name` varchar(127) NOT NULL,
  `brand` varchar(127) DEFAULT NULL,
  `street` varchar(127) NOT NULL,
  `house_number` varchar(7) NOT NULL DEFAULT '',
  `post_code` varchar(5) NOT NULL,
  `city` varchar(31) NOT NULL,
  `latitude` double NOT NULL,
  `longitude` double NOT NULL,
  `openingtimes_json` longtext CHARACTER SET utf8mb4 COLLATE=utf8mb4_bin NOT NULL CHECK (json_valid(`openingtimes_json`)),
  `first_active` date NOT NULL,
  PRIMARY KEY (`uuid`),
  KEY `post_code` (`post_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

优化后的解决方案 #

通过先预统计各30分钟区间的最低价及对应时间区间，再关联获取对应加油站信息的方式，避免依赖子查询的全表扫描，同时保证名称与最低价的对应关系：

SELECT 
  stations.name AS "Tankstelle",
  date - interval minute(date)%30 minute as time_begin,
  date - interval minute(date)%30 minute + interval 29 minute 59 second as time_end
FROM stations 
JOIN prices
  ON prices.station_uuid = stations.uuid
JOIN (
  SELECT 
    MIN(diesel) as price,
    date_format(date - interval minute(date)%30 minute, "%Y-%m-%d %H:%i:00") as time_interval
  FROM prices p
  JOIN stations
    ON stations.uuid = p.station_uuid
  WHERE p.date BETWEEN FROM_UNIXTIME(1684627200) AND FROM_UNIXTIME(1684713599)
    AND diesel > 0
    AND stations.post_code = 59929
  GROUP BY time_interval
) as min_prices
  ON min_prices.price = prices.diesel
  AND prices.date BETWEEN min_prices.time_interval AND min_prices.time_interval + interval 29 minute 59 second
WHERE `date` BETWEEN FROM_UNIXTIME(1684627200) AND FROM_UNIXTIME(1684713599)
    AND diesel > 0.0 
    AND post_code = 59929;

说明：优化后的查询先通过子查询min_prices统计每个30分钟区间的最低价，再通过价格匹配+时间区间匹配关联到对应的加油站记录，避免了依赖子查询的重复扫描，同时保证了名称与最低价的对应关系。

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