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:

1. 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:

1. 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 …

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. …

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 …

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 …

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 …

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 …

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 …

Est-il mauvais de stocker des fichiers dans MySQL ? Il n’y a pas de bonne ou mauvaise réponse à cette question. Tout dépend de vos besoins. Personnellement, je préfère stocker les fichiers à l’extérieur de la base de données pour les raisons suivantes:

• Le filesystem va mieux cacher les fichiers
• Le serveur MySQL va avoir plus de facilité à cacher les autres données
• Le débit de donnée du serveur va être moins élevé
• Il est plus facile de réorganiser et maintenir les fichiers
• Le tablespace demeure petit (si vous devez utiliser InnoDB)

Une bonne approche est de stocker un pointeur vers les fichiers sur le filesystem plutôt que le binaire du fichier directement dans la BD. Il y a cependant des avantages à les stocker dans la base de données:

• Toutes les données sont centralisées à une place pour les backups
• C’est plus …

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:

1. SELECT SQL_NO_CACHE *
2. FROM tableA
3. ORDER BY id LIMIT 10;
4. // 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 …