Index de texte intégral composite dans MySQL

Question

Je souhaite que le système autorise la recherche de messages utilisateur par utilisateurs spécifiques. Supposons qu'il y ait le tableau suivant

create table messages(
  user_id int,
  message nvarchar(500));

Si je souhaite rechercher tous les messages de l'utilisateur 1 contenant le mot "foo", quel index dois-je utiliser ici.

1. Index simple et non unique user_id

   
   

   Il filtrera uniquement les messages utilisateur spécifiques, puis recherchera entièrement les mots spécifiques.

2. Texte intégralindex des messages

   
   

   Cela recherche tous les messages de tous les utilisateurs, puis filtre par ID, ce qui semble inefficace lorsque le volume d'utilisateurs est important.

3. Compositeindex de

   user_id
   et

   message

   Par conséquent, l'arborescence d'indexation en texte intégral est créée individuellement pour chaque utilisateur et peut donc être recherchée individuellement. Pendant la requête, le système filtre les messages par ID puis effectue une recherche de texte sur les lignes restantes de l'index.

Pour autant que je sache. Le dernier élément est impossible. Je suppose donc que je devrais utiliser la première option, fonctionnera-t-elle mieux s’il y a quelques milliers d’utilisateurs ?

Une itération complète ne coûterait-elle pas trop de ressources s'il y avait environ 100 messages chacun ?

Peut-être que je pourrais inclure le nom d'utilisateur dans le message et utiliser le mode de recherche en texte intégral BOOLÉEN, mais je pense que cela serait plus lent que d'utiliser l'index user_id.

Answer 1

Vous devez ajouter un index régulier sur message 上添加全文索引，在 user_id et utiliser la requête suivante :

SELECT *
FROM messages
WHERE MATCH(message) AGAINST(@search_query)
AND user_id = @user_id;

Vous avez raison, vous ne pouvez pas faire l'option 3. Mais au lieu d’essayer de choisir entre 1 et 2, laissez MySQL faire le travail à votre place. MySQL n'utilisera qu'un des deux index et effectuera une analyse linéaire pour compléter le deuxième filtrage, mais il estimera l'efficacité de chaque index et choisira le meilleur.

REMARQUE : ne faites cela que si vous pouvez vous permettre la surcharge de deux index (insertions/mises à jour/suppressions plus lentes). De plus, si vous savez qu'il n'y aura que quelques messages par utilisateur, il peut être judicieux d'utiliser un simple index et de faire une regex ou quelque chose de similaire dans la couche d'application.

Answer 2

La réponse de @Alden Quimby est correcte en soi, mais il y a plus à dire, car MySQL va seulement essayer de choisir le meilleur index, et sa capacité à prendre cette décision est limitée en raison des index en texte intégral. Comment interagir avec l'optimiseur.

Ce qui s'est réellement passé est ceci :

Si l'id_utilisateur spécifié existe dans 0 ou 1 lignes correspondantes dans la table, l'optimiseur s'en rendra compte et sélectionnera user_id comme index pour cette requête. Exécutez rapidement.

Sinon, l'optimiseur sélectionnera l'index de texte intégral, en filtrant chaque ligne qui correspond à l'index de texte intégral pour éliminer les lignes qui ne contiennent pas d'identifiant utilisateur correspondant à la clause WHERE. Pas si vite.

Ce n’est donc pas vraiment la « meilleure » voie. C'est plutôt du texte intégral, avec une belle optimisation qui évite de faire une recherche en texte intégral alors qu'on sait qu'il n'y a presque rien d'intéressant dans le tableau.

Cela se produit car l'index de texte intégral ne fournit aucune statistique significative à l'optimiseur. Il dit simplement "oui, je pense que la requête pourrait ne me demander de vérifier qu'une seule ligne"... Bien sûr, cela satisfait grandement l'optimiseur, donc l'index de texte intégral remporte l'enchère avec le coût le plus bas, à moins que l'index n'ait un nombre entier. valeur trop Relativement faible ou inférieure.

Cela ne veut pas dire que je n’essaierai pas ça en premier.

Il existe une autre option, mieux adaptée aux requêtes en texte intégral IN BOOLEAN MODE, qui consiste à créer une autre colonne que vous pouvez remplir avec CONCAT('user_id_',user_id) ou similaire, puis à déclarer un index de texte intégral à 2 colonnes.

filter_string VARCHAR(48) # populated with CONCAT('user_id_',user_id);
....
FULLTEXT KEY (message,filter_string)

Puis précisez tout dans la requête.

SELECT ...
 WHERE user_id = 500 AND
 MATCH (message,filter_string) AGAINST ('+kittens +puppies +user_id_500' IN BOOLEAN MODE);

Maintenant, l'index de texte intégral sera chargé de faire correspondre uniquement les lignes où les chatons, les chiots et "user_id_500" apparaissent dans l'index de texte intégral combiné des deux colonnes, mais vous souhaitez toujours y avoir un filtre entier pour garantir que le le résultat final est restreint malgré des occurrences aléatoires dans le message "user_id_500".