Index de texte intégral composite dans MySQL-Questions et réponses sur le réseau chinois PHP

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.

Index simpleet non uniqueuser_id

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

Cela recherche tous les messages de tous les utilisateurs, puis filtre par ID, ce qui semble inefficace lorsque le volume d'utilisateurs est important.
Compositeindexde
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 parIDpuis 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'indexuser_id.

répondre à tous (2)

P粉4211197782023-11-08 15:18:58 2 plancher

Vous devez ajouter un index régulier surmessage上添加全文索引，在user_idet 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 voussavezqu'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.

Comme+0

Ajouter une réponse

P粉0769873862023-11-08 12:16:14 1 plancher

La réponse de @Alden Quimby est correcte en soi, mais il y a plus à dire, car MySQL va seulementessayerde 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égralIN 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".