今天我在MariaDB查询中遇到了一个性能问题:我有一个名为stations的表,其中保存了加油站(大约17k个条目),其中包含post_code、uuid、name和其他在本例中未使用的信息。然后还有另一个名为prices的表(约2.1亿个条目),其中一个条目表示单个站点的单个价格变化,该站点具有特定的日期、时间和station_uuid。
首先,我需要在一天的30分钟间隔内检索柴油的最低价格。这个很好用。但后来我需要添加相应的车站的名称的车站的价格变化发生。所以我添加了特定的连接逻辑,但后来想起,当按时间分组时,不能保证使用正确的站名。所以我尝试使用子查询,但这导致了超时错误。
我在价格上使用单列BTREE指数。柴油,prices.datestations.post_code。
以下是我到目前为止尝试的SQL查询:
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');
1.姓名:
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');
1.使用子查询:
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))
编辑以回答前三条评论(23.05.2023):
MariaDB版本:10.11.3-MariaDB-1:10.11.3+maria~ubu2204
“diesel”是柴油的价格,但如果它是e10的变化,那么柴油将为0,以避免使用那些行,我过滤为0值
可能听起来有点无知,但我不知道“带领带”是怎么工作的,所以我还没有意见。聚集键是主键,对吗?如果是,则应使用表定义进行回答。如果没有,那么它听起来更无知,因为你需要向我解释它与主键的区别。
谢谢你的帮助提前。
EXPLAIN的结果如下:
查询1:
+------+-------------+----------+--------+-------------------+---------+---------+------------------------------+--------+---------------------------------------------------------------------+
| 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 |
+------+-------------+----------+--------+-------------------+---------+---------+------------------------------+--------+---------------------------------------------------------------------+
查询二:
+------+-------------+----------+--------+-------------------+---------+---------+------------------------------+--------+---------------------------------------------------------------------+
| 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:explain result
+------+--------------------+----------+--------+-------------------+---------+---------+------------------------------+-----------+------------------------------------+
| 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 |
+------+--------------------+----------+--------+-------------------+---------+---------+------------------------------+-----------+------------------------------------+
我的表格定义:
价格:table pricesindexes prices
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table
|
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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 |
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
桩号:table stationsindexes stations
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table
|
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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 |
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
编辑(09.06.2023):
我成功地优化了它。首先,我完成了标记答案中提到的步骤,然后它进行得更好,但不够快,所以我继续尝试不同的方法,并成功地:
SELECT
stations.name AS "Tankstelle",
date - interval minute(date)%30 minute as time_begin,
date - interval minute(date)%30 - 30 minute 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:59") as time
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 date_format(time, '%Y-%m-%d %H:%i')) as min
ON min.time Between (date - interval minute(date)%30 minute) and (date - interval minute(date)%30 - 30 minute)
WHERE `date` BETWEEN FROM_UNIXTIME(1684627200) AND FROM_UNIXTIME(1684713599)
AND diesel > 0.0
AND post_code = 59929;
像这样,它给我的车站名称对应于最便宜的柴油价格在给定的城市分组为每半小时。
1条答案
按热度按时间nvbavucw1#
-->
即简单地使用表达式;你不需要把它放大。
由于
WHERE
子句测试来自不同表的列,因此优化器将选择其中一个表(p
vss
)作为开始。它可能会偷懒。由于在
WHERE
中存在对s.PostCode
的测试,因此LEFT JOIN
实际上是JOIN
。请更改此设置以帮助人们阅读查询。作为一个JOIN打开了从s
开始的可能性,而不是被迫从p
开始。从s开始:
从p开始:
建议(更改为
JOIN
后):第二个p
索引注意事项:
s
和p
开始进行选择。PRIMARY KEY
的副本(因此它可以访问其余行)。因此,(post_code, uuid)
与您已经拥有的(post_code)
相同。OVER(PARTITION BY...)
有什么神奇的性能,所以我担心 that 会是查询中最慢的部分。