May/092
La gestion des IP dans MySQL
La gestion des IP dans MySQL est très simple. Premièrement, il faut savoir que la manière la plus efficace de stocker un IP et de le représenté sous une forme numérique, soit un INT UNSIGNED (donc 4 bytes) plutot qu’un CHAR(15) de 15 bytes.
Il demeure malgré tout possible de manipuler les IP avec leur forme alphanumérique en utilisant 2 function de MySQL: INET_ATON() et INET_NTOA().
mysql> SELECT INET_ATON('192.168.20.76');
+----------------------------+
| INET_ATON('192.168.20.76') |
+----------------------------+
| 3232240716 |
+----------------------------+
mysql> SELECT INET_NTOA(3232240716);
+-----------------------+
| INET_NTOA(3232240716) |
+-----------------------+
| 192.168.20.76 |
+-----------------------+
Si vous avez voulez savoir si un IP fait parti d’un sous reseaux, vous pouvez faire des manipulations bitwise:
SET @myIP := INET_ATON('192.168.20.76');
SET @theNetMask = INET_ATON('255.255.255.255');
-- La premiere addresse du subnet s'écrit (@myIP & @theNetMask) -- et la dernière (@myIP | ~ @theNetMask & 0xffffff); -- Donc, pour savoir si un ip fait parti d'un sous-réseaux:
SELECT INET_ATON('192.168.20.0')
BETWEEN (@myIP & @theNetMask)
AND (@myIP | ~ @theNetMask & 0xffffff);
Si on désire connaitre chaque parti du IP à partir de sa notation numérique, on peut faire:
SET @myIP := INET_ATON('192.168.20.76');
SELECT @myIP, (@myIP >> 24) as firstOctet,
(@myIP>>16) & 255 as secondOctet,
(@myIP>>8) & 255 as thirdOctet,
@myIP & 255 as fourthOctet;
+------------+------------+-------------+------------+-------------+ | @myIP | firstOctet | secondOctet | thirdOctet | fourthOctet | +------------+------------+-------------+------------+-------------+ | 3232240716 | 192 | 168 | 20 | 76 | +------------+------------+-------------+------------+-------------+
Notez que les 2 fonctions sont limité à 32bits, donc il ne fonctionne que pour les IPv4. De plus, l’utilisation des methodes rend impossible l’utilisation des indexes lors d’une recherche. Il est préférable de transformer à l’avance le IP avec la fonction PHP ip2long() par exemple.
Mar/092
Truc rapide pour faire un csv avec MySQL
Je dois régulièrement créer des rapports pour la comptabilité (et d’autres gens moins à l’aise avec des ordinateurs) au boulot. Le moyen facile est de leur envoyer le tout dans un fichier CSV converti en excel par email.
Il y a plusieurs manières de créer un CSV à partir de MySQL. Voici la manière que je qualifie de “standard”:
-
SELECT a,b,a+b INTO OUTFILE '/tmp/result.txt'
-
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
-
LINES TERMINATED BY '\n'
-
FROM test_table;
Une manière un peu plus rapide (trouvé en fouillant sur google):
-
mysql -umyUser-p dbName -B -e "SELECT a,b,a+b FROM test_table;" | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > filename.csv
Et maintenant.. (roulement de tambour).. “MA” manière!
-
CREATE TABLE test_table_csv SELECT a,b,a+b FROM test_table; ALTER test_table_csv ENGINE = csv;
Maintenant, si vous allez voir dans /var/lib/mysql/dbName/ vous y trouverez un fichier csv nommé test_table_csv.CSV. MySQL s’est occupé de la syntaxe “compliqué” pour nous! Le seul inconvénient de cette manière est que vous devez avoir les permissions pour lire /var/lib/mysql.
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).
Dec/081
Event Scheduler
Je ne sais pas si je suis le seul à penser ça, mais je trouve qu’il y a peu de ressources (ou alors, je ne les connais pas) distribuant des exemples de procédures stockées, de fonctions, de triggers ou d’events scheduler. Pour pallier ce manque, je viens de publier une page avec le code d’un Event.
Je compte faire une page (et non un post, pour pas que ça tombe archiver avec le temps) avec des exemples de chaque type de routine, avec leurs particularités. Mon Event est donc le premier exemple d’une petite série à venir. Je n’explique pas en détail chaque ligne; je montre les possibilités et j’invite les gens intéressés à consulter la documentation pour mieux apprendre sur chaques features.
Mon exemple se trouve donc ici : http://www.noidea.ca/mysql-event-scheduler/
Oct/083
MySQL: Le Book
Un gars sur IRC me demandait l’autre jour quel était “Le book” de MySQL. Il n’est évidemment pas possible de devenir expert en la matière avec un seul livre. Après quelques échanges, j’ai vite compris qu’il cherchait à améliorer l’utilisation qu’il en fait en tant que développeur d’application Web.
Je n’ai pas eu l’occasion (comprendre le temps) de lire des milliers de livres. Par contre dans ceux que je connais, je lui ai suggéré deux qui répondront bien à ce qu’il veut: MySQL 5.0 Certification Study Guide et MySQL Stored Procedure Programming.
Le Certification Study Guide se divise en deux parties distinctes: la première est pour les développeurs alors que la deuxième s’adresse aux Administrateurs. Dans la première partie, on y voit tous les concepts essentiels pour les développeurs d’application: syntaxe, sous requête, jointure, datatype et index, routines, etc… Il fait un tour complet sur tous les sujets nécessaires pour passer l’examen de certification. C’est super, mais ça parle très peu d’optimisation et on ne fait que survoler certains sujets; le livre tente plutôt de donner tous les éléments nécessaires pour comprendre la matière et faire les bons choix pour arriver à un résultat optimisé. C’est pourquoi je lui ai également suggéré MySQL Stored Procedure Programming.
Ce deuxième livre focus principalement sur les procédures stockées, les fonctions et les triggers. Ces trois aspects jouent, à mon avis, un rôle important dans la performance d’une application et l’intégrité des données. Il est difficile de ne pas parler de syntaxe, de jointures, de datatypes et d’autres concepts clés lorsqu’on explique ce qu’est une procédure stockée. L’auteur ne néglige aucun détail. Il arrive brillamment à expliquer comment et pourquoi 2 petites requêtes peuvent être mieux qu’une seule grosse avec des jointures complexes dans certains cas, et pourquoi une seule peut meilleur que 2 petites dans d’autres. Il y a dans chaque chapitre un souci de la performance qui n’existe pas dans le Certification Study Guide.
Ces deux oeuvres forment à mon avis une base solide pour comprendre et utiliser intelligemment MySQL en tant que développeur. Je recommande de les lire dans l’ordre dans lequel je les décris. On fait le tour de tous les features importants et on réussit à aller chercher les connaissances nécessaires pour en faire une utilisation solide et efficace avec MySQL Certification Study Guide et on ajoute à ça des notions de performance et d’optimisation avec MySQL Stored Procedure Programming.
Bonne lecture!
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/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