Optymalizacja bazy danych – indeksy cz. 2

Rozszerzymy teraz bazę testową z poprzedniego artykułu o nowe kolumny i wiersze.

Rozszerzona baza testowa

Dodamy dwie kolumny i dwa wiersze

PESEL	IMIE	NAZWISKO	PLEC	DATA_URODZENIA
62	Jan	Kowalski	M	2012-01-02
51	Ewa	Wiśniewska	K	2012-01-03
73	Piotr	Kowalski	M	2012-01-03
48	Anna	Nowak	K	2012-01-05
56	Michał	Wójcik	M	2012-01-04

Indeks na jednym polu

Załóżmy że chcielibyśmy wykonać zapytanie:

select * from osoby where data_urodzenia='2012-01-03'

Zakładamy indeks na jednym polu:

create index on osoby(data_urodzenia)

Wykonanie tego zapytania spowoduje przejrzenie indeksu oraz wybranie 2 wierszy.

Warunek na dwóch polach, indeks na jednym

Pozostawmy indeks jak poprzednio

create index on osoby(data_urodzenia)

natomiast warunek uzupełnimy o jedno pole

select * from osoby where plec='K' and data_urodzenia='2012-01-03'

Wykonanie przy tym indeksie jest identyczne jak przy pojedynczym indeksie – przejrzenie indeksu, wybranie wg indeksu 2 wierszy a następnie odrzucenie jednego z dwóch niepasującego wiersza

Warunek na dwóch polach, indeks podwójny

Tym razem indeks będzie podwójny

 create index on osoby(data_urodzenia, plec)

Zapytanie bez zmian

 select * from osoby where plec='K' and data_urodzenia='2012-01-03'

Teraz baza korzystając z indeksu wybierze bezpośrednio jeden pasujący wiersz, już bez odrzucenia rekordu

Kolejność pól w indeksie

Zmienimy nieco zapytanie na warunek większości

 select * from osoby where plec='K' and data_urodzenia >= '2012-01-03'

Jak zadziała pierwszy indeks?

create index on osoby(data_urodzenia, plec)

Przypominam że indeks jest pomocniczą posortowaną tabelą. Ten indeks jest posortowany w kolejności według pól: data_urodzenia i płeć, czyli wygląda tak:

PESEL	IMIE	NAZWISKO	PLEC	DATA_URODZENIA
62	Jan	Kowalski	M	2012-01-02
51	Ewa	Wiśniewska	K	2012-01-03
73	Piotr	Kowalski	M	2012-01-03
56	Michał	Wójcik	M	2012-01-04
48	Anna	Nowak	K	2012-01-05

Przy warunku mniejszości indeks musi wybrać najpierw grupę 4 pasujących wierszy do warunku na datę urodzenia a następnie odrzucić 2 nie pasujące wiersz do warunku na płeć.

Teraz odwróćmy kolejność w indeksie:

create index on osoby(plec, data_urodzenia)

Tabela indeksu posortowana wygląda tak:

PESEL	IMIE	NAZWISKO	PLEC	DATA_URODZENIA
51	Ewa	Wiśniewska	K	2012-01-03
48	Anna	Nowak	K	2012-01-05
62	Jan	Kowalski	M	2012-01-02
73	Piotr	Kowalski	M	2012-01-03
56	Michał	Wójcik	M	2012-01-04

Teraz wykonanie indeksu rozpocznie się od warunku na płeć a następnie na datę i zostanie wybrany spójny przedział z tabeli indeksowej – wybrane zostaną od razu 2 pasujące wiersze.