[MySQL] Generowanie/iteracja sekwencji liczbowej np. od 1 do 10000000

Nieco ponad rok temu podczas pisania jednej z funkcjonalności do robota sieciowego natknąłem się na małą przeszkodę. Moim zadaniem było pobranie brakujących ID graczy z tabeli aby następnie uzupełnić braki. Ponieważ skala przedsięwzięcia była dość spora (miliony rekordów) to niestety zwykły SELECT wszystkich rekordów z danego zakresu odpadał. Głównie ze względu na ograniczenia w postaci CPU/pamięci RAM/innych zasobów. Wpadłem na pomysł aby wygenerować kolejno liczby z danego zakresu, a następnie sprawdzić przy pomocy LEFT JOIN czy dany rekord istnieję. Oczywiście to było elementem założenia gdyż w przypadku kiedy ilość rekordów była mniejsza niż połowa zakresu to bardziej opłacało się zastosować banalny SELECT i następnie porównanie w pętli czy dany ID istnieję i jeżeli nie to uzupełnienie go. Wszystko tak jak już wspomniałem rozbijało się o wiele takich procesów działających niezależnie na różnych zakresach liczbowych, które pożerały zasoby jak diabli. Niestety MySQL nie posiada jakiegoś banalnego mechanizmu ażeby wygenerować sobie sekwencje stąd trzeba kombinować. Doszedłem do takiego wniosku kiedy zapytałem o poradę na dwóch forach dyskusyjnych. Na jednym z nich oczywiście jak to w życiu bywa doszło do "przepychanek", które moim zdaniem wynikały z niezrozumienia problematyki. Całość można przeczytać tutaj. Jeden użytkownik podał rozwiązanie, które w tamtym momencie wydawało mi się optymalne jednak po dalszych grubszych testach niestety wyszło inaczej. Poprawiony prototyp zapytania ze zwiększonym zakresem wygląda tak:
Kod:

SELECT x.id AS missing_account_id
FROM
(
   SELECT (t1 * 1000000 + t2 * 100000 + t3 * 10000 + t4 * 1000 + t5 * 100 + t6 * 10 + t7 + 1) AS id
   FROM
   (SELECT 0 AS t1 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS t1,
   (SELECT 0 AS t2 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS t2,
   (SELECT 0 AS t3 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS t3,
   (SELECT 0 AS t4 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS t4,
   (SELECT 0 AS t5 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS t5,
   (SELECT 0 AS t6 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS t6,
   (SELECT 0 AS t7 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS t7
   ORDER BY id ASC
) x
LEFT JOIN players p on x.id = p.account_id
WHERE p.account_id IS NULL

i wykonuje się około 17 sekund. Niestety takie rozwiązanie troszkę utrudnia manipulację zakresu dlatego zacząłem wtedy kombinować. Efektem był taki prototyp:
Kod:

SELECT @row := @row + 1 AS missing_account_id
FROM
   (SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS t1,
   (SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS t2,
   (SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS t3,
   (SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS t4,
   (SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS t5,
   (SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS t6,
   (SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS t7,
   (SELECT @row := 0) AS t0

który wykonuję się znacznie szybciej, bo zamyka się w ułamku sekundy. Finalnie wygląda tak:
Kod:

SELECT x.id AS missing_account_id
FROM
(
   SELECT @row := @row + 1 AS id
   FROM
   (SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS t1,
   (SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS t2,
   (SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS t3,
   (SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS t4,
   (SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS t5,
   (SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS t6,
   (SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS t7,
   (SELECT @row := ? - 1) AS t0
) x
LEFT JOIN players p ON x.id = p.account_id
WHERE p.account_id IS NULL AND x.id BETWEEN ? AND ?

Komentarze

Dodaj komentarz

Zezwolono używać: BBCode
Zabroniono używać: znaczników HTML

Podpis: (Wymagany)

E-mail: (Wymagany, niepublikowany)

Strona WWW: (Nie wymagana)

Token:

Treść tokena: (Przepisz tylko cyfry!)

Treść: (Wymagana)

Tagi

webmaster webmastering programowanie strony generowanie ciekawostka tworzenie bazy danych db www tutorial porada example artykul rada projektowanie stron internetowych database zapytanie query iteracja zakres range sekwencja