Cet article explore des idées pour améliorer les performances de la base de données MySQL et propose des solutions spécifiques sous 8 aspects.
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 NULL
Définissez le chemin des données et placez les fichiers de données de la base de données dans le répertoire NFS partagé (serveur NAS).
Les fichiers PID et innioDB doivent être placés dans le répertoire local du serveur pour démarrer et arrêter le service normalement :
1125. vi /etc/ my.cnf
[mysqld]
#Où installer une base de données data
datadir=/data/mysqldata
#Où installer un moteur innoDB
innodb_data_home_dir = /usr/local/mysql/data
innodb_log_group_home_dir= /usr/local/mysql/data
innodb_data_file_path=ibdata1: 50M;ibdata2:50M:autoextend
1106 cp ./support-files/mysql.server /etc/rc.d/init.d/
vi /etc/rc.d/init.d/mysql.server
Compilation liée items à partir de 222nd Les deux lignes de , placez le fichier PID dans le répertoire local du serveur :
pid_file=/usr/local/mysql/data/mysqlmanager-`/bin/hostname`.pid
server_pid_file=/usr/local/mysql /data/`/bin/ hostname`.pid
installe la base de données de base de MySQL :
1123 mount 10.4.66.251:/data /data
1124 mkdir /data/mysqldata
1127 ./scripts/mysql_install_db --user=mysql
1145 chown -R mysql .mysql /data/mysqldata/
if Si c'est normal, vous pouvez voir que mysql démarre normalement
1146 /etc/rc.d/init.d/mysql.server start
1146 /etc/rc.d/init.d/mysql.server ; arrêtez de
configurer la haute disponibilité HA, ne la configurez pas. Le montage NFS et le service mysql s'exécutent automatiquement au démarrage du serveur ;
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 son exclusivité, cela affecte parfois la performances de la base de données, en particulier sur de très longues périodes dans les grands systèmes d'application. É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';
DÉBLOQUER LES TABLEAUX
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 le tableau. 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
L'indexation et la recherche en texte intégral sont prises en charge à 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 commande 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 inventor WHERE Amount/7<24;
SELECT * 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.