Co oznacza „indeks” w RDBMS? [Zamknięte]

Używam indeksów, jak większość programistów (głównie na indeksie ... no cóż!), Ale jestem pewien, że istnieje wiele subtelnych sposobów optymalizacji bazy danych za pomocą indeksu. Nie jestem pewien, czy jest to specyficzne dla jakiejkolwiek implementacji DBMS.

Moje pytanie brzmi: jakie są dobre przykłady użycia indeksu (z wyjątkiem prostych, oczywistych przypadków) i w jaki sposób DBMS optymalizuje swoją bazę danych, gdy określasz indeks w tabeli?

Pomyśl o indeksie jako o „spisie treści” ... to uporządkowana lista wskaźników do pozycji w pliku, czyli przesunięć. Załóżmy, że masz miliony rekordów zapisanych w tabeli, zamiast przeszukiwać tabelę w celu znalezienia pasujących kryteriów, znacznie szybciej jest odwoływać się do uporządkowanej listy w celu znalezienia dopasowań, a następnie układać wskaźniki w określone pasujące wiersze. Doskonałym przykładem indeksu jest pole klucza podstawowego tabeli, najczęściej jego pole „id”. Jeśli chcesz mieć identyfikator wiersza # 11234566, znacznie szybciej zapytaj indeksu o wskaźnik do danych niż w przypadku skanowania źródła danych w poszukiwaniu pozycji 11234566.

Oto nie tak oczywiste zastosowanie indeksowania:

CREATE TABLE activity_log (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
activity_type_id SMALLINT UNSIGNED NOT NULL,
datetime_created DATETIME
KEY(activity_type_id),
PRIMARY KEY(id)
);
CREATE TABLE activity_log_to_date_key (
activity_log_id INT UNSIGNED NOT NULL,
date_created_key  INT UNSIGNED NOT NULL REFERENCES dim_datetime(id),
UNIQUE KEY(activity_log_id),
KEY(date_created_key)
);
CREATE TABLE dim_datetime (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
date_hour DATETIME NOT NULL,
PRIMARY KEY(id),
KEY(date_hour)
);

Operacja może utworzyć rekord dziennika, ale następnie utworzyć odwołanie do zindeksowanej daty / godziny, która jest szybsza do wyszukiwania / sortowania niż tabela dziennika. Następnie ponownie dołącz tabelę dziennika do własnego klucza podstawowego. Jeśli potrzebujesz mnie rozwinąć, daj mi znać. Mam nadzieję, że to ma sens.

Przykładowe zapytanie:

SELECT a.activity_log_id, al.activity_type_id, al.datetime_created
FROM activity_log_to_date_key a 
INNER JOIN dim_datetime d ON (d.id = a.date_created_key)
LEFT JOIN activity_log al ON (al.id = a.activity_log_id)
WHERE d.date_hour BETWEEN '2009-01-01 00:00:00' AND '2009-06-01 12:00:00';

Jednym z punktów, który wydaje się być przeoczony wielu osobom, jest to, że DBMS często (lub może) używać tylko jednego indeksu na odwołanie do tabeli w zapytaniu, a jeśli może i używa wielu indeksów, prawdopodobnie szybsze byłoby użycie połączonego indeks, jeśli jest obecny.

Na przykład, jeśli szukając wierszy WHERE AnIntegerColumn = 42 AND AnOtherInt = 69w dużej tabeli, najszybszą drogą do tych wierszy byłby indeks dwóch kolumn AnIntegerColumn i AnOtherInt. Jeśli masz tylko indeks dla każdego z osobna, ale nie ma indeksu łączonego, baza danych przeszuka jeden lub drugi indeks i oddzielnie odfiltruje wyniki za pomocą drugiej klauzuli lub przeskanuje oba i połączy wyniki.

Inną powszechną prostą operacją, którą można ulepszyć za pomocą indeksów kompozytowych, jest WHERE SomeColumn = <SomeValue> ORDER BY SomeOtherColumn- jeśli w SomeColumn i SomeOtherColumn istnieje indeks (w odpowiedniej kolejności), w niektórych okolicznościach mogą być jednocześnie wykonywane operacje filtrowania i porządkowania.

Dodanie zbyt wielu indeksów może być złą optymalizacją, ponieważ dodatkowa przestrzeń używana do przechowywania indeksów (i obciążenie IO do ich utrzymania, jeśli twoja baza danych widzi wiele operacji zapisu) może być gorszym problemem niż nieco mniej optymalne zapytania odczytu , więc nie przesadzaj.

David i Randy zajmują się tym. Chciałem tylko dodać, że komenda może być ogromna pomoc w zastanawianie się, kiedy dostaniesz duża oszczędność z tworzenia indeksu, a także sugeruje, które potrzebne są indeksy. Wyświetli kroki, które baza danych podejmuje w celu uruchomienia zapytania, abyś wiedział, które bity zabierają najwięcej czasu.EXPLAIN

Coś, o czym jeszcze nie wspomniałem tutaj, to to, że jeśli masz więcej niż jeden dysk, prawdopodobnie chcesz umieścić swój indeks na innym dysku niż miejsce, w którym faktycznie znajdują się dane. Może to przyspieszyć niektóre operacje. Myślę, że to zasługuje na pytanie samo w sobie.