Méthodes courantes pour l'optimisation MySQL

1. Sélectionnez l'attribut de champ le plus applicable
Définissez la largeur des champs dans le tableau aussi petite que possible : la limite supérieure de caractères est de 255. octets (espace d'occupation fixe), la limite supérieure de varchar est de 65 535 octets (espace réellement occupé) et la limite supérieure du texte est de 65 535 octets.
Essayez de définir les champs sur NOT NULL afin que la base de données n'ait pas besoin de comparer les valeurs NULL lors de l'exécution de requêtes.

2. Utilisez des jointures (JOIN) au lieu de sous-requêtes (Sous-requêtes)
La raison pour laquelle les jointures (JOIN) sont plus efficaces est que MySQL n'a pas besoin de l'être. en mémoire La création d'une table temporaire pour compléter cette requête logique nécessite deux étapes (les conditions de requête conjointes et les index sont plus rapides).

3. Utilisez UNION pour remplacer les tables temporaires créées manuellement.
Combinez deux ou plusieurs requêtes SELECT nécessitant l'utilisation de tables temporaires en une seule requête.
SELECT Nom, Téléphone FROM client UNION SELECT Nom, Date de naissance FROM auteur UNION SELECT Nom, Fournisseur FROM produit ;

Transaction
Bien que nous puissions utiliser une sous-requête (Sub - - Requêtes), connexions (JOIN) et unions (UNION) pour créer une variété de requêtes, mais toutes les opérations de base de données ne peuvent pas être effectuées avec une ou quelques instructions SQL. Le plus souvent, une série d’énoncés est nécessaire pour accomplir un certain type de travail.
L'effet est le suivant : soit chaque instruction du bloc d'instructions fonctionne avec succès, soit toutes échouent. En d’autres termes, la cohérence et l’intégrité des données de la base de données peuvent être maintenues. Les choses commencent par le mot-clé BEGIN et se terminent par le mot-clé COMMIT. Si une opération SQL échoue pendant cette période, la commande ROLLBACK peut restaurer la base de données à l'état avant le démarrage de BEGIN.

5. Verrouillage des tables
Bien que les transactions soient un très bon moyen de maintenir l'intégrité de la base de données, du fait de leur exclusivité, elles affectent parfois les performances de la base de données, notamment dans grands systèmes d’applications. Étant donné que la base de données sera verrouillée lors de l'exécution de la transaction, les autres demandes des utilisateurs ne pourront qu'attendre la fin de la transaction.
LOCK TABLE inventaire WRITE
SELECT Quantité FROM inventaire
WHEREItem='book';
...
UPDATE inventor SET Quantity=11
WHEREItem='book'
UNLOCK TABLES
Ici, nous utilisons une instruction SELECT pour récupérer les données initiales, et grâce à certains calculs, utilisons une instruction UPDATE pour mettre à jour les nouvelles valeurs dans la table. L'instruction LOCK TABLE contenant le mot clé WRITE garantit qu'il n'y aura aucun autre accès à l'inventaire à insérer, mettre à jour ou supprimer avant l'exécution de la commande UNLOCK TABLES.

6. L'utilisation de clés étrangères
pour verrouiller la table peut maintenir l'intégrité des données, mais elle ne peut pas garantir la pertinence des données. À ce stade, nous pouvons utiliser des clés étrangères. Par exemple, une clé étrangère peut garantir que chaque enregistrement de vente pointe vers un client existant. Ici, la clé étrangère peut mapper le CustomerID de la table customerinfo au CustomerID de la table salesinfo. Tout enregistrement sans CustomerID valide ne sera pas mis à jour ou inséré dans salesinfo.

CREATE TABLE customerinfo 
( 
CustomerID INT NOT NULL , 
PRIMARY KEY ( CustomerID ) 
) TYPE = INNODB; 
CREATE TABLE salesinfo 
( 
SalesID INT NOT NULL, 
CustomerID INT NOT NULL, 
PRIMARY KEY(CustomerID, SalesID), 
FOREIGN KEY (CustomerID) REFERENCES customerinfo 
(CustomerID) ON DELETECASCADE 
) TYPE = INNODB;

Notez le paramètre « ON DELETE CASCADE » dans l'exemple. Ce paramètre garantit que lorsqu'un enregistrement client dans la table customerinfo est supprimé, tous les enregistrements liés au client dans la table salesinfo seront également automatiquement supprimés. Si vous souhaitez utiliser des clés étrangères dans MySQL, n'oubliez pas de définir le type de table comme un type InnoDB sécurisé pour les transactions lors de la création de la table. Ce type n'est pas le type par défaut pour les tables MySQL. Il est défini en ajoutant TYPE=INNODB à l'instruction CREATE TABLE.

7. Lors de l'utilisation de l'index
, l'amélioration des performances est plus évidente lorsque l'instruction de requête contient des commandes telles que MAX (), MIN () et ORDERBY.
Les indices doivent être construits sur les champs qui seront utilisés pour le tri JOIN, WHERE et ORDER BY. Essayez de ne pas indexer un champ de la base de données contenant un grand nombre de valeurs en double. Pour un champ de type ENUM, il est très possible d'avoir un grand nombre de valeurs en double, comme par exemple le champ "province".. dans customerinfo ne servira à rien, bien au contraire. Réduisez les performances de la base de données.

8. Instruction de requête optimisée
SELECT FROM order WHERE YEAR(OrderDate)<2001;
SELECT FROM order WHERE OrderDate<"2001-01-01"

SELECT FROM inventor WHERE Amount/7<24;
SELECT FROM inventor WHERE Amount<24*7;
Évitez de laisser MySQL effectuer une conversion de type automatique dans la requête, car le processus de conversion rendra également le l’index devient inefficace.

Pour plus d'articles techniques liés à SQL, veuillez visiter la colonne Tutoriel SQL pour apprendre !

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!