InnoDB Fulltext search

Con la versione MySQL 5.6 sono stati introdotti gli indici Fulltext anche per InnoDB. Parliamone!

Utilizzo

La funzionalita' di fulltext search (FTS) e' presente da sempre sull'Engine MyISAM e, dalla release 5.6, e' disponibile anche con InnoDB.
Come usarla? Vediamo un esempio!

CREATE TABLE articoli (id SERIAL, occhiello VARCHAR(128), titolo VARCHAR(64), sommario VARCHAR(256), catenaccio VARCHAR(64), corpo TEXT) ENGINE=InnoDB; INSERT INTO articoli(titolo, corpo) VALUES ('MySQL 5.6', 'Con la nuova versione del database MySQL sono finalmente...'); CREATE FULLTEXT INDEX idx_ft on articoli (corpo); SELECT occhiello, titolo, corpo FROM articoli WHERE MATCH (corpo) AGAINST ('database');

Con la query dell'esempio si cerca la parola "database" nel corpo di tutti gli articoli: semplice!

Riassumendo molto: nella ricerca naturale basta utilizzare la clausola MATCH nelle query indicando la colonna e la parola da ricercare. Nel 90% dei casi basta questo tipo di ricerca... ma non sempre!

Possono essere definiti indici FTS su una o piu' colonne (naturalmente di tipo testo): la ricerca delle parole chiave viene svolta su tutte le colonne dichiarate nell'indice. Le colonne utilizzate nella clausola MATCH debbono corrispondere a quelle definite nell'indice. In caso contrario viene restituito l'errore:
 ERROR 1191 (HY000): Can't find FULLTEXT index matching the column list

Se serve effettuare ricerche su piu' campi basta definire l'indice su tutti i campi desiderati:

CREATE FULLTEXT INDEX idx_ft on articoli (titolo, corpo); SELECT occhiello, titolo, corpo FROM articoli WHERE MATCH (titolo, corpo) AGAINST ('database');

E' tuttavia consigliabile utilizzare un solo indice con lo stesso elenco di colonne, anche perche' un indice FTS e' molto piu' pensante da mantenere rispetto ad un classico indice B-Tree.

Una variante della clausola MATCH e' quella di utilizzare il boolean mode che consente di effettuare ricerche piu' complesse. Ad esempio con
 AGAINST ('+database -nosql' IN BOOLEAN MODE)
si cercano gli articoli che contengono la parola database ma non la parola nosql.
Sono molti gli operatori specificabili per la ricerca booleana:

Come sempre in MySQL il tipo di ricerca dipende dalla collation utilizzata sulla colonna ricercata: latin1_swedish_ci (il default in molti casi) e' case-insensitive mentre latin1_bin e' case-sensitive...
Per i piu' completi character set come utf8 o, meglio, utf8mb4 vi sono decine di collation disponibili tra cui ad esempio, rispettivamente non-sensibili e sensibili al maiscolo: utf8_general_ci e utf8_bin.
E' quindi molto importante definire correttamente la collation delle colonne su cui effettuare le ricerche altrimenti non funzioneranno; o meglio funzioneranno perfettamente... ma non nel modo desiderato!

La clausola MATCH, oltre che nella WHERE, puo' essere utilizzata anche nella SELECT e restituisce il valore di confidenza di ogni risultato trovato:

SELECT titolo, corpo, MATCH (corpo) AGAINST ('+database -MySQL' in boolean mode) as punteggio FROM articoli ORDER BY punteggio desc; +------------+-------------------------------------------------+----------------------------+ | titolo | corpo | punteggio | +------------+-------------------------------------------------+----------------------------+ | MySQL 5.6 | Con la nuova versione del database MySQL sono...| 1.0310081243515015 | | MySQL 5.7 | La versione 5.7 del database Open Source MySQ...| 1.0310081243515015 | ...

In questo modo i risultati possono essere ordinati per rilevanza come in una ricerca su Google!

Facile vero?
Se servono ulteriori dettagli la documentazione online e' molto completa.

Un poco piu' complesso e' capire come funzionano gli indici FULLTEXT... per questo bisogna capirne la struttura.

Strutture dati

Gli indici FULLTEXT esistono da sempre in MySQL [NdA in realta' dalla versione 3.23.23] ma erano disponibili solo per l'Engine MyISAM. Dalla versione MySQL 5.6 sono disponibili anche per InnoDB che e' l'Engine transazionale e nel seguito vedremo solo questa implementazione.

Una tabella InnoDB e' rappresentata dal file .frm (che contiene la definizione della tabella per tutti gli Engine) e dal tablespace dei dati contenuto nel file .ibd (nel caso di configurazione innodb_file_per_table, che e' il default).

Gli indici piu' adatti per le ricerche di tipo fulltext sono gli inverted index: in pratica si estraggono tutte le parole piu' lunghe di innodb_ft_min_token_size (default=3) e per ciascuna si crea la lista delle occorrenze. Le ricerche delle parole chiave sono quindi gia' pronte nell'indice fulltext e l'ottimizzatore deve solo raccogliere le altre condizioni della query.
Quando si creano indici di tipo fulltext search (FTS) in InnoDB la struttura viene arricchita da nuovi tablespace che possono essere ricercate con la query:

SELECT table_id, name, space FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE name LIKE '%FTS%';

E corrispondono ai seguenti file: 

-rw-rw----   1 mysql  mysql       4507 Feb 14 15:16 articolo.frm
-rw-rw----   1 mysql  mysql     114688 Feb 14 15:16 arcicolo.ibd
-rw-rw----   1 mysql  mysql      98304 Feb 14 15:16 FTS_0000000000000020_DELETED_CACHE.ibd
-rw-rw----   1 mysql  mysql      98304 Feb 14 15:16 FTS_0000000000000020_DELETED.ibd
-rw-rw----   1 mysql  mysql      98304 Feb 14 15:16 FTS_0000000000000020_CONFIG.ibd
-rw-rw----   1 mysql  mysql      98304 Feb 14 15:16 FTS_0000000000000020_BEING_DELETED_CACHE.ibd
-rw-rw----   1 mysql  mysql      98304 Feb 14 15:16 FTS_0000000000000020_BEING_DELETED.ibd
-rw-rw----   1 mysql  mysql      98304 Feb 14 15:16 FTS_0000000000000020_0000000000000023_INDEX_6.ibd
-rw-rw----   1 mysql  mysql      98304 Feb 14 15:16 FTS_0000000000000020_0000000000000023_INDEX_5.ibd
-rw-rw----   1 mysql  mysql      98304 Feb 14 15:16 FTS_0000000000000020_0000000000000023_INDEX_4.ibd
-rw-rw----   1 mysql  mysql      98304 Feb 14 15:16 FTS_0000000000000020_0000000000000023_INDEX_3.ibd
-rw-rw----   1 mysql  mysql      98304 Feb 14 15:16 FTS_0000000000000020_0000000000000023_INDEX_2.ibd
-rw-rw----   1 mysql  mysql      98304 Feb 14 15:16 FTS_0000000000000020_0000000000000023_INDEX_1.ibd

Poiche' gli inserimenti, ed ancora di piu' le cancellazioni, sono molto pesanti da gestire gli indici FTS non vengono ricalcolati ogni volta ma solo periodicamente. A volte quindi c'e'... qualche problema.

Problemi? Parliamone!

Questa sezione e' introdotta dal vostro psicologo Rogersiano...
- Ci sono problemi con gli indici fulltext?
- Tu pensi che ci siano problemi?
- Non saprei, intendo quelli introdotti nella 5.6 su InnoDB
- Dici di non saperlo per una ragione particolare?
- Non ho avuto il coraggio di utilizzarli all'inizio
- Questo e' piuttosto interessante

In effetti inizialmente era sconsigliabile utilizzare gli indici FTS InnoDB su tabelle di dimensioni significative perche' venivano allocati sulla tablespace di sistema [NdE problema risolto dalla 5.6.20].

Ovviamente piu' indici si utilizzano su una tabella piu' lente sono le operazioni di DML, questo vale a maggior ragione per gli indici fulltext.
Poiche' le operazioni di DML sono piu' pesanti con i FULLTEXT che con i B-TREE, a fronte di pesanti modifiche su una tabella e' spesso consigliabile rimuovere gli indici FULLTEXT e poi ricrearli a termine dei caricamenti.
MySQL utilizza una serie di tecniche per ridurre per ottimizzare gli indici FTS: gli inserimenti vengono effettuati solo al momento del commit ed in caso di DELETE non viene aggiornata la chiave ma segnato come cancellato il documento.

Chi opera sui sistemi documentali sa bene che sono necessarie periodiche attivita' di ricostruzione degli indici... lo stesso puo' avvenire con un FTS su InnoDB: utilizzando una normale OPTIMIZE TABLE, operazione standard su MySQL, l'indice viene ricostruito.
In realta' gli indici FTS possono essere ricostruiti solo in parte da un'OPTIMIZE: ma basta insistere chiamandola piu' volte! [NdE il numero di token analizzati dall'optimize dipende dal parametro innodb_ft_num_word_optimize, il cui default e' 2000].

Mi e' capitato qualche errore come:
 InnoDB: Error (Duplicate key) writing word node to FTS auxiliary index table
che indica una corruzione nelle strutture dell'indice.
Per risolvere la sequenza corretta di operative, dalla meno invasiva e breve a quella piu' incerta, e' la seguente: OPTIMIZE TABLE, REPAIR TABLE, Dump&Restore, aprire una SR (Service Request), accendere un cero, passare a...
Saro' stato fortunato... ma mi e' sempre bastato solo il primo passo lanciato un paio di volte.

Altre domande? Leggete la documentazione ufficiale!

Varie ed eventuali

Il parser di default utilizza gli spazi ed i caratteri di interpunzione per separare le parole... Naturalmente va bene nella maggioranza dei casi pero' per alcuni linguaggi (eg. giapponese) questo e' una limitazione perche' igiapponesiscrivonotuttoattaccato. Per superare tale limite la versione 5.7 ha aggiunto due parser Full-Text specifici: ngram e MeCab.

E' possibile modificare il comportamento delle ricerche fulltext con alcuni parametri di tuning... ma e' fortemente sconsigliato se non in casi molto particolari. Alcune modifiche richiedono il riavvio del server, altre la ricostruzione di tutti gli indici, inoltre, se non si sa esattamente quello che si sta facendo, e' molto piu' facile peggiorare le prestazioni o la qualita' delle ricerche che migliorare qualcosa.
Per evitare complessita' quindi non vi diro' che esistono i parametri o le posdibilita' di configurare innodb_ft_min_token_size, innodb_ft_max_token_size, innodb_ft_server_stopword_table, innodb_ft_user_stopword_table, innodb_ft_enable_stopword, ngram_token_size, sql/share/charsets, INNODB_FT_DEFAULT_STOPWORD, INNODB_FT_DELETED, ...

Titolo: InnoDB Fulltext search
Livello: Avanzato (3/5)
Data: 14 Febbraio 2017
Versione: 1.0.3 - 1 Novembre 2018
Autore: mail [AT] meo.bogliolo.name