MySQL Fulltext - zpomalení vyhledávání

MySQL Fulltext

Není úplně běžné, aby u nás zákazníci používali fulltext integrovaný v MySQL, ale občas se přecijen někdo takový najde.

Důvody k použítí fulltextu v MySQL jsou převážně šetření zdroji. Na menší projekty je vhodný a ušetří se trochu na systémových zdrojích, na vývoji a nakonec má aplikace o jednu závislost méně.

Popis problému

Zákazník přišel s problémem, kdy vyhledávání pomocí fulltext search v MySQL trvá výrazně déle než na jeho vývojovém prostředí.

Tabulka

CREATE TABLE `search` (
  `id` int(11) NOT NULL,
  `name` text CHARACTER SET utf8 NOT NULL,
  `score` int(11) NOT NULL DEFAULT '100',
  PRIMARY KEY (`id`),
  FULLTEXT KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci

SQL dotaz

# Query_time: 1.278426  Lock_time: 0.000123  Rows_sent: 7  Rows_examined: 125  Rows_affected: 0
SELECT `id` FROM `search` WHERE (MATCH(`name`) AGAINST('+stále*' IN BOOLEAN MODE)) ORDER BY `score` DESC LIMIT 7;

Počet záznamů

mysql> select count(id) from search;
+-----------+
| count(id) |
+-----------+
|    132990 |
+-----------+
1 row in set (0.04 sec)

Někteří by mohli namítnout, že nad sloupcem score chybí index. V tomto konkrétním případě není nutný, protože se stejně nepoužije. Ve finálním řazení je stejně jen několik záznamů, takže to na výkon vliv nemá.

ALTER TABLE

Fulltext index nemá v rámci MySQL žádné parametry, které by se daly upravit, takže jsme na začátku úplně nevěděli jakým směrem se vydat.

V jeden moment došlo k ALTERu tabulky, který celý problém vyřešil.

ALTER TABLE search ENGINE = InnoDB;

Jedná se o SQL dotaz, který neudělá žádné změny. Pouze tabulku znovu vytvoří, zkopíruje data a s tím vytvoří i znovu všechny indexy, které má tabulka definované.

Od té doby se původní SQL dotaz dostal na rychlost, která byla zákazníkem očekávaná

# Query_time: 0.083153  Lock_time: 0.000119  Rows_sent: 7  Rows_examined: 125  Rows_affected: 0
SELECT `id` FROM `search` WHERE (MATCH(`name`) AGAINST('+stále*' IN BOOLEAN MODE)) ORDER BY `score` DESC LIMIT 7;

Mazání položek z fulltext indexu MySQL

Zeptali jsme se tedy zákazníka jakým způsobem se tabulka plní. Odpověď a následné vysvětlení zpomalení na sebe nenechalo dlouho čekat.

Jednou za den se tabulka celá promaže a následně naplní novými daty.

Promazání tabulky

DELETE FROM search;

Následné naplění

INSERT INTO search (id, name, score) SELECT id, name, (100, ...) FROM book WHERE ... );

SQL bylo záměrně upraveno tak, aby obsahovalo jen to nejdůležitější.

Příčina

Při odstranění záznamu z tabulky s fulltextovým indexem dojde pouze k uložení informace, že záznam byl odstraněn. Z indexu však odstraněn není. Při vyhledávání se nejdříve najdou všechny výskyty daného termínu a až následně se z výsledků vyhledávání vyjmou odstraněné záznamy. Důvodem k tomuto je optimalizace výkonu při odstranění záznamů.

V našem případě se tedy musely při každém vyhledávání z výsledků odstranit všechny záznamy z předchozích dnů, které se tam za celou dobu nashromáždily. A protože se výše zmíněná aktualizace vyhledávací tabulky spouští každý den, tak se jich tam sešlo už znatelné množství, aby to mělo vliv na výkon.

Originál z dokumentace

Deleting a record that has a full-text index column could result in numerous small deletions in the auxiliary index tables, making concurrent access to these tables a point of contention. To avoid this problem, the Document ID (DOCID) of a deleted document is logged in a special FTSDELETED table whenever a record is deleted from an indexed table, and the indexed record remains in the full-text index. Before returning query results, information in the FTS_DELETED table is used to filter out deleted Document IDs. The benefit of this design is that deletions are fast and inexpensive. The drawback is that the size of the index is not immediately reduced after deleting records. To remove full-text index entries for deleted records, run OPTIMIZE TABLE on the indexed table with innodb_optimize_fulltext_only=ON to rebuild the full-text index.

Více informací k tématu

Témata