SQL troszkę bardziej zaawansowany (relacje jeden do wielu, optymalizacja zapytań)

Jacek | 2011-02-19 01:01:41

Zajmiemy się podstawowymi (i nie tylko) funkcjami w bazie MySQL, których jeszcze nie opisałem. Zacznijmy od utworzenia tabeli:

Tworzenie tabeli

CREATE TABLE `uzytkownicy` (
   `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
   `imie` VARCHAR(255) NOT NULL,
   `nazwisko` VARCHAR(255) NOT NULL 
)

Od razu można zauważyć kilka elementów, które nie pojawiły się w poprzednim artykule.

NOT NULL = nie puste

Dodanie NOT NULL sprawia, że przy dodawaniu rekordu (INSERT) musimy to pole wypełnić i nie możemy go pominąć. Można za to uzupełnić je wartością pustą: '', ale nie "wartością" NULL.

AUTO_INCREMENT = automatyczna numeracja rekordów

Pomimo ustawienia NOT NULL, jeśli pominiemy to pole, baza MySQL automatycznie wstawi tu następny wolny numer, począwszy od 1.

PRIMARY KEY = indeks

Tutaj zaczynają się ciekawsze funkcje bazy danych - indeksy. Bardzo dobrym porównaniem jest spis treści w książce. Aby dotrzeć do danych MySQL nie musi przechodzić przez wszystkie strony, a tylko sprawdzić w indeksie, na której stronie znajduje się szukana wartość. Indeks PRIMARY KEY oznacza, że wybrana wartość, w tym przypadku pole id, jednoznacznie wskazuje na konkretny rekord. Ponieważ indeks ten jest unikalny, dwa rekordy nie mogą mieć identycznego identyfikatora. W danej tabeli nie może być też dwóch indeksów PRIMARY.

Istnieją też inne typy indeksów:

UNIQUE

podobny do PRIMARY KEY, jednak może istnieć więcej niż jeden indeks UNIQUE

INDEX

najprostszy indeks. Stara się zapamiętać kolejność rekordów według danego pola (przydatne do sortowania - patrz dalej).

FULLTEXT

ten typ indeksu zapamiętuje, w których rekordach znajdują się poszczególne całe słowa.

Na potrzeby tego artykułu potrzebna będzie jeszcze jedna tabela:

CREATE TABLE `adresy` (
   `uzytkownik` INT NOT NULL,
   `adres` VARCHAR(255) NOT NULL,
   `kod` CHAR(6) NOT NULL,
   `miasto` VARCHAR(255) NOT NULL,
   INDEX (`uzytkownik`),
   INDEX (`miasto`, `uzytkownik`)
)

I kolejne dodatkowe informacje:

CHAR(n) - stała liczba znaków

VARCHAR(n) pozwala na przechowywanie zmiennej liczby znaków, ale nie więcej niż n. CHAR(n) zawsze przechowuje dokładnie n znaków. Jeśli podamy ich mniej, to baza dopełni wartość zerami (dokładniej znakiem NUL). Przy zwracaniu wartości dodane zera są usuwane.

INDEX (`uzytkownik`) - też indeks

Składnia INDEX (`nazwa_pola`) to po prostu inny sposób zapisu indeksu. W pierwszym przykładzie usuwając PRIMARY KEY z drugiej linii i dodając po:

`nazwisko` VARCHAR(255) NOT NULL

przecinek, a po nim (najlepiej w nowej linii):

PRIMARY KEY (`id`)

uzyskalibyśmy identyczną tabelę.

Istnieje możliwość tworzenia indeksu dla więcej niż jednego pola. W tym wypadku musimy skorzystać z tej właśnie składni:

INDEX (`pole1`, `pole2`)

Indeks taki zachowuje się tak, jakby indeksował jedno pole, które składa się z połączonych w podanej kolejności wartości wskazanych pól.

Dodwanie danych

Tu już bez niespodzianek:

INSERT INTO `uzytkownicy`
   (`imie`, `nazwisko`)
VALUES
   ('Jan', 'Kowalski'),
   ('Adam', 'Nowak');

Tabela wygląda teraz tak:

SELECT * FROM `uzytkownicy`

/----------------------\\
| id | imie | nazwisko |
|----|------|----------|
|  1 | Jan  | Kowalski |
|  2 | Adam | Nowak    |
\\----------------------/

Jeszcze trochę danych:

INSERT INTO `adresy`
   (`uzytkownik`, `adres`, `kod`, `miasto`)
VALUES
   ('1', 'Akacjowa 100', '28-133', 'Pacanów'),
   ('1', 'Bajkowa 6', '29-100', 'Włoszczowa'),
   ('1', 'Cicha 18', '14-530', 'Frombork'),
   ('2', 'Jasna 21', '29-100', 'Włoszczowa');

Siostro, poproszę dane...

Zajmiemy się jedną z relacji - jeden do wielu. Jeden użytkownik może mieć kilka adresów, jednak każdy adres należy do jednego, konkretnego użytkownika. Spróbujmy wykorzystać dane z bazy:

Imiona i nazwiska użytkowników mieszkających we Włoszczowie:

SELECT imie, nazwisko
   FROM uzytkownicy
   LEFT JOIN adresy
      ON (`uzytkownicy`.`id` = `adresy`.`uzytkownik`)
   WHERE `miasto` = 'Włoszczowa'

Poproszę o imię i nazwisko (SELECT imie, nazwisko) z tabeli użytkownicy (FROM uzytkownicy). Proszę dołączyć tabelę adresy (LEFT JOIN adresy) tak, by pole `id` było równe polu `uzytkownik` (ON (warunki)). A teraz chcę tylko rekordy z `miasto` = 'Włoszczowa' (WHERE ...).

Tak zapytanie wygląda. MySQL dokonuje kilku sztuczek z indeksami, by zapytanie było wykonywane optymalniej i szybciej:

1. WHERE jest ważniejsze, więc zacznę od tabeli `adresy`.
2. Mam indeks z polami `miasto` i `uzytkownik` oraz indeks z samym polem `uzytkownik`.
3. Indeks (`miasto`, `uzytkownik`) jest lepszy. Poszukajmy w nim ciągu zaczynającego się od 'Włoszczowa'.
4. Mam dwa rekordy z `uzytkownik`='1' i `uzytkownik`='2'
5. Zajmijmy się drugą częścią zapytania.
6. W tabeli `uzytkownicy` mam indeks według pola `id`. Poszukam w nim 1 i 2.
7. Odczytam rekordy, które wskazał indeks. Gotowe.

W celu sprawdzenia, czy MySQL zachowuje się tak jak chcemy i korzysta z indeksów, co jest szczególnie ważne przy dużej ilości danych, wykonujemy zapytanie:

EXPLAIN SELECT imie, nazwisko
   FROM uzytkownicy
   LEFT JOIN adresy
      ON (`uzytkownicy`.`id` = `adresy`.`uzytkownik`)
   WHERE `miasto` = 'Włoszczowa'

Zwróci ono taką "tabelkę" z wynikiem:

Wiemy ile "prostych" zapytań wykonał MySQL (2), jakich indeksów próbował użyć (są to nazwy - zobacz np. w phpMyAdmin), i czy faktycznie ich użył (świadczy o tym tekst "Using index" w sekcji Extra).

EXPLAIN to bardzo użyteczne narzędzie do optymalizowania zapytań. Dokładne informacje na temat zwracanych wartości dostępne są w dokumentacji bazy MySQL.

Kelner, jeszcze raz to samo...

SELECT imie, nazwisko
   FROM adresy, uzytkownicy
   WHERE `uzytkownicy`.`id` = `adresy`.`uzytkownik`
      AND `miasto` = 'Włoszczowa'

Tak! Powyższe zapytanie zachowa się tak samo jak opisany powyżej LEFT JOIN.

Jesze jeden przykład:

SELECT imie, nazwisko
   FROM uzytkownicy
   WHERE `id` IN (
      SELECT `uzytkownik`
      FROM adresy
      WHERE `miasto` = 'Włoszczowa'
   )

Trzeba jednak zauważyć (EXPLAIN), że zapytanie to nie będzie optymalne i (najprawdopodobniej) MySQL przeszuka całą tablicę uzytkownicy.

Epilog

O bazach danych i SQL można pisać długo - istnieje więc niezerowe prawdopodobieństwo, że pojawi się kolejna część artykułu. Niecierpliwych odsyłam do przepastnej dokumentacji: SELECT, JOIN, optymalizowanie zapytań SELECT.

---

• Wróć do MySQL
• Brakuje ci artykułu? Zaproponuj go!

---

© by Jacek Kowalski Sponsor: poszukiwany

Menu