Image
18.5.2016 0 Comments

Databázy III / 2.časť

Späť na úvod >> Späť na seriál

V predošlej časti sme si povedali dôležité veci o kľúčoch a indexoch. Teraz vieme, že správne postavenie indexov dokáže podstatne zrýchliť vyhľadávanie dát. Bohužiaľ, klasické indexy sa dali použiť len na jednoznačné dáta. Čo v takom prípade, ak chceme vyhľadávať text v rôznych stĺpcoch, keď presne nevieme, kde sa hľadaný reťazec nachádza alebo aký je presne jeho zápis? Na to sa v moderných databázových strojoch používa tzv. fulltextové vyhľadávanie.
FULL-TEXT INDEX. Fulltextové vyhľadávanie môžeme preložiť ako plnotextové vyhľadávanie, teda vyhľadávanie v celom texte.
Aby sme mohli zrealizovať fulltextové vyhľadávanie, musíme v konkrétnej tabuľke vytvoriť fulltextový index.
Takýto index a vyhľadávanie podporuje MySQL od verzie 3.23.23.
Full-text index sa označuje FULLTEXT a môže byť vytvorený na stĺpce typu VARCHARTEXT.

Jeho vytvorenie je podobné ako pri klasických indexoch, teda použitím v príkaze CREATE TABLE alebo priamo príkazom CREATE INDEX či ALTER TABLE. Ak si spomenieme na zásady, ktoré sme si vysvetľovali minule, môže byť použitie CREATE INDEX alebo ALTER TABLE  omnoho efektnejšie pri rozsiahlych databázach ako vkladanie záznamov do prázdnej tabuľky s už nadefinovaným indexom. Ako vždy správne rozhodnutie záleží na použití aplikácie.

MATCH A AGAINST. Pri zavedení fulltextového indexovania a vyhľadávania sa objavujú aj nové kľúčové slová MATCHAGAINST. MATCH je zároveň aj funkciou. Jej úlohu si vysvetlíme neskôr.
MATCH môžeme voľne preložiť ako porovnanie.
AGAINST môžeme v tomto význame preložiť ako s alebo proti.
Význam týchto spojení si vysvetlíme neskôr na konkrétnom príklade.
VZOROVÝ PRÍKLAD. Vráťme do našej virtuálnej knižnice a majme takúto jednoduchú tabuľku s názvom KNIHY o každej knihe:

id

nazov

opis

Nech obsahuje dva indexy – jeden primárny index na položku id a druhý fulltextový index na položky nazovopis. Je zrejmé, že tento druhý index bude tzv. zložený index. SQL zápis vytvorenia takejto tabuľky potom bude:

CREATE TABLE knihy
          (
            id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
            nazov VARCHAR(50),
            popis TEXT,
            FULLTEXT (nazov,opis)
          )

Na ilustráciu príkladu si naplníme tabuľku KNIHY týmito dátami:

id

nazov

opis

1

MySQL Tutorial

DBMS stands for DataBase Management ...

2

How To Use MySQL Efficiently

After you went through a ...

3

Optimising MySQL

In this tutorial we will show how to ...

4

1001 MySQL Trick

1. Never run mysqld as root. 2. Normalise ...

5

MySQL vs. YourSQL

In the following database comparison we ...

6

MySQL Security

When configured properly, MySQL could be ...

Zápis SQL príkazu bude:

INSERT INTO knihy VALUES
  (0,'MySQL Tutorial', 'DBMS stands for DataBase Management ...'),
  (0,'How To Use MySQL Efficiently', 'After you went through a ...'),
  (0,'Optimising MySQL','In this tutorial we will show how to ...'),
  (0,'1001 MySQL Trick','1. Never run mysqld as root. 2. Normalise ...'),
  (0,'MySQL vs. YourSQL', 'In the following database comparison we ...'),
  (0,'MySQL Security', 'When configured properly, MySQL could be ...')

Je len samozrejmé, že nula na začiatku každého riadka značí automatické pridanie čísla v zmysle autoinkrementácie.
Poďme si teraz vyskúšať fulltextové vyhľadávanie:
Nájdime tie záznamy, kde sa niekde v stĺpcoch nazov alebo opis nachádza text database.
SQL príkaz pre túto požiadavku bude:

SELECT * from knihy WHERE MATCH (nazov,opis) AGAINST (‘database’)

Ako by sme v našom jazyku opísali tento príkaz? Môžeme to skúsiť aj takto: „Vypíš všetko z tabuľky KNIHY, kde v stĺpcoch nazov alebo opis nájdeš porovnanie so slovom ‘database’“.
Funkcia MATCH porovná dopyt v prirodzenom jazyku (alebo logickom – povieme si neskôr) s tvarom za kľúčovým slovom AGAINST. Táto funkcia je case-insensitive, teda nezáleží na veľkosti písmen.
Potom výsledok dopytu SQL, napr. v okne monitora mysql, je podobný výpisu č. 1:

Vidíme výpis dvoch riadkov, ktoré skutočne obsahujú slovo database.
RELEVANCE (SLOV. RELEVANCIA). Pre každý záznam v tabuľke sa vracia tzv. relevance – to je akýsi stupeň podobnosti medzi textom v zázname a v SQL príkaze. Relevance je nezáporné reálne číslo s plávajúcou čiarkou. Nulová relevancia značí žiadnu podobnosť. Relevanciu vypočíta SQL server na základe počtu slov v zázname, počtu jedinečných slov v tomto zázname, celkového počtu slov v sade a počtu záznamov, ktoré obsahujú časť slova.
Ukážme si, ako zistíme relevanciu slova Tutorial:

Zadáme príkaz SQL:
SELECT id, MATCH nazov,opis AGAINST (‘Tutorial’) from knihy

Vieme, že slovo Tutorial sa nachádza v 1. a 3. riadku. V týchto riadkoch funkcia MATCH vypočíta relevanciu podľa uvedeného postupu. Je zrejmé, že číslo relevancie bude pre 1. riadok iné ako pre 3. riadok. Na ostatných riadkoch bude hodnota rovná nule, lebo tam sa slovo nenachádza.
Výsledok vidíme na výpise č. 2:

MySQL používa veľmi jednoduchý parser (= syntaktický analyzátor) na analýzu slov. „SLOVO“ je v tomto prípade postupnosť písmen, číslic a znakov apostrofu a podčiarkovníka. Každé slovo, ktoré je zapísané v zozname „stopword list“ alebo má menej ako 3 znaky, je ignorované. Stopword je slovo, ktoré sa pri fulltextovom vyhľadávaní nezohľadňuje a vyhľadávanie sa ukončí a nevráti sa žiadny záznam.
Každé platné slovo je vážené podľa jeho významu. Teda slovo, ktoré sa nachádza vo viacerých záznamoch, má menšiu váhu ako slovo, ktorého výskyt je vzácny. Váha slov sa použije pri výpočte relevancie.
Zadajme teraz tento príkaz:

SELECT * from knihy where MATCH (nazov, opis) AGAINST (‘MySQL’)

Podľa všetkého predpokladáme, že by sa mali vypísať všetky záznamy, lebo v každom z nich sa toto slovo nachádza. Ale pozrime na výpis č. 3:

Nevrátili sa žiadne záznamy! Prečo?
Slovo MySQL sa nachádza vo viac ako v polovici všetkých záznamov, a tak je v tomto prípade prezentované ako stopword, teda nemá žiadnu sémantickú hodnotu.
Je to vlastne požadované správanie sa SQL stroja, pretože si nevieme predstaviť, keby sa vrátil každý druhý záznam napr. z 1 GB tabuľky! Na podobnom princípe fungujú aj veľké internetové vyhľadávače.
LOGICKÉ FULLTEXTOVÉ VYHĽADÁVANIE. Od verzie 4.0.1 MySQL môže uskutočňovať aj boolean fulltext vyhľadávanie použitím parametra IN BOOLEAN MODE. Jeho úlohou je ovplyvňovať výsledok príkazu SELECT podľa určitých logických operátorov.
Použijeme príkaz SQL:

SELECT id, nazov from knihy WHERE MATCH (nazov, opis)
AGAINST (‘+MySQL -YourSQL’ IN BOOLEAN MODE)

Čo to znamená?
Vypíš (nájdi) stĺpec idnazov z tabuľky KNIHY, kde je možné v stĺpcoch nazov alebo opis nájsť slovo MySQL (znamienko +), ale nesmie sa tam nachádzať slovo YourSQL (znamienko -).
Výsledok je na výpise č. 4:

Vidíme, že sa vrátili všetky záznamy obsahujúce slovo MySQL a neobsahujúce slovo YourSQL. Záznam číslo 5 túto podmienku nespĺňa, a preto bol z výsledku vyradený.
Aj keď sa slovo MySQL nachádza vo viac ako 50 % záznamov, toto obmedzenie sa tu nevyužilo, lebo slovo YourSQL túto obmedzujúcu podmienku 50 % nespĺňa!

Operátory boolean fulltextového vyhľadávania
V zápise za kľúčovým slovom AGAINST je možno používať tieto logické operátory:

“+”       Plus pred slovom znamená, že toto slovo sa musí nachádzať vo vrátených záznamoch
“-”        Mínus pred slovom znamená, že toto slovo sa nesmie nachádzať vo vrátených záznamoch
“<>”     Tieto dva operátory sú používané na zväčšovanie alebo zmenšovanie relevačnej hodnoty slova. Pozri príklad ďalej
“( )”      Zátvorky sú použité ako obyčajne na združovanie výrazov
“~”       Tilda operátor je negátor. Používa sa pri označovaní rušivých slov. Záznam, ktorý obsahuje takto označené slovo, má menšiu hodnotu ako ostatné, ale nie je vylúčený z vyhľadávania ako pri operátore mínus
“*”        Zrezanie slova – poznáme ako wildcards – divoké znaky

A tu je pár príkladov:
“auto motor”     nájde záznamy, ktoré obsahujú najmenej jedno z týchto slov
“+auto +motor” nájde záznamy, ktoré obsahujú obidve slová
“+auto motor”   nájde slovo auto, ale hodnota relevancie je vyššia, ak záznam obsahuje aj slovo motor
“+auto -motor” nájde záznamy, ktoré obsahujú slovo auto, ale neobsahujú motor
“+auto +(>motor <brzda)” záznam môže obsahovať túto kombináciu: auto a motor, alebo auto a brzda, ale auto motor má väčšiu hodnotu relevancie ako auto brzda
“auto*” možnosti sú: autobus, automat, autor....

Obmedzenia pri fulltextovom vyhľadávaní
Ako všetko aj fulltextové vyhľadávanie má určité obmedzenia:

  • všetky parametre pri funkcii MATCH musia byť stĺpce z tej istej tabuľky okrem použitia IN BOOLEAN MODE
  • zoznam stĺpcov medzi MATCHAGAINST musí byť definovaný ako FULLTEXT index okrem použitia IN BOOLEAN MODE
  • argument v AGAINST musí byť konštantný reťazec

Vylaďovanie fulltextového vyhľadávania
Nanešťastie fulltextové vyhľadávanie má zatiaľ iba niekoľko nastavovacích parametrov. Vylaďovanie je možné počas prekladu zo zdrojových kódov MySQL.
Tu je niekoľko možných nastavení:

  • minimálna dĺžka slova pre indexáciu je definovaná v premennej ft_min_word_lenght
  • zoznam stopwordov je definovaný v súbore myisam/ft_static.c
  • prah 50 % obmedzenia je možné vypnúť v súbore ftdefs.h zamenením položky
  • #define GWS_IN_USE GWS_PROB
  • na
  • #define GWS_IN_USE GWS_FREQ a rekompilovaním MySQL
  • keby sme potrebovali zmeniť operátory použité pri logickom vyhľadávaní, nájdeme ich definované v premennej ft_boolean_syntax v súbore ft_static.c

Čo sa chystá do budúcnosti
V najbližších verziách programu MySQL sa v oblasti fulltextového vyhľadávania očakávajú tieto zlepšenia:

  • všetky operátory s FULLTEXT indexom budú rýchlejšie
  • zavedie sa tzv. frázové vyhľadávanie a príbuznosť operátorov
  • logické (boolean) vyhľadávanie bude môcť fungovať aj bez FULLTEXT indexu
  • podpora „vždy indexovaných“ slov – to môžu byť reťazce, ktoré používateľ bude chcieť spracovať ako slová, napr. C++, AS/400, TCP/IP a iné
  • podpora fulltextového vyhľadávania v tabuľkách MERGE
  • podpora pre mnohobajtové znakové sady
  • možnosť tvorby zoznamu stopword listu v závislosti od jazyka dát
  • možnosť tvorby UDF preparsera
  • viac flexibility

Ak hľadáte vhodnosť použitia fulltextového indexovania a vyhľadávania, asi najideálnejší príklad je v spojení s inter-/intranetom.
Uvediem príklad z praxe:
Dennodenne zbierame príspevky z rôznych emailových konferencií. Tie sa potom spracúvajú rôznymi programami a ukladajú do MySQL databázy. Používateľ na klientskej stanici môže spustiť program, ktorý dokáže v tejto databáze vyhľadávať tie záznamy, ktoré obsahujú požadované slovo. Keďže sa tu nachádzajú informácie v textovej podobe a je potrebné vyhľadávať v celom texte, práve tu sa uplatní fulltextové vyhľadávanie.
No nesmieme zabudnúť, že fulltext index je pomerne nová vlastnosť MySQL, a preto nemusí hneď splniť naše očakávania.
Isto poznáte situáciu, keď máme určitú tabuľku s dátami, ku ktorej pristupuje naraz niekoľko používateľov tak, že jeden chce iba čítať, ale ten druhý chce zapísať nové dáta do tabuľky. Ako to urobiť, aby nedošlo k nejednoznačnosti dát? Na to slúži systém zamykania.

Ale ten si vysvetlíme nabudúce.

 

Zobrazit Galériu

Nechajte si posielať prehľad najdôležitejších správ emailom

Mohlo by Vás zaujímať

Ako na to

Ako zbaviť fotky hmly

08.12.2016 11:59

Hmla alebo dym sú často veľmi kreatívne nástroje. No všetkého veľa škodí. Fotka potom stráca kontrast a v podstate na nej nič nevidieť. Hmlu môžete neraz následnými úpravami odstrániť alebo zredukovať ...

Ako na to

Užitočné SW nástroje

08.12.2016 11:53

AllDup v4.0.3 Určenie: program na vyhľadávanie a odstraňovanie duplicitných súborov Vlastnosti: duplicitné súbory sa vyhľadávajú len na zvolených diskových jednotkách alebo len v rámci vybraných ...

Ako na to

Fotografovanie s bleskom

08.12.2016 11:47

Ak máte moderný fotoaparát so vstavaným alebo externým bleskom, zdá sa vám téma článku triviálna. Jednoducho nastavíte vhodný režim, vyberiete najlepšiu kompozíciu záberu, exponujete a o zvyšok sa už ...

Žiadne komentáre

Vyhľadávanie

Kyocera - prve-zariadenia-formatu-a4-s-vykonom-a3

Najnovšie videá