如何优化超过 200 万行的 SQL 查询

Question

<p>我有一个包含超过 200 万行的 Sql 数据库，并且增长速度很快。列不多，只有<code>代码、价格、日期和stationID</code>。</p> <p>目的是通过代码和stationID获取最新价格。 查询效果很好，但需要 10 多秒。</p> <p>有没有办法优化查询？</p> <pre class="brush:php;toolbar:false;">$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);</pre> <p>编辑： 第一列有一个名为“id”的索引。我不知道这是否有帮助。</p> <p>数据库（InnoDB）如下所示：</p> <pre class="brush:php;toolbar:false;">id primary - int stationID - int code - int price - decimal(10,5) date - datetime</pre> <p>编辑2：</p> <p>结果需要按stationID分组，每个stationID需要显示多行。每个带有最新日期的代码一行。像这样：</p> <pre class="brush:php;toolbar:false;">22456: 代码：1 价格：3 日期：2023-06-21 代码：2 价格：2 日期：2023-06-21 代码：3 价格：5 日期：2023-06-21 22457： 代码：1 价格：10 日期：2023-06-21 代码：2 价格：1 日期：2023-06-21 代码：3 价格：33 日期: 2023-06-21</pre> <p>json 输出应该是这样的：</p>

{"1000001":[{"代码":1,"价格":1.661,"日期":"2023-06- 06 12:46:32","最新":1},{"代码":2,"价格":1.867,"日期":"2023-06-06 12:46:32", "最新":1},{"代码":3,"价格":1.05,"日期":"2023-06-06 12:46:32","最新":1}, {"code":5,"price":1.818,"date":"2023-06-06 12:46:32","latest":1},{"code":6, “价格”:1.879,“日期”:“2023-06-06 12:46:32”,“最新”:1}],“1000002”:[{“代码”:1,” ;价格”:1.65,“日期”:“2023-06-03 08:53:26”,“最新”:1},{“代码”:2,“价格”:1.868,”日期”:“2023-06-03 08:53:26”,“最新”:1},{“代码”:6,“价格”:1.889,“日期”:“2023-06 -03 08:53:27","最新":1}],…</pre></p>            

Answer 1

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

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

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

Answer 2

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

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;

它需要以下索引：

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

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

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

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;