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 ?
Feb/091
Le client mysql: trucs et astuces
Pour plusieurs apprentis en développement Web, MySQL se résume qu’à phpMyAdmin. Plusieurs personnes ne savent pas qu’il existe un client en command line qui s’appelle tout simplement mysql. Pourtant, c’est un client hyper performant et très polyvalent que je recommande à tous.
Sa syntaxe est très simple. Pour se connecter:
-
prompt> mysql -h youdomaine.com -u login -p
On indique ensuite la base de données qu’on souhaite utiliser et hop, on est prêt à exécuter des requêtes:
-
mysql> USE myDatabaseName;
Plutôt simple n’est-ce pas ? Maintenant que vous connaissez le strict minimum pour l’utiliser, voici quelques trucs et astuces que même ceux qui l’utilisent ne savent pas toujours.
Les raccourcis les plus pratiques
\c – Annule une requête
Lorsqu’on fait une erreur dans une requête, on peut l’annuler avec \c à tout moment. Ceci évite d’envoyer une requête erronée ou malformée au serveur.
\G – Affiche les résultats verticalement
Lorsque le résultat possède plusieurs champs, l’affichage conventionnel n’arrive pas a tout mettre sur la même ligne et le résultat devient vient illisible. Avec l’affichage vertical, nous n’avons pas ce problème.
\R – Change le prompt
Très utile pour un DBA qui administre plusieurs serveurs à la fois. Pour éviter toute confusion, suffit de renommer le prompt par défaut par l’identifiant du serveur.
\! – Exécute une commande système
Il est possible d’exécuter des commandes système comme si on était dans une console. Par exemple: \! ls ou \! mv fichier.sql fichierrenomme.sql
L’aide “en ligne”
Peu de monde connaît ce truc, mais si le serveur possède sa table “help” remplie (c’est le cas par défaut), la syntaxe de la majorité des commandes SQL est accessible à l’intérieur de mysql ! Suffit de taper: help keyword; où keyword est le mot-clé recherché:
-
HELP SELECT;
-
HELP CREATE TABLE;
J’ai découvert ce truc il n’y a pas très longtemps et j’avoue que c’est drôlement pratique !
Les types d’affichage
Le client mysql permet de retourner le résultat d’une requête de plusieurs manières différentes, un feature très intéressant pour toute sorte de situations. À la connexion, on indique donc le format d’affichage souhaité. Il n’y en a quatre:
– table ou -t
Est l’affichage par défaut. Les champs sont délimités par des | et forment une table.
– batch ou -B
Produit les résultats sous forme de table, comme l’affichage par défaut, mais n’est pas délimité par des | mais plutôt par des “tabs” .
–html ou -H
Comme le nom l’indique, les résultats sont affichés sous forme de table, mais en format HTML. Les valeurs sont donc entourées des balises HTML <table><tr><td> …
–xml ou -X
Un peu comme le format HTML, le format XML retourne les valeurs à l’intérieur de balise XML comme ceci:
<row>
<field name=”id”>123456</field>
<field name=”field1″>value1</field>
<field name=”field2″>value2</field>
<field name=”field3″>value3</field>
</row>
-
mysql -uroot -p -X
Des tonnes de requêtes
J’ai souvent vu des gens copier-coller plusieurs centaines, parfois milliers, de requêtes dans phpMyAdmin. J’ai même déjà vu du monde vouloir uploader un fichier.sql d’une 30aine de Mo via phpMyAdmin. C’est très inefficace. Le client mysql offre 2 moyens plus efficaces de pouvoir exécuter un fichier.sql:
Lors de la connexion, comme ceci:
-
mysql -u login -p databaseName < fichier.sql
ou directement à l’intérieur de mysql
-
mysql> SOURCE /tmp/fichier.sql;
Ce sont 2 moyens régulièrement utilisé pour restaurer un dump créé avec mysqldump.
À la différence de phpMyAdmin, si client mysql se trouve sur le même serveur que le mysqld, il peut se connecter avec les unix socket (ou named pipe) qui est un protocole reconnu pour être plus rapide que TCP/IP. Si on veut utiliser phpMyAdmin, les requêtes sont d’abord uploadées sur le serveur web avant d’être transférées sur le server MySQL et la connexion est la plus part du temps effectué via TCP/IP. De plus, le client mysql ne nous limite pas par les différents timeouts de apache et la grosseur limite de fichier uploadé (qui est de 8Mo par défaut, si je ne me trompe pas).
Jan/091
General Log sur demande
Dans les nouveautés de MySQL 5.1, il y a l’activation / désactivation des logs sur demande. J’adore ce feature. Pour moi, c’est un outil de debug. Je m’en sers pour trouver des problèmes de performance ou de transactions qui sont souvent complexes à identifier dans une application avec une grosse architecture.
Le principe est simple: dans un environnement isolé, c’est-à-dire un environnement où vous savez que vous êtes le seul à travailler, accédez à l’interface problématique de votre application. Activez les logs avec la commande:
SET GLOBAL general_log = 1;
Lancez ensuite l’opération problématique. Lorsque terminé, désactivez de suite les logs:
SET GLOBAL general_log = 0;
Le log est maintenant rempli de toutes les requêtes effectuées durant l’opération. C’est là que commence l’analyse. Regarder les requêtes, la quantité, l’ordre dans lequel elles sont faites, la gestion des transactions, etc.. Il n’est habituellement pas souhaitable de voir:
- Plusieurs requêtes identiques – vous devriez songer à un système de cache
- Plusieurs requêtes consécutives pour aller chercher une étendue d’enregistrement de la même table (ex: .. WHERE id = 1; .. WHERE id =2; .. WHERE id = 3; .. WHERE id = 4;)
- Plusieurs connexions différentes (rappelez-vous, il s’agit d’une opération déclenchée par un seul utilisateur)
- Plusieurs transactions simultanées.
Les logs peuvent vous démontrer plusieurs problèmes différents. J’ai découvert qu’un processus peu performant d’une application envoyait jusqu’à 5Mo de requêtes SQL. Si on estime qu’une requête SQL fait environ 100 octets, imaginez le nombre de requêtes effectuées pour se rendre à 5Mo.
Il est important de désactiver de suite les logs, car ceux-ci rendent le serveur lent et utilise abusivement de l’espace disque. De plus, l’analyse du problème peut être faussée si quelqu’un accède à votre environnement et effectue des opérations. Et surtout, n’activez JAMAIS les logs sur un serveur en production. J’ai déjà vu un log augmenter de plusieurs centaines de Mo à la minute.
Nov/082
Tout ce qu’il faut savoir sur les routines
La syntaxe SQL pour créer une procédure stockée ou une fonction est simple. Cependant, il y a plusieurs choses à savoir pour créer et utiliser une routine. Tout d’abord, il faut connaitre les trois nouveaux privilèges liés aux routines:
- Create routine pour créer des routines (procédure stockée ou fonction)
- Alter routine pour modifier ou supprimer une routine. Ce privilège est automatiquement donné au créateur de la routine.
- Execute pour les exécuter. Ce privilège est également donné automatiquement au créateur.
Si l’option automatic_sp_privilèges est à 0, Alter routine et Execute ne seront pas automatiquement attribués au créateur. Il faut aussi savoir que ces 3 privilèges ne sont pas les seuls éléments de sécurité. Avec les procédures stockées, la caractéristique SQL SECURITY peut être utilisée pour définir si c’est les privilèges du créateur ou de celui qui l’exécute qui doivent être utilisés. La valeur par défaut est definer.
Le privilège SUPER doit être donné aux utilisateurs qui crée et exécute des routines sur des serveurs ayant le binary log activé, en plus des privilèges Create routine et alter routine. Vous conviendrez que donner un tel privilège n’est pas toujours souhaitable, donc MySQL s’est doté de l’option log_bin_trust_routine_creators (désactivée par défaut) pour pallier ce “problème”.
On pourrait se demander pourquoi cette option est désactivée par défaut. La réponse est simple: par souci de sécurité. Lorsque le binlog est activé, la création de routine doit absolument avoir DETERMINISTIC, NO SQL ou READ SQL DATA dans sa déclaration.
- Les caractéristiques DETERMINISTIC et NOT DETERMINISTIC indiquent si la routine produit toujours le même résultat pour la même entrée. Pour la déclarer DETERMINISTIC, vous devez le spécifier explicitement. Le défaut est NOT DETERMINISTIC ce qui signifie que la routine peut retourner des résultats différents pour la même entrée.
- Les caractéristiques CONTAINS SQL, NO SQL, READS SQL DATA et MODIFIES SQL DATA indiquent si la routine sélectionne ou modifie des données. Le défaut est CONTAINS SQL si aucune caractéristique n’est spécifiée.
L’option log_bin_trust_routine_creators permet d’outre passer cette sécurité. Si elle est activée, le créateur d’une fonction peut déclarer qu’elle est not deterministic. C’est pourquoi il est conseillé d’activer cette option seulement si vous avez une confiance entière aux usagers qui peuvent créer des routines. Une telle routine peut causer d’importants problèmes:
- Rendre les Slaves différents du master, puisqu’il n’y a aucune garantie que le résultat sera le même
- Les données restaurées peuvent être différentes des données originales
Comme vous devez vous douter, les routines sont stockées directement dans le serveur. Pour se faire, MySQL utilise 3 nouvelles tables: information_schema.routine, mysql.proc et mysql.proc_priv. Si vous upgradez un serveur à partir d’une version inférieure à MySQL 5, il faut s’assurer que ces tables sont présentes. Vous pouvez utiliser le script mysql_upgrade pour les ajouter.
Pour conclure, vous devez aussi savoir que le code qui constitue une routine est stocké différemment du code utilisé pour la créer. Les commentaires sont supprimés, c’est pourquoi il est conseillé de toujours conserver une copie du code de votre routine dans un fichier texte à l’extérieur du serveur.
Bien sur, il y a beaucoup d’autres choses à savoir, mais je crois que ça fait le tour de ce qui est essentiel!
Jul/080
Optimisez vos requêtes en fonction des index
Aujourd’hui, je vais expliquer comment tirer avantage des index avec certains types de requête.
Nous avons une table client avec un monstrueux index nommé “nom” que voici: nom, prenom, entreprise, telephone. Nous avons un champ de recherche dans une page Web qui nous permet de trouver un client à partir de son prénom ou son nom.
On veut trouver une personne qui a le nom “Phil”:
SELECT SQL_NO_CACHE * FROM client
WHERE groupe = 1
AND ( nom LIKE 'phil%' OR prenom LIKE 'phil%')
---
(490 total, Query took 0.4523 sec)
(490 total, Query took 0.4511 sec)
(490 total, Query took 0.4879 sec)
(490 total, Query took 0.4455 sec)
Dans cette requête, aucun index n’est utilisé. À cause de la condition OR, l’optimiseur estime qu’il est plus efficace de faire un table scan que d’utiliser l’index nom, même si celui-ci possède les champs nécessaires et dans le bon ordre. EXPLAIN le confirme.
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: client
type: ALL
possible_keys: nom
key: NULL
key_len: NULL
ref: NULL
rows: 55449
Extra: Using where
J’ajoute un index sur le champ prenom en espérant que l’optimiseur va utiliser soit l’index sur nom ou prenom pour satisfaire au moins 1 des 2 conditions dans le WHERE.
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: client
type: ALL
possible_keys: nom, prenom
key: NULL
key_len: NULL
ref: NULL
rows: 55449
Extra: Using where
Même chose. On veut sélectionner les clients qui ont “phil” comme nom ou prénom et l’optimiseur refuse d’utiliser les index sur ces champs. Puisqu’on veut conserver la logique, on pourrait croire qu’il n’y a rien à faire avec cette requête. Faux ! En utilisant un UNION, il devient possible d’utiliser les 2 index en même temps.
(SELECT SQL_NO_CACHE *
FROM client
WHERE groupe = 1
AND nom LIKE 'phil%')
UNION
(SELECT SQL_NO_CACHE *
FROM client
WHERE groupe = 1
AND prenom LIKE 'phil%')
---
(490 total, Query took 0.0627 sec)
(490 total, Query took 0.0586 sec)
(490 total, Query took 0.0609 sec)
(490 total, Query took 0.0581 sec)
On crée 2 requêtes avec chacune une des 2 conditions de la première et l’UNION nous permet d’unir les 2 résultats. De cette manière, on évite la condition OR et les 2 requêtes peuvent utiliser l’index qui fait référence à leur champ respectif. On obtient un gain de performance d’environs 86% dans mon cas.
La première requête utilise l’index cl_nom, la 2ieme cl_prenom. EXPLAIN:
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: client
type: range
possible_keys: nom
key: nom
key_len: 32
ref: NULL
rows: 53
Extra: Using where
*************************** 2. row ***************************
id: 2
select_type: UNION
table: client
type: range
possible_keys: prenom
key: prenom
key_len: 32
ref: NULL
rows: 437
Extra: Using where
*************************** 3. row ***************************
id: NULL
select_type: UNION RESULT
table:
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
Extra:
L’UNION nous permet d’utiliser nos index en formulant la requête différemment, sans pour autant modifier la logique de cette dernière.
Jul/082
Trouver les indexes inutiles
Arjen Lentz a écrit un article très intéressant pour trouver les indexes inutilisés. En gros, il explique la règle du 30% pour les indexes. Pour ceux qui ne la connaissent pas, les indexes ne sont pas utilisés si environs 30% des valeurs indexées sont les mêmes. Arjen propose donc une requête qui permet de trouver quel indexes sont inférieurs à 30% grâce à la cardinalité.
Mais attention! Sa technique est un peu controversée. Premièrement, il faut savoir que les 30% sont une approximation. Les indexes peuvent être utilisés si 25% ou 40% des valeurs sont pareil.
J’ai fait mes propres tests sur la base de données avec laquelle je travail au boulot et les résultats m’ont assez étonné. EXPLAIN indiquait qu’un index avec une cardinalité de 4 (la table possédait 3422 000 rows) était utilisé. C’est beaucoup plus que 30%. J’ai ensuite ajouté un indexe sur un champ dont je savais les valeurs identiques à 99%. L’index était également utilisé !
La règle du 30% ne faisait aucun sens selon mes tests jusqu’à ce que je réalise que toutes les tables avec lesquelles je testais étaient InnoDB. Les résultats étaient tout à fait différents après avoir modifié les tables pour MyISAM. La règle est bonne.
La raison pour laquelle InnoDB utilise l’index même si 99% des valeurs sont identiques demeure relativement étrange. Comme vous devez le savoir, InnoDB possède des covering index et plein d’autre statistique se qui le rend apte à décider si l’utilisation de l’index est nécessaire ou pas. De plus, la cardinalité avec InnoDB est une estimation; on ne peut donc pas se fier à la valeur retournée comme valeur sûre. ANALYZE TABLE met à jour la cardinalité des indexes, mais ça demeure une estimation et les résultats peuvent varier si on roule le statement plusieurs fois.
ANALYZE TABLE determines index cardinality (as displayed in the Cardinality column of SHOW INDEX output) by doing ten random dives to each of the index trees and updating index cardinality estimates accordingly. Note that because these are only estimates, repeated runs of ANALYZE TABLE may produce different numbers. This makes ANALYZE TABLE fast on InnoDB tables but not 100% accurate as it doesn’t take all rows into account.
En résumé, la règle du 30% est vraie et relativement fiable, mais elle ne s’applique pas à InnoDB. De plus, même si vous trouvez des indexes avec une petite cardinalité sur une table MyISAM, ca ne veut pas nécessairement dire qu’il est inutilisé. Utilisez votre jugement à savoir s’il est pertinent de l’enlever.
Jul/080
je deteste show warnings
Une des choses à laquelle il faut faire spécialement attention sont les warnings. MySQL est une base de données très permissive et beaucoup d’opérations réussies le sont grâce à la souplesse qu’il permet. J’ai vu des bases de données rouler pendant plusieurs jours, voir années, avec des erreurs sans que personne ne s’en rende compte.
Pourquoi je déteste “show warnings”? Parce que c’est un feature qui manque d’utilité. La documentation qui y fait référence est aussi défaillante. Elle n’indique même pas que le statement est par connexion. C’est un gros manque à mon avis. Si une base de données est exclusivement utilisée via une application, les requêtes sont toujours les mêmes. Pour un DBA en charge de s’assurer que tout fonctionne bien, il faudrait être capable d’obtenir ces warnings.
Je travaillais récemment sur un serveur, connecté avec le client command line. J’effectuais ce que j’avais à faire et j’obtiens un
Query OK, 3 rows affected, 4 warnings (0.15 sec)
Bon ok, j’ai merdé un truc. Je vais voir quoi exactement. J’ai la chance d’être connecté et de savoir que j’ai un warnings.
mysql> show warning;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'warning' at line 1
Grrr, j’oublis que c’est warnings avec un S. Je recommence
mysql> show warnings;
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| Error | 1064 | You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'warning' at line 1 |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
Pffff. J’ai perdu mes “vrais warnings” car j’ai tappé trop vite lorsque j’ai tappé show warnings. C’est complètement ridicule, mais voulu si on se fie à la doc
Cette commande affiche les erreurs, alertes et notes qui ont été générées par la dernière commande
La dernière commande… Faut croire qu’il faut vivre avec… Soyez vigilant !
Jul/081
Énumérez vos champs !
On a parfois la mauvaise habitude de faire des SELECT * FROM table; Mon astuce de la semaine va tenter d’expliquer pourquoi il faudrait toujours énumérer les champs qu’on veut sélectionner, même s’il s’agit de la table au complet.
Il y a 3 raisons majeures pour lesquelles il ne faudrait JAMAIS faire de SELECT * FROM dans les requêtes d’une application:
- La description de la table par le biais de la requête est perdue
- Les changements de champs
- La grosseur des champs
Description de la table
Les Q.A vont être d’accord avec moi là-dessus, il n’y a rien de mieux qu’un code avec des noms de variables et de fonctions bien choisis. Et lorsque c’est pas suffisamment explicite, les programmeurs doivent ajouter des commentaires pour aider à la compréhension du code. Les requêtes SQL ne doivent pas faire exceptions à cette règle. Supposons que vous devez fixer un bug et que vous tombez sur cette requête dans un module que vous ne connaissez pas:
SELECT * FROM users WHERE id = 1;
Simple. Tout le monde arrive à comprendre qu’est-ce que la requête fait. Mais qu’est-ce qu’on reçoit en réalité ? 1, 2, 5, 100 champs ? Quel sont leur nom et dans quel ordre on les reçoit ? On ne peut pas le savoir à moins d’avoir le schéma de la table ou de se connecter directement à la base de données pour l’obtenir.
Mais encore, on ne sait pas non plus quels champs le programmeur initial avait l’intention d’utiliser dans le reste du code. Pas moyen de figurer s’il aurait pu faire ça autrement à l’aide d’un autre champ. Peut-être qu’il a fait une faute de frappe dans le nom d’un champ.
SELECT id_officiel , nom, prenom, email, categorie FROM users WHERE id =1;
Cette requête est un peu plus longue, ok, mais elle beaucoup plus explicite. On sait avec certitude ce qu’on demande et ce qu’on reçoit. Le nom des champs forme une documentation en soit. Je peux même supposer que le champs « id » n’est pas le ID qui sera affiché à l’écran puisque la table possède un «id_officiel». Un concept impossible à apercevoir avec la première requête.
Changements de champs
Les tables changent, pour le mieux la plus part du temps. Supposons qu’on décide de renommer le champs email pour courriel, car on désire garder des noms français.
SELECT * FROM users WHERE id = 1;
Super, la requête fonctionne toujours. Mais un programmeur qui n’est pas au courant de notre modif pourrait chercher longtemps avant de trouver pourquoi les emails ne s’affichent plus. Le code PHP risque de simplement faire un notice qui passera inaperçue.
SELECT id_officiel , nom, prenom, email, categorie FROM users WHERE id =1;
#1054 - Unknown column 'email' in 'field list'
Puisqu’on spécifie exactement ce que nous désirons, MySQL est capable de nous indiquer si ce que nous lui demandons correspond toujours à son schéma de table. Avec une erreur comme celle-là, le bug va prendre 10 secondes à être fixé.
La grosseur des champs
Pourquoi sélectionner tous les champs quand nous avons besoin de seulement 3 ? Supposons qu’au fil du temps on décide d’ajouter un blob nommé “photo”, et que la grosseur moyenne de ces photos sera de 120k. Une vielle interface qui liste les users fait un SELECT * FROM users LIMIT 0,100;
Supposons aussi que les 100 premiers users ai ajouté leur photo à leur profil (rappelez-vous, c’est un nouveau feature). La vieille interface fait toujours la job, car on ne souhaite pas afficher les photos ici, mais elle devenue soudainement très lente. Si on fait le calcule, on constate que 11.72 Mo supplémentaires de données sont demandées et retournées sans qu’on en ai besoin. On pourrait croire que c’est pas si grave, le serveur Web est sur le même réseau local que le serveur MySQL. Mais lorsqu’on pense que le bottleneck d’une base de données est souvent le Disque IO, qu’on lui demande 11.72 Mo de données inutiles, qu’on met ces données en RAM (la RAM est souvent une ressource assez limitée), on se rend compte que c’est pas une si bonne idée. Supposons encore que durant une période de rush, 25 personnes demandent d’afficher la page simultanément. 25 X 11.72 = 293 Mo de données à transférer pour aucune raison. Même sur un réseau local ça peut être long.
En conclusion, prenez les 20 secondes que ça prend de plus à énumérer les champs que vous voulez sélectionner!
Traduit et inspiré par piazza_milshake