MySQL Indeks: Samouczek tworzenia, dodawania i upuszczania

Czym są sterowniki MySQL Indeks?

An wskaźnik in MySQL to struktura danych, która przechowuje wartości kolumn w uporządkowany sposób, dzięki czemu wyszukiwarka może szybko wyszukiwać wiersze. Indeksy są tworzone na podstawie kolumny lub kolumn najczęściej używanych do filtrowania danych. Wyobraź sobie indeks jak listę posortowaną alfabetycznie: znalezienie nazwy na liście posortowanej jest znacznie szybsze niż w niesortowanym stosie.

Indeksy wiążą się z pewnym kompromisem — każda instrukcja INSERT lub UPDATE musi utrzymywać indeks, więc dodanie zbyt wielu indeksów do tabeli z dużą liczbą zapisów może negatywnie wpłynąć na ogólną wydajność. Zasadniczo kolumny indeksów, które pojawiają się w klauzulach WHERE, JOIN i ORDER BY w tabelach, są odczytywane częściej niż zapisywane.

Dlaczego warto używać indeksu?

Nikt nie lubi powolnych systemów. Wysoka wydajność jest priorytetem dla niemal każdej aplikacji opartej na bazie danych. Firmy inwestują ogromne sumy w sprzęt, aby zapewnić szybkość zapytań, ale możliwości samego sprzętu są ograniczone. Optymalizacja indeksów to tańsze i skuteczniejsze rozwiązanie.

Długie czasy reakcji wynikają zazwyczaj z przechowywania wierszy w kolejności fizycznej na dysku. Bez indeksu, MySQL musi przeskanować każdy wiersz, aby znaleźć te, które pasują do predykatu — „pełne skanowanie tabeli”. Indeksy pozwalają MySQL przejdź bezpośrednio do pasujących wierszy, co zmienia plan zapytania z O(n) na mniej więcej O(log n) w przypadku wyszukiwań w drzewie B.

Składnia: Utwórz indeks

Indeks można zdefiniować w dwóch miejscach:

1. W momencie tworzenia tabeli.
2. Po tym jak tabela już istnieje.

Przykład: Utwórz indeks wbudowany za pomocą polecenia CREATE TABLE

Dla myflixdb W bazie danych spodziewamy się wielu wyszukiwań w kolumnie z pełnym imieniem i nazwiskiem. Poniższy skrypt tworzy nową members_indexed tabela z indeksem na full_names Kolumna.

CREATE TABLE `members_indexed` (
    `membership_number` INT(11) NOT NULL AUTO_INCREMENT,
    `full_names`        VARCHAR(150) DEFAULT NULL,
    `gender`            VARCHAR(6)   DEFAULT NULL,
    `date_of_birth`     DATE         DEFAULT NULL,
    `physical_address`  VARCHAR(255) DEFAULT NULL,
    `postal_address`    VARCHAR(255) DEFAULT NULL,
    `contact_number`    VARCHAR(75)  DEFAULT NULL,
    `email`             VARCHAR(255) DEFAULT NULL,
    PRIMARY KEY (`membership_number`),
    INDEX (`full_names`)
) ENGINE = InnoDB;

Wykonaj skrypt w MySQL Stół warsztatowy przeciwko myflixdb Baza danych.

Odśwież kod myflixdb zobaczyć nowe members_indexed tabela. The full_names kolumna teraz pojawia się pod Indeksy węzeł.

W miarę wzrostu liczby członków, zapytania wyszukiwania na temat members_indexed które używają WHERE i ORDER BY full_names są znacznie szybsze niż te same zapytania w oryginale members tabela bez indeksu.

Dodaj indeks po utworzeniu tabeli

Często odkryjesz, że istniejąca tabela potrzebuje indeksu — zapytania wyszukiwania są powolne, a plan EXPLAIN pokazuje pełne skanowanie tabeli w kolumnie, która pojawia się w WHERE. CREATE INDEX Polecenie dodaje indeks bez ponownego tworzenia tabeli.

CREATE INDEX `id_index` ON `table_name` (`column_name`);

Konkretny przykład — przyspieszenie wyszukiwania na title kolumna movies stół:

CREATE INDEX `title_index` ON `movies` (`title`);

Każde zapytanie, które filtruje movies.title jest teraz wspierany przez nowy indeks. Zapytania filtrujące według innych kolumn nadal skanują tabelę, chyba że mają własny indeks.

Uwaga: Możesz utworzyć indeks złożony obejmujący wiele kolumn, jeśli Twoje zapytania zawsze filtrują lub sortują według tej samej kombinacji. Kolejność ma znaczenie — wiodąca kolumna decyduje o tym, czy indeks może zostać użyty.

Wyświetlanie indeksów w tabeli

Zastosowanie SHOW INDEXES aby zobaczyć wszystkie indeksy zdefiniowane w tabeli.

SHOW INDEXES FROM `table_name`;

Przykład — lista indeksów na movies stół:

SHOW INDEXES FROM `movies`;

Uruchom instrukcję w MySQL Workbench przed myflixdb aby zobaczyć istniejące indeksy i kolumny, które obejmują.

Uwaga: Klucze podstawowe i obce są indeksowane automatycznie przez MySQLKażdy indeks ma unikalną nazwę i wymienia kolumny, które obejmuje.

Składnia: Usuń indeks

Zastosowanie DROP INDEX Aby usunąć istniejący indeks z tabeli. Jest to przydatne, gdy tabela intensywnie zapisana jest spowalniana przez indeks, który nie jest już dostępny do odczytu.

DROP INDEX `index_id` ON `table_name`;

Konkretny przykład — pomiń full_names indeks z members_indexed:

DROP INDEX `full_names` ON `members_indexed`;

Rodzaje MySQL Indeksy

MySQL obsługuje kilka typów indeksów, z których każdy jest przeznaczony do innego obciążenia.

Typ	Cel
KLUCZ PODSTAWOWY	Unikalny identyfikator wiersza; powiązany z danymi tabeli w InnoDB.
NIECODZIENNCH	Wymusza unikalność, pełniąc funkcję indeksu.
INDEKS (B-drzewo)	Domyślny indeks pomocniczy używany w zapytaniach zakresowych i wyszukiwaniach równości.
PEŁNY TEKST	Zoptymalizowany pod kątem wyszukiwania tekstu w języku naturalnym za pomocą funkcji MATCH … AGAINST.
PRZESTRZENNE	Indeks R-tree dla typów danych GIS, takich jak POINT i POLYGON.
HASZYSZ	Wyszukiwania równości w czasie stałym; używane przez moduł pamięci masowej MEMORY.
Kompozyt (wielokolumnowy)	Łączy kilka kolumn w jeden indeks, respektując regułę lewego prefiksu.

Najlepsze praktyki dla MySQL Indeksy

Poniższe nawyki sprawiają, że indeksy pozostają użyteczne i nie stają się bezużyteczne.

• Indeks wzorca zapytania, nie nazwa kolumny: dodawaj indeksy, które pasują do prawdziwych klauzul WHERE, JOIN i ORDER BY, a nie do „każdej kolumny, która wydaje się ważna”.
• Obserwuj kolejność indeksu złożonego: Aby indeks mógł zostać użyty, wiodąca kolumna musi pojawić się w zapytaniu.
• Unikaj duplikatów indeksów: wiodący prefiks indeksu złożonego obejmuje już wyszukiwania jednokolumnowe na podstawie tego prefiksu.
• Sprawdź za pomocą EXPLAIN: potwierdzić, że planista faktycznie wybiera nowy indeks.
• Usuń nieużywane indeksy: posługiwać się sys.schema_unused_indexes in MySQL 5.7+ do znajdowania indeksów, których nic nie odczytuje.
• Dopasuj typy danych: jeśli klauzula WHERE porównuje kolumnę VARCHAR z liczbą, indeksu nie można użyć z powodu niejawnego rzutowania.

FAQ