Ponowne zaszczepienie kolumny tożsamości: kiedy jest to konieczne?

Podczas jednej z ostatnich lekcji na uniwersytecie (jestem studentem) wykładowca poprosił nas o opracowanie bazy danych (jeśli ma to znaczenie MySQL Server) i małej aplikacji klienckiej, która wykorzystywałaby bazę danych jako źródło danych.

Jednym z wymagań było to, że kolumna tożsamości (która jest PK w każdej tabeli) musi być sekwencyjna, ponieważ jest to dobra praktyka (jak na słowa wykładowcy). Oznacza to, że po usunięciu wiersza tabeli jego PK musi być ponownie użyte w kolejnych wstawkach. Mam średnią wiedzę na temat RDBMS, PK i kolumn tożsamości. Z tego, co rozumiem, ta kolumna tożsamości to tylko sposób, aby DB automatycznie generował PK podczas wstawiania wierszy i nic więcej. A wartość kolumny tożsamości nie powinna być w żaden sposób powiązana z atrybutami wiersza (o ile nie jest to klucz naturalny).

Ten wymóg (ściśle sekwencyjna kolumna tożsamości) był dla mnie podejrzany. Próbowałem zapytać prowadzącego, co jest nie tak, jeśli tożsamość nie jest sekwencyjna (z lukami spowodowanymi usunięciem), ale otrzymałem bardzo abstrakcyjną odpowiedź, taką jak: „jest wygodny dla użytkowników i przydatny dla administratorów DB, którzy utrzymują bazę danych”. Brak konkretnych przykładów. Argument „wygodny dla użytkowników” brzmi głupio, ponieważ nie ma żadnego znaczenia w domenie biznesowej.

Dlatego jestem ciekawy, czy te powody są prawdziwe? Mogę wymyślić tylko jeden przypadek, w którym wymagane jest zresetowanie kolumny tożsamości - gdy przestrzeń tożsamości jest wyczerpana. Jest to jednak większy problem projektowy, gdy typ kolumny tożsamości został nieprawidłowo wybrany, powiedzmy prosty intzamiast bigintlub uniqueidentifiergdy tabela zawiera miliardy wierszy. Załóżmy, że kolumna tożsamości jest indeksem klastrowym: czy luki w kolumnie tożsamości mogą wpływać na wydajność indeksu? Może istnieją inne rzeczywiste powody automatycznego ponownego inicjowania kolumny tożsamości po każdym usunięciu, o którym nie wiem?

Z góry dziękuję!

Oznacza to, że po usunięciu wiersza tabeli jego PK musi być ponownie użyte w kolejnych wstawkach.

Z jakiego wszechświata jest twój wykładowca?

To jest rażąco nieefektywne. Jeśli spróbujesz to zrobić, zmniejszysz swoje perspektywy wydajności dziesięciokrotnie.

Jeśli potrzebujesz numerów bez przerwy dla celów kontroli, zbuduj je jawnie, nie bezpośrednio z narzędzi bazy danych. I nigdy nie usuwaj wierszy, ale oznacz je jako „usunięte”. Zwiększy to bałagan zapytań, ponieważ będą musiały ignorować takie wiersze.

W MySQL InnoDB wymaga istnienia unikalnego PRIMARY KEYdla każdej tabeli. Ale taki jest zakres wymagań. Klucz może być nawet ciągiem.

Luki są wygodą dla użytkowników i DBA, a nie niedogodnością.

Mogę wymyślić jeden przypadek, w którym bez przerwy byłby wygodny - dzielenie na grupy po 100 rzędów na raz. Istnieje jednak proste obejście tego problemu LIMIT 100,1.

Luki mają zerowy wpływ na wydajność. Obejmuje to indeksy nienumeryczne. I nieunikalne indeksy. I indeksy złożone.

Jasne, możesz zabraknąć identyfikatorów. Wydaje mi się, że widziałem to dwa razy w ciągu prawie 2 dekad używania MySQL. Równie dobrze mogę się martwić, że uderzy mnie asteroida. Jest mało na mojej liście rzeczy, które trzymają mnie na jawie w nocy.

Luk występują z (co najmniej): INSERT IGNORE, IODKU, REPLACE, DELETE, ROLLBACK(wyraźne, lub z powodu awarii), replikacja multi-master (w tym Galera grupy replikacji). Czy naprawdę chcesz wymyślić dla nich obejścia ?!

Zachęcamy do sprawdzania rozsądku wszystkiego, co według wykładowcy jest podejrzane.

Ponowne użycie wartości tożsamości należy ogólnie odradzać. Albo wartość jest wykorzystywana całkowicie wewnętrznie, w którym to przypadku jej rzeczywista wartość jest nieistotna, lub też jest stosowana zewnętrznie, w którym to przypadku ponowne użycie wartości najprawdopodobniej doprowadzi do błędnej identyfikacji.

Weźmy oczywisty przypadek faktury lub numeru zamówienia zakupu, mogą one łatwo pochodzić z kolumny tożsamości i być widoczne z zewnątrz, ale nigdy nie chciałbyś ich ponownie użyć z tego właśnie powodu. Oba odnoszą się do konkretnych transakcji, których nie chciałbyś się pomylić.

Rozwiązanie takich problemów może być dużym problemem, gdy firmy się połączą lub zostaną przejęte. Celowo tworzysz takie problemy? Nie mądrze.

Ponowne użycie wartości PK id ma problemy i ogólnie należy tego unikać.

Po pierwsze, implementacja kolumn auto_increment nie daje gwarancji bycia bez przerwy. W rzeczywistości pojawią się luki, jeśli cofniesz wstawkę w kolumnie automatycznego przyrostu.

Po drugie, identyfikator luki może odnosić się do istniejących danych, które nie zostały usunięte (z powodu brakujących ograniczeń FK). Jeśli przekładają się one na numery członków przekazywane poza systemem, stwarza to potencjalne ryzyko tożsamości biznesowej.

Po trzecie, bigint unsignednie zabraknie identyfikatorów przez dłuższy czas, nawet przy wyjątkowo dużej częstotliwości wstawiania.

Największy problem z lukami napotykają audytorzy, którzy nalegają na jego wadę. W przypadku DBA wiedzą, że istnieją luki i dlaczego.

Nie powtórzę komentarzy wszystkich innych, że ponowne użycie PK jest złym pomysłem, ale zdarzyło mi się, że kolumna tożsamości musi zostać ponownie zaszczepiona.

Korupcja samego indeksu PK.

To prawda, że ​​korzystałem z MS-SQL i wiele, wiele lat temu, ale nadal jest to istotne. Wiele lat temu dla firmy, dla której pracuję, ktoś pomyślał, że dobrym pomysłem byłoby ponowne użycie komputerów jako serwerów w naszych ponad 150 zdalnych lokalizacjach po tym, jak były zbyt stare, aby mogły być używane przez klientów, a następnie wbić je do szafy bez wentylacji. Kiedy nie Ponieważ wszyscy wiemy, że stos śmieciowego 10-letniego komputera w maleńkim pokoju z ponad 120 uruchomionymi bazami danych o znaczeniu krytycznym może przynieść tylko dobre rzeczy. Jak 40% wskaźników niepowodzenia i ja na nowo zastanawiam się nad wyborem kariery. Powielilibyśmy dane z powrotem do siedziby głównej korpusu, ale częściej te awarie powodowałyby złe rzeczy w bazach danych. Jedną z takich rzeczy była baza danych z uszkodzonymi indeksami, które zajmowałyby bazę danych i proces replikacji. Dwa razy w tym świetnym środowisku jedynym rozwiązaniem, które naprawiło replikację, było zresetowanie indeksów, a następnie ponowne ustanowienie replikacji. Zastąpiliśmy serwery później, zanim całkowicie je zrzuciłem.