Mar/093
Les tables temporaires
J’oublie parfois à quel point les tables temporaires peuvent être pratiques. On tente de sortir des rapports avec des tables qui ne sont pas prévues pour ça. On écrit des requêtes inimaginables et souvent très lentes. Résultat: on perd notre temps.
La solution: les tables temporaires ! Ce n’est pas très coûteux et drôlement pratique. Au lieu de prendre 25 minutes à écrire “une” requête qui prend un temps énorme à s’exécuter, prenez 10 minutes pour écrire 2-3 requêtes simples qui utilisent des tables temporaires et sortez le même résultat en quelques secondes!
Et si c’est efficace, performant (surtout performant) et récurrent, pourquoi ne pas transformer ces tables temporaires en view ?
Sep/082
Performance avec les procédures stockées
Les procédures stockées ont fait leur apparition avec MySQL 5.0. Une procédure stockée est un ensemble de plusieurs requêtes basées sur le standard sql:2003, regroupées ensemble et stockées dans la base de données. On leur attribut plusieurs avantages, notamment:
- Elles réduisent le trafic réseau: on peut exécuter plusieurs requêtes avec un seul échange entre le client et le serveur.
- Elles offrent un contrôle de sécurité: un user peut exécuter une procédure qui fait des requêtes sur une ou des tables auxquelles il n’a pas accès. Ces requêtes peuvent être en lecture ou en écriture.
- Elles assurent le respect de logiques particulières ou d’intégrité.
J’avais un cas où une application devait faire très régulièrement (jusqu’à plusieurs fois par secondes) les 4 mêmes requêtes: SELECT.. UPDATE.. SELECT.. UPDATE. En déplaçant ces 4 requêtes dans une procédure stockée, j’ai sauvé 3 échanges entre le client et le serveur, quelques boucles dans le code et l’instanciation de quelques objets PHP. Au total, le même processus est devenu 46% plus rapide.
L’utilisation de procédure n’est pas sans risque. En effet, la souplesse de MySQL peut rendre la création et l’exécution d’une procédure valide, alors qu’elle ne le devrait pas. C’est pourquoi il est recommandé de créer la procédure en strict mode. Ainsi, vous serez averti si une de vos opérations repose sur un comportement variable.
Il y a plusieurs niveaux de sécurité reliés aux procédures. Tout d’abord, MySQL 5.0 possède de nouveaux privilèges permettant à un utilisateur de:
- Créer des routines*
- Modifier des routines
- Exécuter des routines
*Les procédures stockées, les fonctions et les “triggers” sont inclus dans la définition d’une routine.
Il est généralement déconseillé de permettre aux utilisateurs de créer ou modifier des routines. Puisqu’elles sont exécutées sur le serveur, il est possible de faire planter tout le système si un utilisateur crée accidentellement une boucle infinie. Ces privilèges devraient être réservés à un DBA ou à un utilisateur expérimenté en qui vous avez confiance.
De plus, l’exécution d’une procédure doit obéir aux privilèges du definer ou de l’invoker. Lors de sa création, il est possible d’indiquer si l’exécution de chaque statement doit respecter les privilèges du créateur ou de l’exécuteur. Le défaut est definer, ce qui peut être potentiellement dangereux si la procédure est créée en étant root. À l’inverse, ça peut être très pratique pour permettre de lire ou écrire de manière restreinte dans une table dans laquelle un utilisateur n’a pas forcement accès.
Les procédures possèdent un tas d’avantages: rapidité, sécurité et consistance. On peut même les utiliser pour réduire des erreurs liées aux transactions (locktimeout, deadlock, etc..) et prévenir les injections SQL. Elles possèdent cependant des désavantages: les requêtes exécutées ne peuvent être cachées. Puisqu’elle sont préalablement parsées et stockées sur le serveur, il n’est pas possible de faire appel au mécanisme de caching lors de leur exécution. C’est un aspect à considérer lors d’optimisation d’un processus.
En conclusion, lorsqu’elles sont correctement utilisées, les procédures stockées peuvent ajouter beaucoup de rapidité à un processus. Plusieurs programmeurs qui n’ont jamais utilisé d’autre SGBD que MySQL ne sont peut-être pas familliers avec les procédures. Je recommande à tous de prendre un instant pour y jeter un coup d’oeil !
May/084
ORDER BY RAND() Optimisation
Il n’existe pas d’autres moyen magique de sélectionner des enregistrements aléatoirement. Il y a seulement ORDER BY RAND(). Cependant, ORDER BY RAND() est un performance killer lorsque la table possède plusieurs milliés d’enregistrements.Prenons le cas où on veut afficher 10 enregistrements aléatoirement et que cette requête va être exécutée excessivement souvent par l’application. La table utilisée pour les tests est une table avec 500000 enregistrements.
Pour commencer, voyons voir comment MySQL réagis avec une requête qui ordonne sur la clef primaire avec une limite de 10 enregistrements:
-
SELECT SQL_NO_CACHE *
-
FROM tableA
-
ORDER BY id LIMIT 10;
-
// 0.00 sec
On s’y attendait, c’est tellement rapide que le client Mysql n’arrive pas à être assez précis pour dire le temps que ca a pris. Maintenant, une requête qui ordonne par RAND() avec une limite de 10 enregistrements également:
-
SELECT SQL_NO_CACHE *
-
FROM tableA
-
ORDER BY RAND() LIMIT 10;
-
// 2.24 sec
La différence est énorme considérant qu’on passe de plus petit que 0.00 à 2.24 secondes. Puisque ORDER BY RAND() est la seule manière de sortir des enregistrements aléatoirement, on peut croire qu’on doit vivre avec. C’est presque vrai..!
Si la logique de l’application le permet, il est possible de restraindre le range des colones aléatoires. Il faut cependant comprendre qu’on limite ainsi le champs de valeur possible. On peut le faire de deux manières:
-
SELECT SQL_NO_CACHE *
-
FROM (SELECT * FROM tableA LIMIT 100000) rs
-
ORDER BY RAND() LIMIT 10;
-
// 0.38 sec
-
SELECT SQL_NO_CACHE *
-
FROM (SELECT * FROM tableA WHERE id > 1 AND id < 250000) rs
-
ORDER BY RAND() LIMIT 10;
-
// 1.21 sec
Ce n’est pas un random absolue, mais je selectionne quand même 10 enregistrements aléoirement dans un range de 0 à 100000 dans le premier cas, et dans un range de 1 à 250000 (50% des enregistrements) dans la seconde requête.
L’avantage de la deuxième syntaxe permet à l’application de générer elle-même un range aléatoire. On peut établir une regle selon laquelle le range ne doit pas exceder 50% des valeurs. On peut donc avoir un code PHP simpliste comme:
-
$min = rand(1,250000);
-
$max = rand(250000,500000);
-
// s'assurer que le range soit suffisament grand et pas trop petit ..
-
$sql = "SELECT SQL_NO_CACHE *
-
FROM ( SELECT * FROM tableA
-
WHERE id > ".$min." AND id < ".$max."
-
) rs
-
ORDER BY RAND() LIMIT 10;
-
Et voila! Chaque fois que l’application exécute cette requête, les valeurs aléatoires auront l’aire réelement aléatoires beaucoup plus rapidement 