Pour ou contre les procédures et fonctions stockées ?

8 décembre 2010 par stephane

Faut-il oui ou non utiliser des procédures ou fonctions stockées avec MySQL ? Le question a souvent été soulevée et donne lieu à chaque fois à de vifs échanges entre pro et anti. Cet article vous propose une approche différente : se focaliser sur quelques points particuliers (sécurité, performance, débogage) et donner les avantages et inconvénients de l’utilisation des routines stockées. Avec ces éléments en main, vous pourrez décider par vous-même si les routines stockées sont pertinentes pour votre application.

Sécurité

Un des points clés cités par les partisans des routines stockées est la sécurité accrue : en effet, on peut contrôler les droits donnés aux utilisateurs plus finement et de manière plus flexible qu’avec le système de droits classiques via les commandes GRANT. Il suffit pour cela, au moment de la déclaration de la routine, d’ajouter la clause SQL SECURITY DEFINER 'user'@'host', qui permet d’exécuter la routine non pas avec les droits de l’utilisateur invoquant la routine mais avec les droits de l’utilisateur 'user'@'host'.

Par exemple, on peut décider de ne donner aucun droit via GRANT à un utilisateur pour une table t, mais lui laisser la possibilité de consulter cette table avec une routine getTData() et de modifier cette table avec une routine setTData(). On se rapproche ainsi du concept d’encapsulation des données que connaissent bien tous ceux qui ont l’habitude de travailler avec des langages objet.

Une autre possibilité intéressante des routines stockées est d’utiliser des requêtes préparées pour diminuer le risque d’injection SQL. Dans notre débat, cet argument ne constitue pas un avantage décisif dans la mesure où il est bien sûr possible de se servir des requêtes préparées sans routine stockée. A noter aussi que les requêtes préparées n’offrent pas une protection totale contre les injections SQL (c’est un bon sujet pour un autre article).

Il existe cependant quelques cas où une routine stockée peut créer des trous de sécurité. Ce sera le cas par exemple si vous utilisez des fonctions de cryptage dans vos routines : si quelqu’un peut accéder à la base, il pourra à la fois visualiser les données cryptées et la fonction de cryptage, facilitant le processus de décryptage des données. Si le cryptage avait été fait dans l’application, il faudrait réussir à avoir accès à la base de données et au code applicatif pour obtenir la même information.

Performance

C’est peut-être le sujet le plus intéressant sur les routines stockées car vous trouverez des exemples qui vous montreront tous les cas de figure possibles, de ceux qui ont gagné 200% de performance en écrivant une routine à ceux qui ont gagné 200% de performance en remplaçant les routines par du code applicatif. Pour bien comprendre pourquoi un tel fossé peut exister, il faut garder à l’esprit les quelques points suivants qui sont parfois contradictoires :

  • exécuter une routine stockée économise du trafic réseau car toutes les requêtes internes à la routine n’ont pas à être transmises du client au serveur
  • le plan d’exécution de la routine est mis en cache, ce qui est intéressant si on doit appeler plusieurs fois la même procédure (hors routines, le plan d’exécution est calculé à chaque passage d’une requête qui ne se trouve pas dans le cache de requêtes). Cependant, cette mise en cache se fait par connexion, ce qui peut gâcher des ressources et être peu efficace si de nombreuses connexions n’exécutent qu’une seule fois la même routine.
  • le langage est vraiment simpliste par rapport à un vrai langage de programmation rendant difficile par exemple les manipulations avancées sur les chaînes de caractères, beaucoup de constructions se révèlent très lentes
  • exécuter des routines stockées revient à décharger le serveur d’applications (ou le serveur web) et à charger le serveur de base de données, or la scalabilité de la base de données est plus difficile à assurer que celle d’un serveur d’applications

Ces quelques règles très simples vont vous donner une bonne indication sur les cas où les routines seront bénéfiques et sur les cas où il vaut mieux les éviter. Par exemple, si vous devez exécuter des requêtes très simples, le temps passé à la communication entre le client et le serveur devient non négligeable dans le temps total d’exécution de la requête. De même pour le temps passé pour l’optimisation. Avec une routine stockée, la communication client-serveur disparaît et comme le plan d’exécution est mis en cache, l’optimisation n’est faite qu’une seule fois.

On peut vérifier ces hypothèses dans le cas d’une table qu’on souhaite remplir avec un jeu de données de test :

CREATE TABLE t (
id int(11) NOT NULL AUTO_INCREMENT,
col1 varchar(10) DEFAULT NULL,
col2 int(11) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB;

Une procédure stockée permettant de remplir la table pourrait être la suivante :

DELIMITER $$

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `gen_table`(IN total INT)
BEGIN
   DECLARE i INT DEFAULT 0;
   WHILE i < total DO
     INSERT INTO t (col1,col2) VALUES ('aaaa',i*2+1);
     SET i = i+1;
   END WHILE;
END

et un programme PHP réalisant la même opération pourrait être :

<?php

$dbh = mysqli_connect('localhost','root','','test');

for($i=0;$i<$argv[1];$i++){

   $sql = "INSERT INTO t (col1,col2) VALUES ('aaaa',$i*2+1)";

   $dbh->query($sql) or die(mysqli_error($dbh));
}

?>

En cherchant à insérer 100 000 enregistrements dans la table par chacune des méthodes, j’obtiens les temps d’exécution suivants :

  • procédure stockée : 4,48s
  • programme PHP : 9,67s

Je vous laisse imaginer un exemple basé sur des calculs un peu compliqués, et qui tournerait à l’avantage du programme PHP…

Débogage

Voici bien un point pour lequel les routines stockées MySQL sont très faibles : rien, absolument rien n’est prévu pour vous aider à comprendre pourquoi une routine stockée est lente ou pourquoi le comportement observé n’est pas celui que vous attendiez. Pire, si vous activez le general_log pour essayer de voir toutes les requêtes qui sont faites par votre routine, vous ne verrez rien : en effet, seul l’appel à la routine sera stocké.

Conclusion

Alors, pour ou contre les routines stockées ? Comme j’espère vous l’avoir montré dans cet article, le tableau est loin d’être tout blanc ou tout noir. D’autant plus qu’il vous faudra également prendre en compte dans vos sauvegardes le fait d’utiliser des routines stockées et que les interactions entre réplication et routines stockées (si vous avez mis en place la réplication) sont loin d’être simples à gérer.

Mots-clefs : ,

2 commentaires sur “Pour ou contre les procédures et fonctions stockées ?”

  1. Jean Moniatte dit :

    Un autre point important je pense est la centralisation du code (« Business Logic »). J’évite en général les procédures stockées pour cette raison et préfère garder la logique au niveau de l’application. Ca facilite également les mises en production et la gestion des codes source.

  2. NicoD. dit :

    Pour ma part, c’est uniquement en dernier recours que j’utilise les routines stockées. Garder toute la logique de code au niveau de l’application, c’est quand même plus simple pour les programmeurs…

    J’ai eu cependant à m’occuper d’une base de données MySQL qui était « attaquée » par plusieurs applications différentes écrits dans des langages divers et par des développeurs différents. J’ai utilisé les procédures stockées pour les points critiques de l’application et ainsi assurer une cohérence d’ensemble.

    Je préférais en effet laisser certaines tâches (log, flux de données) à MySQL plutôt que laisser chaque développeur les écrire de son côté.

Laisser une réponse