FTS nie działa zgodnie z oczekiwaniami w przypadku wiadomości e-mail z kropkami

Rozwijamy wyszukiwanie w ramach większego systemu.

Mamy Microsoft SQL Server 2014 - 12.0.2000.8 (X64) Standard Edition (64-bit)z tą konfiguracją:

CREATE TABLE NewCompanies(
    [Id] [uniqueidentifier] NOT NULL,
    [Name] [nvarchar](400) NOT NULL,
    [Phone] [nvarchar](max) NULL,
    [Email] [nvarchar](max) NULL,
    [Contacts1] [nvarchar](max) NULL,
    [Contacts2] [nvarchar](max) NULL,
    [Contacts3] [nvarchar](max) NULL,
    [Contacts4] [nvarchar](max) NULL,
    [Address] [nvarchar](max) NULL,
    CONSTRAINT PK_Id PRIMARY KEY (Id)
);

1. Phone jest ciągiem cyfr rozdzielonych przecinkami, np "77777777777, 88888888888"
2. Emailjest uporządkowanym ciągiem wiadomości e-mail z przecinkami podobnymi "email1@gmail.com, email2@gmail.com"(lub w ogóle bez przecinków "email1@gmail.com")
3. Contacts1, Contacts2, Contacts3, Contacts4to pola tekstowe, w których użytkownicy mogą podać dane kontaktowe w dowolnej formie. Jak "John Smith +1 202 555 0156"lub "Bob, +1-999-888-0156, bob@company.com". Te pola mogą zawierać wiadomości e-mail i telefony, które chcemy dalej wyszukiwać.

Tutaj tworzymy treści pełnotekstowe

-- FULL TEXT SEARCH
CREATE FULLTEXT CATALOG NewCompanySearch AS DEFAULT;  
CREATE FULLTEXT INDEX ON NewCompanies(Name, Phone, Email, Contacts1, Contacts2, Contacts3, Contacts4, Address)
KEY INDEX PK_Id

Oto próbka danych

INSERT INTO NewCompanies(Id, Name, Phone, Email, Contacts1, Contacts2, Contacts3, Contacts4) 
VALUES ('7BA05F18-1337-4AFB-80D9-00001A777E4F', 'PJSC Azimuth', '79001002030, 78005005044', 'regular@hotmail.com, s.m.s@gmail.com', 'John Smith', 'Call only at weekends +7-999-666-22-11', NULL, NULL)

W rzeczywistości mamy około 100 tysięcy takich zapisów.

Oczekujemy, że użytkownicy mogą określić część wiadomości e-mail, np. „@ Gmail.com”, co powinno zwrócić wszystkie wiersze z adresami e-mail Gmaila w dowolnym Email, Contacts1, Contacts2, Contacts3, Contacts4polu.

To samo dotyczy numerów telefonów. Użytkownicy mogą wyszukiwać wzór taki jak „70283”, a zapytanie powinno zwrócić telefony z tymi cyframi. Dotyczy to nawet Contacts1, Contacts2, Contacts3, Contacts4pól o dowolnych formach, w których prawdopodobnie powinniśmy usunąć wszystkie oprócz cyfr i spacji przed rozpoczęciem wyszukiwania.

Kiedyś mieliśmy do LIKEwyszukiwania, kiedy mieliśmy około 1500 rekordów i działało dobrze, ale teraz mamy wiele rekordów, a LIKEwyszukiwanie trwa nieskończenie, aby uzyskać wyniki.

W ten sposób staramy się uzyskać stamtąd dane:

SELECT * FROM NewCompanies WHERE CONTAINS((Email, Contacts1, Contacts2, Contacts3, Contacts4), '"s.m.s@gmail.com*"') -- this doesn't get the row
SELECT * FROM NewCompanies WHERE CONTAINS((Phone, Contacts1, Contacts2, Contacts3, Contacts4), '"6662211*"') -- doesn't get anything
SELECT * FROM NewCompanies WHERE CONTAINS(Name, '"zimuth*"') -- doesn't get anything

Właściwie to prośby

WYBIERZ [...] ZAWIERA ([...], '”6662211 *”') - nic nie dostaje

przeciw 'Call only at weekends +7-999-666-22-11' i

WYBIERZ [...] ZAWIERA (nazwa, „„ zimuth * ””) - nic nie dostaje

przeciwko 'PJSC Azimuth'

wykonuj prace zgodnie z oczekiwaniami .
Patrz termin przedrostek . Ponieważ 6662211*nie jest to prefiks z +7-999-666-22-11jak również zimuth*nie jest prefiks zAzimuth

Jeśli chodzi o

WYBIERZ [...] ZAWIERA ([...], '"sms@gmail.com*"') - to nie dostaje wiersza

Wynika to prawdopodobnie z łamaczy słów, jak zawsze wskazano w komentarzach. Zobacz dzielniki słów

Nie sądzę, że wyszukiwanie pełnotekstowe ma zastosowanie do twojego zadania.

Dlaczego warto korzystać z FTS w dokładnie takich samych zadaniach, jak w przypadku operatora LIKE? Gdyby istniał lepszy typ indeksu dla zapytań LIKE ... to byłby lepszy typ indeksu , a nie zupełnie inna technologia i składnia.
I w żaden sposób nie pomoże ci dopasować się "6662211*"do „666 jakiegoś arbitralnego char 22 jakiegoś arbitralnego char 11”.
Wyszukiwanie pełnotekstowe "6662211*"nie dotyczy wyrażeń regularnych (i nie jest nawet poprawnym wyrażeniem dla zadania - nie ma nic o części „dowolnych znaków”), ale o synonimach, formach słów itp.

Ale czy w ogóle można skutecznie szukać podciągów?

Tak to jest. Pomijając takie perspektywy, jak napisanie własnej wyszukiwarki, co możemy zrobić SQL?

Przede wszystkim - konieczne jest oczyszczenie danych! Jeśli chcesz zwrócić użytkownikom dokładne ciągi, które wprowadzili

użytkownicy mogą podać dane kontaktowe w dowolnej formie

... możesz je uratować takimi, jakie są ... i zostawić je przy sobie.
Następnie musisz wyodrębnić dane z dowolnego tekstu (nie jest to takie trudne dla wiadomości e-mail i numerów telefonów) i zapisać dane w jakiejś kanonicznej formie. W przypadku wiadomości e-mail jedyne, co naprawdę musisz zrobić - ułóż je małymi lub wielkimi literami (nie ma to znaczenia), a być może podzielone na @sing. Ale w numerach telefonów musisz pozostawić tylko cyfry
(... A potem możesz nawet zapisać je jako liczby . To może zaoszczędzić trochę miejsca i czasu. Ale wyszukiwanie będzie inne ... Na razie przejdźmy do prostszego i uniwersalne rozwiązanie wykorzystujące ciągi.)

Jak wspomniał MatthewBaker , możesz utworzyć tabelę przyrostków. Następnie możesz tak wyszukiwać

SELECT DISTINCT * FROM NewCompanies JOIN Sufficies ON NewCompanies.Id = Sufficies.Id WHERE Sufficies.sufficies LIKE 'some text%'

Powinieneś umieścić symbol wieloznaczny %tylko na końcu . Albo nie byłoby żadnych korzyści z tabeli sufiksów.

Weźmy na przykład numer telefonu

+ 7-999-666-22-11

Gdy pozbędziemy się w nim znaków odpadów, będzie miał 11 cyfr. Oznacza to, że potrzebujemy 11 sufiksów dla jednego numeru telefonu

           1
          11
         211
        2211
       62211
      662211
     6662211
    96662211
   996662211
  9996662211
 79996662211

Więc złożoność przestrzeni dla tego rozwiązania jest liniowa ... nie jest taka zła, powiedziałbym ... Ale poczekaj, to złożoność liczby rekordów. Ale w symbolach ... potrzebujemy N(N+1)/2symboli do przechowywania wszystkich sufiksów - to kwadratowa złożoność ... nie dobrze ... ale jeśli masz teraz 100 000zapisy i nie masz planów na miliony w najbliższej przyszłości - możesz to zrobić rozwiązanie.

Czy możemy zmniejszyć złożoność przestrzeni?

Opiszę tylko pomysł, jego wdrożenie zajmie trochę wysiłku. I prawdopodobnie będziemy musieli przekroczyć graniceSQL

Załóżmy, że masz 2 wiersze NewCompaniesi 2 ciągi tekstu w dowolnym formacie:

    aaaaa
    11111

Jak duża powinna być tabela sufiksów? Oczywiście potrzebujemy tylko 2 płyt.

Weźmy inny przykład. Również 2 wiersze, 2 ciągi tekstowe do wyszukiwania. Ale teraz jest to:

    aa11aa
    cc11cc

Zobaczmy, ile sufiksów potrzebujemy teraz:

         a // no need, LIKE `a%`  will match against 'aa' and 'a11aa' and 'aa11aa'
        aa // no need, LIKE `aa%` will match against 'aa11aa'
       1aa
      11aa
     a11aa
    aa11aa
         c // no need, LIKE `c%`  will match against 'cc' and 'c11cc' and 'cc11cc'
        cc // no need, LIKE `cc%` will match against 'cc11cc'
       1cc
      11cc
     c11cc
    cc11cc

Nie tak źle, ale też nie tak dobrze.

Co jeszcze możemy zrobić?

Załóżmy, że użytkownik wchodzi "c11"w pole wyszukiwania. Następnie LIKE 'c11%'potrzebuje przyrostka „ c11 cc”, aby odnieść sukces. Ale jeśli zamiast wyszukiwania "c11"najpierw szukamy "c%", to "c1%"i tak dalej? Pierwsze wyszukiwanie da tylko jeden wiersz od NewCompanies. I nie będzie potrzeby kolejnych wyszukiwań. I możemy

       1aa // drop this as well, because LIKE '1%' matches '11aa'
      11aa
     a11aa // drop this as well, because LIKE 'a%' matches 'aa11aa'
    aa11aa
       1cc // same here
      11cc
     c11cc // same here
    cc11cc

i otrzymujemy tylko 4 sufiksy

      11aa
    aa11aa
      11cc
    cc11cc

Nie mogę powiedzieć, jaka byłaby w tym przypadku złożoność przestrzeni, ale wydaje się, że byłaby do przyjęcia.

W takich przypadkach wyszukiwanie pełnotekstowe jest mniej niż idealne. Byłem na tej samej łodzi co ty. Podobnie, jak wyszukiwania są zbyt wolne, a wyszukiwania pełnotekstowe szukają słów, które zaczynają się od terminu, a nie zawierają terminu.

Wypróbowaliśmy kilka rozwiązań, jedną z czystych opcji SQL jest zbudowanie własnej wersji wyszukiwania pełnotekstowego, w szczególności wyszukiwania indeksu odwróconego. Próbowaliśmy tego i udało się, ale zajęło dużo miejsca. Stworzyliśmy dodatkową tabelę wstrzymującą dla częściowych wyszukiwanych haseł i wykorzystaliśmy w niej indeksowanie pełnotekstowe. Oznacza to jednak, że wielokrotnie przechowywaliśmy wiele kopii tej samej rzeczy. Na przykład, zapisaliśmy „longword” jako Longword, ongword, ngword, gword .... itd. Tak więc każda zawarta fraza zawsze będzie na początku indeksowanego terminu. Przerażające rozwiązanie, pełne wad, ale zadziałało.

Następnie przyjrzeliśmy się hostowaniu oddzielnego serwera do wyszukiwania. Googling Lucene i elastisearch dadzą ci dobre informacje na temat tych gotowych opakowań.

W końcu opracowaliśmy własną wyszukiwarkę, która działa obok SQL. Pozwoliło nam to na przeprowadzenie wyszukiwania fonetycznego (podwójny metafon), a następnie zastosowanie obliczeń lewenshteina obok soundexa w celu ustalenia znaczenia. Przesada w przypadku wielu rozwiązań, ale warta wysiłku w naszym przypadku użycia. Nawet teraz mamy opcję wykorzystania procesorów graficznych Nvidia do wyszukiwania cuda, ale stanowiło to zupełnie nowy zestaw bólów głowy i nieprzespanych nocy. Istotność tych wszystkich zależeć będzie od tego, jak często widzisz, że Twoje wyszukiwanie jest przeprowadzane, i od tego, jak reaktywne potrzebujesz.

Indeksy pełnotekstowe mają wiele ograniczeń. Możesz używać symboli wieloznacznych dla słów, które znalezione w indeksie są całymi „częściami”, ale nawet wtedy jesteś ograniczony do końcowej części słowa. Dlatego możesz używać, CONTAINS(Name, '"Azimut*"')ale nie możeszCONTAINS(Name, '"zimuth*"')

Z dokumentacji Microsoft :

Gdy terminem przedrostka jest fraza, każdy token tworzący frazę jest uważany za osobny termin przedrostka. Wszystkie wiersze zawierające słowa zaczynające się od warunków przedrostka zostaną zwrócone. Na przykład przedrostek „lekki chleb *” znajdzie wiersze z tekstem „lekki chleb”, „lekko chleb” lub „lekki chleb”, ale nie zwróci „lekko chleba”.

Kropki w wiadomości e-mail, jak wskazuje tytuł, nie są głównym problemem. To na przykład działa:

SELECT * FROM NewCompanies 
WHERE CONTAINS((Email, Contacts1, Contacts2, Contacts3, Contacts4), 's.m.s@gmail.com') 

W takim przypadku indeks identyfikuje cały ciąg wiadomości e-mail jako prawidłowy, a także „gmail” i „gmail.com”. Tylko „sms” nie jest poprawne.

Ostatni przykład jest podobny. Części numeru telefonu są indeksowane (na przykład 666-22-11 i 999-666-22-11), ale usunięcie łączników nie jest ciągiem, o którym indeks będzie wiedział. W przeciwnym razie działa to:

SELECT * FROM NewCompanies 
WHERE CONTAINS((Phone, Contacts1, Contacts2, Contacts3, Contacts4), '"666-22-11*"')