Condividi tramite

Ricerca full-text

  • Articolo

Si applica a: SQL Server database SQL di Azure Istanza gestita di SQL di Azure

In SQL Server e nel database SQL di Azure la ricerca full-text consente ad applicazioni e utenti di eseguire query full-text su dati di tipo carattere in tabelle di SQL Server.

Attività di base

Questo articolo offre una panoramica della ricerca full-text e descrive i componenti e l'architettura di questa funzionalità. Per iniziare subito, ecco le attività di base.

La ricerca full-text è un componente facoltativo del motore di database di SQL Server. Se non è stata selezionata l'opzione Ricerca full-text durante l'installazione di SQL Server, eseguire di nuovo il programma di installazione di SQL Server per aggiungerla.

Panoramica

Un indice full-text include una o più colonne basate su caratteri in una tabella. Queste colonne possono avere uno dei tipi di dati seguenti: char, varchar, nchar, nvarchar, text, ntext, image, xml o varbinary(max) e FILESTREAM. Ogni indice full-text consente di indicizzare una o più colonne della tabella e ciascuna colonna può essere utilizzata con una lingua specifica.

Attraverso le query full-text è possibile eseguire ricerche linguistiche sui dati di testo contenuti negli indici full-text, usando parole e frasi in base alle regole di una determinata lingua, come ad esempio l'inglese o il giapponese. Le query full-text possono contenere semplici parole e frasi oppure più forme di una parola o frase. Una query full-text restituisce qualsiasi documento contenente almeno una corrispondenza, nota anche come riscontro. Si ottiene una corrispondenza quando un documento di destinazione contiene tutti i termini specificati nella query full-text e soddisfa qualsiasi altra condizione di ricerca, come ad esempio la distanza entro i termini corrispondenti.

Query di ricerca full-text

Dopo l'aggiunta delle colonne a un indice full-text, gli utenti e le applicazioni possono eseguire query full-text sul testo contenuto all'interno delle colonne. Queste query possono consentire la ricerca delle seguenti condizioni:

  • Una o più parole o frasi specifiche (termine semplice)
  • Parola o frase in cui le parole iniziano con il testo specificato (termine di prefisso)
  • Forme flessive di una parola specifica (termine di generazione)
  • Una parola o frase vicina a un'altra parola o frase (termine di prossimità)
  • Sinonimi di una parola specifica (thesaurus)
  • Parole o frasi che usano valori ponderati (termine ponderato)

Per le query di ricerca full-text non viene fatta distinzione tra maiuscole e minuscole. Ad esempio, dalla ricerca di Aluminum o aluminum vengono restituiti gli stessi risultati.

Le query full-text usano un piccolo insieme di predicati Transact-SQL (CONTAINS e FREETEXT) e funzioni (CONTAINSTABLE e FREETEXTTABLE). Tuttavia, gli obiettivi di ricerca di un determinato scenario aziendale influiscono sulla struttura delle query full-text. Ad esempio:

  • Ricerca di un prodotto in un sito Web di e-commerce:

    SELECT product_id
FROM products
WHERE CONTAINS (product_description, '"Snap Happy 100EZ" OR FORMSOF(THESAURUS,"Snap Happy") OR "100EZ"')
    AND product_cost < 200;

  • Scenario di assunzione con ricerca di candidati con esperienza di lavoro in SQL Server: 

    SELECT candidate_name,
    SSN
FROM candidates
WHERE CONTAINS (candidate_resume, '"SQL Server"')
    AND candidate_division = 'DBA';

Per altre informazioni, vedere Esecuzione della query con ricerca Full-Text.

Query di ricerca full-text a confronto con il predicato LIKE

Contrariamente alla ricerca full-text, il predicato LIKE di Transact-SQL funziona unicamente con modelli di caratteri. Non è inoltre possibile utilizzare il predicato LIKE per eseguire query su dati binari formattati. Inoltre, l'esecuzione di una query LIKE su una grande quantità di dati di testo non strutturati è molto più lenta dell'esecuzione di una query full-text equivalente sugli stessi dati. Una query LIKE eseguita su milioni di righe di dati di testo può richiedere diversi minuti, mentre per una query full-text sugli stessi dati possono essere necessari al massimo pochi secondi, a seconda del numero di righe restituite.

Architettura della ricerca full-text

L'architettura della ricerca full-text è costituita dai processi seguenti:

  • Processo di SQL Server (sqlservr.exe).

  • Processo host del daemon di filtri (fdhost.exe).

    Ai fini della sicurezza, i filtri vengono caricati da processi separati definiti host del daemon di filtri. I processi fdhost.exe vengono creati da un servizio dell'utilità di avvio di FDHOST (MSSQLFDLauncher) e vengono eseguiti con le credenziali di sicurezza dell'account del servizio dell'utilità di avvio di FDHOST. Il funzionamento delle query e dell'indicizzazione full-text dipende quindi dall'esecuzione del servizio dell'utilità di avvio di FDHOST. Per informazioni sulla configurazione dell'account del servizio per questo servizio, vedere Impostazione dell'account del servizio dell'Utilità di avvio del daemon di filtri full-text.

In questi due processi sono inclusi i componenti dell'architettura della ricerca full-text, riepilogati con le relative relazioni nell'illustrazione seguente. I componenti vengono descritti dopo l'illustrazione.

Diagramma dell'architettura della ricerca full-text.

Processo di SQL Server

Nel processo di SQL Server vengono utilizzati i seguenti componenti per la ricerca full-text:

Componente Descrizione
Tabelle utente In queste tabelle sono contenuti i dati da inserire nell'indice full-text.
Gatherer full-text Il gatherer full-text funziona con i thread della ricerca per indicizzazione full-text È responsabil della pianificazione e gestione del popolamento degli indici full-text, nonché di monitorare i cataloghi full-text.
File del thesaurus In questi file sono contenuti i sinonimi dei termini da cercare. Per altre informazioni, vedere Configurare e gestire i file del thesaurus per la ricerca full-text.
Oggetti dell'elenco di parole non significative Negli oggetti dell'elenco di parole non significative sono inclusi un insieme di parole comuni che non risultano utili per la ricerca. Per altre informazioni, vedere Configurare e gestire parole non significative ed elenchi di parole non significative per la ricerca full-text.
Query processor di SQL Server Query Processor consente di compilare ed eseguire query SQL. Se in una query SQL è inclusa una query di ricerca full-text, la query viene inviata al motore di ricerca full-text sia durante la compilazione sia durante l'esecuzione. Il risultato della query viene messo a confronto con l'indice full-text.
Motore di ricerca full-text Il motore di ricerca full-text in SQL Server è completamente integrato con il query processor. Il motore di ricerca full-text compila ed esegue query full-text. Come parte dell'esecuzione di query, il motore di ricerca full-text può ricevere input dal thesaurus e dall'elenco di parole non significative.
Writer di indici (indicizzatore) Il writer di indici consente di compilare la struttura utilizzata per archiviare i token indicizzati.
Strumento di gestione del daemon di filtri Lo strumento di gestione del daemon di filtri consente di monitorare lo stato dell'host del daemon di filtri per il motore di ricerca full-text.

Processo host del daemon di filtri

L'host del daemon di filtri è un processo avviato dal motore di ricerca full-text e consente l'esecuzione dei componenti della ricerca full-text seguenti, responsabili dell'accesso, del filtraggio e del word breaking di dati dalle tabelle, nonché del word breaking e dello stemming dell'input della query.

I componenti dell'host del daemon di filtri sono i seguenti:

Componente Descrizione
Gestore di protocollo Questo componente consente di effettuare il pull dei dati dalla memoria per un'ulteriore elaborazione e di accedere ai dati da una tabella utente in un database specificato. È inoltre responsabile della raccolta dei dati dalle colonne oggetto dell'indicizzazione full-text e del loro passaggio all'host del daemon di filtri tramite il quale applica il filtro e il word breaker in base alle specifiche esigenze.
Filtri Per alcuni tipi di dati è richiesta l'applicazione di un filtro prima che i dati di un documento possano essere sottoposti all'indicizzazione full-text, inclusi i dati nelle colonne varbinary, varbinary(max), imageo xml . Il filtro utilizzato per un documento dipende dal relativo tipo. Vengono infatti utilizzati filtri diversi per documenti di Microsoft Word (.doc), documenti di Microsoft Excel (.xls) e documenti XML (.xml). Il filtro estrae blocchi di testo dal documento, rimuovendo la formattazione incorporata e mantenendo il testo e, potenzialmente, le informazioni sulla posizione del testo. Il risultato è un flusso di informazioni testuali. Per altre informazioni, vedere Configurazione e gestione di filtri per la ricerca.
Word breaker e stemmer Un word breaker è un componente specifico della lingua che consente di trovare i delimitatori di parola in base alle regole lessicali di una determinata lingua (word breaking). Ogni word breaker è associato a uno stemmer specifico della lingua che coniuga i verbi ed esegue le espansioni flessionali. In fase di indicizzazione, l'host del daemon di filtri utilizza un word breaker e uno stemmer per eseguire l'analisi linguistica sui dati testuali di una colonna di tabella specificata. Il word breaker e lo stemmer utilizzati per l'indicizzazione della colonna sono determinati dalla lingua associata a una colonna di tabella nell'indice full-text. Per ulteriori informazioni, vedere Configurare e gestire word breaker e stemmer per la ricerca (server SQL).

SQL Server 2012 (11.x) installa una nuova versione dei word breaker e degli stemmer per l'Inglese (Stati Uniti), LCID 1033, e l'Inglese (Regno Unito), LCID 2057. È tuttavia possibile passare alla versione precedente di questi componenti se si desidera mantenere il comportamento precedente. Per altre informazioni vedere Modifica del word breaker utilizzato per le lingue Inglese (Stati Uniti) e Inglese (Regno Unito).

Elaborazione della ricerca full-text

La ricerca full-text è supportata dal motore di ricerca full-text Il motore full-text svolge due ruoli: il supporto per l'indicizzazione e l'esecuzione di query.

Processo di indicizzazione full-text

Quando viene iniziato un popolamento full-text, noto anche come ricerca per indicizzazione, tramite il motore di ricerca full-text viene eseguito il push di batch di grandi dimensioni di dati in memoria e viene inviata una notifica all'host del daemon di filtri. L'host filtra ed esegue il word breaking dei dati ed esegue inoltre la conversione dei dati convertiti in elenchi di parole invertiti. La ricerca full-text effettua quindi il pull dei dati convertiti dagli elenchi di parole, elabora i dati per rimuovere le parole non significative e salva in modo permanente gli elenchi di parole per un batch in uno o più indici invertiti.

Durante l'indicizzazione dei dati archiviati in una colonna varbinary(max) o image, il filtro, che implementa l'interfaccia IFilter, estrae testo in base al formato file specificato per tali dati (ad esempio Microsoft Word). Per i componenti filtro, talvolta, i dati di tipo varbinary(max) o image devono essere scritti nella cartella filterdata e non deve esserne eseguito il push in memoria.

Nell'ambito dell'elaborazione, i dati di testo raccolti vengono sottoposti a un word breaker per separare il testo in singoli token o parole chiave. La lingua usata per la tokenizzazione viene specificata a livello di colonna o può essere identificata all'interno dei dati varbinary(max), imageo xml dal componente filtro.

È possibile eseguire un'ulteriore elaborazione per rimuovere le parole non significative e normalizzare i token prima di archiviarli nell'indice full-text o in un frammento di indice.

Al termine di un popolamento, viene attivato un processo di unione conclusivo che associa i frammenti di indice in un singolo indice full-text master. Ciò consente prestazioni di query superiori poiché è necessario eseguire query solo sull'indice master anziché su diversi frammenti di indice ed è possibile utilizzare statistiche di punteggio migliori per la classificazione della pertinenza.

Processo di esecuzione di query full-text

Query Processor consente di passare le parti full-text di una query al motore di ricerca full-text affinché vengano elaborate. Il motore di ricerca full-text esegue il word breaking e, facoltativamente, le espansioni del thesaurus, lo stemming e l'elaborazione delle parole non significative. Le parti full-text della query vengono rappresentate come operatori SQL, principalmente come funzioni di flusso con valori di tabella. Durante l'esecuzione della query, queste funzioni accedono all'indice invertito per recuperare i risultati corretti. I risultati vengono restituiti al client immediatamente oppure dopo essere stati ulteriormente elaborati.

Architettura degli indici full-text

Le informazioni contenute negli indici full-text vengono utilizzate dal motore di ricerca full-text per compilare query full-text che consentono di cercare rapidamente parole o combinazioni di parole specifiche in una tabella. In un indice full-text vengono archiviate informazioni su parole significative e sulla relativa posizione all'interno di una o più colonne di una tabella di database. Un indice full-text è un tipo speciale di indice funzionale basato su token compilato e gestito dal motore di ricerca full-text per SQL Server. Il processo di compilazione di un indice full-text è diverso da quello di altri tipi di indici. Anziché creare un albero B basato su un valore archiviato in una riga specifica, il motore di ricerca full-text compila una struttura con indice invertito, compresso e in pila dai singoli token dal testo indicizzato. Le dimensioni di un indice full-text sono limitate solo dalle risorse di memoria disponibili del computer in cui viene eseguita l'istanza di SQL Server.

A partire da SQL Server 2008 (10.0.x), gli indici full-text sono integrati nel motore di database anziché risiedere nel file system come nelle versioni precedenti di SQL Server. Per un nuovo database, il catalogo full-text è ora un oggetto virtuale che non appartiene ad alcun filegroup. Si tratta semplicemente di un concetto logico che fa riferimento a un gruppo di indici full-text. Nota tuttavia che, durante l'aggiornamento di un database di SQL Server 2005 (9.x), ovvero qualsiasi catalogo full-text contenente file di dati, viene creato un nuovo filegroup. Per ulteriori informazioni, vedi Aggiornare la ricerca full-text.

È consentito un solo indice full-text per tabella. Per creare un indice full-text su una tabella, quest'ultima deve contenere una colonna singola, univoca e non Null. È possibile compilare un indice full-text su colonne di tipo char, varchar, nchar, nvarchar, text, ntext, image, xml, varbinarye varbinary(max) . La creazione di un indice full-text in colonne con tipo di dati varbinary, varbinary(max), imageo xml richiede la specifica di una colonna del tipo. Una colonna del tipo è una colonna di tabella in cui è possibile archiviare l'estensione file (.doc, .pdf, .xls e così via) del documento in ogni riga.

Struttura di un indice full-text

Comprendere a fondo la struttura di un indice full-text è fondamentale per comprendere il funzionamento del motore di ricerca full-text. In questo articolo viene usato come esempio l'estratto seguente della tabella Document in AdventureWorks2022. In questo estratto sono visualizzate solo due colonne, la colonna DocumentID e la colonna Title e tre righe della tabella.

In questo esempio si suppone che nella colonna Title sia stato creato un indice full-text.

DocumentID Title
1 Crank Arm and Tire Maintenance
2 Front Reflector Bracket and Reflector Assembly 3
3 Front Reflector Bracket Installation

Nella tabella seguente, Fragment 1, viene illustrato il contenuto dell'indice full-text creato nella colonna Title della tabella Document. Gli indici full-text contengono più informazioni rispetto a quelle riportate in questa tabella. La tabella è una rappresentazione logica di un indice full-text e ha solo scopo illustrativo. Per ottimizzare l'utilizzo del disco, le righe vengono archiviate in un formato compresso.

I dati sono stati invertiti dai documenti originali. Questa inversione è dovuta al fatto che per le parole chiave viene eseguito il mapping agli ID documento. Per questo motivo, un indice full-text viene spesso definito come un indice invertito.

Si noti, inoltre, che la parola chiave and è rimossa dall'indice full-text. Poiché and è una parola non significativa, la rimozione di tali parole da un indice full-text può contribuire a risparmiare spazio su disco, migliorando di conseguenza le prestazioni delle query. Per altre informazioni sulle parole non significative e sugli elenchi di parole non significative, vedere Configurare e gestire parole non significative ed elenchi di parole non significative per la ricerca full-text.

Frammento 1

Parola chiave ColId DocId Occorrenza
Crank 1 1 1
Arm 1 1 2
Tire 1 1 4
Maintenance 1 1 5
Front 1 2 1
Front 1 3 1
Reflector 1 2 2
Reflector 1 2 5
Reflector 1 3 2
Bracket 1 2 3
Bracket 1 3 3
Assembly 1 2 6
3 1 2 7
Installation 1 3 4

La colonna Keyword contiene la rappresentazione di un singolo token estratto al momento dell'indicizzazione. I word breaker stabiliscono il modo in cui un token viene determinato.

La colonna ColId contiene un valore che corrisponde a una particolare colonna con indicizzazione full-text.

La colonna DocId contiene valori per un integer a otto byte con mapping a un determinato valore chiave full-text in una tabella con indicizzazione full-text. Questo mapping è necessario se la chiave full-text non è un tipo di dati integer. In questi casi i mapping tra i valori chiave full-text e i valori DocId vengono mantenuti in una tabella separata denominata DocId Mapping. Per eseguire una query per questi mapping usare la stored procedure di sistema sp_fulltext_keymappings . Per soddisfare una condizione di ricerca, è necessario creare un join tra i valori DocId della tabella precedente e la tabella di mappatura DocId per recuperare le righe dalla tabella di base su cui viene eseguita la query. Se il valore della chiave full-text della tabella di base è di tipo integer, il valore viene utilizzato direttamente come DocId e non è necessario alcun mapping. Pertanto, l'utilizzo di valori chiave full-text di tipo integer può contribuire all'ottimizzazione delle query full-text.

La colonna Occurrence contiene un valore di tipo integer. Per ogni valore DocId è presente un elenco di valori di occorrenza corrispondenti agli offset relativi di una particolare parola chiave all'interno di DocId. I valori di occorrenza sono utili per determinare le corrispondenze di frase o prossimità, ad esempio frasi con valori di occorrenza numericamente adiacenti. Sono inoltre utili per calcolare i punteggi di pertinenza, ad esempio il numero di occorrenze di una parola chiave in un DocId può essere utilizzato per l'assegnazione del punteggio.

Frammenti di indice full-text

L'indice full-text logico viene in genere suddiviso tra più tabelle interne. Ogni tabella interna viene definita un frammento di indice full-text. Alcuni di questi frammenti potrebbero contenere dati più recenti di altri. Ad esempio, se un utente aggiorna la riga seguente il cui DocId è 3 e per la tabella è impostato il rilevamento automatico delle modifiche, viene creato un nuovo frammento.

DocumentID Title
3 Rear Reflector

Nell'esempio seguente, Fragment 2, il frammento contiene dati più recenti su DocId 3, rispetto a Fragment 1. Pertanto, quando viene eseguita una query per Rear Reflector i dati di Fragment 2 vengono utilizzati per DocId 3. Ogni frammento viene contrassegnato con un timestamp di creazione su cui è possibile eseguire query tramite la vista del catalogo sys.fulltext_index_fragments .

Frammento 2

Parola chiave ColId DocId Occ
Rear 1 3 1
Reflector 1 3 2

Come si può vedere da Fragment 2, le query full-text devono essere eseguite internamente su ogni frammento e le voci più obsolete devono essere eliminate. Un numero eccessivo di frammenti di indice full-text nell'indice full-text può causare un calo sensibile delle prestazioni di esecuzione delle query. Per ridurre il numero di frammenti, riorganizza il catalogo full-text tramite l'opzione REORGANIZE dell'istruzione ALTER FULLTEXT CATALOG di Transact-SQL. Questa istruzione consente di eseguire un' unione nell'indice master, ovvero un'unione dei frammenti in un singolo frammento più grande e la rimozione di tutte le voci obsolete dall'indice full-text.

Dopo essere stato riorganizzato, l'indice di esempio dovrebbe contenere le righe seguenti:

Parola chiave ColId DocId Occ
Crank 1 1 1
Arm 1 1 2
Tire 1 1 4
Maintenance 1 1 5
Front 1 2 1
Rear 1 3 1
Reflector 1 2 2
Reflector 1 2 5
Reflector 1 3 2
Bracket 1 2 3
Assembly 1 2 6
3 1 2 7

Differenze tra indici full-text e indici SQL Server normali

Indici full-text Indici di SQL Server normali
È consentito un solo indice full-text per tabella. Sono consentiti più indici normali per tabella.
L'aggiunta di dati a indici full-text, definita popolamento, può essere richiesta in modo specifico, tramite pianificazione oppure può avvenire in modo automatico con l'aggiunta di nuovi dati. Vengono automaticamente aggiornati quando si inseriscono, aggiornano o eliminano dati.
Sono raggruppati all'interno dello stesso database in uno o più cataloghi full-text. Non sono raggruppati.

Componenti linguistici e supporto per la lingua nella ricerca full-text

La ricerca full-text supporta quasi 50 lingue, tra cui inglese, spagnolo, cinese, giapponese, arabo, bengalese e hindi. Per un elenco completo delle lingue full-text supportate, vedi sys.fulltext_languages. A ognuna delle colonne contenute nell'indice full-text è associato un identificatore delle impostazioni locali (LCID) di Microsoft Windows che corrisponde a una lingua supportata dalla ricerca full-text. L'identificatore LCID 1033, ad esempio, corrisponde all'inglese americano, mentre l'identificatore LCID 2057 corrisponde all'inglese britannico. Per ogni lingua full-text supportata, SQL Server fornisce componenti linguistici che supportano l'indicizzazione e l'esecuzione di query su dati full-text archiviati in quella lingua.

Tra i componenti specifici della lingua sono inclusi gli elementi seguenti:

Componente Descrizione
Word breaker e stemmer Un word breaker consente di trovare i delimitatori di parola in base alle regole lessicali di una determinata lingua (word breaking). A ogni word breaker è associato uno stemmer tramite cui vengono coniugati i verbi per la stessa lingua. Per ulteriori informazioni, vedere Configurare e gestire word breaker e stemmer per la ricerca (server SQL).
Elenchi di parole non significative Viene, inoltre, fornito un esaustivo elenco di parole non significative di sistema (anche dette stopword). Per parola non significativa si intende una parola inutile ai fini della ricerca e quindi ignorata dalle query full-text. Ad esempio, nelle impostazioni locali della lingua italiana parole quali a, and, is e the sono considerate non significative. In genere, è necessario configurare uno o più file del thesaurus ed elenchi di parole non significative. Per altre informazioni, vedere Configurare e gestire parole non significative ed elenchi di parole non significative per la ricerca full-text.
File del thesaurus SQL Server installa anche un file del thesaurus per ogni lingua full-text e un file del thesaurus globale. I file del thesaurus installati sono vuoti, ma è possibile modificarli per definire sinonimi per una lingua o uno scenario aziendale specifico. Sviluppando un thesaurus basato sui dati full-text in uso, è possibile ampliare in modo efficace l'ambito delle query full-text su tali dati. Per altre informazioni, vedere Configurare e gestire i file del thesaurus per la ricerca full-text.
Filtri (iFilters) Per l'indicizzazione di un documento in una colonna del tipo di dati varbinary(max), imageo xml è richiesta l'applicazione di un filtro per eseguire altre operazioni di elaborazione. Il filtro deve essere specifico del tipo di documento (.doc, .pdf, .xls, .xml e così via). Per altre informazioni, vedere Configurazione e gestione di filtri per la ricerca.

I word breaker (così come gli stemmer) e i filtri vengono eseguiti nel processo host del daemon di filtri (fdhost.exe).

Contenuto correlato