200 万行を超える SQL クエリを最適化する方法

Question

200 万行を超える SQL データベースがあり、急速に増加しています。列はそれほど多くなく、コード、価格、日付、および stationID だけです。

目的は、コードとステーションIDから最新の価格を取得することです。 クエリはうまく機能しますが、10 秒以上かかります。

クエリを最適化する方法はありますか?

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

        'WITH CTE AS
        (

        SELECT stationID AS ind、code、CAST(price AS DOUBLE ) AS 価格、日付

        ,ROW_NUMBER() OVER(
             コード、ステーションIDによるパーティション
             日付 DESC で注文
         ) 最新のものとして

        価格から
        )
        
        選択する *
        中部から
        WHERE 最新 = 1
        '
    );

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

編集: 最初の列には「id」というインデックスがあります。これが役立つかどうかはわかりません。

データベース (InnoDB) は次のようになります:

id プライマリ - int
ステーションID - 整数
コード - 整数
価格-10進数(10,5)
日付 - 日時

編集 2:

結果は stationID ごとにグループ化する必要があり、stationID ごとに複数の行を表示する必要があります。コードごとに 1 行に最新の日付を記載します。画像：

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
<p>json 出力は次のようになります。
<pre class="brush:php;toolbar:false;">{"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}、 {"コード":5、"価格":1.818、"日付":"2023-06-06 12:46:32"、"最新":1}、{"コード":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}],…

Answer 1

クエリを適切に実行するには、次のインデックスが必要だと思います (データベース設計の一部として 1 回だけ実行する必要があります)。

リーリー

最初の 2 つの列は任意の順序で配置できます。

Answer 2

同じ日時を持つ同じ code、stationID のペアが 2 行存在することができない限り、ウィンドウ関数を使用することは、大ハンマーを使用してナットを割ることと少し似ています。

リーリー

次のインデックスが必要です:

リーリー

このクエリの所要時間は 1 ミリ秒未満です。これは、派生テーブルがインデックスから構築され、テーブルから必要な行のみが読み取られるためです。

あるいは、各 コード、ステーション ID のペアが特定の期間 (1 時間、1 日、1 週間) 内に更新された価格を受け取ることがわかっていれば、労力を大幅に削減できます。ウィンドウ関数には次のような必要があります。 where 句を追加するには:

リーリー