MySQL est le deuxième système de gestion de bases de données relationnelles open source le plus populaire au monde. Il est utilisé dans le monde entier en raison de ses performances rapides, de sa grande fiabilité et de sa facilité d’utilisation. Cet article présente certaines des meilleures pratiques MySQL.

Avec eux, vous pouvez pratiquer SQL et vous fixer des exercices SQL, en apprendre davantage sur la limite SQL et la pratique SQL en ligne, ainsi que sur les problèmes de pratique SQL les plus courants.

Cela vous aidera à comprendre le numéro de ligne SQL, le top SQL, le décalage de limite MySQL et la pratique en ligne SQL.

Si vous recherchez un moyen simple et agréable d’afficher un grand nombre de lignes et de colonnes aux visiteurs de votre site Web WordPress avec des tableaux et des graphiques interactifs colorés, informatifs et réactifs, vous pouvez l’utiliser avec le plugin wpDataTables, vérifier les prix et tous les fonctionnalités qu’il donne ici.

1 Utilisez toujours le bon type de données

L’une des meilleures pratiques MySQL les plus importantes consiste à utiliser des types de données basés sur la nature des données. L’utilisation de types de données non pertinents peut consommer plus d’espace ou entraîner des erreurs.

Par exemple : l’utilisation de varchar (20) au lieu du type de données DATETIME pour stocker les valeurs de date et d’heure entraînera des erreurs dans les calculs liés à la date et à l’heure. De plus, il est possible que des données non valides soient stockées.

2 Utilisez CHAR (1) sur VARCHAR (1)

VARCHAR (1) prend des octets supplémentaires pour stocker des informations, donc si vous enchaînez un seul caractère, il est préférable d’utiliser CHAR (1).

3 Utilisez le type de données CHAR pour stocker uniquement des données de longueur fixe

Par exemple : si la longueur des données est inférieure à 1 000, l’utilisation de char (1 000) au lieu de varchar (1 000) consommera plus d’espace.

4 Évitez d’utiliser des formats de date régionaux

Lorsque vous utilisez le type de données DATETIME ou DATE, utilisez toujours le format de date AAAA-MM-JJ ou le format de date ISO adapté à votre moteur SQL. Les formats régionaux tels que JJ-MM-AAAA ou MM-JJ-AAAA ne seront pas stockés correctement.

5 colonnes de clé d’index

Il est souhaitable que la requête renvoie le résultat rapidement, alors assurez-vous d’indexer les colonnes qui sont utilisées dans les clauses JOIN.

Si vous utilisez l’instruction UPDATE impliquant plusieurs tables, assurez-vous d’indexer toutes les colonnes utilisées pour joindre les tables.

6 N’utilisez pas de fonctions sur des colonnes indexées…

… car alors l’indice perd sa raison d’être.

Par exemple, supposons que vous vouliez obtenir des données où les deux premiers caractères du code client sont AK. Écrire:

SELECT columns FROM table WHERE customer_code like 'AK%'

et n’écris pas

SELECT columns FROM table WHERE left (customer_code,2)='AK’

Pourquoi? Le premier exemple utilisera l’index, ce qui se traduira par un temps de réponse plus rapide.

**7 Utilisez SQL SELECT * uniquement si nécessaire**

Suite à la liste des meilleures pratiques MySQL, n’utilisez pas aveuglément SELECT * dans le code. Si la table comporte plusieurs colonnes, elles seront toutes renvoyées. Cela ralentira le temps de réponse, surtout si vous envoyez le résultat à une application frontale.

Au lieu de cela, tapez explicitement les noms de colonne qui sont réellement nécessaires.

Remarque: n’oubliez pas que toutes les instructions SELECT nécessitent une clause WHERE.

8 Utilisez la clause ORDER BY uniquement si nécessaire

Si vous souhaitez afficher le résultat dans l’application frontale, laissez-le ORDER le jeu de résultats. Si vous faites cela dans SQL, le temps de réponse peut être ralenti dans l’environnement multi-utilisateur.

9 Choisissez un moteur de base de données approprié

Si vous développez une application qui lit les données plus souvent qu’elle n’en écrit (par exemple un moteur de recherche), choisissez le moteur de stockage MyISAM.

Choisir le mauvais moteur de stockage affectera les performances.

10 Utilisez la clause EXISTS chaque fois que nécessaire

Pour vérifier l’existence des données, utilisez la clause EXISTS qui est plus rapide en temps de réponse. Par exemple, utilisez :

If EXISTS(SELECT * from Table WHERE col=’some value’)

Ne pas utiliser :

If (SELECT count(*) from Table WHERE col=’some value’)>0

11 EXPLIQUEZ vos requêtes SELECT

Si vous utilisez le mot-clé EXPLAIN, vous pouvez avoir un aperçu de ce que MySQL fait pour exécuter votre requête. Cela peut vous aider à détecter les problèmes avec votre requête ou les structures de table (par exemple, les goulots d’étranglement).

Une requête EXPLAIN vous montre quels index sont utilisés, comment la table est analysée, triée, etc.

Il suffit d’ajouter le mot-clé EXPLAIN devant une requête SELECT (de préférence complexe avec des jointures). De plus, si vous utilisez phpmyadmin pour cela, vos résultats seront affichés dans un joli tableau.

12 Utilisez LIMIT 1 pour obtenir une ligne unique

Parfois, vous savez à l’avance que vous recherchez une seule ligne lorsque vous interrogez vos tables. Par exemple, vous pouvez récupérer un enregistrement unique ou simplement vérifier l’existence d’un nombre quelconque d’enregistrements qui satisfont votre clause WHERE.

Dans de tels cas, vous voudrez utiliser la fonction de limite de MySQL pour augmenter les performances. Voici une autre des meilleures pratiques MySQL : ajoutez simplement LIMIT 1 à votre requête. De cette façon, le moteur de base de données n’aura pas à parcourir toute la table ou l’index. Il arrêtera l’analyse lorsqu’il ne trouvera qu’un seul enregistrement de ce que vous recherchez.

// do I have anyusersfrom Alabama?

// ce qu’il ne faut PAS faire :
$r = mysql_query("SELECT * FROM user WHERE state = ‘Alabama’");
if (mysql_num_rows($r) > 0) {
// …
}

// beaucoup mieux :
$r = mysql_query("SELECT 1 FROM user WHERE state = ‘Alabama’ LIMIT 1");
si (mysql_num_rows($r) > 0) {
// …
}

13 Indexer et utiliser les mêmes types de colonnes pour les jointures

Un autre conseil essentiel des meilleures pratiques MySQL – si votre application a de nombreuses requêtes JOIN, assurez-vous que les colonnes que vous rejoignez sont indexées sur les deux tables. Cela affecte l’optimisation interne de l’opération de jointure par MySQL.

De plus, les colonnes jointes doivent être du même type. Par exemple, si vous joignez une colonne DECIMAL à une colonne INT d’une autre table, MySQL ne pourra utiliser aucun des index. Même les encodages de caractères doivent être du même type pour les colonnes de type chaîne.

// looking for companies in my state $r = mysql_query("SELECT company_name FROM users LEFT JOIN companies ON (users.state = companies.state) WHERE users.id = $user_id");

// les deux colonnes d’état doivent être indexées
// et elles doivent toutes deux avoir le même type et le même encodage de caractères
// ou MySQL peut effectuer des analyses complètes de la table

14 Masquer MySQL sur Internet

Les administrateurs de base de données expérimentés et le personnel de sécurité le savent: n’hébergez jamais la base de données sous la racine du serveur Web.

Pour les applications Web, MySQL doit être caché derrière un pare-feu. La communication doit être activée uniquement entre les serveurs d’applications et vos serveurs Web.

Une autre option consiste à utiliser MySQL skip-networking. Lorsqu’il est activé, MySQL n’écoute que les connexions socket locales et ignore tous les ports TCP.

15 Utilisez les plus petits types de données possibles

Laissez-moi vous raconter une histoire. Quand j’étais à l’université, la philosophie était que «la mémoire est rare ». C’était l’époque des disques durs de 256 Mo. De nos jours, personne ne semble se soucier d’un iota de la mémoire ou de l’espace disque. La nouvelle philosophie est que «la mémoire est bon marché ». C’est peut-être vrai en dollars, mais la lecture de grands types de données prend toujours plus de temps que la lecture de plus petits. Les types de données volumineux nécessitent davantage de secteurs de disque pour être lus en mémoire.

La morale est d’ignorer la tentation de sauter immédiatement au plus grand type de données lorsque vous concevez vos tables. Pensez à utiliser un int au lieu d’un bigint.

Évitez également les champs de texte largechar (255) lorsqu’un varchar ou un caractère plus petit suffit.

Si vous utilisez le bon type de données, plus d’enregistrements tiendront dans la mémoire ou le bloc de clés d’index. Cela conduit à moins de lectures et à des performances plus rapides.

16 Tirez parti de la mise en cache des requêtes

La mise en cache des requêtes est l’une des méthodes les plus efficaces pour améliorer les performances. La plupart des serveurs MySQL l’ont activé par défaut.

Le cache de requête stocke le texte d’une instruction SELECT avec le jeu de résultats correspondant. Si le serveur reçoit ultérieurement une instruction identique, il récupérera les résultats du cache de requêtes plutôt que d’analyser et d’exécuter à nouveau l’instruction. Le cache de requêtes est partagé entre les sessions, de sorte qu’un ensemble de résultats généré par un client peut être envoyé en réponse à la même requête émise par un autre client.

Cependant, aussi génial soit-il, la mise en cache des requêtes a ses limites. Prenez la déclaration suivante :

Le problème ici est que les requêtes contiennent certaines fonctions non déterministes, comme NOW() et RAND(). MySQL ne peut pas calculer de telles fonctions à l’avance, elles finissent donc par ne pas être mises en cache.

Heureusement, il existe une solution simple à cela: vous pouvez stocker les résultats de la fonction dans une variable.

17 Ne modifiez pas les fichiers de vidage

Les fichiers de vidage sont très trompeurs et peuvent entraîner une corruption. Pourquoi? Si vous avez déjà vu les fichiers de vidage créés par mysqldump, vous conviendrez qu’ils ressemblent à des fichiers texte normaux et inoffensifs. C’est pourquoi la plupart des gens les éditent dans un éditeur de texte standard, ce qui provoque l’apparition de corruptions.

Si vous avez déjà essayé de modifier des fichiers de vidage, vous avez rapidement appris qu’ils sont tout sauf un fichier texte. Ainsi, le seul moyen garanti d’éviter les problèmes est de laisser les fichiers de vidage seuls.

18 Utilisez le paramètre de taille de bloc MyISAM

Le paramétrage des tailles de bloc dans les index des tables MyISAM est l’une des meilleures pratiques de MySQL. Il se trouve dans les fichiers .MYI du tampon de clé, ainsi que sur le disque. Le paramètre ressemble à ceci: myisam_block_size.

Il a une valeur par défaut de 1k. Il est assez petit pour être optimal sur un système moderne. Des blocs de plus grande taille sont utilisés par la plupart des systèmes de fichiers. Et nous savons que l’écriture d’un seul bloc d’index nécessite une lecture puis une écriture. Le système d’exploitation n’aura jamais à écrire sur le disque sous-jacent tant que la taille de bloc est égale ou supérieure à la taille de bloc du système de fichiers.

19 Activer delay_key_write

Delay_key_write est désactivé par défaut. Il y a une raison pour ça. Si vous rencontrez un plantage au milieu du projet, votre base de données peut être corrompue.

Alors, pourquoi voudriez-vous l’activer ? La raison est simple. Parce que le delay_key_write garantit que la base de données ne videra pas le fichier de clé MyISAM après chaque écriture. Par conséquent, si vous faites une autre écriture dans un proche avenir, vous gagnerez beaucoup de temps.

Voici une autre astuce intéressante que nous avons choisie parmi les meilleures pratiques de MySQL : l’activation de delay_key_write est différente pour chaque version. Pour voir comment l’activer dans une version spécifique, consultez le manuel officiel du site MySQL.

20 Utilisez Stack Trace pour isoler les bogues

Suite à ces meilleures pratiques MySQL, cette astuce est empruntée à Sky SQL car elle est tout simplement trop simple et trop pratique pour être laissée de côté.

MySQL stack_trace peut être utilisé pour isoler divers bogues. Au lieu de creuser dans toutes les utilisations possibles, le programmeur peut vouloir prendre note de la facilité avec laquelle un pointeur nul peut ruiner votre code.

Avec cette astuce Sky SQL, le repérage, le suivi et la réparation deviennent beaucoup plus faciles.

21 MySQL change le mot de passe ROOT

Changer le mot de passe ROOT peut sembler basique, mais savoir comment le faire est tout aussi important pour votre système d’exploitation domestique que sur les serveurs MySQL.

Il arrive parfois que des débutants trop zélés et des utilisateurs expérimentés distraits ne comprennent pas pourquoi ils ne peuvent pas modifier certains paramètres et renvoient des erreurs. Cela peut être aussi simple que de vous donner un accès ROOT. Un utilisateur ne devrait même pas avoir à le rechercher sur Google.

Voici comment configurer le mot de passe ROOT et comment modifier le mot de passe ROOT d’un utilisateur :

//Straightforward MySQL 101 $mysqladmin -u rootpassword [Type in selectedpassword] //Changingusers ROOT password $mysqladmin -u root -p [type oldpassword] newpass [hit enter and type new password. Pressenter] //Use mysqlsqlcommand $mysql -u root -p //prompt "mysql>" pops up. Enter: $use MySQL; //Enter usernameyouwant to change the password for $update user set password=PASSWORD (Type new PasswordHere) where User = 'username'; //Don'tforget the previoussemicolon, nowreload the settings for the user'sprivileges $flush privileges; $quit

22 Corrigez vos fichiers de configuration

MySQL Tuner est un script Perl qui peut en quelque sorte optimiser vos performances en suggérant des modifications à vos fichiers de configuration.

Si certains trucs et astuces de MySQL sont incroyablement pratiques, des outils comme MySQL Tuner sont une aubaine qui mérite d’être dans une catégorie à part.

C’est quelque chose qui peut être utilisé aussi bien par les novices que par les pros. MySQL Tuner n’est pas une astuce spécifique pour MySQL, il existe donc une variété de réglages et de mods qui peuvent être appliqués. Plus vous l’utilisez, plus vous pouvez apprendre à appliquer des modifications pour votre propre usage.

Cela peut sembler intimidant au début, mais c’est pourquoi vous pouvez trouver le manuel -man db ainsi que toutes les notes, lectures nécessaires et paramètres sur la page d’accueil officielle du projet. Avec tout cela, MySQL Tuner devrait rapidement devenir votre outil incontournable pour accélérer MySQL et tester vos fichiers de configuration.

Réflexions finales sur les meilleures pratiques MySQL

Dans le monde de l’informatique, MySQL est sans aucun doute l’un des programmes les plus importants et les plus influents jamais apparus. Il est si impliqué et polyvalent que juste au moment où il semble que toutes les astuces et tous les raccourcis possibles ont été découverts, quelqu’un d’autre intervient avec une nouvelle façon d’utiliser un nouvel argument ou un nouveau paramètre.

Nous avons décrit uniquement les meilleures pratiques MySQL que tout le monde devrait connaître. Mais, plus vous utilisez MySQL, plus vous les utiliserez. Vous pouvez pratiquer SQL en ligne et vous définir des tests SQL. Vous développerez ensuite vos propres astuces et trouverez vos propres astuces sur lesquelles vous pouvez compter, telles que la limite SQL, le nombre de lignes SQL et comprendrez pourquoi vous devriez sélectionner MySQL en premier lieu.

Si vous avez aimé lire cet article sur les bonnes pratiques de MySQL, vous devriez également lire ceci :

Source d’enregistrement: wpdatatables.com