Indeksy w MySQL

Lekcja: Indeksy w MySQL

Cel lekcji:

  • Zrozumienie, czym są indeksy w MySQL i dlaczego są używane.
  • Poznanie różnych typów indeksów i ich zastosowań.
  • Nauka tworzenia, usuwania i optymalizacji indeksów.

Teoria:

1. Czym są indeksy?

Indeksy w MySQL są strukturami danych, które poprawiają wydajność zapytań odczytujących dane. Działają podobnie do indeksu w książce – pozwalają szybciej znaleźć dane bez konieczności przeszukiwania całej tabeli. Indeks jest tworzony na jednej lub kilku kolumnach tabeli i umożliwia MySQL szybkie lokalizowanie rekordów.

2. Dlaczego używamy indeksów?

Indeksy przyspieszają operacje odczytu, zwłaszcza przy dużych tabelach, gdzie pełne skanowanie tabeli byłoby bardzo czasochłonne. Indeksy są szczególnie przydatne dla kolumn, które często są używane w:

  • Klauzulach WHERE
  • Klauzulach JOIN
  • Funkcjach agregujących (GROUP BY, ORDER BY)

Bez indeksów MySQL musiałby przejrzeć każdy rekord, aby znaleźć potrzebne dane, co może znacząco obniżyć wydajność w przypadku dużych zbiorów danych.

3. Typy indeksów w MySQL

MySQL oferuje różne typy indeksów, dostosowane do różnych zastosowań:

  • Primary Key (klucz główny): Jest to specjalny indeks, który identyfikuje każdy rekord w tabeli unikalnie. Tabela może mieć tylko jeden klucz główny.

  • Unique Index (indeks unikalny): Gwarantuje, że wszystkie wartości w indeksowanej kolumnie (lub zestawie kolumn) są unikalne. Używany jest w przypadku, gdy nie chcemy, aby wartości się powtarzały.

  • Index (indeks zwykły): Zwykły indeks służy głównie do przyspieszania wyszukiwania w dużych tabelach. Nie ma ograniczeń co do unikalności wartości.

  • Full-Text Index (indeks pełnotekstowy): Służy do wyszukiwania tekstowego w dużych ilościach tekstu. Używany jest w przypadku przeszukiwania kolumn tekstowych za pomocą funkcji MATCH i AGAINST.

  • Spatial Index (indeks przestrzenny): Służy do indeksowania danych przestrzennych (geograficznych) i może być używany w tabelach obsługujących kolumny przestrzenne.

4. Jak działają indeksy?

Kiedy tworzymy indeks na kolumnie, MySQL tworzy strukturę danych, która pozwala na szybkie wyszukiwanie wartości w tej kolumnie. Struktury te przechowują wartości w zorganizowany sposób (np. drzewa B-Tree lub inne struktury), co przyspiesza operacje odczytu. Jednakże, indeksy mogą również spowolnić operacje zapisu, ponieważ MySQL musi aktualizować indeksy po każdym wstawieniu, aktualizacji lub usunięciu danych.

Przykłady zastosowania indeksów

  1. Tworzenie indeksu na kolumnie używanej w zapytaniach:

    CREATE INDEX idx_employee_name ON employees (name);

    Tworzymy indeks na kolumnie name tabeli employees, co przyspieszy zapytania zawierające tę kolumnę.

  2. Indeks unikalny:

    CREATE UNIQUE INDEX idx_unique_email ON users (email);

    Tworzymy unikalny indeks na kolumnie email, co zapobiega dodawaniu duplikatów.

Ćwiczenia praktyczne w phpMyAdmin

1. Ćwiczenie 1: Tworzenie indeksu na kolumnie nazwisko

  • Cel: Zobaczyć, jak indeks wpływa na wydajność zapytań.
  • Kroki:
    1. W phpMyAdmin wybierz tabelę zawierającą kolumnę nazwisko.
    2. Wybierz zakładkę Struktura, a następnie kliknij Indeks obok kolumny nazwisko.
    3. Zatwierdź tworzenie indeksu.
    4. Wykonaj zapytanie SELECT * FROM tabela WHERE nazwisko = 'Kowalski' i zwróć uwagę na szybkość wykonania.
  • Oczekiwany wynik: Dzięki indeksowi zapytanie powinno działać szybciej, zwłaszcza przy dużej liczbie rekordów.

2. Ćwiczenie 2: Tworzenie indeksu unikalnego na kolumnie email

  • Cel: Zapobiec wstawianiu duplikatów w kolumnie email.
  • Kroki:
    1. W phpMyAdmin wybierz tabelę z kolumną email.
    2. Przejdź do zakładki Struktura i wybierz Indeks unikalny dla kolumny email.
    3. Zatwierdź operację.
    4. Spróbuj dodać nowy rekord z istniejącym już email i sprawdź, czy otrzymasz błąd.
  • Oczekiwany wynik: phpMyAdmin nie powinien pozwolić na dodanie duplikatów w kolumnie email.

3. Ćwiczenie 3: Tworzenie indeksu pełnotekstowego na kolumnie opis

  • Cel: Przyspieszenie wyszukiwania pełnotekstowego.
  • Kroki:
    1. Wybierz tabelę, która zawiera kolumnę opis (np. opis produktów).
    2. Przejdź do zakładki Struktura i utwórz indeks pełnotekstowy dla kolumny opis.
    3. Wykonaj zapytanie pełnotekstowe, np. SELECT * FROM produkty WHERE MATCH(opis) AGAINST('laptop').
  • Oczekiwany wynik: Wyszukiwanie pełnotekstowe w kolumnie opis będzie szybsze.

4. Ćwiczenie 4: Usuwanie indeksu i sprawdzenie wydajności

  • Cel: Zobaczyć, jak usunięcie indeksu wpływa na wydajność zapytań.
  • Kroki:
    1. Przejdź do zakładki Struktura i wybierz istniejący indeks w tabeli.
    2. Usuń indeks, klikając ikonę kosza obok niego.
    3. Ponownie wykonaj zapytanie, które wcześniej działało szybciej, np. SELECT * FROM tabela WHERE nazwisko = 'Kowalski'.
  • Oczekiwany wynik: Po usunięciu indeksu zapytanie powinno działać wolniej.

Podsumowanie:

Indeksy są kluczowym elementem optymalizacji zapytań w MySQL. Dobrze zastosowane indeksy mogą znacząco przyspieszyć działanie bazy danych, szczególnie przy dużych zbiorach danych. Jednak nadmiar indeksów lub ich złe zastosowanie może negatywnie wpłynąć na wydajność operacji zapisu. Dlatego ważne jest, aby świadomie wybierać kolumny do indeksowania, analizować wydajność zapytań i unikać zbędnych indeksów.