Top 20 des recommandations pour MySQL

La base de données MySQL est devenue la base de données relationnelle open source la plus populaire au monde en raison de ses performances élevées, de sa cohérence, de sa haute fiabilité et de sa facilité d'utilisation. Cependant, ces avantages qu'il nous offre sont souvent affectés par la façon dont nous y travaillons.

Dans ce tutoriel, vous apprendrez une série de conseils qui seront très utiles et nous permettront de tirer le meilleur parti à la fois du point de vue du programmeur et de l'administrateur de la base de données.

Quel outil allons-nous utiliser ?Console de commande MySQL '] MySQL a un programme, appelé avec le même nom que la base de données (mysql) qui est utilisé pour gérer la base de données par ligne de commande.

les fenêtresC'est dans un répertoire comme :

C:\Program Files\MySQL\MySQL Server 5.6\bin

Le répertoire peut varier, par exemple, il peut être situé à la racine du disque C:, ou à tout autre endroit où l'on peut avoir installé MySQL. Pour accéder à la console MySQL sous Windows, nous devrons nous trouver dans ce répertoire.

LinuxVoir le lien suivant :

Accéder à MySQL depuis Linux

MacOrdinateurs avec système Mac OS X ils ont le terminal de ligne de commande intégré parmi les applications disponibles. Pour accéder à la même commande est utilisée que sous Linux.

phpMyAdminC'est un outil logiciel gratuit écrit en PHP, qui est utilisé pour administrer MySQL via Internet. Si vous avez installé un environnement de développement local tel que XAMPP ou WAMP, cet outil sera déjà installé. Si vous avez un plan d'hébergement avec un panneau d'administration, la plupart d'entre eux proposent cet outil en tant qu'administrateur de base de données MySQL.

[couleur = rgb (169,169,169)] phpMyAdmin de XAMPP [/couleur]

AGRANDIR

[color = # a9a9a9] phpMyAdmin de CPanel [/ color]

Ayant ces 2 outils à portée de main, nous pouvons commencer à tester toutes ces bonnes pratiques que nous montrons ci-dessous.

Ceux-ci sont Les 20 meilleures pratiques MySQL :

1. Convention de nommage

Règles généralesGardez les règles suivantes à l'esprit pour éviter les problèmes.

• Utilisez les minuscules car cela vous aide à taper rapidement, cela évitera les erreurs en cas de fonctions sensibles aux minuscules et majuscules, etc.

• N'utilisez pas d'espace, utilisez plutôt le trait de soulignement (_).

• N'utilisez pas de chiffres dans les noms, uniquement des caractères alphabétiques anglais.

• Utilisez des noms compréhensibles valides.

• Les noms doivent être explicites.

• Les noms ne doivent pas contenir plus de 64 caractères.

• Évitez d'utiliser des préfixes.

Règles pour les noms de base de donnéesSuivez toutes les règles générales ci-dessus.

• Le nom peut être à la fois singulier et pluriel, mais la base de données représente une base de données, il doit donc être au singulier autant que possible.

• Évitez les préfixes dans la mesure du possible.

Règles pour les noms de tableUtilisez des minuscules pour les noms de table : MySQL est généralement hébergé sur des serveurs Linux, il est donc sensible à la casse, donc la meilleure pratique consiste à mettre les noms de table en minuscules.

• Les noms des tables doivent être au singulier : la table est une entité unique tout comme le modèle, il est donc étrange d'avoir le nom de la table au pluriel.

• Préfixes dans le nom de la table : Nous avons vu plusieurs fois que les tables sont préfixées du nom de la base de données ou du nom du projet. Parfois, cela est nécessaire lorsque dans notre projet, nous avons de nombreuses bases de données pour surmonter la limitation de certains hébergeurs. Mais si ce n'est pas nécessaire et que notre projet est petit, évitez d'utiliser des préfixes.

Nom des champsUtilisez toutes les règles ci-dessus, c'est-à-dire utilisez des minuscules, n'utilisez pas d'espaces vides, n'utilisez pas de chiffres et évitez les préfixes.

• Utilisez un ou deux mots courts si possible.

• Les noms des champs doivent pouvoir être compris, par exemple : prix, nom_société, etc.

• Nom de la colonne primaire : la clé primaire peut avoir un nom d'identifiant ou un nom de table _id. Cela dépendra du choix.

• Évitez d'utiliser des mots réservés dans les champs : *****, date, etc. Dans ces cas, il est préférable d'utiliser des préfixes tels que record_date, etc.

• Évitez d'utiliser des noms de colonnes avec le même nom de table. Cela peut être source de confusion lors de la rédaction de vos requêtes.

• Évitez les noms dans des acronymes abrégés ou concaténés.

2. Utilisez toujours le bon type de données

ExempleL'utilisation de varchar (20) pour stocker des valeurs datetime au lieu de DATETIME peut provoquer des erreurs lors des calculs d'heure liés à la date et est également possible en cas de stockage de données non valide.

3. Utilisez CHAR (1) sur VARCHAR (1)

4. CHAR pour les données fixes

Exemple:

L'utilisation de CHAR (1 000) au lieu de VARCHAR (1 000) consomme plus d'espace si la longueur des données est inférieure à 1 000.

5. Évitez d'utiliser des formats de date régionaux

• DATEHEURE
• DATE

Utilisez toujours le format AAAA-MM-JJ ou le format de date ISO qui convient à votre moteur SQL. Les autres formats régionaux tels que JJ-MM-AAAA, JJ-MM-AAAA ne seront pas stockés correctement.

6. Optimisez vos demandes de cache

 // Le cache ne fonctionne PAS $ r = mysql_query ("SELECT name FROM users WHERE record> = CURDATE ()"); 

 // Le cache fonctionne $ aujourd'hui = date ("A-m-d"); $ r = mysql_query ("SELECT name FROM utilisateurs WHERE record> = '$ today'"); 

7. Évitez d'utiliser "SELECT *" dans vos requêtes

C'est une bonne habitude spécifiez les colonnes nécessaires dans votre instruction SELECT.

8. Utilisez LIMIT 1 lorsque vous ne voulez qu'une seule ligne

Dans ces cas, l'ajout de LIMIT 1 à votre requête peut considérablement améliorer la vitesse. De cette façon, la base de données arrêtera d'analyser les résultats dès qu'elle en trouvera un, au lieu de parcourir toute la table ou un index.

 // J'ai des utilisateurs de Madrid ? // quoi NE PAS faire : $ r = mysql_query ("SELECT * FROM user WHERE city =' Madrid '"); if (mysql_num_rows ($ r)> 0) {//…} // bien mieux : $ r = mysql_query ("SELECT 1 FROM user WHERE city =' Madrid 'LIMIT 1"); if (mysql_num_rows ($ r)> 0) {//…}

9. Utilisation de ***** PAR

N'abusez pas de son utilisation.

10. Choisissez le moteur de base approprié

Si vous développez une application qui écrit des données plus fréquemment que la lecture
(par exemple : banque en temps réel), choisissez le moteur de stockage InnoDB.

Choisir le mauvais moteur de stockage affectera les performances de vos requêtes.

11. Utilisez la clause EXISTS chaque fois que nécessaire

 Si (SELECT compte (*) de la table WHERE col = 'une certaine valeur')> 0

 Si EXISTE (SELECT * de la table WHERE col = 'une valeur')

12. Utilisez EXPLAIN dans vos requêtes SELECT

Le résultat d'une requête EXPLAIN vous montrera les index qui sont utilisés, comment la table est explorée, comment elle est ordonnée, etc.

Sélectionnez une requête SELECT (de préférence une requête complexe, avec des jointures) et ajoutez le mot EXPLAIN au début de tout. Il renverra les résultats dans un tableau simple. Par exemple, supposons que j'ai oublié d'indexer une colonne, cela nous montrerait l'écran suivant :

AGRANDIR

Après avoir ajouté l'index à la table d'état, cela ressemblerait à ceci :

AGRANDIR

13. Indexer et utiliser le même type de colonne pour les jointures

De plus, les colonnes que vous joignez doivent être du même type. Par exemple, si vous joignez une colonne de type DECIMAL à une colonne de type INT d'une autre table, MySQL ne pourra pas utiliser au moins un des deux index. Même l'encodage des caractères doit être du même type pour les colonnes de Type de chaîne.

 // recherche d'entreprises dans ma ville $ r = mysql_query ("SELECT company_name FROM users LEFT JOIN companyias ON (users.city = companyias.city) WHERE users.id = $ user_id");

14. Utilisez NOT NULL si vous le pouvez

Tout d'abord, demandez-vous s'il y aurait une différence entre avoir une chaîne vide et une valeur NULL (ou pour les champs INT : 0 contre NULL). S'il n'y a pas de problème entre les deux valeurs, vous n'avez pas besoin d'un champ NULL. Les colonnes NULL nécessitent de l'espace supplémentaire et peuvent ajouter de la complexité à vos instructions de comparaison. Évitez-les quand vous le pouvez.

Dans tous les cas, nous comprenons que dans certains cas très spécifiques il y a une raison d'utiliser des colonnes NULL, ce qui n'est pas toujours une mauvaise chose.

15. Les tables de taille fixe (statique) sont plus rapides

Voici quelques exemples de types de colonnes qui ne sont PAS de taille fixe :

• VARCHAR
• TEXTE
• GOUTTE

Si vous n'incluez qu'un seul de ces types de colonnes, le tableau ne sera plus de taille fixe et devra être traité différemment par le Moteur MySQL.

Les tableaux de taille fixe peuvent augmenter la productivité car il est plus rapide pour le moteur MySQL de rechercher dans vos enregistrements. Lorsque vous souhaitez lire une ligne spécifique dans le tableau, vous pouvez rapidement calculer sa position. Si la taille de la ligne n'est pas fixe, chaque fois que vous devez rechercher, vous devez d'abord interroger l'index de la clé primaire.

Ils sont également plus faciles à rechercher et à reconstruire après un accident. Mais d'un autre côté, ils pourraient aussi prendre plus de place.

16. Partitionnement vertical

Exemple 1:

Vous aurez sûrement une table utilisateur qui contient une adresse postale, ce qui n'est pas très souvent utilisé. Ici, vous pouvez diviser la table et stocker les adresses dans une table séparée. De cette façon, votre table utilisateur principale aurait une taille plus étroite. Comme vous le savez, plus les tables sont petites, plus les tables sont rapides.

Exemple 2 :

Vous avez un champ "last_access" dans votre table. Il est mis à jour chaque fois qu'un utilisateur accède à votre page. Mais chaque accès provoque la libération du cache de requêtes pour cette table. Ce que vous pouvez faire est de mettre ce champ dans une autre table afin que les modifications dans votre table utilisateur soient réduites au minimum.

Mais vous devez également vous assurer que vous n'avez pas besoin de joindre constamment les deux tables après le partitionnement ou vous subirez une baisse de performances, tout le contraire de ce que nous recherchions.

17. Stocke les adresses IP comme UNSIGNED INT

Mais vous devez vous assurer que le la colonne est UNSIGNED INT (entier non signé) car les adresses IP utilisent toute la plage non signée de 32 bits.

Dans vos requêtes, vous pouvez utiliser le Fonction INET_ATON() pour convertir une adresse IP en un entier, et INET_NTOA() pour faire le contraire. Il existe également des fonctions similaires en PHP appelées ip2long () Oui long2ip ().

18. Créer des vues pour simplifier l'utilisation courante dans les tableaux

Il peut également être utilisé pour filtrer les colonnes non indexées, ne laissant que les champs qui s'affichent plus rapidement dans la recherche.

19. Ne pas utiliser ***** PAR RAND ()

Si vous avez vraiment besoin de tableaux aléatoires pour votre résultat, il existe de bien meilleures façons de le faire. Il est clair qu'ils prendront plus de code, mais vous éviterez un éventuel goulot d'étranglement qui augmente de façon exponentielle à mesure que votre contenu se développe.

Le problème est que MySQL devra s'exécuter RAND () (ce qui nécessite de la puissance de traitement) pour chacune des lignes avant de les trier et de renvoyer une seule ligne.

 // la façon de NE PAS le faire : $ r = mysql_query ("SELECT username FROM user ***** BY RAND () LIMIT 1"); // bien mieux : $ r = mysql_query ("SELECT count (*) FROM user"); $ d = mysql_fetch_row ($ r); $ rand = mt_rand (0, $ d [0] - 1); $ r = mysql_query ("SELECT nom d'utilisateur FROM nom d'utilisateur LIMIT $ rand, 1");

20. Optimiser la clause WHERE

• Supprimez les parenthèses inutiles. Par exemple:

 De à5 ET b = c ET a = 5

 SELECT COUNT (*) FROM table.