MariaDB查询速度太慢

ha5z0ras  于 2023-06-22  发布在  其他
关注(0)|答案(1)|浏览(124)

今天我在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;

像这样，它给我的车站名称对应于最便宜的柴油价格在给定的城市分组为每半小时。

mariadb

1条答案

按热度按时间
nvbavucw

nvbavucw1#

BETWEEN  (  SELECT  FROM_UNIXTIME(1684627200) )  AND ...

-->

BETWEEN FROM_UNIXTIME(1684627200) AND ...

即简单地使用表达式;你不需要把它放大。
由于WHERE子句测试来自不同表的列，因此优化器将选择其中一个表（p vs s）作为开始。它可能会偷懒。
由于在WHERE中存在对s.PostCode的测试，因此LEFT JOIN实际上是JOIN。请更改此设置以帮助人们阅读查询。作为一个JOIN打开了从s开始的可能性，而不是被迫从p开始。

FROM  prices AS p
            JOIN  stations AS s  ON s.uuid = p.station_uuid
            WHERE  p.date BETWEEN FROM_UNIXTIME(1684627200)
                              AND FROM_UNIXTIME(1684713599)
              AND  p.diesel > 0.0
              AND  s.post_code = 1

从s开始：

s: (you effectively have this)
   INDEX(post_code,       -- because of =
         uuid)            -- for "covering"
p: (you have this)
   INDEX(station_uuid,    -- =
         date,            -- assuming it is more selective than diesel
         diesel)          -- covering

从p开始：

p: INDEX(date, diesel, station_uuid)  -- covering, but otherwise unexciting
s: the PRIMARY KEY(uuid) will be used effectively.

建议（更改为JOIN后）：第二个p索引
注意事项：

  • 优化器将根据其原始统计数据从s和p开始进行选择。
  • 每个二级索引隐式地包含PRIMARY KEY的副本（因此它可以访问其余行）。因此，(post_code, uuid)与您已经拥有的(post_code)相同。
  • 我没有发现OVER(PARTITION BY...)有什么神奇的性能，所以我担心 that 会是查询中最慢的部分。
  • 我的Groupwise-Max发现它不是最快的，但确实避免了显示重复项，而不相关子查询会遇到重复项。
赞(0）分享  回复(0）举报  2023-06-22
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备2022004421号-2 NULL123 https://www.null123.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com