Comment optimiser une requête SQL avec plus de 2 millions de lignes

Question

J'ai une base de données SQL contenant plus de 2 millions de lignes et elle se développe rapidement. Il n'y a pas beaucoup de colonnes, juste code, prix, date et stationID.

Le but est d'obtenir le dernier prix par code et ID de station. La requête fonctionne très bien mais prend plus de 10 secondes.

Existe-t-il un moyen d'optimiser la requête ?

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

        'AVEC CTE COMME
        (

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

        ,ROW_NUMBER() PLUS(
             PARTITION PAR code, ID station
             COMMANDER PAR date DESC
         ) COMME le plus récent

        DE prix
        )
        
        SÉLECTIONNER *
        DE CTE
        OÙ dernier = 1
        '
    );

    $instruction->exécuter();
    $results = $statement->fetchAll(PDO::FETCH_GROUP | PDO::FETCH_ASSOC);

Modifier : La première colonne a un index appelé « id ». Je ne sais pas si cela aide.

La base de données (InnoDB) ressemble à ceci :

id primaire - int
IDstation - int
code - entier
prix-décimal(10,5)
date - dateheure

Modifier 2 :

Les résultats doivent être regroupés par stationID et plusieurs lignes doivent être affichées pour chaque stationID. Une ligne pour chaque code avec la dernière date.Description :

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

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

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

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

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

      code: 3
      prix : 33
      date : 2023-06-21</pré>
<p>La sortie json doit être 像这样：</p>
<pre class="brush:php;toolbar:false;">{"1000001":[{"code":1,"prix":1.661,"date":"2023-06- 06 12:46:32", "dernier": 1}, {" code ": 2, " prix ": 1,867, " date " : " 2023-06-06 12:46:32 ", "dernier": 1}, {" code ": 3," prix ": 1,05, " date ": " 2023-06-06 12:46:32", " dernier ": 1}, {"code":5,"prix":1,818,"date":"06/06/2023 12:46:32","dernier":1},{"code":6, "prix": 1,879, "date": "06/06/2023 12:46:32", "dernier": 1}], "1000002": [{" code ": 1," ;prix":1,65,"date":"03/06/2023 08:53:26","dernier":1}, {"code":2,"prix":1,868," date": "2023-06-03 08:53:26", "dernier": 1}, {" code ": 6, " prix ": 1,889, " date ": " 2023-06 -03 08:53:27", "dernier" : 1}],…

Answer 1

Je suppose que vous avez besoin des index suivants pour que la requête s'exécute correctement (vous ne devez le faire qu'une seule fois dans le cadre de la conception de la base de données).

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

Les deux premières colonnes peuvent être disposées dans n'importe quel ordre.

Answer 2

Tant que la même code, stationID paire ne peut pas avoir deux lignes avec la même date/heure, utiliser les fonctions de fenêtre revient un peu à utiliser une masse pour casser une noix.

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;

Il nécessite l'index suivant :

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

Cette requête devrait prendre moins de 1 ms puisque la table dérivée peut être construite à partir de l'index, puis elle lit uniquement les lignes requises de la table.

Alternativement, si vous savez que chaque code, stationID paire recevra un prix mis à jour dans une période de temps spécifique (1 heure, 1 jour, 1 semaine), vous pouvez réduire considérablement la quantité de travail que la fonction de fenêtre doit effectuer en ajoutant une clause 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;