Une introduction à la Recherche Full Text avec MariaDB
Source : https://severalnines.com/database-blog/introduction-full-text-search-mariadb
Les bases de données sont destinées à stocker et rechercher des données efficacement. Le problème est qu’il y a de nombreux types de données que nous pouvons stocker : des nombres, des chaînes de caractères, JSON, des données géométriques. Les bases de données utilisent différentes méthodes pour stocker différents types de données – structures de tables, indexes. Ce n’est pas toujours la même manière de stocker et de rechercher la donnée qui est efficiente pour tous les types, ce qui rend difficile d’utiliser une solution passe-partout.
En conséquence, les bases de données essaient d’utiliser des approches différentes pour différents types de données. Par exemple, en MySQL ou MariaDB nous avons la solution générique et bien performante telle que InnoDB, qui marche bien dans la majorité des cas, mais nous avons aussi des fonctions séparées pour travailler avec des données JSON, des index spatiaux séparés pour accélérer les requêtes sur les données géométriques ou des indexes fulltext, aidant avec les données de texte.
Dans cet article, nous allons regarder comment MariaDB peut être utilisée pour travailler avec des données full text .
Les index réguliers B+Tree en InnoDB peuvent être aussi utilisés pour accélérer les recherches pour les données texte. Le principal problème avec cela, à cause de leur structure et nature, ils peuvent seulement aider avec les recherches sur les préfixes les plus à gauche. C’est aussi couteux d’indexer de gros volumes de texte (ce qui, compte tenu des limitations des préfixes gauches, ne fait pas vraiment sens). Pourquoi ? Examinons un exemple simple. Nous avons la phrase suivante:
“The quick brown fox jumps over the lazy dog”
En utilisant des indexes réguliers en InnoDB nous pouvons indexer la phrase complète:
“The quick brown fox jumps over the lazy dog”
Les point est que en recherchant cette donnée, nous devons examiner le préfixe gauche. Alors une requête comme:
SELECT text FROM mytable WHERE sentence LIKE “The quick brown fox jumps”; |
profitera de cet index. Mais une requête comme:
SELECT text FROM mytable WHERE sentence LIKE “quick brown fox jumps”; |
n’en profitera pas. Il n’y a pas d’entrée dans l’index qui commence par ‘quick’.
Il y a une entrée dans l’index qui contient ‘quick’ mais elle commence par ‘The’, alors elle ne peut pas être utilisée. En conséquence, il est virtuellement impossible de rechercher de manière efficient des données texte en utilisant les indexes B+Tree.
Par chance, Aussi bien MyISAM que InnoDB ont implementé les indexes FULLTEXT, qui peuvent être utilisés pour travailler avec des données texte dans MariaDB. La syntaxe est légèrement différente des SELECTs classiques, Voyons ce que nous pouvons faire grâce à eux. Comme données nous avons utilisé un fichier d’index qulconque au hasard depuis le dump of Wikipedia database. La structure des données est comme ci-dessous:
617:11539268:Arthur Hamerschlag617:11539269:Rooster Cogburn (character)617:11539275:Membership function617:11539282:Secondarily Generalized Tonic-Clonic Seizures617:11539283:Corporate Challenge617:11539285:Perimeter Mall617:11539286:1994 St. Louis Cardinals season |
En conséquence, nous avons créé dans MariaDB une table avec deux colonnes BIG INT et une colonne VARCHAR.
> CREATE TABLE ft_table (c1 BIGINT, c2 BIGINT, c3 VARCHAR, PRIMARY KEY (c1, c2); |
Ce après quoi nous avons chargé les données depuis le fichier:
> LOAD DATA INFILE '/vagrant/enwiki-20190620-pages-articles-multistream-index17.txt-p11539268p13039268' IGNORE INTO TABLE ft_table COLUMNS TERMINATED BY ':'; |
> ALTER TABLE ft_table ADD FULLTEXT INDEX idx_ft (c3);Query OK, 0 rows affected (5.497 sec)Records: 0 Duplicates: 0 Warnings: 0 |
Nous avons aussi créé l’index FULLTEXT. Comme vous pouvez le voir, la syntaxe pour cela est similaire aux indexes classiques, nous devons juste passer l’information concernant le type d’index, car le type par défaut est B+Tree. Maintenant nous sommes prêts à lancer quelques requêtes.
> SELECT * FROM ft_table WHERE MATCH(c3) AGAINST ('Starship');+-----------+----------+------------------------------------++-----------+----------+------------------------------------+| 119794610 | 12007923 | Starship Troopers 3 || 250627749 | 12479782 | Miranda class starship (Star Trek) || 250971304 | 12481409 | Starship Hospital || 253430758 | 12489743 | Starship Children's Hospital |+-----------+----------+------------------------------------+4 rows in set (0.009 sec) |
Comme vous pouvez le voir, la syntaxe pour le SELECT est légèrement différente de ce à quoi nous sommes habitués. Pour une recherche fulltext vous devriez utiliser la syntaxe MATCH() … AGAINST () , dans laquelle dans MATCH() vous passez la colonne ou les colonnes sur lesquelles vous voulez faire des recherches et dans AGAINST() vous passez, séparés par des virgules, des listes de mots-clés. Vous pouvez voir dans le résultat que par défaut la recherche n’est pas sensible à la casse, et qu’elle cherche sur la chaîne complète, pas seulement sur le début comme cela se fait avec les indexes B+Tree. Comparons à quoi cela ressemble si nous ajoutons un index normal sur la colonne ‘c3’ – Les indexes FULLTEXT et B+Tree peuvent coexister sur la même colonne sans aucun problème. Lequel sera utilisé est décidé basé sur la syntaxe du SELECT.
> ALTER TABLE ft_table ADD INDEX idx_c3 (c3);Query OK, 0 rows affected (1.884 sec)Records: 0 Duplicates: 0 Warnings: 0 |
Une fois que l’index a été créé, examinons le résultat de la recherche:
> SELECT * FROM ft_table WHERE c3 LIKE 'Starship%';+-----------+----------+------------------------------+| c1 | c2 | c3 |+-----------+----------+------------------------------+| 253430758 | 12489743 | Starship Children's Hospital || 250971304 | 12481409 | Starship Hospital || 119794610 | 12007923 | Starship Troopers 3 |+-----------+----------+------------------------------+3 rows in set (0.001 sec) |
Comme vous pouvez le voir, notre requête n’a retourné que 3 lignes. Ceci est attendu, étant donné que nous ne recherchons que les lignes qui commencent par la chaîne ‘Starship’.
> EXPLAIN SELECT * FROM ft_table WHERE c3 LIKE 'Starship%'\G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: ft_table type: rangepossible_keys: idx_c3,idx_ft key: idx_c3 key_len: 103 ref: NULL rows: 3 Extra: Using where; Using index1 row in set (0.000 sec) |
Quand nous examinons la sortie de EXPLAIN nous pouvons voir que l’index a été utilisé pour rechercher les données. Mais comment faire si nous voulons trouver toutes les lignes qui contiennent la chaîne ‘Starship’, qu’elle soit au début ou pas ? Nous devons écrire la requête suivante :
> SELECT * FROM ft_data.ft_table WHERE c3 LIKE '%Starship%';+-----------+----------+------------------------------------+| c1 | c2 | c3 |+-----------+----------+------------------------------------+| 250627749 | 12479782 | Miranda class starship (Star Trek) || 253430758 | 12489743 | Starship Children's Hospital || 250971304 | 12481409 | Starship Hospital || 119794610 | 12007923 | Starship Troopers 3 |+-----------+----------+------------------------------------+4 rows in set (0.084 sec) |
Le résultat correspond à ce que nous obtenons avec la recherche fulltext.
> EXPLAIN SELECT * FROM ft_data.ft_table WHERE c3 LIKE '%Starship%'\G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: ft_table type: indexpossible_keys: NULL key: idx_c3 key_len: 103 ref: NULL rows: 473367 Extra: Using where; Using index1 row in set (0.000 sec) |
Le EXPLAIN est différent cependant – comme vous pouvez le voir il utilise toujours des indexes mais cette fois il effectue un scan full index. Cela est rendu possible car nous avons indexée « full » la colonne c3 alors toutes les données sont disponibles dans l’index. Le parcours de l’index va entrainer des lectures aléatoires depuis la table mais pour une telle petite table MariaDB a décidé que c’est plus efficient que de lire la table entière. Veuillez noter le temps d’éxécution: 0.084s pour notre classique SELECT. Comparé à cette requête fulltext, c’est mauvais:
> SELECT * FROM ft_table WHERE MATCH(c3) AGAINST ('Starship');+-----------+----------+------------------------------------+| c1 | c2 | c3 |+-----------+----------+------------------------------------+| 119794610 | 12007923 | Starship Troopers 3 || 250627749 | 12479782 | Miranda class starship (Star Trek) || 250971304 | 12481409 | Starship Hospital || 253430758 | 12489743 | Starship Children's Hospital |+-----------+----------+------------------------------------+4 rows in set (0.001 sec) |
Comme vous pouvez le voir, la requête qui utilise un index FULLTEXT a pris 0.001s pour s’exécuter. Nous parlons ici de différences d’ordres de magnitudes.
> EXPLAIN SELECT * FROM ft_table WHERE MATCH(c3) AGAINST ('Starship')\G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: ft_table type: fulltextpossible_keys: idx_ft key: idx_ft key_len: 0 ref: rows: 1 Extra: Using where1 row in set (0.000 sec) |
Ceci est ce à quoi ressemble le résultat de EXPLAIN pour la requête utilisant l’index FULLTEXT – Ce fait est indiqué par type: fulltext.
Les requêtes Fulltext ont aussi d’autres fonctionnalités. Il est possible, par exemple, de retourner les lignes qui pourraient être pertinentes pour les termes de la recherche. MariaDB recherche des mots situés près de la chaîne que vous recherchez, puis lance une recherche aussi sur eux.
> SELECT * FROM ft_table WHERE MATCH(c3) AGAINST ('Starship');+-----------+----------+------------------------------------+| c1 | c2 | c3 |+-----------+----------+------------------------------------+| 119794610 | 12007923 | Starship Troopers 3 || 250627749 | 12479782 | Miranda class starship (Star Trek) || 250971304 | 12481409 | Starship Hospital || 253430758 | 12489743 | Starship Children's Hospital |+-----------+----------+------------------------------------+4 rows in set (0.001 sec) |
Dans notre cas, le mot ‘Starship’ peut être relatif à des mots comme ‘Troopers’, ‘class’, ‘Star Trek’, ‘Hospital’ etc. Pour utiliser cette fonctionnalité nous devrions utiliser cette requête avec le modificateur “WITH QUERY EXPANSION” :
> SELECT * FROM ft_table WHERE MATCH(c3) AGAINST ('Starship' WITH QUERY EXPANSION) LIMIT 10;+-----------+----------+-------------------------------------+| c1 | c2 | c3 |+-----------+----------+-------------------------------------+| 250627749 | 12479782 | Miranda class starship (Star Trek) || 119794610 | 12007923 | Starship Troopers 3 || 253430758 | 12489743 | Starship Children's Hospital || 250971304 | 12481409 | Starship Hospital || 277700214 | 12573467 | Star ship troopers || 86748633 | 11886457 | Troopers Drum and Bugle Corps || 255120817 | 12495666 | Casper Troopers || 396408580 | 13014545 | Battle Android Troopers || 12453401 | 11585248 | Star trek tos || 21380240 | 11622781 | Who Mourns for Adonais? (Star Trek) |+-----------+----------+-------------------------------------+10 rows in set (0.002 sec) |
Le résultat contient un grand nombre de lignes mais cet exemple est suffisant pour voir comment cela fonctionne. La requête a retourné des lignes comme:
“Troopers Drum and Bugle Corps”
“Battle Android Troopers”
Celles-ci sont basées sur la recherche du mot ‘Troopers’. Elle a aussi retourné des lignes avec des chaînes telles que :
“Star trek tos”
“Who Mourns for Adonais? (Star Trek)”
Lesquelles, de toute évidence, sont basées sur la recherche du mot ‘Start Trek’.
Si vous vouliez plus de contrôle sur le terme que vous voulez rechercher, vous pouvez utiliser “IN BOOLEAN MODE”. Cela permet d’utiliser des opérateurs additionnels. La liste complète est dans la documentation, Nous verrons seulement quelques exemple.
Disons que nous ne voulons pas rechercher juste le mot ‘Star’ mais aussi d’autres mots qui commencent par la chaîne ‘Star’:
> SELECT * FROM ft_table WHERE MATCH(c3) AGAINST ('Star*' IN BOOLEAN MODE) LIMIT 10;+----------+----------+---------------------------------------------------+| c1 | c2 | c3 |+----------+----------+---------------------------------------------------+| 20014704 | 11614055 | Ringo Starr and His third All-Starr Band-Volume 1 || 154810 | 11539775 | Rough blazing star || 154810 | 11539787 | Great blazing star || 234851 | 11540119 | Mary Star of the Sea High School || 325782 | 11540427 | HMS Starfish (19S) || 598616 | 11541589 | Dwarf (star) || 1951655 | 11545092 | Yellow starthistle || 2963775 | 11548654 | Hydrogenated starch hydrolysates || 3248823 | 11549445 | Starbooty || 3993625 | 11553042 | Harvest of Stars |+----------+----------+---------------------------------------------------+10 rows in set (0.001 sec) |
Comme vous pouvez le voir, dans le résultat nous avons des lignes qui contiennent des chaînes comme ‘Stars’, ‘Starfish’ or ‘starch’.
Un autre cas d’usage pour le mode BOOLEAN. Disons que nous voulons rechercher les lignes qui concernent le House of Representatives in Pennsylvania. Si nous lancions une requête classique, nous obtiendrons des résultats en quelque sorte en relation to any of those strings:
> SELECT COUNT(*) FROM ft_table WHERE MATCH(c3) AGAINST ('House, Representatives, Pennsylvania');+----------+| COUNT(*) |+----------+| 1529 |+----------+1 row in set (0.005 sec) |
> SELECT * FROM ft_table WHERE MATCH(c3) AGAINST ('House, Representatives, Pennsylvania') LIMIT 20;+-----------+----------+--------------------------------------------------------------------------+| c1 | c2 | c3 |+-----------+----------+--------------------------------------------------------------------------+| 198783294 | 12289308 | Pennsylvania House of Representatives, District 175 || 236302417 | 12427322 | Pennsylvania House of Representatives, District 156 || 236373831 | 12427423 | Pennsylvania House of Representatives, District 158 || 282031847 | 12588702 | Pennsylvania House of Representatives, District 47 || 282031847 | 12588772 | Pennsylvania House of Representatives, District 196 || 282031847 | 12588864 | Pennsylvania House of Representatives, District 92 || 282031847 | 12588900 | Pennsylvania House of Representatives, District 93 || 282031847 | 12588904 | Pennsylvania House of Representatives, District 94 || 282031847 | 12588909 | Pennsylvania House of Representatives, District 193 || 303827502 | 12671054 | Pennsylvania House of Representatives, District 55 || 303827502 | 12671089 | Pennsylvania House of Representatives, District 64 || 337545922 | 12797838 | Pennsylvania House of Representatives, District 95 || 219202000 | 12366957 | United States House of Representatives House Resolution 121 || 277521229 | 12572732 | United States House of Representatives proposed House Resolution 121 || 20923615 | 11618759 | Special elections to the United States House of Representatives || 20923615 | 11618772 | List of Special elections to the United States House of Representatives || 37794558 | 11693157 | Nebraska House of Representatives || 39430531 | 11699551 | Belgian House of Representatives || 53779065 | 11756435 | List of United States House of Representatives elections in North Dakota || 54048114 | 11757334 | 2008 United States House of Representatives election in North Dakota |+-----------+----------+--------------------------------------------------------------------------+20 rows in set (0.003 sec) |
Comme vous pouvez le voir, nous avons trouvé quelques données utiles mais nous avions aussi trouvé des données qui ne sont absolument pas relevant à notre recherche. Par chance, nous pouvons affiner une telle requête :
> SELECT * FROM ft_table WHERE MATCH(c3) AGAINST ('+House, +Representatives, +Pennsylvania' IN BOOLEAN MODE);+-----------+----------+-----------------------------------------------------+| c1 | c2 | c3 |+-----------+----------+-----------------------------------------------------+| 198783294 | 12289308 | Pennsylvania House of Representatives, District 175 || 236302417 | 12427322 | Pennsylvania House of Representatives, District 156 || 236373831 | 12427423 | Pennsylvania House of Representatives, District 158 || 282031847 | 12588702 | Pennsylvania House of Representatives, District 47 || 282031847 | 12588772 | Pennsylvania House of Representatives, District 196 || 282031847 | 12588864 | Pennsylvania House of Representatives, District 92 || 282031847 | 12588900 | Pennsylvania House of Representatives, District 93 || 282031847 | 12588904 | Pennsylvania House of Representatives, District 94 || 282031847 | 12588909 | Pennsylvania House of Representatives, District 193 || 303827502 | 12671054 | Pennsylvania House of Representatives, District 55 || 303827502 | 12671089 | Pennsylvania House of Representatives, District 64 || 337545922 | 12797838 | Pennsylvania House of Representatives, District 95 |+-----------+----------+-----------------------------------------------------+12 rows in set (0.001 sec) |
Comme vous pouvez le voir, en ajoutant l’opérateur ‘+’ nous avons indiqué clairement que nous ne sommes intéressés que par les résultat où les mots concernés existent. En conséquence les données que nous obtenons en retour sont exactement ce que nous recherchions.
Nous pouvons aussi exclure des mots de la recherche. Disons que nous recherchons des choses volantes mais nos résultats de recherche sont contaminés par différent animaux volants qui ne nous intéressent pas. Nous pouvons facilement nous débarrasser des foxes (renards), squirrels (écureuils) and frogs (grenouilles):
> SELECT * FROM ft_table WHERE MATCH(c3) AGAINST ('+flying -fox* -squirrel* -frog*' IN BOOLEAN MODE) LIMIT 10;+----------+----------+-----------------------------------------------------+| c1 | c2 | c3 |+----------+----------+-----------------------------------------------------+| 13340153 | 11587884 | List of surviving Boeing B-17 Flying Fortresses || 16774061 | 11600031 | Flying Dutchman Funicular || 23137426 | 11631421 | 80th Flying Training Wing || 26477490 | 11646247 | Kites and Kite Flying || 28568750 | 11655638 | Fear of Flying || 28752660 | 11656721 | Flying Machine (song) || 31375047 | 11666654 | Flying Dutchman (train) || 32726276 | 11672784 | Flying Wazuma || 47115925 | 11728593 | The Flying Locked Room! Kudou Shinichi's First Case || 64330511 | 11796326 | The Church of the Flying Spaghetti Monster |+----------+----------+-----------------------------------------------------+10 rows in set (0.001 sec) |
La dernière fonctionnalité que nous désirons montrer est la capacité de rechercher des quotes (apostrophes) précis:
> SELECT * FROM ft_table WHERE MATCH(c3) AGAINST ('"People\'s Republic of China"' IN BOOLEAN MODE) LIMIT 10;+-----------+----------+------------------------------------------------------------------------------------------------------+| c1 | c2 | c3 |+-----------+----------+------------------------------------------------------------------------------------------------------+| 12093896 | 11583713 | Religion in the People's Republic of China || 25280224 | 11640533 | Political rankings in the People's Republic of China || 43930887 | 11716084 | Cuisine of the People's Republic of China || 62272294 | 11789886 | Office of the Commissioner of the Ministry of Foreign Affairs of the People's Republic of China in t || 70970904 | 11824702 | Scouting in the People's Republic of China || 154301063 | 12145003 | Tibetan culture under the People's Republic of China || 167640800 | 12189851 | Product safety in the People's Republic of China || 172735782 | 12208560 | Agriculture in the people's republic of china || 176185516 | 12221117 | Special Economic Zone of the People's Republic of China || 197034766 | 12282071 | People's Republic of China and the United Nations |+-----------+----------+------------------------------------------------------------------------------------------------------+10 rows in set (0.001 sec) |
Il est aussi possible de lancer des recherches FullText sur plusieurs colonnes d’une même table. Par exemple vous avez créé une boutique en ligne, et vous souhaitez que les internautes puissent chercher une expression sur la référence du produit ET la désignation ET la description longue.
La première étape est d’ajouter l’index FULLTEXT :
> ALTER TABLE goods ADD FULLTEXT(reference,short_descr,description);
Il est désormais possible de lancer des recherche sur ces 3 colonnes simultanément. Exemple :
> SELECT reference, short_descr FROM goods WHERE MATCH(reference,short_descr,description) AGAINST ('harley');
Le résultat affichera les lignes pour lesquelles la chaîne « harley » se trouve au moins dans une des 3 colonnes « reference », « short_descr », « description » :
+-----------+----------------------------------------------------------+ | reference | short_descr | +-----------+----------------------------------------------------------+ | 959505 | NESS MINI OVAL BLACK MIRROR | | 23100010 | 15 | | 516304 | Blue Streak. point tune-up kit | | 548157 | NAMZ. Deutsch DT connectors. Black. plug housing. 3-pins | | 548145 | NAMZ. Deutsch connectors. Black. receptable. 3-pins | | 936413 | Tsubaki 530 QRB Gamma chain. 120 links | | 903942 | Tsubaki 530 QRB Gamma chain. 112 links | +-----------+----------------------------------------------------------+ 7 rows in set (0.001 sec)
Comme vous pouvez le voir, la recherche fulltext dans MariaDB fonctionne assez bien, c’est aussi plus rapide et plus flexible que de chercher en utilisant les indexes B+Tree.
Veuillez gardez à l’esprit cependant que ce n’est en aucun cas un moyen de gérer de gros volumes de données – avec l’accroissement des données, la faisabilité de cette solution va se réduire.
Néanmois, pour les petits jeux de données cette solution est parfaitement valide. Cela peut certainement vous faire gagner plus de performance, éventuellement, d’implémenter des solutions fulltext dédiées comme Sphinx ou Lucene.
