Comment optimiser une requête SQL avec plus de 2 millions de lignes-Questions et réponses sur le réseau chinois PHP

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

La sortie json doit être 像这样：

{"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}],…

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;

répondre à tous (2)

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

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.

Comme+0

Ajouter une réponse

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

Tant que la mêmecode, stationIDpaire 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.

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 chaquecode, stationIDpaire 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. :