如何优化超过 200 万行的 SQL 查询-PHP中文网问答

我有一个包含超过 200 万行的 Sql 数据库，并且增长速度很快。列不多，只有代码、价格、日期和stationID。

目的是通过代码和stationID获取最新价格。查询效果很好，但需要 10 多秒。

有没有办法优化查询？

$statement = $this->pdo->prepare(

        'WITH cte AS 
        (

        SELECT stationID AS ind, code, CAST(price AS DOUBLE ) AS price, date

        ,ROW_NUMBER() OVER(
             PARTITION BY code, stationID
             ORDER BY date DESC
         ) AS latest

        FROM price
        )
        
        SELECT *
        FROM cte
        WHERE latest  = 1
        '
    );

    $statement->execute();
    $results = $statement->fetchAll(PDO::FETCH_GROUP | PDO::FETCH_ASSOC);

编辑：第一列有一个名为“id”的索引。我不知道这是否有帮助。

数据库（InnoDB）如下所示：

id primary - int
stationID - int
code - int
price - decimal(10,5)
date - datetime

编辑2：

结果需要按stationID分组，每个stationID需要显示多行。每个带有最新日期的代码一行。像这样：

22456:
      code:  1
      price: 3
      date:  2023-06-21

      code:  2
      price: 2
      date:  2023-06-21

      code:  3
      price: 5
      date:  2023-06-21

22457:
      code:  1
      price: 10
      date:  2023-06-21

      code:  2
      price: 1
      date:  2023-06-21

      code:  3
      price: 33
      date:  2023-06-21

The json output should be 像这样：

{"1000001":[{"code":1,"price":1.661,"date":"2023-06-06 12:46:32","latest":1},{"code":2,"price":1.867,"date":"2023-06-06 12:46:32","latest":1},{"code":3,"price":1.05,"date":"2023-06-06 12:46:32","latest":1},{"code":5,"price":1.818,"date":"2023-06-06 12:46:32","latest":1},{"code":6,"price":1.879,"date":"2023-06-06 12:46:32","latest":1}],"1000002":[{"code":1,"price":1.65,"date":"2023-06-03 08:53:26","latest":1},{"code":2,"price":1.868,"date":"2023-06-03 08:53:26","latest":1},{"code":6,"price":1.889,"date":"2023-06-03 08:53:27","latest":1}],…

select p.stationID, p.code, p.price, p.date from ( select code, stationID, max(date) as max_date from price group by code, stationID ) max join price p on max.code = p.code and max.stationID = p.stationID and max.max_date = p.date;

with cte as ( select stationID as ind, code, price, date, row_number() over(partition by code, stationID order by date desc) as latest from price where date >= now() - interval 1 week ) select * from cte where latest = 1;

全部回复(2)

P粉1414555122023-09-02 11:48:59 2楼

我想您需要以下索引才能使查询良好执行（作为数据库设计的一部分，您只需执行一次）。

CREATE INDEX IX ON price
  (code, stationID, date DESC, price)

前两列可以按任意顺序排列。

赞 +0

添加回复

P粉2974349092023-09-02 00:29:02 1楼

只要同一 code, stationID 对不能有两行具有相同的日期时间，使用窗口函数就有点像使用大锤敲开坚果。

它需要以下索引：

alter table price add index (code, stationID, date desc);

此查询应该花费不到 1 毫秒的时间，因为可以从索引构建派生表，然后它只从表中读取所需的行。

或者，如果您知道每个 code, stationID 对都会在特定时间段（1 小时、1 天、1 周）内收到更新的价格，那么您可以显着减少工作量窗口函数需要添加一个 where 子句：