Quelques astuces MySQL

applications internet utilitaires

Ce site ne sera plus alimenté de contenu après août 2014. Tous les nouveaux articles seront redigés pour www.waitingforcode.com
Malgré les tentatives d'introduction de MariaDB, MySQL fait encore partie inséparable du PHP dans la majorité des applications web. Dans cet article on explorera un peu plus ce moteur de base de données afin de comprendre et de découvrir ses quelques aspects.

Au tout début on abordera la thématique du tri personnalisé, en fonction des champs définis dans la base. La deuxième partie sera destinée à la recherche avec plusieurs éléments existants ou non existants dans une autre table. A la fin on analysera une méthode d'optimisation de l'exécution de la requête contenant les dates.

Avant de commencer, voici la structure des tables qu'on allait utiliser dans nos exemples. Les tables seront remplies par les scripts que vous pouvez téléchargez.


CREATE TABLE IF NOT EXISTS history (
id_hi int(11) NOT NULL AUTO_INCREMENT,
date_hi DATETIME NOT NULL,
PRIMARY KEY (id_hi)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS dates (
id_da int(11) NOT NULL AUTO_INCREMENT,
date_da DATE NOT NULL,
PRIMARY KEY (id_da)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS players (
id_pl int(11) unsigned NOT NULL AUTO_INCREMENT,
fname_pl varchar(20) NOT NULL,
lname_pl varchar(30) NOT NULL,
position_pl char(2) NOT NULL,
borndate_pl date NOT NULL,
PRIMARY KEY (id_pl)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


Tri personnalisé en MySQL
L'affichage des données dans les tableaux impose souvent l'utilisation du tri. Dans MySQL on peut facilement trier les données par les valeurs des colonnes en utilisant "ORDER BY column_name ASC|DESC". Cependant, on peut pousser le visse encore plus loin et gérer un tri personnalisé grâce à la méthode "ORDER BY FIELD()".

Pour illustrer l'utilisation de cette méthode, regardons notre table "players". Imaginons qu'on veut récupérer tous les joueurs, en commençant par gardiens, en passant par les défenseurs et les milieux de terrain, pour terminer sur les attaquants. Voici la requête qui fera l'affaire:


SELECT * FROM players ORDER BY FIELD(position_pl, "GK", "DC", "MC", "ST");


Le fonctionnement paraît évident. Le premier paramètre est le nom de la colonne à trier. Ensuite on place une liste des valeurs dans l'ordre souhaité.

EXISTS or NOT EXISTS ?
Les conditions IN() et NOT IN() sont celles de base qu'on découvre quasiment au tout début du contact avec MySQL. Cependant, souvent avec beaucoup de données à traiter, elles sont trop lentes. Une des alternatives à elles peuvent être conditions EXISTS() et NOT EXISTS().

Le fonctionnement de ces deux conditions ressemble énormément à celui de IN() et NOT IN() :
- EXISTS() : retourne TRUE si aucune occurrence n'a été trouvée dans la sous-requête
- NOT EXISTS() : retourne FALSE si aucune ligne n'a été trouvée dans la sous-requête

Pourquoi EXISTS() est plus rapide que IN() ? Contrairement à IN(), EXISTS() retourne le résultat (booléan) après avoir trouvé la première occurrence recherchée. Cela trouve bien évidement son utilité dans toutes les tables qui peuvent posséder les duplicatas de données.

Voici les deux requêtes qui illustreront la différence dans les performances des deux méthodes de recherche. Dans ces cas, on cherche à savoir pour quels joueurs on a les dates correspondantes dans la grosse table history. On peut le faire soit avec EXISTS(), soit avec IN(). Voici un benchmark des deux méthodes :

SELECT * FROM players WHERE EXISTS(SELECT id_hi FROM history WHERE DATE(date_hi) = players.borndate_pl);

Temps moyen d'exécution sur 1 058 283 lignes : 16.14 sec


SELECT * FROM players WHERE borndate_pl IN (SELECT DATE(date_hi) FROM history);

Temps moyen d'exécution sur 1 058 283 lignes : 19.24 sec

La différence est, certes, peu significative, mais elle prouve déjà un certain avantage de l'EXISTS() par rapport à l'IN().

Optimiser des dates en MySQL
Devant gérer une table pesant approximativement 600mb, dont les données doivent être recherchées en fonction des dates, on peut être confronté à un grave problème de réactivité du côté MySQL. Malgré la présence d'un index sur le champ avec la date, certaines requêtes peuvent prendre une vingtaine de secondes à s'effectuer. Le problème peut être évité grâce à INTERVAL.

Imaginons la situation où la requête gourmande se présente de cette manière (en fonction du paramètre, la recherche s'effectuait sur un an, un mois ou 1 jour en utilisant les méthodes YEAR, MONTH ou DATE) :

SELECT * FROM history WHERE DATE(date_hi) = '$searchedDate'

Temps moyen d'exécution sur 1 058 283 lignes : 0.19372 sec

La requête plus performante est la suivante :

SELECT * FROM history WHERE date_hi BETWEEN DATE '$searchedDate' AND '$searchedDate' + INTERVAL 1 DAY

Temps moyen d'exécution sur 1 058 283 lignes : 0.096 sec pour la date 2015-03-28
Les deux tests ont été effectués sur la date 2015-03-28

Pourquoi la deuxième requête double quasiment les performances de la première ? La présence d'INTERVAL y est pour quelque chose. INTERVAL permet de déterminer le nombre de jours, mois ou années à rajouter à la date recherchée. Concernant remplacement de conversion du champ date_hi (DATE(date_hi)) par BETWEEN. Il permet au MySQL d'utiliser l'index sur le champ date_hi (un discussion pour en savoir plus). Sans cela, le moteur de la base traite chaque ligne de la table sans prendre en compte les indexes spécifiés sur les colonnes recherchées.

On a vu les astuces qui permettent de décharger le langage côté serveur des tâches inutiles (ordre personnalisé). On a également pu découvrir comment accélérer l'exécution de certaines requêtes SQL. Et tout cela pour être prêt d’accueillir les millions de lignes dans notre bases de données.
Bartosz KONIECZNY 22-07-2012 19:31 applications web
Moi

Développeur d'applications Internet et journaliste passionné par l'adjectif français. Un aigle polonais orienté vers la progression, volant très haut et écoutant du zouk après les matches du foot français.

Vous appréciez mon travail ?

Pour contribuer au développement de ce site, ou pour remercier pour des articles rédigés, vous pouvez faire un don.

Un conseil Symfony2

A quoi peut être lié le problème "Unknown Entity namespace alias" ?

Ce problème peut apparaître dans Symfony2 parce que le bundle de l'entité peut ne pas être défini dans AppKernel.