Czytałem, że indeksowanie niektórych baz danych ( SQL Server to ten, o którym czytałem ) nie ma większego wpływu, dopóki nie przekroczysz określonego progu wierszy, ponieważ baza danych będzie przechowywać całą tabelę X w pamięć.

Zwykle planowałbym indeksować moje GDZIE i unikalne kolumny / mniej zmienione tabele. Po zapoznaniu się z sugerowanym minimum ( które wynosiło około 10k ), chciałem dowiedzieć się więcej o tym pomyśle. Jeśli istnieją tabele, o których wiem, że nigdy nie przekroczą określonego punktu, może to zmienić sposób indeksowania niektórych z nich.

W przypadku czegoś takiego jak MySQL, MyISAM / INNODB, czy istnieje punkt, w którym indeksowanie ma niewielką wartość i jakie są sposoby jej określenia?

Uwaga: z poważaniem, nie szukam sugestii dotyczących struktury mojej bazy danych, takich jak „Mimo wszystko powinieneś indeksować”. Chcę zrozumieć tę koncepcję, czy jest prawdziwa, czy nie, jak określić progi i podobne informacje.

1
Matt Kenefick 3 kwiecień 2020, 21:02

3 odpowiedzi

Najlepsza odpowiedź

Jednym z głównych zastosowań indeksów jest zmniejszenie liczby odczytywanych stron. Sam indeks jest zwykle mniejszy niż tabela. Tak więc tylko jeśli chodzi o odczyt / zapis stron , generalnie potrzebujesz co najmniej trzech stron z danymi, aby zobaczyć korzyści, ponieważ używanie indeksu wymaga co najmniej dwóch stron danych (jednej dla indeksu i jednej dla oryginalne dane).

(W rzeczywistości, jeśli indeks obejmuje zapytanie, rentowność wynosi dwa).

Liczba stron danych potrzebnych do tabeli zależy od rozmiaru rekordów i liczby wierszy. Tak więc naprawdę nie jest możliwe określenie progu liczby wierszy.

Powyższe bardzo podstawowe wyjaśnienie pomija kilka rzeczy:

  • Koszt skanowania stron danych w celu porównania dla każdego wiersza.
  • Koszt wczytywania i korzystania ze stron indeksowych.
  • Inne zastosowania indeksowania.

Ale daje ci to pomysł i możesz zobaczyć korzyści na tabelach znacznie mniejszych niż 10 tys. Wierszy. To powiedziawszy, możesz łatwo przeprowadzić testy na swoich danych, aby zobaczyć, jak działają zapytania w danych tabelach.

Ponadto zdecydowanie zalecam posiadanie kluczy podstawowych we wszystkich tabelach i używanie tych kluczy do relacji z kluczami obcymi. Sam klucz podstawowy jest indeksem.

2
Gordon Linoff 3 kwiecień 2020, 18:19

Każda sytuacja jest inna. Jeśli sprofilujesz swój kod, lepiej zrozumiesz każdy anty-wzorzec. Aby zademonstrować ekstremalną nieoczekiwanie, rozważ Oracle:

Gdyby to była Oracle, powiedziałbym, że zero, ponieważ jeśli wysoki znacznik poziomu pustej tabeli jest bardzo wysoki, wówczas zapytanie motywujące do pełnego skanowania tabeli, które zwraca zero wierszy, byłoby znacznie droższe niż to samo zapytanie, które miałoby wywołać nawet pełne skanowanie indeksu.

Ten sam proces, przez który przeszedłem, aby zrozumieć Oracle, który możesz wykonać z MySQL: profiluj swój kod.

0
Jeff Holt 3 kwiecień 2020, 18:17

Indeksy służą wielu celom. Tabele InnoDB są zawsze zorganizowane jako indeks na kluczu klastra. Indeksy mogą służyć do wymuszania unikatowych ograniczeń, a także do obsługi ograniczeń związanych z kluczami obcymi. Temat „indeksów” obejmuje znacznie więcej niż tylko wydajność zapytań.

Jeśli chodzi o wydajność zapytania, to naprawdę zależy od tego, co robi zapytanie. Jeśli wybieramy niewielki podzbiór wierszy z dużego zestawu, wtedy efektywne użycie indeksu może przyspieszyć ten proces, eliminując sprawdzanie dużych połaci wierszy. Stąd pochodzi największy huk.

Jeśli wyciągamy wszystkie wiersze lub prawie wszystkie wiersze z zestawu, indeks zazwyczaj nie pomaga zawęzić, które wiersze należy sprawdzić; nawet jeśli indeks jest dostępny, optymalizator może wybrać pełne skanowanie wszystkich wierszy.

Ale nawet podczas ściągania dużych podzbiorów odpowiednie indeksy mogą poprawić wydajność operacji łączenia i mogą znacznie poprawić wydajność zapytań z klauzulami GROUP BY lub ORDER BY, wykorzystując indeks do pobierania wierszy w kolejności, zamiast wymagać użycia opcji „Using filesort " operacja.

Jeśli szukamy prostej praktycznej zasady ... dla dużego zestawu, jeśli potrzebujemy wyciągnąć (lub spojrzeć na) mniej niż 10% wszystkich wierszy, wówczas plan dostępu wykorzystujący odpowiedni indeks zwykle przewyższa pełne skanowanie. Jeśli szukamy konkretnego wiersza na podstawie unikalnego identyfikatora, indeks będzie szybszy niż pełne skanowanie. Jeśli wyciągniemy wszystkie kolumny dla każdego wiersza w tabeli n bez określonej kolejności, pełne skanowanie będzie szybsze.

Ponownie, tak naprawdę sprowadza się do tego, jakie operacje są wykonywane. Jakie zapytania są wykonywane i jaki profil wydajności jest nam potrzebny na podstawie tych zapytań. To będzie klucz do określenia strategii indeksowania.

Aby uzyskać zrozumienie, użyj EXPLAIN, aby zobaczyć plan wykonania. I poznaj operacje dostępne dla optymalizatora MySQl.

(Temat strategii indeksowania pod względem wydajności bazy danych jest o wiele za duży, aby odpowiedzieć na pytanie dotyczące StackOverflow).

1
spencer7593 3 kwiecień 2020, 18:20