sysops.it

Outsourcing IT pozwoli ci prowadzić biznes taniej

info@sysops.it
+48 666 930 111

Nowości z bloga

TCP Fast Open

Opis modyfikacji stosu TCP, która może przyspieszyć prędkość ładowania popularnych stron od 4% do 41%.

Wyszukiwarki i Sphinx

Słowo wstępu o Sphinksie - silniku wspierającym wyszukiwanie pełnotekstowe.

Kwestia IPv6 NAT w Linuksie

IPv6 NAT w Linuksie - walka ideologii z rzeczywistością.

Fulltext index

Fulltext index

Mechanizm indeksu pełnotekstowego w MySQL-u nie jest optymalnym rozwiązaniem. Zawsze lepiej jest zastosować zewnętrzne mechanizmy indeksujące, np. Sphinx. Jest to jednak materiał na osobny artykuł, z drugiej strony warto znać narzędzia, które są wbudowane w używany silnik bazy danych.

Indeksy cię uratują

Przede wszystkim stara prawda o indeksach: pomagają. Indeksy to mechanizm, który pozwala zoptymalizować zapytania do bazy danych, a co za tym idzie, całą aplikację WWW. Indeks to nic innego jak skorowidz, po którym baza danych w szybki sposób odnajduje informacje, o które jest pytana. Możesz docenić jego znaczenie, gdy poszukując hasła Kilimandżaro w encyklopedii od razu możemy sięgnąć po tom z literą K, zamiast przeczesywać opasłe księgi od pierwszego tomu. Mało tego startujemy z przeszukiwaniem haseł od części tomu z wpisami na Kil, pomijając te rozpoczynające się od Kac (dzięki Bogu!) właśnie dzięki indeksowi w encyklopedii.

Przy braku indeksów to właśnie bazę danych MySQL zaprzęgamy do tak katorżniczej pracy, jaką jest czytanie encyklopedii hasło po haśle od pierwszej strony!

Sądzę, że skutecznie przedstawiłem znaczenie samych indeksów. Wszystko jest pięknie, gdy zwykły indeks (te omówię szerzej w przyszłości) zostanie utworzony w polu z ograniczoną liczbą znaków. Na to pozwala MySQL. Można więc tworzyć indeksy w ramach pól SMALLINT(4), INT(10), VARCHAR(128). Dobrze, jak zatem wyszukiwać sprawnie informację np. po polu TEXT, gdzie nie ma ograniczeń odnośnie długości wpisu w tym polu?

Tutaj z pomocą przychodzi indeks pełnotekstowy, czyli FULLTEXT INDEX.

Podstawowa właściwość - indeks pełnotekstowy można założyć wyłącznie na bazie opartej o MyISAM.

Pełnotekstowa wyszukiwarka domowej roboty

Wyobraźmy sobie, że szukamy słowa człowiek w bazie z cytatami. Potrzebujemy znaleźć wszystkie rekordy z tym słowem. Przykładowo poprzez zapytanie:

SELECT * FROM cytaty WHERE tresc LIKE '%człowiek%';

Jest ono niefunkcjonalne (o tym, że jest nieoptymalne opowiem za chwilę). Po pierwsze wyszukuje inne słowa np. człowiekiem, nadczłowiek, człowieku.... Właśnie, człowieku! Co teraz?

Pierwszy przebłysk - niekoniecznie geniuszu - podpowiada dodanie spacji:

SELECT * FROM cytaty WHERE tresc LIKE '% człowiek %';

Świetnie, znajduję teraz słowo człowiek w środku tekstu. Nie znajdę go wtedy, gdy cytat rozpoczyna się od wyrazu człowiek, pomijam też wszystkie wystąpienia tego słowa w złączeniu ze znakami interpunkcyjnymi począwszy od przecinka przez kropki, średniki, wykrzykniki, skończywszy na znaku zapytania... który domaga się odpowiedzi - co dalej?

Można kombinować i wyrazić się bardziej precyzyjnie poprzez mnożenie warunków w części WHERE np. w taki sposób:

SELECT * FROM cytaty WHERE tresc LIKE 'człowiek %' OR tresc LIKE '% człowiek %' OR tresc LIKE '% człowiek, %' OR tresc LIKE '% człowiek; %' OR tresc LIKE '% człowiek.%' OR tresc LIKE '% człowiek!%' OR tresc LIKE '% człowiek?%';

Nie jest to jednak dobre rozwiązanie. Przede wszystkim używam tzw. podwójnych LIKE'ów, czyli wyrażenia LIKE, które z obydwu stron zawiera wildecard - nieszczęsny znak procenta. Mało tego, że go używam, robię to wielokrotnie. Ponadto na pewno pominąłem niektóre cytaty, które np. rozpoczynają się od frazy Człowiek? […] - ile jeszcze wpisów potrzebnych jest w tej nieoptymalnej metodzie? Szkoda liczenia.

Przeprowadziłem test zapytania na próbce ponad 10 tys. rekordów. Oto jego wytłumaczenie zapytaniem EXPLAIN (szerzej do omówienia też w przyszłości):

+--------+------+---------------+------+---------+-------+ | table | type | possible_keys | key | key_len | rows | +--------+------+---------------+------+---------+-------+ | cytaty | ALL | NULL | NULL | NULL | 14784 | +--------+------+---------------+------+---------+-------+

Mam więc w tabeli 14784 rekordy i przeszukując ją w ramach utworzonego zapytania zaprzęgam je wszystkie do pracy. Brakuje też informacji o możliwych do użycia indeksach (possible_keys), a co za tym idzie brak również użytego indeksu. Jedno takie zapytanie na lokalnym komputerze wykona się zanim zdążę mrugnąć okiem. Problemy zaczną się, gdy uruchomię je np. na hostingu współdzielonym, a dane rozrosną się do setek tysięcy rekordów. Warto od początku myśleć przyszłościowo.

Tworzenie prostego FULLTEXT INDEX

Stworzę zatem indeks dla kolumny typu TEXT o nazwie tresc w tabeli cytaty. Wybieram oczywiście FULLTEXT INDEX, ponieważ nasza kolumna jest typu TEXT, przy którym z uwagi na brak określonej długości pola oraz długie wpisy nie opłaca się nam użycie zwykłego indeksu:

CREATE FULLTEXT INDEX tresc_index ON cytaty (tresc);

Utworzyłem pełnotekstowy indeks na polu tekstowym. Mogę zatem zapytać bazę o żądane rekordy w dużo prostszy sposób:

SELECT * FROM cytaty WHERE MATCH(tresc) AGAINST('człowiek');

Właściwości indeksu FULLTEXT

Kilka słów wyjaśnienia. FULLTEXT INDEX to mechanizm pozwalający na wyszukiwanie w języku naturalnym. Nie martwię się zatem o przecinki, kropki i znaki zapytania. Upraszcza się samo zapytanie, jak i skrypt PHP, który takie zapytanie buduje. Domyślnie słowo powinno składać się min. z 4 znaków. Dostępne jest zapytanie:

SHOW VARIABLES;

Pozwala ono m.in. sprawdzić, jak aktualnie ustawione są parametry określające ilość dozwolonych znaków. Odpowiadają za to następujące wpisy (w przypadku mojego serwera):

ft_max_word_len 84 ft_min_word_len 3

Na moim serwerze takie słowo może powinno zawierać min. 3 znaki, natomiast maks. 84 znaki. Parametry te do ustawienia są w pliku konfiguracyjnym MySQL.

Wracając do samego mechanizmu FULLTEXT INDEX, jest szereg słów, które automatycznie są wykluczane z zapytania m.in. "between", "cause", "many", "say", "where". Słów jest znacznie więcej. Pełna lista dostępna jest na dev.mysql.com.

Jeśli wyszukiwane słowo znajdzie się w ponad 50% rekordów również zostanie pominięte.

Jest to o tyle istotne, jeżeli utworzysz sobie testową tabelę z 2 rekordami i spróbujesz wyszukać poprzez FULLTEXT INDEX dane słowo, zakończy się to zwróceniem pustego wyniku, dopóki nie dodasz więcej zróżnicowanych wpisów. Ważne jest, aby pamiętać, że 50% rekordów to granica co do ilości wpisów zawierających dane słowo. Osiągnięcie lub przekroczenie tej granicy spowoduje, że słowo w zapytaniu zostanie pominięte.

Do tego tematu powrócę jeszcze dzisiaj.

FULLTEXT INDEX w użyciu

Pozwoliłem sobie zatem wydać nasze nowe zapytanie do bazy danych, oto efekt:

+--------+----------+---------------+-------+---------+------+ | table | type | possible_keys | key | key_len | rows | +--------+----------+---------------+-------+---------+------+ | cytaty | fulltext | tresc | tresc | 0 | 1 | +--------+----------+---------------+-------+---------+------+

Powyższe wyjaśnienie zapytania wygląda dużo korzystniej, nie straszy liczbami rzędu tysięcy rekordów, odwołuje się do ineksów oraz pozwala podejrzewać, że użyte rozwiązanie w danej sytuacji jest optymalne.

Mogę wyszukiwać więcej słów. Zakładam, że interesują nas cytaty ze słowami człowiek i miłość. Zwykłe dodanie kolejnego słowa kluczowego po przecinku nie pozwoli osiągnąć tego efektu:

SELECT * FROM cytaty WHERE MATCH(tresc) AGAINST('człowiek, miłość');

Przyczyną jest wyszukiwanie słów w takiej formie z łącznikiem OR (lub). W powyższej postaci znajdę więc cytaty zawierające słowo człowiek lub miłość, co zamiast zawęzić ilość wyników, poszerzy nam zakres wyszukiwania.

Zawężanie wyniku, czyli IN BOOLEAN MODE

Z pomocą przychodzi parametr IN BOOLEAN MODE, gdzie można ustalać, które słowa powinny bezwzględnie znaleźć się w wyniku, a także pojawienie się których słów taki wynik deklasyfikuje z końcowego rezultatu:

SELECT * FROM cytaty WHERE MATCH(tresc) AGAINST('+człowiek, +miłość' IN BOOLEAN MODE);

Jest to proste dodanie frazy IN BOOLEAN MODE na koniec części AGAINST oraz określenie przed każdym słowem kluczowym, czy jest wymagane, czy wykluczające. Gdy nie interesują mnie cytaty ze słowem dziecko, rozwiązanie jest już bardzo proste:

SELECT * FROM cytaty WHERE MATCH(tresc) AGAINST('+człowiek, +miłość, -dziecko' IN BOOLEAN MODE);

Przykłady można mnożyć, jednak zamiast tego parę słów o jeszcze jednym parametrze, WITH QUERY EXPANSION.

Rozszerzanie rezultatu, czyli WITH QUERY EXPANSION

Wspominałem, że może zdarzyć się, iż ilość zwróconych w wyniku rekordów przekroczy 50% wszystkich rekordów w tabeli. Wóczas dane słowo kluczowe zostanie pominięte. Jednakże co się dzieje, gdy rezultat zapytania zawiera zbyt mało informacji? Znowu "ręcznie" można analizować wynik, zmieniać zapytania, tworzyć powiązania między kolejnymi frazami. Rozrośnie nam się kod PHP, zwiększymy też ilość zapytań do bazy danych - nie o to jednak chodzi. Mechanizm FULLTEXT INDEX znów przychodzi nam z pomocą za sprawą parametru WITH QUERY EXPANSION.

Gdy zostanie on dodany do zapytania, przykładowo:

SELECT * FROM cytaty WHERE MATCH(tresc) AGAINST('człowiek' WITH QUERY EXPANSION);

Wówczas oprócz rekordów zawierających słowo człowiek zostaną zwrócone również te rekordy, które zawierają słowa najczęściej występujące w wyniku wyszukiwania słowa człowiek. W przypadku niewielkiej ilości wyników pierwszego zapytania (bez WITH QUERY EXPANSION) możemy zaproponować poszerzenie rezultatu właśnie o taki mechanizm. Być może internauta odnajdzie interesujące treści wśród rekordów, które mogą być powiązane z wydanym zapytaniem.

Podsumowanie

Nie jest to wszystko, co można opowiedzieć o indeksowaniu pełnotekstowym w bazach MySQL. Przygoda z optymalizacją nie polega jednak na tym, aby całość zawrzeć na jednej stronie. Wkrótce postaram się powrócić do tego interesującego tematu. Przede wszystkim warto poruszyć kwestię łącznia indeksów pełnotekstowych ze zwykłymi, zapytania złożone, zapytania wildecard oraz bardziej złożone przykłady, na które tutaj zabrakło miejsca.

Ciąg dalszy znajdziesz w artykule o wyszukiwarkach i Sphinksie.

Indeks pełnotekstowy
Dodano dn. 11 listopada 2012 roku przez rado.