Introduction à l'optimisation des bases de données MySQL (images et texte)

Le contenu de cet article est une introduction (images et textes) sur l'optimisation des bases de données MySQL. Il a une certaine valeur de référence. Les amis dans le besoin peuvent s'y référer.

D'une part, l'optimisation de la base de données consiste à identifier les goulots d'étranglement du système et à améliorer les performances globales de la base de données MySQL. D'autre part, elle nécessite une conception structurelle raisonnable et un ajustement des paramètres pour améliorer la vitesse de réponse de l'utilisateur. , et en même temps, il doit être aussi efficace que possible. Économisez les ressources système afin que le système puisse fournir une plus grande charge (Recommandations associées : Tutoriel MySQL)

1. 🎜>

2. Optimisation

L'auteur divise l'optimisation en deux catégories, l'optimisation douce et l'optimisation dure implique généralement l'exploitation de la base de données, tandis que l'optimisation dure implique l'exploitation. le matériel du serveur et les paramètres des paramètres.

2.1 Optimisation logicielle

2.1.1 Optimisation de l'instruction de requête

1. Tout d'abord, nous pouvons utiliser EXPLAIN ou DESCRIBE (abréviation : DESC). commande pour analyser les informations d'exécution d'une instruction de requête.

2. Exemple :

DESC SELECT * FROM `user`

Afficher :

Dans quelles informations telles que le nombre de données d'index et de requête lues seront affichées.

2.1.2 Optimisation des sous-requêtes

Dans MySQL, essayez d'utiliser JOIN au lieu de sous-requêtes, car les sous-requêtes nécessitent des requêtes imbriquées, a. Une table temporaire sera créée lors de l'imbrication des requêtes. La création et la suppression de la table temporaire entraîneront une surcharge système importante, et les requêtes de jointure ne créent pas de tables temporaires, l'efficacité est donc supérieure à celle des sous-requêtes imbriquées.

2.1.3 Utilisation index

Les index sont un moyen d'améliorer la base de données. L'une des méthodes les plus importantes pour la vitesse des requêtes. Concernant les index, vous pouvez vous référer à l'article de l'auteur "MySQL Database Index" pour une introduction plus détaillée. précautions d'utilisation des index :

Touche LIKE Le mot correspond à la chaîne commençant par '%', et l'index ne sera pas utilisé
1. Les deux champs du mot-clé OR doivent être indexés. , et la requête utilisera l'index.
2. Utiliser plusieurs L'index de colonne doit satisfaire la correspondance la plus à gauche.

2.1.4 Table décomposée

Pour les tables avec plusieurs champs, si certains champs sont utilisés moins fréquemment, ils doivent être séparés pour former une nouvelle table,

2.1.5 Table intermédiaire

Pour les tables qui interrogent un grand nombre de connexions, une table intermédiaire peut être créé pour réduire la connexion fastidieuse provoquée lors de la requête.

2.1.6 Ajouter des champs redondants

Semblable à la création d'une table intermédiaire, l'ajout de redondance consiste également à réduire les requêtes de connexion.

2.1.7 Analyser les tableaux, vérifier les tableaux, optimiser les tableaux

Le tableau d'analyse analyse principalement la répartition des mots-clés dans le tableau, le tableau de contrôle vérifie principalement s'il y a des erreurs dans le tableau, et le La table d'optimisation élimine principalement le gaspillage d'espace table causé par la suppression ou la mise à jour

Table d'analyse : utilisez le mot-clé ANALYZE, tel que l'utilisateur ANALYZE TABLE ;

Op : indique l'opération effectuée.

Msg_type : type d'informations, y compris l'état, les informations, la note, l'avertissement, l'erreur.
Msg_text : afficher les informations.

Liste de contrôle : utilisez le mot-clé CHECK, tel que l'utilisateur CHECK TABLE [option]

L'option n'est valable que pour MyISAM, avec un total de cinq valeurs de paramètres :

RAPIDE : Pas d'analyse des lignes, ne vérifiez pas les mauvaises connexions.

RAPIDE : Vérifiez uniquement les tables qui n'ont pas été vérifiées. fermé correctement.

CHANGED : Vérifiez uniquement les tables qui ont été modifiées depuis la dernière vérification et les tables qui n'ont pas été fermées correctement.

MEDIUM : Scannez les lignes pour vérifier que la connexion supprimée est valide. , et peut également calculer la somme de contrôle de clé de chaque ligne.

ÉTENDU : la vérification la plus complète, clé pour chaque ligne Recherche de mots complète

Optimiser le tableau : utilisez le mot-clé OPTIMIZE, tel que. Utilisateur OPTIMIZE [LOCAL|NO_WRITE_TO_BINLOG] TABLE ; VARCHAR, BLOB et TEXT sont valides. La fragmentation des fichiers peut être éliminée via l'instruction OPTIMIZE TABLE et des verrous en lecture seule seront ajoutés lors de l'exécution.

2.2 Optimisation matérielle

2.2.1 Ensemble matériel en trois parties

1. Configurer un processeur multicœur et haute fréquence peut exécuter plusieurs threads

2. augmentez la mémoire pour augmenter la capacité du cache, réduisant ainsi le temps d'E/S du disque, améliorant ainsi la vitesse de réponse.

3. Configurez des disques à grande vitesse ou des disques raisonnablement distribués : les disques à grande vitesse améliorent les I/. O et les disques distribués peuvent améliorer la capacité des opérations parallèles.

2.2.2 Optimiser les paramètres de la base de données

L'optimisation des paramètres de la base de données peut améliorer l'utilisation des ressources et ainsi améliorer les performances du serveur MySQL. Les services MySQL sont tous dans my.cnf ou my.ini. Voici ceux qui ont le plus grand impact sur les performances.

key_buffer_size : taille du tampon d'index

table_cache : le nombre de. tables pouvant être ouvertes simultanément

query_cache_size et query_cache_type : le premier est la taille du tampon de requête, le second est le commutateur du paramètre précédent, 0 signifie ne pas utiliser le tampon, 1 signifie utiliser le tampon, mais vous pouvez utiliser SQL_NO_CACHE dans la requête pour signifier ne pas utiliser le tampon, 2 signifie Utiliser le tampon uniquement s'il est clairement indiqué dans la requête, c'est-à-dire SQL_CACHE.

sort_buffer_size : Tampon de tri

Portail :Plus de paramètres

2.2.3 Sous-base de données et sous-table

La pression de la base de données étant trop élevée, le premier problème est que les performances du système peuvent être réduites pendant les périodes de pointe, car une base de données excessive la charge aura un impact sur les performances. Autre question, que devez-vous faire si votre base de données plante à cause d’une pression excessive ? Ainsi, à ce stade, vous devez diviser le système en bases de données et tables + séparation lecture-écriture, c'est-à-dire diviser une base de données en plusieurs bases de données, les déployer sur plusieurs services de base de données, puis servir de base de données principale pour gérer les demandes d'écriture. Ensuite, chaque bibliothèque maître monte au moins une bibliothèque esclave, et la bibliothèque esclave gère les demandes de lecture.

2.2.4 Cluster de cache

Si le nombre d'utilisateurs devient de plus en plus grand, vous pouvez continuer à ajouter des machines à ce moment, par exemple, à au niveau du système. En ajoutant plus de machines, vous pouvez gérer des demandes simultanées plus élevées. Ensuite, si la concurrence d'écriture au niveau de la base de données devient de plus en plus élevée, le serveur de base de données sera étendu et la machine sera étendue via le partitionnement de sous-bases de données et de tables. Si la concurrence de lecture au niveau de la base de données devient de plus en plus élevée, la capacité. sera étendu et davantage de bases de données esclaves seront ajoutées. Mais il y a ici un gros problème : la base de données elle-même n'est pas réellement utilisée pour traiter un nombre élevé de requêtes simultanées. Par conséquent, d'une manière générale, la concurrence portée par une seule machine de base de données par seconde est de l'ordre de milliers, et les machines utilisées par la base de données sont de l'ordre de plusieurs milliers. configuration relativement élevée, machines relativement chères, le coût est très élevé. Si vous continuez simplement à ajouter des machines, c’est en réalité une erreur. Par conséquent, le cache est généralement inclus dans les architectures à haute concurrence. Le système de cache est conçu pour supporter une haute concurrence. Par conséquent, la quantité de concurrence transportée par une seule machine est de plusieurs dizaines de milliers, voire centaines de milliers par seconde, et la capacité de charge d'une concurrence élevée est d'un à deux ordres de grandeur supérieure à celle d'un système de base de données. Par conséquent, vous pouvez introduire complètement un cluster de cache pour les requêtes qui nécessitent moins d'écriture et plus de lecture en fonction des caractéristiques métier du système. Plus précisément, lors de l'écriture dans la base de données, une copie des données est écrite simultanément dans le cluster de cache, puis le cluster de cache est utilisé pour transporter la plupart des requêtes de lecture. Dans ce cas, grâce au clustering de cache, moins de ressources machine peuvent être utilisées pour héberger une concurrence plus élevée.

Conclusion

Une architecture système complète et complexe à haute concurrence comprendra certainement : divers systèmes d'infrastructure complexes auto-développés. Toutes sortes de conceptions architecturales exquises. Par conséquent, un petit article peut tout au plus avoir pour effet d'inspirer les autres, mais c'est tout pour les idées d'optimisation de base de données.

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!