如何提高 SQL 查询的性能？-解网

问：

我是 SQL 的初学者，我想使用 SQL 从 Oracle 数据库中查询数据。我有一个表空间，记录了许多汽车的位置。每条记录都有一个里程和一个时间。表空间有三列：“SENDTIME”、“MILEAGE”和“PLATENO”。它们分别表示汽车的时间、里程（以时间戳格式）和车牌号。“SENDTIME”列中的值采用日期时间格式。我想知道特定汽车何时超过 30 秒和少于 300 秒没有移动。所以我写了一个sql查询：

WITH gpsinfo_cte AS (
  SELECT plateno, sendtime, longitude, latitude, mileage, createdate,
    FIRST_VALUE(sendtime) OVER (PARTITION BY plateno, mileage ORDER BY sendtime) AS first_sendtime,
    LAST_VALUE(sendtime) OVER (PARTITION BY plateno, mileage ORDER BY sendtime) AS last_sendtime
  FROM GPSINFO 
  WHERE plateno = '京AEW302'
)
SELECT /*+ NO_MERGE(gpsinfo_cte) */ plateno, sendtime, longitude, latitude, mileage, createdate 
FROM gpsinfo_cte
WHERE (last_sendtime - first_sendtime) * 24 * 60 *60 < 300
AND (last_sendtime - first_sendtime) * 24 * 60 *60 > 30;

但是，它在 oracle 数据库中运行得相当慢。根据 Web 搜索结果，我尝试使用 EXPLAIN PLAN 语句为您的查询生成执行计划，并将其存储在名为 PLAN_TABLE 的表中。输出如下：

我仍然不知道如何提高性能。有人可以帮忙吗？非常感谢！

SQL Oracle SQL性能

WITH gpsinfo_cte AS (
  SELECT plateno
  FROM GPSINFO 
  WHERE plateno = '京AEW302'
  GROUP BY plateno
  HAVING (MAX(sendtime) - MIN(sendtime)) * 24 * 60 * 60 < 300
    AND (MAX(sendtime) - MIN(sendtime)) * 24 * 60 * 60 > 30
)
SELECT *
FROM GPSINFO
WHERE plateno IN (SELECT plateno FROM gpsinfo_cte);

如果这可行，您可以在 plateno 和 sendtime 上添加索引，以便进一步优化它。

5赞 Paul W 5/29/2023 #2

这可能才是最重要的：

 FROM GPSINFO 
  WHERE plateno = '京AEW302'

假设您有很多（也许是数千、数百万辆）汽车，并且只要求提供一个车牌号，那么您希望使用索引来仅访问该特定汽车的表行。您的执行计划显示这不会发生：

TABLE ACCESS FULL GPSINFO

只需在 Plateno 列上创建一个索引，就可以解决您的问题：

CREATE INDEX gpsinfo_plateno_idx1 on gpsinfo(plateno) compress 1

至于查询的其余部分，您必须处理逻辑才能获得正确的结果，但只要您只使用一辆汽车，就不太可能遇到明显的性能问题。就逻辑而言，如果我理解正确，您想知道位置记录之间何时有 30-300 秒的间隔，而它们之间没有里程。因此，您需要将一行与相邻行进行比较。为此，请使用（或）：LAGLEAD

SELECT x.*,
       (sendtime - last_sentime) * 86400 seconds_elapsed
  FROM (SELECT x.*,
               LAG(sendtime) OVER (ORDER BY sendtime) last_sendtime,
               LAG(mileage) OVER (ORDER BY sendtime) last_mileage
          FROM gpsinfo x
         WHERE plateno = '京AEW302') x
 WHERE (sendtime - last_sentime) * 86400 BETWEEN 30 AND 300
   AND mileage = last_mileage

注意：如果您只要求一个值，则无需在子句中包含 plateno，这是多余的，因为只有一个。您当然可以用作我在这里显示的逻辑的替代方案，但是里程可能有许多不同的值，并且按这么多值进行内部分组意味着很多微小的（单行）组，这对于内存/温度效率不是很高。然而，正如我所说，索引确实是你唯一严重的问题。platenoPARTITION BYPARTITION BY mileagemileage = last_mileage

如何提高 SQL 查询的性能？

how to improve the performance of my SQL query?

评论

评论