Obecnie próbuję uruchomić zapytania dotyczące zrzutu danych komentarzy przepełnienia stosu. Oto jak wygląda schemat:

CREATE TABLE `socomments` (
  `Id` int(11) NOT NULL,
  `PostId` int(11) NOT NULL,
  `Score` int(11) DEFAULT NULL,
  `Text` varchar(600) NOT NULL,
  `CreationDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `UserId` int(11) NOT NULL,
  PRIMARY KEY (`Id`),
  KEY `idx_socomments_PostId` (`PostId`),
  KEY `CreationDate` (`CreationDate`),
  FULLTEXT KEY `Text` (`Text`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Uruchomiłem to zapytanie względem tabeli i działało ono niezwykle wolno (ma 29 milionów wierszy, ale ma indeks pełnotekstowy):

SELECT *
FROM socomments
WHERE MATCH (Text) AGAINST ('"fixed the post"' IN BOOLEAN MODE)

Więc profilowałem to, czego rezultatem są:

|| Status                     || Duration ||
|| starting                   || 0.000058 ||
|| checking permissions       || 0.000006 ||
|| Opening tables             || 0.000014 ||
|| init                       || 0.000019 ||
|| System lock                || 0.000006 ||
|| optimizing                 || 0.000007 ||
|| statistics                 || 0.000013 ||
|| preparing                  || 0.000005 ||
|| FULLTEXT initialization    || 207.1112 ||
|| executing                  || 0.000009 ||
|| Sending data               || 0.000856 ||
|| end                        || 0.000004 ||
|| query end                  || 0.000004 ||
|| closing tables             || 0.000006 ||
|| freeing items              || 0.000059 ||
|| logging slow query         || 0.000037 ||
|| cleaning up                || 0.000046 ||

Jak widać, inicjowanie FULLTEXT zajmuje dużo czasu. Czy to normalne? Jeśli nie, jak bym to naprawić?

Inni uznali tę sytuację za kłopotliwą

• Wyszukiwanie pełnotekstowe InnoDB w MySQL 5.6: część 3, Wydajność
• Wyszukiwanie pełnotekstowe InnoDB Z ROZSZERZENIEM QUERY jest bardzo wolne
  • 16 września 2010 r
  • 5.0.51a, 5.0.75, 5.0 bzr
  • Status: Brak opinii
• Zapytanie SELECT zawiesi się w stanie inicjalizacji PEŁNYTEKST
  • 17 października 2014 r
  • MySQL 5.6.19
  • Status: nie zostanie naprawiony

Ponieważ dokumentacja MySQL jest bardzo zwięzła w tym stanie wątku

Inicjalizacja FULLTEXT

Serwer przygotowuje się do wyszukiwania pełnotekstowego w języku naturalnym.

jedynym wyjściem będzie przygotowanie się z mniejszą ilością danych. W jaki sposób ?

SUGESTIA # 1

Spójrz ponownie na zapytanie. Zaznacza wszystkie kolumny. Dokonałbym refaktoryzacji zapytania, aby zebrać tylko kolumny id socomments. Następnie dołącz odzyskane identyfikatory z powrotem do socommentstabeli.

SELECT B.* FROM
(SELECT id FROM socomments
WHERE MATCH (Text) AGAINST ('"fixed the post"' IN BOOLEAN MODE)) A
LEFT JOIN socomments B USING (id);

Może to stworzyć brzydszy plan WYJAŚNIJ, ale myślę, że profilowanie zmieni się na lepsze. Podstawowa idea jest taka: jeśli masz agresywne wyszukiwanie PEŁNOTEKSTOWE, spraw, aby gromadziło najmniej danych w tej FULLTEXT initializationfazie, skracając w ten sposób czas.

Polecałem to wiele razy wcześniej

• May 14, 2012: wolne zapytanie z pełnym tekstem i lewym złączeniem
• Mar 18, 2012: Dlaczego LIKE jest ponad czterokrotnie szybszy niż MATCH ... AGAINST w indeksie FULLTEXT w MySQL?
• Jan 26, 2012: Przeszukiwanie pełnego tekstu MySQL optymalizacja my.cnf :
• Oct 25, 2011: Indeks FULLTEXT jest ignorowany w TRYBIE BOOLEAN z warunkową liczbą słów

SUGESTIA # 2

Upewnij się, że ustawiasz opcje FULLTEXT-a oparte na InnoDB, a nie te dla MyISAM. Dwie opcje, którymi powinieneś się martwić to:

• innodb_ft_cache_size
  • Wartość domyślna 8000000 (7,629 M)
  • Wartość maksymalna 80000000 (76,29M)
• innodb_ft_total_cache_size
  • Wartość domyślna 640000000 (610M)
  • Wartość maksymalna 1600000000 (1525 M = 1,49 G)

Pomyśl o tym przez chwilę. Pole tekstowe to VARCHAR (600). Powiedzmy, że średnia to 300 bajtów. Masz ich 29 000 000 milionów. To by było trochę 8 GB. Być może zwiększenie innodb_ft_cache_size i innodb_ft_total_cache_size może również pomóc.

Upewnij się, że masz wystarczającą ilość pamięci RAM dla większych buforów InnoDB FULLTEXT.

SPRÓBUJ !!!

Jeśli korzystasz z indeksów InnoDB FULLTEXT, zapytania często zawieszają się w stanie „Inicjalizacja FULLTEXT”, jeśli wysyłasz zapytania do tabeli z dużą liczbą usuniętych wierszy. W implementacji FULLTEXT firmy InnoDB usunięte wiersze nie są przycinane, dopóki nie zostanie uruchomiona kolejna operacja OPTIMIZE w odniesieniu do tabeli, której dotyczy problem. Zobacz: https://dev.mysql.com/doc/refman/5.6/en/innodb-fulltext-index.html

Aby usunąć wpisy indeksu pełnotekstowego dla usuniętych rekordów, należy uruchomić OPTYMALIZACJĘ TABELI w indeksowanej tabeli z innodb_optimize_fulltext_only = ON, aby odbudować indeks pełnotekstowy.

Można również sprawdzić liczbę usuniętych, ale nie wyczyszczonych rekordów, sprawdzając informacje_schema.innodb_ft_deleted

Aby rozwiązać ten problem, należy regularnie uruchamiać OPTYMALIZACJĘ TABELI dla tabel z indeksami InnoDB FULLTEXT.

W MySQL potwierdzono błąd ( usunięte DOCID nie są utrzymywane podczas OPTYMALIZACJI tabel FULLTEXT InnoDB ), że wydajność zbiorników pod dużym obciążeniem usuwania (bez odbudowywania tabeli od zera).

Związane .

Indeksy pełnotekstowe w MySQL nie są zaprojektowane do obsługi dużych ilości danych, więc szybkość wyszukiwania spada dość szybko, gdy Twój zbiór danych rośnie. Jednym z rozwiązań jest użycie zewnętrznych wyszukiwarek pełnotekstowych, takich jak Solr lub Sphinx, które mają ulepszoną funkcjonalność wyszukiwania (dostrajanie trafności i wsparcie wyszukiwania wyrażeń, wbudowane aspekty, fragmenty itp.) Rozszerzona składnia zapytań i znacznie szybsza prędkość od średniej do średniej -duże zestawy danych.

Solr jest oparty na platformie Java, więc jeśli uruchomisz aplikację opartą na Javie, będzie to dla ciebie naturalny wybór, Sphinx jest napisany w C ++ i działa jako demon w taki sam sposób jak MySQL. Jak tylko podasz silnikowi zewnętrznemu dane, które chcesz przeszukać, możesz również przenieść niektóre zapytania z MySQL. Nie mogę powiedzieć, który silnik jest lepszy w twoim przypadku, używam głównie Sphinx, a oto przykład użycia: http://astellar.com/2011/12/replacing-mysql-full-text-search-with-sphinx/