Dwa indeksy jednokolumnowe czy jeden indeks dwukolumnowy w MySQL?

114

Mam do czynienia z następującymi problemami i nie jestem pewien, jaka jest najlepsza praktyka.

Rozważ poniższą tabelę (która stanie się duża):

id PK | giver_id FK | odbiorca_id FK | data

Używam InnoDB iz tego, co rozumiem, automatycznie tworzy indeksy dla dwóch kolumn klucza obcego. Jednak będę również wykonywać wiele zapytań, w których muszę dopasować określoną kombinację:

SELECT...WHERE giver_id = x AND recipient_id = t.

Każda taka kombinacja będzie wyjątkowa w tabeli.

Czy jest jakaś korzyść z dodania dwukolumnowego indeksu do tych kolumn, czy też dwa indywidualne indeksy w teorii byłyby wystarczające / takie same?

— Tomek
źródło

1

Jeśli kombinacja dwóch kolumn jest unikalna, możesz utworzyć indeks dwukolumnowy z unikalną funkcją, która nie tylko zwiększy szybkość zapytania, ale także doda spójności tabeli.

— sguven

„MySQL może używać indeksów wielokolumnowych do zapytań testujących wszystkie kolumny indeksu lub zapytań testujących tylko pierwszą kolumnę, pierwsze dwie kolumny, pierwsze trzy kolumny itd. Jeśli określisz kolumny po prawej stronie Kolejność w definicji indeksu, pojedynczy indeks złożony może przyspieszyć kilka rodzajów zapytań w tej samej tabeli. " -

— Wielokolumnowe

Aby ekstrapolować na @ user1585784; Jeśli kombinacja dwóch kolumn jest wyjątkowa, myślę, że należy użyć do nich unikalnego klucza. W rzeczywistości, jeśli ktoś chce wymusić wyjątkowość na poziomie bazy danych, unikatowy klucz jest najłatwiejszym sposobem ...

— Erk

133

Jeśli masz dwa indeksy jednokolumnowe, w Twoim przykładzie zostanie użyty tylko jeden z nich.

Jeśli masz indeks z dwiema kolumnami, zapytanie może być szybsze (powinieneś zmierzyć). Indeks dwukolumnowy może być również używany jako indeks pojedynczej kolumny, ale tylko dla kolumny wymienionej jako pierwsza.

Czasami warto mieć indeks na (A, B) i inny indeks na (B). To sprawia, że zapytania korzystające z jednej lub obu kolumn są szybkie, ale oczywiście zajmuje również więcej miejsca na dysku.

Wybierając indeksy, należy również wziąć pod uwagę wpływ na wstawianie, usuwanie i aktualizację. Więcej indeksów = wolniejsze aktualizacje.

— Mark Byers
źródło

1

„MySQL może używać indeksów wielokolumnowych do zapytań testujących wszystkie kolumny indeksu lub zapytań testujących tylko pierwszą kolumnę, pierwsze dwie kolumny, pierwsze trzy kolumny itd. Jeśli określisz kolumny po prawej stronie Kolejność w definicji indeksu, pojedynczy indeks złożony może przyspieszyć kilka rodzajów zapytań w tej samej tabeli. " -

— Wielokolumnowe

33

Indeks obejmujący, taki jak:

ALTER TABLE your_table ADD INDEX (giver_id, recipient_id);

... oznaczałoby, że indeks mógłby zostać użyty w przypadku odniesienia do zapytania giver_idlub kombinacji giver_idi recipient_id. Pamiętaj, że kryteria indeksu są oparte na skrajnej lewej stronie - zapytanie odnoszące się tylko do recipient_idnie byłoby w stanie użyć indeksu pokrywającego w podanym przeze mnie oświadczeniu.

Ponadto MySQL może używać tylko jednego indeksu na SELECT, więc indeks pokrywający byłby najlepszym sposobem optymalizacji zapytań.

— Kucyki OMG
źródło

10

MySQL can only use one index per SELECTto już nie jest prawdą, byłoby miło, gdybyś zredagował swoją odpowiedź w celu zaktualizowania.

— Davor

Czy mógłbyś wyjaśnić, dlaczego indeks pokrycia nie mógłby być używany przez recipient_id?

— Ivo Pereira

2

@IvoPereira Wielokolumnowe indeksy w MySQL pozwalają na użycie wszystkich pól w indeksie od lewej do prawej. Na przykład, jeśli masz, to INDEX (col1, col2, col3, col4)indeks będzie stosowany do wyszukiwań z WHEREklauzulą taką jak col1 = 'A'lub col1 = 'A' AND col2 = 'B'lub col1 = 'A' AND col2 ='B' AND col3 = 'C' AND col4 = 'D', ale ten konkretny indeks nie będzie używany do niczego podobnego WHERE col2 = 'B'lub WHERE col3 = 'C' AND col4 = 'D'ponieważ pola wyszukiwania nie są pozostawione najbardziej w definicji indeksu. Będziesz musiał dodać dodatkowe indeksy, aby pokryć te pola.

— Slicktrick

„jeden indeks na WYBÓR” , czy jest to nadal prawdą w przypadku Mariadb 10.1?

— oldboy

1

@Anthony: Nie, patrz komentarz Davor powyżej.

— kapad

4

Jeśli jeden z indeksów kluczy obcych jest już bardzo selektywny, mechanizm bazy danych powinien użyć tego indeksu dla określonego zapytania. Większość silników baz danych używa pewnego rodzaju heurystyki, aby wybrać optymalny indeks w takiej sytuacji. Jeśli żaden z indeksów nie jest sam w sobie wysoce selektywny, prawdopodobnie ma sens dodanie indeksu zbudowanego na obu kluczach, ponieważ mówisz, że będziesz często używać tego typu zapytań.

Inną rzeczą do rozważenia jest to, czy możesz wyeliminować pole PK w tej tabeli i zdefiniować indeks klucza podstawowego w polach giver_idi recipient_id. Powiedziałeś, że kombinacja jest wyjątkowa, więc prawdopodobnie zadziała (biorąc pod uwagę wiele innych warunków, na które tylko Ty możesz odpowiedzieć). Zazwyczaj jednak uważam, że dodatkowa złożoność, która dodaje, nie jest warta zachodu.

— Mark Wilkins
źródło

Dzięki Mark, jeden z kluczy jest rzeczywiście bardzo selektywny, więc powinno być dobrze. Zdecydowałem się zachować te dwa (automatyczne) indeksy na miejscu i zobaczyć, jak to działa w czasie. Pomyślałem również o połączonym kluczu głównym giver: receiver, ale ponieważ każde pole musi być również przeszukiwane indywidualnie, po prostu dodałoby to narzut php. Ponadto nowy klucz byłby (dłuższym) łańcuchem zamiast (krótszej) liczby całkowitej.

— Tom

2

Inną rzeczą do rozważenia jest to, że charakterystyka wydajności obu podejść będzie oparta na rozmiarze i liczności zbioru danych. Może się okazać, że indeks dwukolumnowy staje się bardziej wydajny tylko przy pewnym progu rozmiaru zestawu danych lub dokładnie odwrotnie. Nic nie zastąpi metryk wydajności dla konkretnego scenariusza.

— Andrzej
źródło

czy możesz podać link do jakiejś dokumentacji na ten temat. Dzięki.

— kapad