Jak zoptymalizować inserty korzystając z UUID-ów?

W tabeli mam koło 500tys. wierszy (w perspektywie >11mln), a INSERT trwa coraz dłużej (czasem nawet koło 400-500ms, z pustą tabelą kilkadziesiąt). Korzystam z MariaDB 11.4.2 (a więc z wbudowanym typem UUID), wszystkie tabele na InnoDB. Identyfikatory w wersji v7 (sortowalne, oparte o czas) generuję w aplikacji. Uproszczony schemat tabel (uprzedzam pytania, że tabele nie są przeładowana, tj. nie posiadają żadnych dodatkowych kolumn typu TEXT itp.):

messages(id: UUID, room_id: UUID) - room_id to obecnie zwykły index, id - PK
rooms(id: UUID) - id PK

Moje pomysły na rozwiązanie problemu:

1. wincyj mocy (najbardziej oczywiste, ale chcę wycisnąć z rozwiązania co się da);
2. wyrzucić obecny index z messages.room_id i nałożyć na tą kolumnę klucz obcy do tabeli rooms, żeby zaoszczędzić czas na budowanie osobnego indeksu dla identyfikatorów pokojów (czy to ma sens?);
3. przerzucić wiadomości na nosql (chociaż wolałbym nie zwiększać stacka).

Jakieś inne pomysły na optymalizację? Chętnie przyjmę wszelkie poszlaki przy pracy z UUID-ami w Marii

1. FK nie tworzy indeksu. FK z założenia jest do pola PK w innej tabeli więc to pole ma też indeks. Indeks na polu messages->room_id ma sens TYLKO jeśli używasz tego pola do wyszukiwania.
2. co kto lubi

co jeszcze możesz zrobić - sprawdz, czy wszystkie indeksy są używane https://vettabase.com/finding-duplicate-indexes-and-unused-indexes-mariadb-mysql/
zastanó się nad partycjonowaniem danych
no i ja bym jednak zrezygnował z UUID (o ile nie kożystasz z ich właściwości, np. globalna unikalność) na rzecz auto inkrementacji inta/biginta - po pierwsze tracisz czas na generowanie UUID, po drugie zajmują 2 razy więcej miejsca niż bigint albo 4 razy więcej niż int a co za tym idzie indeksy na tych polach są większe i porównywanie trwa dłużej

Tak na szybko:

1. Do relacji/identyfikacji używaj zwykłego ID integerowego incrementowanego.
2. Jeśli chcesz UUID, żeby np. user nie mógł zrobić enumeracji, to przechowuj go jako unique w dodatkowej tabeli i używaj go zawsze gdy zwykły zewnętrzny user ma dostęp do obiektu (np. w adresie URL, w wyszukiwaniu resource'a po tym UUID). I obowiązkowo dodaj index wtedy na ten UUID.
3. Jeśli nie ma znaczenia, czy user zna ID, czy nie, to lepiej korzystać z ID. Np. w panelu admina możesz operować na ID.

Jeszcze jeden pomysł:
Możesz spróbować przejść tylko na zwykłego int. Wywal całkowicie uuid. A ten hash, który będzie widział user, możesz generować w locie na bazie ID z bazy:

https://sqids.org/php

Dzięki za wszystkie sugestie, brakło mi wcześniej czasu by odpowiedzieć.

abrakadaber napisał(a):

1. FK nie tworzy indeksu. FK z założenia jest do pola PK w innej tabeli więc to pole ma też indeks. Indeks na polu messages->room_id ma sens TYLKO jeśli używasz tego pola do wyszukiwania.

Używam pola do wyszukiwania. Gdzieś wyczytałem, że FK nie wymaga dodatkowego indeksu, bo sam w sobie jest indeksem w InnoDB.

co jeszcze możesz zrobić - sprawdz, czy wszystkie indeksy są używane https://vettabase.com/finding-duplicate-indexes-and-unused-indexes-mariadb-mysql/

Zrobiłem indeksy złożone zgodnie z ich opisem w dokumentacji, czyli kolumny od lewej w kolejności użycia w zapytaniach. Mimo to, niektóre zapytania (EXPLAIN) nie używają indexu złożonego, tylko np. PRIMARY.

no i ja bym jednak zrezygnował z UUID (o ile nie kożystasz z ich właściwości, np. globalna unikalność) na rzecz auto inkrementacji inta/biginta - po pierwsze tracisz czas na generowanie UUID, po drugie zajmują 2 razy więcej miejsca niż bigint albo 4 razy więcej niż int a co za tym idzie indeksy na tych polach są większe i porównywanie trwa dłużej

serek napisał(a):

Możesz spróbować przejść tylko na zwykłego int. Wywal całkowicie uuid. A ten hash, który będzie widział user, możesz generować w locie na bazie ID z bazy:
https://sqids.org/php

Niestety jest to system rozproszony czasu rzeczywistego, więc UUID-y są tu koniecznością żeby nie "zarżnąć" bazy samym generowaniem identyfikatorów i pilnowaniem ich unikalności.

serek napisał(a):

1. Do relacji/identyfikacji używaj zwykłego ID integerowego incrementowanego.
2. Jeśli chcesz UUID, żeby np. user nie mógł zrobić enumeracji, to przechowuj go jako unique w dodatkowej tabeli i używaj go zawsze gdy zwykły zewnętrzny user ma dostęp do obiektu (np. w adresie URL, w wyszukiwaniu resource'a po tym UUID). I obowiązkowo dodaj index wtedy na ten UUID.
3. Jeśli nie ma znaczenia, czy user zna ID, czy nie, to lepiej korzystać z ID. Np. w panelu admina możesz operować na ID.

I to chyba będzie rozwiązanie. Stworzę nowe tabele mapujące ID numeryczne do UUID i zrobię relacje po tej intowej wartości. Wtedy przy insertach do tabeli messages jedynym UUID-em do indeksacji będzie ID wiadomości, reszta (room_id i pochodne) będą numeryczne. To chyba dobry kierunek

Co do partycjonowania, to szczerze mówiąc nie wiem jak się za to zabrać. Czytałem ogólnie na czym to polega i na pierwszy rzut oka wydaje mi się, że na tabeli która najbardziej potrzebowałaby tego rozwiązania, to będzie ciężkie do osiągnięcia przez konieczność użycia funkcji agregujących.