Quelles sont les connaissances sur l’optimisation des bases de données MySQL ?

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 en même temps la vitesse de réponse de l'utilisateur. il est également nécessaire d'économiser autant que possible les ressources du système afin que l'utilisateur puisse Le système fournit une plus grande charge.

1. Aperçu de l'optimisation

2. Optimisation

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

2.1 Optimisation douce

2.1.1 Optimisation des instructions de requête

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

2.Exemple :

DESC SELECT * FROM `utilisateur`

Affichage :

Des 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. Étant donné que les sous-requêtes nécessitent des requêtes imbriquées, une table temporaire sera créée lors de l'imbrication des requêtes. L'établissement et la suppression de la table temporaire entraîneront une surcharge système importante, tandis que la requête de jointure ne créera pas de requête. table temporaire, elle est donc plus efficace que les sous-requêtes imbriquées.

2.1.3 Utilisation de l'index

L'indexation est l'un des moyens les plus importants pour améliorer la vitesse d'interrogation des bases de données. Concernant l'indexation, vous pouvez vous référer à l'article de l'auteur pour une introduction plus détaillée. Voici trois précautions majeures pour l'utilisation des index : 数据库索引>

. 1. Le mot-clé LIKE correspond aux chaînes commençant par « % » et n'utilise pas d'index.

2. Les deux champs du mot-clé OR doivent être indexés avant que la requête n'utilise l'index.

3. L'utilisation d'index multi-colonnes doit satisfaire la correspondance la plus à gauche.

2.1.4 Tableau de décomposition

Pour les tableaux comportant de nombreux champs, si certains champs sont utilisés moins fréquemment, il faut les séparer pour former un nouveau tableau,

2.1.5 Table intermédiaire

Pour les tables qui interrogent un grand nombre de connexions, des tables intermédiaires peuvent être créées pour réduire le temps de connexion consommé lors des requêtes.

2.1.6 Ajouter des champs redondants

Semblable à la création de tables intermédiaires, l'ajout de redondance consiste également à réduire les requêtes de connexion.

2.1.7 Tableau d'analyse, checklist, tableau d'optimisation

L'analyse du tableau analyse principalement la répartition des mots-clés dans le tableau, la vérification du tableau vérifie principalement s'il y a des erreurs dans le tableau et l'optimisation du tableau élimine principalement le gaspillage d'espace table causé par la suppression ou la mise à jour.

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

1. Op : Indique l'opération effectuée.

2. Msg_type : type d'informations, y compris le statut, les informations, la note, l'avertissement, l'erreur.

3. Msg_text : Afficher les informations.

2. Vérifiez la table : 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 :

1. RAPIDE : Ne scannez pas les lignes, ne vérifiez pas les mauvaises connexions.

2. RAPIDE : vérifie uniquement les tables qui ne sont pas fermées correctement.

3. MODIFIÉ : vérifie uniquement les tables qui ont été modifiées depuis le dernier contrôle et les tables qui n'ont pas été fermées correctement.

4. MOYEN : scannez les lignes pour vérifier que la connexion supprimée est valide et pouvez également calculer les sommes de contrôle des mots clés pour chaque ligne.

5. ÉTENDU : l'inspection la plus complète, recherche complète de mots-clés dans chaque ligne.

3. Optimisez la table : utilisez le mot-clé OPTIMIZE, tel que OPTIMIZE [LOCAL|NO_WRITE_TO_BINLOG] TABLE user ;

LOCAL|NO_WRITE_TO_BINLOG signifie ne pas écrire dans le journal. Les tables optimisées ne sont valables que pour VARCHAR, BLOB et TEXT. 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 dure

2.2.1 Ensemble de matériel en trois pièces

1. Configurez le processeur multicœur et haute fréquence. Le multicœur peut exécuter plusieurs threads.

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

3. Configurez des disques à grande vitesse ou distribuez raisonnablement des disques : les disques à grande vitesse améliorent les E/S 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, améliorant ainsi les performances du serveur MySQL. Les paramètres de configuration du service MySQL sont tous dans my.cnf ou my.ini. Voici plusieurs paramètres qui ont un plus grand impact sur les performances.

• . key_buffer_size : taille du tampon d'index

• table_cache : Le nombre de tables pouvant être ouvertes en même temps

• 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 peut être utilisé dans la requête SQL_NO_CACHE signifie ne pas utiliser le buffer, 2 signifie dans la requête Il est clairement indiqué que le tampon ne doit être utilisé que lors de l'utilisation du tampon, c'est-à-dire SQL_CACHE.

• sort_buffer_size : tampon de tri

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

La base de données étant soumise à trop de pression, le premier problème est que les performances du système peuvent être réduites pendant les périodes de pointe, car une charge excessive de la base de données 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 augmente, vous pouvez continuer à ajouter des machines. Par exemple, si vous continuez à ajouter des machines au niveau du système, vous pouvez gérer un nombre plus élevé de demandes simultané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é le sera. 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 fait 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.

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!