Określenie kolejnych ID, których brakuje., Gdy pole PK ma ustawione AUTOINCREMENT Opcje

[Sephirus]

Witam (IMG:style_emoticons/default/wink.gif)

Mam ciekawy problem (wydajnościowy)

Muszę sprawdzać integralność danych tabeli (Czy ktoś czegoś nie usunął nie pozamieniał itd)...

Tabela ma strukturę:

Kod

ID (PK + AI) | pole1 | pole2 | ....

Jednym z wymogów jest wykrywanie czy w tabeli czasem nie ma "dziur". Co rozumiem przez dziurę:

Jeśli mamy rekordy o id 1,2,3,4,5,6,7,8,9,... to jest wszystko ok. Ale jeśli mamy 1,2,3,5,6,7,8,9,... (brakuje 4) to jest problem - rekord 4 został usunięty.

I teraz o co chodzi: Czy ktoś zna wydajny sposób na określenie tego, że w tej tabeli brakuje właśnie rekordu numer 4. Czyli chodzi mi o dowolny skrypt/funkcje/pomysł (najlepiej w samym SQL), który zwracałby w takim konkretnym przypadku "4".

Zaznaczam że samo wykrycie dziur (że są) nie jest problemem wystarczy pobrać AUTOINCREMENT i policzyć wiersze... i już wiemy czy są dziury czy nie - ale mi chodzi o określenie ID których brakuje...

Oczywiście można pobrać wszystko i sprawdzać po kolei... ale ta tabela będzie spora a sprawdzanie ma się odbywać często...

Bardzo proszę o pomysły i pomoc (IMG:style_emoticons/default/smile.gif)

[nospor]

[SQL] pobierz, plaintext 
SET @zm=0;
SELECT idzm, roz FROM (
SELECT ID-@zm AS roz, (@zm+1) AS idzm, @zm:=ID FROM TABELA ORDER BY ID ASC
) podsel WHERE podsel.roz > 1
[SQL] pobierz, plaintext 

(IMG:style_emoticons/default/smile.gif)

[Sephirus]

Super to jest coś - to jest pomysł (IMG:style_emoticons/default/smile.gif)

A jak z wydajnością tego przy paru mln rekordów?

Wiem że to sama sobie baza zrobi i raczej nie powinno to zbyt długo trwać (IMG:style_emoticons/default/tongue.gif) Ale lepiej zapytać (IMG:style_emoticons/default/smile.gif)

[nospor]

No cóż.... baza musi te wszystkie miliony rekordów przelecieć, nie ma bata. Ile to zajmie? Nie wiem, wygeneruj milion rekordów i sprawdź. Sam chętnie się przekonam (IMG:style_emoticons/default/smile.gif)

czekaj... własnie sprawdziłem to na swojej tabeli. Mam tam ponad 500tys rekordów. Czas trwania: 0.333 sekundy

[Sephirus]

OK dam znać - ale będę mógł dopiero jutro (IMG:style_emoticons/default/wink.gif) Dzięki za pomoc - w sumie jak się da to walnąć nawet bez procedury to będzie kozak (IMG:style_emoticons/default/wink.gif)

[nospor]

Jak pisałem dla pół miliona trwało to bardzo krótko.
Procedura również tu przecież nie jest potrzebna. Bo i po co? Możesz to spokojnie na zwykłym zapytaniu wykonać

[Sephirus]

Hmm też się pokusiłem o to by sprawdzić, co prawda na mniejszej liczbie i znalazłem pewną przeszkodę (IMG:style_emoticons/default/smile.gif)

w tabeli miałem:

2
3
4
5
6
7
8
9
10
13
14
15
....

Czyli bez 1,11,12

SELECT zwrócił:

idzm roz
1 2
11 3

Czyli nie znalazł 12 (IMG:style_emoticons/default/tongue.gif)

Ale w sumie możną ją wydedukować i obliczyć już w PHP na podstawie:

11 - różnica to 3 zatem kolejny rekord to 11+3=14 (należy jeszcze odjąć 2 bo zaczynaliśmy od SET @zm = 0;)

Zatem z każdego zwróconego rekordu bierzemy zakres (od idzm do idzm+(roz-2))

Co da:

idzm roz id
1 2 1
11 3 11,12

I to mi wystarczy w zupełności (IMG:style_emoticons/default/biggrin.gif) (IMG:style_emoticons/default/smile.gif)

Dzięki!!
Jutro napisze jak śmiga na bazie 5mln+ (IMG:style_emoticons/default/wink.gif)

[nospor]

Ale w sumie możną ją wydedukować i obliczyć już w PHP na podstawie

No cóż... wierzyłem w ciebie i wiedziałem, że z tym "niesamowitym problemem" poradzisz już sobie sam (IMG:style_emoticons/default/tongue.gif)

[kiciafu]

Hej,

Ja miałem podobny problem ale w inny sposób go rozwiązałem choć moje rozwiązanie raczej nie nada się przy 5mln wpisów ale przy małych seriach się sprawdza:

1. Sprawdzamy maksymalną wartość id jaka jest w tabeli w której są luki.
2. Tworzymy sobie tymczasową tabelę zawierającą id
3. Wypełniamy tabelę serią od 1 do max (przy 5mln to by trochę mogło zająć - u mnie pętla robiąca 100 000 insertów wykonuje się w 14s na słabym serwerze)
4. Stosujemy takie zapytanie:

[SQL] pobierz, plaintext 
SELECT ID FROM seria WHERE id NOT IN (SELECT TABELA.ID FROM TABELA);
[SQL] pobierz, plaintext 

Ten post edytował kiciafu 12.02.2012, 12:48:16

[nospor]

kiciafu jak sam słusznie zauważyłeś, Twoje rozwiązanie nie nadaje się dla dużych danych.
Również stosowanie go do małych danych jest mało eleganckie. Bo i po co tworzyć dane tymczasowe, skoro nie trzeba (IMG:style_emoticons/default/smile.gif)

@Sephirus i jak testy?

[Sephirus]

Na wstępie przepraszam, że dopiero teraz piszę... ale naprawdę mam "jumanji" w pracy i wyleciało mi w końcu z głowy (IMG:style_emoticons/default/tongue.gif)

Co do twojej metody nospor...

1. Sprawdziliśmy ją porównawczo z innymi metodami znalezionymi gdzieś w sieci... rezultaty... W każdej próbie na różnych silnikach DB... twoja bije resztę będąc conajmniej 2x szybsza (IMG:style_emoticons/default/biggrin.gif)

2. Testy robiliśmy potem na dwóch silnikach MyISAM i InnoDB (z różnymi konfiguracjami indeksów, pól i samych dziur):
- Na MyISAM po odpowiednich zabiegach konfiguracyjnych (by można było zrobić tak by sobie ta metoda działała "w tle") i obciążeniu metoda radziła sobie świetnie (IMG:style_emoticons/default/smile.gif)

> 100mln rekordów (sporo pól i indeksów) - ~ 1,5 minuty (IMG:style_emoticons/default/smile.gif)

- Na InnoDB (te same warunki) było nieco gorzej ale i tak wyśmienicie:

> 100mln rekordów - ~ 4 minuty

Tak więc reasumując (IMG:style_emoticons/default/smile.gif)

Wielkie dzięki - o to chodziło (IMG:style_emoticons/default/biggrin.gif)
Kubuś rządzi! ;P

[nospor]

100mln rekordów 1,5 minuty - nieźle (IMG:style_emoticons/default/smile.gif)
InnoDB zawsze działa wolniej od MyISAM. Swoją drogą zawsze mnie to dziwiło czemu - skoro przelecenie po rekordach powinno mnie więcej tak samo szybkie niezależnie czy MyIsam czy InnoDB. Coś w tym InnoDB musieli skaszanić (IMG:style_emoticons/default/wink.gif)

Mógłbyś pokazać inne rozwiązania, które znaleźliście? Ciekawi mnie, jak inaczej można było to załatwić.

[Sephirus]

Jedno się ostało jeszcze bo reszta poszła do śmieci od razu (IMG:style_emoticons/default/tongue.gif)

To robi po prostu joina na tej samej tablicy i zwraca rekordy gdzie jest NULL w drugiej ;P sprytne ale wolniejsze ok 2x...

[SQL] pobierz, plaintext 
SELECT a.id + 1 AS lost_id FROM tabela a
LEFT JOIN tabela b ON a.id + 1 =b.id
WHERE b.id IS NULL
ORDER BY a.id
[SQL] pobierz, plaintext 

A co do InnoDB to samo w sobie jest wolniejsze jakby porównać z MyISAM dla jednego procesu - zapis jest dużo wolniejszy itd... Wydaje mi się, że to wszystko się wiąże z indeksami nawet jeśli lecimy po PK... :/ Ale cóż tak już jest. My ogólnie wybraliśmy MyISAM jednak z pewnymi usprawnieniami i założeniami (IMG:style_emoticons/default/smile.gif)

[nospor]

Faktycznie sprytne (IMG:style_emoticons/default/smile.gif) No ale czemu wolniejsze to raczej logiczne.

zapis jest dużo wolniejszy itd...

No to z oczywistych względów. Ale właśnie zastanawia mnie prosty odczyt. No teoretycznie nie powinno być różnicy a jednak jest. No nic, trza nam z tym żyć (IMG:style_emoticons/default/wink.gif)