Comment accélérer sélectionner. Comme les requêtes dans MySQL sur plusieurs colonnes?
J'ai une table MySQL pour laquelle je fais des requêtes SELECT x, y, z FROM table WHERE x LIKE '%text%' OR y LIKE '%text%' OR z LIKE '%text%' très fréquentes. Tout type d'index aider à accélérer les choses?
Il y a quelques millions d'enregistrements dans le tableau. S'il y a quelque chose qui accélérerait la recherche, cela aurait-il un impact sérieux sur l'utilisation du disque par les fichiers de base de données et la vitesse des instructions INSERT et DELETE? (aucun UPDATE n'est jamais effectué)
Update : rapidement après la publication, j'ai vu beaucoup d'informations et de discussions sur la façon dont LIKE est utilisé dans la requête; je voudrais souligner que la solution doit utiliser LIKE '%text%' (c'est-à-dire que le texte que je cherche est ajouté et ajouté avec un % wildcard). La base de données doit également être locale, pour de nombreuses raisons, y compris la sécurité.
6 réponses
Un index n'accélérerait pas la requête, car pour les colonnes textuelles, les index fonctionnent en indexant N caractères à partir de la gauche. Lorsque vous aimez ' % text%', il ne peut pas utiliser l'index car il peut y avoir un nombre variable de caractères avant le texte.
Ce que vous devriez faire est de ne pas utiliser une requête comme ça. Au lieu de cela, vous devriez utiliser quelque chose comme FTS (Full Text Search) que MySQL prend en charge pour les tables MyISAM. Il est également assez facile de faire un tel système d'indexation vous-même pour non-MyISAM tables, vous avez juste besoin d'une table d'index séparée où vous stockez les mots et leurs ID pertinents dans la table réelle.
Un index n'aide pas la correspondance de texte avec un caractère générique principal, un index peut être utilisé pour:
LIKE 'text%'
Mais je suppose que cela ne le coupera pas. Pour ce type de requête, vous devriez vraiment consulter un fournisseur de recherche en texte intégral si vous souhaitez mettre à l'échelle la quantité d'enregistrements que vous pouvez rechercher. Mon fournisseur préféré est Sphinx, très complet/rapide etc. Lucene {[5] } pourrait aussi valoir le coup d'oeil. Un index fulltext sur une table MyISAM fonctionnera également, mais poursuit finalement MyISAM pour toute base de données qui a une quantité importante d'Écritures n'est pas une bonne idée.
Un indice peut - pas être utilisé pour accélérer les requêtes où les critères de recherche commence avec un générique:
LIKE '%text%'
Un index peut (et peut être, selon la sélectivité) utilisé pour les termes de recherche de la forme:
LIKE 'text%'
J'ajouterais que dans certains cas, vous pouvez accélérer la requête en utilisant un index avec like/rlike si le champ que vous regardez est souvent vide ou contient quelque chose de constant.
Dans ce cas, il semble que vous pouvez limiter les lignes visitées en utilisant l'index en ajoutant une clause "and" Avec la valeur fixe.
J'ai essayé ceci pour rechercher des 'tags' dans une énorme table qui ne contient généralement pas beaucoup de tags.
SELECT * FROM objects WHERE tags RLIKE("((^|,)tag(,|$))" AND tags!=''
Si vous avez un index sur les tags, vous verra qu'il est utilisé pour limiter les lignes qui sont recherchées.
Peut-être que vous pouvez essayer de mettre à niveau mysql5.1 vers mysql5.7.
J'ai environ 70 000 disques. Et exécutez SQL suivant:
select * from comics where name like '%test%';
Il faut 2000ms dans mysql5.1. Et il faut 200ms {[7] } dans mysql5. 7 ou mysql5.6.
Une autre alternative pour éviter les analyses de table complètes consiste à sélectionner des sous-chaînes et à les vérifier dans l'instruction having:
SELECT
al3.article_number,
SUBSTR(al3.article_number, 2, 3) AS art_nr_substr,
SUBSTR(al3.article_number, 1, 3) AS art_nr_substr2,
al1.*
FROM
t1 al1
INNER JOIN t2 al2 ON al2.t1_id = al1.id
INNER JOIN t3 al3 ON al3.id = al2.t3_id
WHERE
al1.created_at > '2018-05-29'
HAVING
(art_nr_substr = "FLA" OR art_nr_substr = 'VKV' OR art_nr_subst2 = 'PBR');