Indeksy w MySQL

Indeksy i optymalizacja

Najlepszym sposobem zwiększenia wydajności operacji wyszukiwania w bazie danych (instrukcja SELECT) jest tworzenie indeksów. Indeksy można tworzyć na jednej lub kilku kolumnach, które zamierzamy sprawdzić w zapytaniu.Indeksy to struktury danych na dysku umożliwiające szybkie wyszukiwanie danych w bazie danych na podstawie wartości kluczy wyszukiwania. Wpisy indeksu działają jak wskaźniki do wierszy w tabeli, co pozwala na szybkie znalezienie wierszy pasujących do określonych kryteriów podanych w zapytaniu (instrukcja WHERE) i pobranie wartości kolumn dla tych wierszy.Wszystkie typy danych w MySQL mogą być indeksowane.

Początkującym użytkownikom baz danych, bardzo często, najlepszym rozwiązaniem wydaje się tworzenie indeksów dla wszystkich kolumn używanych w zapytaniu. Niestety takie postępowanie prowadzi jedynie do niepotrzebnej utraty miejsca na dysku oraz opóźnień w działaniu MySQL, który traci czas na ustalanie, którego indeksu należy użyć. Praca z bazą danych polega na znalezieniu złotego środka umożliwiającego uzyskanie szybkich odpowiedzi na zapytania przy użyciu optymalnej ilości indeksów.

Użycie klucza głównego

Klucz główny tabeli reprezentuje kolumnę lub zestaw kolumn, które są używane podczas wykonywania najważniejszych zapytań. Indeks ten służy do bardzo szybkiego ich wykonywania. Szybkość tą uzyskuje się dzięki specyficznym właściwościom klucza. Klucz główny nie może bowiem zawierać wartości NULL (ang. NOT NULL optimization). W przypadku użycia silnika InnoDB dane w tabeli są fizycznie uporządkowane w celu uzyskania bardzo szybkiego ich wyszukiwania i sortowania w oparciu o klucz główny.

Jeśli tabela jest duża i ważna, ale nie posiada kolumny lub zestawu kolumn nadających się do użycia jako klucz główny, można utworzyć dodatkową kolumnę z automatycznie przyrastającymi wartościami i użyć jej jako klucza głównego. W przypadku połączenia tabel za pomocą klucza obcego, unikalne identyfikatory klucza głównego mogą służyć jako wskaźniki do odpowiadających im wierszy w innych tabelach.

Użycie klucza obcego

Jeżeli tabela posiada wiele kolumn a użytkownik tworzy wiele zapytań z ich udziałem przeniesienie rzadziej używanych danych do kilku oddzielnych tabel może okazać się bardzo efektywnym rozwiązaniem. Dane te należy połączyć z tabelą główną za pomocą duplikowania kolumny z unikalnym identyfikatorem. W ten sposób każda mniejsza tabela może posiadać własny klucz główny służący do szybkiego przeszukiwania jej zawartości. Takie postępowanie daje możliwość przeszukiwania tylko tych danych, które są dla nas istotne, za pomocą operacji join na wybranych tabelach. W zależności od sposobu w jaki dane są dystrybuowane pomiędzy tabelami zapytanie może wymagać mniejszej ilości operacji I/O i zużywać mniejszą ilość pamięci cache ponieważ dane z odpowiednich kolumny są zapisywane na dysku obok siebie. (W celu maksymalizacji wydajności MySQL podczas odpowiedzi na zapytanie stara się odczytywać jak najmniejszą ilość danych z dysku. Czym mniej kolumn w tabeli tym więcej rekordów mieści się w pojedynczym bloku danych)

Indeks na pojedynczej kolumnie

Najczęściej spotyka się indeksy zakładane na pojedyncze kolumny. W uproszczeniu stworzenie indeksu powoduje przekopiowanie części danych z kolumny w sposób umożliwiający szybkie przeglądanie wierszy odpowiadających wartością w kolumnie. Struktura B-tree pozwala na szybkie odnalezienie konkretnej wartości, zestawu, zakresu co odpowiada operatorom takim jak: =, >, ≤, BETWEEN, IN w instrukcji WHERE.

Max. ilość indeksów jaką można utworzyć dla pojedynczej tabeli jest definiowana dla konkretnego silnika bazy danych. Wszystkie silniki dopuszczają min. 16 indeksów dla pojedynczej tabeli o całkowitej min. długości 256 bajtów. Większość silników ma ustawione większe limity.

Prefiks Indeks

Za pomocą składni col_name(N) można w MySQL stworzyć indeks, który używa tylko pierwszych N znaków z kolumny. Dzięki takiemu rozwiązaniu można stworzyć znacznie mniejszy indeks. Podczas tworzenia indeksu na kolumnie typu BLOB lub TEXT musimy podać długość indeksu. Na przykład:

CREATE TABLE test (blob_col BLOB, INDEX(blob_col(10)));

Prefiks może mieć długość do 1000 bajtów (767 dla tabeli InnoDB). Należy zwrócić uwagę, że max. długość indeksów mierzona jest w bajtach a podczas gdy w instrukcji CREATE TABLE długość należy podać jako liczbę znaków.

Indeks pełnotekstowy

W MySQL dopuszcza się tworzenie indeksów pełnotekstowych (ang. FULLTEXT index) . Są one używane do wyszukiwania pełnotekstowego. Jedynie silniki baz danych InnoDB oraz MyISAM wspierają indeksy pełnotekstowe. Indeksy takie można zakładać na kolumnach typu: CHAR, VARCHAR i TEXT.

Dla niektórych przeszukań pełnotekstowych na pojedynczej tabeli InnoDB w MySQL stosuje optymalizację. Następujące zapytania są najbardziej efektywne:

• zapytania, które zwracają ID dokumentu lub ID dokumentu oraz zakres wyszukiwania.

• zapytania, które zwracają posortowane malejąco rekordy i zakładają klauzulę LIMIT na N pierwszych rekordów. Przy tego typu optymalizacji nie może występować instrukcja WHERE natomiast instrukcja ORDER BY może wystąpić tylko raz i wymuszać sortowanie malejące

• zapytania, które zwracają jedynie wartość COUNT(*) dla rekordów odpowiadających zapytaniu bez użycia frazy WHERE. Instrukcję WHERE należy podać jako WHERE MATCH(text) AGAINST (‚other_text’) bez użycia operatora >0.

Indeks przestrzenny

W MySQL można tworzyć indeksy na przestrzennych typach danych. Obecnie tylko MyISAM wspiera indeksy przestrzenne typu R-tree. Pozostałe silniki baz danych używają indeksów B-tree na potrzeby indeksowania przestrzennego (za wyjątkiem ARCHIVE, który nie wspiera tego typu indeksowania).

Indeks na kilku kolumnach

MySQL może tworzyć indeksy złożone (tj. takie, które obejmują kilka kolumn jednocześnie). Indeks taki może składać się z max. 16 kolumn. Dla niektórych typów danych można tworzyć indeks który używa tylko pierwszych N znaków z kolumny. MySQL może używać indeksów złożonych do zapytań obejmujących wszystkie kolumny indeksu lub tylko ich część. Jeśli podczas definiowania indeksu kolumny zostaną podane w odpowiedniej kolejności pojedynczy indeks złożony może przyspieszać działanie kilku typów zapytań. Indeks oparty na kilku kolumnach może być postrzegany jako posortowana macierz, której wiersze zawierają wartości stworzone poprzez konkatenację wartości poszczególnych kolumn.

Załóżmy, że tabela posiada następującą specyfikację:

CREATE TABLE test ( id INT NOT NULL, last_name CHAR(30) NOT NULL, first_name CHAR(30) NOT NULL, PRIMARY KEY (id), INDEX name (last_name,first_name) );

Indeks name jest indeksem na kolumnach last_name i first_name. Indeks ten może być użyty do zapytań, które obejmują kombinację wartości z obu tych kolumn jak i do zapytań o kolumnę last_name z uwagi na fakt, że została ona używa do budowy „początku” indeksu. (W składni kolumna ta znajduje się najbardziej na lewo). Oto przykład prawidłowego użycia indeksu name:

SELECT * FROM test WHERE last_name=’Widenius’;

SELECT * FROM test WHERE last_name=’Widenius’ AND first_name=’Michael’;

SELECT * FROM test WHERE last_name=’Widenius’ AND (first_name=’Michael’ OR first_name=’Monty’);

SELECT * FROM test WHERE last_name=’Widenius’ AND first_name >=’M’ AND first_name < ‚N’;

Dla takiego zapytania index name nie zostanie użyty:

SELECT * FROM test WHERE last_name=’Widenius’;

SELECT * FROM test WHERE last_name=’Widenius’ AND first_name=’Michael’;

SELECT * FROM test WHERE last_name=’Widenius’ AND (first_name=’Michael’ OR first_name=’Monty’);

SELECT * FROM test WHERE last_name=’Widenius’ AND first_name >=’M’ AND first_name < ‚N’;

Załóżmy, że wykonujemy następujące zapytanie:

mysql> SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;

Jeśli indeks wielokolumnowy istnieje dla col1 i col2 odpowiednie rekordy mogą zostać znalezione bezpośrednio. Jeśli istnieją oddzielne indeksy dla col1 i col2 optymalizator użyje funkcji Index Merge optimization bądź też użyje najbardziej ograniczającego indeksu poprzez sprawdzenie, który indeks wykluczy więcej wierszy.

Bibliografia

• http://dev.mysql.com/doc/refman/5.6/en/optimization-indexes.html – manual MySQL
• Lech Banachowski, Krzysztof Stencel, Systemy zarządzania bazami danych, Wyd. PJWSTK, 2007