Base de données : quelques conseils

dimanche 23 septembre 2012 à 17:01

Ayant travaillé assez souvent dans les profondeurs des bases de données, je vous propose quelques petits conseils afin de bien utiliser votre base de donnée, dans tous les sens.

Bien créer sa base de donnée

Tout commence par la création. En théorie, on ne devrait jamais utiliser les tables en MyISAM et ne faire quasiment que de l'innoDB (sauf pour d'éventuelles tables de back up si on est très à cheval). En pratique, rares sont les projets qui utilisent les commit et les rollback et plus rares sont ceux à les utiliser convenablement. Donc vu les performances en écriture de MyISAM, on préferera ce moteur de classement (sauf bien sûr pour des projets utilisant les transaction). De manière plus simple, si du traitement doit être fait en base de donnée (les adeptes du MVC vont s'arracher les cheveux), préférez InnoDB.

Concernant l'interclassement, je conseil utf8_general_ci. Le «ci» signifie «case insensitive»; concrètement, ça veux dire que lorsque vous ferez une recherche «where user="toto"», ça reviendra au même que «where user="TOTO"» C'est a prendre en considération. Naturellement, si votre projet est en ANSI (ce que vous ne devriez pas faire en PHP), il vaudra mieux utiliser le latin1_general_ci. Il se peut que selon les projets vous ayez besoin d'utiliser des interclassement un peu exotique. Rappelez vous juste que faire une jointure entre deux tables qui n'ont pas le même interclassement peut donner des résultats éronés… Apprenez a utiliser la fonction «collate» pour ce genre de requêtes.

Les procédures stockées

Les procédures stockées (PS) sont l'équivalent de fonctions qui sont déportées en base de données. Dans un projet, il faut éviter tant que possible les traitement qui feront 150 appels à la DB car en production, le temps d'exécution d'une requête n'est pas la seule chose à prendre en compte. Il ne faut pas oublier que bien souvent, vos serveurs de base de donnée et vos serveur d'exécution ne sont pas les mêmes; donc le temps de transfert peut avoir une importance non négligeable. De plus, les procédures stockées ont certains avantages non négligeables. Un exemple simple est la sécurité :

PDO::query('call ps_test("'. $_POST['value'] .'")');

CREATE PROCEDURE ps_test(IN login_test VARCHAR(255), OUT exists INT)
BEGIN
SELECT COUNT(1) into exists FROM users where login = login_test;
END;

Je vous met au défi de faire une injection SQL dans ce code qui n'est pas du tout sécurisé… Si vous tentez, vous ferez juste échoué l'appel à la PS, mais vous n'obtiendrez aucune donnée sensible. Attention : je ne dis pas qu'il ne faut pas sécuriser les requêtes à des procédures stockées (un petit «PDO::quote» reste obligatoire pour des raisons évidentes), je dis juste que si la procédure stockée est bien fait il est presque impossible d'injecter du code. Malheureusement il n'est pas possible de mettre des variables dans certaines clauses en MySQL. Par exemple «LIMIT» ne prend pas de variable :

CREATE PROCEDURE ps_test(IN page INT, OUT exists INT)
BEGIN
SELECT COUNT(1) into exists FROM users LIMIT (page*30), ((page+1)*30);
END;

Ce code ne fonctionnera pas car les valeurs du limit doivent être fixes. Il est toujours possible de faire ceci :

CREATE PROCEDURE ps_test(IN page INT, OUT exists INT)
BEGIN
SET @s = CONCAT('SELECT COUNT(1) into exists FROM users LIMIT ', (page*30) , ', ', ((page+1)*30) );

PREPARE stmt1 FROM @s;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
END;

mais on perd alors tout l'intérêt des procédures stockées. En effet, si on doit préparé la requête dans une procédure stockée, celle-ci n'est pas compilée et c'est pire que d'exécuter directement la même requête (donc de l'appeler depuis le code). De plus, on perd beaucoup en sécurité. Bref, je déconseil vivement l'utilisation des statment dans les PS. Attention, rien n'existe par hasard en programmation. Si les statments existent, c'est qu'ils ont une raison d'être car sans eux certaines choses seraient purement impossible. Ça ne veut pas non plus dire qu'il est conseillé de les utiliser à tout bout de champ. Ils existent, c'est bien, mais autant les éviter au possible.

L'utilisation efficace des procédures stockées va être pour des traitement complexes qui, même si ils pouvaient être fait coté code demanderait trop d'aller-retour vers le serveur de base de donnée. On déporte donc ces traitements vers la base de donnée pour les optimiser.

Les procédures stockées sont-elles plus rapide que le traitement PHP ?

Ça dépend, mais si vous utilisez convenablement les deux, il ne devrait pas y avoir tant de différence que ça dans le temps de traitement. Savoir utiliser veut aussi dire savoir quand ne pas utiliser. Il ne faut pas utiliser de PS pour faire ça :

INSERT INTO historique SELECT * FROM ma_table WHERE date_insert > ADDDATE(NOW(), INTERVAL 15 DAY);

Connaissez MySQL aussi bien que PHP (ou tout autre langage) et vous vous éviterez bien des problèmes. MySQL ce n'est pas juste insert/select/update/delete. C'est un langage complet qu'il faut savoir utiliser.

INSERT INTO SELECT

Je vois encore (trop) souvent des transfert de tables qui se font ligne par ligne (pour l'archivage par exemple) en repassant par PHP entre temps… Hors il existe un outil spécialement fait pour : INSERT INTO SELECT :

INSERT INTO historique SELECT * FROM ma_table WHERE date_insert > ADDDATE(NOW(), INTERVAL 15 DAY);

court, pratique, rapide, efficace, pas de problème de coupure de script (c'est du tout ou rien). Bref, cette requête n'a que des avantages.

UPDATE ... JOIN ...

Et oui : le jointures ne sont pas limitées qu'au SELECT. Personne ne vous empèche de faire ceci :

UPDATE
	user	U
		LEFT JOIN
	post	P	ON P.user_id = U.user_id
SET
	U.status = 0,
	P.status = 0
WHERE
	U.user_id = 25;

Bien utilisé, les UPDATE JOIN sont redoutable et permettent d'accélérer énnormément les traitements.

DELETE JOIN

Et oui : le jointures ne sont pas limitées qu'au SELECT (j'ai déjà entendu ça quelque part). Elles sont aussi applicables lors d'un DELETE :

DELETE
	U.*,
	P.*,
	C.*
FROM
	user	U
		LEFT JOIN
	post	P	ON P.user_id = U.user_id
		LEFT JOIN
	comments	C	ON C.user_id = U.user_id
WHERE
	U.user_id = 25;

Le gros avantage, c'est le tout ou rien; avec cette requête, vous vous assurez que les données de chaque table sont bien supprimées et qu'il ne reste aucune donnée lié. Vous pouvez donc facilement appliquer les contraintes structurelles sans vous faire trop de mal.

UPDATE JOIN GROUP BY… ou presque

Un des problème sur lequel je suis fréquemment tombé : il n'est pas possible d'utiliser de fonction de regroupement dans une requête UPDATE ou DELETE. Mais il est possible d'utiliser les jointures et les sous-requêtes ! À utiliser avec précaution, mais utilisable quand même. Par exemple, vous venez de changer vos règles et sur le forum, les utilisateurs qui ont plus de 50 messages obtiennent le niveau «gourou». La requête qui vient à l'esprit est :

UPDATE
	user	U
		LEFT JOIN
	post	P	ON P.user_id = U.user_id
SET
	U.level = "gourou"
WHERE
	COUNT(P.post_id) >= 50;

Cette requête ne peux pas marcher et pour cause : on ne fait pas de count dans un UPDATE. La vraie requête est donc :

UPDATE
	user	U
	LEFT JOIN
	(	SELECT
			user_id,
			count(1) as nb
		FROM
			post
		GROUP BY
			user_id
	) P	ON P.user_id = U.user_id
SET
	U.level = "gourou"
WHERE
	P.nb >= 50;

oui, c'est un peu complexe a première vue, mais en détail, c'est plus simple :

  • on commence par créer une table contenant le nombre de message par utilisateur
  • on fait la jointure avec la table user
  • on fait notre update sur le sous ensemble qui nous convient (les utilisateurs ayant plus de 50 messages)

En fait, on pourrait même pousser encore un tout petit peu en disant qu'il est innutile de sélectionner les utilisateurs qui ont moins de 50 messages dans notre sous-requête :

UPDATE
	user	U
		LEFT JOIN
	(	SELECT
			user_id
		FROM
			post
		GROUP BY
			user_id
		HAVING
			count(1) >= 50
	) P	ON P.user_id = U.user_id
SET
	U.level = "gourou";

La logique de cette requête est un peu plus direct :

  • on créer une table ne contenant que les utilisateurs qui nous interressent (ayant plus de 50 messages)
  • on fait la jointure
  • on met a jour les utilisateurs sélectionnés

Ça permet d'économiser un peu de poids, mais c'est avec ce genre d'optimisation qu'on multiplie par 2 la vitesse d'une application.

TEMPORARY TABLES

Voici typiquement le genre de concept dont tout le monde a entendu parlé mais qui n'est jamais utilisé alors qu'il permettrait d'obtenir des résultats impressionnant dans pas mal de situation. C'est un peu comme les RegExp quand on commence à programmer : on comprend le concept mais on ne veux pas les utiliser soit-même de peur de faire des erreurs. On préfère laisser les autres faire à notre place. Voici un exemple, la problèmatique est exactement la même qu'avant : changer le niveau des utilisateurs ayant plus de 50 messages, mais la réponse est un peu différente :

CREATE TEMPORARY TABLE IF NOT EXISTS user_to_update ( user_id INT );
TRUNCATE TABLE user_to_update;
INSERT INTO user_to_update (
	user_id
)	SELECT
		user_id
	FROM
		post
	GROUP BY
		user_id
	HAVING
		count(1) >= 50;

UPDATE
	user	U
		LEFT JOIN
	user_to_update P	ON P.user_id = U.user_id
SET
	U.level = "gourou";

La logique est stictement identique, mais c'est juste un peu plus souple et ça permet d'effectuer des traitements intermédiaires sur la table temporaire avant de faire la mise à jour.

Quelques conseils en vrac :

- une date, si elle doit être précise à l'heure, doit être un timestamp. Il y a trop de problèmes à cause des changement d'heure entre autre. C'est peut-être moins lisible mais bien plus pratique à manipuler lorsque vous la sortez de la base. J'ai vu des applications ou a chaque récuperation de date je devais faire :

list($date, $time) = explode(' ', $result['date']);
list($d,$m,$Y) = explode('-', $date);
list($H,$mi,$s) = explode(':', $time);

$date = mktime($H, $mi, $s, $m, $d, $Y);

Vous avez une chance sur deux de vous tromper dans l'ordre des paramètres (merci les dates anglaises) et vive la perte de temps. Bien sûr restez logique : une date d'anniversaire ne sera pas manipuler et est bien plus pratique en champ date; mais évitez les DATETIME ou mettez en place une interface qui traduit directement en timestamp.

- n'utilisez pas USING ! oui c'est pratique, jusqu'au jour ou vous êtes rejoint par quelqu'un qui ne l'utilise pas et vous vous retrouvez avec une application qui ne l'utilise pas partout. Pire : vous commencez avec et vous vous rendez compte que vous devez renommer l'un de vos champ pour rester logique. De plus, il s'agit d'une exception à MySQL. A ma connaissance, aucun autre SGBD ne l'utilise.

- adaptez vous au conventions existantes si vous reprenez un projet ! Il n'y a rien de plus moche qu'une base de donnée avec des noms de tables en vrac du genre «user», «Post», «user_2_post», «userToComments». En général, dans les projets que j'ai vu, on écrit tout de la même manière : en minuscule avec des underscores pour séparer les mots («user_2_post»).

- faites attention au noms de vos champs ! «status» (que j'ai utilisé plus haut) est un mot réservé; «user», «text», «date», «length», «exp» et «order» aussi. Et pourtant, je les ai déjà vu utilisé comme nom de champ dans des projets sérieux. Aller comprendre pourquoi votre requête ne passe pas si vous devez échaper le nom d'un de vos champs… bon courage.

- tant que j'y suis : n'échapez pas vos noms de champs dans vos requêtes. Soit vous utilisez une interface qui le fait pour vous, soit vous laissez tombé. Si vous avez fait attention aux noms de vos champs, ça ne sert à rien.

- placez les indexes correctements. Ce point mériterait un article à lui seul (d'ailleur prochainement je le ferais). Évitez les indexes multiples sauf si vous savez que vos requêtes ne se feront jamais sur un seul des deux champs et que vous respectez l'ordre de l'index… Généralement, c'est trop de contraintes pour le faire.

PS : en me relisant, je me rend compte que j'ai fais l'amalgamme entre MySQL et SQL. Évitez de faire ça : MySQL est un SGBD (un programme), SQL est un langage, un standard. C'est pire que de confondre Java et Javascript…

Laissez un commentaire

captcha

En bref...

Dernier article

La dictature sur facebook

Cette semaine, l'actu, c'est Facebook qui trébuche. Le réseau social vient en effet de supprimer le droit de vote de ses membres. On a aussi GoogleNow sur Chrome, des nouvelles pas très réjouissantes de l'UIT, du piratage, l'étoile de la mort de Star Wars et une évasion échoué.

Ressources externes