1. Sélectionnez les attributs de champ les plus applicables.
MySQL peut bien prendre en charge l'accès à de grandes quantités de données, mais d'une manière générale, plus la table de la base de données est petite, plus les requêtes exécutées dessus seront rapides. Par conséquent, lors de la création d’un tableau, afin d’obtenir de meilleures performances, nous pouvons définir la largeur des champs du tableau aussi petite que possible. Par exemple, lors de la définition du champ du code postal, si vous le définissez sur CHAR(255), cela ajoutera évidemment de l'espace inutile à la base de données. Même l'utilisation du type VARCHAR est redondante, car CHAR(6) est une bonne mission accomplie. De même, si possible, nous devrions utiliser MEDIUMINT au lieu de BIGIN pour définir des champs entiers.
Une autre façon d'améliorer l'efficacité consiste à définir les champs sur NOT NULL lorsque cela est possible, afin que la base de données n'ait pas besoin de comparer les valeurs NULL lors de l'exécution de requêtes ultérieures.
Pour certains champs de texte, comme « province » ou « sexe », nous pouvons les définir comme types ENUM. Parce que dans MySQL, le type ENUM est traité comme des données numériques et les données numériques sont traitées beaucoup plus rapidement que les types texte. De cette façon, nous pouvons améliorer les performances de la base de données.
2. Utilisez des jointures (JOIN) au lieu de sous-requêtes (Sous-requêtes).
MySQL prend en charge les sous-requêtes SQL à partir de la version 4.1. Cette technique vous permet d'utiliser une instruction SELECT pour créer une seule colonne de résultats de requête, puis d'utiliser ce résultat comme condition de filtre dans une autre requête. Par exemple, si nous souhaitons supprimer les clients qui n'ont aucune commande dans la table d'informations client de base, nous pouvons utiliser une sous-requête pour récupérer d'abord les identifiants de tous les clients qui ont émis des commandes à partir de la table d'informations sur les ventes, puis transmettre les résultats à la requête principale, comme indiqué ci-dessous :
DELETE FROM customerinfo
WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo)
L'utilisation de sous-requêtes peut effectuer de nombreuses opérations SQL qui nécessitent logiquement plusieurs étapes à la fois. Cela peut également éviter les verrous de transaction ou de table, et il est également facile à écrire. Cependant, dans certains cas, les sous-requêtes peuvent être remplacées par des jointures plus efficaces (JOIN). Par exemple, supposons que nous souhaitions récupérer tous les utilisateurs qui n'ont pas d'enregistrement de commande, nous pouvons utiliser la requête suivante :
SELECT * FROM customerinfo
OÙ CustomerID PAS dans (SELECT CustomerID FROM salesinfo)
Si vous utilisez la connexion (JOIN) .. pour compléter cette requête, la vitesse sera beaucoup plus rapide. Surtout s'il y a un index sur CustomerID dans la table salesinfo, les performances seront meilleures. La requête est la suivante :
SELECT * FROM customerinfo.
REJOIGNEZ À GAUCHE salesinfoON customerinfo.CustomerID=salesinfo.
Numéro client
OÙ salesinfo.CustomerID EST une connexion NULL
(JOIN). La raison pour laquelle elle est plus efficace est que MySQL n'a pas besoin de créer une table temporaire en mémoire pour terminer cette requête logique en deux étapes.
3. Utilisez union (UNION) pour remplacer les tables temporaires créées manuellement.
MySQL prend en charge les requêtes UNION à partir de la version 4.0, qui peuvent fusionner deux ou plusieurs requêtes SELECT nécessitant l'utilisation de tables temporaires en une seule requête. À la fin de la session de requête du client, la table temporaire sera automatiquement supprimée pour garantir que la base de données est ordonnée et efficace. Lorsque vous utilisez UNION pour créer une requête, nous devons uniquement utiliser UNION comme mot-clé pour connecter plusieurs instructions SELECT. Il convient de noter que le nombre de champs dans toutes les instructions SELECT doit être le même. L'exemple suivant illustre une requête utilisant UNION.
SELECT Nom, Téléphone DU client
UNION
SELECT Nom, Date de naissance FROM auteur
UNION
SELECT Nom, Fournisseur FROM produit
4. Transactions
Bien que nous puissions utiliser des sous-requêtes, des connexions (JOIN) et des unions (UNION) pour créer une variété de requêtes, toutes les opérations de base de données ne peuvent pas être effectuées avec une seule. Ou elles peuvent être complétées avec un seul quelques instructions SQL. Le plus souvent, une série d’énoncés est nécessaire pour accomplir un certain type de travail. Mais dans ce cas, lorsqu'une certaine instruction de ce bloc d'instructions s'exécute de manière incorrecte, le fonctionnement de l'ensemble du bloc d'instructions deviendra incertain. Imaginez que vous souhaitiez insérer certaines données dans deux tables liées en même temps. Cela peut arriver : une fois la mise à jour réussie de la première table, une situation inattendue se produit dans la base de données, empêchant l'opération dans la deuxième table de se terminer. De cette façon, les données seront incomplètes et même les données de la base de données seront détruites. Pour éviter cette situation, vous devez utiliser des transactions. Sa fonction est la suivante : soit chaque instruction du bloc d'instructions réussit, soit elle échoue. 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.
COMMENCER;
INSERT INTO salesinfo SET CustomerID=14 ;
MISE À JOUR de l'inventaire SET Quantité = 11
OÙ item='livre';
COMMIT;
Un autre rôle important des transactions est que lorsque plusieurs utilisateurs utilisent la même source de données en même temps, ils peuvent utiliser la méthode de verrouillage de la base de données pour fournir aux utilisateurs une méthode d'accès sécurisée, qui peut garantir que les opérations de l'utilisateur ne sont pas bloquées. par d'autres utilisateurs.
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, en raison de leur exclusivité, elles affectent parfois les performances de la base de données, en particulier dans les 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. Si un système de base de données n'est utilisé que par quelques utilisateurs, l'impact des transactions ne deviendra pas un gros problème ; mais si des milliers d'utilisateurs accèdent à un système de base de données en même temps, par exemple en accédant à un site Web de commerce électronique, cela entraînera de graves problèmes. délai de réponse.
En fait, dans certains cas, nous pouvons obtenir de meilleures performances en verrouillant la table. L'exemple suivant utilise la méthode de table de verrouillage pour terminer la fonction de transaction dans l'exemple précédent.
VERROUILLER TABLE inventaire ÉCRIRE
SELECT Quantité FROM inventaire
OÙItem='livre';
...
MISE À JOUR de l'inventaire SET Quantité = 11
OÙItem='livre';
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.
La méthode d'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 TABLEinfoclient
(
IDClient INT NON NULL ,
CLÉ PRIMAIRE (IDClient)
) TYPE = INNODB ;
CREATE TABLE informations commerciales
(
SalesID INT NON NULL,
IDClient INT NON NULL,
CLÉ PRIMAIRE (CustomerID, SalesID),
RÉFÉRENCES DE CLÉ ÉTRANGÈRE (CustomerID) customerinfo
(ID client) SUR 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. La méthode définie consiste à ajouter TYPE=INNODB à l'instruction CREATE TABLE. Comme le montre l'exemple.
7. L'utilisation d'index
est une méthode courante pour améliorer les performances de la base de données. Elle permet au serveur de base de données de récupérer des lignes spécifiques beaucoup plus rapidement que sans index, surtout si l'instruction de requête contient MAX(), MIN() et ORDERBY. l'amélioration des performances est plus évidente. Alors, quels champs doivent être indexés ? De manière générale, les index doivent être construits sur des 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. Nous pouvons créer des index appropriés en même temps lors de la création de la table, ou nous pouvons utiliser ALTER TABLE ou CREATE INDEX pour créer des index ultérieurement. De plus, MySQL prend en charge l'indexation et la recherche en texte intégral à partir de la version 3.23.23. L'index de texte intégral est un index de type FULLTEXT dans MySQL, mais il ne peut être utilisé que pour les tables de type MyISAM. Pour une grande base de données, il sera très rapide de charger les données dans une table sans index FULLTEXT puis d'utiliser ALTER TABLE ou CREATE INDEX pour créer l'index. Mais si vous chargez des données dans une table qui possède déjà un index FULLTEXT, le processus d'exécution sera très lent.
8. Instructions de requête optimisées
Dans la plupart des cas, l'utilisation d'index peut améliorer la vitesse des requêtes, mais si les instructions SQL ne sont pas utilisées correctement, l'index ne pourra pas jouer le rôle qui lui revient. Voici plusieurs aspects auxquels il convient de prêter attention. Premièrement, il est préférable d’effectuer des opérations de comparaison entre des champs du même type. Avant la version 3.23 de MySQL, c'était même une condition requise. Par exemple, un champ INT indexé ne peut pas être comparé à un champ BIGINT mais comme cas particulier, lorsqu'un champ de type CHAR et un champ de type VARCHAR ont la même taille, ils peuvent être comparés. Deuxièmement, essayez de ne pas utiliser de fonctions pour opérer sur des champs indexés.
Par exemple, lors de l'utilisation de la fonction YEAE() sur un champ de type DATE, l'index ne fonctionnera pas comme il le devrait. Par conséquent, même si les deux requêtes suivantes renvoient les mêmes résultats, la dernière est beaucoup plus rapide que la première.
SELECT * FROM order WHERE YEAR(OrderDate)＜2001;
SELECT*
FROM order WHERE OrderDate＜"2001-01-01";
La même situation se produira également lors du calcul des champs numériques :
SELECT * FROM stock WHERE Amount/ 7<24;
* FROM inventor WHERE Amount<24*7;
Les deux requêtes ci-dessus renvoient également les mêmes résultats, mais cette dernière requête sera beaucoup plus rapide que la précédente. Troisièmement, lors de la recherche de champs de caractères, nous utilisons parfois des mots-clés LIKE et des caractères génériques. Bien que cette approche soit simple, elle se fait également au détriment des performances du système. Par exemple, la requête suivante comparera chaque enregistrement de la table.
SELECT * FROM livres
WHERE nom comme "MySQL%"
Mais si vous utilisez la requête suivante, les résultats renvoyés sont les mêmes, mais la vitesse est beaucoup plus rapide :
SELECT * FROM books
WHERE name>="MySQL" et name<"MySQM"
Enfin, vous devez faire attention à éviter de laisser MySQL effectuer une conversion de type automatique dans la requête, car le processus de conversion rendra également l'index inefficace.