Oczywiste jest, że istnieje wiele różnych sposobów uzyskiwania takich samych wyników, wydaje się, że Twoim pytaniem jest skuteczny sposób uzyskania ostatnich wyników w każdej grupie w MySQL. Jeśli pracujesz z ogromną ilością danych i zakładasz, że używasz InnoDB nawet z najnowszymi wersjami MySQL (takimi jak 5.7.21 i 8.0.4-rc), może nie być skutecznego sposobu na zrobienie tego.
Czasami musimy to zrobić w przypadku tabel zawierających nawet ponad 60 milionów wierszy.
W tych przykładach wykorzystam dane zawierające tylko około 1,5 miliona wierszy, w których zapytania będą musiały znaleźć wyniki dla wszystkich grup w danych. W naszych rzeczywistych przypadkach często musielibyśmy zwrócić dane z około 2000 grup (co hipotetycznie nie wymagałoby badania bardzo dużej ilości danych).
Użyję następujących tabel:
CREATE TABLE temperature(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
groupID INT UNSIGNED NOT NULL,
recordedTimestamp TIMESTAMP NOT NULL,
recordedValue INT NOT NULL,
INDEX groupIndex(groupID, recordedTimestamp),
PRIMARY KEY (id)
);
CREATE TEMPORARY TABLE selected_group(id INT UNSIGNED NOT NULL, PRIMARY KEY(id));
Tabela temperatur zawiera około 1,5 miliona losowych rekordów i 100 różnych grup. Grupa selected_group jest wypełniona tymi 100 grupami (w naszych przypadkach byłoby to zwykle mniej niż 20% dla wszystkich grup).
Ponieważ dane te są losowe, oznacza to, że wiele wierszy może mieć takie same zarejestrowane znaczniki czasu. Chcemy uzyskać listę wszystkich wybranych grup w kolejności groupID z ostatnim zarejestrowanym znacznikiem czasu dla każdej grupy, a jeśli ta sama grupa ma więcej niż jeden pasujący wiersz, to ostatni pasujący identyfikator tych wierszy.
Gdyby hipotetycznie MySQL miał funkcję last (), która zwracała wartości z ostatniego wiersza w specjalnej klauzuli ORDER BY, moglibyśmy po prostu zrobić:
SELECT
last(t1.id) AS id,
t1.groupID,
last(t1.recordedTimestamp) AS recordedTimestamp,
last(t1.recordedValue) AS recordedValue
FROM selected_group g
INNER JOIN temperature t1 ON t1.groupID = g.id
ORDER BY t1.recordedTimestamp, t1.id
GROUP BY t1.groupID;
który w tym przypadku musiałby zbadać tylko kilka 100 wierszy, ponieważ nie używa żadnej z normalnych funkcji GROUP BY. Wykonałoby się to w 0 sekund, a zatem byłoby bardzo wydajne. Zauważ, że normalnie w MySQL zobaczylibyśmy klauzulę ORDER BY następującą po klauzuli GROUP BY, jednak ta klauzula ORDER BY służy do określenia ORDER dla funkcji last (), gdyby była po GROUP BY, wówczas zamawiałaby GRUPY. Jeśli nie ma klauzuli GROUP BY, ostatnie wartości będą takie same we wszystkich zwróconych wierszach.
Jednak MySQL tego nie ma, więc przyjrzyjmy się różnym pomysłom na to, co ma i udowodnij, że żadne z nich nie jest wydajne.
Przykład 1
SELECT t1.id, t1.groupID, t1.recordedTimestamp, t1.recordedValue
FROM selected_group g
INNER JOIN temperature t1 ON t1.id = (
SELECT t2.id
FROM temperature t2
WHERE t2.groupID = g.id
ORDER BY t2.recordedTimestamp DESC, t2.id DESC
LIMIT 1
);
Przebadano 3 009 254 wierszy i zajęło ~ 0,859 sekund na 5.7.21 i nieco dłużej na 8.0.4-rc
Przykład 2
SELECT t1.id, t1.groupID, t1.recordedTimestamp, t1.recordedValue
FROM temperature t1
INNER JOIN (
SELECT max(t2.id) AS id
FROM temperature t2
INNER JOIN (
SELECT t3.groupID, max(t3.recordedTimestamp) AS recordedTimestamp
FROM selected_group g
INNER JOIN temperature t3 ON t3.groupID = g.id
GROUP BY t3.groupID
) t4 ON t4.groupID = t2.groupID AND t4.recordedTimestamp = t2.recordedTimestamp
GROUP BY t2.groupID
) t5 ON t5.id = t1.id;
Przebadano 15050331 rzędów i zajęło ~ 1,25 sekundy na 5.7.21 i nieco dłużej na 8.0.4-rc
Przykład 3
SELECT t1.id, t1.groupID, t1.recordedTimestamp, t1.recordedValue
FROM temperature t1
WHERE t1.id IN (
SELECT max(t2.id) AS id
FROM temperature t2
INNER JOIN (
SELECT t3.groupID, max(t3.recordedTimestamp) AS recordedTimestamp
FROM selected_group g
INNER JOIN temperature t3 ON t3.groupID = g.id
GROUP BY t3.groupID
) t4 ON t4.groupID = t2.groupID AND t4.recordedTimestamp = t2.recordedTimestamp
GROUP BY t2.groupID
)
ORDER BY t1.groupID;
Przebadano 3 009 685 wierszy i zajęło ~ 1,95 sekundy na 57,21 i nieco dłużej na 8,0.4-rc
Przykład 4
SELECT t1.id, t1.groupID, t1.recordedTimestamp, t1.recordedValue
FROM selected_group g
INNER JOIN temperature t1 ON t1.id = (
SELECT max(t2.id)
FROM temperature t2
WHERE t2.groupID = g.id AND t2.recordedTimestamp = (
SELECT max(t3.recordedTimestamp)
FROM temperature t3
WHERE t3.groupID = g.id
)
);
Przebadano 6 137 810 wierszy i zajęło ~ 2,2 sekundy na 57,21 i nieco dłużej na 8,0.4-rc
Przykład 5
SELECT t1.id, t1.groupID, t1.recordedTimestamp, t1.recordedValue
FROM (
SELECT
t2.id,
t2.groupID,
t2.recordedTimestamp,
t2.recordedValue,
row_number() OVER (
PARTITION BY t2.groupID ORDER BY t2.recordedTimestamp DESC, t2.id DESC
) AS rowNumber
FROM selected_group g
INNER JOIN temperature t2 ON t2.groupID = g.id
) t1 WHERE t1.rowNumber = 1;
To zbadało 6017808 rzędów i zajęło ~ 4,2 sekundy na 8.0.4-rc
Przykład 6
SELECT t1.id, t1.groupID, t1.recordedTimestamp, t1.recordedValue
FROM (
SELECT
last_value(t2.id) OVER w AS id,
t2.groupID,
last_value(t2.recordedTimestamp) OVER w AS recordedTimestamp,
last_value(t2.recordedValue) OVER w AS recordedValue
FROM selected_group g
INNER JOIN temperature t2 ON t2.groupID = g.id
WINDOW w AS (
PARTITION BY t2.groupID
ORDER BY t2.recordedTimestamp, t2.id
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)
) t1
GROUP BY t1.groupID;
To zbadało 6017908 rzędów i zajęło ~ 17,5 sekundy na 8.0.4-rc
Przykład 7
SELECT t1.id, t1.groupID, t1.recordedTimestamp, t1.recordedValue
FROM selected_group g
INNER JOIN temperature t1 ON t1.groupID = g.id
LEFT JOIN temperature t2
ON t2.groupID = g.id
AND (
t2.recordedTimestamp > t1.recordedTimestamp
OR (t2.recordedTimestamp = t1.recordedTimestamp AND t2.id > t1.id)
)
WHERE t2.id IS NULL
ORDER BY t1.groupID;
Ten trwał wiecznie, więc musiałem go zabić.