Quelle est la surcharge engendrée par une recherche texte ? - Conseil sur la base de données MySQL

Retour à l'accueil

Retour à la liste des conseils

Les recherches dans une base de données constituent un des points les plus sensibles dans la gestion de la charge d'un serveur web.

Ainsi, pour la recherche d'un enregistrement, il est recommandé d'effectuer une recherche grâce à son identifiant numérique.

Cependant, certains sites (notamment Wikipédia et Youtube) semblent se passer des identifiant dans leurs liens.

http://fr.wikipedia.org/wiki/Wikipédia

Une analyse des liens de Wikipédia permet de voir que la recherche du nom d'un article se fait sur la chaîne de caractères. Il n'y a aucune trace d'un identifiant numérique. La recherche se fait donc obligatoirement sur une chaîne de caractères.

http://www.youtube.com/watch?v=Y7dpJ0oseIA

De même, les URLs utilisés par Youtube incluent en paramètre une chaîne de caractères alphanumériques sensibles à la casse. Là aussi, la recherche passe donc forcément par une recherche sur une chaîne de caractères.

Le traitement de ces recherches entraîne une augmentation de l'utilisation des ressources d'une machine.

Protocole


Ce test tente d'estimer le temps supplémentaire nécessaire pour traiter ces requêtes. Les tests sont effectués sur une table MySQL nommée TEST contenant 2 champs : TEST_id et TEST_data. Le champ TEST_id était soit de tpye INTEGER avec un paramètre AUTO_INCREMENT ou un VARCHAR(50). Le champ TEST_data était VARCHAR(50).

Pour niveler les résultats et éliminer les pics d'activités externes, chaque configuration a été testée 10 000 fois.

Les mesures de temps sont effectuées selon l'ordre suivant :

création du tableau des identifiants recherchés;
mesure du temps 1;
connexion à la base de données;
requête;
traitement du résultat et affichage;
mesure du temps 2;

Ces résultats sont représentés par les barres rouges. Pour les mesures de durée de la requête uniquement (les barres vertes sur les graphiques), la "mesure du temps 1" intervient juste après la connexion à la base de données.

La table TEST a été remplie avec 1 000 000 d'enregistrements. Le champ TEST_id était soit alimenté par un entier ou par une chaîne de caractères de type [a-z0-9] de 8 caractères de longueurs en moyenne. Chaque enregistrement était unique.

On peut noter que le temps de remplissage (environ 300 secondes soit 5 minutes) de la table est resté à peu près identique quelque soit les types d'insertions (numériques ou alphanumériques). Les index sur les champs ont été ajoutées ensuite.

Les différences entre les configurations testées portent sur le champ TEST_id :

Nom du testType du champType d'indexPoids
DonnéesIndex
EntierINTEGERAucun26 Mo0 Mo
Chaîne sans indexVARCHAR(50)Aucun30 Mo0 Mo
Chaîne avec indexVARCHAR(50)Index30 Mo11 Mo
Chaîne avec fulltextVARCHAR(50)Fulltext30 Mo17 Mo

La durée de chaque opération a été mesurée avec la fonction microtime(true) au début et à la fin du processus.

Pour chaque test, 2 mesures ont été produites : sans et avec la prise en compte de la connexion à la base de données.

Tests


Avant chaque recherche, un tableau des recherches est constitué pour minimiser l'influence du temps de création d'un identifiant aléatoire.

Les recherches sur le champ TEST_id sont effectuées de 2 manières selon son type :

Pour un champ de type INTEGER :
"SELECT TEST_data FROM TEST WHERE TEST_id=".$i

Pour un champ de type VARCHAR(50), il existe 2 possibilités suivant l'indexation :

Champ VARCHAR(50) non indexé ou avec un index normal :
"SELECT TEST_data FROM TEST WHERE TEST_id='".$i."'"

Champ VARCHAR(50) indexé en fulltext :
"SELECT TEST_data FROM TEST WHERE MATCH(TEST_id) AGAINST ('".$i."')"

1er test : recherche d'un enregistrement aléatoire

Le premier test consiste en une recherche d'un enregistrement aléatoire sur le champ TEST_id. Le résultat est l'enregistrement du champ TEST_data.

Résultats de la recherche aléatoire de 10 enregistrements

La différence est flagrante pour le champ non indexé. Pour la clarté de l'affichage, le graphique suivant a volontairement été limité à un intervalle compris entre 0 et 2 ms. La recherche sur un champ texte sans index prend plus de 1 seconde et dépasse largement le champ de représentation.

Résultats détaillés de la recherche aléatoire de 10 enregistrements

L'avantage est clairement pour une recherche sur un champ de type entier. Cependant on constate que le type d'index influe sur la durée de la recherche. Utiliser un index fulltext entraîne un gain de temps d'environ 30 % par rapport à une indexation classique. Au final, la recherche sur une chaîne de caractères nécessite, dans le meilleur des cas, 27 % de temps supplémentaire qu'une recherche sur un entier.

L'influence du temps nécessaire à la connexion à la base apparait aussi, il est dans ce cas de l'ordre de 0,5 ms.

2ème test : 10 recherches aléatoires d'un enregistrement

Résultats de la recherche aléatoire de 10 enregistrements

La différence apparait aussi nettement. Pour la clarté de l'affichage, le graphique suivant a volontairement été limité à un intervalle compris entre 0 et 10 ms. La recherche sur un champ texte sans index prend plus de 11 secondes et dépasse largement le champ de représentation.

Résultats détaillés de la recherche aléatoire de 10 enregistrements

Le temps de connexion prend ici moins d'importance. Les conclusions du test précédent sont toujours valables. Une recherche sur un champ indexé de type chaîne de caractères prend 2 fois plus de temps qu'une recherche sur un champ de type entier. La recherche en plein texte nécessite 30 % de temps supplémentaire.

3ème test : 100 recherches aléatoires d'un enregistrement

Résultats de la recherche aléatoire de 100 enregistrements

Les précédentes différences restent d'actualité. Pour la clarté de l'affichage, le graphique suivant a volontairement été limité à un intervalle compris entre 0 et 100 ms. La recherche sur un champ texte sans index prend plus de 118 secondes et dépasse largement le champ de représentation.

Résultats détaillés de la recherche aléatoire de 100 enregistrements

Les conclusions des 2 tests précédents sont toujours valables. Une recherche sur un champ indexé de type chaîne de caractères prend 2 fois plus de temps qu'une recherche sur un champ de type entier. La recherche en texte intégral nécessite 28 % de temps supplémentaire.

Conclusion


Logiquement, la recherche sur une chaîne de caractères nécessite plus de temps de traitement. Cependant, les différentes manières d'indexation d'un champ de type chaîne de caractères permettent de limiter cette augmentation. L'index classique nécessite en moyenne 2 fois plus de temps qu'une recherche sur un entier.

La méthode la plus efficace passe par l'utilisation d'une recherche de texte libre (fulltext) qui entraine quand même un traitement 30 % plus long. L'inconvénient de cette méthode réside dans l'espace disque supplémentaire occupé par l'indexation.

Version classique - Contact - Règles de confidentialité