<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:
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</pre>
<p>The json output should be 像这样：</p>
<pre class="brush:php;toolbar:false;">{"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}],…</pre></p>
2
793
![ThinkPHP5快速开发企业站点[全程实录]](https://img.php.cn/upload/course/000/000/068/6253d918a3ce7278.png)
收藏
我想您需要以下索引才能使查询良好执行（作为数据库设计的一部分，您只需执行一次）。
前两列可以按任意顺序排列。
只要同一
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;它需要以下索引：
此查询应该花费不到 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;