Wydajność UUID w MySQL?

Rozważamy użycie wartości UUID jako kluczy podstawowych dla naszej bazy danych MySQL. Wstawiane dane są generowane z dziesiątek, setek, a nawet tysięcy zdalnych komputerów i są wstawiane z szybkością 100-40 000 wstawień na sekundę, a my nigdy nie wykonamy żadnych aktualizacji.

Sama baza danych zazwyczaj osiąga około 50 milionów rekordów, zanim zaczniemy wybierać dane, więc nie jest to ogromna baza danych, ale też nie mała. Planujemy również działać na InnoDB, chociaż jesteśmy otwarci na zmianę tego, jeśli istnieje lepszy silnik do tego, co robimy.

Byliśmy gotowi do użycia identyfikatora UUID Java Type 4, ale podczas testów zauważyliśmy dziwne zachowanie. Po pierwsze, przechowujemy jako varchar (36) i teraz zdaję sobie sprawę, że lepiej byłoby użyć binarnego (16) - chociaż nie jestem pewien, o ile lepiej.

Większe pytanie brzmi: jak bardzo te losowe dane psują indeks, skoro mamy 50 milionów rekordów? Czy byłoby lepiej, gdybyśmy na przykład użyli identyfikatora UUID typu 1, w którym skrajne lewe bity miały znacznik czasu? A może powinniśmy całkowicie porzucić UUID i rozważyć klucze podstawowe auto_increment?

Szukam ogólnych przemyśleń / wskazówek na temat wydajności różnych typów UUID, gdy są one przechowywane jako indeks / klucz podstawowy w MySQL. Dzięki!

UUID to uniwersalnie unikalny identyfikator. To uniwersalna część, którą powinieneś rozważyć tutaj.

Czy naprawdę potrzebujesz, aby identyfikatory były uniwersalne i niepowtarzalne? Jeśli tak, to identyfikatory UUID mogą być jedynym wyborem.

Chciałbym zdecydowanie sugerują, że jeśli zrobić użytku UUID, można przechowywać je jako liczby, a nie jako ciąg znaków. Jeśli masz ponad 50 milionów rekordów, oszczędność miejsca na dysku poprawi Twoją wydajność (chociaż nie mogę powiedzieć, o ile).

Jeśli twoje identyfikatory nie muszą być unikalne uniwersalnie, to nie sądzę, że możesz zrobić o wiele lepiej niż po prostu używając auto_increment, co gwarantuje, że identyfikatory będą unikalne w tabeli (ponieważ wartość będzie rosła za każdym razem)

W mojej pracy używamy UUID jako PK. Z doświadczenia mogę ci powiedzieć, że NIE UŻYWAJ ICH jako PK (nawiasem mówiąc, SQL Server).

To jedna z tych rzeczy, które jeśli masz mniej niż 1000 nagrań, to jest ok, ale kiedy masz miliony, to najgorsza rzecz, jaką możesz zrobić. Czemu? Ponieważ UUID nie są sekwencyjne, więc za każdym razem, gdy wstawiany jest nowy rekord, MSSQL musi spojrzeć na odpowiednią stronę, aby wstawić rekord, a następnie wstawić rekord. Naprawdę brzydką konsekwencją tego jest to, że strony kończą się w różnych rozmiarach i kończą na fragmentacji, więc teraz musimy przeprowadzać okresową de-fragmentację.

Gdy użyjesz autoinkrementacji, MSSQL zawsze przejdzie do ostatniej strony, a skończysz z równymi rozmiarami stron (w teorii), więc wydajność wybierania tych rekordów jest znacznie lepsza (również dlatego, że INSERT nie będą blokować tabeli / strony dla Tak długo).

Jednak dużą zaletą używania UUID jako PK jest to, że jeśli mamy klastry DB, nie będzie konfliktów podczas łączenia.

Poleciłbym następujący model: 1. PK INT Identity 2. Dodatkowa kolumna generowana automatycznie jako UUID.

W ten sposób proces łączenia jest możliwy (UUID byłby twoim PRAWDZIWYM kluczem, podczas gdy PK byłby czymś tymczasowym, co zapewnia dobrą wydajność).

UWAGA: Najlepszym rozwiązaniem jest użycie NEWSEQUENTIALID (jak mówiłem w komentarzach), ale w przypadku starszej aplikacji, która ma niewiele czasu na refaktoryzację (i co gorsza, nie kontroluje wszystkich wstawek), nie jest to możliwe. Ale rzeczywiście od 2017 roku powiedziałbym, że najlepszym rozwiązaniem jest tutaj NEWSEQUENTIALID lub Guid.Comb z NHibernate.

Mam nadzieję że to pomoże

Należy wziąć pod uwagę fakt, że Autoinkrementy są generowane pojedynczo i nie można ich rozwiązać za pomocą rozwiązania równoległego. Walka o używanie UUID ostatecznie sprowadza się do tego, co chcesz osiągnąć, w porównaniu z tym, co potencjalnie poświęcasz.

Krótko o wydajności :

Identyfikator UUID, taki jak powyższy, ma 36 znaków, w tym myślniki. Jeśli zapiszesz ten VARCHAR (36), dramatycznie zmniejszysz wydajność porównania. To jest twój klucz podstawowy, nie chcesz, aby był powolny.

Na poziomie bitowym UUID ma 128 bitów, co oznacza, że ​​zmieści się w 16 bajtach, pamiętaj, że nie jest to bardzo czytelne dla człowieka, ale utrzyma niski poziom pamięci i jest tylko 4 razy większy niż 32-bitowy int, czyli 2 razy większe niż 64-bitowe int. Użyję VARBINARY (16) Teoretycznie może to działać bez dużego narzutu.

Polecam przeczytanie następujących dwóch postów:

• Bezczynne myśli Briana „Krow” Akera - mity, identyfikator GUID kontra autoinkrementacja
• Do UUID czy nie do UUID?

Sądzę, że między tymi dwoma odpowiadają na twoje pytanie.

Staram się unikać UUID po prostu dlatego, że przechowywanie go i używanie go jako klucza podstawowego jest trudne, ale są też zalety. Głównym jest to, że są WYJĄTKOWE.

Zwykle rozwiązuję problem i unikam UUID, używając podwójnych pól kluczy.

COLLECTOR = UNIKALNY PRZYPISANY DO MASZYNY

ID = REKORD ZBIERANY PRZEZ KOLEKCJĘ (pole auto_inc)

To daje mi dwie rzeczy. Szybkość pól automatycznego włączania i niepowtarzalność danych przechowywanych w centralnej lokalizacji po ich zebraniu i zgrupowaniu. Wiem też, przeglądając dane, w których zostały zebrane, co często jest dość istotne dla moich potrzeb.

Widziałem wiele przypadków, gdy miałem do czynienia z innymi zestawami danych dla klientów, w których zdecydowali się użyć UUID, ale nadal mam pole, w którym zebrano dane, co naprawdę jest stratą czasu. Po prostu użycie dwóch (lub więcej w razie potrzeby) pól jako klucza naprawdę pomaga.

Właśnie widziałem zbyt wiele hitów wydajnościowych przy użyciu UUID. Czują się jak oszust ...

Zamiast centralnie generować unikalne klucze dla każdego wstawienia, co powiesz na przydzielanie bloków kluczy do poszczególnych serwerów? Kiedy skończą im się klucze, mogą poprosić o nowy blok. Następnie rozwiązujesz problem narzutu, podłączając każdą wkładkę.

Keyserver utrzymuje następny dostępny identyfikator

• Serwer 1 żąda blokady identyfikatora.
• Serwer kluczy zwraca (1,1000)
  Serwer 1 może wstawić 1000 rekordów, dopóki nie zażąda nowego bloku
• Serwer 2 żąda bloku indeksu.
• Zwroty serwera kluczy (1001,2000)
• itp...

Mógłbyś wymyślić bardziej wyrafinowaną wersję, w której serwer mógłby zażądać liczby potrzebnych kluczy lub zwrócić nieużywane bloki do serwera kluczy, który wtedy oczywiście musiałby utrzymywać mapę używanych / nieużywanych bloków.

Przypisałbym każdemu serwerowi numeryczny identyfikator w sposób transakcyjny. Następnie każdy wstawiony rekord będzie automatycznie zwiększał swój własny licznik. Połączenie ServerID i RecordID będzie unikalne. Pole ServerID może być indeksowane, a przyszłe wybieranie wydajności na podstawie ServerID (w razie potrzeby) może być znacznie lepsze.

Krótka odpowiedź jest taka, że ​​wiele baz danych ma problemy z wydajnością (w szczególności z dużymi wolumenami INSERT) z powodu konfliktu między ich metodą indeksowania a celową entropią UUID w bitach wyższego rzędu. Istnieje kilka typowych hacków:

• wybierz inny typ indeksu (np. nieklastrowany w MSSQL), któremu to nie przeszkadza
• zmontuj dane, aby przenieść entropię do bitów niższego rzędu (np. zmiana kolejności bajtów UUID V1 w MySQL)
• uczyń identyfikator UUID kluczem pomocniczym z automatycznym zwiększaniem int klucz podstawowy

... ale to wszystko są hacki - i to prawdopodobnie kruche.

Najlepszą odpowiedzią, ale niestety najwolniejszą, jest zażądanie od dostawcy ulepszenia produktu, aby mógł on traktować UUID jako klucze podstawowe, tak jak każdy inny typ. Nie powinni zmuszać Cię do rzucania własnego, na wpół upieczonego hacka, aby nadrobić swoje niepowodzenie w rozwiązaniu tego, co stało się powszechnym przypadkiem użycia i będzie się nadal rozwijać.

A co z ręcznie wykonanym UID? Nadaj każdemu z tysięcy serwerów identyfikator i uczyń klucz podstawowy kluczem combo autoincrement, MachineID ???

Ponieważ klucz podstawowy jest generowany zdecentralizowany, i tak nie masz możliwości korzystania z auto_increment.

Jeśli nie musisz ukrywać tożsamości komputerów zdalnych, użyj identyfikatorów UUID typu 1 zamiast identyfikatorów UUID. Są łatwiejsze do wygenerowania i przynajmniej nie szkodzą wydajności bazy danych.

To samo dotyczy varchar (tak naprawdę char) kontra binarne: może to tylko pomóc. Czy to naprawdę ważne, o ile poprawiono wydajność?

Zdaję sobie sprawę, że to pytanie jest dość stare, ale trafiłem na to w swoich badaniach. Od tego czasu wydarzyło się wiele rzeczy (dyski SSD są wszechobecne, InnoDB ma aktualizacje itp.).

W swoich badaniach znalazłem ten dość interesujący post dotyczący wydajności:

twierdząc, że ze względu na losowość GUID / UUID drzewa indeksu mogą być raczej niezrównoważone. w bazie wiedzy MariaDB znalazłem inny post sugerujący rozwiązanie. Ale od tego czasu zajmuje się tym nowy UUID_TO_BIN . Ta funkcja jest dostępna tylko w MySQL (testowana wersja 8.0.18), a nie w MariaDB (wersja 10.4.10)

TL; DR: Przechowuj UUID jako przekonwertowane / zoptymalizowane wartości BINARY (16).