MySQL via shell PART VII (THE LAST ONE): OPTYMALIZACJA WYSZUKIWANIA (indeksowanie baz danych plus parę przydatnych ciekawostek)

Hejo!

Często i gęsto pracować będziemy na bazach danych zawierających mnóstwo informacji. Nie bez kozery jest fakt, że typowa, w miarę niewielka baza danych składa się z 500k rekordów :) Za pomocą języka zapytań jakim bez wątpienia jest MySQL możemy w szybki i konkretny sposób 'wyciągać' z niej potrzebne nam informacje, edytować je, wstawiać, podmieniać etc. za pomocą konkretnych poleceń.

Warto jednak wiedzieć o możliwości indeksowania danych. Działa to na zasadzie podobnej do książki telefonicznej, gdzie alfabetycznie mamy indeksowane wszystkie nazwiska. Za pomocą języka zapytań możemy również nadać takie indeksy naszej bazie danych, co zazwyczaj znacząco wpływa na prędkość wyszukiwania poszczególnych informacji. Niby prędkość z naszego punktu widzenia znacznie nie wzrasta ale kiedy serwer działa pod sporym przeciążeniem to każdy uzyskany % ogólnej szybkości jest na wagę złota : )

Oto o co chodzi w tym całym indeksowaniu. Posłużymy się bazą danych wygenerowaną zupełnie losowo i automatycznie :) Jak wcześniej wszelkie operacje będziemy wykonywać za pomocą powłoki systemowej, tak, byśmy wiedzieli co tam się tak naprawdę dzieje i dlaczego.

==========================

Zaczynając indeksowanie warto przeliczyć ilość rekordów, jakie posiada nasza tabela. Służy do tego polecenie count(1) o którym już wspominaliśmy wcześniej:

select count(1) from duzatabelka;

Kolejnym sposobem na uzyskanie informacji będzie wywołanie statusu tabeli. Robimy to za pomocą polecenia:

show table status;

my jednak do tego zapisu dodamy końcówkę ze slashem ;)

show table status\G;

oznacza to, że wszelkie kolejne wyniki będą zapisywane przejrzyściej, na zasadzie zapisu wierszowego (chociaż to może kwestia gustu co jest bardziej przejrzyste). Za pomocą tego polecenia uzyskamy nie tylko info odnośnie ilości rekordów ale np. odnośnie silnika, na jakim "hula" nasza baza danych etc.

Wychodzi na to, że nasza jest całkiem spora i zawiera prawie 550 tyś rekordów. Warto w tym przypadku zastanowić się nad tym, czy chcemy np. przy pomocy powłoki systemowej wyciągać wszystkie informacje z tej tabeli. Jasne, jet to możliwe ale zalew informacji, jaki będzie przetwarzać się na naszych oczach będzie troszkę przypominać matrixowy motyw z zielonymi zero-jedynkami :) W takich przypadkach musimy już umiejętnie wyciągać nasze informacje, a przede wszystkim musimy wiedzieć co chcemy wyciągnąć :)

CIEKAWOSTKA!

Kolejną ciekawą funkcją jest możliwość poznania polecenia za pomocą którego była tworzona nasza tabela.

show create table nazwa_tabeli;

Możemy również zapytać MySQL o to, aby wyjaśnił nam, jakie operacje przeprowadza podczas naszych zapytań. Wystarczy, że przed interesującym nas zapytaniem wpiszemy komendę explain (z ang. wyjaśnij):

explain select * from nazwa_tabeli where id=x;

Okay! Jak zoptymalizować szukanie informacji w tabeli? Tak, jak napisaliśmy wcześniej - wystarczy założyć indeksy.

Tworzenie indeksów wygląda następująco:

create index nazwa_indeksu on nazwa_tabeli (nazwa_kolumny_jaka_indeksujemy);

Jeżeli chcemy usunąć indeks używamy komendy:

drop index nazwa_indeksu on nazwa_tabeli;

Zwróćcie proszę uwagę na fakt, że operacja ta będzie trwać troszkę dłużej i będzie dwuetapowa. Logicznym jest, że czas trwania takiej operacji będzie zależny od paru czynników (wielkość bazy danych, przepustowość serwera etc.). Co najważniejsze to fakt, że właśnie założyłem indeks na kolumnie 'cena'. Od teraz wszelkie zapytania odnośnie tej kolumny będą realizować się szybciej. Co za tym idzie, właśnie ZOPTYMALIZOWALIŚMY WYSZUKIWANIE : )

Warto zakładać indeksy na kolumny po których sami wyszukujemy dane. Można jednocześnie założyć indeks na parę kolumn w tabeli ale ważne jest to, aby zachować taką samą kolejność nadawania indexów dla poszczególnych kolumn, w jakiej są one ułożone w naszej tabeli! 
Wtedy indexy będziemy tworzyć w taki sposób:

create index nazwa_indexu on nazwa_tabeli(nazwa_kolumny_na_index_1, nazwa_kolumny_na_index_2);

Należy pamiętać, że indexów nie można zakłądać na polach opisanych zmiennymi BLOB/TEXT. Można jednak używać wyszukowania FULLTEXTowego! Wtedy takie zapytanie będzie wyglądać:

create fulltext index nazwa_indexu on nazwa_tabeli (nazwa_kolumny_po_jakiej_indeksujemy);

Indexy fulltextowe to jedne z najszybszych indexów (wykonują się praktycznie w 0 sekund!)!. Jest z nimi tylko jeden haczyk.. Indeksy takie należy odpytywać w odpowiedni sposób! W jaki? Oto przykład:

select id from duzatabelka where match(nazwa_przeszukiwanej_kolumny) against ('nazwa_szukanego_pola');

Jak sami widzicie, wyszukiwanie wśród prawie 550 tyś opisów zajęło całe 0 sekund : ) Bardzo przydatna rzecz, imho warto o niej wiedzieć.

=======================

Przepraszam za to że ostatnio posty ukazują się rzadziej. Związane jest to z moim przygotowaniem do egzaminu ISTQB, którego będę się podejmować w najbliższym czasie. Dodatkowo kończę doktorat, plus redaguję stale swoje CV, a sam uprawiam jeszcze 4 x w tygodniu pływanie oraz staram się utrzymywać wartościowe dla mnie znajomości.

Po osiemnastym października wznowię częstsze nadawanie postów ale na ten moment chcę zaznaczyć, że to będzie konieć z MySQL tutaj. Myślę, że przekazałem sporo podstawowej i mocnej wiedzy, reszty i tak musicie nauczyć się poprzez doświadczenie, a praca, do jakiej się dostaniecie ukierunkuje Was w zaawansowanym MySQL w zależności od samej specyfiki roboty, jaką będziecie wykonywać.

Teraz chciałbym zająć się PHP ale żeby dobrze zasiąść do PHP chciałbym zrobić szybkie przypomnienie html, bez którego znajomości imho nie ma sensu zabierać się za ten język programowania.


Pozdrawiam serdecznie, trzymajcie kciuki na egzaminie ^_^

F.